max / makenotwork

1.1 KB · 26 lines History Blame Raw

1	-- Mailing list infrastructure for per-project email lists (I3).
2	-- Lists are created automatically on project creation (content + devlog).
3	-- Subscribers are tracked separately from the follow social graph.
4
5	CREATE TABLE mailing_lists (
6	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
7	project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
8	list_type TEXT NOT NULL CHECK (list_type IN ('content', 'devlog', 'patches')),
9	name VARCHAR(200) NOT NULL,
10	description TEXT,
11	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
12	UNIQUE (project_id, list_type)
13	);
14
15	CREATE TABLE mailing_list_subscribers (
16	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17	list_id UUID NOT NULL REFERENCES mailing_lists(id) ON DELETE CASCADE,
18	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
19	subscribed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
20	UNIQUE (list_id, user_id)
21	);
22
23	CREATE INDEX idx_mailing_list_subscribers_user ON mailing_list_subscribers (user_id);
24	CREATE INDEX idx_mailing_list_subscribers_list ON mailing_list_subscribers (list_id);
25	CREATE INDEX idx_mailing_lists_project ON mailing_lists (project_id);
26