ai-agents-metrics

History Pipeline

What this document is: How ai-agents-metrics extracts metrics from raw AI agent session data. The history pipeline reads existing conversation history files and produces session structure, token cost, and session timelines. This document describes the current adapters and the pipeline stages.

Session density definition: session count is the number of session files associated with a thread. Sessions per thread is session_count / thread_count. This is a structural metric: multiple sessions can represent continuation, delegation, or provider-specific storage behavior and do not prove that work was retried.

When to read this:

• Working on history ingestion or transcript analysis
• Adding a new data source adapter
• Debugging data reconstruction or gaps between recorded goals and agent sessions

Related docs:

• architecture.md — where the history pipeline fits in the overall system
• warehouse-layering.md — rules that govern what each raw_* / normalized_* / derived_* layer is allowed to contain

---

Scope and limitations

This pipeline supports three --source values:

• All (--source all, default): reads from both ~/.codex and ~/.claude; sources that don’t exist are skipped silently.
• Codex (--source codex): reads from ~/.codex only — full transcript, token usage, and thread metadata.
• Claude Code (--source claude): reads from ~/.claude/projects/ only — full transcript and token usage from session JSONL files, including subagent sessions.

Both sources feed the same ingest → normalize → derive pipeline and produce the same warehouse table shapes. The history pipeline is the product flow — run it to get session structure, token cost, and session timelines from existing history files with no prior setup.

Note on Codex vs Claude: Session and thread relationships are provider-specific. The pipeline reports their observed structure without interpreting additional sessions as retries.

The product model (threads, sessions, messages, and cost) is source-agnostic. New sources follow the same pattern with different raw tables and source readers.

---

Pipeline overview

Raw sources (~/.codex, Codex sessions)
  ↓  ingest       → raw warehouse tables (SQLite)
  ↓  normalize    → cleaned, stable rows
  ↓  classify     → session kinds (main/subagent) + practice events
  ↓  derive       → goal/attempt/timeline marts
  ↓  compare      → optional consistency analysis

Raw sources (~/.claude/projects, Claude Code sessions)
  ↓  ingest       → raw warehouse tables (SQLite)
  ↓  normalize    → cleaned, stable rows
  ↓  classify     → session kinds (main/subagent) + practice events
  ↓  derive       → goal/attempt/timeline marts
  ↓  compare      → optional consistency analysis

Stage	Module	What it does
Ingest	history/ingest/ (package: warehouse.py, codex.py, claude.py, __init__.py)	Reads raw Codex + Claude sources into the warehouse
Normalize	history/normalize.py	Cleans and stabilises raw rows
Classify	history/classify.py	Labels session kinds (main vs subagent) and extracts practice events
Derive	history/derive.py + derive_build.py + derive_insert.py + derive_schema.py	Builds goal, attempt, and timeline marts

Run in order: ingest → normalize → classify → derive.

---

Raw Sources

Codex (~/.codex)

Source	Content
state_5.sqlite	Thread-level metadata: model, cwd, title, archival state
sessions/**/rollout-*.jsonl	Full event stream per session: messages, token counts, task events
archived_sessions/*.jsonl	Same format as active sessions, post-archival
logs_1.sqlite	Runtime log side-channel: task boundaries, debug traces, model hints

Claude Code (~/.claude/projects/)

Source	Content
<encoded-cwd>/*.jsonl	Full session JSONL: messages, token usage, metadata
<encoded-cwd>/subagents/agent-*.jsonl	Subagent session files (same format, share sessionId with parent)

Thread identity is derived from sessionId in the session events. Thread metadata (model, cwd, version) is extracted from the session file itself — there is no separate state DB for Claude Code. Subagent files share the parent’s sessionId, so the parent’s raw_threads row is preserved when subagents are imported.

---

Relationship Map

flowchart LR
    subgraph Sources["~/.codex sources"]
        ST["state_5.sqlite"]
        SS["sessions/**/rollout-*.jsonl"]
        AS["archived_sessions/*.jsonl"]
        LG["logs_1.sqlite"]
    end

    subgraph Raw["Raw warehouse"]
        RT["raw_threads"]
        RS["raw_sessions"]
        RSE["raw_session_events"]
        RM["raw_messages"]
        RTU["raw_token_usage"]
        RL["raw_logs"]
    end

    subgraph Norm["Normalized warehouse"]
        NT["normalized_threads"]
        NS["normalized_sessions"]
        NM["normalized_messages"]
        NU["normalized_usage_events"]
        NL["normalized_logs"]
        NP["normalized_projects"]
    end

    subgraph Derived["Derived marts"]
        DG["derived_goals"]
        DA["derived_attempts"]
        DTE["derived_timeline_events"]
        DMF["derived_message_facts"]
        DRC["derived_retry_chains"]
        DUS["derived_session_usage"]
        DMU["derived_model_usage"]
        DP["derived_projects"]
    end

    ST --> RT
    SS --> RS
    SS --> RSE
    SS --> RM
    SS --> RTU
    LG --> RL

    RT --> NT
    RS --> NS
    RSE --> NM
    RTU --> NU
    RL --> NL

    NT --> DG
    NS --> DA
    NM --> DTE
    NU --> DUS
    NU --> DMU
    NL --> DTE
    NM --> DMF
    NU --> DMF
    NP --> DP
    DG --> DRC
    DG --> DP
    DA --> DUS
    DA --> DTE

