16 lines
734 B
SQL
16 lines
734 B
SQL
CREATE TABLE IF NOT EXISTS creator_work_source (
|
|
work_id text NOT NULL REFERENCES creator_work(id) ON DELETE CASCADE,
|
|
platform text NOT NULL CHECK (platform IN ('douyin', 'xiaohongshu')),
|
|
source_type text NOT NULL CHECK (source_type IN ('owned', 'competitor')),
|
|
source_id text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (work_id, source_type, source_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS creator_work_source_lookup_idx
|
|
ON creator_work_source (platform, source_type, source_id, work_id);
|
|
|
|
INSERT INTO creator_work_source (work_id, platform, source_type, source_id)
|
|
SELECT id, platform, source_type, source_id
|
|
FROM creator_work
|
|
ON CONFLICT (work_id, source_type, source_id) DO NOTHING;
|