The event history is a SQLite file, history.db #629

Part of #623.

Scope

After this PR the event history is one SQLite file, history.db, in the data directory. A fresh install writes no events.jsonl. simlock events --since and GET /v1/events?since= read the new file, with or without a running daemon, on a worker and on a gateway. Once the daemon holds its socket, and every hour, old event rows are pruned by age and by their stored size, and the freed space goes back to the disk. A damaged file is set aside and a fresh one started. simlock status says when the history is not written or was set aside. The history module offers a projection step inside the event's transaction; #630 adds the first projection. No figures yet. ADR 0032 §1, §2, §3, §4 (the set-aside half) and §5 (the events.replay half) fix the rules.

In short

Every event the daemon emits is written to <data dir>/history.db instead of events.jsonl. Reading back and pruning become indexed queries. Nothing a user types changes. simlock status gains up to two lines, and simlock config set drops a key that no longer exists.

Set aside: a damaged history.db is renamed to history.db.damaged-<epoch ms>, with its -wal and -shm files when they exist, kept, never deleted, and a fresh file takes its place. Example: history.db.damaged-1760605920000.

Stored size of the event rows: the sum, over every event row, of the UTF-8 byte length of its stored envelope JSON. Example: 1 000 000 events of about 230 bytes each are about 230 MB, under the 256 MiB default.

flowchart LR
  E[Module emits] --> B[Bus]
  B --> H["History writer (changed)<br/>BEGIN; INSERT OR IGNORE event; projections; COMMIT"]
  H --> D[(history.db)]
  D -->|"timestamp index (changed)"| R[events.replay / events --since]
  S["Daemon holds its socket"] --> Q{"quick_check (new)"}
  Q -->|not ok| A["Set aside (new)"] --> D
  Q -->|ok| D
  T["Prune (new): after the claim + hourly<br/>rows, vacuum, WAL checkpoint"] --> D

Session, before and after:

$ simlock events --since 1h          # daemon running or stopped
{"id":"evt_…","seq":12,"timestamp":…,"event":"lease.granted",…}   # same output before and after

$ ls ~/.simlock
before: config.json daemon.log events.jsonl state.json …
after:  config.json daemon.log history.db state.json …   (an old events.jsonl is left untouched)

$ simlock status        # after a write failed
...
History: the event history is not being written; see daemon.log.

$ simlock status        # after this start set a damaged file aside
...
History: the event history was damaged and set aside at 2026-10-16 09:12; a fresh one was started.

$ simlock config set log.level debug     # config.json still has eventLog.rotateBytes
Removed eventLog.rotateBytes from /Users/me/.simlock/config.json: it is no longer used.     # stderr, once
Updated log.level in /Users/me/.simlock/config.json; takes effect on daemon restart.

The Removed line's variable part is the config file's path.

