-- Abuse reports from the /abuse form, abuse@ mail and the per-site report endpoint. One queue for admins;
-- reporter_hash is an HMAC of the reporter IP or sender address so repeat reports count once per reporter.
CREATE TABLE abuse_reports (
id TEXT PRIMARY KEY,
source TEXT NOT NULL CHECK (source IN ('web', 'email', 'site')),
target_url TEXT NOT NULL,
site_id TEXT REFERENCES sites(id) ON DELETE SET NULL,
category TEXT NOT NULL,
details TEXT NOT NULL DEFAULT '',
reporter_email TEXT,
reporter_hash TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'actioned', 'dismissed')),
resolution TEXT,
resolved_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
resolved_at INTEGER,
created_at INTEGER NOT NULL
);
CREATE INDEX abuse_reports_status ON abuse_reports(status, created_at);
CREATE INDEX abuse_reports_site ON abuse_reports(site_id, status, reporter_hash);
-- Set while a site is held for abuse review, so restoring clears only abuse pauses and never other disables.
ALTER TABLE sites ADD COLUMN paused_at INTEGER;
INSERT INTO abuse_reports (id, source, target_url, site_id, category, details, reporter_hash, created_at)
SELECT r.id, 'site', s.label, r.site_id, 'other', r.reason, r.reporter_ip_hash, r.created_at
FROM site_abuse_reports r JOIN sites s ON s.id = r.site_id;
UPDATE sites SET paused_at = updated_at WHERE disabled_reason = 'Disabled after abuse reports';