Skip to main content

max / makenotwork

4.3 KB · 90 lines History Blame Raw
1 -- Account notification preferences become subscriptions.
2 --
3 -- Step 5 of wiki [[mnw-mailing-lists]]. Seven bool columns on `users` decided
4 -- who got which notification, while project lists were rows in a table with an
5 -- unsubscribe path. Same act, two mechanisms, two standards of care. This is
6 -- the migration that levels them.
7 --
8 -- The task named six columns. There are seven: notify_tip exists too, and it
9 -- gates a real send (routes/stripe/webhook/checkout_helpers.rs).
10 --
11 -- READS STILL USE THE COLUMNS after this migration, deliberately. Each of those
12 -- reads is a send decision and there are nine of them across auth, follows,
13 -- issues and checkout; flipping them in the same release as the data move gives
14 -- two suspects when something stops arriving. The columns stay authoritative
15 -- for one deploy and every write keeps both sides in step, so this is
16 -- reversible by ignoring the new rows.
17
18 -- The notification kinds. 'releases' and 'issues' already exist for project and
19 -- repo scopes; a platform-scope list of the same kind is a different list,
20 -- which the (scope, kind) uniqueness already allows.
21 ALTER TABLE lists DROP CONSTRAINT IF EXISTS lists_kind_check;
22 ALTER TABLE lists ADD CONSTRAINT lists_kind_check CHECK (kind IN (
23 'content', 'devlog', 'patches', 'releases',
24 'issues', 'announce', 'marketing',
25 'sale', 'follower', 'login', 'status', 'tip'
26 ));
27
28 -- One platform list per preference.
29 --
30 -- Sign-in alerts are `required`. Opting out of being told your account was
31 -- accessed from a new device is not a preference worth offering: it is the
32 -- notification most likely to be the first sign of a compromise, and the
33 -- account it protects is the one an attacker would silence first. It keeps its
34 -- column and its settings toggle for now (see the read note above), but on the
35 -- unsubscribe page it renders as "Always sent" and unsubscribe-from-all skips
36 -- it. Decided here rather than inherited by accident.
37 INSERT INTO lists (scope, scope_id, kind, title, required, owner_id) VALUES
38 ('platform', NULL, 'sale', 'Sales', FALSE, NULL),
39 ('platform', NULL, 'follower', 'New followers', FALSE, NULL),
40 ('platform', NULL, 'releases', 'Releases you follow', FALSE, NULL),
41 ('platform', NULL, 'issues', 'Issue activity', FALSE, NULL),
42 ('platform', NULL, 'status', 'Platform status', FALSE, NULL),
43 ('platform', NULL, 'tip', 'Tips', FALSE, NULL),
44 ('platform', NULL, 'login', 'Sign-in alerts', TRUE, NULL)
45 ON CONFLICT DO NOTHING;
46
47 -- Backfill one subscription per user per preference, carrying the column's
48 -- current value. A false column becomes 'unsubscribed' rather than an absent
49 -- row: the difference between "said no" and "never asked" is exactly what the
50 -- consent log exists to record, and an absent row would read as the latter.
51 INSERT INTO list_subscriptions (list_id, user_id, state, source, created_at, unsubscribed_at)
52 SELECT l.id,
53 u.id,
54 CASE WHEN pref.enabled THEN 'confirmed' ELSE 'unsubscribed' END,
55 'import',
56 u.created_at,
57 CASE WHEN pref.enabled THEN NULL ELSE NOW() END
58 FROM users u
59 CROSS JOIN LATERAL (VALUES
60 ('sale', u.notify_sale),
61 ('follower', u.notify_follower),
62 ('releases', u.notify_release),
63 ('issues', u.notify_issues),
64 ('status', u.notify_status),
65 ('tip', u.notify_tip),
66 ('login', u.login_notification_enabled)
67 ) AS pref(kind, enabled)
68 JOIN lists l ON l.scope = 'platform' AND l.kind = pref.kind
69 ON CONFLICT DO NOTHING;
70
71 -- Provenance, same honesty rule as the step-2 backfill: these came from a
72 -- column whose history nobody kept, so the event says import rather than
73 -- claiming an opt-in with a date we do not have.
74 INSERT INTO consent_events (subscription_id, event, at, evidence)
75 SELECT ls.id,
76 'import',
77 ls.created_at,
78 'Backfilled from the users.notify_* column of the same name. The columns '
79 || 'recorded only the current value, so the date is the account''s and no '
80 || 'record of the original choice survives.'
81 FROM list_subscriptions ls
82 JOIN lists l ON l.id = ls.list_id
83 WHERE l.scope = 'platform'
84 AND l.kind IN ('sale', 'follower', 'releases', 'issues', 'status', 'tip', 'login')
85 AND ls.source = 'import'
86 AND NOT EXISTS (
87 SELECT 1 FROM consent_events ce
88 WHERE ce.subscription_id = ls.id AND ce.event = 'import'
89 );
90