SQLite is the easy part; durability is the feature

“Use SQLite by default” is good product advice and not much of an implementation story. The interesting failures arrive after the first run works.

What happens when a schema changes six months later? When one message row is corrupt? When two Smith processes open the same workspace? When a branch crashes halfway through copying history? When DELETE succeeds but the WAL keeps the file just as large? When tool output containing a secret lands in a sidecar created with the wrong permissions?

Efferent’s current conversation store answers those questions in about one file: packages/providers/src/store/sqliteStore.ts. SQLite removes the setup ceremony. The durability posture is what makes “the agent remembers” a promise rather than a demo.

One file, created before the user thinks about storage#

Smith opens a workspace-local database and creates its parent directory on demand. There is no server, connection string, or migration command. The schema owns its own lifecycle.

const database = new Database(dbPath, { create: true })
chmodSync(dbPath, 0o600) 
database.exec("PRAGMA journal_mode = WAL;")
database.exec("PRAGMA busy_timeout = 5000;")
migrate(database)

The ordering of those lines is not cosmetic.

chmod happens before WAL mode creates -wal and -shm sidecars, so they inherit owner-only permissions. Conversation rows contain everything the model saw, including file contents and shell output. Treating the database as ordinary cache data would quietly widen access to secrets.

WAL lets readers proceed while a writer commits. busy_timeout makes a second process wait up to five seconds for a lock rather than turning normal overlap into an instant SQLITE_BUSY. Neither setting is exciting until the first time two terminals point Smith at the same repo.

Migrations are an append-only array#

The schema version lives in SQLite’s own PRAGMA user_version. Startup slices the migration array from that version, runs each remaining step in a transaction, then records the new length:

const migrate = (db: Database): void => {
  const version = (db.query("PRAGMA user_version").get() as {
    user_version: number
  }).user_version

  MIGRATIONS.slice(version).forEach((step) =>
    db.transaction(() => db.exec(step))(),
  )
  db.exec(`PRAGMA user_version = ${MIGRATIONS.length}`)
}

The rule is simple: schema growth appends to MIGRATIONS; released entries are never edited. The first migration is idempotent because pre-versioning databases may already contain the initial tables while still reporting version zero.

Bundling migrations as strings beside the store avoids a packaging trap. A source-run TypeScript CLI cannot forget to include a migrations directory in its publish artifact when there is no external directory to include. Code and schema history cross the boundary together.

Positions are assigned by the database#

Messages form an ordered log. The caller never asks for the current maximum and then inserts the next number — that would split one invariant across two operations. One statement calculates, inserts, and returns the durable position:

INSERT INTO messages (conversation_id, position, content, created_at)
SELECT ?1, COALESCE(MAX(position) + 1, 0), ?2, ?3
FROM messages WHERE conversation_id = ?1
RETURNING position

The composite primary key (conversation_id, position) is the final arbiter. Sequence belongs to the store, not to whichever loop happens to be appending today.

Decode strictly, salvage locally#

Reading JSON.parse(row.content) as AgentMessage would turn disk bytes into a trusted domain value by assertion. The store instead composes JSON parsing with the AgentMessage schema:

const decodeMessage = Schema.decodeUnknownEither(
  Schema.parseJson(AgentMessage),
)

The surprising choice comes next. A row that fails decoding is logged and skipped; valid neighbors still load. That is not the only defensible policy. Failing the entire conversation would be stricter and can make one old or corrupt row unrecoverable through the product.

Efferent chooses row-granular salvage: preserve the typed boundary, admit the hole, keep the rest available. The warning includes the operation and conversation id so the loss is observable. Silent casts would hide corruption; fail-whole would amplify it.

A branch must be atomic#

:branch creates a new conversation from a chosen point in the trail. That operation copies three related things: the conversation metadata, the bounded message prefix, and the latest checkpoint whose covered position falls inside that prefix.

All three writes happen in one transaction:

db.transaction(() => {
  insertConversation(forkId, source)
  copyMessages(id, forkId, upToPosition)
  copyApplicableCheckpoint(id, forkId, upToPosition)
})()

Without the transaction, a crash can leave a branch that exists in the picker but contains half a trail or a checkpoint pointing past its copied messages. “Branch” is one domain action, so the database sees one commit.

This is a recurring rule in small local tools: SQLite does not make an operation atomic merely because everything is in one file. You still have to draw the transaction boundary around the user’s concept.

Delete is not the same as reclaim#

The store can prune conversations older than a cutoff. It deletes their messages, checkpoints, and metadata in one transaction, then runs:

PRAGMA wal_checkpoint(TRUNCATE);

That last line is the difference between logical deletion and the user seeing disk space return. In WAL mode, deleted pages can remain in the log; a prune command that never checkpoints the WAL satisfies SQL and disappoints the filesystem.

Pruning works at conversation granularity. It never edits the middle of a retained transcript, so prompt-prefix stability and auditability remain intact.

What the default costs#

Efferent is a local, source-run agent and chooses one-process SQLite semantics. A shared service would need a different adapter and a concurrency review, not an environment variable pretending two databases are interchangeable.

Row salvage can hide a missing turn behind a warning. A five-second busy timeout delays rather than solves pathological contention. Embedded migration strings demand discipline never to rewrite history. WAL adds sidecars and operational details a toy new Database() example omits.

Those are the real contents of “zero config.” The user performs no setup because the program has taken responsibility for permissions, migrations, concurrency, corruption, atomicity, and reclamation. SQLite is what makes that responsibility tractable. It is not what makes it disappear.