Quantities: eventLog.retention is milliseconds, counted back from the prune's Clock.now(), compared with each event's timestamp. eventLog.maxBytes caps the stored size of the event rows (above), checked after the age prune; projection rows do not count (feature #623 Shared values, Config keys and Prune).

If this goes wrong, a user sees simlock events --since miss events from before a restart, or simlock status say the history is not written on a healthy machine.

Technical spec

Facts

Modules touched

Searches (in src, e2e, ui, docs, scripts, .github, package.json, ADRs left out): events\.jsonl|EVENT_FILE_NAME 163 hits in 26 files; EventHistory|eventHistory 61 in 14; readEventFile|readEventHistory 36 in 6; eventLog\. 34 in 11; rotateBytes 87 in 30 (most are log.rotateBytes or test configs); retentionMs:|totalMaxBytes 61 in 15.

• src/ports/sqlite.ts (new): the node:sqlite port. One interface, one Node adapter, no fake (F28): tests use the adapter over a temp file. Only this file names node:sqlite, and it loads it with a dynamic import() on first open, so the warning filter is in place first. index.ts and the ports manual's table gain it.
• src/history/ (new component): owns history.db. Placement: a new module, not inside src/bus. Why:
  • Architecture rule 4: a durable store with a schema, prune timer and damage handling is its own functionality; the bus is the in-memory fact stream.
  • Rule 15: the history is optional. The composition root can wire it or nothing, and leasing behaves the same. The bus never imports it.
  • Rule 18: a new module hands out one factory and a handle. The bus's index.ts predates the rule and should not grow.
  • Daemon state stores in SQLite (state.db) #624 will reuse the SQLite port, not this module.
• src/bus/: event-file.ts, its test, and the EventHistory / readEventFile / readEventHistory / EVENT_FILE_NAME exports go. eventKey stays.
• src/daemon/main.ts: openEventHistory builds the history before any event, with a NodeFilesystem of its own, whatever options.filesystem is, because node:sqlite writes the real disk (F27). void history.start() runs from onSocketClaimed in both modes (F24); history.stop() runs after #stop (F25). The warning filter is installed at the top of the entry (F23, :1438). dispatcher.ts, server.ts: the replay type, the history field in status.get.
• src/gateway/dispatcher.ts: the replay type, history in its status.get. worker-link.ts unchanged (F5).
• src/cli/index.ts: readEventFile → the history reader; the two status lines; config set drops eventLog.rotateBytes. src/cli/main.ts: the warning filter.
• src/core/config.ts: drop rotateBytes; validateEventLogBounds checks the 16 MiB floor instead.
• src/contract/operations.ts, schemas.ts: status.get history; config.get eventLog without rotateBytes.
• src/ports/logger.ts: the retentionMs and totalMaxBytes options and the generation code behind them go (F14); daemon.log keeps its one rotated file. Their tests in logger.test.ts go with them.
• Tests named in F29 and the test configs with eventLog.rotateBytes.
• package.json engines → >=22.13. CI unchanged: it runs the latest 22.x (F15).
• Docs: docs/CLI.md (2238-2245, 2260, 2515, config set), docs/CONFIGURATION.md (66-68), docs/HTTP-API.md (1493-1496, and GET /v1/status), docs/EVENTS.md (155, 166-170, 186-195), docs/internal/EVENTS.md (13, 146, 239, 266-274), docs/internal/flows/startup.md:11, docs/internal/agent-rules/events.md rule 7, docs/internal/ARCHITECTURE.md (bus row 55, a new history row), manuals of bus, daemon, cli, ports, and a new src/history/AGENTS.md and CLAUDE.md.

flowchart LR
  main["daemon/main.ts (changed)"] --> hist["history (new)"]
  cli["cli (changed)"] --> hist
  hist --> sqlite["ports/sqlite (new)"]
  hist --> fs["ports/filesystem"]
  hist --> bus["bus (changed: file code out)"]
  main --> bus
  disp["daemon/dispatcher (changed)"] -. replay, status .-> hist
  gw["gateway/dispatcher (changed)"] -. replay, status .-> hist
  wl[gateway/worker-link] --> bus
  config["core/config (changed)"]
  contract["contract (changed)"]

sequenceDiagram
  participant C as CLI
  participant D as Daemon
  participant H as history.db
  C->>D: events.replay { sinceTs }
  alt daemon answers
    D->>H: SELECT … WHERE timestamp > ? ORDER BY timestamp, seq, rowid
    H-->>D: rows
    D-->>C: envelopes
  else no daemon
    C->>C: stat history.db
    alt missing
      C-->>C: print nothing, exit 0
    else present
      C->>H: open readOnly; same SELECT
      alt open or read throws
        C-->>C: one JSON error line on stderr, exit 1
      end
    end
  end

Surface and extension points

• Port src/ports/sqlite.ts: SqliteOpener.open(path, { readOnly }) → Promise<SqliteDatabase> with exec, prepare(sql) → { run, get, all }, close. Node adapter NodeSqliteOpener. As small as its callers need.
• Module src/history/index.ts exports:
  • createEventHistory({ bus, sqlite, filesystem, clock, logger, dataDirectory, config: { retention, maxBytes }, projections }) → handle { replay({ sinceTs? }), status(): { opened, writing, setAsideAt? }, start(): Promise<void>, stop(): Promise<void> };
  • openHistoryReader({ sqlite, filesystem, dataDirectory }) → { read({ sinceTs }) } for the CLI;
  • the HistoryProjectionDefinition type and HISTORY_DB_NAME. Nothing else. testing.ts holds a reader for tests (F29).
• Lifecycle (architecture rule 12; one named state at every exit):
  • created: the factory only subscribes to the bus; it touches no file. status() is { opened: false, writing: false }. replay answers the ring, with or without sinceTs.
  • start(): load the port; open; auto_vacuum=INCREMENTAL on a new file, then journal_mode=WAL (F22), synchronous=NORMAL, busy_timeout=5000; schema; quick_check; set aside if damaged; write this run's ring events (F26, deduplicated by id; events past the ring's capacity before start() are not written); prune; arm the hourly timer. Then opened: true, writing: true.
  • stop(): sets stopped, cancels the timer, closes the database. A start() still awaiting sees stopped after each await, closes what it opened, and returns: it never opens a file or arms the timer after stop(). A start() called after stop() does nothing.
