Rindle

Recipes

Background & system writes

Cron jobs, sweepers, webhook handlers, and queue consumers write without a browser in the loop. The default is a system write — plain SQL through `@rindle/sql-client`, which carries no client identity, never touches the optimistic protocol, and still fans out through sync to every subscribed client. Plus exactly-once patterns for retries, and when to mint a mutation envelope instead.

View as Markdown

Not every write starts with a user’s click. A nightly cleanup, a webhook from your payment provider, a queue consumer, the sweeper that closes crashed LLM streams — these run with no browser, no optimistic prediction, and no mutation queue. Rindle has a name for this shape: a system write. The rule that defines it:

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 practical consequence is pleasant: the system-write API is just 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],
  });
}

And the write fans out like any other: it commits on the write-master, rides the change log to the followers, and every client subscribed to a query over message sees the delta. This isn’t best-effort. The master fail-closes on writes it can’t ship — a write to a table it doesn’t replicate is rolled back with an error, never silently local. So a system write either reaches every subscriber or it doesn’t happen.

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. A job that reads, decides, then writes must read what it just wrote. Pass consistency: "strong" to createSqlClient so reads route to the write-master instead of an eventually-consistent follower.
  • Bulk writes. For seeding and backfills, batch multi-row INSERTs at ≤100 rows per statement (SQLite caps a statement at 999 bound parameters), and split very large jobs into multiple transactions. See the API server’s out-of-band-writes guidance.

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:

// POST /webhooks/billing — provider retries until it sees a 2xx
const event = verifySignature(await request.text(), request.headers);
try {
  await sql.withTransaction(async (tx) => {
    // A redelivery hits this PRIMARY KEY and aborts the whole transaction —
    // so the effect below can only ever have landed once.
    await tx.execute({ sql: "insert into webhook_event (id, receivedAt) values (?, ?)",
                       args: [event.id, Date.now()] });
    await tx.execute({ sql: "update account set plan = ?, planUpdatedAt = ? where id = ?",
                       args: [event.plan, Date.now(), event.accountId] });
  });
} catch (err) {
  if (!isUniqueViolation(err)) throw err;   // duplicate delivery: already applied — fall through
}
return new Response("ok");                  // 2xx either way, or the provider retries forever

3. A durable idempotency key on the transaction. The daemon client’s executeSqlTxn accepts idempotencyKey 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.

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:

const out = await api.pushMutation({
  user: SYSTEM_USER,   // your authorizers and ctx.user see this principal
  envelope: { clientID: `cron:${jobRunId}`, mid: 1, name: "closeStaleIssues", args: { before } },
});
if (!out.accepted) console.error("closeStaleIssues rejected:", out);

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

Nothing special — that’s the point. A system write is indistinguishable from any other authority write once it commits. Subscribed views tick, countAs badges move, and no client’s optimistic stack is disturbed, because no client claimed the write. The sweeper flips a stuck bubble to “Response was cut short” on every open tab at once.

See also