Raw Warehouse Tables

raw_threads

Thread-level metadata from state_5.sqlite.

• Primary key: thread_id
• Use for: thread identity, model, cwd, title, archival state, rollout path
• Note: does not store usage cost; join on thread_id to get tokens or pricing

raw_sessions

One row per rollout session file.

• Primary key: session_path
• Join key: thread_id
• Use for: source rollout file, session timestamp, cwd, source, CLI metadata

raw_session_events

Every JSONL event in session order.

• Primary key: event_id
• Use for: the full raw event stream when you need non-message events such as task_started, turn_context, response_item, or token_count
• Note: most verbose source; prefer more specific raw tables when possible

raw_messages

Parsed developer, user, and assistant text extracted from response_item.

• Primary key: message_id
• Join keys: thread_id, session_path, event_index
• Use for: transcript search, message ordering, conversation reconstruction
• Note: usually the first table to query for history search

raw_token_usage

Parsed token usage rows from token_count events.

• Primary key: token_event_id
• Join keys: thread_id, session_path, event_index
• Use for: input, cached input, output, reasoning, and total tokens
• Note: model is not populated here; use raw_threads.model or normalized_threads.model for model attribution

raw_logs

Runtime/log side-channel rows from logs_1.sqlite.

• Primary key: (source_path, row_id)
• Join key: thread_id
• Use for: task boundary traces, debug logs, model hints, and any side-band evidence not in rollout JSONL

---

Normalized Tables

normalized_threads

Thread metadata after normalization.

• Primary key: thread_id
• Use for: cleaned thread attributes plus derived counts (session/message/log totals)

normalized_sessions

Session metadata after normalization.

• Primary key: session_path
• Use for: canonical session-level metadata and ordering

normalized_messages

Message rows after normalization.

• Primary key: message_id
• Use for: stable transcript search and turn reconstruction

normalized_usage_events

Token usage rows after normalization.

• Primary key: usage_event_id
• Use for: stable usage accounting before derived aggregation
• Model attribution uses the explicit usage-event model when present. Codex events without that field inherit the nearest preceding turn_context model from the same session.
• Provider token categories are not directly additive across sources. OpenAI cached input is a subset of input tokens, while Claude reports uncached input, cache creation, and cache reads as separate categories. Use total_tokens for cross-provider token volume and provider-aware pricing for cost; do not sum input_tokens + cached_input_tokens across providers.

normalized_logs

Log rows after normalization.

• Primary key: (source_path, row_id)
• Use for: cleaned log analysis and thread-level tracing

normalized_projects

Project-level aggregate after normalization.

• Primary key: project_cwd
• Use for: counts by project root before higher-level goal derivation

---

Derived Tables

derived_goals

One row per thread/goal.

• Primary key: thread_id
• Use for: the main unit of product analysis
• Key fields: attempt_count, retry_count, message_count, usage_event_count, timeline_event_count, model

derived_attempts

One row per session inside a thread. The table name is retained for warehouse compatibility.

• Primary key: attempt_id
• Join keys: thread_id, session_path
• Use for: session-level comparison

derived_timeline_events

