reports

Overview

The reports table stores user-submitted reports of problematic content or behavior. Reports go through a workflow: pending, reviewing, resolved, or dismissed. Admins can add resolution notes. The system tracks which content or user was reported and categorizes the reason.

Relevant Invariants

• Invariant #5: "Repair Over Punishment" -- Reports lead to de-escalation, not automatic punishment

Schema

-- From 20260202_admin_system.sql
CREATE TABLE reports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reporter_id UUID NOT NULL REFERENCES auth.users(id),
  reported_user_id UUID REFERENCES auth.users(id),
  reported_post_id UUID REFERENCES posts(id),
  reported_comment_id UUID REFERENCES comments(id),
  reason TEXT NOT NULL,
  category TEXT DEFAULT 'other',
  description TEXT,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'reviewing', 'resolved', 'dismissed')),
  resolved_by UUID REFERENCES auth.users(id),
  resolution_note TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Columns

Column	Type	Nullable	Default	Description
id	uuid	No	gen_random_uuid()	Primary key
reporter_id	uuid	No	--	User who submitted the report
reported_user_id	uuid	Yes	--	User being reported (if applicable)
reported_post_id	uuid	Yes	--	Post being reported (if applicable)
reported_comment_id	uuid	Yes	--	Comment being reported (if applicable)
reason	text	No	--	Report reason text
category	text	No	'other'	Report category
description	text	Yes	--	Additional details
status	text	No	'pending'	Workflow status
resolved_by	uuid	Yes	--	Admin who resolved the report
resolution_note	text	Yes	--	Admin resolution notes
created_at	timestamptz	No	NOW()	Report submission timestamp
updated_at	timestamptz	No	NOW()	Last status change

RLS Policies

-- SELECT: Admins can view all reports; users can view their own
CREATE POLICY "Users can view own reports, admins view all"
  ON reports FOR SELECT
  USING (
    auth.uid() = reporter_id OR
    EXISTS (SELECT 1 FROM admin_roles WHERE admin_roles.user_id = auth.uid())
  );

-- INSERT: Authenticated users can submit reports
CREATE POLICY "Users can submit reports"
  ON reports FOR INSERT
  WITH CHECK (auth.uid() = reporter_id);

-- UPDATE: Admins can update report status
CREATE POLICY "Admins can update reports"
  ON reports FOR UPDATE
  USING (EXISTS (SELECT 1 FROM admin_roles WHERE admin_roles.user_id = auth.uid()));

Related

• profiles -- Reporter and reported user
• posts -- Reported post
• comments -- Reported comment
• admin_roles -- Admin who resolves
• admin_audit_log -- Audit trail

---

Last updated: 2026-02-07