supabase-brand-studio-basic-reads

-- Brand Studio release 022: saved basic brand reads. Additive only.
--
-- One row is one free homepage read (logo, graded palette, typography, homepage
-- image references, linked social profiles) that an account chose to keep. The
-- server runs the read itself and writes the row with the service role; clients
-- can only read their own rows. Deleting the brand or the account deletes them.
-- The composite foreign key makes it impossible to file a read under another
-- account's brand, the same way brand_studio_pulls does.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

CREATE TABLE IF NOT EXISTS public.brand_studio_basic_reads (
  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,
  domain text NOT NULL CHECK (char_length(domain) BETWEEN 1 AND 253),
  source_url text NOT NULL DEFAULT '' CHECK (char_length(source_url) <= 2048),
  schema_version text NOT NULL CHECK (schema_version ~ '^brand-studio-basic-read/v[0-9]+$'),
  observed_at timestamptz NOT NULL,
  summary jsonb NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(summary) = 'object' AND octet_length(summary::text) <= 16384),
  read jsonb NOT NULL CHECK (jsonb_typeof(read) = 'object' AND octet_length(read::text) <= 524288),
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT brand_studio_basic_reads_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS brand_studio_basic_reads_user_created_idx
  ON public.brand_studio_basic_reads (user_id, created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS brand_studio_basic_reads_brand_created_idx
  ON public.brand_studio_basic_reads (brand_id, created_at DESC);

ALTER TABLE public.brand_studio_basic_reads ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public.brand_studio_basic_reads FROM PUBLIC, anon, authenticated;
DROP POLICY IF EXISTS brand_studio_basic_reads_select_own ON public.brand_studio_basic_reads;
CREATE POLICY brand_studio_basic_reads_select_own ON public.brand_studio_basic_reads
  FOR SELECT TO authenticated USING (user_id = (SELECT auth.uid()));
GRANT SELECT ON TABLE public.brand_studio_basic_reads TO authenticated;
GRANT SELECT, INSERT, DELETE ON TABLE public.brand_studio_basic_reads TO service_role;

COMMIT;