Architecture note: Using SurrealDB LIVE queries in a backend service

Requirements

To deliver real‑time updates from SurrealDB to clients you need a persistent subscription that pushes changes as they are committed. The core requirements are:

• A SurrealDB instance reachable via HTTP/WebSocket (port 8000 by default).
• Authentication and authorization configured so that only permitted roles can open a LIVE subscription.
• A backend service that opens a WebSocket connection, issues a SELECT ... LIVE statement, and forwards the received messages to the UI or other consumers.
• Operational visibility into active subscription count, memory usage, and query latency to detect leaks or back‑pressure.

Minimal Suitable Design

The smallest architecture that satisfies the requirements consists of a single SurrealDB server (embedded or standalone) and a thin backend that only manages the WebSocket lifecycle.

Server setup

# Start SurrealDB with HTTP/WebSocket enabled and a password for the root user
surreal start --log trace --bind 0.0.0.0:8000 --user root --pass 

After the process is running, create a namespace, database, role and permission that allow LIVE queries on a specific table.

# Connect via HTTP to run setup commands
curl -L http://localhost:8000/signup -X POST -d "{\"username\":\"root\",\"password\":\"\",\"ns\":\"testns\",\"db\":\"testdb\"}"

# Define a role that can read the user table
surreal sql --conn http://localhost:8000 --user root --pass  <

Backend subscription code (example in Node.js)

const WebSocket = require('ws');
const ws = new WebSocket('ws://localhost:8000/rpc');

ws.on('open', () => {
  // Authenticate first (required when auth is enabled)
  ws.send(JSON.stringify({
    method: 'authenticate',
    params: { username: 'reader', password: '' },
    id: 1
  }));
});

ws.on('message', (data) => {
  const msg = JSON.parse(data);
  if (msg.result && msg.result.subscription) {
    // Live updates arrive here
    console.log('Live update:', msg.result.subscription);
    // Forward to UI, cache, etc.
  }
});

// Subscribe to live changes on active users
ws.send(JSON.stringify({
  method: 'query',
  params: { sql: 'SELECT * FROM user WHERE active = true LIVE' },
  id: 2
}));

// Graceful shutdown
process.on('SIGINT', () => {
  ws.close();
});

The backend does not store any state; it merely relays the stream. All trust decisions are enforced by SurrealDB’s permission engine.

Trust and Data Boundaries

SurrealDB evaluates the SELECT statement of a LIVE query against the caller’s permissions each time a transaction affects the queried data. This means:

• The server never pushes data that the caller is not allowed to see.
• If a role’s permission changes, existing subscriptions continue to run with the old permission set until they are re‑established (a reconnection refreshes the check).
• Network‑level trust is limited to the WebSocket connection; TLS should be terminated at a reverse proxy or enabled directly in SurrealDB for production.

No application‑side validation of the payload is required for correctness, though you may still want to sanitize for UI‑specific concerns.

Operational Checks

Monitor the health of the LIVE query subsystem with the built‑in INFO commands.

Subscription count

surreal sql --conn http://localhost:8000 --user root --pass  -q "INFO FOR DB"

Look for the field subs (or active_subscriptions depending on version). After a client disconnects, this number should decrease by one.

Resource usage

• CPU spikes can be observed via top or the server’s INFO FOR DB output (query_latency_ms, mem_used).
• Network throughput can be measured at the NIC or with a proxy that logs WebSocket frame sizes.

Verification steps

1. Start SurrealDB as shown above.
2. Run the backend subscription code (or a simple wscat -c ws://localhost:8000/rpc after authenticating).
3. Insert a row matching the LIVE filter (INSERT INTO user { name: "Alice", active: true }) via HTTP or the SurrealDB CLI.
4. Observe that the backend receives a push message containing the new row.
5. Close the WebSocket client and re‑run INFO FOR DB; confirm the subscription count dropped.
6. Optionally, generate a burst of writes (e.g., a loop inserting 10 000 rows) and watch CPU/memory to ensure they stay within your budget.

Failure Modes

Understanding where the design can break helps you set alerts and define mitigation.

• Subscription leaks: If a client crashes or fails to close its WebSocket, the server retains a live query handler that continues to consume memory until the connection times out or the process is restarted. Mitigation: enable idle‑timeout on the WebSocket layer and monitor the subscription count for unexplained growth.
• Back‑pressure spikes: Many clients subscribing to a high‑write table cause the server to serialize and push a large volume of updates, potentially saturating CPU or network bandwidth. Mitigation: limit the number of concurrent LIVE subscriptions per role, or shard high‑write tables and subscribe only to relevant partitions.
• Permission re‑evaluation cost: Each triggering transaction re‑runs the SELECT statement to check visibility. Complex queries with joins, functions, or large result sets can become a bottleneck. Mitigation: keep LIVE queries simple (point lookups or filtered scans) and materialize complex logic in a separate read‑model if needed.
• Ordering guarantees: LIVE does not guarantee global ordering across tables; updates to different tables may arrive out‑of‑order relative to each other. If your application requires strict cross‑table sequencing, you must buffer and reorder events on the client side or redesign the data model.

Conditions That Would Change the Design

Re‑evaluate the architecture when any of the following occurs:

• You need to push updates to >10 000 concurrent clients; a single SurrealDB instance may exceed its WebSocket or CPU limits, prompting a move to a clustered deployment or a fan‑out layer (e.g., a Pub/Sub broker).
• The data model evolves to require multi‑table transactions with strict ordering; you might replace LIVE with a custom change‑data‑capture pipeline that emits ordered events.
• Regulatory constraints demand end‑to‑end encryption of the update stream; you would terminate TLS at the load balancer and possibly add message‑level signing.
• Performance testing shows that permission re‑evaluation for your LIVE query consumes >30 % of CPU under load; you would simplify the query or create a dedicated read replica with pre‑computed permissions.

---

By following the minimal design above, you gain real‑time push capabilities with clear trust boundaries, observable health metrics, and a set of well‑understood failure modes. Adjust the design only when the listed conditions indicate that the single‑instance, permission‑checked LIVE query approach no longer fits your operational or functional requirements.