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:
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

```js
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

```js
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.

```js
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:

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

### Welcome and protocol

On connect, the server sends:

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

Then a heartbeat every 30s:

```json
{ "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):

```sql
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