Embedded stores need explicit export and repair paths

Embedded stores need explicit export and repair paths because putting a database inside an application removes an operator, not the need for operations. I gain local latency, fewer network dependencies, and simple deployment. In exchange, I inherit responsibility for getting user data out, recovering after interruption, and proving that repaired state is trustworthy. If those actions exist only as improvised developer commands, the storage design is incomplete.

An embedded database can look deceptively like an ordinary file. SQLite may have a main database beside a write-ahead log and shared-memory index; a pure-Rust store may hide transactions behind one process-level API. The application makes this machinery pleasantly invisible during normal use. It also leaves users unable to tell which files form consistent state or whether copying a directory is safe. I treat that opacity as a product obligation.

For SQLite in WAL mode, export cannot mean copying the main file while the application runs. Committed pages may still live in the WAL, so that copy can omit valid state. SQLite’s online backup API copies pages under appropriate locking and can run incrementally. I want it behind a named export action with completion status and a verified destination. A logical export supports portability; a WAL-safe backup separately preserves faithful restoration.

Repair should be a workflow, not a button labelled “fix.” After a crash, the first connection may hold an exclusive lock during recovery, whose duration grows with the WAL. SQLITE_BUSY is not evidence that random retries are safe. I want the application to stop competing connections, preserve the original files, open through its canonical path, and report whether recovery and read-only verification completed. Failed repair should restore into a new file rather than mutate the only surviving copy blindly.

Checkpointing belongs on that operational surface. Routine PASSIVE checkpoints avoid blocking but may stop at an active reader; FULL, RESTART, and TRUNCATE progressively demand more coordination. TRUNCATE fits quiescent shutdown or explicit maintenance, not an arbitrary busy moment. I would expose WAL size and checkpoint progress, keep read transactions short, and treat growth as a diagnosis signal. A command that ignores returned checkpoint statistics can announce success while starvation continues.

The repair path must also preserve the configuration that made the database correct. Many SQLite PRAGMAs are per connection: foreign keys, busy timeout, synchronous mode, cache, memory mapping, and temporary storage must be reapplied on every open. WAL is unsafe on network filesystems whose shared-memory semantics do not hold across machines. A recovered file opened differently or moved onto NFS can be readable yet operated under the wrong guarantees. My canonical opener is part of the practical data format.

Engine choice changes how credible these promises are. SQLite offers a long-lived, widely readable format and a defined backup mechanism. redb offers single-file ACID transactions, MVCC, and a documented stable format for genuinely key-value data. sled is better reserved for ephemeral or rebuildable indexes because its beta status and format history weaken long-horizon recovery. I ask not only how fast an embedded store runs, but who can open its bytes after the current library, maintainer, or application version is gone.

Encryption makes export and repair stricter. With SQLCipher, the key must be the first SQL applied to a connection, and the recommended path derives a 256-bit key from a passphrase with Argon2id instead of accepting engine defaults. Recovery therefore depends on more than the database file: salt, KDF parameters, cipher compatibility, and key-change procedure belong to the contract. An encrypted backup whose derivation metadata cannot be reconstructed is durable ciphertext, not recoverable data.

I make one precise concession: a store used only as a deterministic, disposable projection does not need a bespoke user export if its canonical source remains intact and a clean rebuild is routinely tested. That boundary fits caches and derived indexes, not chat history, user-authored records, or state whose loss changes what the user owns. “Rebuildable” must describe an exercised procedure with known inputs, not an intention added after corruption.

I want every embedded-store release to rehearse three paths: export a consistent snapshot, diagnose and repair without destroying evidence, and restore into a fresh location before cutover. Version and engine metadata should travel with the artifact, and fixtures should prove an older export still opens. Embedding storage is a deployment convenience. Recoverability is a lifecycle property, and I believe it exists only when the application can invoke and verify it before an incident.