Compressed timeline view of the full thread.

• Primary key: timeline_event_id
• Use for: ordered reconstruction of what happened over time

derived_message_facts

Message-level OLAP fact table with token usage attributed to the nearest assistant turn, the resolved model, and a derived message date.

• Primary key: message_id
• Join keys: thread_id, session_path, event_index
• Use for: token-by-message reporting, model slicing, date slicing

derived_retry_chains

Legacy structural summary per thread. The table name and columns are retained for warehouse compatibility; they describe session multiplicity and must not be interpreted as observed user retries.

• Primary key: thread_id
• Key fields: retry_count, has_retry_pressure, attempt_count
• retry_count: legacy name for additional sessions after the first
• has_retry_pressure: legacy name for whether a thread contains multiple sessions
• attempt_count: legacy name for the session count
• Use for: compatibility with existing warehouses; prefer session counts for new analysis

derived_session_usage

Session-level usage aggregates.

• Primary key: session_usage_id
• Join keys: thread_id, session_path, attempt_index
• Use for: cost analysis per dialogue/session usage aggregate

derived_model_usage

Usage aggregates split by session and model so a model switch does not assign the full session to one model.

• Primary key: model_usage_id
• Join keys: thread_id, session_path, attempt_index
• Use for: model-level token and cost reporting

derived_projects

Project-level aggregate after derivation.

• Primary key: project_cwd
• Use for: project comparison across threads, sessions, tokens, and timeline volume

---

Stable Identifiers

Identifier	Meaning
thread_id	Top-level conversation/thread identity
session_path	Source rollout file for one session
turn_id	Per-turn identifier from task_started and turn_context events
event_index	Raw JSONL line order within a session
message_id	Deterministic id for a parsed message row
usage_event_id	Deterministic id for a token usage event
timeline_event_id	Deterministic id for derived timeline rows

---

Practical Search Workflow

For Codex or Claude Code sessions (full transcript + cost):

1. Ingest sources into the raw warehouse:
   • All sources: history-ingest (reads ~/.codex + ~/.claude, default)
   • Codex only: history-ingest --source codex (reads ~/.codex)
   • Claude Code only: history-ingest --source claude (reads ~/.claude/projects/)
2. Normalize raw rows into stable message, usage, and project tables.
3. Derive higher-level goal, attempt, and timeline marts.
4. Search raw_messages or normalized_messages for transcript text.
5. Use thread_id, session_path, and turn_id to group related context.
6. Use raw_token_usage or normalized_usage_events for cost or token questions.
7. Use derived_message_facts for message-level OLAP analysis or token spend by date.
8. Use derived_goals and derived_projects for project-level comparison.

Useful Query Shapes

Search transcript text:

SELECT thread_id, session_path, role, text
FROM raw_messages
WHERE text LIKE '%keyword%'
ORDER BY thread_id, session_path, event_index, message_index;

Find usage for a turn or session:

SELECT thread_id, session_path, input_tokens, cached_input_tokens, output_tokens, total_tokens
FROM raw_token_usage
WHERE thread_id = ? OR session_path = ?
ORDER BY event_index;

Inspect message-level token facts:

SELECT message_date, model, role, text, total_tokens
FROM derived_message_facts
WHERE thread_id = ?
ORDER BY message_timestamp, event_index, message_index;

Inspect derived project slice:

SELECT project_cwd, thread_count, attempt_count, message_count, usage_event_count, total_tokens
FROM derived_projects
ORDER BY thread_count DESC, attempt_count DESC, project_cwd ASC;

---

Extending the pipeline

To add a new data source (e.g. a different agent or log format):

1. Add a new ingest module that reads from the new source and writes into the same raw warehouse schema, or a new schema alongside it.
2. Add normalize and derive stages that produce the same output table shapes (derived_goals, derived_attempts, etc.) so downstream comparison and analysis remain unchanged.

The current raw table names (raw_threads, raw_sessions, etc.) are Codex-specific. New adapters should introduce their own raw table namespace rather than reusing these names.

---

Notes

• raw_messages is the first place to look for transcript text.
• Do not add a new message table unless the current pipeline cannot represent the question.
• For new search features, prefer a read-only CLI over new storage unless new storage is clearly the bottleneck.

This site is open source. Improve this page.