Postgres Changes

Subscribe to real-time notifications when database rows are inserted, updated, or deleted. Changes are filtered through Row Level Security (RLS) policies, ensuring users only receive events for data they're authorized to see.

How it works

1. When a user subscribes, Volcano lazily installs triggers on the table
2. When data changes, Postgres sends a notification via LISTEN/NOTIFY
3. The realtime server evaluates RLS policies for each subscriber
4. Authorized subscribers receive the change event via WebSocket

Volcano starts listening when you subscribe to a table, including tables added while another subscription is active. Unsubscribing stops delivery for that subscription; other subscribers keep receiving changes.

Volcano maintains a dedicated database session for notifications. Your application can keep using pooled database connections for its queries.

Postgres change notifications generated by Volcano do not count toward the realtime message allowance.

Basic usage

Subscribe to all changes on a table

import { VolcanoRealtime } from '@volcano.dev/sdk/realtime';

const realtime = new VolcanoRealtime({
  apiUrl: 'https://api.yourapp.com',
  anonKey: 'your-anon-key',
  accessToken: accessToken
});

await realtime.connect();

// Channel name is schema:table for postgres type
const channel = realtime.channel('public:messages', { type: 'postgres' });

// Listen for all changes
channel.onPostgresChanges('*', 'public', 'messages', (payload) => {
  console.log('Change type:', payload.type);
  console.log('Row ID:', payload.id ?? payload.record?.id);
});

await channel.subscribe();

Subscribe to specific events

// Only INSERTs
channel.onPostgresChanges('INSERT', 'public', 'messages', (payload) => {
  console.log('Inserted row ID:', payload.id ?? payload.record?.id);
});

// Only UPDATEs
channel.onPostgresChanges('UPDATE', 'public', 'messages', (payload) => {
  console.log('Updated row ID:', payload.id ?? payload.record?.id);
});

// Only DELETEs
channel.onPostgresChanges('DELETE', 'public', 'messages', (payload) => {
  console.log('Deleted row ID:', payload.id);
});

await channel.subscribe();

Wire payload

The WebSocket notification carries the primary key in id for every change. It does not retain the row's columns or its previous values.

interface PostgresChangeNotification {
  type: 'INSERT' | 'UPDATE' | 'DELETE';
  schema: string;
  table: string;
  id: unknown;
  mode: 'lightweight';
  timestamp: string;
}

A service-key subscriber receives this DELETE notification:

{
  "type": "DELETE",
  "schema": "public",
  "table": "messages",
  "id": 1,
  "mode": "lightweight",
  "timestamp": "2024-01-15T10:32:00Z"
}

record and old_record are omitted; record is not null.

JavaScript SDK callbacks

For DELETE, the JavaScript SDK preserves id, adds old_record: { id: 1 } for compatibility, and omits mode and record. This old_record contains only the key, not a snapshot of the deleted row.

For INSERT and UPDATE, a standalone VolcanoRealtime client delivers the lightweight notification above. When configured with a volcanoClient and auto-fetch enabled, it fetches the current row and delivers record instead of id and mode. If fetching fails, it delivers the lightweight notification, so callbacks must handle a missing record.

{
  type: 'UPDATE',
  schema: 'public',
  table: 'messages',
  record: { id: 1, text: 'Hello (edited)', user_id: 'user-123' },
  timestamp: '2024-01-15T10:31:00Z'
}

Row Level Security

Postgres changes respect RLS policies. If a user can't SELECT a row, they won't receive change events for it. This is a critical security feature.

Visibility is checked against the current row. Authenticated user subscriptions currently do not receive a DELETE notification after that row is gone. Service-key subscriptions bypass this check and can receive deletion events. Do not rely on end-user delete notifications to maintain a persistent replica.

Example: User-scoped messages

-- Table with user_id column
CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  room_id TEXT NOT NULL,
  text TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- RLS policy: users can only see their own messages
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_own_messages" ON messages
  FOR SELECT USING (user_id = auth.uid());

With this policy:

• User A inserts a message → Only User A receives the INSERT event
• User B won't see User A's messages in realtime

Example: Room-based access

-- Users can see messages in rooms they belong to
CREATE POLICY "room_members" ON messages
  FOR SELECT USING (
    room_id IN (
      SELECT room_id FROM room_members 
      WHERE user_id = auth.uid()
    )
  );

Channel naming

For postgres channels, the name is schema:table:

// Public schema, messages table
const channel = realtime.channel('public:messages', { type: 'postgres' });

// Custom schema
const channel = realtime.channel('app:orders', { type: 'postgres' });

For realtime validation, the schema:table channel name must be 64 characters or fewer.

Project isolation is automatic from your anon key - you never specify project ID.

A subscription fails if Volcano cannot initialize change delivery for the table. Create the table first, grant the subscriber role SELECT access, and configure its RLS policies before subscribing. Subscribers without SELECT permission receive a permission-denied error before change tracking starts.

Example: Live notifications

Pass your authenticated Volcano client and database name to enable row fetching.

function NotificationFeed({ anonKey, accessToken, volcanoClient, databaseName }) {
  const [notifications, setNotifications] = useState([]);

  useEffect(() => {
    const realtime = new VolcanoRealtime({
      apiUrl: 'https://api.yourapp.com',
      anonKey,
      accessToken,
      volcanoClient,
      databaseName
    });

    async function setup() {
      await realtime.connect();
      
      const channel = realtime.channel('public:notifications', { type: 'postgres' });
      
      channel.onPostgresChanges('INSERT', 'public', 'notifications', (payload) => {
        if (!payload.record) return;
        setNotifications(prev => [payload.record, ...prev]);
        
        // Show browser notification
        if (Notification.permission === 'granted') {
          new Notification(payload.record.title, {
            body: payload.record.message
          });
        }
      });
      
      await channel.subscribe();
    }
    
    setup();
    
    return () => realtime.disconnect();
  }, [anonKey, accessToken, volcanoClient, databaseName]);

  return (
    <div className="notifications">
      {notifications.map(n => (
        <div key={n.id} className="notification">
          <h4>{n.title}</h4>
          <p>{n.message}</p>
        </div>
      ))}
    </div>
  );
}

Lazy trigger installation

Triggers are installed lazily when users subscribe, not when databases are provisioned:

1. Database provisioning: Creates database, sets up roles - no realtime triggers
2. First subscription: When a user subscribes to public:messages, triggers are created
3. Subsequent subscriptions: Trigger already exists, just starts listening

This means:

• Faster database provisioning
• No overhead for tables without realtime
• Triggers are created with appropriate permissions

Limitations

Performance considerations

1. Index your filter columns: If filtering by room_id, ensure it's indexed

2. Avoid high-frequency tables: Tables with thousands of writes/second may cause performance issues

3. Use specific filters: The RLS check happens for each subscriber, so fewer subscribers = faster

4. Consider batching: For bulk operations, updates are sent individually

Best practices

1. Always use RLS: Never rely on client-side filtering for security

2. Handle reconnection: Re-fetch data after reconnecting to catch missed events

3. Optimistic updates: Update UI immediately on user action, then reconcile with realtime events

4. Debounce saves: For frequently-updated data, debounce saves to reduce database load

5. Use appropriate schemas: Keep realtime tables in a dedicated schema for organization