Persistence and Database Design

Conclusion: persistence is a portfolio of ownership contracts, not “the database”

This separation is the chapter's most important result. JSONL and SQLite are alternative physical providers for the same Session contract, but the generic JSON and SQLite backends are not Session databases. Likewise, a Workspace account is not a foreign-key ownership claim over a transcript, and an attachment reference is not the object itself.

1. The ownership map prevents accidental cascades

2. SessionPersistence is a behavioral seam, not a CRUD repository

create(header)              // reserve identity; may remain purely in memory
append(id, contiguousBatch) // resolve only after durability
inspect(id)                 // balanced logical view; no physical repair
load(id)                    // commit repair when cold, then return a balanced view
prepare(id)                 // reserve the exact unpublished Session for resume
readFrom(id, seq)           // detached, non-mutating stored suffix
list / listSnapshots        // materialized logs only

The seam makes crash semantics part of the interface. A complete, valid interrupted final turn is preserved and deterministically closed, while a provider returns a contiguous prefix plus an opaque marker for the suffix it classifies as unrecoverable. That abstraction is broader than a literally partial physical record; Sections 6 and 8 audit the concrete classification. A created-but-never-appended Session may leave no artifact at all. SQLite therefore cannot be substituted by implementing ordinary create/read/update/delete calls—the provider must implement append continuity, identity, repair, and quiescent shutdown.

3. The coordinator is the real portability layer

PersistenceCoordinator owns lazy identity collision checks, write-behind controllers, per-ID serialization, prepared-session caching, live adoption, crash-repair sequencing, revision retries, and disposal. Providers contribute media primitives rather than reimplementing this state machine.

4. Write-behind separates hot-path admission from a durable quiescent point

session/event → structuredClone → pending queue
                 first item arms one fixed deadline
deadline → stable batch → backend append
failure  → batch restored at queue head; automatic retries pause
flush    → cancel timer → join active write → retry and drain to empty

Later events do not extend the current batching deadline. Events admitted during one write form a follow-up batch. A background failure is reported while the exact batch remains retained; explicit flush is the retry and quiescence barrier. Backend disposal drains controllers before it closes media.

5. JSONL is a logical event stream with two physical encodings

The configured root is required, then resolved once in the constructor so later process.cwd() changes cannot split one backend. Session IDs are injectively escaped before becoming path segments. Header identity and the derived cwd/id path are checked before repair or append, and a root mixing compression suffixes or the retired flat layout is rejected rather than guessed.

6. JSONL durability is strong, but its repair is intentionally two-stage

Raw mode tolerates only an incomplete final record or a defect strictly after the last committed turn/end. Zstandard mode rejects a broken complete frame, but can salvage complete JSONL records emitted from a structurally incomplete final frame. It truncates at that frame's start, then rewrites only recovered records and closers—not the earlier committed prefix.

7. Session SQLite maps the same log contract onto strict rows

The database carries both application_id and user_version. A pristine database is initialized and stamped; a nonempty unversioned database, a foreign application identity, or any version other than SCHEMA_VERSION = 15 refuses. Despite the word “version,” there is no migration path in this pre-release implementation.

8. SQLite append and repair gain transaction atomicity and real suffix reads

First materialization and every event in its batch commit in one transaction. Later append batches do the same and increment the Session revision once. Load scans a transactionally consistent full row set, identifies the first invalid tail seq, then repairs by deleting seq >= tornMarker, inserting deterministic closers, and incrementing the revision in one transaction.

9. The documented WAL default has a direct-constructor trap

The same shape appears in the generic SQLite storage backend: Schemastery promises a WAL default, while its public constructor casts an omitted optional value and the open path dereferences it. Loader-normalized plugin construction is safe; direct programmatic construction without journalMode is not.

10. Generic Storage deliberately knows less than SessionPersistence

Storage hub
  ├─ backend registry: name → one medium and optional facets
  └─ mounted forms: domain today

KV backend contract
  open(unit descriptor) → loadAll / putRecord / deleteRecord / setGlobal / close

Domain form
  spec + zod schemas + route → authoritative memory + one write chain + change events

KV values are opaque JSON to the backend. A unit guarantees each single call is atomic and durable, but explicitly does not serialize concurrent calls. The Domain layer supplies that ordering, validates data when opening, applies backend durability before changing its in-memory map, and emits domain/changed only after both agree.

