Consistency

The easy parts

One writer. Exactly one worker owns a database at a time, and within it writes execute one transaction at a time in arrival order. There is no write conflict to handle, no isolation level to pick, and no SQLITE_BUSY to retry. SQLite’s default isolation — serializable — is what you get, because the implementation is literally serial.

Read-your-writes. Once a write returns 200, every subsequent read of that database observes it. Always, with no staleness window and no read-after-write flag to set.

Atomic batches. A batch is one SQLite transaction and one durable segment. Either the whole thing committed and is durable, or none of it happened. There is no partial batch.

Foreign keys are on. PRAGMA foreign_keys is enabled and not something you can turn off, so a violated reference is an error rather than a slowly rotting row.

The one surprising part

A read can observe a write that was never acknowledged to anybody.

Reads are served from the owning worker’s local copy, which is committed locally at the moment the transaction runs — before the segment covering it reaches object storage. The window is the commit window, single-digit milliseconds up to about 200 ms. Inside that window:

1. Client A sends a write. It commits locally.
2. Client B reads and sees it.
3. Object storage fails to accept the segment. Client A gets a 503.
4. The database is recovered from object storage. The write is not in it.

Client B saw a row that no longer exists and that nobody was ever told was durable.

When this matters: if you read a value, act irreversibly on it outside the database — charge a card, send an email, call another service — and the acting client is not the client that wrote it.

What to do about it: if a decision is irreversible, make the database the thing that authorizes it, in the same batch:

{
  "statements": [
    { "sql": "UPDATE invoices SET charged = 1 WHERE id = ? AND charged = 0", "params": [42] },
    { "sql": "SELECT changes() AS claimed" }
  ]
}

If claimed is 1, you hold the claim and the durable acknowledgement that you hold it. If the batch returns 503 instead, you do not know whether you hold it — read back before charging anything.

For ordinary application reads, this window is not worth engineering around. It is documented because a guarantee you have to discover is not a guarantee.

Nothing crosses a database boundary

There are no cross-database transactions, joins, or reads. ATTACH is refused. Each database is an independent unit of consistency, ownership, and failure — which is also why one database being recovered has no effect on any other.

Ordering and txids

txid is the durable high-water mark for the database at the moment your request was acknowledged, not an id assigned to your specific statement. Two writes committed in the same batch report the same txid, because they became durable together.

So txid is monotonically non-decreasing and is a valid answer to “is the database at least this far along?” It is not a per-write sequence number and should not be used as one. If you need a per-row ordering, use INTEGER PRIMARY KEY or a timestamp column, which is what they are for.