/realtime

name: realtime
description: Use when enabling WebSocket subscriptions for live database changes, presence/multiplayer state, or when debugging clients that connect but receive no events

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: 1. A Postgres trigger is installed → writes every change to `realtime.changes`. 2. A LISTEN connection in `RealtimeManager` reads those changes. 3. For each connected client, the change is RLS-checked **as that user** before broadcasting. 4. Clients only receive events for rows they could read with a regular SELECT.

---

2. Prerequisites

Before calling `configure`, the table must:

1. **Exist.** Run `manage_schema` (`action: "apply"`) first. Realtime won't auto-create it. 2. **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. 3. **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 disconn