SQL Server

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.

ORDER BY is allowed inside each FOR JSON subquery, but not on the outer query. Cast a uniqueidentifier ID to NVARCHAR, as shown.

Connection string

sqlserver://benchouse_read:PASSWORD@HOST:1433?database=DB&encrypt=true

Query

SELECT
    CAST(t.id AS NVARCHAR(50))      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 NVARCHAR(50)) AS conversation_id,
    CAST(t.user_id AS NVARCHAR(50)) 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 q.sql_text AS [sql], q.status AS [status], q.error AS [error],
                     q.row_count AS [rows], q.started_at AS [started_at],
                     q.ended_at AS [ended_at], q.is_final AS [is_answer]
                FROM agent_queries q WHERE q.turn_id = t.id
               ORDER BY q.started_at FOR JSON PATH), '[]') AS statements,
    COALESCE((SELECT r.kind AS [label], r.content AS [text]
                FROM agent_reasoning r WHERE r.turn_id = t.id
               ORDER BY r.seq FOR JSON PATH), '[]') AS reasoning,
    COALESCE((SELECT m.kind AS [kind], m.content AS [text]
                FROM agent_messages m WHERE m.turn_id = t.id
               ORDER BY m.seq FOR JSON PATH), '[]') AS messages,
    COALESCE((SELECT c.tool AS [name], JSON_QUERY(c.input) AS [input],
                     JSON_QUERY(c.output) AS [output], c.status AS [status],
                     c.started_at AS [started_at], c.ended_at AS [ended_at]
                FROM agent_tool_calls c WHERE c.turn_id = t.id
               ORDER BY c.seq FOR JSON PATH), '[]') AS tool_calls
FROM agent_turns t
LEFT JOIN app_users u ON u.id = t.user_id

Read-only user

CREATE LOGIN benchouse_read WITH PASSWORD = '…';
CREATE USER benchouse_read FOR LOGIN benchouse_read;
ALTER ROLE db_datareader ADD MEMBER benchouse_read;