supabase-brand-studio-pulls

-- Brand Studio — brands, pulls, and typed diffs.
--
-- Run AFTER docs/supabase-brand-studio-auth.sql. This migration is ADDITIVE and
-- reversible: brand_studio_runs is not dropped, renamed, or altered, and the
-- live server keeps reading and writing it while the UI is moved over. Nothing
-- here orphans an existing row.
--
-- Three things this schema does that RLS alone cannot:
--
--  1. Cross-table ownership. `user_id = auth.uid()` on each table does NOT stop
--     a pull from referencing another user's brand_id. The composite foreign
--     keys below make that physically impossible rather than merely forbidden.
--  2. Server-side enforcement. The live server uses the service-role key, which
--     BYPASSES RLS entirely. RLS is the backstop; the server must still check
--     ownership itself. Documented here so nobody assumes RLS is the gate.
--  3. Honest nulls. A null score means "not graded", never zero — a run that
--     was never scored must not render as a bad run.

create extension if not exists pgcrypto;

-- ---------------------------------------------------------------- brands ----
-- The diff anchor: a brand a user tracks over time.
create table if not exists public.brand_studio_brands (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  brand_key text not null,
  domain text not null default '',
  display_name text not null default '',
  pull_profile_id text not null default 'standard',
  -- The pinned baseline drift is measured against (see anchor_drift in
  -- src/local/pull-diff.mjs). Null until the owner pins one.
  anchor_pull_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, brand_key)
);

-- Required so pulls can carry a composite FK back to (id, user_id).
create unique index if not exists brand_studio_brands_id_user_idx
  on public.brand_studio_brands (id, user_id);

create index if not exists brand_studio_brands_user_updated_idx
  on public.brand_studio_brands (user_id, updated_at desc);

-- ----------------------------------------------------------------- pulls ----
-- One capture of one brand at one time. Carries every field the existing run
-- library UI reads, so rows copy from brand_studio_runs losslessly.
create table if not exists public.brand_studio_pulls (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  brand_id uuid not null,
  run_id text not null,

  -- carried over from brand_studio_runs (lossless copy)
  source_url text not null default '',
  domain text not null default '',
  title text not null default '',
  summary jsonb not null default '{}'::jsonb,
  signals jsonb not null default '{}'::jsonb,
  result_path text not null default '',
  trace_path text not null default '',
  elapsed_ms integer not null default 0,

  route text not null default 'local' check (route in ('local', 'hosted')),
  status text not null default 'running' check (status in ('running', 'passed', 'partial', 'failed')),

  -- comparability: the hash of RESOLVED budgets, not the preset label
  pull_profile_id text not null default 'standard',
  pull_profile_hash text not null default '',
  escalations jsonb not null default '[]'::jsonb,

  -- scores; null means NOT GRADED and must never render as zero
  section_quality jsonb,
  analysis_quality jsonb,
  speed jsonb,
  sources jsonb,
  overall_score numeric check (overall_score is null or (overall_score >= 1 and overall_score <= 5)),

  -- reproducibility: a score is meaningless without knowing what produced it
  scorer_version text not null default '',
  gold_spec_version text not null default '',
  langsmith_run_id text not null default '',

  created_at timestamptz not null default now(),
  unique (user_id, run_id),
  -- A pull can only ever belong to a brand the SAME user owns.
  constraint brand_studio_pulls_brand_owner_fk
    foreign key (brand_id, user_id)
    references public.brand_studio_brands (id, user_id)
    on delete cascade
);

create unique index if not exists brand_studio_pulls_id_user_idx
  on public.brand_studio_pulls (id, user_id);

create index if not exists brand_studio_pulls_user_brand_created_idx
  on public.brand_studio_pulls (user_id, brand_id, created_at desc);
create index if not exists brand_studio_pulls_brand_created_idx
  on public.brand_studio_pulls (brand_id, created_at desc);   -- previous-pull lookup
create index if not exists brand_studio_pulls_user_status_idx
  on public.brand_studio_pulls (user_id, status);

-- THE ATOMIC RUN GUARD (G7 review #1, round 7). "One running pull per
-- account" was enforced by a check-then-insert pair in
-- comprehensive-pull-handler.mjs, and two concurrent reservations could both
-- pass the check before either row existed — two pulls, two spends, one
-- credit. This partial unique index makes the INSERT itself the guard: the
-- second concurrent insert fails with SQLSTATE 23505, which the handler
-- answers as a named, retryable 409 (reservation_race_lost), never a second
-- charge. The handler ALSO verifies its reservation after the insert
-- (post-insert running/balance re-check with a deterministic winner), so a
-- deployment whose database has not applied this index yet is still closed —
-- but the index is the clean fix and should be applied.
--
-- MIGRATION NOTE (2026-08-25, added after the table shipped): apply with
--   create unique index concurrently if not exists ... -- (outside a transaction)
-- on a live database. Rows already violating it (two `running` pulls for one
-- account) must be resolved first — reap the stale one via the reaper's
-- normal path (status='failed', never charged) before creating the index.
create unique index if not exists brand_studio_pulls_one_running_per_user_idx
  on public.brand_studio_pulls (user_id) where status = 'running';

-- ----------------------------------------------------------------- diffs ----
create table if not exists public.brand_studio_pull_diffs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  brand_id uuid not null,
  from_pull_id uuid not null,
  to_pull_id uuid not null,

  changes jsonb not null default '[]'::jsonb,
  change_count integer not null default 0,
  alertable_changes integer not null default 0,
  confounded_changes integer not null default 0,
  anchor_drift jsonb not null default '[]'::jsonb,
  has_material_change boolean not null default false,

  -- An incomparable diff must say so rather than be quietly trusted.
  comparable boolean not null default true,
  incomparable_reason text not null default '',

  created_at timestamptz not null default now(),
  unique (from_pull_id, to_pull_id),
  -- A diff cannot compare a pull to itself.
  constraint brand_studio_pull_diffs_distinct_pulls check (from_pull_id <> to_pull_id),
  constraint brand_studio_pull_diffs_brand_owner_fk
    foreign key (brand_id, user_id)
    references public.brand_studio_brands (id, user_id) on delete cascade,
  constraint brand_studio_pull_diffs_from_owner_fk
    foreign key (from_pull_id, user_id)
    references public.brand_studio_pulls (id, user_id) on delete cascade,
  constraint brand_studio_pull_diffs_to_owner_fk
    foreign key (to_pull_id, user_id)
    references public.brand_studio_pulls (id, user_id) on delete cascade
);

