The Complete Production SOP: How to Deploy and Run Supabase Realtime PostgreSQL CDC Broadcasts & Presence in Autonomous AI Agents

---
name: "supabase-realtime-postgres-sync"
description: "Stream live database changes and user presence states directly to web clients with Supabase."
metadata:
  author: "Supabase"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "subscribe postgres_changes supabase"
  - "supabase presence tracking"
  - "supabase realtime broadcast"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

# Supabase Realtime PostgreSQL CDC Broadcasts & Presence

Implements Supabase Realtime: subscribing to PostgreSQL table changes (postgres_changes), client-to-client broadcasts (broadcast), tracking online user presence (presence), and managing channel cleanup.

## Execution SOP & Rules
1. Ensure table replication is enabled: `ALTER PUBLICATION supabase_realtime ADD TABLE <table_name>;`
2. Create channel: `const channel = supabase.channel('<room_name>');`
3. Listen to DB changes: `channel.on('postgres_changes', { event: 'INSERT', schema: 'public', table: '<table_name>' }, handleNewMessage);`
4. Track presence: `channel.on('presence', { event: 'sync' }, () => { const state = channel.presenceState(); }).subscribe();`
5. Always unsubscribe on component unmount: `supabase.removeChannel(channel);`

import { createClient } from '@supabase/supabase-js';

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY;

if (!supabaseUrl || !supabaseAnonKey) {
  throw new Error('Missing Supabase Environment Variables in lib/supabaseClient.ts');
}

export const supabase = createClient(supabaseUrl, supabaseAnonKey, {
  realtime: {
    params: {
      eventsPerSecond: 10,
    },
  },
});

import { useEffect, useState } from 'react';
import { supabase } from '@/lib/supabaseClient';
import { RealtimeChannel } from '@supabase/supabase-js';

export interface Message {
  id: string;
  room_id: string;
  content: string;
  created_at: string;
}

export function useRealtimeMessages(roomId: string) {
  const [messages, setMessages] = useState<Message[]>([]);

  useEffect(() => {
    const channel: RealtimeChannel = supabase
      .channel(`room:${roomId}`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'messages',
          filter: `room_id=eq.${roomId}`,
        },
        (payload) => {
          setMessages((prev) => [...prev, payload.new as Message]);
        }
      )
      .subscribe((status) => {
        if (status === 'SUBSCRIBED') {
          console.log(`Connected to room:${roomId}`);
        }
      });

    return () => {
      supabase.removeChannel(channel);
    };
  }, [roomId]);

  return { messages };
}