max / makenotwork

3.0 KB · 63 lines History Blame Raw

1	-- Alerts that repeat until a human says they read them.
2	--
3	-- Some alerts must be seen, not merely sent. The first case: a creator's Stripe
4	-- default_currency changes, so every price they have set is now a number in a
5	-- different currency, and only they can decide the new numbers. 194's handler
6	-- files a wam ticket for us and says nothing to the creator, because there was
7	-- no mechanism for a message that keeps asking. This is that mechanism.
8	--
9	-- One open row per (user, kind, dedup_key). The uniqueness is partial on
10	-- acknowledged_at IS NULL, so a Stripe account whose currency flaps produces
11	-- one open row rather than a pile, and an acknowledged one does not block the
12	-- next genuine occurrence of the same thing.
13	CREATE TABLE IF NOT EXISTS pending_acknowledgements (
14	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
15	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16
17	-- What happened. Read back as db::acknowledgements::AckKind, whose ALL is
18	-- asserted against this constraint by a test, the same shape lists uses.
19	kind TEXT NOT NULL CHECK (kind IN ('settlement_currency_changed')),
20
21	-- What distinguishes two occurrences of the same kind for the same user.
22	-- The caller decides; for a currency change it is the pair of currencies,
23	-- so a second, different change opens a second row while Stripe restating
24	-- the same one does not.
25	dedup_key TEXT NOT NULL,
26
27	-- Whatever the page needs to say what happened. Structured rather than
28	-- pre-rendered prose so the copy can be fixed without a migration.
29	details JSONB NOT NULL DEFAULT '{}'::jsonb,
30
31	-- SHA-256 of the token in the link, never the token. Same shape as
32	-- password_reset_tokens. Minted once and stable for the life of the row, so
33	-- the link in the first email still works when the fourth arrives.
34	token_hash VARCHAR(64) NOT NULL,
35
36	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
37
38	-- NULL means never sent, which is due now. The row is opened by the caller
39	-- and every send comes from the scheduler, so the first message and the
40	-- repeats travel the same path and cannot drift apart.
41	last_notified_at TIMESTAMPTZ,
42	notify_count INTEGER NOT NULL DEFAULT 0,
43
44	-- Set when a person was asked to step in. Sending stops here: a loop that
45	-- runs for a year unattended is not an alert, it is a filter rule.
46	escalated_at TIMESTAMPTZ,
47
48	acknowledged_at TIMESTAMPTZ
49	);
50
51	-- One open row per thing. Partial, so acknowledged history accumulates freely.
52	CREATE UNIQUE INDEX IF NOT EXISTS idx_pending_ack_open
53	ON pending_acknowledgements(user_id, kind, dedup_key)
54	WHERE acknowledged_at IS NULL;
55
56	CREATE UNIQUE INDEX IF NOT EXISTS idx_pending_ack_token
57	ON pending_acknowledgements(token_hash);
58
59	-- The sweep's query: open, not escalated, ordered by when it was last sent.
60	CREATE INDEX IF NOT EXISTS idx_pending_ack_due
61	ON pending_acknowledgements(last_notified_at NULLS FIRST)
62	WHERE acknowledged_at IS NULL AND escalated_at IS NULL;
63