supabase-brand-studio-section-review-recovery

-- Additive sidecar; original pull/capture/checkpoint/review are never updated.
-- Run after supabase-brand-studio-pulls.sql. Server owner-auth then service-role
-- CAS is mandatory: neither clients nor owners write ledger documents directly.
create table if not exists public.brand_studio_section_review_recoveries (
  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 section_review_recoveries_owner_fk foreign key (pull_id, owner_id)
    references public.brand_studio_pulls(id, user_id) on delete cascade,
  constraint section_review_recoveries_envelope check ((
    jsonb_typeof(state) = 'object' and state->>'schema_version' = 'brand-studio-section-review-recoveries/v1'
    and jsonb_typeof(state->'data') = 'string' and jsonb_typeof(state->'bytes') = 'number'
    and state->>'sha256' ~ '^[a-f0-9]{64}$' and pg_column_size(state) <= 12582912
  ) is true)
);
alter table public.brand_studio_section_review_recoveries enable row level security;
revoke all on public.brand_studio_section_review_recoveries from anon, authenticated;
grant select, insert, update, delete on public.brand_studio_section_review_recoveries to service_role;
-- Envelope is bounded gzip+sha256 of the server document, not encryption.
-- Contains retained evidence/prepared images and must stay private. CAS PATCH
-- filters pull_id + owner_id + version; unknown acknowledgements stop dispatch.
-- Claimed rounds never expire into permission to repeat. Keep uncertain holds.