SQL Session Backend

WAL + busy_timeout + FULL sync serves concurrent agent processes

Learning Objectives

After this chapter you will be able to:

• Explain why the JSONL tree needs to be stored in SQLite for multi-process scenarios
• Describe how WAL mode lets multiple agent processes share one session database concurrently
• Explain how the SessionStorage contract enables pluggable backends (JSONL/Memory/SQLite)
• Understand what each PRAGMA (WAL, synchronous=FULL, busy_timeout) protects against

Key Source Files

File	Lines	Responsibility
packages/storage/sqlite-node/src/index.ts	97	Entry: factory functions, exports
packages/storage/sqlite-node/src/sqlite/repo.ts	192	Database wrapper: async interface over sync node:sqlite
packages/storage/sqlite-node/src/sqlite/storage/index.ts	449	SqliteSessionStorage implementation
packages/storage/sqlite-node/src/sqlite/migrations/001_initial.sql	59	Table DDL: sessions, session_entries, branch_entries

Design Motivation: Is JSONL Not Enough?

Problem: JSONL (→ Chapter 4) is append-only, ideal for single-process writes. But in pi-server (→ Chapter 19):

• Multiple RPC subprocesses may operate on the same session simultaneously
• Conditional queries are needed ("find all sessions that used opus")
• Branch navigation is required ("find the leaf node of the 5th fork")

JSONL limitations under concurrency:

• Query = full file scan (O(n))
• Concurrent writes = file lock contention
• Branches = complex multi-file management

Analogy: JSONL is a paper notebook — fast to write, never loses data, but hard to search and impossible for multiple people to write simultaneously. SQLite is a database — supports queries, concurrency, and transactions.

PRAGMA Configuration: Three Lines That Matter

PRAGMA journal_mode = WAL;       -- Write-Ahead Logging
PRAGMA synchronous = FULL;       -- full fsync on every commit
PRAGMA busy_timeout = 5000;      -- wait 5s for lock before erroring

WAL (Write-Ahead Logging):

• Writers don't block readers — readers see the old version while writer appends to WAL file
• Multiple agent processes can read sessions concurrently; only one writes at a time
• ~10x better concurrency than the default rollback journal

synchronous = FULL:

• Every transaction commit is fsynced to disk
• Cost: writes are ~2x slower
• Benefit: power-loss safe — sessions are users' work records, they must not be lost

busy_timeout = 5000:

• On lock contention, wait 5 seconds instead of failing immediately
• Brief lock contention is normal in multi-process scenarios
• 5 seconds is enough for the preceding transaction to complete

SessionStorage Contract: Pluggable Backends

// Interface defined in agent-core
interface SessionStorage {
  append(sessionId: string, entry: SessionEntry): Promise<void>
  branch(sessionId: string, fromEntry: string): Promise<string>
  getLeaf(sessionId: string): Promise<SessionEntry | null>
  getEntries(sessionId: string): Promise<SessionEntry[]>
  setLabel(sessionId: string, label: string): Promise<void>
  compact(sessionId: string, summary: string): Promise<void>
}

// Three interchangeable implementations:
// - JsonlSessionStorage (→ Chapter 4, default for CLI)
// - MemorySessionStorage (for tests and evals)
// - SqliteSessionStorage (this chapter, multi-process)

Key design: The upper layer (AgentSession) depends only on the interface, never knowing whether the backend is JSONL or SQLite. Switching backends = swapping one factory function, zero changes upstream.

Table Schema

-- 001_initial.sql
CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL,
  label TEXT,
  parent_id TEXT REFERENCES sessions(id)  -- branch relationship (self-ref)
);

CREATE TABLE session_entries (
  id TEXT PRIMARY KEY,
  session_id TEXT REFERENCES sessions(id),
  seq INTEGER NOT NULL,           -- sequence number within session
  kind TEXT NOT NULL,             -- message/model_change/compaction/...
  data TEXT NOT NULL,             -- JSON-serialized entry payload
  UNIQUE(session_id, seq)
);

CREATE TABLE branch_entries (
  session_id TEXT REFERENCES sessions(id),
  branch_point TEXT,              -- which entry the fork originates from
  child_id TEXT REFERENCES sessions(id)
);

Tree mapping: The JSONL tree (→ Chapter 4) maps to sessions.parent_id self-reference. A fork = inserting a new session row whose parent_id points to the original.

Engineering Insights

1. storage/index.ts at 449 lines: Mostly interface-to-SQL mapping. Each method is 1–3 SQL statements — complexity lies not in SQL itself but in correctly handling branch sequence numbering.

2. node:sqlite is synchronous: Node's built-in node:sqlite module exposes only DatabaseSync. repo.ts wraps it in async — not for concurrency, but for interface consistency (SessionStorage is an async contract).

3. SQLite coexists with JSONL: SQLite does not replace JSONL — single-user CLI scenarios are simpler without a database file. SQLite serves pi-server's multi-process needs. Both coexist behind the SessionStorage interface.

Cross-Chapter Links

Direction	Chapter	Relationship
Prerequisite	Ch04 Session Tree	JSONL tree is the logical model SQLite stores
Prerequisite	Ch11 AgentSession	AgentSession writes through the SessionStorage interface
Prerequisite	Ch19 RPC Server	Multiple RPC processes create the concurrency need
Related	Ch03 Compaction	compact() updates the summary row in SQL
Related	Ch21 Evals	Evals use MemorySessionStorage for isolation

Check Your Understanding

1. If synchronous were changed from FULL to NORMAL, under what scenario would data be lost? Is that risk acceptable for session data?
2. Why use parent_id self-reference instead of nested JSON for branch relationships? How does this affect query performance?
3. Without WAL (using the default journal mode), what specific problem would multi-process concurrency cause?

Takeaways

• JSONL tree stored in SQLite — enables queries, concurrency, and transactions
• WAL + FULL + busy_timeout: concurrent reads/writes, power-loss safety, graceful lock waiting
• SessionStorage contract: JSONL/Memory/SQLite backends are interchangeable
• Serves pi-server multi-process scenarios; single-user CLI still defaults to JSONL

---

This chapter is based on the main-branch source of earendil-works/pi.