The generic SQLite provider gives each unit a row in units, an optional row in unit_globals, and one STRICT u_<unit>_<table> table per declared table. Its physical user_version is 1; any other stamped layout refuses instead of migrating. Each KV mutation is one prepared statement, so the provider supplies per-call atomicity while Domain still supplies write order.PERSIST-STORAGE-SQLITE-SCHEMAPERSIST-STORAGE-SQLITE-UNIT

11. JSON KV publication is carefully durable, but relative roots remain dynamic

Every JSON KV mutation republishes the entire human-readable unit file. The writer creates a same-directory owner-only temporary file, writes and fsyncs it, atomically renames it over the target, then fsyncs the parent directory on POSIX. Windows receives atomic replacement through libuv but no explicit write-through flag. There is no cross-process writer lock.

12. Domain close has a one-way failure state

The happy path is sound: close() immediately rejects new writes, drains the settled write-chain tail, closes the backend unit, marks reads closed, then releases the facility's name reservation. Repeated close calls share one promise.

13. Attachments use commit-before-reference and content-addressed ownership

Deduplication is safe because an existing object is read and hashed before it is accepted. Reads re-hash bytes, compare stored metadata with the logged reference, and preserve cancellation. Objects are retained indefinitely because resumed and forked Sessions may share them; no Session or Workspace deletion implicitly garbage-collects them.PERSIST-ATTACH-LIMITS

14. Attachment read-time decoding is documented incorrectly

The implementation still verifies integrity and reference metadata; the drift is about computational work and malformed-payload revalidation, not about skipping digest verification. Documentation should say “full decode at admission, header probe after digest verification on read.”

15. Workspace is a recoverable two-write registry over Domain state

The workspace domain (version 2) stores one record per Workspace—canonical path, title, ordered candidate Session IDs, and timestamps—plus global initialization, Workspace order, archive IDs, and an optional pending create/delete marker. First startup lists only Session headers, canonicalizes valid cwd directories, groups them, and writes the initialized marker last.PERSIST-WORKSPACE-INITPERSIST-WORKSPACE-BOOTSTRAP

Create/delete cannot be one generic cross-record transaction, so the registry writes a pending marker before the record/order pair can diverge. Startup completes only the named mutation; unexplained order/table, duplicate-path, or duplicate-session ownership still fails loud. Workspace deletion removes the registration and account only, never the directory, files, live Session, persisted log, or attachment.PERSIST-WORKSPACE-MUTATIONPERSIST-WORKSPACE-OWNERSHIP

16. Workspace title, schema, and refresh contracts have visible gaps

17. A useful Codex comparison: similar media, different authority topology

For Codex users, the filenames can look deceptively familiar. At official public Codex commit 66919805ea080053d1933b6b43afeb0d8bf70c91, the rollout module says JSONL Session rollouts are persisted for replay or later inspection, and its recorder writes canonical rollout items to JSONL. The public config labels sqlite_home as the directory for the SQLite state DB; the App Server's thread/list contract says its default path may scan JSONL rollouts to repair metadata, while useStateDbOnly opts out.

18. Audit result: strong contracts, with seven concrete follow-ups

• Normalize SQLite defaults at constructor boundaries. Fix both direct-constructor journalMode paths or require a normalized config type.
• Name the torn-tail policy precisely. Document that both providers may discard physically complete defective records after the last closed Turn; consider preserving diagnostics or requiring explicit repair when physical tearing is not established.
• Resolve JSON KV post-publication failures. Keep Domain, unit memory, and the published target in a defined state when directory sync fails after rename.
• Freeze JSON KV root identity. Resolve an explicit relative root once, matching Session JSONL behavior.
• Specify failed Domain closure. Decide retry and name-release semantics so a close error cannot strand a readable-but-unwritable handle.
• Correct attachment documentation. State full decode on admission and digest-plus-header verification on read.
• Tighten or document Workspace boundaries. Remove the dead create-time title branch, validate user-facing title/timestamps/ids as intended, and expose or stop promising a runtime full refresh.

The system's central design remains coherent: durable facts have explicit owners and providers share one Session contract. Recovery is conservative through the last closed Turn, while the final open tail follows a broader policy that should be named explicitly. The static gaps cluster around repair classification, post-publication failure, configuration, lifecycle, documentation, and cache refresh—not around confusion over which component owns the event log.