Skip to main content

max / makenotwork

8.9 KB · 191 lines History Blame Raw
1 -- One subscription model for every list-like thing.
2 --
3 -- Step 2 of wiki [[mnw-mailing-lists]]. Today five mechanisms decide who gets
4 -- email about what and none knows about the others: project mailing_lists,
5 -- email_signups, six users.notify_* bools, follows, and email_suppressions.
6 -- Same act, different standards of care. These three tables are the one model
7 -- the rest collapses into.
8 --
9 -- Nothing reads them yet. Step 3 routes sends through a resolver, step 4 builds
10 -- the unsubscribe surface, step 5 migrates users.notify_*. The old tables stay
11 -- authoritative until then, so this migration is additive and reversible by
12 -- dropping what it creates.
13
14 -- A list is anything somebody can be subscribed to.
15 CREATE TABLE IF NOT EXISTS lists (
16 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
17 scope TEXT NOT NULL CHECK (scope IN ('platform', 'project', 'repo', 'creator')),
18 -- NULL exactly when the list is platform-wide. The CHECK ties the two
19 -- together so a project list cannot exist without a project, and a platform
20 -- list cannot accidentally acquire one.
21 scope_id UUID,
22 kind TEXT NOT NULL CHECK (kind IN (
23 'content', 'devlog', 'patches', 'releases',
24 'issues', 'announce', 'marketing'
25 )),
26 title TEXT NOT NULL,
27 -- Transactional lists nobody may leave: receipts, security, account
28 -- lifecycle. This is a property of the LIST rather than of the sender, and
29 -- it is what structurally keeps a marketing line out of a receipt: if the
30 -- content is marketing it goes on a marketing list, which is opt-out-able
31 -- by construction.
32 required BOOLEAN NOT NULL DEFAULT FALSE,
33 owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
34 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
35 CONSTRAINT chk_lists_scope_id CHECK ((scope = 'platform') = (scope_id IS NULL))
36 );
37
38 -- One list per (scope, scope_id, kind). Split in two because scope_id is NULL
39 -- for platform lists and NULL never equals NULL in a unique index.
40 CREATE UNIQUE INDEX IF NOT EXISTS idx_lists_scoped
41 ON lists (scope, scope_id, kind) WHERE scope_id IS NOT NULL;
42 CREATE UNIQUE INDEX IF NOT EXISTS idx_lists_platform
43 ON lists (kind) WHERE scope_id IS NULL;
44
45 -- A subscription is one recipient's relationship to one list.
46 CREATE TABLE IF NOT EXISTS list_subscriptions (
47 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
48 list_id UUID NOT NULL REFERENCES lists(id) ON DELETE CASCADE,
49 -- Exactly one of these. A subscriber is either an account or a bare
50 -- address; the old mailing_list_subscribers allowed both at once, which
51 -- left "which one is authoritative" to whoever read the row next.
52 user_id UUID REFERENCES users(id) ON DELETE CASCADE,
53 email TEXT,
54 -- 'imported' is the honest state for everything this migration backfills.
55 -- Those rows predate any consent record, so calling them 'confirmed' would
56 -- manufacture evidence we do not have, and calling them 'pending' would
57 -- assert they are mid-double-opt-in, which they are not. Whether an
58 -- imported subscriber may be mailed is a live decision (GoingsOn
59 -- 04a882b4); until it is answered, the state says exactly what we know.
60 state TEXT NOT NULL CHECK (state IN (
61 'pending', 'confirmed', 'imported', 'unsubscribed', 'bounced'
62 )),
63 source TEXT NOT NULL CHECK (source IN (
64 'landing_form', 'project_page', 'checkout',
65 'import', 'admin', 'api'
66 )),
67 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
68 confirmed_at TIMESTAMPTZ,
69 unsubscribed_at TIMESTAMPTZ,
70 CONSTRAINT chk_subscription_identity CHECK ((user_id IS NULL) <> (email IS NULL))
71 );
72
73 CREATE UNIQUE INDEX IF NOT EXISTS idx_list_subscriptions_user
74 ON list_subscriptions (list_id, user_id) WHERE user_id IS NOT NULL;
75 CREATE UNIQUE INDEX IF NOT EXISTS idx_list_subscriptions_email
76 ON list_subscriptions (list_id, email) WHERE email IS NOT NULL;
77 -- The query a send draws from: one list, the states that may receive mail.
78 CREATE INDEX IF NOT EXISTS idx_list_subscriptions_sendable
79 ON list_subscriptions (list_id, state);
80
81 -- Why we believe we are allowed to mail this person. Append-only.
82 CREATE TABLE IF NOT EXISTS consent_events (
83 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
84 subscription_id UUID NOT NULL REFERENCES list_subscriptions(id) ON DELETE CASCADE,
85 event TEXT NOT NULL CHECK (event IN (
86 'opt_in', 'confirm', 'opt_out', 'bounce',
87 'complaint', 'admin_removal', 'import'
88 )),
89 at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
90 ip INET,
91 user_agent TEXT,
92 -- The exact copy shown at opt-in, or the provenance of an import. This is
93 -- the column that makes consent evidenceable rather than asserted: without
94 -- it we can say someone subscribed but not what they were told they were
95 -- subscribing to.
96 evidence TEXT
97 );
98
99 CREATE INDEX IF NOT EXISTS idx_consent_events_subscription
100 ON consent_events (subscription_id, at DESC);
101
102 -- Append-only, enforced rather than documented. An unsubscribe adds an opt_out
103 -- row; it never edits the opt_in that came before, because the record of what
104 -- was agreed to is the whole point of the table.
105 --
106 -- DELETE is deliberately allowed: erasure cascades from list_subscriptions, and
107 -- a right-to-erasure request has to be able to remove the person.
108 CREATE OR REPLACE FUNCTION consent_events_no_update() RETURNS TRIGGER AS $$
109 BEGIN
110 RAISE EXCEPTION 'consent_events is append-only; record a new event instead';
111 END;
112 $$ LANGUAGE plpgsql;
113
114 DROP TRIGGER IF EXISTS trg_consent_events_no_update ON consent_events;
115 CREATE TRIGGER trg_consent_events_no_update
116 BEFORE UPDATE ON consent_events
117 FOR EACH ROW EXECUTE FUNCTION consent_events_no_update();
118
119 -- ── Backfill ──
120 --
121 -- Every existing subscriber lands as 'imported' with an import consent event
122 -- stating where it came from. None of these rows carries evidence of an opt-in,
123 -- and the backfill says so rather than inventing one.
124
125 -- Project mailing lists.
126 INSERT INTO lists (scope, scope_id, kind, title, required, owner_id)
127 SELECT 'project', ml.project_id, ml.list_type, ml.name, FALSE, p.user_id
128 FROM mailing_lists ml
129 JOIN projects p ON p.id = ml.project_id
130 ON CONFLICT DO NOTHING;
131
132 -- The platform marketing list behind the landing "notify me" form.
133 INSERT INTO lists (scope, scope_id, kind, title, required, owner_id)
134 VALUES ('platform', NULL, 'marketing', 'Makenotwork updates', FALSE, NULL)
135 ON CONFLICT DO NOTHING;
136
137 -- Project subscribers. user_id wins where a legacy row carried both, since an
138 -- account is the stronger identity and the new CHECK permits only one.
139 INSERT INTO list_subscriptions (list_id, user_id, email, state, source, created_at)
140 SELECT l.id,
141 mls.user_id,
142 CASE WHEN mls.user_id IS NULL THEN LOWER(mls.email) END,
143 'imported',
144 'import',
145 mls.subscribed_at
146 FROM mailing_list_subscribers mls
147 JOIN mailing_lists ml ON ml.id = mls.list_id
148 JOIN lists l ON l.scope = 'project' AND l.scope_id = ml.project_id AND l.kind = ml.list_type
149 ON CONFLICT DO NOTHING;
150
151 -- Landing signups. An already-unsubscribed row stays unsubscribed: the opt-out
152 -- is the one piece of consent history this table does have, and losing it in
153 -- the migration would re-subscribe people who asked to leave.
154 INSERT INTO list_subscriptions (list_id, email, state, source, created_at, unsubscribed_at)
155 SELECT l.id,
156 LOWER(es.email),
157 CASE WHEN es.unsubscribed_at IS NULL THEN 'imported' ELSE 'unsubscribed' END,
158 'import',
159 es.created_at,
160 es.unsubscribed_at
161 FROM email_signups es
162 CROSS JOIN lists l
163 WHERE l.scope = 'platform' AND l.kind = 'marketing'
164 ON CONFLICT DO NOTHING;
165
166 -- One import event per backfilled subscription, naming the table it came from
167 -- so a later re-confirmation pass can tell the two provenances apart. The
168 -- project rows came from a subscribe action of unknown shape (the old table
169 -- kept no source, so a page subscribe and a creator's CSV import are
170 -- indistinguishable); the signup rows came from the landing form.
171 INSERT INTO consent_events (subscription_id, event, at, evidence)
172 SELECT ls.id,
173 'import',
174 ls.created_at,
175 CASE l.scope
176 WHEN 'platform' THEN
177 'Backfilled from email_signups (landing form, single opt-in, '
178 || 'no record of the copy shown at signup).'
179 ELSE
180 'Backfilled from mailing_list_subscribers. The source table kept '
181 || 'no provenance, so a page subscribe and a creator CSV import '
182 || 'are indistinguishable in this row.'
183 END
184 FROM list_subscriptions ls
185 JOIN lists l ON l.id = ls.list_id
186 WHERE ls.source = 'import'
187 AND NOT EXISTS (
188 SELECT 1 FROM consent_events ce
189 WHERE ce.subscription_id = ls.id AND ce.event = 'import'
190 );
191