Concurrency + expiry

Two column-level tools for questions a schema cannot otherwise answer: which write is newest, and when does this row stop counting.

#.version() — optimistic locking

Two clients read the same row and both write it. Without a version the second silently wins, and the first user's change is gone with no trace. That is the shape of every "my edit disappeared" report.

export const documents = table('documents', {
  id:      id(),
  title:   text(),
  version: integer().version(),
})

From then on the store increments version on every update, and an update that carries an expectation fails when the row has moved on:

// The client sends the version it read.
yield* ctx.store.update('documents', input.id, { title: input.title, version: input.version })
// → VersionConflict { expected: 3, actual: 7 } when four writes landed in between

VersionConflict is a typed error, so it reaches the client typed and a UI can offer reload and re-apply instead of showing a crash. It carries both numbers, because "someone else changed it" is not actionable while "you had 3, it is now 7" is.

Why not updatedAt. A timestamp cannot do this job. Two writes in the same millisecond are indistinguishable, and across replicas the clocks disagree — a comparison that looks correct in a test loses rows under load. An integer the database owns is totally ordered and needs no clock. (.version() therefore rejects a text() or timestamp() column at declaration.)

What it does not do. It is not a history — it records that a row changed, not what to; use plugin-row-history for that. It is not a lock: a conflict is reported, never queued or merged, because merging two intents is a decision only your application can make. And it is not a retry — "re-apply my change on top of theirs" is correct for some changes and wrong for others, so you write it.

How the check is made — the write is the check. The store reads the current version, then issues the UPDATE with the version it read in the statement: WHERE id = ? AND version = <read>. The affected-row count decides. Zero rows means the row moved (or vanished) between the read and the write — on every dialect, at any isolation level, inside or outside a transaction. That is what makes it a compare-and-swap rather than a check-then-act: a plain SELECT takes no row lock under READ COMMITTED, so "compare in memory, then write by key" lets two transactions read 3, both write 4, and both commit — exactly the lost update the column exists to prevent.

Four details worth knowing:

• The version a caller sends is an expectation, never a write. It is stripped from the patch, so a client cannot pin its own version and win every race.
• An update with no expectation is still last-write-wins — the default does not change — but the version still advances, by exactly one per write. A version that moved only for careful writers would be worse than none: it would sit still while a careless write changed the row. If such a write loses its swap to a concurrent writer, the store re-reads and re-applies it (bounded); a row still contended after that surfaces as VersionConflict rather than being written blind.
• A set-based update (ctx.store.updateMany, the fluent .set()) on a versioned table advances every matched row too — one guarded write per row, since the store API carries no column expression. Only tables that opted into a version column pay it.
• A row deleted underneath you is a conflict too, with actual: null. That is how you tell "deleted" from "changed".

Inside a mutation, a read is an expectation. A mutation runs in one transaction, and a keyed ctx.store.update of a versioned row that this transaction has already read carries the version it read — without the caller sending one. Read the row, change it in memory, write it back: if the row moved in between, the write is a conflict rather than a clobber, the runtime replays the whole mutation (the handler re-reads and re-decides, the same way a deadlock is replayed), and only after the bounded replays does the typed VersionConflict reach the client. This is the protection a fleet of read-modify-write handlers gets from one line per table — the column — and no change to a handler. A read that projected the version column away remembers nothing; a row the transaction never read stays last-write-wins.

#expires() — a row with an end date

export const inviteLinks = table('inviteLinks', {
  id:    id(),
  email: text(),
}).with(expires())

await ctx.store.insert('inviteLinks', {
  email,
  expiresAt: new Date(Date.now() + 24 * 3_600_000),
})

After that instant the row is not returned by reads. expiresAt is nullable and null means never, so adding the mixin to an existing table does not make its rows vanish.

Opt out for a deliberate read — an admin view, a grace-period check:

ctx.store.select('inviteLinks').includeExpired()

#Read this before you rely on it

Visibility and storage are two different guarantees, and only one of them holds everywhere.

	where	when
Invisible to reads	every dialect	immediately, the instant it passes
Physically deleted	every dialect	eventually, by the retention sweep

So an expired row is invisible immediately and still present in the database for a while. That is the right trade — making visibility depend on the sweep would mean a row that vanished on postgres and kept serving on MariaDB — but it matters: do not treat an expired row as unreachable. If the value must actually be gone, delete it, or do not store it in a row at all.

