> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getcargo.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Querying orchestration data
> Read-only ClickHouse SQL over your runs, batches, records and spans: the tables behind alert query scopes, the CLI and the API.
Every run Cargo executes is written to a ClickHouse database you can query directly: four tables (`spans`, `runs`, `records` and `batches`) holding the full execution history of your plays, tools and agents.
Queries are **read-only** and **automatically scoped to your workspace**: you never write a `workspace_uuid` filter, and you can't reach another workspace's data.
## Where you can run a query
```bash theme={null}
cargo-ai orchestration query execute \
"select count(*) from runs where created_at > now() - interval 1 day"
```
```bash theme={null}
curl -X POST "https://api.getcargo.io/v1/orchestration/query" \
-H "Authorization: Bearer YOUR_API_TOKEN" \
-H "Content-Type: application/json" \
-d '{"query": "select count(*) from runs"}'
```
```ts alerts/error-spike.ts theme={null}
export const errorSpike = defineAlert("error-spike", {
schedule: { type: "cron", cron: "@every 15m" },
scope: {
kind: "orchestrationQuery",
query: `select count(*) from spans
where execution_status = 'error'
and execution_started_at > now() - interval 1 hour`,
},
threshold: { operator: "gte", value: 50 },
actions: [{ ref: sentinel, config: { prompt: "{{event.value}} errors" } }],
});
```
In the UI, the same engine backs the **Orchestration SQL** scope of the alert editor; see [Using the UI](/alerts/using-ui) and the [query scope reference](/alerts/overview#query-scope).
## This is ClickHouse SQL
The dialect is ClickHouse, not PostgreSQL, so the idioms differ from most warehouses:
| Instead of | Write |
| --------------------------- | ------------------------------------- |
| `count(*) filter (where …)` | `countIf(execution_status = 'error')` |
| `now() - interval '1 hour'` | `now() - interval 1 hour` |
| `extract(epoch from b - a)` | `dateDiff('second', a, b)` |
| `date_trunc('hour', ts)` | `toStartOfHour(ts)` |
Before a query reaches ClickHouse it is parsed with a PostgreSQL grammar, so a
few ClickHouse-only constructs are rejected even though ClickHouse itself
would accept them. The one you are most likely to hit is **parametric
aggregates**: `quantile(0.95)(x)` fails to parse. Use `avg(x)`, `min(x)`,
`max(x)` or `countIf(...)` instead.
## Rules and limits
Only a single `SELECT` runs. `INSERT`, `UPDATE`, `DELETE`, DDL and multiple statements separated by `;` are all rejected before execution, as is any table other than the four below. Reference them unqualified (`spans`, not `orchestration.spans`) and name your own CTEs freely.
| Limit | Value |
| ---------------------- | ----------------- |
| Rows returned | 10,000 |
| Execution time | 30s |
| Rows scanned | 10 million |
| Memory | 1 GB |
| Columns read per query | 50 |
| Subquery depth | 5 |
| Query length | 10,000 characters |
All four tables are `ReplacingMergeTree`, and queries run with `FINAL`: you
always see the latest version of each row, with no deduplication of your own
needed.
## `spans`
One row **per node execution**, the table to reach for when you're measuring how individual actions behave. Runs that were idle or skipped never produce spans.
| Column | Type | Meaning |
| ---------------------------------- | ----------- | -------------------------------------------------- |
| `workspace_uuid` | UUID | Your workspace. Filtered automatically |
| `workflow_uuid` | UUID | The workflow behind the play or tool |
| `user_uuid` | UUID? | Who started the run |
| `batch_uuid` | UUID | Batch the run belongs to |
| `trace_uuid` | UUID | Groups everything triggered together |
| `record_id` | String | The record the run processed |
| `record_title` | String | That record's title |
| `run_uuid` | UUID | The run this span belongs to |
| `run_context_s3_filename` | String | Internal: run context artifact |
| `run_computed_configs_s3_filename` | String | Internal: resolved node configs |
| `parent_run_uuid` | UUID? | Run that spawned this one |
| `parent_node_uuid` | UUID? | Node that spawned it |
| `parent_batch_uuid` | UUID? | Batch that spawned it |
| `parent_agent_uuid` | UUID? | Agent that spawned it, the agent-trigger case |
| `parent_chat_uuid` | UUID? | Chat that spawned it |
| `parent_message_uuid` | UUID? | Message that spawned it |
| `is_group_parent` | Bool? | Whether the run is the parent of a group execution |
| `node_uuid` | UUID | The node that executed |
| `node_kind` | Enum | `native`, `connector`, `tool` or `agent` |
| `node_slug` | String | The node's slug in the workflow |
| `node_action_slug` | String | The action it ran, e.g. `postMessage` |
| `node_connector_uuid` | UUID? | Connector used, for connector nodes |
| `node_integration_slug` | String? | Integration used, e.g. `slack` |
| `node_tool_uuid` | UUID? | Tool called, for tool nodes |
| `node_agent_uuid` | UUID? | Agent called, for agent nodes |
| `node_release_uuid` | UUID? | Release the node ran from |
| `node_batch_uuid` | UUID? | Batch the node spawned |
| `node_run_uuid` | UUID? | Run the node spawned |
| `node_message_uuid` | UUID? | Message the node produced |
| `node_child_index` | Int32? | Position within a group's children |
| `node_wait_until_finished` | Bool | Whether the node blocked on what it spawned |
| `node_next_uuid` | UUID? | Next node in the workflow |
| `execution_index` | UInt32 | Position of this execution within the run, from 0 |
| `execution_status` | Enum | `error`, `pending` or `success` |
| `execution_error_message` | String? | Why it failed |
| `execution_title` | String? | Human-readable title, as shown in the Spans view |
| `execution_icon_url` | String? | Icon shown alongside it |
| `execution_credits_used_count` | Float32 | Credits this execution consumed |
| `execution_started_at` | DateTime64 | When it started |
| `execution_updated_at` | DateTime64 | When it last changed |
| `execution_finished_at` | DateTime64? | When it finished, null while pending |
`spans` is partitioned by month on `execution_started_at`, so a query that
bounds that column reads far less data than one that doesn't. It also has 41
columns against a 50-column budget, so prefer naming the columns you need over
`select *`.
## `runs`
One row **per run**, the whole execution of one record through one workflow. Unlike `spans`, this includes runs that ended up `idle` or `skipped`.
| Column | Type | Meaning |
| ------------------------------ | ----------- | -------------------------------------------------------------------------------------- |
| `uuid` | UUID | The run |
| `workspace_uuid` | UUID | Your workspace. Filtered automatically |
| `workflow_uuid` | UUID | The workflow it ran |
| `user_uuid` | UUID? | Who started it |
| `batch_uuid` | UUID | Batch it belongs to |
| `trace_uuid` | UUID | Groups everything triggered together |
| `release_uuid` | UUID? | Release it ran from |
| `record_id` | String | The record it processed |
| `record_title` | String | That record's title |
| `status` | Enum | `idle`, `pending`, `running`, `success`, `error`, `skipped`, `cancelling`, `cancelled` |
| `error_message` | String? | Why it failed |
| `executions` | Nested | Per-node executions (see below) |
| `created_at` | DateTime64 | When it was created |
| `updated_at` | DateTime64 | When it last changed |
| `finished_at` | DateTime64? | When it finished |
| `deleted_at` | DateTime64? | Set when soft-deleted |
| `group` | Tuple | `batch_uuid`, `run_uuid`, `node_uuid` of the group it belongs to |
| `parent_uuid` | UUID? | Run that spawned it |
| `parent_node_uuid` | UUID? | Node that spawned it |
| `parent_batch_uuid` | UUID? | Batch that spawned it |
| `parent_agent_uuid` | UUID? | Agent that spawned it |
| `parent_chat_uuid` | UUID? | Chat that spawned it |
| `parent_message_uuid` | UUID? | Message that spawned it |
| `is_group_parent` | Bool? | Whether it is the parent of a group execution |
| `temporal_workflow_id` | UUID | Internal: Temporal workflow instance |
| `stringified_nodes` | String? | Internal: JSON snapshot of the workflow's nodes |
| `context_s3_filename` | String | Internal: run context artifact |
| `computed_configs_s3_filename` | String | Internal: resolved node configs |
### The `executions` nested column
`runs` and `records` both carry the run's node executions as a nested column, which reads as parallel arrays: `executions.status`, `executions.credits_used_count`, and so on. Its fields mirror the `spans` columns of the same name, minus the `node_`/`execution_` prefixes: `node_uuid`, `node_slug`, `node_kind`, `node_action_slug`, `node_connector_uuid`, `node_integration_slug`, `node_tool_uuid`, `node_agent_uuid`, `node_release_uuid`, `node_batch_uuid`, `node_run_uuid`, `node_message_uuid`, `node_child_index`, `node_wait_until_finished`, `next_node_uuid`, `status`, `error_message`, `title`, `icon_url`, `credits_used_count`, `started_at`, `updated_at`, `finished_at`.
Array functions work on them directly, so you can ask how many steps a run took, what it cost, whether any step failed:
```sql theme={null}
select
uuid,
length(executions.node_uuid) as steps,
arraySum(executions.credits_used_count) as credits
from runs
where created_at > now() - interval 1 day
and countEqual(executions.status, 'error') > 0
```
`array join` is **rejected**: the validator reads the joined nested column as
a table name, and only `spans`, `runs`, `batches` and `records` are allowed.
To work one row per execution, query `spans`: it is exactly that flattening,
precomputed, unless you need runs that were idle or skipped.
## `records`
The same shape as `runs`, keyed by the record rather than the run, and excluding idle and skipped runs. Use it to answer "what happened to this record", where `runs` answers "what did this execution do".
It differs from `runs` in four places: `id` and `title` replace `record_id` and `record_title`, `run_uuid` replaces `uuid`, `parent_run_uuid` replaces `parent_uuid`, and `status` can't be `idle` or `skipped`. There is no `deleted_at`. Everything else, including `executions`, is identical.
## `batches`
One row **per batch**, a play or tool triggered over a set of records.
| Column | Type | Meaning |
| ---------------------- | ----------- | ----------------------------------------------------------------------------------------------------- |
| `uuid` | UUID | The batch |
| `workspace_uuid` | UUID | Your workspace. Filtered automatically |
| `workflow_uuid` | UUID | The workflow it ran |
| `user_uuid` | UUID? | Who triggered it |
| `trace_uuid` | UUID? | Groups everything triggered together |
| `release_uuid` | UUID? | Release it ran from |
| `status` | Enum | `pending`, `syncing`, `querying`, `running`, `success`, `error`, `skipped`, `cancelling`, `cancelled` |
| `error_message` | String? | Why it failed |
| `runs_count` | Int32 | Runs the batch created |
| `executed_runs_count` | Int32 | Runs that actually executed |
| `failed_runs_count` | Int32 | Runs that failed |
| `runs_status` | Enum? | `healthy` or `unhealthy`, from the outcome of its runs |
| `credits_used_count` | Float32 | Credits the whole batch consumed |
| `created_at` | DateTime64 | When it was created |
| `updated_at` | DateTime64 | When it last changed |
| `finished_at` | DateTime64? | When it finished |
| `data` | Tuple | What triggered it (see below) |
| `temporal_workflow_id` | UUID | Internal: Temporal workflow instance |
| `stringified_nodes` | String? | Internal: JSON snapshot of the workflow's nodes |
`data.kind` names the trigger (`segment`, `change`, `filter`, `recordIds`, `records`, `file`, `runs`, `group`, `schedule`, `form` or `watchedRecords`), and the rest of the tuple carries the trigger's payload: `model_uuid`, `segment_uuid`, `change_uuid`, `change_kinds`, `ids`, `limit`, `webhook_url`, the `stringified_*` filter and sort, the `parent_*` and `group_*` links, and the record counts `total_records_count`, `added_records_count`, `updated_records_count`, `removed_records_count` and `unchanged_records_count`. Read a field with dot access: `data.kind`, `data.segment_uuid`.
## Example queries
Error rate over the last hour, as a percentage:
```sql theme={null}
select 100.0 * countIf(execution_status = 'error') / nullif(count(*), 0)
from spans
where execution_started_at > now() - interval 1 hour
```
`nullif` on the divisor keeps an hour without spans as a `NULL` rather than the
`0 / 0` a bare `count(*)` would give. As an alert scope that reads as an empty
window, so a quiet hour never reports a rate.
The slowest integrations yesterday:
```sql theme={null}
select
node_integration_slug,
count(*) as executions,
avg(dateDiff('millisecond', execution_started_at, execution_finished_at) / 1000) as avg_seconds
from spans
where execution_finished_at is not null
and execution_started_at > now() - interval 1 day
and node_integration_slug is not null
group by node_integration_slug
order by avg_seconds desc
```
Credits burned per tool this week:
```sql theme={null}
select node_tool_uuid, sum(execution_credits_used_count) as credits
from spans
where execution_started_at > now() - interval 7 day
and node_tool_uuid is not null
group by node_tool_uuid
order by credits desc
```
Batches that finished unhealthy today:
```sql theme={null}
select uuid, workflow_uuid, runs_count, failed_runs_count
from batches
where runs_status = 'unhealthy'
and created_at > now() - interval 1 day
order by failed_runs_count desc
```
The most common error messages, grouped:
```sql theme={null}
select execution_error_message, count(*) as occurrences
from spans
where execution_status = 'error'
and execution_started_at > now() - interval 1 day
group by execution_error_message
order by occurrences desc
```
A query used as an alert scope is **not** windowed for you: it runs exactly
as written on every tick. Keep the `interval` bound in the query itself, and
make it return a single numeric value: the alert reads the first column of the
first row.
## Next steps
Turn any of these queries into a scheduled threshold check that fires
actions when it breaches.
List runs, batches, spans and traces without writing SQL.