Subscriptions

PerSQL ships a bearer-authenticated WebSocket endpoint that pushes a change event every time a write hits one of the tables you’re listening to. Think Postgres LISTEN/NOTIFY — but built into the database, not a separate channel you have to wire up yourself.

MCP runtimes (Claude Desktop, Cursor, …) can’t hold a WebSocket open inside a tool call. Use the wait_for_changes MCP tool instead — it long-polls the same change feed for up to 25 seconds per call, returns a cursor you pass back to continue, and is the recommended shape for JSON-RPC agents. The WebSocket below is for SDK and direct callers.

SDK

import { PerSQL } from "@persql/sdk";

const persql = new PerSQL({ token: process.env.PERSQL_TOKEN! });
const db = persql.database("acme/orders");

const off = db.subscribe({
  tables: ["orders", "order_items"],
  onChange: (e) => {
    console.log(`${e.kind} on ${e.table}`);
    // re-fetch / forward to a queue / push to your cache
  },
  onReady: (tables) => console.log("subscribed to", tables),
  onError: (err) => console.error(err),
});

// later
off();

Wildcard subscription — pass tables: [] (or omit) to receive events from every table in the database.

REST

GET /v1/db/:ns/:db/subscribe?tables=orders,order_items
Sec-WebSocket-Protocol: persql.bearer.<token>

Or, in environments where you can’t set a sub-protocol:

GET /v1/db/:ns/:db/subscribe?tables=orders&token=<token>

The query-string fallback is convenient but the token will appear in URL access logs — prefer the sub-protocol when you can.

Server messages

type	Fields	Notes
subscribed	tables: string[] | "*"	Sent once on connect (and again after a subscribe re-config). The list is your effective scope after the token’s tableScope is applied.
change	table, kind	One per write. kind ∈ update / insert / delete / schema / unknown.
error	message	Server-side issue.

Client messages

// Update the active filter mid-session
{ "type": "set-tables", "tables": ["orders"] }
// Or wildcard
{ "type": "set-tables", "tables": "*" }
// Keepalive — server returns a WebSocket pong
{ "type": "ping" }

Auth, roles, and scope

• Any role can subscribe — admin, readwrite, readonly. The feed is read-only by definition.
• Token tableScope is enforced. A wildcard subscription on a scoped token resolves to the scope itself; an explicit list outside the scope is rejected with a 403 before the upgrade succeeds. Mid-session subscribe messages are clipped server-side too.

Delivery semantics

• At-most-once, no replay. If your client disconnects, events during the gap are gone. Don’t rely on change for state — use it to invalidate caches or trigger re-fetches.
• Best-effort table detection. The server figures out which table was written by regex-matching the SQL prefix (INSERT INTO …, UPDATE …, DELETE FROM …). Compound DDL or queries that hit multiple tables only emit the first match.
• No row-level data in the event. We send { table, kind }, nothing else. The pattern is “something changed in this table — re-query if you care.”

Pairing with realtime infrastructure

For multi-tab UI collab (cursor positions, who’s editing a cell), use the cookie-authenticated /api/.../realtime endpoint — same DO under the hood, but with peer presence and identity. Subscriptions are the agent / backend / cache version of the same channel.