supabase-brand-studio-sweep-health

-- Additive shared scheduler lease and bounded latest health, service access only.
CREATE TABLE IF NOT EXISTS public.brand_studio_monitoring_sweeps (
  lane text PRIMARY KEY CHECK (lane IN ('basic','search','social','review','notification')),
  holder text,
  lease_token uuid,
  lease_until timestamptz,
  last_run_at timestamptz,
  last_finished_at timestamptz,
  last_skipped_at timestamptz,
  last_skipped_reason text,
  overlap_skips bigint NOT NULL DEFAULT 0,
  last_result jsonb NOT NULL DEFAULT '{}'::jsonb
);
ALTER TABLE public.brand_studio_monitoring_sweeps ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.brand_studio_monitoring_sweeps
  ADD COLUMN IF NOT EXISTS last_skipped_at timestamptz,
  ADD COLUMN IF NOT EXISTS last_skipped_reason text,
  ADD COLUMN IF NOT EXISTS overlap_skips bigint NOT NULL DEFAULT 0;
REVOKE ALL ON public.brand_studio_monitoring_sweeps FROM PUBLIC, anon, authenticated;
GRANT SELECT ON public.brand_studio_monitoring_sweeps TO service_role;

CREATE OR REPLACE FUNCTION public.brand_studio_monitoring_sweep_claim(p_lane text,p_holder text)
RETURNS SETOF public.brand_studio_monitoring_sweeps
LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  IF p_lane NOT IN ('basic','search','social','review','notification') OR length(p_holder)>80 THEN
    RAISE EXCEPTION 'invalid_sweep';
  END IF;
  INSERT INTO public.brand_studio_monitoring_sweeps(lane) VALUES(p_lane) ON CONFLICT DO NOTHING;
  RETURN QUERY UPDATE public.brand_studio_monitoring_sweeps
    SET holder=p_holder,lease_token=gen_random_uuid(),lease_until=now()+interval '10 minutes',last_run_at=now()
    WHERE lane=p_lane AND (lease_until IS NULL OR lease_until<now()) RETURNING *;
  IF NOT FOUND THEN
    UPDATE public.brand_studio_monitoring_sweeps SET last_skipped_at=now(),last_skipped_reason='sweep_lease_busy',overlap_skips=overlap_skips+1 WHERE lane=p_lane;
  END IF;
END $$;

CREATE OR REPLACE FUNCTION public.brand_studio_monitoring_sweep_finish(p_lane text,p_lease uuid,p_result jsonb)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  IF jsonb_typeof(p_result)<>'object' OR octet_length(p_result::text)>2048 THEN RAISE EXCEPTION 'invalid_sweep_result'; END IF;
  UPDATE public.brand_studio_monitoring_sweeps
    SET lease_token=NULL,lease_until=NULL,last_finished_at=now(),last_result=p_result
    WHERE lane=p_lane AND lease_token=p_lease;
  RETURN FOUND;
END $$;
REVOKE ALL ON FUNCTION public.brand_studio_monitoring_sweep_claim(text,text),public.brand_studio_monitoring_sweep_finish(text,uuid,jsonb) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.brand_studio_monitoring_sweep_claim(text,text),public.brand_studio_monitoring_sweep_finish(text,uuid,jsonb) TO service_role;