Transactions and storage backends

Everything in pi-durable passes through one narrow gate: a commit on the Session's single mutation line. A commit callback reads and changes records and documents through a transaction object; when it returns, the transaction becomes a list of StorageWrites, the storage backend makes them durable atomically, and only then does the Session adopt and publish the new state. This chapter follows that path and then opens the four backends: memory (the reference semantics), SQLite (in Node and in Cloudflare Durable Objects) and JSONL.

Learning goals. Explain the single-writer mutation line and why it removes the need for locks; trace #runCommit from callback to publication; state the table rules and why reads after writes are forbidden; describe the storage interface; explain SQLite's WAL mode and the durability trade-off of synchronous = NORMAL; explain the JSONL commit-marker protocol and its crash recovery; state the failure rules.

14.1 One writer: the mutation line#

The Session serializes every mutation through a promise chain (session/session.ts):

#enqueue<T>(job: () => Promise<T>): Promise<T> {
	const run = this.#tail.then(job);
	this.#tail = run.then(
		() => undefined,
		() => undefined,
	);
	return run;
}

Each job starts only after the previous one settles, success or failure (the second then swallows errors so one failed job does not poison the chain). This is the single-writer principle: with exactly one writer, there are no write-write conflicts to detect, no locks to order and no deadlocks. Concurrency still exists — many tasks call models and run tools in parallel — but only their commits are serialized, and commits are short. The spec's invariant 7 adds that "The mutation line remains held through storage settlement and committed-state adoption", so the in-memory cache can never get ahead of or behind storage. Reads that must observe one consistent state run on the line too (readOnLine).

The same design appears in Redis (single-threaded command execution), in the actor model (one mailbox per actor), and in Cloudflare Durable Objects (one object instance processes requests serially) — a reason pi-durable maps naturally onto the latter.

14.2 Anatomy of a commit#

From the caller's side a commit looks like this example in the spec:

await session.commit(async tx => {
  const conversation = await tx.conversation(conversationId); // table read
  const live = await tx.doc(LiveDoc, conversationId);

  await tx.appendEntry(conversationId, message);     // first table write
  delete live.generation;                            // document mutation remains valid
  await tx.createTask(Follow, {}, { ownership: { kind: "conversation" }, conversationId }); // further table writes are fine
}, context);

Inside the Session, #runCommit performs five steps:

const tx = new Transaction(this.#host, context, scope);
let result: T;
try {
	result = await change(tx);
	// A callback that caught a failed read must not commit, nor succeed as if it had.
	this.#assertHealthy();
} catch (error) {
	await tx.settleFailure();
	this.#sweepDocuments();
	throw error;
}
let writes: readonly StorageWrite[];
…	writes = await tx.settleSuccess(); …
if (writes.length === 0) { tx.discard(); … return result; }
let seq: Seq;
try {
	// Once admitted, caller cancellation does not interrupt Storage settlement.
	seq = await this.#storage.commit(writes, withoutAbortSignal(context));
} catch (error) {
	// The guard has failed the Session.
	tx.discard();
	throw error;
}
…	documents = tx.adopt(seq); …
this.#publish(seq, writes, documents, context);

1. Run the callback against a Transaction. Document drafts record operations; table writes accumulate in the transaction. If the callback throws, everything is rolled back — nothing was written anywhere yet.
2. Settle: close every draft ("Document drafts are fully revoked when their transaction callback settles") and turn the transaction into a list of StorageWrites. An empty list commits nothing.
3. Store: storage.commit(writes) returns a new sequence number. Note withoutAbortSignal(context): once storage has the batch, the caller's cancellation must not interrupt it, because a half-written batch is the one outcome atomicity forbids.
4. Adopt: install the committed document values as the new in-memory baseline. "Storage already committed; a failed adoption leaves memory behind durable state" — so a failure here fails the whole Session.
5. Publish: notify commit listeners (the scheduler, views, watches) with the committed changes. "The commit is durable: a failing listener neither fails the commit nor skips the others."

This ordering is the write-ahead principle applied to an application: make it durable, then make it visible.

Table rules. The four tables (conversations, entries, tasks, submissions) obey a constraint: "Table reads are allowed before the first table write. Any table read after the first table write throws ReadAfterWrite." Why? Because a transaction's table writes are not applied anywhere until commit, a read after a write would see the old state and could not see its own write — a read-your-writes anomaly. Rather than implementing a merged view, the API forbids the pattern: read first, then write. Documents, which are tracked in memory, do support read-your-writes. This is a good example of making an invariant cheap to keep by restricting the interface.

Borrowing and detachment. "Commit arguments are borrowed until Storage.commit() settles. Anything retained after settlement is detached first." Every storage read "returns a detached JSON value". If the Session handed a caller an object that storage also holds, a later mutation by the caller would silently corrupt storage's copy. Detaching (deep copying, or serializing and parsing) enforces an ownership boundary in a language without ownership types.

14.3 The storage interface#

A backend implements one interface (spec section 10). Its write side is a single method:

interface Storage {
  commit(writes: readonly StorageWrite[], context: Context): Promise<Seq>;
  mintId<I extends Id<string>>(): Promise<I>;
  conversation(id: ConversationId, context: Context): Promise<ConversationRecord | undefined>;
  scanConversations(query: ConversationQuery, limit: number, cursor: Cursor | undefined, context: Context): Promise<Page<ConversationRecord, Cursor>>;
  …
}

StorageWrite is a union: a whole conversation, entry, task or submission record, or a document operation (document.create with a base, document.copy for forks, document.change with a base or a delta, document.retire). Tasks are written as full records on every transition — "Live task transitions replace one row" — which makes recovery trivial: the latest row is the state.

Reads are keyed lookups and cursor-based scans (Page<T, Cursor> with an opaque next). Cursor (keyset) pagination resumes from the last seen key rather than an offset, so pages stay stable while new rows are inserted and each page costs an index seek instead of skipping rows.

The interface comment states the division of trust: storage "Trusts the owning Session to supply semantically valid records, references, ancestry, and transitions. Enforces atomicity, global ID ownership, immutable conversation/entry creation, document record consistency, and detached values; Session serializes commits." A correct backend therefore needs only to be a faithful, atomic store — and registerStorageConformance() in @earendil-works/pi-durable/testing lets anyone prove theirs is, with a shared test suite runnable under Vitest or Jest. A conformance suite turns an informal contract into an executable one.

14.4 Memory: the reference semantics#

"Memory storage is the reference semantics. It copies retained write values and all read results. This deliberately simulates the ownership boundary naturally created by SQLite encoding/decoding and JSONL serialization." A memory backend that returned live objects would let bugs pass tests that fail against a real database. Making the test double as strict as production about aliasing is a lesson that applies far beyond this package.

14.5 SQLite#

The SQLite backend maps one Session commit to one SQL transaction. Its schema (storage/sqlite/migrations.ts, abridged):

CREATE TABLE entries (
	id INTEGER PRIMARY KEY,
	conversation_id INTEGER NOT NULL,
	head INTEGER,
	commit_seq INTEGER NOT NULL,
	record TEXT NOT NULL CHECK (json_valid(record))
) STRICT;
CREATE INDEX entries_by_conversation ON entries (conversation_id, id DESC);
CREATE INDEX entry_heads_by_conversation ON entries (conversation_id, id DESC) WHERE head IS NOT NULL;

CREATE TABLE tasks (
	id INTEGER PRIMARY KEY,
	conversation_id INTEGER NOT NULL,
	kind TEXT NOT NULL,
	status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'waiting', 'completing', 'terminal')),
	abort_requested INTEGER NOT NULL CHECK (abort_requested IN (0, 1)),
	background INTEGER NOT NULL CHECK (background IN (0, 1)),
	…
) STRICT;
CREATE INDEX tasks_by_status ON tasks (status, id);

