acmcc/supabase/migrations/20260319222801_4cb0a1e5-81eb-45f7-9d51-9815d745b23e.sql

-- Helper function: returns all association_ids the current user belongs to
-- via either the owners table or the board_members table
CREATE OR REPLACE FUNCTION public.get_user_association_ids()
RETURNS SETOF uuid
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT DISTINCT association_id FROM (
    SELECT association_id FROM public.owners WHERE user_id = auth.uid()
    UNION
    SELECT association_id FROM public.board_members WHERE user_id = auth.uid()
  ) sub
$$;

-- ============================================================
-- ASSOCIATIONS: scope homeowners/board to their own HOAs
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view associations" ON public.associations;

CREATE POLICY "Staff can view all associations"
  ON public.associations FOR SELECT TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own associations"
  ON public.associations FOR SELECT TO authenticated
  USING (
    id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- OWNERS: homeowners see only their association's owners
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view owners" ON public.owners;

CREATE POLICY "Staff can view all owners"
  ON public.owners FOR SELECT TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own association owners"
  ON public.owners FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- UNITS: scope to association
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view units" ON public.units;

CREATE POLICY "Staff can view all units"
  ON public.units FOR SELECT TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own association units"
  ON public.units FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- VIOLATIONS: scope authenticated users to their association
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view violations" ON public.violations;

CREATE POLICY "Staff can view all violations"
  ON public.violations FOR SELECT TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own association violations"
  ON public.violations FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- BOARD_MEMBERS: scope to association
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view board_members" ON public.board_members;

CREATE POLICY "Staff can view all board_members"
  ON public.board_members FOR SELECT TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own association board_members"
  ON public.board_members FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- BOARD_VOTES: scope to association
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view board_votes" ON public.board_votes;

CREATE POLICY "Members can view own association board_votes"
  ON public.board_votes FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- ASSOCIATION_FAQS: scope to association
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can view association FAQs" ON public.association_faqs;
DROP POLICY IF EXISTS "Authenticated users can update association FAQs" ON public.association_faqs;
DROP POLICY IF EXISTS "Authenticated users can delete association FAQs" ON public.association_faqs;
DROP POLICY IF EXISTS "Authenticated users can insert association FAQs" ON public.association_faqs;

CREATE POLICY "Staff can manage association FAQs"
  ON public.association_faqs FOR ALL TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  )
  WITH CHECK (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
    OR has_role(auth.uid(), 'employee'::app_role)
  );

CREATE POLICY "Members can view own association FAQs"
  ON public.association_faqs FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- CALENDAR_EVENTS: add read access for members (staff already has ALL)
-- ============================================================
CREATE POLICY "Members can view own association calendar_events"
  ON public.calendar_events FOR SELECT TO authenticated
  USING (
    association_id IN (SELECT get_user_association_ids())
  );

-- ============================================================
-- ANNOUNCEMENTS: scope to association (announcements table has no
-- association_id, so we keep the existing policy but note this)
-- ============================================================
-- Announcements don't have association_id, keeping existing behavior

-- ============================================================
-- Also tighten board_members DELETE/UPDATE/INSERT to staff only
-- ============================================================
DROP POLICY IF EXISTS "Authenticated users can delete board_members" ON public.board_members;
DROP POLICY IF EXISTS "Authenticated users can update board_members" ON public.board_members;
DROP POLICY IF EXISTS "Authenticated users can insert board_members" ON public.board_members;

CREATE POLICY "Staff can manage board_members"
  ON public.board_members FOR ALL TO authenticated
  USING (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
  )
  WITH CHECK (
    has_role(auth.uid(), 'admin'::app_role)
    OR has_role(auth.uid(), 'manager'::app_role)
  );