-- Abuse reports can target forge objects (repos, users, issues, comments, Pages projects), not only hosted sites. -- reporter_weight is how much a report counts toward auto-pause (REPORTER_WEIGHT in src/server/abuse.ts): signed-in -- users 2, DMARC-aligned email domains 2, anonymous IPs 1, unauthenticated email 0 (kept for review, never pauses -- anything). Rows from before this migration keep the default 1, the IP weight. That slightly over-counts any -- legacy unauthenticated-email rows (now 0) and under-counts signed-in ones (now 2). Production almost certainly had -- no such rows, so no backfill is done; only open reports from the last seven days affect auto-pause anyway. ALTER TABLE abuse_reports ADD COLUMN target_kind TEXT; ALTER TABLE abuse_reports ADD COLUMN target_id TEXT; ALTER TABLE abuse_reports ADD COLUMN reporter_weight REAL NOT NULL DEFAULT 1; UPDATE abuse_reports SET target_kind = 'site', target_id = site_id WHERE site_id IS NOT NULL; CREATE INDEX abuse_reports_target ON abuse_reports(target_kind, target_id, status); -- Moderation: a hidden repo, issue or comment is invisible to everyone but site admins; a taken-down Pages -- project stops serving until restored. ALTER TABLE repos ADD COLUMN hidden_at INTEGER; ALTER TABLE issues ADD COLUMN hidden_at INTEGER; ALTER TABLE comments ADD COLUMN hidden_at INTEGER; ALTER TABLE pages_sites ADD COLUMN taken_down_at INTEGER; -- Every site-admin action, append-only. CREATE TABLE admin_audit_log ( id TEXT PRIMARY KEY, actor_id TEXT REFERENCES accounts(id) ON DELETE SET NULL, action TEXT NOT NULL, target_kind TEXT NOT NULL, target_id TEXT NOT NULL, reason TEXT, created_at INTEGER NOT NULL ); CREATE INDEX admin_audit_log_created ON admin_audit_log(created_at DESC); CREATE INDEX admin_audit_log_target ON admin_audit_log(target_kind, target_id, created_at DESC);