supabase-brand-studio-site-pages

-- Brand Studio release 040: website page lists from the brand's own sitemaps. Additive only.
--
-- brand_studio_site_pages records each free read of a saved brand's declared
-- sitemaps (by hand, or by the weekly basic check): the page count, whether
-- the read was complete, section counts, what changed since the previous read
-- (pages newly listed, no longer listed, and updated by the site's own lastmod
-- dates), and the page list itself, gzip-compressed. A read whose page list is
-- identical to the previous one stores no list and points at the row that has
-- it. The server writes rows with the service role; owners read their own.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

CREATE TABLE IF NOT EXISTS public.brand_studio_site_pages (
  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 CHECK (char_length(source_url) BETWEEN 1 AND 2048),
  via text NOT NULL CHECK (via IN ('read', 'weekly_check')),
  observed_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('complete', 'incomplete')),
  page_count integer NOT NULL CHECK (page_count BETWEEN 0 AND 100000),
  pages_with_dates integer NOT NULL DEFAULT 0 CHECK (pages_with_dates BETWEEN 0 AND 100000),
  summary jsonb NOT NULL CHECK (jsonb_typeof(summary) = 'object' AND octet_length(summary::text) <= 65536),
  changes jsonb CHECK (changes IS NULL OR (jsonb_typeof(changes) = 'object' AND octet_length(changes::text) <= 131072)),
  change_counts jsonb CHECK (change_counts IS NULL OR (jsonb_typeof(change_counts) = 'object' AND octet_length(change_counts::text) <= 1024)),
  pages_gz text CHECK (pages_gz IS NULL OR char_length(pages_gz) <= 4000000),
  pages_sha256 text NOT NULL CHECK (pages_sha256 ~ '^[a-f0-9]{64}$'),
  same_pages_as uuid,
  previous_id uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT brand_studio_site_pages_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_site_pages_list_kept
    CHECK (pages_gz IS NOT NULL OR same_pages_as IS NOT NULL OR page_count = 0)
);

CREATE INDEX IF NOT EXISTS brand_studio_site_pages_brand_observed_idx
  ON public.brand_studio_site_pages (user_id, brand_id, observed_at DESC);
CREATE INDEX IF NOT EXISTS brand_studio_site_pages_user_created_idx
  ON public.brand_studio_site_pages (user_id, created_at DESC);

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

COMMIT;