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

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](/docs/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):

```ts
// 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:

```ts
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 `INSERT`s 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](/docs/api-server#talking-to-rindle)'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:

```ts
// 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:

```ts
// 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`](/docs/sql-client) 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:

```ts
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](/docs/api-server#work-outside-the-mutation-transaction) and the
[LLM-streams recipe](/docs/llm-streams) 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

- [The SQL surface](/docs/sql-client) — `createSqlClient`, transactions, consistency modes, and
  session cursors.
- [The API server](/docs/api-server) — `scoped()` post-commit effects, and the bulk out-of-band
  write guidance.
- [Streaming LLM responses](/docs/llm-streams) — the plane whose sweeper this recipe implements.
- [Isomorphic mutators](/docs/mutators) — what an envelope buys you when you do want one.

---

[View this page on Rindle](https://rindle.sh/docs/background-writes)