supabase-brand-studio-review-release-check

-- Read-only catalog probe for scripts/release/verify-client-review-and-retry.mjs.
-- Install after the client-review and section-review-recovery migrations.
-- A successful service-role table read alone cannot establish RLS or indexes.
-- This function exposes only these two tables' structure, never customer rows.
create or replace function public.brand_studio_review_release_check()
returns jsonb language sql stable security invoker
set search_path = pg_catalog
as $probe$
with targets(name) as (values ('brand_studio_client_reviews'), ('brand_studio_section_review_recoveries')),
tables as (
  select t.name, c.oid, c.relrowsecurity, c.relkind
  from targets t left join pg_class c on c.relname=t.name and c.relnamespace='public'::regnamespace
)
select jsonb_build_object('schema_version','brand-studio-review-release-catalog/v1', 'tables', jsonb_agg(jsonb_build_object(
  'name',t.name,'exists',t.oid is not null,'rls',coalesce(t.relrowsecurity,false),'ordinary_table',t.relkind='r',
  'service_role_bypassrls',coalesce((select rolbypassrls from pg_roles where rolname='service_role'),false),
  'service_crud',coalesce((select bool_and(has_table_privilege('service_role',t.oid,p)) from unnest(array['SELECT','INSERT','UPDATE','DELETE']) p),false),
  'client_privileges_absent',coalesce(not (has_table_privilege('anon',t.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
    or has_table_privilege('authenticated',t.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
    or has_any_column_privilege('anon',t.oid,'SELECT,INSERT,UPDATE,REFERENCES')
    or has_any_column_privilege('authenticated',t.oid,'SELECT,INSERT,UPDATE,REFERENCES')),false),
  'service_only_policies',not exists(select 1 from pg_policy p,unnest(p.polroles) r where p.polrelid=t.oid
    and (r=0 or r<>(select oid from pg_roles where rolname='service_role'))),
  'columns',coalesce((select jsonb_agg(jsonb_build_object('name',a.attname,'type',format_type(a.atttypid,a.atttypmod),'not_null',a.attnotnull,
    'default',pg_get_expr(d.adbin,d.adrelid))) from pg_attribute a left join pg_attrdef d on d.adrelid=a.attrelid and d.adnum=a.attnum
    where a.attrelid=t.oid and a.attnum>0 and not a.attisdropped),'[]'::jsonb),
  'constraints',coalesce((select jsonb_agg(jsonb_build_object('type',c.contype,'valid',c.convalidated,'definition',pg_get_constraintdef(c.oid)))
    from pg_constraint c where c.conrelid=t.oid),'[]'::jsonb),
  'indexes',coalesce((select jsonb_agg(jsonb_build_object('name',i.relname,'valid',x.indisvalid,'ready',x.indisready,'primary',x.indisprimary,
    'unique',x.indisunique,'definition',pg_get_indexdef(x.indexrelid))) from pg_index x join pg_class i on i.oid=x.indexrelid where x.indrelid=t.oid),'[]'::jsonb)
))) from tables t;
$probe$;
revoke all on function public.brand_studio_review_release_check() from public, anon, authenticated;
grant execute on function public.brand_studio_review_release_check() to service_role;
notify pgrst, 'reload schema';