internal/db/migrations/028_create_admin_reports_table.sql at main

A community based topic aggregation platform built on atproto

coves / internal / db / migrations / 028_create_admin_reports_table.sql

at main 23 lines 985 B view raw

wrap content

 1-- +goose Up
 2CREATE TABLE admin_reports (
 3    id BIGSERIAL PRIMARY KEY,
 4    reporter_did TEXT NOT NULL,
 5    target_uri TEXT NOT NULL,           -- AT-URI of post/comment
 6    target_type TEXT NOT NULL,          -- 'post' or 'comment'
 7    reason TEXT NOT NULL,               -- csam, doxing, harassment, spam, illegal, other
 8    explanation TEXT,                   -- optional details (max 1000 chars)
 9    status TEXT NOT NULL DEFAULT 'open',
10    resolved_by TEXT,
11    resolution_notes TEXT,
12    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
13    resolved_at TIMESTAMPTZ,
14
15    CONSTRAINT valid_reason CHECK (reason IN ('csam', 'doxing', 'harassment', 'spam', 'illegal', 'other')),
16    CONSTRAINT valid_status CHECK (status IN ('open', 'reviewing', 'resolved', 'dismissed'))
17);
18
19CREATE INDEX idx_admin_reports_status_created ON admin_reports(status, created_at DESC);
20CREATE INDEX idx_admin_reports_target ON admin_reports(target_uri);
21
22-- +goose Down
23DROP TABLE IF EXISTS admin_reports;