max / makenotwork

2.4 KB · 57 lines History Blame Raw

1	-- Give every new account its notification subscriptions.
2	--
3	-- 186 backfilled the accounts that existed when it ran. Accounts created after
4	-- it had no subscription rows at all, so the preferences page showed them
5	-- nothing and no unsubscribe link could be minted for a notification they were
6	-- actually receiving.
7	--
8	-- A trigger rather than a line in create_user, because there is no single
9	-- creation path: create_user, create_sandbox_user, create_example_creator, the
10	-- seed flow and the test harness all insert into `users` directly. Six call
11	-- sites that each have to remember is five chances to forget, and the failure
12	-- is silent.
13	--
14	-- Separate migration rather than an edit to 186, which is already applied.
15	-- sqlx checksums the whole file and a deployed database rejects boot when one
16	-- changes, comments included.
17	CREATE OR REPLACE FUNCTION seed_notification_subscriptions() RETURNS TRIGGER AS $$
18	BEGIN
19	WITH prefs(kind, enabled) AS (
20	VALUES
21	('sale', NEW.notify_sale),
22	('follower', NEW.notify_follower),
23	('releases', NEW.notify_release),
24	('issues', NEW.notify_issues),
25	('status', NEW.notify_status),
26	('tip', NEW.notify_tip),
27	('login', NEW.login_notification_enabled)
28	),
29	inserted AS (
30	INSERT INTO list_subscriptions (list_id, user_id, state, source, unsubscribed_at)
31	SELECT l.id,
32	NEW.id,
33	CASE WHEN prefs.enabled THEN 'confirmed' ELSE 'unsubscribed' END,
34	'admin',
35	CASE WHEN prefs.enabled THEN NULL ELSE NOW() END
36	FROM prefs
37	JOIN lists l ON l.scope = 'platform' AND l.kind = prefs.kind
38	ON CONFLICT DO NOTHING
39	RETURNING id, state
40	)
41	-- The account default is a choice we made, not one the user made, and the
42	-- consent log says so rather than recording an opt_in nobody performed.
43	INSERT INTO consent_events (subscription_id, event, evidence)
44	SELECT inserted.id,
45	CASE WHEN inserted.state = 'confirmed' THEN 'opt_in' ELSE 'opt_out' END,
46	'Account default at signup, not an explicit choice by the account holder.'
47	FROM inserted;
48
49	RETURN NEW;
50	END;
51	$$ LANGUAGE plpgsql;
52
53	DROP TRIGGER IF EXISTS trg_seed_notification_subscriptions ON users;
54	CREATE TRIGGER trg_seed_notification_subscriptions
55	AFTER INSERT ON users
56	FOR EACH ROW EXECUTE FUNCTION seed_notification_subscriptions();
57