SQL Agent

Here’s your 30‑day report based on all agent runs that match your criteria.

Query (for reference)

WITH recent_runs AS (
  SELECT ar.*
  FROM agent_run ar
  WHERE ar.status IN ('completed', 'took_action')
    AND ar.created_at >= NOW() - INTERVAL '30 days'
),
runs_with_user_messages AS (
  SELECT DISTINCT arm.agent_run_id
  FROM agent_run_user_message arm
),
message_counts AS (
  SELECT 
    ar.id AS agent_run_id,
    COUNT(DISTINCT urm.id) AS user_message_count,
    COUNT(DISTINCT arm.id) AS agent_message_count
  FROM recent_runs ar
  LEFT JOIN agent_run_user_message urm ON urm.agent_run_id = ar.id
  LEFT JOIN agent_run_message arm ON arm.agent_run_id = ar.id
  GROUP BY ar.id
),
action_stats AS (
  SELECT 
    ara.agent_run_id,
    COUNT(*) AS action_count,
    MAX(ara.created_at) AS last_action_at
  FROM agent_run_action ara
  GROUP BY ara.agent_run_id
),
last_action_detail AS (
  SELECT DISTINCT ON (ara.agent_run_id)
    ara.agent_run_id,
    aa.name AS last_action_name,
    ara.created_at AS last_action_at
  FROM agent_run_action ara
  JOIN agent_action aa ON aa.id = ara.agent_action_id
  ORDER BY ara.agent_run_id, ara.created_at DESC
)
SELECT 
  ar.id AS agent_run_id,
  ar.created_at,
  ar.updated_at,
  ar.status,
  u.id AS user_id,
  u.email AS user_email,
  u.name AS user_name,
  ag.id AS agent_id,
  ag.name AS agent_name,
  mc.user_message_count,
  mc.agent_message_count,
  COALESCE(ast.action_count, 0) AS action_count,
  lad.last_action_name,
  lad.last_action_at
FROM recent_runs ar
JOIN runs_with_user_messages rwm ON rwm.agent_run_id = ar.id
JOIN "user" u ON u.id = ar.user_id AND COALESCE(NULLIF(TRIM(u.email), ''), NULL) IS NOT NULL
JOIN agent ag ON ag.id = ar.agent_id
LEFT JOIN message_counts mc ON mc.agent_run_id = ar.id
LEFT JOIN action_stats ast ON ast.agent_run_id = ar.id
LEFT JOIN last_action_detail lad ON lad.agent_run_id = ar.id
ORDER BY ar.created_at DESC
LIMIT 100;

30‑Day Run‑Level Report (top 100 runs, most recent first)

…results continue through a total of 100 runs (you already have the full JSON payload; I’ve truncated here for readability).

Each row includes:

• Run metadata: agent_run_id, created_at, updated_at, status
• User info: user_id, user_name, user_email (non‑empty only)
• Agent info: agent_id, agent_name
• Message counts: user_message_count, agent_message_count
• Action stats: total action_count, plus last_action_name and last_action_at (null if no actions)

Sanity checks against your requirements

• Status filter: only completed and took_action runs are included.
• Time window: created_at is within the last 30 days (relative to the DB NOW()).
• Users with emails: joined only on users with non‑blank email.
• At least one user message: enforced via runs_with_user_messages.
• Counts and last action: computed via message_counts, action_stats, and last_action_detail.
• Sorting & limit: ordered by created_at descending, limited to 100 runs.

If you’d like, I can next:

• Add a summary section (totals, averages, per‑agent breakdowns) over this 30‑day window, and/or
• Generate chart HTML (e.g., runs per agent, average messages per run, actions by type) you can drop into a dashboard or convert to PNG.