CREATE TABLE document_revisions (
	document_id INTEGER NOT NULL,
	seq INTEGER NOT NULL,
	kind TEXT NOT NULL CHECK (kind IN ('base', 'delta')),
	version INTEGER NOT NULL,
	content TEXT NOT NULL CHECK (json_valid(content)),
	PRIMARY KEY (document_id, seq)
);

(Index and table names are shown unprefixed; the real names come from a configurable prefix.) Design points to notice:

• A hybrid relational/document model. Each row stores the complete record as JSON (record TEXT, guarded by json_valid), while the fields queries filter on are copied into typed columns. Queries use indexes; reads return whole records. This avoids designing a column for every field of an evolving schema.
• STRICT tables and CHECK constraints make SQLite enforce types and enumerations it would otherwise accept loosely — the database defends its own invariants.
• Indexes match access paths. (conversation_id, id DESC) serves "newest entries of a conversation"; the partial index WHERE head IS NOT NULL serves "find the latest head marker" (findLatestHeadMarker) while indexing only the few rows that are markers; tasks (status, id) lets the scheduler find live tasks without scanning terminal ones.
• Document revisions store bases and deltas keyed by (document_id, seq), with an index on (document_id, kind, seq DESC) to find the latest base quickly and replay the deltas after it.
• Migrations are versioned lists of statements recorded in a schema-version table, applied in a transaction at open.

The Node adapter uses Node's built-in node:sqlite (DatabaseSync) and configures it at open:

await adapter.exec("PRAGMA journal_mode = WAL");
await adapter.exec("PRAGMA synchronous = NORMAL");
await adapter.exec(`PRAGMA wal_autocheckpoint = ${checkpointPages}`);

In write-ahead logging (WAL) mode, SQLite appends changed pages to a separate -wal file instead of overwriting the database in place; a commit is a record in the log, readers see a consistent snapshot, and a periodic checkpoint copies log pages back into the main file. With synchronous = NORMAL, SQLite does not fsync the log on every commit, only at checkpoints. The README states the consequence precisely: "commits survive process crashes; the newest may be lost on power or host failure." A process crash cannot lose data the operating system already received; a power failure can lose what was still in the OS page cache. That trade buys much faster commits — important when every streamed chunk is a commit.

The synchronous node:sqlite API is wrapped in a promise-based SqliteDatabase facade, with a SerialOperationQueue so that "Adapters must queue unrelated operations and other transactions until the transaction finishes". On Cloudflare, openDurableObjectSqliteStorage(ctx.storage) runs the same SQL against a Durable Object's built-in SQLite storage, where "commits are its storage transactions". Because the portable cores avoid Node APIs, the same storage code runs in Node, Bun and Workers — the facade is the portability layer.

14.6 JSONL: building atomic commits from appends#

Plain files offer no multi-file transactions. The JSONL backend builds them from appends and one rule. Its files (spec 11.3):

main.jsonl       table writes, document records, and one marker per commit
doc-<id>.jsonl   one document incarnation
task-<id>.jsonl  live task replacements

The publication protocol: "1. Append complete prepared records to every affected sidecar. 2. Append one complete main marker listing those records. 3. Publish in memory only after the marker write succeeds." The marker in main.jsonl is the commit record: sidecar lines without a marker that references them do not exist as far as the store is concerned. Recovery follows directly: "Remove torn final lines. Ignore and remove unconfirmed sidecar tails. Apply confirmed records only. Missing required confirmed data is corruption and opening fails." This is the same idea as a database's commit record in its log, and as Git's model of writing objects first and moving a ref last.

Durability is configurable. With fsync: true, "the backend appends all affected sidecar records, flushes each affected sidecar, and only then appends the main marker" — so a marker can never survive a crash while the data it confirms is lost. main.jsonl itself is not flushed on every commit, so "an acknowledged tail commit may therefore still disappear, but a marker that survives should not overtake its sidecar data." Ordering of flushes, not just their presence, is what guarantees consistency.