create index if not exists brand_studio_pull_diffs_brand_created_idx
  on public.brand_studio_pull_diffs (brand_id, created_at desc);
create index if not exists brand_studio_pull_diffs_to_pull_idx
  on public.brand_studio_pull_diffs (to_pull_id);

-- ------------------------------------------------------------------ RLS ----
alter table public.brand_studio_brands enable row level security;
alter table public.brand_studio_pulls enable row level security;
alter table public.brand_studio_pull_diffs enable row level security;

do $$
declare
  t text;
begin
  foreach t in array array['brand_studio_brands', 'brand_studio_pulls', 'brand_studio_pull_diffs']
  loop
    execute format('drop policy if exists %I_select_own on public.%I', t, t);
    execute format('create policy %I_select_own on public.%I for select using (user_id = auth.uid())', t, t);
    execute format('drop policy if exists %I_insert_own on public.%I', t, t);
    execute format('create policy %I_insert_own on public.%I for insert with check (user_id = auth.uid())', t, t);
    execute format('drop policy if exists %I_update_own on public.%I', t, t);
    execute format('create policy %I_update_own on public.%I for update using (user_id = auth.uid()) with check (user_id = auth.uid())', t, t);
    execute format('drop policy if exists %I_delete_own on public.%I', t, t);
    execute format('create policy %I_delete_own on public.%I for delete using (user_id = auth.uid())', t, t);
  end loop;
end $$;

-- ------------------------------------------------------------ backfill ----
-- Phase 1 of a two-phase migration: create + dual-write. brand_studio_runs
-- stays authoritative for the existing UI until phase 2 moves it to pulls.
--
-- brand_key defaults to '' on the live table, so it is derived from the domain
-- when empty. A row with neither is REPORTED, never invented — see the final
-- select.

insert into public.brand_studio_brands (user_id, brand_key, domain, display_name)
select distinct
  r.user_id,
  coalesce(nullif(r.brand_key, ''), nullif(r.domain, '')) as brand_key,
  coalesce(r.domain, ''),
  coalesce(nullif(r.title, ''), coalesce(r.domain, ''))
from public.brand_studio_runs r
where coalesce(nullif(r.brand_key, ''), nullif(r.domain, '')) is not null
on conflict (user_id, brand_key) do nothing;

insert into public.brand_studio_pulls (
  user_id, brand_id, run_id, source_url, domain, title, summary, signals,
  result_path, trace_path, elapsed_ms, route, status, created_at
)
select
  r.user_id,
  b.id,
  r.run_id,
  coalesce(r.source_url, ''),
  coalesce(r.domain, ''),
  coalesce(r.title, ''),
  coalesce(r.summary, '{}'::jsonb),
  coalesce(r.signals, '{}'::jsonb),
  coalesce(r.result_path, ''),
  coalesce(r.trace_path, ''),
  coalesce(r.elapsed_ms, 0),
  -- Historical rows predate the hosted route; 'local' is the honest value,
  -- not a guess.
  'local',
  case when r.status in ('running', 'passed', 'partial', 'failed') then r.status else 'passed' end,
  coalesce(r.created_at, now())
from public.brand_studio_runs r
join public.brand_studio_brands b
  on b.user_id = r.user_id
 and b.brand_key = coalesce(nullif(r.brand_key, ''), nullif(r.domain, ''))
on conflict (user_id, run_id) do nothing;

-- Rows that could not be mapped to a brand. Reported, never invented.
select
  count(*) as unmapped_runs,
  'runs with neither brand_key nor domain — left in brand_studio_runs, not backfilled' as note
from public.brand_studio_runs r
where coalesce(nullif(r.brand_key, ''), nullif(r.domain, '')) is null;

-- 2026-08-10: the full capture payload. Until this column existed the pull row
-- stored only `summary` (leg names, statuses, evidence COUNTS) — the actual
-- content (page markdown, social post records, seo keywords, voice analysis)
-- was captured in memory during the invocation and DISCARDED. The customer got
-- a receipt, not the goods; found by the owner inspecting a pull and asking
-- where the site scrape was. Owner-scoped by the table's existing RLS.
alter table public.brand_studio_pulls
  add column if not exists capture_result jsonb;