moviedice/supabase/migrations/00005_movies_update_recursion_fix.sql

-- Fix infinite recursion in movies UPDATE policy.
--
-- The original WITH CHECK clause referenced `public.movies` in a subquery
-- (to enforce that added_by cannot change). Selecting from movies while the
-- movies_update policy is being evaluated triggers RLS recursion (42P17).
--
-- Replace the self-referencing check with a BEFORE UPDATE trigger that
-- forbids mutating added_by at the row level. Group-membership check stays
-- in the policy.

DROP POLICY IF EXISTS movies_update ON public.movies;

CREATE POLICY movies_update ON public.movies
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.group_members
      WHERE group_members.group_id = movies.group_id
        AND group_members.user_id = auth.uid()
    )
  );

CREATE OR REPLACE FUNCTION public.movies_prevent_added_by_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.added_by IS DISTINCT FROM OLD.added_by THEN
    RAISE EXCEPTION 'movies.added_by is immutable';
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS movies_added_by_immutable ON public.movies;

CREATE TRIGGER movies_added_by_immutable
  BEFORE UPDATE ON public.movies
  FOR EACH ROW
  EXECUTE FUNCTION public.movies_prevent_added_by_change();