Skip to content
Rindle
Guides contents

GuidesSQL & data sources

Background & system writes

Write SQL from jobs, webhooks, and services, and understand how those writes reach subscribed clients.

View as Markdown

Jobs, webhooks, and queue consumers can write to Rindle without a browser client. These system writes use SQL and have no optimistic prediction to confirm. This guide assumes a Rindle database and a trusted server process. Start with Rindle SQL for the connection and transaction API.

A client mutation carries { clientID, mid } and advances that client’s lmid — the watermark that releases its optimistic prediction. A system write has no prediction to release, so it carries no client identity and must never advance an lmid.

The system-write API is ordinary SQL.

The default: plain SQL through the same ingress

@rindle/sql-client talks to the same unified URL as everything else. Ordinary execute / batch / withTransaction calls never enter the mutation protocol (only the explicit mutation facade accepts an envelope, so generic SQL can’t reach it by accident):

// server/sweeper.ts
import { createSqlClient } from "@rindle/sql-client";

const sql = createSqlClient({
  url: process.env.RINDLE_URL!,               // both exported by `rindle dev`;
  authToken: process.env.RINDLE_DATABASE_TOKEN!,  // Rindle Cloud's Connect panel supplies the pair
});

// The sweeper the LLM-streams recipe asks for: a host that died mid-generation
// leaves rows saying `streaming` forever — reap them past any plausible runtime.
const STALE_MS = 10 * 60 * 1000;
export async function sweepInterruptedStreams(): Promise<void> {
  await sql.execute({
    sql: "update message set status = 'interrupted' where status = 'streaming' and createdAt < ?",
    args: [Date.now() - STALE_MS],
  });
}

The write follows the database’s normal change path. In a fleet, it commits on the write master and replicates to followers. A standalone daemon serves the write and its subscriptions directly. Connected clients receive changes to their subscribed queries as those changes reach the serving replica.

A successful commit does not mean that every subscriber received it. Clients can disconnect or lag behind. Replication errors can reject a write, but commit acknowledgment and client delivery are separate events.

Scheduling is yours — a setInterval in your server process is fine for a sweeper:

const timer = setInterval(() => {
  void sweepInterruptedStreams().catch((err) => console.error("sweep failed:", err));
}, 60_000);
process.on("SIGTERM", () => { clearInterval(timer); void sql.close(); });

(A serverless cron — a Cloudflare Cron Trigger, a scheduled Action — is the same code with the platform’s scheduler instead of the interval. You created this client, so you close it.)

Two knobs worth knowing:

  • Read-your-writes. SQL reads use session consistency by default and go to the write authority. Use withTransaction for a read and its dependent write. See SQL consistency for session cursors across requests.
  • Bulk writes. Use bounded batches and account for the number of parameters per row. Split large backfills into several transactions. The SQL guide covers transaction limits and retries.

Retries: make the write idempotent, not the scheduler careful

Everything in this recipe re-runs — crons overlap, webhook providers redeliver, queues are at-least-once. Don’t fight that upstream. Make the write safe to repeat. Three mechanisms, in order of preference:

1. Deterministic statements. The sweeper above is already idempotent: re-running the UPDATE matches nothing the first run didn’t. Prefer shapes that are no-ops on replay — ON CONFLICT DO NOTHING, guarded UPDATE … WHERE status = 'x', a compare-and-swap on a version column.

2. A unique key the retry collides with. For webhooks, the provider’s event id is that key. Record it in the same transaction as the effect:

This example assumes webhook_event(id TEXT PRIMARY KEY, receivedAt REAL) and account(id TEXT PRIMARY KEY, plan TEXT, planUpdatedAt REAL). Your HTTP adapter must verify the provider’s signature before passing the event to this function:

import type { SqlClient } from "@rindle/sql-client";

interface BillingEvent {
  id: string;
  accountId: string;
  plan: string;
}

export async function applyBillingEvent(sql: SqlClient, event: BillingEvent) {
  return sql.withTransaction(async (tx) => {
    const claimed = await tx.execute({
      sql: "insert into webhook_event (id, receivedAt) values (?, ?) on conflict (id) do nothing",
      args: [event.id, Date.now()],
    });
    if (claimed.rowsAffected === 0) return false;

    await tx.execute({
      sql: "update account set plan = ?, planUpdatedAt = ? where id = ?",
      args: [event.plan, Date.now(), event.accountId],
    });
    return true;
  });
}

A repeated event ID returns false without applying the effect again. The receipt and update commit together. Other database errors propagate to the HTTP adapter; they are not mistaken for duplicate delivery. Decide separately how your application handles valid provider events that arrive out of order.

