supabase-brand-studio-social-watches

-- Brand Studio release 036: scheduled social snapshots. Additive only.
--
-- brand_studio_social_watches holds an owner's standing approval to take a
-- weekly social snapshot of one saved brand: the confirmed accounts, the
-- provider-cost ceiling per run the owner saw in a live quote, and the credits
-- per run. brand_studio_social_watch_runs records each scheduled occurrence
-- once ((watch_id, occurrence_at) is unique) 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_social_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')),
  profiles jsonb NOT NULL CHECK (jsonb_typeof(profiles) = 'array' AND jsonb_array_length(profiles) BETWEEN 1 AND 5 AND octet_length(profiles::text) <= 4096),
  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_social_watches_brand_owner_fk
    FOREIGN KEY (brand_id, user_id) REFERENCES public.brand_studio_brands (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_social_watches_one_per_brand UNIQUE (user_id, brand_id),
  CONSTRAINT brand_studio_social_watches_id_owner UNIQUE (id, user_id)
);

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

CREATE TABLE IF NOT EXISTS public.brand_studio_social_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_social_watch_runs_watch_owner_fk
    FOREIGN KEY (watch_id, user_id) REFERENCES public.brand_studio_social_watches (id, user_id) ON DELETE CASCADE,
  CONSTRAINT brand_studio_social_watch_runs_one_per_occurrence UNIQUE (watch_id, occurrence_at)
);

CREATE INDEX IF NOT EXISTS brand_studio_social_watch_runs_user_started_idx
  ON public.brand_studio_social_watch_runs (user_id, started_at DESC);

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

COMMIT;