d3ro-voice/server/supabase/migrations/20260824000029_content_reporting.sql

-- Authenticated content reporting for ephemeral AI output.
--
-- Generation receipts deliberately contain no prompt or generated text. A report
-- stores only the snapshot the reporter explicitly submits for moderation. All
-- tables remain service-only; user and moderator access crosses narrow RPCs.

BEGIN;

CREATE TABLE public.content_generation_receipts (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  purpose     text NOT NULL CHECK (purpose ~ '^[a-z][a-z0-9_]{2,39}$'),
  model       text NOT NULL CHECK (char_length(model) BETWEEN 1 AND 120),
  created_at  timestamptz NOT NULL DEFAULT now(),
  expires_at  timestamptz NOT NULL DEFAULT (now() + interval '30 days'),
  CHECK (expires_at > created_at)
);

COMMENT ON TABLE public.content_generation_receipts IS
  'Service-issued proof that a user received an AI generation; never stores prompts or generated text.';

CREATE INDEX content_generation_receipts_expiry_idx
  ON public.content_generation_receipts(expires_at);
CREATE INDEX content_generation_receipts_user_created_idx
  ON public.content_generation_receipts(user_id, created_at DESC);

CREATE TABLE public.content_reports (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  reporter_id           uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  idempotency_key       uuid NOT NULL,
  request_hash          text NOT NULL CHECK (request_hash ~ '^[0-9a-f]{64}$'),
  kind                  text NOT NULL CHECK (kind ~ '^[a-z][a-z0-9_]{2,39}$'),
  source_type           text NOT NULL CHECK (source_type ~ '^[a-z][a-z0-9_]{2,39}$'),
  source_id             uuid NOT NULL,
  generation_receipt_id uuid REFERENCES public.content_generation_receipts(id) ON DELETE SET NULL,
  reason                text NOT NULL CHECK (reason IN (
                          'harmful', 'sexual', 'hateful', 'violent', 'self_harm',
                          'misinformation', 'privacy', 'spam', 'other'
                        )),
  reporter_comment      text CHECK (
                          reporter_comment IS NULL
                          OR char_length(reporter_comment) BETWEEN 1 AND 500
                        ),
  reported_snapshot     text NOT NULL CHECK (char_length(reported_snapshot) BETWEEN 1 AND 4000),
  snapshot_sha256       text NOT NULL CHECK (snapshot_sha256 ~ '^[0-9a-f]{64}$'),
  status                text NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending', 'reviewing', 'actioned', 'dismissed')),
  resolution_code       text CHECK (
                          resolution_code IS NULL
                          OR resolution_code IN ('dismiss', 'confirm_violation')
                        ),
  reviewed_by           uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  reviewed_at           timestamptz,
  evidence_expires_at   timestamptz,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  CHECK (
    (status IN ('pending', 'reviewing') AND resolution_code IS NULL)
    OR (status = 'dismissed' AND resolution_code = 'dismiss')
    OR (status = 'actioned' AND resolution_code = 'confirm_violation')
  ),
  CHECK (
    (status = 'pending' AND reviewed_by IS NULL AND reviewed_at IS NULL)
    OR (status <> 'pending' AND reviewed_at IS NOT NULL)
  ),
  CHECK (
    (status IN ('pending', 'reviewing') AND evidence_expires_at IS NULL)
    OR (status IN ('actioned', 'dismissed') AND evidence_expires_at > reviewed_at)
  )
);

COMMENT ON COLUMN public.content_reports.reported_snapshot IS
  'Reporter-selected evidence retained for moderation; handlers must never copy it into application logs.';

CREATE UNIQUE INDEX content_reports_reporter_idempotency_idx
  ON public.content_reports(reporter_id, idempotency_key)
  WHERE reporter_id IS NOT NULL;
CREATE UNIQUE INDEX content_reports_reporter_source_idx
  ON public.content_reports(reporter_id, source_type, source_id)
  WHERE reporter_id IS NOT NULL;
CREATE INDEX content_reports_moderation_queue_idx
  ON public.content_reports(status, created_at, id);
CREATE INDEX content_reports_reporter_rate_idx
  ON public.content_reports(reporter_id, created_at DESC)
  WHERE reporter_id IS NOT NULL;

