mirror of https://github.com/synctv-org/synctv
You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
48 lines
2.2 KiB
SQL
48 lines
2.2 KiB
SQL
CREATE TABLE IF NOT EXISTS content_reports (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
reporter_user_id BIGINT NOT NULL,
|
|
room_id BIGINT,
|
|
target_type SMALLINT NOT NULL,
|
|
target_room_id BIGINT,
|
|
target_user_id BIGINT,
|
|
target_member_room_id BIGINT,
|
|
target_member_user_id BIGINT,
|
|
target_chat_message_id BIGINT,
|
|
target_chat_message_created_at TIMESTAMPTZ,
|
|
reason_code TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
metadata JSONB,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
reviewed_by BIGINT,
|
|
reviewed_at TIMESTAMPTZ,
|
|
resolution_note TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (metadata IS NULL OR jsonb_typeof(metadata) = 'object'),
|
|
FOREIGN KEY (reporter_user_id) REFERENCES users(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_reporter_created
|
|
ON content_reports(reporter_user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_room_created
|
|
ON content_reports(room_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_target_room
|
|
ON content_reports(target_room_id, created_at DESC)
|
|
WHERE target_room_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_target_user
|
|
ON content_reports(target_user_id, created_at DESC)
|
|
WHERE target_user_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_target_member
|
|
ON content_reports(target_member_room_id, target_member_user_id, created_at DESC)
|
|
WHERE target_member_room_id IS NOT NULL AND target_member_user_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_target_chat_message
|
|
ON content_reports(room_id, target_chat_message_id, target_chat_message_created_at)
|
|
WHERE target_chat_message_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_status_created
|
|
ON content_reports(status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_reason_code_trgm
|
|
ON content_reports USING gin(reason_code gin_trgm_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_content_reports_reason_trgm
|
|
ON content_reports USING gin(reason gin_trgm_ops);
|