max / makenotwork

1.5 KB · 33 lines History Blame Raw

1	-- Audit log of admin scan-pipeline actions.
2	--
3	-- Every promote / quarantine / rescan that an admin performs from the
4	-- /admin/uploads dashboard writes a row here. The dashboard surfaces the
5	-- last action inline on each held row; the full log lives at
6	-- /admin/uploads/audit (Phase 2 of `docs/scan-pipeline-audit.md`).
7	--
8	-- One of `version_id` or `item_id` is populated to identify the target;
9	-- bulk operations write one row per affected target (no separate "bulk"
10	-- target_kind). `action` covers both per-row and bulk variants so we can
11	-- distinguish a single promote from one issued via bulk-promote when
12	-- reviewing intent later.
13
14	CREATE TABLE scan_admin_actions (
15	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
16	version_id UUID REFERENCES versions(id) ON DELETE SET NULL,
17	item_id UUID REFERENCES items(id) ON DELETE SET NULL,
18	admin_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
19	action TEXT NOT NULL CHECK (action IN (
20	'promote', 'quarantine', 'rescan',
21	'bulk_promote', 'bulk_rescan'
22	)),
23	prev_status TEXT,
24	new_status TEXT,
25	note TEXT,
26	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
27	);
28
29	CREATE INDEX idx_scan_admin_actions_version ON scan_admin_actions(version_id) WHERE version_id IS NOT NULL;
30	CREATE INDEX idx_scan_admin_actions_item ON scan_admin_actions(item_id) WHERE item_id IS NOT NULL;
31	CREATE INDEX idx_scan_admin_actions_admin ON scan_admin_actions(admin_id, created_at DESC);
32	CREATE INDEX idx_scan_admin_actions_created ON scan_admin_actions(created_at DESC);
33