• Extension point: projections. Projections: request, minute and device fact rows #630's projector is the named implementation (architecture rule 19). Shape per rule 20:
  • HistoryProjectionDefinition { name, create(db) → { apply(envelope), prune(now) } }. Projections: request, minute and device fact rows #630 adds the version, migration and rebuild members it needs.
  • One registry, HISTORY_PROJECTIONS, keyed by name, empty in this PR. One directory per implementation under src/history/projections/. One conformance suite (describe.each over the registry): apply of a duplicate never runs, a throwing apply writes nothing.
  • The history never names a projection (rule 21).
• Transaction per event: BEGIN; INSERT OR IGNORE INTO events …; if changes = 1 then each projection's apply; COMMIT. A duplicate id (F18) runs no projection.
• Prune, in order, each step its own transaction:
  1. Delete event rows with timestamp older than now − retention.
  2. Size: each row stores its size, the UTF-8 byte length of its envelope JSON. The total is kept in memory, read once with SUM(size) at start() and updated by each insert and delete. While the total is over maxBytes, delete the 1 000 oldest rows (by timestamp, then rowid). The loop ends when the total fits or a step deletes no row, so it runs at most ⌈rows / 1000⌉ + 1 steps.
  3. Each projection's prune(now).
  4. PRAGMA incremental_vacuum, then PRAGMA wal_checkpoint(TRUNCATE) (F21).
• Warning filter: both entries (F23) install it before anything loads node:sqlite. It drops only an ExperimentalWarning whose message starts SQLite is an experimental feature; every other warning prints as Node prints it.

Contract and event changes

