Transcript Lake

A single, privacy-masked, real-time archive of the coding-agent conversations on this machine, queryable with plain SQL through DuckDB.

• Code lives in this repository (transcript-lake).
• Data lives outside the repository, in the directory named by the LAKE_DATA environment variable or, when that is unset, ~/.transcript-lake. Partitions, cursors, projections, and stream state all live there; nothing is written into the repository.
• The DuckDB CLI (version one dot five) must be on PATH as duckdb.
• The CLI is one Rust binary. It carries its own view definitions and needs no language runtime, package manager, or interpreter on the host.

Canonical event#

Every source line from every runtime is normalised into one JSON object per line (NDJSON) with this frozen shape:

The extra object stays small by design: full tool outputs are never embedded, and any single text field is capped at sixty-four kilobytes.

The driver also stores extra.source_stem_hash, a one-way digest of the source filename stem. The plaintext stem never enters lake storage. This bridges indexes that name a conversation by file to the runtime-native session_id without persisting the external identifier.

OMP message events retain recorded attribution, steering and synthetic fields in extra. transcript-lake show <session-id> --include user --json includes each event's extra object, so consumers can distinguish agent-origin messages and runtime injections from explicitly user-attributed input. These fields describe the source record; Lake does not infer sender identity from message wording. Missing source fields remain absent. Previously ingested events without these fields are not retroactively enriched, and rebuilding the Oko projection from those events cannot recover information they never retained.

Adapters#

Each runtime has one adapter in src/adapters/ implementing the Adapter trait: runtime() returns the stable identifier, roots(home) returns the directories its sessions are in, watch_roots(home) the stable parents the stream watches, list_sessions(root) supports historical replay, entry_for(path) resolves a filesystem notification without scanning siblings, and read(ctx) says how a session is read: Reading::Lines, a stateful Parser fed the appended lines from a verified byte cursor, or Reading::Whole, a loader the stream calls whenever the source (and its SQLite write-ahead log) changed, whose events are recognised by occurrence so nothing archived before is appended twice. Parsers swallow malformed lines without failing the process, never perform IO inside on_line, and emit UNMASKED text: masking is the stream driver's job, so adapters stay pure. Databases are opened read-only.

