moviedice/supabase/migrations/00006_movies_realtime_replica_identity.sql

-- Enable Supabase Realtime UPDATE payloads for movies.
--
-- Two requirements for cross-window real-time UPDATE propagation:
--
-- 1. The table must be a member of the `supabase_realtime` publication.
--    Without this, no postgres_changes events are emitted at all.
--
-- 2. REPLICA IDENTITY FULL is required for UPDATE payloads to include the
--    full new row. With the default (REPLICA IDENTITY DEFAULT, which logs
--    only the primary key), `payload.new` arrives with only `id` populated
--    and our TanStack Query cache merge replaces a fully-populated row with
--    a sparse one, losing the `watched` flag and other fields.
--
-- INSERT and DELETE work without FULL because INSERT carries the full new
-- row regardless and DELETE only needs the PK to identify the row to remove.
-- That matches the user-reported symptom (only UPDATE was broken).

ALTER TABLE public.movies REPLICA IDENTITY FULL;

-- Idempotent publication membership: ADD TABLE errors if already present,
-- so guard with a DO block that checks pg_publication_tables first.
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_publication_tables
    WHERE pubname = 'supabase_realtime'
      AND schemaname = 'public'
      AND tablename = 'movies'
  ) THEN
    EXECUTE 'ALTER PUBLICATION supabase_realtime ADD TABLE public.movies';
  END IF;
END
$$;