max / makenotwork

2.4 KB · 50 lines History Blame Raw

1	-- Fail-closed "held gate" for CDN-served images (audit 2026-07-01).
2	--
3	-- Four scan-target kinds render straight from cdn.makenot.work/{key} with no
4	-- per-request gate and no scan_status column: item covers, project covers, the
5	-- item/project gallery carousels, and content-insertion clips. Before this
6	-- migration they rendered during the pre-verdict window, and a HeldForReview
7	-- verdict left them serving indefinitely (only a hard Quarantined verdict
8	-- removed them, by deleting the row). Add a per-row scan status, default
9	-- 'pending', that the scan worker flips to 'clean' or 'held' keyed on s3_key;
10	-- rendering is gated to 'clean' only, so 'pending' and 'held' rows stay hidden.
11	--
12	-- Existing rows predate the gate, so backfill them to 'clean' to keep every
13	-- currently-serving image visible. New rows start 'pending' and only render
14	-- once the pipeline clears them.
15
16	ALTER TABLE item_images
17	ADD COLUMN IF NOT EXISTS scan_status TEXT NOT NULL DEFAULT 'pending';
18	ALTER TABLE project_images
19	ADD COLUMN IF NOT EXISTS scan_status TEXT NOT NULL DEFAULT 'pending';
20	ALTER TABLE content_insertions
21	ADD COLUMN IF NOT EXISTS scan_status TEXT NOT NULL DEFAULT 'pending';
22
23	ALTER TABLE items
24	ADD COLUMN IF NOT EXISTS cover_scan_status TEXT NOT NULL DEFAULT 'pending';
25	ALTER TABLE projects
26	ADD COLUMN IF NOT EXISTS cover_scan_status TEXT NOT NULL DEFAULT 'pending';
27
28	-- Backfill: every existing gallery / insertion row predates the gate, so mark
29	-- the whole table 'clean' (nothing is mid-scan at migration time).
30	UPDATE item_images SET scan_status = 'clean';
31	UPDATE project_images SET scan_status = 'clean';
32	UPDATE content_insertions SET scan_status = 'clean';
33
34	-- Covers live on the items/projects row: only clear rows that actually have a
35	-- cover. Rows with no cover keep 'pending' harmlessly (there is nothing to
36	-- render, and a future cover upload re-enters the gate).
37	UPDATE items
38	SET cover_scan_status = 'clean'
39	WHERE cover_s3_key IS NOT NULL OR cover_image_url IS NOT NULL;
40	UPDATE projects
41	SET cover_scan_status = 'clean'
42	WHERE cover_s3_key IS NOT NULL OR cover_image_url IS NOT NULL;
43
44	-- Partial indexes matching the render reads: the gallery lists select clean
45	-- rows for a parent in display order.
46	CREATE INDEX IF NOT EXISTS idx_item_images_item_clean
47	ON item_images(item_id, position) WHERE scan_status = 'clean';
48	CREATE INDEX IF NOT EXISTS idx_project_images_project_clean
49	ON project_images(project_id, position) WHERE scan_status = 'clean';
50