The registry is src/adapters/mod.rs; each adapter's module documentation names its store path, every record shape it reads and the upstream source or observed-format registry the shapes were read from. transcript-lake sources --json lists every runtime with its roots on the machine it runs on. The runtimes are: claude, codex, omp, droid, kimi, jeden, pi, senpi, gjc, prime, kimchi, openclaw (Pi's session files), cherrystudio (Claude Code's), trae (Codex rollouts), gemini, qwen, copilot (Copilot CLI), copilot-chat (VS Code chat), cline, roo, kilocode, continue, cursor (the editor), cursor-agent (the CLI), opencode, kilo, zcode (OpenCode's database), crush, goose, zed, amazon-q, kiro-cli (Amazon Q's conversation rows), kiro, antigravity, grok, codebuddy, commandcode, openhands, gptme, vibe, muse, junie, amp, codewhale, hermes, devin and aider.

• hooks — Tama publishes immutable closed segments in its ready directory; each segment is streamed once, content-checked, committed, and acknowledged. When that directory is absent, the adapter follows legacy ~/.hooks-adaptive/telemetry.jsonl and telemetry.prev.jsonl. Each record becomes a hook_decision event: tool_name is the hook identity, text is the masked reason (null when the record gives none), and extra carries decision, event, and infrastructure; a legacy telemetry record keeps every non-null field it holds in extra. A closed-segment record whose ts is not a number of milliseconds keeps its event without a time and says why in extra.ts_error; a field the hook left empty or null is null.

Stream, cursors, partitions#

src/commands/stream/ owns the long-running process. It watches the selected source, or discovered adapter roots and Tama's ready directory before selection. Startup enumerates the current candidates once. Notifications then carry changed paths to their adapters. The reader verifies the consumed source prefix before resuming; unchanged metadata includes file identity and change timestamps, not only size and mtime.

One writer lease covers canonical partition appends and fsync, affected Oko projections, and cursor publication. A changed or shorter prefix and a legacy cursor trigger masked recovery against retained canonical occurrence counts. Recovery keeps its previous cursor until the whole source succeeds; an interrupted attempt can repeat without duplicating its already retained suffix.

• Cursor store: LAKE_DATA/cursors.json maps source paths to mtimeMs, size, offset, and source. The source checkpoint contains a consumed-prefix SHA-256 and opened-file identity. Writes are atomic and fsynced. Legacy records migrate lazily; corrupt cursor state is a refusal.
• Source replacement: recover missing occurrences, restore missing Oko rows, and preserve all masked history. Rewriting a vendor journal never authorizes deletion from the Lake. Corrupt existing canonical evidence is refused with the partition path, line, and cause.
• Partition path: LAKE_DATA/events/runtime=<runtime>/date=<year>-<month>-<day>/part-<hash>.ndjson, append mode, where <hash> is the first twelve hex characters of a SHA digest of the source file path. One source always lands in the same partition file per day.
• Concurrency: one writer lease owns a source delta. Compaction, recovery, or another writer fails before mutation rather than racing the incumbent.
• Separate reconstruction: rebuild --to <empty-path> [--source <runtime>] replays selected source history into a separate empty Lake. Ordinary replacement recovery needs neither this command nor a second root.

Masking guarantees#

src/record/redact.rs exposes Masker; the stream uses one masker instance while it canonicalizes every event in a source delta.

• Each hit is replaced ENTIRELY by a marker of the form [masked:<class>:<length>:<fingerprint>]. No plaintext prefix or suffix of the original value survives.
• <class> is one of token (provider-credential-shaped strings: a short lowercase prefix of two to seven letters, a dash, then twenty or more characters from the letter, digit, underscore, dash alphabet), entropy (dense runs of forty or more base-sixty-four-like characters), or assignment (an uppercase NAME, an equals sign, then a long value).
• <length> is the original length, so downstream analysis can still reason about size without seeing content.
• <fingerprint> is the first eight hex characters of a SHA digest of the value: stable enough to correlate reuse of the same value across events, and non-reversible.
• The transform is pure, deterministic, and idempotent — masking a masked string changes nothing.
• Masking happens after parsing and before any byte reaches a partition or Oko projection file, so no unmasked text is written to LAKE_DATA.
• Per-class hit counts are emitted with each successful stream commit.

Querying with DuckDB#

transcript-lake query "<sql>" runs DuckDB with the selected data root, loads the canonical views, then runs operator SQL. sessions, events, search, stats, and hooks expose bounded common queries without requiring SQL input. search runs a case-insensitive substring match over the text column of the canonical events view and escapes LIKE wildcards in the operator's term, so the term always matches literally.

The view definitions live in sql/views.sql and sql/signals.sql in this repository and are compiled into the binary at build time, so a moved or partially copied installation can never present a Lake without its views. TRANSCRIPT_LAKE_SQL names a directory of replacement scripts for iterating on view definitions against an installed binary; a directory that is set but missing the requested script is an error rather than a silent fall back to the compiled copy.

Views defined by views.sql:

• events — every canonical column plus filename (the partition file the row came from). The reader pins the frozen column list explicitly, so schema inference can never drift, and skips a torn final line while an stream appends.
• sessions — one row per runtime-native conversation identity: runtime, project, first and last timestamps, message and tool counts, summed usage counters, and oko_session_hash, the aggregate one-way source-stem alias.
• interrupted_sessions — conversations that stopped without an answer: the last recorded turn is a user message the agent never replied to (stopped_as = 'unanswered') or a tool call whose run was cut off before the agent spoke again (stopped_as = 'cut_off_mid_tool'). Carries the session view's identity, span, and counts plus last_user_text, the masked opening of that final request. transcript-lake sessions --interrupted reads it, newest first.
• tools_daily — tool-call volume per day, runtime, and tool.
• tokens_daily — usage counters summed per day, runtime, and model.
• hook_decisions — the adaptive-hook decision stream (runtime hooks) with decision, hook event, infra flag, and masked reason.
• blocks_by_hook — blocking pressure per hook: block count, distinct conversations, first and last block, and the most recent reason.
• labels — operator-owned session annotations from LAKE_DATA/labels: one row per assignment (ts, session_id, runtime, aspect, value, note, source). The store is append-only and this view exposes the full history; CLI reads apply latest-assignment-wins per session and aspect. The same empty-store stub and torn-final-line tolerance as the events view apply.

Empty-lake bootstrap: DuckDB refuses to bind a view over a glob that matches no files, so views.sql materialises a zero-row stub file under /tmp and points the reader at it only while the lake has no partitions at all. As soon as one partition exists, the views read the live glob directly, and partition files created later are visible to every subsequent query without reloading.

signals.sql installs and loads DuckDB's SQLite extension, attaches the Oko transcript index read-only, and creates named cross-source views: oko_frustration, hook_frustration_overlap, hook_frustration_daily, and oko_lake_freshness. transcript-lake signals --report <name> selects those views through the installed CLI, so examples never depend on source-tree SQL. The join tries native session identity, then compares the source-stem digest with a digest of Oko's session key; plaintext aliases are never persisted. Statements touching the oko schema are tagged REQUIRES-OKO and fail when the index is absent without affecting Lake-only commands.

compact converts each runtime partition directory to Parquet under LAKE_DATA/parquet/runtime=<r>/ and reports both sizes. The NDJSON originals are never deleted; Parquet is an additive, faster-scan mirror.

Oko import path#

Transcript Lake is the only component that parses local vendor transcript stores for Oko's historical catalog. The stream's writer (src/stream/writer.rs) hands each newly canonicalized event to src/oko_export/, which projects it into the affected conversation file in the same source transaction under:

LAKE_DATA/exports/oko/runtime=<runtime>/<session-hash>.jsonl

Oko recursively imports that directory, decodes the canonical rows, and maintains its own single-writer SQLite FTS index as a disposable interactive read model. DuckDB continues to own cross-runtime analytics. Oko may still inspect native process/session locations to launch or resume a live agent, but it does not parse them into the historical index.

The exporter covers every conversation runtime present in the lake and keeps provider identity in the directory name. User, assistant, thinking, tool call, tool result, and meta events retain the frozen Lake fields plus a deterministic event UUID.

• Live commits merge only the newly canonicalized rows into affected session files; the stream does not reread Lake partitions to produce them.
• projection rebuild --target oko is the recovery path: it scans authoritative partitions, rebuilds every session in bounded memory, and prunes files absent from the completed Lake scan.
• Replayed Lake events are deduplicated by UUID before publication.
• Session files and projection metadata are written atomically. Unchanged files keep their mtimes, so Oko's indexer skips them.
• --reindex optionally invokes oko-cli transcripts reindex; a running Oko also discovers projection changes through its own filesystem observation.
• freshness() compares the Oko index read-only with Lake cursor recency.

Operator labels#

transcript-lake label records operator-owned annotations over sessions: label add appends one JSON record per assignment to LAKE_DATA/labels/labels.ndjson after validating the session against the canonical sessions view, label list shows the latest assignment per session and aspect (newest first), and label aspects aggregates the effective labels. A record carries ts, session_id, runtime (denormalized from the session row), aspect (lowercase-normalized), value, nullable note, and namespaced provenance source matching ^(manual|human|model|brama)(:[A-Za-z0-9._/-]+)?$ (manual when --source is absent). manual and human mean a person assigned the label; brama:<model-id> means an LLM assigned it through the Brama gateway; model:<artifact> means a local fine-tuned classifier merely suggested it. Training ground truth is manual/human/brama:*; model:* suggestions await acceptance and are excluded from training runs by convention — that filter lives in transcript-label-trainer, not in this store.

• Labels are derived operator data, not masked Lake events: label text is stored exactly as given and never passes through the masker, so labels must not carry secrets.
• The store is append-only. Re-labeling the same session and aspect adds another record; the latest assignment wins in label list and label aspects, while the labels DuckDB view exposes the full history for joins against sessions and events.
• Writes are single complete lines appended in one call and fsynced; a crash loses at most the record being written, and readers skip a torn final line, mirroring the events partitions.
• The events writer lease is deliberately not taken: labels live outside events/ and cursors.json, so labeling neither blocks nor is blocked by the stream.
• Deleting LAKE_DATA/labels/ loses only labels; events, cursors, and exports are unaffected.

Out of scope, deliberately#

• gemini and qwen — no session stores for these runtimes exist on this machine, so no adapters ship. Adding one later means dropping a module into src/adapters/ implementing the frozen Adapter trait; nothing else changes.
• Semantic embeddings — vector search over the lake waits on a local embedding backend; until then the lake offers lexical matching through search and SQL, and the Oko index covers full-text search needs.
• Implicit corpus backfill — the live stream never scans full history. Historical reconstruction is an explicit separate-root rebuild.

Operating instructions#

LAKE="$HOME/.transcript-lake"

transcript-lake --data-dir "$LAKE" paths
transcript-lake --data-dir "$LAKE" sources
transcript-lake --data-dir "$LAKE" doctor
transcript-lake --data-dir "$LAKE" stream
transcript-lake --data-dir "$LAKE" sessions --limit 20
transcript-lake --data-dir "$LAKE" events --type tool_call --limit 20
transcript-lake --data-dir "$LAKE" search "ssh" --limit 20
transcript-lake --data-dir "$LAKE" label add <session-id> --aspect reviewed --value yes
transcript-lake --data-dir "$LAKE" label list --aspect reviewed
transcript-lake --data-dir "$LAKE" stats --days 7
transcript-lake --data-dir "$LAKE" hooks --decision block
transcript-lake --data-dir "$LAKE" signals --report freshness
transcript-lake --data-dir "$LAKE" query "FROM tokens_daily ORDER BY day DESC"
transcript-lake --data-dir "$LAKE" compact --source droid
transcript-lake --data-dir "$LAKE" projection rebuild --target oko --reindex
transcript-lake --data-dir "$LAKE" clean --target all

For recovery, preserve the current root and use transcript-lake --data-dir "$LAKE" rebuild --to <empty-path>. Applied clean removes only rebuildable Parquet/Oko data and shares the state writer lease with streaming, export recovery, and compaction.

The stream is restart-safe: cursors make every source tail incremental, masking is idempotent, and partitions remain append-only per source file and day.