max / makenotwork

3.8 KB · 71 lines History Blame Raw

1	-- Who was mailing, when an address bounces or complains.
2	--
3	-- `email_suppressions` records `(email, reason)` and is keyed on the address
4	-- alone. It says an address complained; it cannot say what it complained
5	-- about. So no complaint rate is computable at any granularity, and complaint
6	-- rate is the number a mail provider actually judges an account on -- the one
7	-- that predicts reputation damage before the shared Postmark IP pool feels it.
8	--
9	-- Recorded at the finest grain: the send, carrying its list and its creator,
10	-- with the per-list and per-creator rates derived from it. A coarser grain
11	-- forecloses the other two and saves nothing, because the attribution work is
12	-- identical either way (`93f23f00`, settled 2026-08-30).
13
14	-- One row per fan-out. This is the denominator: a rate needs both halves, and
15	-- nothing recorded how much mail a list send put on the wire.
16	--
17	-- `creator_mail_usage` counts what a creator sent, which answers the cap's
18	-- question and not this one -- it is a running counter per billing period, so
19	-- it cannot be windowed to a fortnight and knows nothing about lists.
20	--
21	-- `recipients` is what was handed to the fan-out, not what Postmark accepted.
22	-- The two differ by whatever failed in flight, and the honest denominator for
23	-- "how many complained about this send" is how many were mailed.
24	CREATE TABLE IF NOT EXISTS email_sends (
25	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
26	creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
27	list_id UUID REFERENCES lists(id) ON DELETE SET NULL,
28	kind TEXT NOT NULL,
29	recipients BIGINT NOT NULL CHECK (recipients >= 0),
30	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
31	);
32
33	-- Both rate queries scan a window and group, so the window is the leading
34	-- column in each.
35	CREATE INDEX IF NOT EXISTS idx_email_sends_creator
36	ON email_sends (creator_id, created_at DESC);
37	CREATE INDEX IF NOT EXISTS idx_email_sends_list
38	ON email_sends (list_id, created_at DESC) WHERE list_id IS NOT NULL;
39
40	-- The numerator: one row per bounce or complaint, attributed to the send it
41	-- came from.
42	--
43	-- A table of its own rather than columns on `email_suppressions`, and the
44	-- reason is that table's own shape: it is unique on the address and written
45	-- `ON CONFLICT DO NOTHING`, so a second complaint from an address already
46	-- suppressed records nothing at all. That is right for a suppression list --
47	-- the address is already off -- and wrong for a rate, which needs every event.
48	--
49	-- `send_id` is nullable and stays that way. Transactional mail carries no send,
50	-- Postmark's metadata does not come back on every record type, and an
51	-- unattributed complaint is still worth counting: dropping it would flatter the
52	-- rate, which is the wrong direction for a number that exists to warn.
53	CREATE TABLE IF NOT EXISTS email_incidents (
54	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
55	send_id UUID REFERENCES email_sends(id) ON DELETE SET NULL,
56	creator_id UUID REFERENCES users(id) ON DELETE CASCADE,
57	list_id UUID REFERENCES lists(id) ON DELETE SET NULL,
58	email TEXT NOT NULL,
59	kind TEXT NOT NULL,
60	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
61	);
62
63	-- Denormalised beside `send_id` on purpose. A send row can be deleted with its
64	-- creator's account or lose its list, and the incident still has to count
65	-- against the window it happened in; joining through a row that may be gone
66	-- would silently shrink the numerator.
67	CREATE INDEX IF NOT EXISTS idx_email_incidents_creator
68	ON email_incidents (creator_id, created_at DESC) WHERE creator_id IS NOT NULL;
69	CREATE INDEX IF NOT EXISTS idx_email_incidents_list
70	ON email_incidents (list_id, created_at DESC) WHERE list_id IS NOT NULL;
71