supabase-brand-studio-release-2026-09-08

-- Release gate F05/F07. Apply ONLY to an explicitly authorized disposable DB
-- first, after the existing auth/pulls/billing/API-key migrations. No live DB
-- was modified by the implementation pass. Transactional, rerunnable.
BEGIN;
DO $$
DECLARE t text; col record;
BEGIN
  FOREACH t IN ARRAY ARRAY['brand_studio_pulls','brand_studio_brands','brand_studio_pull_diffs','brand_studio_subscriptions','brand_studio_plans','brand_studio_credit_ledger','brand_studio_api_keys','brand_studio_byo_keys'] LOOP
    IF to_regclass('public.' || t) IS NULL THEN
      IF t IN ('brand_studio_pulls','brand_studio_credit_ledger') THEN RAISE EXCEPTION 'required table missing: %', t; END IF;
      CONTINUE;
    END IF;
    EXECUTE format('ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('REVOKE ALL ON TABLE public.%I FROM PUBLIC, anon, authenticated', t);
    -- Explicitly reconcile column privileges as defense in depth. Restrictive
    -- policies also constrain grants inherited through other client roles.
    FOR col IN SELECT attname FROM pg_attribute WHERE attrelid=to_regclass('public.'||t) AND attnum>0 AND NOT attisdropped LOOP
      EXECUTE format('REVOKE ALL (%I) ON public.%I FROM PUBLIC, anon, authenticated', col.attname, t);
    END LOOP;
    EXECUTE format('DROP POLICY IF EXISTS release_deny_client_insert ON public.%I', t);
    EXECUTE format('DROP POLICY IF EXISTS release_deny_client_update ON public.%I', t);
    EXECUTE format('DROP POLICY IF EXISTS release_deny_client_delete ON public.%I', t);
    EXECUTE format('CREATE POLICY release_deny_client_insert ON public.%I AS RESTRICTIVE FOR INSERT TO anon, authenticated WITH CHECK (false)', t);
    EXECUTE format('CREATE POLICY release_deny_client_update ON public.%I AS RESTRICTIVE FOR UPDATE TO anon, authenticated USING (false) WITH CHECK (false)', t);
    EXECUTE format('CREATE POLICY release_deny_client_delete ON public.%I AS RESTRICTIVE FOR DELETE TO anon, authenticated USING (false)', t);
    -- Authenticated SELECT remains owner-bound by the existing RLS policy.
    IF t NOT IN ('brand_studio_api_keys','brand_studio_byo_keys') THEN
      EXECUTE format('GRANT SELECT ON public.%I TO authenticated', t);
    END IF;
    EXECUTE format('GRANT ALL ON TABLE public.%I TO service_role', t);
  END LOOP;
END $$;
-- No SECURITY DEFINER elevation: ONLY service_role can invoke the snapshot.
-- Group-free entries preserve spend->pull linkage used by outstanding liability.
-- JSON singleton bypasses PostgREST max-rows while remaining one MVCC snapshot.
CREATE OR REPLACE FUNCTION public.brand_studio_ledger_snapshot(p_user_id uuid)
RETURNS jsonb LANGUAGE sql STABLE SECURITY INVOKER SET search_path = '' AS $$
  WITH rows AS MATERIALIZED (
    SELECT kind, amount, pull_id FROM public.brand_studio_credit_ledger
    WHERE user_id=p_user_id ORDER BY id LIMIT 100001
  )
  SELECT CASE WHEN count(*)>100000 THEN jsonb_build_object('error','ledger_limit_exceeded')
    ELSE jsonb_build_object('entries',coalesce(jsonb_agg(to_jsonb(rows)), '[]'::jsonb),
      'row_count',count(*),'snapshot_at',statement_timestamp()) END FROM rows;
$$;
REVOKE ALL ON FUNCTION public.brand_studio_ledger_snapshot(uuid) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.brand_studio_ledger_snapshot(uuid) TO service_role;
COMMIT;