deepseek-harness/packages/session-persistence/session-persistence-sqlite

@deepseek-ai/dsh-session-persistence-sqlite

A SQLite durable session-persistence backend — a second SessionPersistence implementation (session persistence), built to validate that the abstract seam and the shared runPersistenceContract suite are genuinely backend-agnostic. It satisfies the SAME contract as dsh-session-persistence-jsonl (append-only, contiguous-seq, lazy materialization, interrupted-turn close on load), expressed over node:sqlite rows instead of file bytes.

locate(meta) returns undefined: all sessions share one database, so there is no honest independent per-session transcript path.

TODO: this backend talks to node:sqlite directly. If a cordis database service (cordis/db / a @cordisjs SQL driver plugin) is adopted, route through that instead of holding a raw DatabaseSync here — the contract surface (SessionPersistence) would not change, only the storage driver.

Storage model

Each SessionEvent maps 1:1 onto a row in an events table (session_id, seq, type, time, data, source_event_seqs, surface_op) — data is the event payload as JSON text, so the row shape is the event verbatim (including assistant/chunk, keeping seq contiguous). The two TEXT columns source_event_seqs and surface_op are nullable; they store the event's optional surface-metadata fields (see session surface). Out-of-log metadata (SessionHeader) lives in a sessions row. A sessions row is written only by the first append — its existence is the lazy-materialization signal (list reports exactly the sessions that have a row), so no separate column is needed.

The repository's Node range supports unflagged node:sqlite. The database enables foreign keys and uses the configured journal mode (wal by default; use a rollback mode where WAL shared-memory files are unsuitable). PRAGMA user_version stores the table-layout version; databases with any other version are rejected because this unreleased format has no migrations.

On filesystems with POSIX modes, the backend requests mode 0700 for missing directories and exclusively creates a missing database with mode 0600 before SQLite opens it; the process umask may further restrict both. New WAL, shared-memory, and persistent rollback-journal sidecars receive the database's resulting owner-only mode. Existing directories, database files, and sidecars keep their modes; filesystem setup errors other than an existing database fail initialization. These defaults prevent incidental exposure through a permissive process umask, but do not protect database confidentiality or integrity when another principal can replace the database entry in its parent directory.

Contract semantics over rows

• Append = a transaction. append runs BEGIN/COMMIT around the batch: it materializes the sessions row (if still lazy) and INSERTs every event, asserting the contiguous-seq contract first (the first event's seq must equal the stored next-seq). A mid-batch failure (a UNIQUE violation on a duplicated seq) rolls back entirely, so the stored log and the in-memory cursor stay consistent. (load() already balanced the stored log, so append never has to repair a crash tail.)
• Lazy materialization. create() records intent in memory only — no row is written until the first append. A created-but-never-appended session has no sessions row, so it is absent from list() (which reports exactly the sessions that have a row).
• Interrupted-turn close on load. load() implements the shared crash-recovery contract: preserve the valid interrupted turn, append its synthetic closing events in one transaction, and remove only a torn tail row. Committed parse errors or sequence gaps make the session unloadable. Because recovery mutates stored rows, the next append starts from a balanced log and accurate cursor.

Configuration (schemastery)

interface Config {
  path: string   // SQLite database file path, or ':memory:' for an in-process DB
  journalMode?: 'wal' | 'delete' | 'truncate' | 'persist'   // journal_mode pragma; default 'wal'
}

Write path

Like the JSONL backend, the plugin also installs the session/event → buffer → session/flush drain: it copies each already-frozen event into a persistence-owned buffer, persists a fork's seed once on session/created, keeps a per-session write cursor so a resumed session never re-appends stored events, and seeds existing live sessions on apply (HMR does not replay session/created). Dispose awaits every in-flight init + final drain and then closes the database, so no write lands after teardown.

Model Experience

Resumed conversation history

What the model sees

SQLite storage contributes no live prompt or schema. Loading restores the same surface history as JSONL and preserves prior headers for reconstruction; the new loop composes its current envelope. Each unanswered call in interrupted rows is balanced with the exact error text Tool call interrupted by a crash; no result was recorded. Row metadata and raw chunks are not messages.

Token effect

Zero live-request tokens. Resume restores retained history and pays the current envelope, plus the quoted repair result for each interrupted call.

KV Cache effect

SQLite storage does not mutate live request prefixes. A resumed loop can reuse provider cache only when its reconstructed history, current envelope, and model route match; crash-repair results append.

Known Limitations and Deferred Work

• DatabaseSync is synchronous — every append transaction blocks the event loop for its duration; acceptable for local stores, a throughput ceiling for busy multi-session servers.
• Write contention has no wait or retry policy — the backend sets no busy timeout and retries no locked-database error, so another connection holding a write transaction makes the operation reject immediately.
• Only the current SCHEMA_VERSION opens — a database with any other schema version is rejected rather than migrated (unreleased software; no persisted user data to preserve).
• Nothing deletes stored sessions — rows accumulate until removed externally (the seam has no deletion surface; ON DELETE CASCADE is wired for such out-of-band cleanup).