20 lines
939 B
SQL
20 lines
939 B
SQL
-- Post reports — a viewer flags a specific post (not its author) with a
|
|
-- category + optional free-text reason. Mirrors user_reports but keyed to
|
|
-- a post; resolved_at is one-way (admins clear it later). The partial
|
|
-- index keeps the open-reports queue cheap to list.
|
|
CREATE TABLE IF NOT EXISTS post_reports (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
category VARCHAR(32) NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
resolved_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS post_reports_post_idx
|
|
ON post_reports (post_id, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS post_reports_unresolved_idx
|
|
ON post_reports (created_at DESC) WHERE resolved_at IS NULL;
|