max / makenotwork

2.4 KB · 57 lines History Blame Raw

1	-- The notify_* columns go; subscriptions are the only record now.
2	--
3	-- Step 5c of wiki [[mnw-mailing-lists]]. 5b (MNW@11516ff4) moved all thirteen
4	-- send decisions onto db::lists::may_notify, so nothing reads these columns.
5	-- They stayed one release longer to keep that read flip revertible.
6	--
7	-- THE TRIGGER GOES FIRST, IN THE SAME MIGRATION. 187's seed function reads
8	-- NEW.notify_* to give each new account its subscriptions. Dropping the columns
9	-- without rewriting it breaks the next signup, and the two cannot be separate
10	-- migrations because there is a moment between them where one is true.
11	--
12	-- The defaults move from the column DEFAULT clauses into the function, which is
13	-- now the only place they live. They match db::lists::default_enabled: status
14	-- alerts are opt-in, everything else opt-out.
15	CREATE OR REPLACE FUNCTION seed_notification_subscriptions() RETURNS TRIGGER AS $$
16	BEGIN
17	WITH prefs(kind, enabled) AS (
18	VALUES
19	('sale', TRUE),
20	('follower', TRUE),
21	('releases', TRUE),
22	('issues', TRUE),
23	-- Platform operations noise. A new account has not asked for it.
24	('status', FALSE),
25	('tip', TRUE),
26	('login', TRUE)
27	),
28	inserted AS (
29	INSERT INTO list_subscriptions (list_id, user_id, state, source, unsubscribed_at)
30	SELECT l.id,
31	NEW.id,
32	CASE WHEN prefs.enabled THEN 'confirmed' ELSE 'unsubscribed' END,
33	'admin',
34	CASE WHEN prefs.enabled THEN NULL ELSE NOW() END
35	FROM prefs
36	JOIN lists l ON l.scope = 'platform' AND l.kind = prefs.kind
37	ON CONFLICT DO NOTHING
38	RETURNING id, state
39	)
40	INSERT INTO consent_events (subscription_id, event, evidence)
41	SELECT inserted.id,
42	CASE WHEN inserted.state = 'confirmed' THEN 'opt_in' ELSE 'opt_out' END,
43	'Account default at signup, not an explicit choice by the account holder.'
44	FROM inserted;
45
46	RETURN NEW;
47	END;
48	$$ LANGUAGE plpgsql;
49
50	ALTER TABLE users DROP COLUMN IF EXISTS notify_sale;
51	ALTER TABLE users DROP COLUMN IF EXISTS notify_follower;
52	ALTER TABLE users DROP COLUMN IF EXISTS notify_release;
53	ALTER TABLE users DROP COLUMN IF EXISTS notify_issues;
54	ALTER TABLE users DROP COLUMN IF EXISTS notify_status;
55	ALTER TABLE users DROP COLUMN IF EXISTS notify_tip;
56	ALTER TABLE users DROP COLUMN IF EXISTS login_notification_enabled;
57