R524809
82 lines
6.3 KiB
PL/PgSQL
82 lines
6.3 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
-- Fixed platform-capability schema used by collection sources and the ERP header.
|
|
UPDATE platforms
|
|
SET capabilities = capabilities || jsonb_build_object(
|
|
'can_be_source', true, 'can_be_target', true, 'can_be_header_context', true
|
|
), updated_at = now()
|
|
WHERE code = 'OZON';
|
|
|
|
INSERT INTO platforms (code, name, capabilities)
|
|
VALUES
|
|
('1688', '1688', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb),
|
|
('TAOBAO', '淘宝', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb),
|
|
('TMALL', '天猫', '{"can_be_source": true, "can_be_target": false, "can_be_header_context": false}'::jsonb)
|
|
ON CONFLICT (code) DO UPDATE SET capabilities = platforms.capabilities || EXCLUDED.capabilities, updated_at = now();
|
|
|
|
CREATE TYPE collection_source_kind AS ENUM ('platform', 'manual');
|
|
CREATE TYPE collection_item_status AS ENUM ('received', 'materializing', 'ready_for_edit', 'needs_review', 'failed', 'archived', 'deleted');
|
|
CREATE TYPE processing_status AS ENUM ('queued', 'running', 'succeeded', 'failed', 'cancelled');
|
|
CREATE TYPE media_candidate_role AS ENUM ('source_main', 'source_sku', 'source_detail');
|
|
|
|
ALTER TABLE workspaces ADD COLUMN collection_item_limit INTEGER NOT NULL DEFAULT 1000 CHECK (collection_item_limit >= 0);
|
|
|
|
CREATE TABLE collection_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
|
|
source_kind collection_source_kind NOT NULL, source_platform_id UUID REFERENCES platforms(id) ON DELETE RESTRICT,
|
|
source_item_key TEXT NOT NULL, source_url TEXT, source_title TEXT, source_currency_code CHAR(3) REFERENCES currencies(code),
|
|
source_min_price NUMERIC(18,4) CHECK (source_min_price IS NULL OR source_min_price >= 0),
|
|
source_max_price NUMERIC(18,4) CHECK (source_max_price IS NULL OR source_max_price >= source_min_price),
|
|
note TEXT, status collection_item_status NOT NULL DEFAULT 'received', raw_evidence JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
normalized_payload JSONB NOT NULL DEFAULT '{}'::jsonb, parser_version TEXT, collected_at TIMESTAMPTZ,
|
|
last_error JSONB, deleted_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
revision BIGINT NOT NULL DEFAULT 1 CHECK (revision > 0), UNIQUE (workspace_id, id),
|
|
CHECK ((source_kind = 'platform' AND source_platform_id IS NOT NULL) OR (source_kind = 'manual' AND source_platform_id IS NULL)),
|
|
CHECK ((status = 'deleted') = (deleted_at IS NOT NULL))
|
|
);
|
|
CREATE UNIQUE INDEX ux_collection_items_active_source_identity ON collection_items (workspace_id, source_platform_id, source_item_key) WHERE source_platform_id IS NOT NULL AND deleted_at IS NULL;
|
|
CREATE INDEX ix_collection_items_workspace_list ON collection_items (workspace_id, updated_at DESC, id DESC) WHERE deleted_at IS NULL;
|
|
|
|
CREATE OR REPLACE FUNCTION enforce_collection_item_limit() RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|
DECLARE configured_limit INTEGER; active_count INTEGER;
|
|
BEGIN
|
|
IF (TG_OP = 'INSERT' AND NEW.deleted_at IS NULL) OR (TG_OP = 'UPDATE' AND OLD.deleted_at IS NOT NULL AND NEW.deleted_at IS NULL) THEN
|
|
SELECT collection_item_limit INTO configured_limit FROM workspaces WHERE id = NEW.workspace_id FOR UPDATE;
|
|
SELECT count(*) INTO active_count FROM collection_items WHERE workspace_id = NEW.workspace_id AND deleted_at IS NULL;
|
|
IF active_count >= configured_limit THEN RAISE EXCEPTION 'collection item limit reached' USING ERRCODE = 'check_violation'; END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER enforce_collection_item_limit_before_write BEFORE INSERT OR UPDATE OF deleted_at ON collection_items FOR EACH ROW EXECUTE FUNCTION enforce_collection_item_limit();
|
|
|
|
CREATE TABLE products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, collection_item_id UUID NOT NULL,
|
|
source_title TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), version BIGINT NOT NULL DEFAULT 1,
|
|
FOREIGN KEY (workspace_id, collection_item_id) REFERENCES collection_items(workspace_id, id) ON DELETE RESTRICT,
|
|
UNIQUE (workspace_id, id), UNIQUE (workspace_id, collection_item_id)
|
|
);
|
|
CREATE TABLE product_variants (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, product_id UUID NOT NULL,
|
|
source_variant_key TEXT, display_name TEXT, source_sku TEXT, sort_order INTEGER NOT NULL DEFAULT 0, deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
FOREIGN KEY (workspace_id, product_id) REFERENCES products(workspace_id, id) ON DELETE RESTRICT, UNIQUE (workspace_id, id)
|
|
);
|
|
CREATE INDEX ix_product_variants_product ON product_variants (workspace_id, product_id, sort_order) WHERE deleted_at IS NULL;
|
|
CREATE TABLE collection_asset_transfers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id UUID NOT NULL, collection_item_id UUID NOT NULL, product_id UUID NOT NULL,
|
|
source_variant_id UUID, role media_candidate_role NOT NULL, source_url TEXT NOT NULL, status processing_status NOT NULL DEFAULT 'queued',
|
|
asset_id UUID, error_payload JSONB, task_key UUID NOT NULL DEFAULT gen_random_uuid(), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), completed_at TIMESTAMPTZ,
|
|
FOREIGN KEY (workspace_id, collection_item_id) REFERENCES collection_items(workspace_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (workspace_id, product_id) REFERENCES products(workspace_id, id) ON DELETE CASCADE,
|
|
FOREIGN KEY (workspace_id, source_variant_id) REFERENCES product_variants(workspace_id, id) ON DELETE SET NULL,
|
|
FOREIGN KEY (workspace_id, asset_id) REFERENCES media_assets(workspace_id, id) ON DELETE RESTRICT, UNIQUE (workspace_id, task_key)
|
|
);
|
|
|
|
DO $$ DECLARE table_name TEXT; BEGIN
|
|
FOREACH table_name IN ARRAY ARRAY['collection_items', 'products', 'product_variants', 'collection_asset_transfers'] 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 $$;
|