supabase-brand-studio-site-page-reads

-- Brand Studio release 041: re-reads of changed website pages. Additive only.
--
-- When a website page list read (brand_studio_site_pages) finds pages newly
-- listed or updated, a few of them are read again from public HTML, free, and
-- compared with the previous read of the same page: title, main heading,
-- description, word count and the sentences added or removed. Only the latest
-- text of each page is kept (older rows keep their hash and comparison). 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_page_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),
  site_read_id uuid NOT NULL REFERENCES public.brand_studio_site_pages(id) ON DELETE CASCADE,
  url text NOT NULL CHECK (char_length(url) BETWEEN 1 AND 2048),
  final_url text CHECK (final_url IS NULL OR char_length(final_url) <= 2048),
  kind text NOT NULL CHECK (kind IN ('updated', 'newly_listed', 'newly_seen')),
  lastmod text CHECK (lastmod IS NULL OR char_length(lastmod) <= 40),
  observed_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('read', 'blocked', 'not_found', 'no_static_text', 'redirected_off_site', 'failed')),
  reason text CHECK (reason IS NULL OR char_length(reason) <= 200),
  title text CHECK (title IS NULL OR char_length(title) <= 300),
  h1 text CHECK (h1 IS NULL OR char_length(h1) <= 300),
  description text CHECK (description IS NULL OR char_length(description) <= 600),
  word_count integer CHECK (word_count IS NULL OR word_count >= 0),
  text_sha256 text CHECK (text_sha256 IS NULL OR text_sha256 ~ '^[a-f0-9]{64}$'),
  text text CHECK (text IS NULL OR char_length(text) <= 30000),
  comparison jsonb CHECK (comparison IS NULL OR (jsonb_typeof(comparison) = 'object' AND octet_length(comparison::text) <= 32768)),
  previous_id uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT brand_studio_site_page_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_site_page_reads_page_idx
  ON public.brand_studio_site_page_reads (user_id, brand_id, url, observed_at DESC);
CREATE INDEX IF NOT EXISTS brand_studio_site_page_reads_site_read_idx
  ON public.brand_studio_site_page_reads (site_read_id);

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

COMMIT;