Building an agent-transcript indexer

Your coding agents already write down everything they do, in session files nobody reads. An indexer loads those files into a database you can query. Mine puts Claude Code, Codex and pi into one PostgreSQL schema on ParadeDB. It has keyword search, optional vector search, a read-only MCP server so an agent can query its own history, a classifier that records what triggered each model call, and a live view of every loop. It is called agent-history, it is public at github.com/rknightion/agent-history under the MIT licence, and the current release is v0.6.1.

My homelab server runs it as three services from one image pinned to a commit of that repository: the indexer, the embedder and journal-sync. This is written from the code, its contract and tests, and queries against that catalogue. The repository has a Compose quick start; the last section is for building your own.

Terms

A few terms first, because the rest leans on them.

• A transcript is the JSONL file an agent writes for a session: one JSON record per line, for every message, tool call and result.
• The catalogue is the database those records are loaded into, in a schema called ah.
• ParadeDB is PostgreSQL with extensions for keyword ranking (pg_search) and vectors (pgvector). The compose file pins one image, tag 18-v0.25.10.
• BM25 is the ranking most keyword search engines use. It scores a message by how often the query’s words appear in it, weighted by how rare each word is across everything indexed.
• An embedding is a list of numbers, 1,024 of them here, that a model computes for a piece of text so that texts with similar meaning land near each other. Vector search returns the nearest ones.
• MCP is the Model Context Protocol, the way an agent calls tools offered by a local server. Here the tools are read-only database queries.
• An offset is a record’s byte position in its file.
• A root is the agent running a loop and a lane is a helper it starts, as the Codex article defines them.

Why index at all

A loop root can’t count its own wakeups, and its closeout doesn’t either; the Codex article shows what counting them over transcripts found. The transcript is the only complete record of what an agent did. The archive article covers how every transcript reaches that server and is kept. But a pile of JSONL files in three formats isn’t something you can ask a question of.

Queried on 5 October, the catalogue held 8,956 Claude Code sessions going back to 5 June, 8,475 Codex sessions back to 29 June and 2,852 pi sessions since 27 September, with 1.15 million messages and 1.38 million tool calls between them. It answers these:

• What did I decide about this? search finds prompts, replies, sub-agent briefs and reports, and compaction summaries by phrase. search_summaries searches the session summaries journal-sync pulls in. find_sessions names the root session to resume when a hit is a sub-agent.
• Why did this change? touched takes a path pattern and returns the sessions that touched the file. why takes a commit sha and returns the session and event that made it, the last human prompt before it and, when a sub-agent committed, its brief.
• How did a loop go? loops lists loop runs with their status, lanes, model calls, tokens, commits and list-price cost.
• What failed? A failed tool call carries an error class (denied, timeout, interrupted, validation_error, not_found, permission, network, nonzero_exit, tool_error, fake_cell_wait or other) and an excerpt.
• Where did the model calls go? efficiency, the classifier below.

There are also active_sessions, for root sessions with events in the last few minutes, infra_actions, for the ssh, scp and rsync commands agents ran against one host in a time window, task for Backlog tasks, and schema and sql for anything else.

Three formats, one schema

Claude Code, Codex and pi all write JSONL and none of them write it the same way. The parsers are pure: each turns one decoded record into typed rows and never touches the database. The loader owns every write. Two rules hold the design together. Rows refer to sessions by natural key, never by a surrogate id. And every key is a content identity, so seeing the same record again from another file, such as a fork replaying its parent’s history or an archived copy, produces the same key and the upsert changes nothing.

Each format needs its own care.

• Claude Code: keys come from the line uuid, the tool_use id and the API message.id. One API response is written as several lines that each repeat the id, so the model-call row keeps the largest token counts seen.
• Codex: a forked thread copies its parent’s history into its own file, and counting that twice would double the parent’s activity. The parser skips the replay by ordinal where the fork records one, and otherwise treats the burst of records at fork time as replay. Its docstring records that the burst rule matched the parent files’ ground truth on 858 of 858 legacy forks.
• pi: there are no turn records, so turns are inferred. The agent is busy after an assistant message that stopped for a tool call and idle after any other stop, and a user message while idle opens a turn. A sub-agent’s parent comes from its file path, and its display name is never taken as evidence of its type. pi’s cache writes are stored, so a pi loop’s cost is known rather than left blank.

The schema has sessions, turns, messages, model calls with token counts, tool calls with their full input and output, sub-agent spawns linked to the child sessions they started, compactions, file touches, git events, and loops with their lanes. Messages carry a class (human_prompt, queued_prompt, assistant_text, subagent_brief, subagent_report, reasoning, system_prompt, hook_output and others), so a search can be limited to what a person typed.