"Eventually" is a number: both boot paths register every expires() table with the retention sweep on expiresAt, and a row is deleted once it has been expired for VOLTRO_EXPIRED_ROWS_TTL_HOURS (default 24) — the grace that keeps a just-expired row inspectable. Before that registration existed the second row of this table was a wish: nothing registered an expires() table with the sweep on any dialect, while the mixin's own header still said the storage half was "postgres only". What is postgres-specific is the fast set-based DELETE, not the policy — elsewhere the sweep falls back to a bounded read plus one set-based delete per batch. The framework's own policies on the _voltro_workflow_* tables are armed only where the workflow engine that creates those tables is composed — an api with no *.workflow.tsx has neither, and a policy on a missing table was five failing DELETEs per tick, each an ERROR line in the database log. The boot line retention: N policy(ies) armed lists exactly what is swept.

#.forUpdate() — a row lock for read-modify-write

.version() refuses the stale writer; .forUpdate() makes the second writer wait instead. Both builders take it, and it marks the descriptor lock: 'update':

await ctx.store.transactional(async (tx) => {
  const ticket = await tx.one(database.tickets.where(eq('id', id)).forUpdate())
  await tx.update('tickets', id, { watchers: [...ticket.watchers, userId] })
})

The read locks the matched rows until the enclosing transaction commits, so a concurrent request's read of the same row blocks until this one is done and then sees the row it wrote — the json append above keeps both appends. Per dialect it is SELECT … FOR UPDATE (postgres, mysql, mariadb) and WITH (UPDLOCK, ROWLOCK) (mssql); sqlite emits nothing, because its writers are serialised already. Outside a transaction the lock ends with the statement — harmless, and useless; the point is that read and write share one transaction. A set operation (union, intersect) ignores it: there is no row to lock in a combined result.

Choose it over .version() when the write cannot be retried meaningfully — an append, a counter, a merge into a json column — and .version() when the caller can be told to look again.

#Read-modify-write in a mutation — what protects you, per engine

"It runs in a transaction" is the intuition, and it is wrong on every engine at the default isolation level: a plain SELECT takes no row lock, so two transactions read the same version and the second write is built on a stale copy. What the engine then does is the only thing that differs:

Engine	Default isolation	A stale write is…	Replayed by the runtime?
MariaDB ≥ 11.6.2 (innodb_snapshot_isolation=ON)	REPEATABLE READ	refused — ER_CHECKREAD (1020)	yes, transient since 0.64.0
MariaDB with snapshot isolation OFF, MySQL	REPEATABLE READ	applied in silence	nothing to replay
Postgres, SQL Server	READ COMMITTED	applied in silence	nothing to replay
SQLite / turso	serialised writers	applied in silence (the read was not serialised)	nothing to replay

Three of four rows lose the edit and say nothing. So the protection is never the transaction; it is one of:

• .version() on the table — the read becomes the expectation (above), the conflict is detected by the write itself on every engine, and the mutation runner replays. Nothing in the handler changes.
• .forUpdate() on the read — the second writer waits on the row lock and reads the committed row. For a write that must not be replayed.
• ctx.store.raw — available inside a mutation as well, on the transaction's own connection, for a lock the builders cannot spell.

A contention error the engine raises (a deadlock, a lock-wait timeout, a serialization failure, MariaDB's ER_CHECKREAD) is replayed the same way; the per-engine list is transientContention.ts in @voltro/database, shared by every store's own retry and by the mutation runner.

What the read-as-expectation does not see. Only rows that came back through the store's own reads with their id and the version column count: a raw read, a projection that dropped the version column, and the relation rows of an eager load remember nothing, and a keyed update after one of those is last-write-wins as before. patchJson patches in place and needs none of it.

Many writers on one row. The replay bound is mutations.replayAttempts in app.config.ts (default 3; env VOLTRO_MUTATION_REPLAY_ATTEMPTS wins). It is right for the occasional collision and thin for a document that a room full of people drags at once: every loser re-reads and writes again, and one that loses every race surfaces VersionConflict. For that row, .forUpdate() on the read queues the writers instead of racing them — no replay, no conflict, one commit after another.

voltro doctor names the handlers that read a row, rebuild it and write it back on a table without .version() (mutation/read-modify-write-unversioned), so the tables that need the column are a report, not an audit.

#See also

Column types · Mutations · Soft delete