• status.get gains history: { opened: boolean, writing: boolean, setAsideAt?: number } (feature Operational metrics kept over hours and shown in the console #623 Shared values, status.get history). setAsideAt is a finite epoch ms. Sent on a worker and a gateway, in the starting answer too.
• config.get's eventLog loses rotateBytes.
• This PR bumps the protocol by one. Published surfaces rerun: MCP tools/list, HTTP GET /v1/status, CLI help.
• No event added or changed. docs/EVENTS.md and docs/internal/EVENTS.md change their text on where events are stored.
• Rows read back from history.db are envelopes this daemon wrote; a row whose id fails EVENT_ID_PATTERN is skipped, as the file reader does today (src/bus/event-file.ts:92-101).

Other code on the same state

Searches for the state itself: the event file path (events\.jsonl|EVENT_FILE_NAME, 163 hits), subscribeAll (bus subscribers), the data directory's file names (git grep -l "daemon\.log\|state\.json", 16 files: none deletes or lists the data directory), republish (2 production hits, F5), config.json writers (writeConfigFile, 1 production caller, F10).

Failure modes

State lines:

• history.db: a crash mid-transaction leaves the last commit; a power loss can lose the last few (ADR 0032 §2). Tested by the restart e2e line, not by power loss.
• Set aside: a crash after the -wal rename leaves a main file with no WAL; the next start checks it again and sets it aside if still damaged. A crash after the main rename leaves no history.db; the next start opens a fresh one and sets nothing aside.
• Prune: each step commits alone; a crash between steps leaves fewer deletions, redone at the next start.
• Two writers at once: only the socket-claim holder opens the file (F24).
• A stop during converge, or after convergence-failed (F24): stop() runs; a start() in flight returns without a file or timer.

Rules in play

• Architecture rule 9, "every external API is accessed through a port": node:sqlite only in src/ports/sqlite.ts.
• Rule 15, "dependencies point from the optional to the required": the history subscribes to the bus; nothing required imports it.
• Rules 18-21: one factory; the projection extension point has the rule 20 shape and a named implementation (Projections: request, minute and device fact rows #630); the host names none.
• Rule 11, "every wait that crosses a process boundary is bounded": a wait on another process's lock is at most 5 s.
• Rule 12, "every exit … leaves its subject in exactly one named state": the lifecycle above; no open or timer after stop().
• Events rules 5 and 7: a failing write never reaches the emitter; rule 7's text changes from events.jsonl to history.db.
• Safety: the history deletes rows, never files; a set-aside file is renamed, never deleted.
• ADR 0006 §7, "the event file is an observer": kept for the database. ADR 0014 §4: identity is id; INSERT OR IGNORE enforces it.
• Documentation rule 2: end-user docs name history.db and its keys, no ADR.

Tests

Seam: createEventHistory over a real node:sqlite file in a temp directory with a NodeFilesystem, driven by EventBus.emit and a FakeClock, in a new src/history/history.test.ts modelled on src/bus/event-file.test.ts. CLI lines through runCli with a test environment (src/cli/index.test.ts), e2e through withDaemon in e2e/event-history.test.ts.

• writes every emitted and every republished event as one row equal to its envelope, read back by replay({ sinceTs }) in timestamp, then seq order
• a second insert of an event with an id already stored adds no row and runs no projection step
• a test projection's rows and the event row commit together: a projection that throws leaves neither, and stops writing
• a write that throws logs one error, sets writing false, and later events still reach the ring and other subscribers
• replays the ring without sinceTs; with sinceTs before start(), once writing has stopped, and when the read throws
• a new file has auto_vacuum=INCREMENTAL, WAL, synchronous=NORMAL and a 5 s busy timeout
• the factory touches no file and status() is opened: false; start() writes the events this run emitted before it, once each, and later events as they come
• stop() while start() awaits its open: no file stays open, no timer is armed, a later start() does nothing, and no prune runs an hour later
• a file that fails quick_check and a path that will not open are each renamed to history.db.damaged-<now>, -wal and -shm with it (each skipped when missing, the main file last), a fresh file is written, and status() gives setAsideAt equal to now; a healthy file is not renamed
• a lock another connection holds past 5 s at open, check, write or prune never sets the file aside; at open it leaves opened false
• a failed load of node:sqlite leaves a healthy history.db byte-identical and in place, and opened false
• when the fresh file cannot be opened either, opened and writing are false and replay answers the ring
• prune deletes event rows older than retention and keeps one stamped exactly at the cutoff
• prune deletes the oldest event rows, 1 000 per step, until their stored size fits maxBytes, keeps the newest, and the size on disk of history.db plus -wal is smaller after the prune than before it
• prune runs again one hour after start() on the clock, not before (straddled), and stop() cancels it; a prune step that throws logs one warning and writing goes on
• the conformance suite runs every registered projection (empty registry passes; the suite runs over a test registry of one)
• a row whose id the envelope schema refuses is skipped on replay
• the history reader reads rows a writer committed while that writer is still open; answers empty when history.db is missing; throws when it is present and unreadable
• status.get carries history.opened, writing and setAsideAt on a worker and on a gateway, in the starting answer too (extends the status tests in dispatcher.test.ts and gateway/dispatcher.test.ts)
• simlock status prints the not-written line when writing is false, the set-aside line with the local time when setAsideAt is set, both when both hold, and neither otherwise
• simlock events --since with no daemon reads history.db and launches none (extends src/cli/index.test.ts:5220); with an unreadable history.db it prints one JSON error and exits 1
• simlock config set on a file holding eventLog.rotateBytes writes it without that key and prints the Removed line once; on a file without it, no such line
• a config with eventLog.rotateBytes loads with the unknown-key warning, on a worker and a gateway; eventLog.maxBytes of 16 MiB loads and one byte less fails naming the key (inverts config.test.ts:189, :235, :1729)
• config.get reports eventLog.retention and maxBytes and no rotateBytes
• the daemon and the CLI print no ExperimentalWarning for SQLite, and another warning still prints
• a daemon whose history path is a directory starts, sets it aside and writes a fresh file (extends main.test.ts:1966); a daemon that loses the socket claim leaves a damaged history.db byte-identical and in place
• only src/ports/sqlite.ts names node:sqlite (one scan of every file under src/)
• (e2e) simlock events --since 1h after simlock daemon stop and start prints a lease granted before the restart with its id, with the daemon running and with it stopped (replaces the four file-generation tests in e2e/event-history.test.ts)
• (e2e) with eventLog.retention a few seconds, events of a first run are gone after the next start's prune, and a second run's are there; a fresh data directory has history.db and no events.jsonl, and an events.jsonl placed there before start is byte-identical after
• (e2e) a history.db overwritten with junk while the daemon is stopped is renamed aside at the next start, a lease is granted, and simlock status prints the set-aside line

Done when

• On the fake driver, a fresh install writes history.db and no events.jsonl; simlock events --since 1h prints the run's events with the daemon running and stopped.
• A history.db corrupted while the daemon is stopped is renamed aside at the next start; leasing works; simlock status says the history was set aside; the damaged file is still there.
• With eventLog.retention set to a few seconds, events older than that are gone after the next start.
• eventLog.rotateBytes in a config gives the unknown-key warning, and simlock config set removes it.
• No file under docs/ or the manuals names events.jsonl as the current history, except to say an old one is left untouched.

Out of scope

• Projections, figures, metrics.retention, rebuiltAt, rebuildFailed (Projections: request, minute and device fact rows #630).
• Importing an old events.jsonl.
• daemon.log rotation.
• Registry, tokens, workers in SQLite (Daemon state stores in SQLite (state.db) #624).

Depends on

• A waiting request says why each worker cannot take it, on a gateway #637

Written by an agent.