max / makenotwork

4.0 KB · 75 lines History Blame Raw

1	-- A soft monthly ceiling on how much mail one creator sends.
2	--
3	-- The commons being protected is the shared Postmark IP pool: one creator's
4	-- fan-out degrades delivery for every other creator on it. Nothing bounded that
5	-- before this. `db::lists::resolve_audience` caps one audience at 10,000 and
6	-- `users.last_broadcast_at` allows one broadcast per 24 hours; neither bounds
7	-- the count over a month, which is the number reputation actually follows.
8	--
9	-- Soft, not hard, and the shape matters as much as the number: the creator can
10	-- see the count and the cap before meeting either, a send that would cross it is
11	-- refused with a message rather than silently swallowed, and an increase is
12	-- something they can ask for. A silent throttle reads as the platform losing
13	-- mail, which is worse than a refusal.
14
15	-- One row per creator per billing period. The window is the creator's own
16	-- payment period rather than a calendar month, so the cap is legible next to
17	-- what they pay for; `period_start` is what makes the row, and it comes from
18	-- `creator_subscriptions.current_period_start` when there is one and from the
19	-- calendar month when there is not (an unsubscribed creator still sends).
20	--
21	-- `sent_count` is reserved up front, per send, for the whole audience: the
22	-- decision has to be made before any mail leaves, and a per-mail increment would
23	-- be 8,000 writes for one announcement and would leave a half-mailed list when
24	-- it hit the cap mid-fan-out.
25	CREATE TABLE IF NOT EXISTS creator_mail_usage (
26	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
27	period_start TIMESTAMPTZ NOT NULL,
28	period_end TIMESTAMPTZ NOT NULL,
29	sent_count BIGINT NOT NULL DEFAULT 0 CHECK (sent_count >= 0),
30	updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
31	PRIMARY KEY (user_id, period_start)
32	);
33
34	-- The per-account override, above whatever the creator's tier defaults to.
35	-- NULL means "the tier default applies", which is every account until an
36	-- operator grants an increase. Nullable rather than defaulted to the tier
37	-- number: a default would freeze today's number onto the row and stop a config
38	-- change from reaching accounts that never asked for anything.
39	ALTER TABLE users
40	ADD COLUMN monthly_mail_cap_override INTEGER
41	CHECK (monthly_mail_cap_override IS NULL OR monthly_mail_cap_override > 0);
42
43	-- The application path. A creator asks for a number and says why; the
44	-- explanation is the point, since it is what separates a growing list from a
45	-- stale one being blasted.
46	--
47	-- `status` is a closed set enforced app-side ('pending', 'granted', 'denied'),
48	-- stored as TEXT for the same reason `admin_alerts` does it: extending the set
49	-- should be a code change and not a migration.
50	CREATE TABLE IF NOT EXISTS mail_cap_requests (
51	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
52	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
53	requested_cap INTEGER NOT NULL CHECK (requested_cap > 0),
54	reason TEXT NOT NULL,
55	status TEXT NOT NULL DEFAULT 'pending',
56	granted_cap INTEGER CHECK (granted_cap IS NULL OR granted_cap > 0),
57	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
58	decided_at TIMESTAMPTZ,
59	decided_by UUID REFERENCES users(id) ON DELETE SET NULL
60	);
61
62	-- The operator queue is "pending, oldest first"; the creator's own view is
63	-- "mine, newest first". One index serves the first, the PK-less user lookup the
64	-- second.
65	CREATE INDEX IF NOT EXISTS idx_mail_cap_requests_pending
66	ON mail_cap_requests (created_at) WHERE status = 'pending';
67	CREATE INDEX IF NOT EXISTS idx_mail_cap_requests_user
68	ON mail_cap_requests (user_id, created_at DESC);
69
70	-- One open request at a time. A creator who asks twice is amending their ask,
71	-- not queueing a second one, and an operator granting one of two identical
72	-- pending rows leaves the other to be granted again later.
73	CREATE UNIQUE INDEX IF NOT EXISTS idx_mail_cap_requests_one_open
74	ON mail_cap_requests (user_id) WHERE status = 'pending';
75