ClickHouse

This query assumes an example schema: agent_turns, with one row per question; app_users; and agent_queries, agent_reasoning, agent_messages and agent_tool_calls, with one row per step, keyed by turn_id. Replace the names with your own, and drop any column your agent doesn’t record.

ClickHouse doesn’t support correlated subqueries, so each step table is aggregated and joined. A turn with no steps gets an empty string from the join, which if turns into [].

Connection string

clickhouse://benchouse_read:PASSWORD@HOST:9440/DB?secure=true

Query

SELECT
    toString(t.id)      AS turn_id,
    t.created_at        AS asked_at,
    t.updated_at        AS updated_at,
    t.finished_at       AS completed_at,
    t.question          AS question_text,
    t.answer            AS answer_text,
    t.status            AS outcome,
    t.error             AS error_message,
    toString(t.chat_id) AS conversation_id,
    toString(t.user_id) AS user_id,
    u.email             AS user_email,
    t.model             AS model_name,
    t.input_tokens      AS input_tokens,
    t.output_tokens     AS output_tokens,
    if(q.statements = '', '[]', q.statements) AS statements,
    if(r.reasoning = '', '[]', r.reasoning)   AS reasoning,
    if(m.messages = '', '[]', m.messages)     AS messages,
    if(c.tool_calls = '', '[]', c.tool_calls) AS tool_calls
FROM agent_turns AS t
LEFT JOIN app_users AS u ON u.id = t.user_id
LEFT JOIN (
    SELECT turn_id, toJSONString(arrayMap(x -> x.2, arraySort(x -> x.1, groupArray((started_at,
        CAST((sql_text, status, error, row_count, started_at, ended_at, is_final),
             'Tuple(sql String, status String, error Nullable(String), rows Nullable(Int64), started_at DateTime64(6, ''UTC''), ended_at DateTime64(6, ''UTC''), is_answer Bool)')))))) AS statements
      FROM agent_queries GROUP BY turn_id
) AS q ON q.turn_id = t.id
LEFT JOIN (
    SELECT turn_id, toJSONString(arrayMap(x -> x.2, arraySort(x -> x.1, groupArray((seq,
        CAST((kind, content), 'Tuple(label String, text String)')))))) AS reasoning
      FROM agent_reasoning GROUP BY turn_id
) AS r ON r.turn_id = t.id
LEFT JOIN (
    SELECT turn_id, toJSONString(arrayMap(x -> x.2, arraySort(x -> x.1, groupArray((seq,
        CAST((kind, content), 'Tuple(kind String, text String)')))))) AS messages
      FROM agent_messages GROUP BY turn_id
) AS m ON m.turn_id = t.id
LEFT JOIN (
    SELECT turn_id, toJSONString(arrayMap(x -> x.2, arraySort(x -> x.1, groupArray((seq,
        CAST((tool, input, output, status, started_at, ended_at),
             'Tuple(name String, input String, output String, status String, started_at DateTime64(6, ''UTC''), ended_at DateTime64(6, ''UTC''))')))))) AS tool_calls
      FROM agent_tool_calls GROUP BY turn_id
) AS c ON c.turn_id = t.id

Read-only user

CREATE USER benchouse_read IDENTIFIED BY '…' SETTINGS readonly = 2;
GRANT SELECT ON agent_logs.* TO benchouse_read;