Skip to main content
This audit covers SQLite and PGlite/PostgreSQL storage, inspecting the live SQLite store read-only: no production rows were deleted, compacted, or vacuumed.

​
What stays durable

No current durable event should become Prometheus-only: Prometheus is a lossy, low-cardinality aggregate that can’t reconstruct an individual run or event sequence. _smithers_events is ordered run history for SSE reconnect, gateway history, transcript search, replay, and recovery; TokenUsageReported, for example, seeds a resumed run’s budget. TaskHeartbeat is the closest tempting candidate, but at just 80.7 MB across 228,484 rows (0.114% of the 65.99 GiB database) it stays durable because the gateway exposes heartbeat history and reconnect ordering; aggregate count, interval, timeout, and payload-size metrics already live in Prometheus. Runs, nodes, attempts, frames, outputs, snapshots, approvals, human requests, signals, alerts, cron state, VCS pointers, scorer results, audit rows, and memory remain semantic workflow state; existing bounded caches and workspace checkpoint pruning keep their current eviction contracts.

​
Measured storage hot spots

SQLite dbstat reported: The dominant sampled snapshot held about 6.45 MB of state, almost all in outputs_json; 40 consecutive rows shared the exact same content hash, making snapshot representation the first storage target by a wide margin.

​
Snapshot representation

Migration 0025_snapshot_contents leaves _smithers_snapshots physically unchanged and only adds:
  • _smithers_snapshot_contents, keyed by the snapshot content hash, holding the four raw JSON fields once;
  • _smithers_snapshot_payload_refs, keyed by (run_id, frame_no), foreign-keyed to both the snapshot metadata row and its immutable content;
  • a content-hash index on that reference table, plus lifecycle triggers maintaining derived reference counts.
New snapshot metadata rows keep the existing columns but store empty inline JSON, with the reference row as the compact marker. Exact/latest loads join metadata, reference, and content in one statement, so concurrent rewind can’t delete content between two reads; legacy inline rows still load directly. Capture, frame commit, fork, replacement, rewind, and direct snapshot deletion keep their existing atomic boundaries. SQLite-compatible runtimes install a delete fallback trigger where PRAGMA foreign_keys is rejected; PostgreSQL uses the foreign-key cascade instead. Store migration commits table-at-a-time, not with whole-copy atomicity, ordering content before snapshots and references, copying derived counts as zero, and letting destination triggers rebuild them. The exported low-level smithersSnapshots object still maps the physical table; its schema and inline-write path stay compatible, and an inline update over a compact row atomically retires its old content reference. New compact rows aren’t self-contained through that raw table: their four empty JSON strings are markers, so raw-table readers must move to loadSnapshot/loadLatestSnapshot, which return the same hydrated public Snapshot shape as before. External SQLite descriptors must provide an atomic transaction() callback; those that can’t hold that boundary, including Cloudflare D1, fail before a transactional write begins rather than risk a partial frame or snapshot. The unreleased gzip prototype behind existing dogfood data stays under its old table name, readable with hash verification; the final migration doesn’t rename, copy, index, or add a column to the roughly 41 GB snapshot table.

​
Measurement

The deterministic SQLite fixture uses a structured 6,608,625-byte state with 1,536 agent-result records: twelve inline copies occupied 79,761,408 bytes versus 13,602,816 bytes for the content-addressed database (about 17.1% of the inline baseline), returning the identical public snapshot. It also covers replacement, shared references, direct deletion, legacy inline rows, and the compressed prototype; the engine’s real PGlite suite covers capture, resume, fork, and hydration through the PostgreSQL trigger path. Run the focused fixtures with:

​
What this does not reclaim

The change limits future growth without rewriting existing inline snapshots. SQLite has auto_vacuum=0, so deleting or clearing old payloads wouldn’t return file space to the OS anyway; reclaiming existing space needs a separate offline workflow (verified backup, free-space check, batched materialization, integrity verification, VACUUM INTO, atomic replacement) that must never run automatically against the live 66 GB store. Old binaries don’t understand compact rows, so rollback after new writes means restoring a pre-upgrade backup: the migration doesn’t claim an unavailable reverse materializer.

​
Follow-up opportunities

The next candidates are representation changes rather than metrics deletion:
  1. Versioned asynchronous compression/normalization for AgentSession, AgentEvent, and AgentTrace payloads (about 1.664 GB together in the audited store).
  2. Content-address repeated mounted_task_ids_json and task_index_json frame metadata, preserving the existing bounded keyframe/delta codec.
  3. Deduplicate the same large error JSON currently present in both attempts and matching NodeFailed events.
  4. Offer explicit terminal-run archival with complete run-owned-table coverage.
Each needs its own compatibility, concurrency, store-copy, and recovery design; none is folded into the snapshot migration.