supabase-brand-studio-client-review

-- Additive client-review sidecar. Run after supabase-brand-studio-pulls.sql.
-- No UPDATE of captured evidence, costs, plans, or existing owner overrides.
-- One document per owned pull makes request/round/AI reservation changes atomic.
-- Service-role HTTP handlers validate ownership or a hashed public capability;
-- clients cannot bypass that validation through PostgREST, including the owner.
create table if not exists public.brand_studio_client_reviews (
  pull_id uuid primary key,
  owner_id uuid not null references auth.users(id) on delete cascade,
  version bigint not null default 1 check (version >= 1),
  state jsonb not null,
  created_at timestamptz not null default now(),
  constraint brand_studio_client_reviews_pull_owner_fk foreign key (pull_id, owner_id)
    references public.brand_studio_pulls(id, user_id) on delete cascade,
  constraint brand_studio_client_reviews_binding check ((
    jsonb_typeof(state) = 'object' and state->>'schema_version' = 'brand-studio-client-review/v1'
    and state->>'pull_id' = pull_id::text and state->>'owner_id' = owner_id::text
    and jsonb_typeof(state->'shares') = 'array' and jsonb_typeof(state->'requests') = 'array'
    and jsonb_typeof(state->'rounds') = 'array' and jsonb_typeof(state->'proposals') = 'array'
    and jsonb_typeof(state->'overrides') = 'object' and pg_column_size(state) <= 12582912
  ) is true)
);
create index if not exists brand_studio_client_reviews_shares_idx
  on public.brand_studio_client_reviews using gin ((state->'shares') jsonb_path_ops);
create index if not exists brand_studio_client_reviews_requests_idx
  on public.brand_studio_client_reviews using gin ((state->'requests') jsonb_path_ops);
alter table public.brand_studio_client_reviews enable row level security;
revoke all on public.brand_studio_client_reviews from anon, authenticated;
grant select, insert, update, delete on public.brand_studio_client_reviews to service_role;
-- No anon/authenticated policies: link expiry/revocation cannot be bypassed.
-- Compare-and-swap PATCH includes both owner_id and the previous version.
-- Ambiguous transport failures are not retried as if they were CAS conflicts.