supabase-brand-studio-review

-- Brand Studio — human score overrides (R3: the review surface's disagreement layer).
--
-- ADDITIVE. Run AFTER docs/supabase-brand-studio-pulls.sql — this table's
-- composite FK targets brand_studio_pulls(id, user_id), which only exists
-- once that migration has run. Nothing here alters brand_studio_pulls; the
-- computed section_quality / analysis_quality columns on that table are never
-- written to by anything in this file.
--
-- The one rule this schema exists to enforce (see src/local/review-overrides.mjs):
-- an override sits BESIDE the computed score it disagrees with, never over
-- it. This table only ever grows; nobody UPDATEs a pull's section_quality
-- from a review.

create extension if not exists pgcrypto;

create table if not exists public.brand_studio_score_overrides (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  pull_id uuid not null,
  section text not null,

  -- Duplicated from the pull's section_quality at the moment of the review,
  -- not joined live against it. A pull's section_quality can be recomputed
  -- later — a scorer-version bump, a fix landing from the R5 audit — and if
  -- this column were a live join instead of a pinned copy, that recompute
  -- would retroactively change what the override appears to be disagreeing
  -- with. Pinning it here is what keeps "the scorer used to say 2, a fix
  -- changed it to 4, and this reviewer said 4 all along" a legible historical
  -- fact instead of a silently rewritten one — the override is evidence
  -- about a specific scorer run, not a live opinion about whatever the
  -- scorer currently says.
  computed_score numeric not null check (computed_score >= 1 and computed_score <= 5),
  human_score numeric not null check (human_score >= 1 and human_score <= 5),

  -- No default and NOT NULL by the check below: an override with no stated
  -- reason teaches the scorer nothing (see applyOverride) and is refused
  -- before it ever reaches this table, so a row that got here always has one.
  reason text not null check (length(trim(reason)) > 0),
  reviewer text not null default '',
  scorer_version text not null default '',

  created_at timestamptz not null default now(),

  -- One reviewer, one CURRENT opinion per section. A reviewer who changes
  -- their mind upserts this row (on conflict do update) rather than appending
  -- a second one that would silently double-count them in overrideDrift.
  unique (pull_id, section, reviewer),

  -- A pull can only ever carry overrides from the SAME user who owns it —
  -- physically impossible to attach a review to someone else's pull, not just
  -- forbidden by the row-level policies below.
  constraint brand_studio_score_overrides_pull_owner_fk
    foreign key (pull_id, user_id)
    references public.brand_studio_pulls (id, user_id)
    on delete cascade
);

create index if not exists brand_studio_score_overrides_pull_idx
  on public.brand_studio_score_overrides (pull_id);

-- ------------------------------------------------------------------ RLS ----
alter table public.brand_studio_score_overrides enable row level security;

drop policy if exists brand_studio_score_overrides_select_own on public.brand_studio_score_overrides;
create policy brand_studio_score_overrides_select_own on public.brand_studio_score_overrides
  for select using (user_id = auth.uid());

drop policy if exists brand_studio_score_overrides_insert_own on public.brand_studio_score_overrides;
create policy brand_studio_score_overrides_insert_own on public.brand_studio_score_overrides
  for insert with check (user_id = auth.uid());

drop policy if exists brand_studio_score_overrides_update_own on public.brand_studio_score_overrides;
create policy brand_studio_score_overrides_update_own on public.brand_studio_score_overrides
  for update using (user_id = auth.uid()) with check (user_id = auth.uid());

drop policy if exists brand_studio_score_overrides_delete_own on public.brand_studio_score_overrides;
create policy brand_studio_score_overrides_delete_own on public.brand_studio_score_overrides
  for delete using (user_id = auth.uid());