Sidecars accumulate obsolete records (old task states, deltas before a newer base). Reclamation rewrites a sidecar after the authorizing base or retirement has committed: write a temporary replacement, rename it over the original (rename is atomic on POSIX file systems), and "invalidate cached file descriptors so future appends cannot target an unlinked inode" — a subtle Unix bug where writes to a file descriptor of a replaced file vanish into the old, now nameless file. An uncertain append failure "poisons" the open backend (JsonlStoragePoisonedError: "JSONL storage is poisoned and must be reopened"), because after a failed append nobody knows what is on disk; only a reopen, which re-reads and recovers, restores certainty.

The README's practical advice: JSONL "Keeps the whole history in memory too, so memory grows with it; use SQLite for long-running agents."

14.7 Failure rules#

The spec's section 10.1 gives one rule per kind of error "so a host knows what happens without reading code":

1. A storage error ends the Session. Any error from commit, a read, or mintId fails it: the triggering call rejects, every later call and pending wait rejects with SessionFailed, watches end, running tasks get their abort signal, and the Session closes itself. "Nothing is retried … Reopen to recover: commits are atomic, so the store is consistent, and resubmitting with the same requestId finds a submission that was already admitted."
2. An invalid request fails only that call (StorageRequestError: unknown conversation, malformed cursor).
3. User code fails only its own unit: a throwing task phase faults that task, a throwing tool gets an error result, a throwing hook is reported and skipped (except beforeTool, which blocks — fail closed again), a throwing commit callback rolls back its commit.

Rule 1 is the fail-stop model: after an error of unknown effect, stop and recover from durable state rather than continue with a memory that may disagree with disk. Its justification is in the text: "An error from commit() or mintId() is never exempt: once a batch is admitted, whether it committed is unknown." Hosts restart with a few lines:

harness.closed.then((end) => {
	if (end.reason === "failed") reopen(end.error);
});

There is also no cross-process locking: "One process owns a storage at a time." Ownership of a storage is the host's responsibility, which on Cloudflare is guaranteed by the Durable Object model itself.

14.8 Concepts in this chapter#

• ACID transactions. Atomicity (all or nothing), consistency (invariants hold), isolation (single writer here), durability (survives failure, to a stated degree).
• Single-writer principle / serial execution. Serialize all mutations to eliminate write conflicts and locks.
• Write-ahead / commit-then-publish. Durability before visibility.
• Read-your-writes and interface restrictions. Forbid patterns (read after write) whose correct semantics would be expensive.
• Ownership boundaries and defensive copying. Detach values crossing a storage boundary.
• WAL, checkpoints and fsync. How databases make commits cheap and when data is truly on disk.
• Commit records and two-phase publication. Data first, confirming marker last; recovery ignores unconfirmed data.
• Atomic rename and stale file descriptors. Replace files safely; beware writes to unlinked inodes.
• Keyset (cursor) pagination. Stable, index-friendly paging.
• Hybrid relational/JSON storage; partial indexes; STRICT tables.
• Conformance testing. One test suite defines the contract for every implementation.
• Fail-stop. Stop on uncertain failure; recover from durable state.

Exercises#

1. Explain why #enqueue chains on run.then(() => undefined, () => undefined) rather than on run. What happens to the line if a job rejects otherwise?
2. Write a commit callback that violates the table rules. What error do you get, and how would you restructure it?
3. Simulate a JSONL crash: a sidecar has two new lines and main.jsonl ends with a torn half-line. What does recovery do with each?
4. With fsync: true, why must sidecars be flushed before appending the marker? Construct the failure if the order were reversed.
5. Compare synchronous = NORMAL and FULL in WAL mode. Which failure each survives, and what a host that charges customers per answered request should choose.
6. Write a storage backend in which conversation() returns the stored object itself. Which conformance test should fail, and what production bug does it model?
7. Why does a storage error fail the whole Session instead of retrying the commit?