R524809
133 lines
8.3 KiB
PL/PgSQL
133 lines
8.3 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
-- 发布批次与每个 SKU 的不可变适配快照均沿用商品、配置和工作空间归属链。
|
|
CREATE TABLE product_publish_jobs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
product_id UUID NOT NULL,
|
|
profile_id UUID NOT NULL,
|
|
platform_id UUID NOT NULL,
|
|
store_id UUID NOT NULL,
|
|
product_revision BIGINT NOT NULL,
|
|
profile_revision BIGINT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','succeeded','partially_succeeded','failed','reconciling')),
|
|
item_count INTEGER NOT NULL CHECK (item_count BETWEEN 1 AND 50),
|
|
ozon_task_id TEXT,
|
|
submit_started_at TIMESTAMPTZ,
|
|
poll_count INTEGER NOT NULL DEFAULT 0,
|
|
lease_until TIMESTAMPTZ,
|
|
lease_token UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
completed_at TIMESTAMPTZ,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
FOREIGN KEY (workspace_id, profile_id, product_id, platform_id)
|
|
REFERENCES product_platform_profiles(workspace_id, id, product_id, platform_id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (workspace_id, store_id, platform_id)
|
|
REFERENCES stores(workspace_id, id, platform_id) ON DELETE RESTRICT,
|
|
UNIQUE (workspace_id, id),
|
|
UNIQUE (workspace_id, id, product_id, profile_id, platform_id, store_id)
|
|
);
|
|
CREATE INDEX ix_product_publish_jobs_profile ON product_publish_jobs(workspace_id, profile_id, created_at DESC);
|
|
|
|
CREATE TABLE product_publish_job_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL,
|
|
job_id UUID NOT NULL,
|
|
product_id UUID NOT NULL,
|
|
profile_id UUID NOT NULL,
|
|
platform_id UUID NOT NULL,
|
|
store_id UUID NOT NULL,
|
|
listing_id UUID NOT NULL,
|
|
variant_id UUID NOT NULL,
|
|
display_name TEXT,
|
|
offer_id TEXT NOT NULL CHECK (char_length(offer_id) BETWEEN 1 AND 50),
|
|
status TEXT NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','submitting','polling','imported','failed','skipped','reconciling')),
|
|
snapshot JSONB NOT NULL CHECK (jsonb_typeof(snapshot) = 'object'),
|
|
platform_product_id TEXT,
|
|
errors JSONB NOT NULL DEFAULT '[]'::jsonb CHECK (jsonb_typeof(errors) = 'array'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
FOREIGN KEY (workspace_id, job_id, product_id, profile_id, platform_id, store_id)
|
|
REFERENCES product_publish_jobs(workspace_id, id, product_id, profile_id, platform_id, store_id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (workspace_id, listing_id, product_id, profile_id, variant_id)
|
|
REFERENCES product_variant_platform_listings(workspace_id, id, product_id, profile_id, product_variant_id) ON DELETE RESTRICT,
|
|
UNIQUE (workspace_id, id),
|
|
UNIQUE (workspace_id, job_id, variant_id)
|
|
);
|
|
CREATE INDEX ix_product_publish_job_items_job ON product_publish_job_items(workspace_id, job_id);
|
|
|
|
DO $$
|
|
DECLARE table_name TEXT;
|
|
BEGIN
|
|
FOREACH table_name IN ARRAY ARRAY['product_publish_jobs','product_publish_job_items'] LOOP
|
|
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
|
|
EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', table_name);
|
|
EXECUTE format('CREATE POLICY workspace_isolation ON %I USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id())', table_name);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- 成功导入身份不可被草稿写入覆盖；进行中 SKU 的删除和型号变更也不能绕过 API。
|
|
CREATE FUNCTION guard_first_publish_identity() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
|
|
BEGIN
|
|
IF TG_TABLE_NAME = 'products' THEN
|
|
IF NEW.model IS DISTINCT FROM OLD.model AND EXISTS (
|
|
SELECT 1 FROM product_variant_platform_listings WHERE product_id = OLD.id AND status IN ('imported','submitting')
|
|
) THEN RAISE EXCEPTION 'PRODUCT_MODEL_LOCKED' USING ERRCODE = '23514'; END IF;
|
|
ELSIF TG_TABLE_NAME = 'product_platform_profiles' THEN
|
|
IF NEW.target_store_id IS DISTINCT FROM OLD.target_store_id AND EXISTS (
|
|
SELECT 1 FROM product_variant_platform_listings WHERE profile_id = OLD.id AND status = 'submitting'
|
|
) THEN RAISE EXCEPTION 'PUBLISH_STORE_LOCKED' USING ERRCODE = '23514'; END IF;
|
|
ELSIF TG_TABLE_NAME = 'product_variants' THEN
|
|
IF NEW.deleted_at IS DISTINCT FROM OLD.deleted_at AND EXISTS (
|
|
SELECT 1 FROM product_variant_platform_listings WHERE product_variant_id = OLD.id AND status IN ('imported','submitting')
|
|
) THEN RAISE EXCEPTION 'PUBLISH_VARIANT_LOCKED' USING ERRCODE = '23514'; END IF;
|
|
ELSIF OLD.status = 'imported' AND (
|
|
NEW.status IS DISTINCT FROM OLD.status OR NEW.offer_id IS DISTINCT FROM OLD.offer_id
|
|
OR NEW.store_id IS DISTINCT FROM OLD.store_id OR NEW.platform_product_id IS DISTINCT FROM OLD.platform_product_id
|
|
OR NEW.first_imported_at IS DISTINCT FROM OLD.first_imported_at
|
|
) THEN RAISE EXCEPTION 'SKU_ALREADY_IMPORTED' USING ERRCODE = '23514';
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER product_model_first_publish_guard BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION guard_first_publish_identity();
|
|
CREATE TRIGGER profile_store_first_publish_guard BEFORE UPDATE ON product_platform_profiles FOR EACH ROW EXECUTE FUNCTION guard_first_publish_identity();
|
|
CREATE TRIGGER variant_first_publish_guard BEFORE UPDATE ON product_variants FOR EACH ROW EXECUTE FUNCTION guard_first_publish_identity();
|
|
CREATE TRIGGER listing_first_publish_guard BEFORE UPDATE ON product_variant_platform_listings FOR EACH ROW EXECUTE FUNCTION guard_first_publish_identity();
|
|
|
|
CREATE FUNCTION guard_publish_snapshot() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
|
|
BEGIN
|
|
IF NEW.snapshot IS DISTINCT FROM OLD.snapshot OR NEW.offer_id IS DISTINCT FROM OLD.offer_id
|
|
OR NEW.job_id IS DISTINCT FROM OLD.job_id OR NEW.listing_id IS DISTINCT FROM OLD.listing_id
|
|
OR NEW.variant_id IS DISTINCT FROM OLD.variant_id OR NEW.workspace_id IS DISTINCT FROM OLD.workspace_id
|
|
OR NEW.product_id IS DISTINCT FROM OLD.product_id OR NEW.profile_id IS DISTINCT FROM OLD.profile_id
|
|
OR NEW.platform_id IS DISTINCT FROM OLD.platform_id OR NEW.store_id IS DISTINCT FROM OLD.store_id
|
|
THEN RAISE EXCEPTION 'PUBLISH_SNAPSHOT_IMMUTABLE' USING ERRCODE = '23514'; END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER publish_snapshot_guard BEFORE UPDATE ON product_publish_job_items FOR EACH ROW EXECUTE FUNCTION guard_publish_snapshot();
|
|
|
|
-- 快照创建时必须与当时的店铺一致；后续失败草稿换店不应改写历史快照。
|
|
CREATE FUNCTION guard_publish_item_creation() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
|
|
DECLARE batch_size INTEGER;
|
|
BEGIN
|
|
SELECT item_count INTO batch_size FROM product_publish_jobs WHERE workspace_id=NEW.workspace_id AND id=NEW.job_id FOR UPDATE;
|
|
IF NOT EXISTS(SELECT 1 FROM product_variant_platform_listings WHERE workspace_id=NEW.workspace_id AND id=NEW.listing_id AND store_id=NEW.store_id AND platform_id=NEW.platform_id)
|
|
THEN RAISE EXCEPTION 'PUBLISH_LISTING_STORE_MISMATCH' USING ERRCODE='23514'; END IF;
|
|
IF (SELECT count(*) FROM product_publish_job_items WHERE workspace_id=NEW.workspace_id AND job_id=NEW.job_id) >= batch_size
|
|
THEN RAISE EXCEPTION 'PUBLISH_ITEM_LIMIT_EXCEEDED' USING ERRCODE='23514'; END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER publish_item_creation_guard BEFORE INSERT ON product_publish_job_items FOR EACH ROW EXECUTE FUNCTION guard_publish_item_creation();
|
|
|
|
|
|
-- 批次身份与版本属于历史事实，只允许 Worker 推进执行状态与租约。
|
|
CREATE FUNCTION guard_publish_job_identity() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
|
|
BEGIN
|
|
IF ROW(NEW.workspace_id,NEW.product_id,NEW.profile_id,NEW.platform_id,NEW.store_id,NEW.product_revision,NEW.profile_revision,NEW.item_count)
|
|
IS DISTINCT FROM ROW(OLD.workspace_id,OLD.product_id,OLD.profile_id,OLD.platform_id,OLD.store_id,OLD.product_revision,OLD.profile_revision,OLD.item_count)
|
|
OR (OLD.ozon_task_id IS NOT NULL AND NEW.ozon_task_id IS DISTINCT FROM OLD.ozon_task_id)
|
|
THEN RAISE EXCEPTION 'PUBLISH_JOB_IMMUTABLE' USING ERRCODE='23514'; END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER publish_job_identity_guard BEFORE UPDATE ON product_publish_jobs FOR EACH ROW EXECUTE FUNCTION guard_publish_job_identity();
|