| 1 | -- Private security reports (lib/security-reports.ts): what people send through /security/report, the |
| 2 | -- contact security.txt names first. Only site administrators can read them. A report is new, triaged |
| 3 | -- or resolved; the audit log keeps every change. |
| 4 | CREATE TABLE IF NOT EXISTS security_reports ( |
| 5 | id TEXT PRIMARY KEY, |
| 6 | summary TEXT NOT NULL, |
| 7 | details TEXT NOT NULL, |
| 8 | area TEXT, -- the affected URL or area, when given |
| 9 | reply_to TEXT, -- where to answer: the address given, or the reporter's own |
| 10 | account_id TEXT, -- the reporter, when signed in |
| 11 | reporter_login TEXT, |
| 12 | ip_hash TEXT, -- SHA-256 of the sender's IP address, for the daily limits only |
| 13 | state TEXT NOT NULL DEFAULT 'new' CHECK (state IN ('new', 'triaged', 'resolved')), |
| 14 | note TEXT, -- the administrator's latest note |
| 15 | updated_by TEXT, -- the administrator's login |
| 16 | updated_at TEXT, |
| 17 | created_at TEXT NOT NULL |
| 18 | ); |
| 19 | CREATE INDEX IF NOT EXISTS security_reports_at ON security_reports(created_at); |
| 20 | CREATE INDEX IF NOT EXISTS security_reports_ip ON security_reports(ip_hash, created_at); |
| 21 | CREATE INDEX IF NOT EXISTS security_reports_account ON security_reports(account_id, created_at); |