max / makenotwork

970 B · 23 lines History Blame Raw

1	-- Migration 129: pending_s3_deletions dead-letter table
2	--
3	-- Audit Run #3 (Storage SERIOUS): `pending_s3_deletions.attempts` was
4	-- incremented forever on a permanently-failing key (malformed s3_key, gone
5	-- bucket, ACL gap), generating retry noise and crowding out real deletions.
6	-- The dead-letter table moves rows that exceed MAX_ATTEMPTS off the hot
7	-- queue into an operator-visible parking lot so the worker can keep making
8	-- progress on the legitimate backlog. Rows here require manual triage.
9
10	CREATE TABLE IF NOT EXISTS pending_s3_deletions_dead_letter (
11	id UUID PRIMARY KEY,
12	s3_key TEXT NOT NULL,
13	bucket TEXT NOT NULL,
14	source TEXT NOT NULL,
15	created_at TIMESTAMPTZ NOT NULL,
16	attempts INT NOT NULL,
17	last_attempted_at TIMESTAMPTZ,
18	dead_lettered_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
19	);
20
21	CREATE INDEX IF NOT EXISTS idx_pending_s3_deletions_dead_letter_dead_lettered_at
22	ON pending_s3_deletions_dead_letter(dead_lettered_at);
23