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 left _smithers_snapshots physically unchanged and added:
  • _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. smithers gc now migrates those pre-0025 inline rows in bounded transactions. Each transaction inserts and verifies content, compacts the snapshot metadata, and adds its reference atomically. Progress is the data shape itself: an interrupted invocation resumes from the remaining non-empty inline rows. The command’s dry run reports row and byte totals without writing. 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:

​
Retention and physical file size

smithers gc --db-retention-days DAYS opts into incremental deletion of old terminal runs. There is no default database retention window and the existing filesystem --older-than setting does not enable it. The dry run inventories eligible runs and per-table rows. Active, paused, waiting, unfinished continued runs, and ancestors needed by retained descendants are never eligible. SQLite has auto_vacuum=0, so compaction and retention return pages to its freelist for reuse without returning file bytes to the OS. Smithers deliberately does not expose an online vacuum path. Physical shrinking remains an explicit offline operator workflow after every process holding the store is stopped and a backup is verified. PostgreSQL remains under operator-managed vacuum policy. 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. Add archive/export-before-retention for operators who want a cold history tier instead of deletion.
Each needs its own compatibility, concurrency, store-copy, and recovery design; none is folded into the snapshot migration.