Realtime

Butterbase Realtime

Live database change notifications over WebSocket, with per-row RLS enforcement. Once a table is enabled, INSERT/UPDATE/DELETE events stream to subscribed clients filtered by the same RLS policies that gate reads.

One tool: manage_realtime with two actions: configure and get.

1. The mental model

Postgres (data plane)              Control API                       Browser
─────────────────────              ────────────                      ────────
INSERT/UPDATE/DELETE  ──trigger──► realtime.changes  ──WAL listener─► WebSocket ──► client
                                          │
                                          └── RLS check per (role, user) ──► filter rows

When you configure a table:

• A Postgres trigger is installed → writes every change to realtime.changes.
• A LISTEN connection in RealtimeManager reads those changes.
• For each connected client, the change is RLS-checked as that user before broadcasting.
• Clients only receive events for rows they could read with a regular SELECT.

2. Prerequisites

Before calling configure, the table must:

• Exist. Run manage_schema (action: "apply") first. Realtime won't auto-create it.
• Have RLS configured (if you care about isolation). Realtime respects whatever policies exist via manage_rls. No policies = all events flow to all users of that role. This is the #1 silent leak.
• Have a primary key. RLS checks query by PK. Tables without one can't be realtime-enabled cleanly.

3. Configure tables

manage_realtime({
  app_id: "app_abc123",
  action: "configure",
  tables: ["messages", "presence", "documents"]
})
// → [{ table: "messages", status: "enabled" }, ...]

• Idempotent — already-enabled tables are skipped.
• All three events (INSERT / UPDATE / DELETE) are enabled together; per-event filtering happens client-side via subscription filter.
• Validation: every named table must exist or you get VALIDATION_TABLE_NOT_FOUND.

Inspect current state

manage_realtime({ app_id: "app_abc123", action: "get" })
// → {
//     tables: [{ table_name, enabled, trigger_installed, drift, created_at, updated_at }, ...],
//     active_connection: true,
//     websocket_url: "wss://api.butterbase.dev/v1/app_abc123/realtime"
//   }

drift: true means the control-plane config says enabled but the data-plane trigger is missing — typically after a schema migration that dropped/recreated the table. Re-run configure to repair.

4. Connect from a client

wss://api.butterbase.dev/v1/{app_id}/realtime?token={JWT_or_API_KEY}

Browsers can't set custom headers on WebSocket upgrade, so the JWT goes in the query string. Server clients can use Authorization: Bearer ... instead.

const ws = new WebSocket(
  `wss://api.butterbase.dev/v1/${appId}/realtime?token=${userJwt}`
);

ws.onopen = () => {
  ws.send(JSON.stringify({ type: "subscribe", table: "messages" }));
};

ws.onmessage = (e) => {
  const msg = JSON.parse(e.data);
  if (msg.type === "change") handleChange(msg);  // { type, table, op, record, old_record, timestamp }
};

The Butterbase SDK wraps this:

const realtime = client.realtime(appId, userJwt);
realtime.subscribe("messages", (change) => console.log(change.op, change.record));

Welcome and protocol

On connect, the server sends:

{ "type": "connected", "app_id": "app_abc123", "role": "butterbase_user" }

Then a heartbeat every 30s:

{ "type": "heartbeat", "timestamp": "..." }

Client → server messages

| Type | Body | Purpose |

|------|------|---------|

| subscribe | { table, filter? } | Subscribe to changes; optional client-side filter { col: value } |

| unsubscribe | { table } | Stop receiving |

| presence_track | { metadata } | Announce yourself with arbitrary metadata (cursor, status) |

| event | { event, payload } | Trigger a function with trigger: { type: "websocket", config: { event } } |

Server → client messages

| Type | Body |

|------|------|

| change | { table, op: "INSERT"\|"UPDATE"\|"DELETE", record, old_record, timestamp } |

| presence_state | { clients: [{ client_id, user_id, metadata }] } |

| heartbeat | { timestamp } |

5. RLS enforcement (the critical pitfall)

For each broadcast, the server runs (roughly):

SET LOCAL ROLE butterbase_user;
SET LOCAL request.jwt.claim.sub = '{user_id}';
SELECT 1 FROM "{table}" WHERE "{pk}" = {record_pk} LIMIT 1;

If the row is not visible under RLS, the change is silently dropped for that client. There is no error.

Common consequences:

• Client connects, sees connected, subscribes — but receives no events. → RLS too restrictive (or no policies at all + access mode authenticated).
• Client receives some events but not others. → RLS works for those rows; others are filtered out (often correct).
• Service key clients see everything. → Service bypasses RLS. Don't use this to "verify realtime works" if testing user-scoped behaviour.

Always test with a real end-user JWT, not the service key.

Anonymous clients

If manage_app access mode is authenticated, anonymous WebSocket connections are rejected with close code 1008 (Policy Violation). To allow anon, the app must be in public mode AND the table must have a permissive policy for butterbase_anon.

6. Connection lifecycle

| Close code | Meaning |

|------------|---------|

| 1008 | App requires authentication, no token provided |

| 1013 (try again later) | Plan limit hit (maxRealtimeListenersPerApp) — upgrade |

| 1013 ("Realtime disabled by plan") | Free / starter tiers may have realtime off entirely |

| Normal close | Heartbeat missed, client disconnected, or server eviction |

The server caches table primary keys for 60s and batches RLS checks per (role, user) group, so connection cost is amortised.

7. Common patterns

Live chat

• manage_schema apply with a messages table (id, room_id, user_id, body, created_at).
• manage_rls create_user_isolation with user_column: "user_id" plus a custom policy that allows reading messages where room_id IN (SELECT room_id FROM members WHERE user_id = current_user_id()).
• manage_realtime configure with tables: ["messages"].
• Client connects, subscribes to messages, optionally filters { room_id: "..." }.

Live dashboard / activity feed

• Add a notifications table with user_id.
• RLS create_user_isolation so each user only sees their own.
• Realtime configure → notifications stream straight to the right user, no extra filtering needed.

Multiplayer cursor sharing

Use presence, not table changes:

ws.send(JSON.stringify({
  type: "presence_track",
  metadata: { cursor: { x: 100, y: 50 }, color: "#f00" }
}));

Other clients receive presence_state updates with everyone's metadata. No DB writes.

Debugging "client connects but no events"

• Confirm trigger installed: manage_realtime get → trigger_installed: true, drift: false.
• Confirm RLS allows the user to SELECT the row: select_rows with as_role: "user", as_user: "<id>" → does the row appear?
• If yes to both and still no events: check for plan limits in close codes; check that the change *actually* happened in Postgres (look at realtime.changes).

8. Anti-patterns

| Don't | Do |

|-------|----|

| Enable realtime before configuring RLS | Set up policies first; otherwise events leak across users |

| Use a service key from the frontend "to make it work" | Service bypasses RLS — ship-stopping leak. Use end-user JWTs. |

| Trust client-side filter for security | filter is just a convenience to reduce client-side work; RLS is the security boundary |

| Hold thousands of subscriptions per client | One connection, one or two subscribed tables — the server handles fan-out |

| Re-call configure in a loop on every page load | It's idempotent but each call still touches the DB. Configure once during app setup. |

| Send custom auth headers from the browser | WebSocket API can't set them — pass the JWT as ?token= query param |

If a docs/butterbase/00-state.md exists in the working directory, prefer invoking via /butterbase-skills:journey-realtime so the journey orchestrator stays in sync.