cmux-team-analyze

cmux-team-analyze: Ad-hoc metrics analysis with DuckDB

This skill is cmux-team metrics query Execute DuckDB ad-hoc SQL via the CLI (T412), and perform exploratory analysis such as cross-task, time-series, and cohort comparisons.

CLI itself: cmux-team metrics query --sql '<SQL>'(requires DuckDB 0.10+) This skill is a collection of recipes + a manual for the available sources.

1. Activation conditions and division of roles

Purpose	Skill / CLI to use
View a single task's session history as a time series	trace-task skill
Check a single task's Conductor / Agent output	trace-task skill
Aggregation across multiple tasks (per-task trends, distributions)	This skill (cmux-team-analyze)
Cohort comparison of baseline / evaluation periods	This skill + cmux-team metrics compare
Detecting side effects after introducing an intervention (token consumption, forced_close rate)	This skill
Analysis joining events.jsonl + traces.db	This skill
Per-task trends across snapshot files	This skill
Just output the default 6-axis aggregation table	cmux-team metrics（aggregate）

Decision axis: If it's "what happened within one task", use trace-task. If it's "what is happening across multiple tasks", use this skill.

2. Available sources

cmux-team metrics query Views / tables automatically attached at startup:

Name	Type	Origin	Main use
t.api_usage	SQLite table	.team/traces/traces.db (READ_ONLY ATTACH)	Token consumption, API failures, status code
t.hook_signals	SQLite table	Same as above	tool call、tool failure、deny、SESSION_STARTED marker
t.task_sessions	SQLite table	Same as above	Linking session_id ↔ task_id
t.rate_limit_snapshots	SQLite table	Same as above	Time series of proxy rate-limit
events	DuckDB view	.team/logs/events.jsonl (read_json newline_delimited)	task lifecycle
snapshots	DuckDB view	.team/metrics/snapshots/*.json (1 row/file)	period summary
snapshots_per_task	DuckDB view	UNNEST(snapshots.per_task) を pre-join	Per-task time-series trends

Missing sources emit a warning to stderr and are silently skipped. Even in a fresh project, SELECT 1 simple queries like this pass.

2.1 hook_signals × task_sessions の JOIN idiom

hook_signals は session_id has task_id but does not have task_sessions を MIN(task_id) GROUP BY session_id To link to task_id, aggregate with

WITH session_to_task AS (
  SELECT session_id, MIN(task_id) AS task_id
  FROM t.task_sessions
  WHERE event IN ('assigned','agent_spawned')
    AND task_id IS NOT NULL
    AND session_id IS NOT NULL AND session_id != ''
  GROUP BY session_id
)
SELECT ...
FROM t.hook_signals h
LEFT JOIN session_to_task s2t
  ON h.session_id = s2t.session_id AND h.session_id != ''
WHERE ...

3. Common guidelines

• Do not scan all of a large events.jsonl / t.hook_signals .WHERE timestamp >= '...' Always narrow down the time range with .
• DuckDB は read-only attach。ATTACH / INSTALL / LOAD Do not redeclare within user SQL.
• --format Guidelines for choosing : table(=default, for humans) / json(pipe to jq) / csv / tsv(spreadsheet integration).
• --explain Hone recipes while looking at the query plan with .

---

DuckDB Recipe Library

Each recipe places SQL immediately after the <!-- recipe: <id> --> comment. Keep it in a form that can be piped to by copy-paste. cmux-team metrics query --sql '<RECIPE>' .

Verification of operation: Each recipe has been confirmed to exit 0 against the real duckdb binary during the implementation phase (results are recorded in the task's implementer.md).

R1. per-task token aggregation (api_usage × task_sessions)

Basic aggregation for viewing "token consumption of the last N tasks" per-task.t.api_usage は task_id directly, so no JOIN is needed. Pick up outcome from events .

WITH closed AS (
  SELECT task_id,
         arg_max(event, ts) AS outcome_event,
         max(ts)             AS closed_ts
  FROM events
  WHERE event IN ('task_completed', 'task_completed_state_mismatch',
                  'task_aborted', 'conductor_disconnect_timeout')
  GROUP BY task_id
)
SELECT
  u.task_id,
  c.outcome_event AS outcome,
  c.closed_ts,
  SUM(u.input_tokens)                                                    AS tokens_input,
  SUM(u.output_tokens)                                                   AS tokens_output,
  SUM(u.cache_creation_input_tokens) + SUM(u.cache_read_input_tokens)    AS tokens_cache,
  COUNT(*)                                                               AS api_requests
FROM t.api_usage u
LEFT JOIN closed c USING (task_id)
WHERE u.task_id IS NOT NULL
GROUP BY u.task_id, c.outcome_event, c.closed_ts
ORDER BY tokens_input DESC
LIMIT 30;

R2. cohort extraction (baseline vs evaluation)

Output the per-task metrics for the baseline / evaluation periods from §8 split into 2 cohorts in a single SQL (for when you want to line up per-task changes in one row).

WITH baseline AS (
  SELECT 'baseline' AS cohort, *
  FROM snapshots_per_task
  WHERE snapshot_date BETWEEN '2026-05-04' AND '2026-05-31'
),
evalu AS (
  SELECT 'evaluation' AS cohort, *
  FROM snapshots_per_task
  WHERE snapshot_date BETWEEN '2026-06-15' AND '2026-07-12'
),
combined AS (
  SELECT * FROM baseline UNION ALL SELECT * FROM evalu
)
SELECT
  cohort,
  COUNT(*)                                  AS tasks_n,
  AVG(duration_ms)                          AS duration_ms_mean,
  AVG(tool_call_total)                      AS tool_call_mean,
  AVG(tool_failure_rate)                    AS tool_failure_rate_mean,
  AVG(tokens.input + tokens.output)         AS tokens_per_task_mean,
  COUNT(*) FILTER (WHERE outcome = 'completed')              AS completed,
  COUNT(*) FILTER (WHERE outcome = 'forced_close')           AS forced_close
FROM combined
GROUP BY cohort
ORDER BY cohort;

If statistical tests (Welch / Mann-Whitney / 2-prop z-test) are needed, use cmux-team metrics compare --baseline ... --comparison ... together. This recipe is for exploratory use to see "whether there seems to be a difference".

R3. hook_signals immediately before forced_close (narrowing down the cause)

conductor_disconnect_timeout list the hooks immediately before tasks closed by in chronological order. Useful as a clue to the root cause of a crash (external command failure, rate limit hit, etc.).

WITH targets AS (
  SELECT task_id, MIN(ts) AS forced_at
  FROM events
  WHERE event = 'conductor_disconnect_timeout'
  GROUP BY task_id
),
session_to_task AS (
  SELECT session_id, MIN(task_id) AS task_id
  FROM t.task_sessions
  WHERE event IN ('assigned','agent_spawned')
    AND task_id IS NOT NULL
    AND session_id != ''
  GROUP BY session_id
)
SELECT
  t.task_id,
  h.timestamp,
  h.type,
  h.tool_name,
  SUBSTRING(h.payload_json, 1, 200) AS payload_head
FROM t.hook_signals h
JOIN session_to_task s2t ON h.session_id = s2t.session_id AND h.session_id != ''
JOIN targets t           ON s2t.task_id = t.task_id
WHERE CAST(h.timestamp AS TIMESTAMP) BETWEEN
  CAST(t.forced_at AS TIMESTAMP) - INTERVAL 5 MINUTE
  AND CAST(t.forced_at AS TIMESTAMP)
ORDER BY t.task_id, h.timestamp DESC
LIMIT 200;

R4. tool_failure chain detection (consecutive failures within the same session)

PostToolUse で success=false extract consecutive intervals with a window function. 3 or more consecutive failures are a signal of the agent's loop / dead end.

WITH failures AS (
  SELECT
    session_id,
    timestamp,
    tool_name,
    CASE WHEN type = 'POST_TOOL_USE'
      AND (
        json_extract(payload_json, '$.payload.tool_response.success') = 0
        OR json_extract(payload_json, '$.payload.tool_response.error') IS NOT NULL
      )
    THEN 1 ELSE 0 END AS is_failure
  FROM t.hook_signals
  WHERE type = 'POST_TOOL_USE'
    AND session_id != ''
    AND timestamp >= '2026-05-01'
    AND payload_json IS NOT NULL
    AND json_valid(payload_json)         -- malformed payload を skip（best-effort 原則）
),
runs AS (
  SELECT *,
    SUM(is_failure) OVER (
      PARTITION BY session_id
      ORDER BY timestamp
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_failures
  FROM failures
)
SELECT session_id, timestamp, tool_name, rolling_failures
FROM runs
WHERE rolling_failures >= 3
ORDER BY session_id, timestamp
LIMIT 100;

R5. SESSION_STARTED の loaded_plugins / loaded_skills cohort filter

Use the loadedPlugins / loadedSkills of the SESSION_STARTED hook payload introduced in §3.5.2 as a cohort tag. Use case: "narrow the population to only sessions where a specific plugin is enabled".

SELECT
  h.session_id,
  s2t.task_id,
  CASE
    WHEN json_type(h.payload_json, '$.loadedPlugins') = 'NULL' THEN 'unknown'
    WHEN json_type(h.payload_json, '$.loadedPlugins') IS NULL  THEN 'unknown'
    WHEN json_array_length(json_extract(h.payload_json, '$.loadedPlugins')) = 0 THEN 'empty'
    ELSE 'loaded'
  END AS plugin_state,
  json_extract(h.payload_json, '$.loadedPlugins') AS plugins,
  json_extract(h.payload_json, '$.loadedSkills')  AS skills
FROM t.hook_signals h
LEFT JOIN (
  SELECT session_id, MIN(task_id) AS task_id
  FROM t.task_sessions
  WHERE event IN ('assigned','agent_spawned') AND task_id IS NOT NULL AND session_id != ''
  GROUP BY session_id
) s2t ON h.session_id = s2t.session_id AND h.session_id != ''
WHERE h.type = 'SESSION_STARTED'
  AND h.timestamp >= '2026-05-01'
  AND json_valid(h.payload_json)         -- malformed payload を skip
ORDER BY h.timestamp DESC
LIMIT 200;

Note: do not mix sessions with plugin_state = 'unknown' into the "plugin-less cohort" via the cohort filter (spec §3.5.2 note).

R6. per-day trend (counts by outcome from snapshots)

Line up the snapshots in chronological order to see the trend of counts by outcome. snapshots_per_task Since snapshot_date is pre-joined into the view, per-day trend only requires GROUP BY.

SELECT
  snapshot_date,
  COUNT(*)                                         AS tasks_n,
  COUNT(*) FILTER (WHERE outcome = 'completed')    AS completed,
  COUNT(*) FILTER (WHERE outcome = 'aborted')      AS aborted,
  COUNT(*) FILTER (WHERE outcome = 'forced_close') AS forced_close,
  COUNT(*) FILTER (WHERE outcome = 'state_mismatch') AS state_mismatch,
  AVG(duration_ms)                                 AS duration_ms_mean,
  AVG(tool_call_total)                             AS tool_call_mean
FROM snapshots_per_task
WHERE snapshot_date >= '2026-05-01'
GROUP BY snapshot_date
ORDER BY snapshot_date;

4. Follow-up candidates (recipe stubs)

The following are candidates for future addition, though this skill does not include finished recipes for them.

• R7. claim-vs-diff detection: From the agent's self-reported outcome and the git diff, extract semantic mismatches such as "says completed but wrote nothing" or "aborted yet there are changes". Implementation requires semantic analysis, so it is treated as a follow-up task (see the T412 discussion history).
• R8. Narrowing down tasks with abnormal duration_ms: Extract outliers by the Z-score of per-task duration.
• R9. Correlation between proxy rate-limit hits and forced_close: t.rate_limit_snapshots と events chronologically JOIN the forced_close events.

5. Related specs / related CLI

• spec: docs/spec/11-metrics.md — Metrics taxonomy / data source / cohort comparison
• CLI:
  • cmux-team metrics query — DuckDB ad-hoc SQL (used directly by this skill)
  • cmux-team metrics — per-task / per-period aggregate for the default 6 axes (predefined)
  • cmux-team metrics snapshot|compare|health — daily snapshot / statistical tests / missing-data check
• Related skills:
  • trace-task — per-task session history analysis (see §1 for the division of roles)