supabase-brand-studio-basic-watches

-- Brand Studio release 025: weekly basic checks. Additive only.
--
-- brand_studio_basic_watches holds one optional weekly check per saved brand,
-- turned on by its owner. brand_studio_basic_watch_runs records each scheduled
-- occurrence once: (watch_id, occurrence_at) is unique, so racing or retried
-- sweeps cannot run the same check twice. The server writes both tables with
-- the service role; owners can only read their own rows. Deleting the brand or
-- the account deletes its checks and runs. A run keeps a link to the saved read
-- it produced; removing that read clears the link and keeps the run.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

CREATE TABLE IF NOT EXISTS public.brand_studio_basic_watches (
  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),
  cadence text NOT NULL DEFAULT 'weekly' CHECK (cadence IN ('weekly')),
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused')),
  paused_reason text CHECK (paused_reason IS NULL OR paused_reason IN ('owner', 'failing')),
  next_check_at timestamptz NOT NULL,
  last_check_at timestamptz,
  last_outcome text CHECK (last_outcome IS NULL OR last_outcome IN ('changed', 'unchanged', 'first_read', 'failed')),
  last_read_id uuid REFERENCES public.brand_studio_basic_reads(id) ON DELETE SET NULL,
  last_change_total integer CHECK (last_change_total IS NULL OR last_change_total >= 0),
  consecutive_failures integer NOT NULL DEFAULT 0 CHECK (consecutive_failures BETWEEN 0 AND 1000),
  lease_until timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT brand_studio_basic_watches_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_basic_watches_one_per_brand UNIQUE (user_id, brand_id),
  CONSTRAINT brand_studio_basic_watches_id_owner UNIQUE (id, user_id)
);

CREATE INDEX IF NOT EXISTS brand_studio_basic_watches_due_idx
  ON public.brand_studio_basic_watches (next_check_at) WHERE status = 'active';

CREATE TABLE IF NOT EXISTS public.brand_studio_basic_watch_runs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  watch_id uuid NOT NULL,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  occurrence_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('running', 'changed', 'unchanged', 'first_read', 'failed')),
  read_id uuid REFERENCES public.brand_studio_basic_reads(id) ON DELETE SET NULL,
  change_total integer CHECK (change_total IS NULL OR change_total >= 0),
  change_counts jsonb CHECK (change_counts IS NULL OR (jsonb_typeof(change_counts) = 'object' AND octet_length(change_counts::text) <= 2048)),
  reason text CHECK (reason IS NULL OR char_length(reason) <= 200),
  started_at timestamptz NOT NULL DEFAULT now(),
  finished_at timestamptz,
  CONSTRAINT brand_studio_basic_watch_runs_watch_owner_fk
    FOREIGN KEY (watch_id, user_id) REFERENCES public.brand_studio_basic_watches (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_basic_watch_runs_one_per_occurrence UNIQUE (watch_id, occurrence_at)
);

CREATE INDEX IF NOT EXISTS brand_studio_basic_watch_runs_user_started_idx
  ON public.brand_studio_basic_watch_runs (user_id, started_at DESC);

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

COMMIT;