acmcc/supabase/migrations/20260410204527_5157badc-32c1-425f-9f0c-b1ac1d4d7e07.sql

-- =====================================================
-- Fix permissive RLS policies: restrict staff-only tables
-- to admin/manager roles using the existing has_role() function
-- =====================================================

-- Helper: the staff check condition is:
--   public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager')

-- 1. announcements
DROP POLICY IF EXISTS "Authenticated users can insert announcements" ON public.announcements;
CREATE POLICY "Staff can insert announcements" ON public.announcements
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 2. annual_meetings
DROP POLICY IF EXISTS "Authenticated users can create annual meetings" ON public.annual_meetings;
CREATE POLICY "Staff can create annual meetings" ON public.annual_meetings
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can update annual meetings" ON public.annual_meetings;
CREATE POLICY "Staff can update annual meetings" ON public.annual_meetings
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can delete annual meetings" ON public.annual_meetings;
CREATE POLICY "Staff can delete annual meetings" ON public.annual_meetings
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 3. billable_expenses
DROP POLICY IF EXISTS "Authenticated users can insert billable_expenses" ON public.billable_expenses;
CREATE POLICY "Staff can insert billable_expenses" ON public.billable_expenses
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can update billable_expenses" ON public.billable_expenses;
CREATE POLICY "Staff can update billable_expenses" ON public.billable_expenses
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can delete billable_expenses" ON public.billable_expenses;
CREATE POLICY "Staff can delete billable_expenses" ON public.billable_expenses
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 4. buildium_gl_mappings
DROP POLICY IF EXISTS "Authenticated users can manage buildium GL mappings" ON public.buildium_gl_mappings;
CREATE POLICY "Staff can manage buildium GL mappings" ON public.buildium_gl_mappings
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 5. bundle_expenses
DROP POLICY IF EXISTS "Authenticated users can manage bundle_expenses" ON public.bundle_expenses;
CREATE POLICY "Staff can manage bundle_expenses" ON public.bundle_expenses
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 6. chart_of_accounts
DROP POLICY IF EXISTS "Authenticated users can insert chart_of_accounts" ON public.chart_of_accounts;
CREATE POLICY "Staff can insert chart_of_accounts" ON public.chart_of_accounts
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can update chart_of_accounts" ON public.chart_of_accounts;
CREATE POLICY "Staff can update chart_of_accounts" ON public.chart_of_accounts
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can delete chart_of_accounts" ON public.chart_of_accounts;
CREATE POLICY "Staff can delete chart_of_accounts" ON public.chart_of_accounts
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 7. custom_ledgers
DROP POLICY IF EXISTS "Authenticated users can manage custom_ledgers" ON public.custom_ledgers;
CREATE POLICY "Staff can manage custom_ledgers" ON public.custom_ledgers
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 8. email_routing_rules
DROP POLICY IF EXISTS "Authenticated users manage routing" ON public.email_routing_rules;
CREATE POLICY "Staff can manage routing" ON public.email_routing_rules
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 9. email_server_settings
DROP POLICY IF EXISTS "Authenticated users manage server settings" ON public.email_server_settings;
CREATE POLICY "Staff can manage server settings" ON public.email_server_settings
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 10. email_templates
DROP POLICY IF EXISTS "Authenticated users manage templates" ON public.email_templates;
CREATE POLICY "Staff can manage templates" ON public.email_templates
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 11. expense_bundles
DROP POLICY IF EXISTS "Authenticated users can manage expense_bundles" ON public.expense_bundles;
CREATE POLICY "Staff can manage expense_bundles" ON public.expense_bundles
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 12. expense_categories
DROP POLICY IF EXISTS "Authenticated users can manage expense categories" ON public.expense_categories;
CREATE POLICY "Staff can manage expense categories" ON public.expense_categories
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 13. expense_subcategories
DROP POLICY IF EXISTS "Authenticated users can manage expense subcategories" ON public.expense_subcategories;
CREATE POLICY "Staff can manage expense subcategories" ON public.expense_subcategories
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 14. fee_schedule_subcategories
DROP POLICY IF EXISTS "Authenticated users can manage subcategories" ON public.fee_schedule_subcategories;
CREATE POLICY "Staff can manage fee schedule subcategories" ON public.fee_schedule_subcategories
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 15. fee_schedules
DROP POLICY IF EXISTS "Authenticated users can manage fee schedules" ON public.fee_schedules;
CREATE POLICY "Staff can manage fee schedules" ON public.fee_schedules
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 16. journal_entries
DROP POLICY IF EXISTS "Authenticated users can insert journal_entries" ON public.journal_entries;
CREATE POLICY "Staff can insert journal_entries" ON public.journal_entries
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can update journal_entries" ON public.journal_entries;
CREATE POLICY "Staff can update journal_entries" ON public.journal_entries
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can delete journal_entries" ON public.journal_entries;
CREATE POLICY "Staff can delete journal_entries" ON public.journal_entries
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 17. notify_board_templates
DROP POLICY IF EXISTS "Authenticated users manage board templates" ON public.notify_board_templates;
CREATE POLICY "Staff can manage board templates" ON public.notify_board_templates
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 18. owner_notification_proofs
DROP POLICY IF EXISTS "Authenticated users manage proofs" ON public.owner_notification_proofs;
CREATE POLICY "Staff can manage proofs" ON public.owner_notification_proofs
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 19. owner_notification_templates
DROP POLICY IF EXISTS "Authenticated users manage notification templates" ON public.owner_notification_templates;
CREATE POLICY "Staff can manage notification templates" ON public.owner_notification_templates
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 20. public_form_submissions (staff manage policy only - keep anon insert)
DROP POLICY IF EXISTS "Staff can manage form submissions" ON public.public_form_submissions;
CREATE POLICY "Staff can manage form submissions" ON public.public_form_submissions
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 21. public_form_templates
DROP POLICY IF EXISTS "Staff can manage form templates" ON public.public_form_templates;
CREATE POLICY "Staff can manage form templates" ON public.public_form_templates
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 22. saved_letters
DROP POLICY IF EXISTS "Authenticated users can manage saved_letters" ON public.saved_letters;
CREATE POLICY "Staff can manage saved_letters" ON public.saved_letters
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 23. unit_general_info
DROP POLICY IF EXISTS "Authenticated users can manage unit_general_info" ON public.unit_general_info;
CREATE POLICY "Staff can manage unit_general_info" ON public.unit_general_info
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 24. unit_occupants
DROP POLICY IF EXISTS "Authenticated users can manage unit_occupants" ON public.unit_occupants;
CREATE POLICY "Staff can manage unit_occupants" ON public.unit_occupants
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 25. unit_parking
DROP POLICY IF EXISTS "Authenticated users can manage unit_parking" ON public.unit_parking;
CREATE POLICY "Staff can manage unit_parking" ON public.unit_parking
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 26. unit_pets
DROP POLICY IF EXISTS "Authenticated users can manage unit_pets" ON public.unit_pets;
CREATE POLICY "Staff can manage unit_pets" ON public.unit_pets
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 27. unit_tenants
DROP POLICY IF EXISTS "Authenticated users can manage unit_tenants" ON public.unit_tenants;
CREATE POLICY "Staff can manage unit_tenants" ON public.unit_tenants
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 28. unit_timeline_events
DROP POLICY IF EXISTS "Authenticated users can create timeline events" ON public.unit_timeline_events;
CREATE POLICY "Staff can create timeline events" ON public.unit_timeline_events
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can update timeline events" ON public.unit_timeline_events;
CREATE POLICY "Staff can update timeline events" ON public.unit_timeline_events
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

