MySQL

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.

Requires MySQL 8.0.14 or later. JSON_ARRAYAGG can’t sort, so each step table is read through a sorted subquery. The LIMIT stops MySQL from dropping the sort.

Connection string

mysql://benchouse_read:PASSWORD@HOST:3306/DB?tls=true

Query

SELECT
    CAST(t.id AS CHAR)      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,
    CAST(t.chat_id AS CHAR) AS conversation_id,
    CAST(t.user_id AS CHAR) 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,
    COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'sql', q.sql_text, 'status', q.status, 'error', q.error,
                'rows', q.row_count, 'started_at', q.started_at,
                'ended_at', q.ended_at, 'is_answer', q.is_final IS TRUE))
         FROM (SELECT * FROM agent_queries ORDER BY started_at LIMIT 18446744073709551615) q
        WHERE q.turn_id = t.id), JSON_ARRAY()) AS statements,
    COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT('label', r.kind, 'text', r.content))
         FROM (SELECT * FROM agent_reasoning ORDER BY seq LIMIT 18446744073709551615) r
        WHERE r.turn_id = t.id), JSON_ARRAY()) AS reasoning,
    COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT('kind', m.kind, 'text', m.content))
         FROM (SELECT * FROM agent_messages ORDER BY seq LIMIT 18446744073709551615) m
        WHERE m.turn_id = t.id), JSON_ARRAY()) AS messages,
    COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'name', c.tool, 'input', c.input, 'output', c.output,
                'status', c.status, 'started_at', c.started_at, 'ended_at', c.ended_at))
         FROM (SELECT * FROM agent_tool_calls ORDER BY seq LIMIT 18446744073709551615) c
        WHERE c.turn_id = t.id), JSON_ARRAY()) AS tool_calls
FROM agent_turns t
LEFT JOIN app_users u ON u.id = t.user_id

Read-only user

CREATE USER 'benchouse_read'@'%' IDENTIFIED BY '…' REQUIRE SSL;
GRANT SELECT ON agent_logs.* TO 'benchouse_read'@'%';