[Rust port] relayburn-ledger: SQLite-only, idiomatic redesign (supersedes #243)

[willwashburn]

Parent: #240
Supersedes: #243 (the literal-port path; PR #253 closed in favor of this)

Context

#243 specified a faithful port of @relayburn/ledger — JSONL + hash sidecars + SQLite archive — so a Rust holder and a TS holder could interoperate against the same on-disk state. With the 2.0 cutover making Rust the source of truth, that constraint goes away, and several pieces of the TS design are workarounds for "I needed fast dedup but didn't have a database handy" rather than first-principles choices.

This issue replaces #243 with a Rust-idiomatic redesign.

Storage model

Two SQLite databases under RELAYBURN_HOME. No JSONL files, no hash sidecars, no file-lock module.

• ~/.relayburn/burn.sqlite — events, stamps, sessions, archive state. Hot DB; analytic queries run here.
• ~/.relayburn/content.sqlite — content blobs + FTS5 index. Big and cold; pruneable on retention without touching the events DB.

Both bundled (rusqlite features = ["bundled"]) so the burn binary stays a single static artifact.

Two classes of table inside burn.sqlite

The DB holds two kinds of data, and burn state rebuild treats them differently:

Class	Tables	Source	burn state rebuild behavior
Derivable	turns, compactions, relationships, tool_result_events, user_turns, sessions	Upstream session files (Claude Code / codex / opencode session stores)	Drop and re-ingest from upstream
First-party	stamps, archive_state	Generated by burn stamp / harness wrapper / ingest cursors	Preserved across rebuild

Backup story collapses to: cp burn.sqlite content.sqlite /your/backup/. One transaction model, one connection model, one fsync story end to end.

Why two SQLite DBs (not one, not per-session JSONL caches)

• Lifecycle asymmetry — events are kept ~forever and queried analytically; content is TTL-pruneable and read on demand. One DB means analytic queries pay VACUUM tax every time we prune content. Splitting isolates that.
• Page cache isolation — content reads don't evict event-index pages and vice versa.
• Independent WAL / checkpoint cadence — bulk content writes don't pause event writes.
• ATTACH DATABASE gives cross-DB joins when we genuinely need them (e.g. fetching a turn's tool-result body) without coupling the schemas.
• Mountable on cheaper/bigger storage via RELAYBURN_CONTENT_PATH.

The cost is two schemas / two migration paths and the absence of a cross-DB transaction (a crash between event commit and content commit can leave an event row pointing at no content). Acceptable: content is derivable from upstream files, so a burn state rebuild reconciles drift.

Why stamps live inside burn.sqlite, not in a separate file

Earlier drafts proposed stamps.jsonl for plaintext durability. Dropped because:

• WAL is more durable than O_APPEND, not less — atomic commits, no torn-write windows. JSONL gives "bytes there or not" but a partial line on hard crash is malformed and the parser has to skip it.
• Stamps aren't routinely human-inspected — they're metadata the harness writes automatically. The "cat | jq | git add" ergonomic argument was real but rarely-used; burn stamps export covers the niche case.
• One DB beats three files: one backup target, one transaction model, one fsync story. Reclassify can apply a stamp + write a turn atomically when needed.
• Eliminates the file-lock module entirely from the new design — SQLite WAL handles all serialization.

The "burn.sqlite is rebuildable" framing becomes "burn.sqlite holds everything; burn state rebuild only nukes the derivable tables." Stamps and ingest cursors are protected by code, not by living in a separate file.

Dedup: two layers, both shipped from day one

1. Primary — UNIQUE(source, session_id, message_id). "Already ingested this exact turn."
2. Secondary — content_fingerprint TEXT column with a non-unique index. Fingerprint = sha256(ts | model | input+output | cacheRead | cacheCreate5m+1h | firstToolArgsHash[..4])[..16]. Catches "same logical turn under a different messageId" — happens when upstream agents reformat session logs, when sessions fork/compact and re-emit turns under new IDs, when we observe the same turn under a different source label, or when a parser bugfix changes messageId derivation. Without this layer, every such event double-counts in burn summary.

The TS implementation gates layer 2 by a 10k-entry rolling-window sidecar file because indexing JSONL isn't an option there. With SQLite as cache we drop the rolling window — the column is a regular indexed column, ~16 bytes per turn, queryable.

Full-text search on content (FTS5, day one)

content.sqlite ships with an FTS5 virtual table over content.body from 2.0. Adding FTS5 later forces every existing user through a multi-minute one-time index rebuild over historical content, so we pay the cost up front:

• Storage: ~2–3× the content table size for the inverted index. Acceptable given content is the prune-on-retention table.
• Writes: tokenization on every insert. Negligible at session-ingest write rates.
• Bundled: no new dependency — FTS5 is part of the same rusqlite features = ["bundled"] build.

Enables burn search "out of memory" + future MCP / SDK queries like "find sessions where a tool returned 'permission denied'", and unlocks BM25-ranked + snippet output.

Append-only enforcement: not needed

Earlier drafts proposed SQLite triggers blocking UPDATE/DELETE on event tables. Dropped: the events tables are a cache, not source of truth. UPDATE / DELETE / reclassify in place is fine — the correctness invariant is "after burn state rebuild, the cache reproduces from upstream files." Stamps are protected by API surface (no update_stamp function exists) + code review, the same way the events tables get protected from accidental writes outside the writer.

What goes away vs the TS design

• ledger.jsonl — gone. Events live as cache rows in burn.sqlite (rebuildable from upstream); first-party stamps are a regular table in the same DB.
• ledger.idx / ledger.content.idx — id-hash and content-fingerprint sidecars. Replaced by indexes on the events tables.
• archive.sqlite as a separate "derived, rebuildable" file — folded into burn.sqlite.
• content/<session_id>.jsonl per-session files — replaced by content.sqlite.
• The withLock("ledger") / withLock("ledger-index") / withLock("archive") / withLock("content.<id>") file-lock dance — SQLite WAL handles all writer serialization. The file-lock module is removed from the new design entirely.

What stays

• RELAYBURN_HOME layout; RELAYBURN_SQLITE_PATH + a new RELAYBURN_CONTENT_PATH override.
• Bundled SQLite (with FTS5) — single static binary.

What's new

• burn export ledger --format jsonl — emits the event stream as JSONL on demand for jq-style debugging and audit-trail consumers.
• burn stamps export — emits the stamps table as JSONL for backup / version control.
• burn search <query> — FTS5 over content bodies, returns ranked hits with snippets.
• burn state rebuild — drops the derivable tables in burn.sqlite and the entire content.sqlite, then re-ingests from upstream session files. Stamps and ingest cursors are preserved.

Schema sketch

-- burn.sqlite
CREATE TABLE turns (
 source TEXT NOT NULL,
 session_id TEXT NOT NULL,
 message_id TEXT NOT NULL,
 ts TEXT NOT NULL,
 -- ... typed columns once relayburn-reader (#242) lands ...
 record_json TEXT NOT NULL, -- raw record blob until typed
 content_fingerprint TEXT NOT NULL, -- layer-2 dedup
 PRIMARY KEY (source, session_id, message_id)
) STRICT;

CREATE INDEX idx_turns_content_fingerprint ON turns(content_fingerprint);

CREATE TABLE stamps (
 -- first-party data; preserved across `burn state rebuild`
 source TEXT NOT NULL,
 session_id TEXT,
 ts TEXT NOT NULL,
 selector_json TEXT NOT NULL,
 enrichment_json TEXT NOT NULL,
 written_at TEXT NOT NULL, -- monotonic write order for the application layer
 PRIMARY KEY (source, session_id, ts, written_at)
);

CREATE TABLE compactions (...);
CREATE TABLE relationships (...);
CREATE TABLE tool_result_events (...);
CREATE TABLE user_turns (...);
CREATE TABLE sessions (...); -- materialized read model

CREATE TABLE archive_state (
 -- first-party data; preserved across `burn state rebuild`
 id INTEGER PRIMARY KEY CHECK (id = 1),
 schema_version INTEGER NOT NULL,
 upstream_cursors_json TEXT NOT NULL, -- per-source ingest cursors
 last_built_at TEXT,
 last_rebuild_at TEXT
);

-- content.sqlite
CREATE TABLE content (
 session_id TEXT NOT NULL,
 message_id TEXT NOT NULL,
 content_hash TEXT NOT NULL,
 body BLOB NOT NULL,
 byte_length INTEGER NOT NULL,
 created_at TEXT NOT NULL,
 PRIMARY KEY (session_id, message_id, content_hash)
) STRICT;

CREATE INDEX idx_content_session ON content(session_id);

-- FTS5 inverted index over content.body. `content=` + `content_rowid=`
-- bind it as an external-content table so we don't double-store the body;
-- contentless wouldn't let us return snippets.
CREATE VIRTUAL TABLE content_fts USING fts5(
 body,
 content='content',
 content_rowid='rowid',
 tokenize='porter unicode61'
);

-- Triggers keep the FTS index in sync with the content table.
CREATE TRIGGER content_fts_ai AFTER INSERT ON content BEGIN
 INSERT INTO content_fts(rowid, body) VALUES (new.rowid, new.body);
END;
CREATE TRIGGER content_fts_ad AFTER DELETE ON content BEGIN
 INSERT INTO content_fts(content_fts, rowid, body) VALUES('delete', old.rowid, old.body);
END;

Migration

No migration. 2.0 users start fresh. TS-built archive.sqlite / ledger.jsonl / content/*.jsonl from the 1.x line are not migrated; the RELAYBURN_HOME layout under 2.0 is incompatible by design. Document this clearly in the 2.0 release notes.

Open questions

1. Stamps pinning policy: what happens when a user wants to retain a session's events past upstream cleanup (Claude Code rotates session files, user rms a project)? Out of scope here, but flag for a follow-up: do we add a burn export ledger --pin <sessionId> that snapshots derived rows back into a plaintext archive, or rely on burn export ledger --format jsonl for full-DB backup?

Files this replaces (TS source-of-truth, until cutover)

• packages/ledger/src/adapters/file-adapter.ts — JSONL append loop
• packages/ledger/src/adapters/sqlite-adapter.ts — useful reference; the new design is closer to this than to file-adapter
• packages/ledger/src/index-sidecar.ts — dedup sidecars (gone, hashing logic moves into the writer)
• packages/ledger/src/archive.ts — derived archive (folded into burn.sqlite)
• packages/ledger/src/content.ts — per-session JSONL content sidecar (replaced by content.sqlite)
• packages/ledger/src/lock.ts + packages/ledger/src/adapters/file-lock.ts — removed entirely; nothing in the new design needs it
• packages/ledger/src/schema.ts — LedgerLine types (no longer needed; events live as SQL columns)
• packages/ledger/src/reclassify.ts — becomes a normal UPDATE on the cache, no special handling

Acceptance

• cargo test -p relayburn-ledger green.
• Steady-state RELAYBURN_HOME layout: burn.sqlite + content.sqlite. No ledger.jsonl / ledger.idx / ledger.content.idx / archive.sqlite / content/*.jsonl / stamps.jsonl / *.lock.
• No file-lock module in the crate; cargo build -p relayburn-ledger produces a binary that doesn't reference any user-space lockfile path.
• Layer-1 dedup (UNIQUE(source, session_id, message_id)) and layer-2 dedup (content_fingerprint index lookup) both covered by tests; second-ingest of a turn under a fresh messageId but identical shape produces no row.
• Stamps written via burn stamp survive burn state rebuild unchanged; ingest cursors in archive_state likewise survive.
• burn state rebuild regenerates the derivable tables in burn.sqlite and the entire content.sqlite (including the FTS index) from upstream session stores; integration test compares row-by-row against a freshly-ingested DB and asserts byte-equivalence (modulo last_built_at timestamps).
• FTS5 search returns ranked hits with snippets across content bodies; tested against a populated content.sqlite with multi-word queries, phrase queries, and boolean operators.
• FTS5 index stays consistent across content insert / delete via the sync triggers; rebuild test asserts MATCH results before and after burn state rebuild are identical.
• Concurrent writers serialize via SQLite WAL on each DB without any user-space lock; correctness test mirrors [Rust port] relayburn-ledger: JSONL + lock + sqlite archive #243's 100-concurrent-callers property.
• burn export ledger --format jsonl round-trips a populated DB to a JSONL stream byte-equivalent to the events ingested.
• burn stamps export round-trips the stamps table to JSONL for backup / version-control.
• Bundled SQLite (with FTS5) — cargo build --release -p relayburn-cli produces a binary with no libsqlite3.so runtime dep.
• Pruning content (TTL or explicit) does not lock the events DB; analytic queries on burn.sqlite keep running.
• 2.0 release notes call out the layout break vs 1.x and the no-migration policy.