3. A durable producer watermark on the transaction. The daemon client’s executeSqlTxn accepts producer: { id, seq } and dedupes it durably server-side. It is the right tool for one-shot setup like a boot seed, where the guard must survive restarts without you modeling a table for it:

For a previously migrated category(id TEXT PRIMARY KEY, name TEXT) table:

import type { RindleDaemonClient } from "@rindle/daemon-client";

export async function seedCatalog(daemon: RindleDaemonClient) {
  return daemon.executeSqlTxn({
    producer: { id: "seed-catalog-v1", seq: 1 },
    statements: [
      { sql: "insert into category (id, name) values (?, ?)", params: ["general", "General"] },
    ],
  });
}

Pass a configured, trusted control client with a write-authority route. A follower-only connection cannot execute this system write. Repeating the same producer sequence returns applied: false without executing the statements.

The rules, because they are a real constraint:

  • id names the writer, seq numbers its writes. seq <= last is absorbed (applied: false, no statements run), seq === last + 1 applies, and anything beyond is a 409 gap. So a long-lived producer must number 1, 2, 3, … and send them in order — the reply for seq: n before submitting n + 1.
  • Concurrency means several producer ids, not unordered submission under one. A worker pool fanning out under a single id will draw spurious gap rejections; give each worker its own id.
  • Keep ids stable and few. The daemon stores one row per producer and overwrites it in place, so the dedup state is bounded by how many producers exist — not by how much they write, and never swept. A fresh id per process run puts that growth back. Nothing server-side can stop that, so watch it instead: rindle_write_producers on a standalone daemon, or rindle_replicator_write_producers on a write-master, counts your distinct producer ids. It should settle; a line that keeps climbing means something is minting ids per run.
  • producer and clientID/mid are mutually exclusive. Sending both is a 400. A mutation from an optimistic client already has mid as its durable retry identity.

This gives you “don’t apply twice”, not “give me the original answer back”: an absorbed replay reports applied: false and no stored result. If the retry needs the exact original reply — the returned rows or assigned IDs — the lower-level /v1/sql/execute HTTP protocol can replay an outcome for a retained idempotency_key. Its cache is bounded by TTL and quota, and expires old keys with 410. The TypeScript SQL client’s automatic key survives its internal retries but is not exposed for reuse across separate calls. For application-level recovery across invocations, a receipt table like the webhook example is usually the clearer contract.

When you actually want a mutator: mint an envelope

Sometimes the background job needs to run your mutator — same validation, same policy guards, same body the clients predict — rather than restate its SQL. The api-server exposes the same in-process entry the HTTP route uses:

This function assumes closeStaleIssues is registered on the API server and its authorizers permit the application’s system:scheduler principal:

import type { RindleApiServer } from "@rindle/api-server";

export async function runIssueCleanup(
  api: RindleApiServer<string>,
  jobRunId: string,
  before: number,
) {
  const out = await api.pushMutation({
    user: "system:scheduler",
    envelope: {
      clientID: `cron:${jobRunId}`,
      mid: 1,
      name: "closeStaleIssues",
      args: { before },
    },
  });
  if (!out.accepted) console.error("closeStaleIssues rejected:", out);
  return out;
}

Keep jobRunId stable across retries of the same logical job. A new ID describes a new mutation sequence.

This is the full client path — arg parse, authorizeMutation, the shared body, and yes, an lmid advance for that synthetic clientID. Envelope semantics are the point here. Which brings the one real trap: per clientID, mid must be exactly lmid + 1. On the standard Rindle SQL mutation backend, a lower mid is absorbed as a replay — accepted with no effects and no error — and a gap is a hard 409. So a worker that reuses one clientID with an in-memory counter silently does nothing after a restart, and two instances sharing a clientID conflict. The safe patterns are either a fresh clientID per job run with mid: 1 (shown above), or a persisted counter. The preview Postgres adapter has different replay limits; see Postgres mutations. If you find yourself engineering around this, stop — the envelope machinery exists for optimistic clients. Your job probably wanted a plain system write, with the shared logic extracted into a function both call.

For a mutation that must trigger a background effect (start a generation, send an email), that belongs inside the mutator itself via scoped() post-commit. See the API server and the LLM-streams recipe for the worked pattern, including the transactional replay guard that keeps a retried envelope from firing the effect twice.

What the user sees

A committed system write updates subscribed views through the ordinary sync protocol. It can also cause clients to rebase pending predictions over the new server state. It does not confirm any client’s pending mutation. The sweeper’s status change reaches each subscribed tab through this same process.

See also