acmcc/supabase/migrations/20260512012001_1a5cadcd-1609-47b1-b398-5287d83f3d6a.sql

CREATE OR REPLACE FUNCTION public.prevent_updates_on_finalized_arc()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF lower(COALESCE(OLD.status,'')) IN ('approved','denied') THEN
    IF public.has_role(auth.uid(), 'admin'::public.app_role) THEN
      RETURN NEW;
    END IF;

    RAISE EXCEPTION 'This ARC application has been finalized (approved or denied) and is locked from further changes.'
      USING ERRCODE = 'check_violation';
  END IF;

  RETURN NEW;
END;
$$;