getcargohq__cargo-skills/cargo-orchestration/references/examples/queries.md

Orchestration query examples

Run SQL against orchestration runtime tables — runs, batches, spans, records — with cargo-ai orchestration query execute. Use this for ad-hoc analytics on workflow execution (error rates, throughput, slowest nodes, per-node failure breakdowns) without the workflow-scoped filters of run get-metrics / run count.

The backing store is ClickHouse; queries are read-only and exit non-zero with {"errorMessage": "..."} on error.

For SQL against workspace storage (Companies, Contacts, Deals…), use cargo-ai storage query execute "<sql>" — documented in the cargo-storage skill (references/examples/queries.md).

Basic query flow

cargo-ai orchestration query execute \
  "SELECT count() FROM runs WHERE status = 'error'"

Success response:

{
  "rows": [{ "count()": 42 }]
}

Tables

Tables are referenced without a schema prefix. The query engine scopes every read to your workspace automatically.

Table	Use it for
runs	Per-record workflow executions (status, timing, executions array, batch)
batches	Batch-level rows: counts (runs_count, failed_runs_count), credit usage
spans	Flattened per-node execution rows (one row per node execution)
records	Materialized view over runs keyed by record id

Common columns: workspace_uuid, workflow_uuid, batch_uuid, release_uuid, status, created_at, updated_at, finished_at, credits_used_count. See the migration files in apps/backend/src/domains/orchestration/migrations/ for the full schema.

Example queries

# Error rate across the whole workspace
cargo-ai orchestration query execute \
  "SELECT countIf(status='error') / count() AS error_rate FROM runs WHERE created_at > now() - INTERVAL 1 DAY"

# Errors per workflow over the last week
cargo-ai orchestration query execute \
  "SELECT workflow_uuid, count() AS errors FROM runs WHERE status='error' AND created_at > now() - INTERVAL 7 DAY GROUP BY workflow_uuid ORDER BY errors DESC"

# Batch status breakdown
cargo-ai orchestration query execute \
  "SELECT status, count() FROM batches GROUP BY status"

# Slowest node executions in the last hour
cargo-ai orchestration query execute \
  "SELECT node_slug, node_kind, dateDiff('second', execution_started_at, execution_finished_at) AS duration_s
   FROM spans
   WHERE execution_finished_at > now() - INTERVAL 1 HOUR
   ORDER BY duration_s DESC
   LIMIT 20"

# Per-node failure counts
cargo-ai orchestration query execute \
  "SELECT node_slug, count() AS failures
   FROM spans
   WHERE execution_status='error' AND execution_started_at > now() - INTERVAL 1 DAY
   GROUP BY node_slug
   ORDER BY failures DESC"

# Credit spend by workflow this month
cargo-ai orchestration query execute \
  "SELECT workflow_uuid, sum(credits_used_count) AS credits
   FROM batches
   WHERE created_at >= toStartOfMonth(now())
   GROUP BY workflow_uuid
   ORDER BY credits DESC"

Common table expressions

cargo-ai orchestration query execute \
  "WITH recent AS (SELECT * FROM runs WHERE created_at > now() - INTERVAL 1 DAY)
   SELECT status, count() FROM recent GROUP BY status"

Limits and restrictions

Orchestration queries run as a read-only ClickHouse user with per-query caps:

Limit	Value
max_execution_time	30s
max_result_rows	10 000
max_rows_to_read	10 000 000
max_columns_to_read	50
max_subquery_depth	5

DDL, introspection functions, table functions (merge, cluster, remote, url, s3, file, …), dictionary accessors, and the query cache are all denied. Wrap heavy aggregations in time filters (created_at > now() - INTERVAL N DAY) to stay under the row-scan cap.

Error handling

{ "errorMessage": "Code: 158. Memory limit exceeded ..." }

Common causes:

• Scanned too many rows → narrow the time window with a created_at/execution_started_at predicate
• Forbidden function (e.g. system.tables, cluster(), url()) → use only SELECT against the four tables above
• Too many result rows → add a LIMIT or aggregate before returning