CREATE TABLE public.content_report_review_actions (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id       uuid NOT NULL REFERENCES public.content_reports(id) ON DELETE CASCADE,
  actor_id        uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  idempotency_key uuid NOT NULL,
  request_hash    text NOT NULL CHECK (request_hash ~ '^[0-9a-f]{64}$'),
  action          text NOT NULL CHECK (action IN ('begin_review', 'dismiss', 'confirm_violation')),
  note            text NOT NULL CHECK (char_length(note) BETWEEN 3 AND 1000),
  before_status   text NOT NULL,
  after_status    text NOT NULL,
  response_data   jsonb NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX content_report_review_action_idempotency_idx
  ON public.content_report_review_actions(actor_id, idempotency_key)
  WHERE actor_id IS NOT NULL;
CREATE INDEX content_report_review_actions_report_idx
  ON public.content_report_review_actions(report_id, created_at DESC);

ALTER TABLE public.content_generation_receipts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.content_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.content_report_review_actions ENABLE ROW LEVEL SECURITY;

-- No RLS policies are created. Even the reporter cannot read or mutate the
-- moderation ledger through PostgREST; the Edge Function uses service RPCs.
REVOKE ALL ON TABLE public.content_generation_receipts FROM PUBLIC, anon, authenticated;
REVOKE ALL ON TABLE public.content_reports FROM PUBLIC, anon, authenticated;
REVOKE ALL ON TABLE public.content_report_review_actions FROM PUBLIC, anon, authenticated;
GRANT ALL ON TABLE public.content_generation_receipts TO service_role;
GRANT ALL ON TABLE public.content_reports TO service_role;
GRANT ALL ON TABLE public.content_report_review_actions TO service_role;

CREATE OR REPLACE FUNCTION public.issue_content_generation_receipt_v1(
  p_actor_id uuid,
  p_purpose text,
  p_model text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, auth
AS $$
DECLARE
  receipt_id uuid;
  receipt_expiry timestamptz;
BEGIN
  IF auth.role() <> 'service_role' THEN
    RAISE EXCEPTION 'service_role_required' USING ERRCODE = '42501';
  END IF;
  IF p_actor_id IS NULL OR p_purpose IS NULL OR p_model IS NULL THEN
    RAISE EXCEPTION 'generation_receipt_fields_required' USING ERRCODE = '22023';
  END IF;
  IF p_purpose NOT IN ('talk_response', 'command_response', 'action_response') THEN
    RAISE EXCEPTION 'invalid_generation_purpose' USING ERRCODE = '22023';
  END IF;
  IF char_length(trim(p_model)) NOT BETWEEN 1 AND 120 THEN
    RAISE EXCEPTION 'invalid_generation_model' USING ERRCODE = '22023';
  END IF;

  -- Protect the user foreign key from a concurrent account deletion until the
  -- receipt has either been inserted or failed closed.
  PERFORM 1 FROM auth.users WHERE id = p_actor_id FOR KEY SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'generation_user_not_found' USING ERRCODE = 'P0002';
  END IF;

  INSERT INTO public.content_generation_receipts(user_id, purpose, model)
  VALUES (p_actor_id, p_purpose, trim(p_model))
  RETURNING id, expires_at INTO receipt_id, receipt_expiry;

  RETURN jsonb_build_object(
    'generationId', receipt_id,
    'expiresAt', receipt_expiry
  );
END;
$$;

REVOKE ALL ON FUNCTION public.issue_content_generation_receipt_v1(uuid, text, text)
  FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.issue_content_generation_receipt_v1(uuid, text, text)
  TO service_role;

CREATE OR REPLACE FUNCTION public.submit_content_report_v1(
  p_actor_id uuid,
  p_idempotency_key uuid,
  p_kind text,
  p_source_type text,
  p_source_id uuid,
  p_reason text,
  p_comment text,
  p_snapshot text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, auth, extensions
AS $$
DECLARE
  request_payload jsonb;
  request_digest text;
  existing_report public.content_reports%ROWTYPE;
  receipt public.content_generation_receipts%ROWTYPE;
  created_report public.content_reports%ROWTYPE;
  normalized_comment text;
  normalized_snapshot text;
BEGIN
  IF auth.role() <> 'service_role' THEN
    RAISE EXCEPTION 'service_role_required' USING ERRCODE = '42501';
  END IF;
  IF p_actor_id IS NULL OR p_idempotency_key IS NULL OR p_source_id IS NULL THEN
    RAISE EXCEPTION 'content_report_identifiers_required' USING ERRCODE = '22023';
  END IF;
  IF p_kind <> 'ai_output'
     OR p_source_type NOT IN ('talk_response', 'command_response', 'action_response') THEN
    RAISE EXCEPTION 'unsupported_content_report_source' USING ERRCODE = '22023';
  END IF;
  IF p_reason NOT IN (
    'harmful', 'sexual', 'hateful', 'violent', 'self_harm',
    'misinformation', 'privacy', 'spam', 'other'
  ) THEN
    RAISE EXCEPTION 'invalid_content_report_reason' USING ERRCODE = '22023';
  END IF;

  normalized_comment := CASE WHEN p_comment IS NULL THEN NULL ELSE trim(p_comment) END;
  normalized_snapshot := trim(coalesce(p_snapshot, ''));
  IF normalized_comment IS NOT NULL
     AND char_length(normalized_comment) NOT BETWEEN 1 AND 500 THEN
    RAISE EXCEPTION 'invalid_content_report_comment' USING ERRCODE = '22023';
  END IF;
  IF char_length(normalized_snapshot) NOT BETWEEN 1 AND 4000 THEN
    RAISE EXCEPTION 'invalid_content_report_snapshot' USING ERRCODE = '22023';
  END IF;

  request_payload := jsonb_build_object(
    'kind', p_kind,
    'source', jsonb_build_object('type', p_source_type, 'generationId', p_source_id),
    'reason', p_reason,
    'comment', normalized_comment,
    'snapshot', normalized_snapshot
  );
  request_digest := encode(extensions.digest(request_payload::text, 'sha256'), 'hex');

  -- One actor lock covers idempotency, duplicate-source detection and both
  -- rate windows. Concurrent calls cannot observe the same remaining slot.
  PERFORM pg_advisory_xact_lock(
    pg_catalog.hashtextextended('d3ro:content-report:' || p_actor_id::text, 0)
  );

  SELECT * INTO existing_report
  FROM public.content_reports
  WHERE reporter_id = p_actor_id AND idempotency_key = p_idempotency_key;

  IF existing_report.id IS NOT NULL THEN
    IF existing_report.request_hash <> request_digest THEN
      RAISE EXCEPTION 'content_report_idempotency_conflict' USING ERRCODE = 'PT409';
    END IF;
    RETURN jsonb_build_object(
      'reportId', existing_report.id,
      'status', 'submitted',
      'idempotent', true,
      'createdAt', existing_report.created_at
    );
  END IF;

  -- Keep account deletion from invalidating the reporter foreign key between
  -- source verification and the report insert. A deletion that wins first is
  -- intentionally indistinguishable from any other missing source.
  PERFORM 1 FROM auth.users WHERE id = p_actor_id FOR KEY SHARE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'content_report_source_not_found' USING ERRCODE = 'P0002';
  END IF;

  SELECT * INTO receipt
  FROM public.content_generation_receipts
  WHERE id = p_source_id
    AND user_id = p_actor_id
    AND purpose = p_source_type
    AND expires_at > now()
  FOR KEY SHARE;
  IF receipt.id IS NULL THEN
    -- Missing, expired and cross-user receipts intentionally look identical.
    RAISE EXCEPTION 'content_report_source_not_found' USING ERRCODE = 'P0002';
  END IF;

  IF EXISTS (
    SELECT 1 FROM public.content_reports
    WHERE reporter_id = p_actor_id
      AND source_type = p_source_type
      AND source_id = p_source_id
  ) THEN
    RAISE EXCEPTION 'content_report_source_already_reported' USING ERRCODE = 'PT409';
  END IF;

  IF (
    SELECT count(*) FROM public.content_reports
    WHERE reporter_id = p_actor_id
      AND created_at > now() - interval '1 hour'
  ) >= 10 OR (
    SELECT count(*) FROM public.content_reports
    WHERE reporter_id = p_actor_id
      AND created_at > now() - interval '24 hours'
  ) >= 30 THEN
    RAISE EXCEPTION 'content_report_rate_limited' USING ERRCODE = 'PT429';
  END IF;

  INSERT INTO public.content_reports(
    reporter_id, idempotency_key, request_hash, kind, source_type, source_id,
    generation_receipt_id, reason, reporter_comment, reported_snapshot,
    snapshot_sha256
  ) VALUES (
    p_actor_id, p_idempotency_key, request_digest, p_kind, p_source_type, p_source_id,
    receipt.id, p_reason, normalized_comment, normalized_snapshot,
    encode(extensions.digest(normalized_snapshot, 'sha256'), 'hex')
  )
  RETURNING * INTO created_report;

  RETURN jsonb_build_object(
    'reportId', created_report.id,
    'status', 'submitted',
    'idempotent', false,
    'createdAt', created_report.created_at
  );
END;
$$;

REVOKE ALL ON FUNCTION public.submit_content_report_v1(
  uuid, uuid, text, text, uuid, text, text, text
) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.submit_content_report_v1(
  uuid, uuid, text, text, uuid, text, text, text
) TO service_role;

CREATE OR REPLACE FUNCTION public.admin_list_content_reports_v1(
  p_status text DEFAULT NULL,
  p_limit integer DEFAULT 50,
  p_before timestamptz DEFAULT NULL
) RETURNS TABLE (
  report_id uuid,
  reporter_id uuid,
  kind text,
  source_type text,
  source_id uuid,
  reason text,
  reporter_comment text,
  reported_snapshot text,
  status text,
  resolution_code text,
  reviewed_by uuid,
  reviewed_at timestamptz,
  created_at timestamptz
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, auth
AS $$
DECLARE
  v_actor_id uuid;
  v_actor_role text;
BEGIN
  v_actor_id := auth.uid();
  SELECT profile.role INTO v_actor_role
  FROM public.profiles AS profile
  WHERE profile.id = v_actor_id;
  IF v_actor_role IS NULL OR v_actor_role NOT IN ('manager', 'admin', 'super_admin') THEN
    RAISE EXCEPTION 'content_report_manager_required' USING ERRCODE = '42501';
  END IF;
  IF p_status IS NOT NULL
     AND p_status NOT IN ('pending', 'reviewing', 'actioned', 'dismissed') THEN
    RAISE EXCEPTION 'invalid_content_report_status' USING ERRCODE = '22023';
  END IF;
  IF p_limit IS NULL OR p_limit NOT BETWEEN 1 AND 100 THEN
    RAISE EXCEPTION 'invalid_content_report_limit' USING ERRCODE = '22023';
  END IF;

  RETURN QUERY
  SELECT
    report.id,
    report.reporter_id,
    report.kind,
    report.source_type,
    report.source_id,
    report.reason,
    report.reporter_comment,
    report.reported_snapshot,
    report.status,
    report.resolution_code,
    report.reviewed_by,
    report.reviewed_at,
    report.created_at
  FROM public.content_reports AS report
  WHERE (p_status IS NULL OR report.status = p_status)
    AND (p_before IS NULL OR report.created_at < p_before)
  ORDER BY report.created_at DESC, report.id DESC
  LIMIT p_limit;
END;
$$;

REVOKE ALL ON FUNCTION public.admin_list_content_reports_v1(text, integer, timestamptz)
  FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.admin_list_content_reports_v1(text, integer, timestamptz)
  TO authenticated;

CREATE OR REPLACE FUNCTION public.admin_act_on_content_report_v1(
  p_report_id uuid,
  p_idempotency_key uuid,
  p_action text,
  p_note text
) RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, auth, extensions
AS $$
DECLARE
  v_actor_id uuid;
  v_actor_role text;
  report public.content_reports%ROWTYPE;
  existing_action public.content_report_review_actions%ROWTYPE;
  normalized_note text;
  request_digest text;
  next_status text;
  next_resolution text;
  result jsonb;
BEGIN
  v_actor_id := auth.uid();
  SELECT profile.role INTO v_actor_role
  FROM public.profiles AS profile
  WHERE profile.id = v_actor_id;
  IF v_actor_role IS NULL OR v_actor_role NOT IN ('manager', 'admin', 'super_admin') THEN
    RAISE EXCEPTION 'content_report_manager_required' USING ERRCODE = '42501';
  END IF;
  IF p_report_id IS NULL OR p_idempotency_key IS NULL THEN
    RAISE EXCEPTION 'content_report_action_identifiers_required' USING ERRCODE = '22023';
  END IF;
  IF p_action NOT IN ('begin_review', 'dismiss', 'confirm_violation') THEN
    RAISE EXCEPTION 'invalid_content_report_action' USING ERRCODE = '22023';
  END IF;
  normalized_note := trim(coalesce(p_note, ''));
  IF char_length(normalized_note) NOT BETWEEN 3 AND 1000 THEN
    RAISE EXCEPTION 'invalid_content_report_action_note' USING ERRCODE = '22023';
  END IF;

  request_digest := encode(extensions.digest(jsonb_build_object(
    'reportId', p_report_id,
    'action', p_action,
    'note', normalized_note
  )::text, 'sha256'), 'hex');

  PERFORM pg_advisory_xact_lock(
    pg_catalog.hashtextextended('d3ro:content-report-review:' || v_actor_id::text, 0)
  );

  SELECT * INTO existing_action
  FROM public.content_report_review_actions
  WHERE actor_id = v_actor_id AND idempotency_key = p_idempotency_key;
  IF existing_action.id IS NOT NULL THEN
    IF existing_action.request_hash <> request_digest THEN
      RAISE EXCEPTION 'content_report_action_idempotency_conflict' USING ERRCODE = 'PT409';
    END IF;
    RETURN existing_action.response_data || jsonb_build_object('idempotent', true);
  END IF;

  SELECT * INTO report FROM public.content_reports
  WHERE id = p_report_id FOR UPDATE;
  IF report.id IS NULL THEN
    RAISE EXCEPTION 'content_report_not_found' USING ERRCODE = 'P0002';
  END IF;

  IF p_action = 'begin_review' THEN
    IF report.status NOT IN ('pending', 'reviewing') THEN
      RAISE EXCEPTION 'content_report_already_resolved' USING ERRCODE = 'PT409';
    END IF;
    next_status := 'reviewing';
    next_resolution := NULL;
  ELSIF p_action = 'dismiss' THEN
    IF report.status NOT IN ('pending', 'reviewing') THEN
      RAISE EXCEPTION 'content_report_already_resolved' USING ERRCODE = 'PT409';
    END IF;
    next_status := 'dismissed';
    next_resolution := 'dismiss';
  ELSE
    IF report.status NOT IN ('pending', 'reviewing') THEN
      RAISE EXCEPTION 'content_report_already_resolved' USING ERRCODE = 'PT409';
    END IF;
    next_status := 'actioned';
    next_resolution := 'confirm_violation';
  END IF;

  UPDATE public.content_reports
  SET status = next_status,
      resolution_code = next_resolution,
      reviewed_by = v_actor_id,
      reviewed_at = now(),
      evidence_expires_at = CASE
        WHEN next_status IN ('actioned', 'dismissed') THEN now() + interval '180 days'
        ELSE NULL
      END,
      updated_at = now()
  WHERE id = report.id
  RETURNING reviewed_at INTO report.reviewed_at;

  result := jsonb_build_object(
    'reportId', report.id,
    'status', next_status,
    'action', p_action,
    'reviewedAt', report.reviewed_at,
    'idempotent', false
  );

  INSERT INTO public.content_report_review_actions(
    report_id, actor_id, idempotency_key, request_hash, action, note,
    before_status, after_status, response_data
  ) VALUES (
    report.id, v_actor_id, p_idempotency_key, request_digest, p_action,
    normalized_note, report.status, next_status, result
  );

  -- Audit metadata only: never duplicate the report snapshot into general logs.
  INSERT INTO public.audit_log(
    admin_id, action, target_type, target_id, before_data, after_data, memo
  ) VALUES (
    v_actor_id,
    'content_report.' || p_action,
    'content_report',
    report.id,
    jsonb_build_object('status', report.status),
    jsonb_build_object('status', next_status, 'resolution', next_resolution),
    normalized_note
  );

  RETURN result;
END;
$$;

REVOKE ALL ON FUNCTION public.admin_act_on_content_report_v1(uuid, uuid, text, text)
  FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.admin_act_on_content_report_v1(uuid, uuid, text, text)
  TO authenticated;

CREATE OR REPLACE FUNCTION public.purge_expired_content_reporting_data_v1()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public, auth
AS $$
DECLARE
  purged_reports integer;
  purged_receipts integer;
BEGIN
  IF auth.role() <> 'service_role' THEN
    RAISE EXCEPTION 'service_role_required' USING ERRCODE = '42501';
  END IF;

  -- Pending/reviewing evidence is never time-purged. Final moderation evidence
  -- receives a fixed 180-day appeal/abuse-defense window when it is resolved.
  DELETE FROM public.content_reports
  WHERE status IN ('actioned', 'dismissed')
    AND evidence_expires_at <= now();
  GET DIAGNOSTICS purged_reports = ROW_COUNT;

  -- Receipts never contain content. Once their reporting window ends they no
  -- longer prove a live source and can be removed; linked reports retain their
  -- source UUID while the foreign-key pointer is nulled.
  DELETE FROM public.content_generation_receipts
  WHERE expires_at <= now();
  GET DIAGNOSTICS purged_receipts = ROW_COUNT;

  RETURN jsonb_build_object(
    'purgedReports', purged_reports,
    'purgedReceipts', purged_receipts
  );
END;
$$;

REVOKE ALL ON FUNCTION public.purge_expired_content_reporting_data_v1()
  FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.purge_expired_content_reporting_data_v1()
  TO service_role;

COMMIT;