The catalogue stores everything unredacted, including tool output. The parsers never redact, omit or scrub, and privacy is the operator’s decision: which homes to index and who can reach the database. Anything an agent printed, secrets included, is in the catalogue, so treat it like the transcript directories it came from.

Loading incrementally

The loader keeps a row per source file with the offset it has indexed, a line count, a hash of the 64 KiB before that offset, a hash of the first 64 KiB, and a small JSON state the parser carries between runs.

• It reads only lines that end in a newline. A record still being written waits for the next run.
• One transaction per batch writes the rows, marks the affected sessions dirty and advances the offset, so a crash can’t double-count or lose a batch.
• Before appending, it checks the file still begins with what it indexed, using both hashes. A rewritten or truncated file has everything it contributed deleted and is parsed again from zero, with a source_rewritten issue recorded.
• One bad file is rolled back, recorded and skipped. It doesn’t stop the run.
• A changed parser version refuses to reparse in place, because some rows keep their first value and can’t be corrected by a second pass. It records a parser_upgraded issue and waits for a rebuild.

The indexer container repeats index --every, every 60 seconds by default, under an advisory lock so two runs can’t overlap. Mine runs about every five minutes, and a pass usually takes around 15 seconds. After the files are read, set-based post-passes over only the sessions touched since the last pass link children to parents and roots, spawns to the sessions they started and paths to repositories, and refresh the loop table below. The embedder works through pending chunks in its own container, and journal-sync copies session summaries in from a separate journal database, read-only.

Everything except the collector tables, the embedding cache and the change logs is derived, and the transcripts stay the authority. A rebuild empties the derived tables and reads every source again, and an agent home only holds so much: Claude Code deletes sessions older than its cleanupPeriodDays setting. A [cold_sources] table maps each namespace to its copy in the archive’s cold tier, mounted read-only on the server, and a rebuild unions the two, preferring the hot copy. It refuses before truncating anything if a transcript already in the catalogue is missing from both, and agent-history rebuild-check reports the per-namespace counts first.

Loops: running, waiting, paused, finished, stale

A loop is found from its root’s launch message: “You are the root” and exactly one codex/closeout-<date>-loop<N>.md path, the shape the loop contract fixes. The launch also names the charter beside it and the charter’s SHA-256. ah.loops holds one row per launch, with the closeout path, charter path and charter digest in the older report_path, goal_path and goal_sha256 columns, and a status:

• finished: a qualified closeout ends the run, or the next launch in the same root replaced it.
• waiting: the root’s last line was WAITING: <what> until <UTC time> and that time hasn’t passed. waiting_until holds it.
• paused: the root’s last line was PAUSED: <reason>.
• running: none of those, and the root was active within 24 hours of the last refresh.
• stale: none of those, and no root activity for 24 hours. A loop that died without a closeout becomes stale, never finished, and new root activity makes it running again.

A closeout qualifies when line 1 is exactly # Closeout: <repo> loop<N> · Charter: <digest>, the repository, loop number and digest agree with the launch and the charter, and the file was written inside the launch’s window. The hourly collector below records each charter’s and closeout’s hash and the closeout’s first line in ah.loop_run_metadata, never the body. A complete, successful write of the exact closeout path captured in a transcript can stand in for the collected copy; a partial edit, a failed write or shell text proves nothing. Waiting and paused repeat what the root last said, they don’t prove the process is alive, and new root activity clears them.

Each row also carries progress: lanes, model calls, tokens, a list-price cost, errors, commits, pushes, tasks landed and parks, each NULL when unknown, never zero. When a closeout finishes a run, the landed and parked counts come from that closeout first: the collector counts the distinct task IDs that open an entry under its ## Landed and ## Parked headings and stores only those two integers. Where those are missing the counts fall back, column by column, to the run’s state log, the Task: trailers on commits that reached the default branch inside the run’s window, and Backlog Done flips. The repository’s count precedence has the rules.

agent-history loop <id or closeout path> --usage prints a short Markdown block for a root to paste into its closeout’s Notes: an as-of timestamp, wall time, model calls and list-price cost by role and model. A relaunch shares its closeout with the original launch, so the two show together, and the output says so. The catalogue lags the transcripts by an indexer pass, hence the timestamp.

Lanes attach through session lineage, or for Codex exec sessions without it, by a Codex command inside the loop window and the same working tree. A relaunched root joins its loop only through its exact launch-file path or a loop-state append naming that loop’s state log. Every lane-return block a lane produces is kept in ah.lane.returns, oldest first, and the latest one’s status (complete, partial, blocked or failed) is stored with the lane.

