supabase-brand-studio-search-watches

-- Brand Studio release 038: weekly search checks. Additive only.
--
-- brand_studio_search_watches holds an owner's standing approval to check one
-- saved brand's Google rankings weekly: the search market, the provider-cost
-- ceiling per snapshot the owner saw in a live quote, and the credits per
-- snapshot. Each check reads the provider's free Google data date and buys a
-- search snapshot only when that date is newer than the brand's latest search
-- snapshot. brand_studio_search_watch_runs records each scheduled check once
-- ((watch_id, occurrence_at) is unique), whether it waited or bought, and the
-- report it started. The server writes both with the service role; owners read
-- their own rows only.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

CREATE TABLE IF NOT EXISTS public.brand_studio_search_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', 'credits', 'limit', 'review')),
  market jsonb NOT NULL CHECK (jsonb_typeof(market) = 'object' AND octet_length(market::text) <= 256),
  max_cost_usd numeric(10, 6) NOT NULL CHECK (max_cost_usd > 0 AND max_cost_usd <= 3),
  credits_per_run integer NOT NULL CHECK (credits_per_run BETWEEN 1 AND 3),
  approved_at timestamptz NOT NULL,
  anchor_at timestamptz NOT NULL,
  next_check_at timestamptz NOT NULL,
  last_check_at timestamptz,
  last_outcome text CHECK (last_outcome IS NULL OR last_outcome IN ('started', 'waiting', 'refused', 'failed')),
  last_pull_id uuid,
  last_reason text CHECK (last_reason IS NULL OR char_length(last_reason) <= 200),
  last_google_data_date date,
  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_search_watches_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_search_watches_one_per_brand UNIQUE (user_id, brand_id),
  CONSTRAINT brand_studio_search_watches_id_owner UNIQUE (id, user_id)
);

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

CREATE TABLE IF NOT EXISTS public.brand_studio_search_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', 'started', 'waiting', 'refused', 'failed')),
  google_data_date date,
  previous_google_data_date date,
  pull_id uuid,
  plan_hash text CHECK (plan_hash IS NULL OR plan_hash ~ '^[a-f0-9]{64}$'),
  max_cost_usd numeric(10, 6),
  reason text CHECK (reason IS NULL OR char_length(reason) <= 200),
  started_at timestamptz NOT NULL DEFAULT now(),
  finished_at timestamptz,
  CONSTRAINT brand_studio_search_watch_runs_watch_owner_fk
    FOREIGN KEY (watch_id, user_id) REFERENCES public.brand_studio_search_watches (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_search_watch_runs_one_per_occurrence UNIQUE (watch_id, occurrence_at)
);

CREATE INDEX IF NOT EXISTS brand_studio_search_watch_runs_user_started_idx
  ON public.brand_studio_search_watch_runs (user_id, started_at DESC);

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

COMMIT;