max / makenotwork

4.1 KB · 97 lines History Blame Raw

1	-- Invite-redeemed becomes a preference instead of unstoppable mail.
2	--
3	-- eae9061f classified every sender and had to put `send_invite_redeemed` in
4	-- the cannot-opt-out set, because no list kind covered "someone used one of
5	-- your invite codes" and adding one was out of that task's scope. It was the
6	-- weakest member of that set: a user could reasonably want it off, and the
7	-- operational category is only worth anything if every member earns its place.
8	-- This is the migration that lets it leave.
9
10	-- The kind. Same shape as 186's constraint edit, with 'invite' appended.
11	ALTER TABLE lists DROP CONSTRAINT IF EXISTS lists_kind_check;
12	ALTER TABLE lists ADD CONSTRAINT lists_kind_check CHECK (kind IN (
13	'content', 'devlog', 'patches', 'releases',
14	'issues', 'announce', 'marketing',
15	'sale', 'follower', 'login', 'status', 'tip',
16	'invite'
17	));
18
19	INSERT INTO lists (scope, scope_id, kind, title, required, owner_id) VALUES
20	('platform', NULL, 'invite', 'Invite codes', FALSE, NULL)
21	ON CONFLICT DO NOTHING;
22
23	-- Every existing account subscribes confirmed, because every existing account
24	-- was already receiving this mail and had no way to decline it. Seeding it
25	-- 'unsubscribed' would read as a choice to opt out that nobody made, and would
26	-- silently stop mail people have been getting.
27	INSERT INTO list_subscriptions (list_id, user_id, state, source, created_at)
28	SELECT l.id, u.id, 'confirmed', 'import', u.created_at
29	FROM users u
30	CROSS JOIN lists l
31	WHERE l.scope = 'platform' AND l.kind = 'invite'
32	ON CONFLICT DO NOTHING;
33
34	-- Provenance, same honesty rule as 186 and the step-2 backfill: this is carried
35	-- state, not an opt-in anybody performed, and the consent log says so.
36	INSERT INTO consent_events (subscription_id, event, at, evidence)
37	SELECT ls.id,
38	'import',
39	ls.created_at,
40	'Carried over when invite-redeemed notices stopped being operational mail. '
41	\|\| 'The account was already receiving these and could not decline them, so '
42	\|\| 'the subscription starts confirmed. No opt-in was recorded because none '
43	\|\| 'was ever asked for.'
44	FROM list_subscriptions ls
45	JOIN lists l ON l.id = ls.list_id
46	WHERE l.scope = 'platform'
47	AND l.kind = 'invite'
48	AND ls.source = 'import'
49	AND NOT EXISTS (
50	SELECT 1 FROM consent_events ce
51	WHERE ce.subscription_id = ls.id AND ce.event = 'import'
52	);
53
54	-- New accounts get it too. Same reason 187 used a trigger and 189 rewrote it:
55	-- there is no single user-creation path, and an account with no row falls back
56	-- to a default rather than to a record of what it was told.
57	--
58	-- The whole function is re-declared rather than patched, because CREATE OR
59	-- REPLACE takes a whole body. Only the 'invite' line is new.
60	CREATE OR REPLACE FUNCTION seed_notification_subscriptions() RETURNS TRIGGER AS $$
61	BEGIN
62	WITH prefs(kind, enabled) AS (
63	VALUES
64	('sale', TRUE),
65	('follower', TRUE),
66	('releases', TRUE),
67	('issues', TRUE),
68	-- Platform operations noise. A new account has not asked for it.
69	('status', FALSE),
70	('tip', TRUE),
71	('login', TRUE),
72	-- On by default: an invite is a finite thing you hold and spend,
73	-- so knowing one was used is worth more than the quiet.
74	('invite', TRUE)
75	),
76	inserted AS (
77	INSERT INTO list_subscriptions (list_id, user_id, state, source, unsubscribed_at)
78	SELECT l.id,
79	NEW.id,
80	CASE WHEN prefs.enabled THEN 'confirmed' ELSE 'unsubscribed' END,
81	'admin',
82	CASE WHEN prefs.enabled THEN NULL ELSE NOW() END
83	FROM prefs
84	JOIN lists l ON l.scope = 'platform' AND l.kind = prefs.kind
85	ON CONFLICT DO NOTHING
86	RETURNING id, state
87	)
88	INSERT INTO consent_events (subscription_id, event, evidence)
89	SELECT inserted.id,
90	CASE WHEN inserted.state = 'confirmed' THEN 'opt_in' ELSE 'opt_out' END,
91	'Account default at signup, not an explicit choice by the account holder.'
92	FROM inserted;
93
94	RETURN NEW;
95	END;
96	$$ LANGUAGE plpgsql;
97