Git activity comes from transcripts and from the repositories. The parsers pick commits and pushes out of tool output, from the [branch sha] line a commit prints and the old..new branch -> branch line a push prints, or from the exit status when a commit printed neither. Claude’s structured gitOperation result and the regex fallback produce the same event key, so the two sightings merge. An hourly collector on each Mac, running as a dedicated ingest role, reads the configured checkouts for commits, Backlog tasks and Done flips, CI runs, installed skills and plugins, permission logs and each loop’s charter and closeout metadata. It compares author emails to your configured identities and never stores one.

Search

BM25 runs on ParadeDB indexes over message text and over tool input and output. Message and tool text use the source_code tokenizer. ParadeDB’s tokenizer documentation describes splitting code identifiers on camelCase and snake_case boundaries. That supports searching identifier parts; it is not a promise that a compound identifier remains one exact token. Namespace, agent, message class and time are filter columns pushed into the scan. The indexes are built after the first bulk load, following ParadeDB’s own guidance.

Vector search is off by default. Enabled, the embedder sends chunks of human and queued prompts, assistant text, sub-agent briefs and reports, compaction summaries, session summaries and failed-tool excerpts to an OpenAI-compatible embeddings endpoint, after replacing secret-shaped spans with [REDACTED]. Other sensitive text isn’t scrubbed. Vectors are cached by model and a hash of the exact input, so a rebuild finds them again without calling the endpoint.

Hybrid search fuses message BM25, tool I/O BM25 and the message vectors by reciprocal rank: each list contributes one over sixty plus the rank, and the vector list is weighted. The SQL default weight is 0.7. The MCP server drops it to 0.35 for queries of three words or fewer and for any query containing an identifier-shaped word, such as a path or a sha, where meaning-matching helps least. If the embeddings endpoint doesn’t answer within three seconds the search falls back to BM25 alone and says so in the response header. A hybrid query is itself sent to the endpoint, raw and unredacted, up to 12,000 characters; mode="bm25" sends nothing.

A context is a named list of namespaces, and a namespace is <agent>-<profile>, such as claude-local. Search tools filter by the selected context. A context is a search scope and nothing more. The sql tool isn’t filtered, and any role that can read the tables can read every row. For real separation use separate databases. Reader roles on shared tables don’t isolate rows without row-level security, which the package doesn’t implement. PostgreSQL’s row-security documentation states that table privileges make all rows equally available by default; row-security policies provide a separate restriction. That is why a search filter alone cannot supply isolation.

The MCP server

The server is read-only and stdio-only. It runs no shell and it never writes. Each call opens a fresh connection as the reader role and checks the role before running anything. It refuses a superuser, the owner of the schema or any table or a member of that owner, a role that can write to or create in the schema, a member of pg_execute_server_program, pg_write_server_files or pg_read_server_files, and a role without default_transaction_read_only on. A misconfigured DSN therefore fails closed instead of handing an agent a writable connection. The stock container image leaves the mcp extra out; install it with the CLI where the agent runs.

The sql tool runs exactly one statement per call in a read-only transaction with a 30-second timeout. It sends the statement over the extended protocol. PostgreSQL’s Parse-message contract permits only one SQL statement in that message, which is the limit this client uses. The server reads at most 201 rows, returns 200 with a truncation note, and cuts any cell at 2,000 characters. Tests cover the writer, superuser and file-role refusals, the second statement, writes and the row cap. PostgreSQL describes read-only transactions as a high-level restriction that does not prevent every disk write, so the role checks and query limits are separate controls, not substitutes for each other.

Registering the server gives the connected agent read access to every row, including stored tool output and any secrets in it, and the model provider may receive whatever the agent puts into its context. That is the trade for an agent that can query its own history, so decide which database it connects to.

The efficiency classifier

The classifier attributes each root model call to the event that last changed the agent’s state before it:

• user: a human prompt, a queued prompt, or a message from a sub-agent’s parent.
• model: the previous model call, with nothing in between.
• work: the result of an ordinary tool call.
• status: the result of a status look, such as git status, gh run view or backlog task list.
• wait: the result of a wait that timed out or only slept.
• event: a wait that delivered something, or an event the harness injected.
• noop: the result of an empty or trivial command.
• orchestrate: the result of spawning or messaging an agent, or arming a wake.
• agent_msg: a sub-agent’s completion notice.

A root whose calls are mostly wait and status is polling instead of waiting on events. In SQL, poll_share is those two over the classified calls; the dashboard’s poll-reaction panel also counts noop.

The rules exist twice in the package, in Python for the metrics collector that reads transcripts and in SQL for the catalogue. Two implementations of one rule drift, so a contract test runs the same tool calls through both and expects the same answer.

