> ## Documentation Index
> Fetch the complete documentation index at: https://smithers.sh/llms.txt
> Use this file to discover all available pages before exploring further.

# Database storage audit

> Durable-storage classification, measured hot spots, and the snapshot growth fix.

This audit covers SQLite and PGlite/PostgreSQL storage. The live SQLite store was
inspected read-only; no production rows were deleted, compacted, or vacuumed.

## What stays durable

No current durable event should become Prometheus-only. Prometheus is the lossy,
low-cardinality aggregate projection. `_smithers_events` is ordered run history
used by SSE reconnect, gateway history, transcript search, replay, and recovery.
`TokenUsageReported`, for example, seeds a resumed run's budget. Metrics cannot
reconstruct an individual run or event sequence.

`TaskHeartbeat` is the closest tempting candidate, but it occupies about 80.7 MB
across 228,484 rows: roughly 0.114% of the 65.99 GiB database. It remains durable
because the gateway exposes heartbeat history and reconnect ordering. Aggregate
heartbeat count, interval, timeout, and payload-size metrics already exist in the
Prometheus surface.

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 retain their current eviction contracts.

## Measured storage hot spots

SQLite `dbstat` reported:

| Table                 | Physical bytes |
| --------------------- | -------------: |
| `_smithers_snapshots` | 41,281,441,792 |
| `_smithers_events`    |  2,140,786,688 |
| `_smithers_frames`    |  1,489,481,728 |
| `_smithers_attempts`  |    197,328,896 |

The dominant sampled snapshot contained about 6.45 MB of state, almost all in
`outputs_json`, and 40 consecutive rows had the exact same content hash. Snapshot
representation is therefore the first storage target by a wide margin.

## Snapshot representation

Migration `0025_snapshot_contents` leaves `_smithers_snapshots` physically
unchanged. It only adds:

* `_smithers_snapshot_contents`, keyed by the existing snapshot content hash and
  containing the four raw JSON fields once;
* `_smithers_snapshot_payload_refs`, keyed by `(run_id, frame_no)` and foreign
  keyed to both the snapshot metadata row and its immutable content;
* a content-hash index on the small reference table and lifecycle triggers that
  maintain derived reference counts.

New snapshot metadata rows keep the existing columns but store empty inline JSON;
the reference row is the explicit compact marker. Exact/latest loads join metadata,
reference, and content in one statement, so concurrent rewind cannot delete the
content between two reads. Legacy inline rows continue to load directly.

Capture, frame commit, fork, replacement, rewind, and direct snapshot deletion
preserve their existing atomic boundaries. SQLite-compatible runtimes also
install a delete fallback trigger for environments that reject
`PRAGMA foreign_keys`; PostgreSQL uses the foreign-key cascade. Store migration
continues its existing table-at-a-time commits rather than claiming whole-copy
atomicity; it orders content before snapshots and references, copies derived
counts as zero, and lets the destination triggers rebuild them.

The exported low-level `smithersSnapshots` object still maps the physical table.
Its schema and inline-write path remain compatible: an inline update over a
compact row atomically retires its old content reference. New compact rows are
not self-contained through that raw table, however—their four empty JSON strings
are markers. Raw-table readers must move to `loadSnapshot`/`loadLatestSnapshot`,
which return the same hydrated public `Snapshot` shape as before.

External SQLite descriptors must provide a real atomic `transaction()` callback.
Descriptors that cannot hold that physical boundary, including Cloudflare D1,
fail before a transactional write begins instead of risking a partial frame or
snapshot.

The unreleased gzip prototype used by existing dogfood data is retained under its
old table name and remains readable with hash verification. The final migration
does not 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; the
content-addressed database occupied 13,602,816 bytes (about 17.1% of the inline
baseline) while returning the identical public snapshot. The test 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:

```sh theme={"theme":{"light":"github-light","dark":"github-dark"}}
pnpm -C packages/db exec bun test tests/migrations.test.js
pnpm -C packages/time-travel exec bun test tests/snapshot.test.js
pnpm -C packages/engine exec bun test tests/time-travel-postgres.test.js
```

## What this does not reclaim

The change limits future growth. It intentionally does not rewrite existing
inline snapshots. SQLite has `auto_vacuum=0`, so deleting or clearing old payloads
would not return file space to the OS anyway. Existing-space reclamation needs a
separate offline workflow with a verified backup, free-space check, batched
materialization, integrity verification, `VACUUM INTO`, and atomic replacement.
It must never run automatically against the live 66 GB store.

Old binaries do not understand compact rows. Rollback after new writes therefore
means restoring a pre-upgrade backup; the migration does not claim an unavailable
reverse materializer.

## Follow-up opportunities

The next candidates are representation changes, not 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 while 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.