Analyze Runs with DuckDB

.agent-journal/experiments/{experiment-id}/runs/{run-id}/events.jsonl

SELECT
    toolName AS tool,
    COUNT(*) AS calls,
    ROUND(AVG(durationMs), 0) AS average_ms,
    COUNT(*) FILTER (WHERE NOT success) AS failures
FROM read_ndjson_auto(
    '.agent-journal/experiments/*/runs/*/events.jsonl',
    union_by_name = true)
WHERE "@type" = 'tool_call'
GROUP BY toolName
ORDER BY calls DESC, tool;

SELECT
    filename,
    SUM(tokenUsage.inputTokens) AS input_tokens,
    SUM(tokenUsage.outputTokens) AS output_tokens,
    SUM(
        cost.inputCostUsd
        + cost.outputCostUsd
        + cost.thinkingCostUsd
        - cost.cacheSavingsUsd
    ) AS total_cost_usd
FROM read_ndjson_auto(
    '.agent-journal/experiments/*/runs/*/events.jsonl',
    union_by_name = true,
    filename = true)
WHERE "@type" = 'llm_call'
GROUP BY filename
ORDER BY filename;

SELECT
    regexp_extract(filename, 'runs/([^/]+)/events', 1) AS run_id,
    ROW_NUMBER() OVER (
        PARTITION BY filename
        ORDER BY timestamp
    ) AS sequence_number,
    toolName AS tool
FROM read_ndjson_auto(
    '.agent-journal/experiments/*/runs/*/events.jsonl',
    union_by_name = true,
    filename = true)
WHERE "@type" = 'tool_call'
ORDER BY run_id, sequence_number;

WITH tool_sequence AS (
    SELECT
        filename,
        toolName AS tool,
        ROW_NUMBER() OVER (
            PARTITION BY filename
            ORDER BY timestamp
        ) AS sequence_number
    FROM read_ndjson_auto(
        '.agent-journal/experiments/*/runs/*/events.jsonl',
        union_by_name = true,
        filename = true)
    WHERE "@type" = 'tool_call'
)
SELECT
    current_step.tool AS from_tool,
    next_step.tool AS to_tool,
    COUNT(*) AS transitions
FROM tool_sequence current_step
JOIN tool_sequence next_step
  ON current_step.filename = next_step.filename
 AND next_step.sequence_number = current_step.sequence_number + 1
GROUP BY from_tool, to_tool
ORDER BY transitions DESC, from_tool, to_tool;