The hard part was ordering. Attributing a call to the event before it needs a reliable order of events, and timestamps don’t give one. Codex and pi results can tie at millisecond resolution, and Claude’s notification records can carry a timestamp that moves backwards. Sorted by time, a call can pick the wrong predecessor. The classifier orders by physical position in the source file instead. That needs two columns: the byte offset of each tool result’s own record, since a result’s offset differs from its call’s, and Claude’s raw record type. One test sets a result’s timestamp to the year 2000 and still expects the status trigger, because position decides.

Rows loaded before those columns existed get them only from a rebuild. If you build a classifier, store every record’s offset from the first day.

Metrics, dashboards and alerts

Metrics leave only over OTLP, as the service agent-history-index. The indexer runs five collectors, archive, catalogue, runs, efficiency and self, on their own thread every 15 seconds by default, so a slow or failing index pass never stops metrics and a failing collector never fails an index pass. Each self-health section reports its own success and last-success time, so one broken section shows as broken while the others carry on, and the agent-history-metrics-stale alert catches collection that has stopped altogether.

The efficiency collector is the one that reads transcripts. It parses them incrementally into agent_efficiency_* families: model calls by trigger, tokens, time per thread state, waits and polls, spawns by route, gate runs, CI waits, reviews, tool failures and human interventions, with a loop label naming the repository and loop. Each collection parses for at most 20 seconds, so a long first parse delays counts rather than freshness, and a record it can’t read is counted and skipped. The other collectors read file metadata, receipts and database aggregates, never content. Label policy is in the archive article.

Per-call telemetry is recorded where the transcript carries it: duration with a latency basis, time to first token, thinking time and recorded cost. An unknown value is NULL, never zero. A Claude or pi usage record whose counts are all zero and that has no stop reason is stored as NULL too, because a proxy that adapts between providers can send zeros it never measured. With a LiteLLM spend-log source configured, the indexer fills those unknown counts for named Claude namespaces by request id and never overwrites a known count. Harness text such as wake notifications doesn’t count as a human prompt. On SIGTERM the periodic workers finish their in-flight pass and then exit.

Two dashboards and 17 alert rules are generated by scripts in the repository and published by CI with gcx, never edited in Grafana: a 21-tab catalogue dashboard of SQL alongside metrics, and a six-tab archive dashboard. The rules ship with promtool fixtures, and just check fails when generated JSON drifts from its builder.

What’s still open

• No row-level security. A context is a search scope, not access control. Separation means separate databases.
• Status isn’t liveness. A dead root can read running for up to 24 hours plus the next refresh, a silent live one can read stale, and waiting or paused only repeats what the root last said.
• Counts without a closeout are partial. A run that ends stale has no closeout to count from. The fallbacks count only what they can attribute exactly: a pi state log that records no landing leaves the count NULL rather than zero, a Task: trailer proves a landing but not that the whole charter was covered, and anything ambiguous stays NULL. Accepted lanes need a qualifying state log and are NULL without one.
• pi compaction events are recorded, but the post-compaction token count is NULL on purpose, because nothing measures it authoritatively.

Building your own

Start smaller than this, in roughly this order:

1. Parse one harness. Read real transcripts, not documentation, because the formats change between versions. Keep a count per record type and subtype, so a new type shows up as a number instead of missing data.
2. Store six things: a source-file table (path, indexed offset, parser version), sessions, messages with their text, tool calls with input and output, model calls with token counts, and the byte offset of every record. Derive keys from ids inside the records. A rebuild will renumber any surrogate id you invent.
3. Load incrementally. Save an offset per file, read only complete lines, and check a hash of the bytes before the offset so a rewritten file is noticed. Commit the rows and the new offset in one transaction. Keep a cold copy of every transcript and let a rebuild read it.
4. Index the text. PostgreSQL full-text search is enough to begin. Move to BM25 when ranking matters. Pick a tokenizer that keeps identifiers whole, and add embeddings only if keyword search visibly misses.
5. Give agents a read-only door. A reader role, one statement, a timeout, a row cap, and a check of the role’s privileges on every connection.
6. Add the classifier and loop tracking last. The classifier needs tool results with offsets, and a long run needs an explicit end signal, such as a terminal file with a fixed first line, rather than a guess from activity. Both only pay off if you run agents unattended.

For metrics, keep labels to a small fixed set and inspect text inside the process so only counts leave it. Never put a path, session id, prompt or file name in a label: each one is unbounded, and message text can hold credentials.

Test with synthetic transcripts. A real one, even trimmed, carries whatever the agent read that day.