acmcc/supabase/migrations/20260607160000_only_admins_mark_bills_paid.sql

-- Restrict the "paid" transition on bills to administrators only.
--
-- Background: bills RLS lets admin, manager, association_management, and
-- accounting staff update bills, so any of them could mark a bill paid. Per
-- requirement, only admins may do that. This trigger enforces it at the data
-- layer regardless of how the write arrives (UI, API, direct SQL).
--
-- System/automation contexts (buildium-sync, accounting triggers, autopay)
-- run with the service role, where auth.uid() is NULL — those remain allowed
-- so imports and back-syncs continue to work.

create or replace function public.enforce_admin_marks_bill_paid()
returns trigger
language plpgsql
security definer
set search_path to 'public'
as $$
begin
  if NEW.status = 'paid'
     and (TG_OP = 'INSERT' or OLD.status is distinct from 'paid') then
    if auth.uid() is not null
       and not public.has_role(auth.uid(), 'admin'::app_role) then
      raise exception 'Only administrators can mark a bill as paid'
        using errcode = 'check_violation';
    end if;
  end if;
  return NEW;
end;
$$;

drop trigger if exists trg_bills_admin_paid_guard on public.bills;
create trigger trg_bills_admin_paid_guard
  before insert or update on public.bills
  for each row execute function public.enforce_admin_marks_bill_paid();