DROP POLICY IF EXISTS "Authenticated users can delete timeline events" ON public.unit_timeline_events;
CREATE POLICY "Staff can delete timeline events" ON public.unit_timeline_events
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 29. vendor_coa_mappings
DROP POLICY IF EXISTS "Authenticated users can manage vendor COA mappings" ON public.vendor_coa_mappings;
CREATE POLICY "Staff can manage vendor COA mappings" ON public.vendor_coa_mappings
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 30. violation_types (two duplicate policies)
DROP POLICY IF EXISTS "Authenticated users can manage violation types" ON public.violation_types;
DROP POLICY IF EXISTS "Authenticated users can manage violation_types" ON public.violation_types;
CREATE POLICY "Staff can manage violation_types" ON public.violation_types
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 31. workflow_template_tasks
DROP POLICY IF EXISTS "Authenticated users can manage workflow_template_tasks" ON public.workflow_template_tasks;
CREATE POLICY "Staff can manage workflow_template_tasks" ON public.workflow_template_tasks
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- 32. workflow_templates
DROP POLICY IF EXISTS "Authenticated users can manage workflow_templates" ON public.workflow_templates;
CREATE POLICY "Staff can manage workflow_templates" ON public.workflow_templates
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
  WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));

-- NOTE: The following anon/public policies are intentionally left unchanged:
-- amenity_bookings INSERT (public booking form)
-- amenity_form_submissions INSERT (public form)
-- election_audit_log INSERT (audit trail)
-- election_ballots INSERT/DELETE (voting flow)
-- election_eligible_voters UPDATE (voting flow)
-- form_inbox INSERT (trigger-driven)
-- public_form_submissions INSERT (public form)
-- violation_responses INSERT (public response page)
-- bill_comments INSERT (user-scoped, authenticated)