steid

steid/migrations/20260906120302_create_webhook_deliveries.sql

1	-- Webhook deliveries: one row per attempt, not per delivery.
2	--
3	-- When a hook is broken the useful question is "what did it say, and how many times did
4	-- we try". Collapsing retries into one final state throws away the only evidence of
5	-- what went wrong, so each attempt is its own row and `attempt` counts from 1.
6	--
7	-- `status_code` is null when the request never got one — DNS, a refused connection,
8	-- TLS, or the timeout. `ok` is derived from the status when the row is written rather
9	-- than at read time, so no future read can forget the rule.
10	create table webhook_deliveries (
11	id text primary key,
12	webhook_id text not null references webhooks (id) on delete cascade,
13	event_id text not null references push_events (id) on delete cascade,
14	attempt integer not null,
15	status_code integer,
16	-- Capped in the domain before it gets here; a hostile endpoint must not be able to
17	-- write a megabyte per attempt.
18	response text not null,
19	duration_ms integer not null,
20	delivered_at integer not null,
21	ok integer not null
22	);
23
24	-- The deliveries page: one hook's attempts, newest first.
25	create index webhook_deliveries_webhook_id
26	on webhook_deliveries (webhook_id, delivered_at desc);