0023_abuse_reports.sql43 lines · main

1	-- 0023_abuse_reports — dedicated table; replaces the audit-log overload
2	-- that services/abuse.ts used pre-cleanup. audit_logs still receives
3	-- one row per state transition for the security audit perspective,
4	-- but the dashboard's abuse-triage list now reads this table directly.
5	-- Historical abuse rows in audit_logs are preserved for forensics;
6	-- a one-off backfill into abuse_reports can be done later if needed.
7
8	CREATE TABLE IF NOT EXISTS "abuse_reports" (
9	"id" text PRIMARY KEY NOT NULL,
10	"target_url" text NOT NULL,
11	"reason" text NOT NULL,
12	"severity" text NOT NULL,
13	"reporter_contact" text,
14	"source_ip_hash" text,
15	"source_user_agent" text,
16	"status" text DEFAULT 'open' NOT NULL,
17	"resolution" text,
18	"project_id" text,
19	"triaged_at" timestamp with time zone,
20	"triaged_by" text,
21	"triage_notes" text,
22	"resolved_at" timestamp with time zone,
23	"resolved_by" text,
24	"resolve_notes" text,
25	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
26	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
27	CONSTRAINT "abuse_reports_project_id_fk"
28	FOREIGN KEY ("project_id") REFERENCES "projects"("id") ON DELETE SET NULL,
29	CONSTRAINT "abuse_reports_triaged_by_fk"
30	FOREIGN KEY ("triaged_by") REFERENCES "users"("id") ON DELETE SET NULL,
31	CONSTRAINT "abuse_reports_resolved_by_fk"
32	FOREIGN KEY ("resolved_by") REFERENCES "users"("id") ON DELETE SET NULL
33	);
34
35	CREATE INDEX IF NOT EXISTS "abuse_reports_status_idx"
36	ON "abuse_reports" USING btree ("status", "created_at");
37
38	CREATE INDEX IF NOT EXISTS "abuse_reports_severity_idx"
39	ON "abuse_reports" USING btree ("severity", "created_at");
40
41	CREATE INDEX IF NOT EXISTS "abuse_reports_project_idx"
42	ON "abuse_reports" USING btree ("project_id")
43	WHERE "project_id" IS NOT NULL;