supabase-brand-studio-review-watches

-- DRAFT: owner-approved weekly ten-newest review samples. Apply only with release coordination.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

CREATE TABLE IF NOT EXISTS public.brand_studio_review_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')),
  profile jsonb NOT NULL CHECK (jsonb_typeof(profile) = 'object' AND octet_length(profile::text) <= 4096 AND profile @> '{"confirmed":true}'::jsonb),
  market jsonb NOT NULL CHECK (jsonb_typeof(market) = 'object' AND market @> '{"confirmed":true}'::jsonb),
  plan_hash text NOT NULL CHECK (plan_hash ~ '^[a-f0-9]{64}$'),
  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', 'refused', 'failed')),
  last_pull_id uuid,
  last_reason text CHECK (last_reason IS NULL OR char_length(last_reason) <= 200),
  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_review_watches_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_review_watches_one_per_brand UNIQUE (user_id, brand_id),
  CONSTRAINT brand_studio_review_watches_id_owner UNIQUE (id, user_id)
);

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

CREATE TABLE IF NOT EXISTS public.brand_studio_review_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', 'refused', 'failed')),
  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_review_watch_runs_watch_owner_fk
    FOREIGN KEY (watch_id, user_id) REFERENCES public.brand_studio_review_watches (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_review_watch_runs_one_per_occurrence UNIQUE (watch_id, occurrence_at)
);

ALTER TABLE public.brand_studio_review_watch_runs
  ADD COLUMN IF NOT EXISTS sample_scope jsonb NOT NULL DEFAULT '{"review_limit":10,"sort":"newest","overlap_possible":true,"interval_complete":false}'::jsonb,
  ADD COLUMN IF NOT EXISTS listing jsonb,
  ADD COLUMN IF NOT EXISTS market jsonb;

CREATE INDEX IF NOT EXISTS brand_studio_review_watch_runs_user_started_idx
  ON public.brand_studio_review_watch_runs (user_id, started_at DESC);

ALTER TABLE public.brand_studio_review_watches ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.brand_studio_review_watch_runs ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public.brand_studio_review_watches FROM PUBLIC, anon, authenticated;
REVOKE ALL ON TABLE public.brand_studio_review_watch_runs FROM PUBLIC, anon, authenticated;
DROP POLICY IF EXISTS brand_studio_review_watches_select_own ON public.brand_studio_review_watches;
CREATE POLICY brand_studio_review_watches_select_own ON public.brand_studio_review_watches
  FOR SELECT TO authenticated USING (user_id = (SELECT auth.uid()));
DROP POLICY IF EXISTS brand_studio_review_watch_runs_select_own ON public.brand_studio_review_watch_runs;
CREATE POLICY brand_studio_review_watch_runs_select_own ON public.brand_studio_review_watch_runs
  FOR SELECT TO authenticated USING (user_id = (SELECT auth.uid()));
GRANT SELECT ON TABLE public.brand_studio_review_watches TO authenticated;
GRANT SELECT ON TABLE public.brand_studio_review_watch_runs TO authenticated;
-- New schedules must use the owner-fenced creation function, including old workers.
REVOKE INSERT ON TABLE public.brand_studio_review_watches FROM service_role;
GRANT SELECT, UPDATE, DELETE ON TABLE public.brand_studio_review_watches TO service_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.brand_studio_review_watch_runs TO service_role;

-- Serialize creation for one owner. A REST count followed by INSERT races at nine.
-- Lock the durable owner row so even different brands share the same fence.
CREATE OR REPLACE FUNCTION public.brand_studio_create_review_watch(
  p_user_id uuid, p_brand_id uuid, p_domain text, p_watch jsonb
) RETURNS SETOF public.brand_studio_review_watches
LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  PERFORM 1 FROM auth.users WHERE id = p_user_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION USING ERRCODE = '23503', MESSAGE = 'review_watch_owner_not_found';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM public.brand_studio_brands
    WHERE id = p_brand_id AND user_id = p_user_id AND domain = p_domain) THEN
    RAISE EXCEPTION USING ERRCODE = '23503', MESSAGE = 'review_watch_brand_not_owned';
  END IF;
  IF (SELECT count(*) FROM public.brand_studio_review_watches WHERE user_id = p_user_id) >= 10 THEN
    RAISE EXCEPTION USING ERRCODE = 'P0010', MESSAGE = 'schedule_limit_reached';
  END IF;
  RETURN QUERY INSERT INTO public.brand_studio_review_watches (
    user_id, brand_id, domain, cadence, status, profile, market, plan_hash,
    max_cost_usd, credits_per_run, approved_at, anchor_at, next_check_at, created_at, updated_at
  ) VALUES (
    p_user_id, p_brand_id, p_domain, 'weekly', 'active', p_watch->'profile', p_watch->'market',
    p_watch->>'plan_hash', (p_watch->>'max_cost_usd')::numeric, (p_watch->>'credits_per_run')::integer,
    (p_watch->>'approved_at')::timestamptz, (p_watch->>'anchor_at')::timestamptz,
    (p_watch->>'next_check_at')::timestamptz, (p_watch->>'updated_at')::timestamptz,
    (p_watch->>'updated_at')::timestamptz
  ) RETURNING *;
END;
$$;
REVOKE ALL ON FUNCTION public.brand_studio_create_review_watch(uuid, uuid, text, jsonb) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.brand_studio_create_review_watch(uuid, uuid, text, jsonb) TO service_role;

COMMIT;