acmcc/supabase/migrations/20260428014702_f586298b-d88d-4639-9c07-8d1226cf2a8e.sql

-- Enable RLS on realtime.messages (Supabase Realtime authorization table)
ALTER TABLE realtime.messages ENABLE ROW LEVEL SECURITY;

-- Drop any existing policies to start clean
DO $$
DECLARE
  pol record;
BEGIN
  FOR pol IN
    SELECT policyname FROM pg_policies
    WHERE schemaname = 'realtime' AND tablename = 'messages'
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON realtime.messages', pol.policyname);
  END LOOP;
END $$;

-- Helper: check if a realtime topic is authorized for the current user
CREATE OR REPLACE FUNCTION public.realtime_topic_authorized(_topic text)
RETURNS boolean
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  _uid uuid := auth.uid();
  _assoc uuid;
BEGIN
  IF _uid IS NULL THEN
    RETURN false;
  END IF;

  -- Staff (admin/manager) can subscribe to any channel
  IF public.has_role(_uid, 'admin'::public.app_role)
     OR public.has_role(_uid, 'manager'::public.app_role) THEN
    RETURN true;
  END IF;

  IF _topic IS NULL OR _topic = '' THEN
    RETURN false;
  END IF;

  -- Channels containing the user's own UID (e.g. dm-unread-<uid>, notifications-<uid>)
  IF position(_uid::text in _topic) > 0 THEN
    RETURN true;
  END IF;

  -- Channels containing an association UUID the user belongs to
  FOR _assoc IN SELECT public.get_user_association_ids() LOOP
    IF _assoc IS NOT NULL AND position(_assoc::text in _topic) > 0 THEN
      RETURN true;
    END IF;
  END LOOP;

  -- Legal users: allow channels for their assigned associations
  IF public.has_role(_uid, 'legal'::public.app_role) THEN
    FOR _assoc IN SELECT public.get_legal_association_ids(_uid) LOOP
      IF _assoc IS NOT NULL AND position(_assoc::text in _topic) > 0 THEN
        RETURN true;
      END IF;
    END LOOP;
  END IF;

  RETURN false;
END;
$$;

-- Restrict EXECUTE to authenticated users only
REVOKE EXECUTE ON FUNCTION public.realtime_topic_authorized(text) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.realtime_topic_authorized(text) TO authenticated;

-- Policy: authenticated users can only access messages on authorized topics
CREATE POLICY "Authenticated users can read authorized topics"
ON realtime.messages
FOR SELECT
TO authenticated
USING (public.realtime_topic_authorized((realtime.topic())::text));

CREATE POLICY "Authenticated users can send to authorized topics"
ON realtime.messages
FOR INSERT
TO authenticated
WITH CHECK (public.realtime_topic_authorized((realtime.topic())::text));