max / makenotwork

1.3 KB · 32 lines History Blame Raw

1	-- Creator platform import system (Phase 13D).
2	-- Tracks import jobs for CSV/JSON data from Patreon, Ko-fi, Gumroad, etc.
3
4	CREATE TABLE import_jobs (
5	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6	user_id UUID NOT NULL REFERENCES users(id),
7	project_id UUID NOT NULL REFERENCES projects(id),
8	source TEXT NOT NULL,
9	status TEXT NOT NULL DEFAULT 'pending',
10	total_rows INTEGER NOT NULL DEFAULT 0,
11	processed_rows INTEGER NOT NULL DEFAULT 0,
12	created_rows INTEGER NOT NULL DEFAULT 0,
13	skipped_rows INTEGER NOT NULL DEFAULT 0,
14	error_log TEXT,
15	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
16	completed_at TIMESTAMPTZ
17	);
18
19	CREATE INDEX idx_import_jobs_user ON import_jobs(user_id);
20
21	-- Allow email-only subscribers for imported contacts who don't have MNW accounts.
22	-- user_id becomes nullable; exactly one of (user_id, email) must be set.
23	ALTER TABLE mailing_list_subscribers ADD COLUMN email VARCHAR(320);
24	ALTER TABLE mailing_list_subscribers ALTER COLUMN user_id DROP NOT NULL;
25	ALTER TABLE mailing_list_subscribers ADD CONSTRAINT chk_subscriber_identity
26	CHECK (user_id IS NOT NULL OR email IS NOT NULL);
27
28	-- Unique constraint for email-only subscribers (prevent duplicate email per list).
29	CREATE UNIQUE INDEX idx_mailing_list_subscribers_email
30	ON mailing_list_subscribers (list_id, email)
31	WHERE email IS NOT NULL;
32