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

// Re-running the seeder replays seq 1, which the daemon absorbs: applied === false, nothing ran.
const out = await daemon.executeSqlTxn({
  producer: { id: "seed-catalog-v1", seq: 1 },
  statements: seedStatements(),
});

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, the assigned ids — use /v1/sql/execute with its idempotency_key instead, whose outcome cache reproduces the reply verbatim (bounded by TTL and quota, and failing closed with 410 once a key ages past the retention floor).

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