Version: 0.5

Command Reference

Complete reference for every ROSQL statement and clause.

Data source statements

`FROM <source>`

Select a data source. The primary entry point for most queries.

FROM traces
FROM logs
FROM metrics
FROM topics
FROM recordings

Sources:

traces — otel_traces table
logs — otel_logs table
metrics — otel_metrics table
topics — topic_messages table
recordings — mcap_metadata table
odom, joint_states, battery, cmd_vel, imu — topic aliases

`SELECT <fields> FROM <source>`

Select specific fields.

SELECT trace_id, span_name, duration FROM traces WHERE status = 'ERROR'
SELECT AVG(duration) AS avg_dur, COUNT(*) AS total FROM traces
SELECT PERCENTILE(duration, 95) FROM traces WHERE action_name = '/navigate_to_pose'

Aggregate functions: AVG, COUNT, MAX, MIN, SUM, PERCENTILE, TOPIC_RATE, ACTION_SUCCESS_RATE, MOVING_AVG, DERIVATIVE, APPROX_COUNT_DISTINCT, APPROX_PERCENTILE

See Aggregate functions in the Syntax reference for syntax and examples.

Filtering

`WHERE <condition>`

Filter rows. Supports standard comparison operators, AND, OR, IN, field access.

FROM traces WHERE status = 'ERROR'
FROM traces WHERE duration > 500 ms AND status = 'ERROR'
FROM logs WHERE severity IN ('ERROR', 'WARN')
FROM topics WHERE topic_name = '/battery_state' AND fields['percentage'] < 20

Unit values (e.g. 500 ms, 2 km, 45 deg) are automatically converted to SI at parse time — see Physical units.

JSON field access: fields['key'] on topic_messages.fields (JSONB)

Array-indexed field access: fields['array[N]'] accesses element N of a JSON array field. Example: fields['position[0]'] → "fields"->'position'->>0 (PostgreSQL/DuckDB).

`WHERE position WITHIN <radius> OF <center>`

Geospatial filter. Returns rows where the position field falls within radius of center.

-- GPS coordinates (Haversine great-circle distance)
FROM odom WHERE position WITHIN 500 m OF (37.7749, -122.4194) SINCE 1 hour ago

-- Local frame (Euclidean distance, metres)
FROM odom WHERE position WITHIN 2 m OF POSITION (1.5, 3.0) SINCE 1 hour ago

GPS form OF (lat, lon): uses inline Haversine SQL; extracts position.latitude and position.longitude from the fields JSONB column.
Local frame OF POSITION (x, y): uses Euclidean SQRT(POWER(x - cx, 2) + POWER(y - cy, 2)); extracts pose.pose.position.x and pose.pose.position.y from fields.

Requires the topic_messages table with position data in fields.

`SINCE <time>`

Filter by timestamp (relative or absolute).

FROM traces WHERE status = 'ERROR' SINCE 1 hour ago
FROM traces SINCE 30 minutes ago
FROM traces SINCE yesterday
FROM traces SINCE last week
FROM traces SINCE '2025-03-25T00:00:00Z'

Relative tokens: X minutes ago, X hours ago, X days ago, yesterday, last week, last month

`LIMIT <n>`

Limit result rows.

FROM traces LIMIT 10

Default limit: 100. Not applied for scalar aggregations, FACET queries, TRACE, MESSAGE FLOW, or PATH.

`OFFSET <n>`

Skip result rows before returning. Use with LIMIT for pagination.

FROM logs LIMIT 20 OFFSET 40
FROM logs OFFSET 10
FROM logs | LIMIT 10 | OFFSET 20

`ORDER BY <field> [DESC|ASC]`

Sort results.

SELECT span_name, duration FROM traces ORDER BY duration DESC LIMIT 20

Scoping

FOR clauses are composable — any combination can be prepended to any query type (standard, pipeline, compound). Multiple FOR clauses are ANDed together.

`FOR ROBOT <robot_id>`

Scope a query to a specific robot.

FOR ROBOT 'robot_sim_001' FROM logs WHERE severity = 'ERROR'
FOR ROBOT 'robot_42' FROM logs SINCE 30 minutes ago

Filters by the robot_id resource attribute.

`FOR FLEET`

Scope a query to the entire fleet (no robot filter). Useful when composing with other FOR clauses.

FOR FLEET FROM traces WHERE status = 'ERROR' SINCE 1 hour ago

`FOR VERSION <semver>`

Scope a query to a specific service version, filtering by the service.version resource attribute.

FOR VERSION 'v1.2.3' FROM traces WHERE status = 'ERROR'
FOR ROBOT 'robot_42' FOR VERSION 'v1.0.0' FROM logs SINCE 1 hour ago

`FOR ENVIRONMENT <env>`

Scope a query to a deployment environment, filtering by the deployment.environment resource attribute.

FOR ENVIRONMENT 'production' FROM traces WHERE status = 'ERROR'
FOR FLEET FOR ENVIRONMENT 'staging' FROM metrics SINCE 30 minutes ago

`FOR SESSION <session_id>`

Scope a query to a specific ROS session, filtering by the ros.session.id resource attribute.

FOR SESSION 'sess_abc123' FROM logs WHERE severity = 'ERROR'

Composing FOR clauses

All FOR clauses can be combined in any order. Each clause adds an additional filter:

FOR ROBOT 'r1' FOR VERSION 'v1.0' FOR ENVIRONMENT 'prod' FROM traces WHERE status = 'ERROR'
FOR FLEET FOR VERSION 'v2.0' FOR ENVIRONMENT 'production' FROM traces | FACET robot_id

Works on all query types: standard, pipeline, and compound.

Cross-signal correlation

`DURING(<subquery>)`

Correlate one data source with a time window defined by another. The outer query is filtered to rows whose timestamp falls within the time windows where the subquery returned results.

FROM traces WHERE status = 'ERROR'
DURING(
  FROM topics WHERE topic_name = '/battery_state'
  AND fields['percentage'] < 15
)
SINCE 6 hours ago

The subquery can use any data source. This is ROSQL's most powerful feature — it replaces complex multi-table JOINs with a declarative, readable correlation.

Message causality

`TRACE <trace_id>`

Return all spans belonging to a specific trace, walking the parent_span_id → span_id tree recursively via a CTE.

TRACE 'a3f1c9d2e8b04f7a'
TRACE 'trace-002'

Returns all columns from otel_traces for every span in the causal chain, in traversal order.

`MESSAGE FLOW FROM TOPIC <topic> [TO NODE|TOPIC <dest>]`

Find message flow paths (publish → subscribe edges) from a topic, with optional destination filtering.

-- All paths from a topic
MESSAGE FLOW FROM TOPIC '/cmd_vel'

-- Filter to a specific destination node
MESSAGE FLOW FROM TOPIC '/cmd_vel' TO NODE '/motor_driver'

-- Filter to a specific destination topic
MESSAGE FLOW FROM TOPIC '/sensor_data' TO TOPIC '/processed_data'

Returns publisher/subscriber node pairs for the specified path.

Health and anomaly

`ANOMALY(<field>) COMPARED TO <baseline>`

Detect statistical anomalies in a field using a two-phase z-score CTE. COMPARED TO is required. FACET is strongly recommended to get per-robot breakdown.

-- Compare current duration anomalies to last week, by robot
ANOMALY(duration) COMPARED TO last week FACET robot_id SINCE 7 days ago

-- Compare to fleet-wide baseline
ANOMALY(duration) COMPARED TO fleet SINCE 1 hour ago FACET robot_id

-- Scope to a specific data source
ANOMALY(duration) FROM traces COMPARED TO last 24 hours FACET robot_id SINCE 12 hours ago

Baselines: last week, last 24 hours, fleet

Returns robot_id, avg_current, avg_baseline, z_score, is_anomalous (|z| > 2), direction (higher/lower/normal).

Requires the robot_joint_map and otel_traces tables.

`PATH DEVIATION`

Compare actual robot trajectory (from /odom topic messages) against the planned path (from /plan topic messages). Computes lateral deviation in metres at each waypoint.

-- Deviation for a specific trace
PATH DEVIATION FOR TRACE 'trace-002'

-- Deviation for a robot over a time window
PATH DEVIATION FOR ROBOT 'robot_sim_001' SINCE 1 hour ago

-- Select a specific plan (0 = first, -1 = latest, default = -1)
PATH DEVIATION PLAN 0 FOR ROBOT 'robot_sim_001' SINCE 6 hours ago
PATH DEVIATION PLAN -1 FOR TRACE 'trace-002'

Returns waypoint_index, planned_x, planned_y, actual_x, actual_y, lateral_deviation_m, and summary stats (max_deviation_m, avg_deviation_m, planned_path_length_m, actual_path_length_m).

Requires the topic_messages table with /odom and /plan data.

`JOINT DEVIATION`

Compare planned joint trajectory (from /joint_trajectory topic messages) against actual joint states (from /joint_states topic messages). Computes per-joint position error in radians.

-- Joint deviation for a specific trace
JOINT DEVIATION FOR TRACE 'trace-002'

-- Joint deviation for a robot over a time window
JOINT DEVIATION FOR ROBOT 'robot_sim_001' SINCE 1 hour ago

Returns joint_name, joint_index, planned_position, actual_position, position_error_rad, and summary stats (max_error_rad, avg_error_rad, sample_count).

Requires the topic_messages table with /joint_trajectory and /joint_states data.

Recordings

`FROM recordings`

Queries the mcap_metadata index table — one row per MCAP file. Returns file-level metadata: which robot, which time window, which topics, and where the file lives (file_uri — an s3://, file://, or gs:// URI). It does not slice into the MCAP content; use file_uri along with the returned start_time/end_time to fetch and seek into the file client-side.

-- Browse all recordings
FROM recordings LIMIT 10

-- Filter by robot
FROM recordings WHERE robot_id = 'amr-01' LIMIT 10

-- Find recordings covering a time window (use start_time / end_time explicitly)
FROM recordings
WHERE end_time >= NOW() - INTERVAL '2 hours'
  AND start_time <= NOW()

-- Find recordings that contain a specific topic
FROM recordings WHERE '/camera/image_raw' = ANY(topics)

-- Combine filters
FROM recordings
WHERE robot_id = 'amr-01'
  AND end_time >= NOW() - INTERVAL '6 hours'
  AND '/joint_states' = ANY(topics)
LIMIT 5

Returns robot_id, session_id, start_time, end_time, file_uri, topics, message_types from mcap_metadata.

SHOW RECORDING

SHOW RECORDING is reserved but deprecated syntax — the compiler rejects it. Use FROM recordings instead.

SHOW commands

`SHOW DEPLOYMENTS`

Return distinct deployment history (version × environment combinations) from otel_traces resource attributes. Supports optional FOR and SINCE scoping.

SHOW DEPLOYMENTS
SHOW DEPLOYMENTS FOR ROBOT 'robot_42'
SHOW DEPLOYMENTS FOR ROBOT 'robot_42' SINCE 7 days ago
SHOW DEPLOYMENTS FOR ENVIRONMENT 'production' SINCE last week

Returns distinct service.version × deployment.environment pairs with first/last seen timestamps.

`SHOW SPAN SUMMARY`

Return top spans ranked by average duration. Useful for identifying performance hotspots. Supports optional FOR and SINCE scoping.

SHOW SPAN SUMMARY
SHOW SPAN SUMMARY FOR ROBOT 'robot_42'
SHOW SPAN SUMMARY FOR ROBOT 'robot_42' SINCE 1 hour ago
SHOW SPAN SUMMARY FOR VERSION 'v1.2.3' SINCE 30 minutes ago

Returns span name, call count, average duration, and max duration, ordered by average duration descending.

`SHOW PLANS`

Return plan-related spans (spans with planning metadata in their attributes). Supports optional FOR TRACE, FOR ROBOT, and SINCE scoping.

SHOW PLANS
SHOW PLANS FOR TRACE 'a3f1c9d2e8b04f7a'
SHOW PLANS FOR ROBOT 'robot_42'
SHOW PLANS FOR ROBOT 'robot_42' SINCE 1 hour ago

Returns plan spans with timing, robot, and trace context.

`SHOW JOINTS`

Return the joint map for a robot from the robot_joint_map table. Queries the URDF-derived joint metadata registered for that robot model.

SHOW JOINTS FOR ROBOT 'robot_sim_001'
SHOW JOINTS FOR ROBOT 'arm_01'

Returns joint_name, joint_index, joint_type, lower_limit, upper_limit for every joint in the robot's URDF model.

Requires the optional robot_joint_map table.

Pipeline syntax

See Pipeline syntax in the Syntax reference for the full grammar and equivalence rules.

Grouping and comparison

`FACET <field>`

Group results by a field.

SELECT AVG(duration) FROM traces FACET robot_id
FROM traces WHERE status = 'ERROR' | FACET robot_id

`COMPARE TO <time>`

Compare current results to a historical baseline.

FROM traces WHERE status = 'ERROR' SINCE 1 hour ago | COMPARE TO last week

Returns current and baseline counts side by side.

`COMPARE TO VERSION <semver>`

Compare current results to a specific version baseline.

FROM traces COMPARE TO VERSION 'v1.2.3'
FROM traces WHERE status = 'ERROR' SINCE 1 hour ago | COMPARE TO VERSION 'v1.0.0'

Scopes the baseline query to spans from the given service.version resource attribute.

`COMPARE VERSION <v1> TO VERSION <v2>`

Compare two versions head-to-head without a time-based baseline.

FROM traces COMPARE VERSION 'v1.0' TO VERSION 'v2.0'
FROM traces WHERE action_name = '/navigate_to_pose' COMPARE VERSION 'v1.0' TO VERSION 'v2.0'

Returns results for each version side by side.

Aggregate function reference

See Aggregate functions in the Syntax reference.

Output format hints

Every ROSQL response includes a format_hint field in its metadata. This tells frontends how to best visualize the result — the REPL and dashboard use it to automatically choose a chart type.

Inferred format hints

ROSQL infers the appropriate hint from the query shape:

Query pattern	`format_hint`	Use for
`TIMESERIES` without `FACET`	`LineChart`	Single time series
`TIMESERIES` with `FACET`	`StackedLineChart`	Multiple series, split by dimension
`FACET` without `TIMESERIES`	`BarChart`	Categorical aggregations
`TRACE 'id'`	`Gantt`	Span waterfall chart
`MESSAGE FLOW`	`DirectedGraph`	Pub/sub topology
`SHOW NODE GRAPH`	`NodeGraph`	Node connectivity map
`SHOW SPAN SUMMARY`	`HorizontalBars`	Ranked span durations
`ANOMALY(...)`	`Table` (with `color_field: is_anomalous`)	Highlighted anomaly rows
Scalar aggregation only	`ScalarCards`	KPI metric cards
`FROM logs` / `FROM system_logs`	`LogTable` (with `color_field: severity`)	Severity-colored log viewer
`FROM recordings`	`RecordingList`	Recording file list
Everything else	`Table`	Generic tabular display

Visualization config

Alongside format_hint, the response metadata includes a visualization object with rendering hints:

Field	Description
`x_axis`	Field to place on the x-axis (e.g. `"time_bucket"` for timeseries)
`y_axis`	Field to place on the y-axis (e.g. the primary aggregated metric alias)
`series_key`	Field to split series by (populated from `FACET` dimension)
`color_field`	Field to drive color encoding (e.g. `"severity"`, `"is_anomalous"`)
`label_field`	Field to use as a display label (e.g. the scalar alias)

FORMAT clause (explicit override)

Append FORMAT <value> to any query to override the inferred hint:

FROM traces TIMESERIES 5 min SINCE 6 hours ago FORMAT table
SELECT COUNT(*) FROM traces FACET robot_id FORMAT table

Valid values: table, timeseries, scalar, trace_tree, graph, path.

Error types

ROSQL produces structured errors — no raw SQL or database error text is ever surfaced. Each error type tells you exactly what to do differently:

Error type	When raised	Example
`ParseError`	Syntax error in the query	`Unexpected token 'WHER' at line 1, col 22. Did you mean 'WHERE'?`
`NotImplemented`	Feature parses but is not yet implemented	`HEALTH() is not yet implemented. See cookbook: rosql.org/docs/cookbook`
`ReservedSyntax`	Reserved keyword used (ALERT, DEFINE)	`ALERT is reserved but not supported. Alerts belong in the Robot Ops platform.`
`DataSourceUnavailable`	Required table is missing	`PATH DEVIATION requires topic_messages table. Configure topic ingest.`
`CompilationError`	Valid syntax but cannot compile	`Named joint access requires FOR ROBOT scope to resolve joint names.`
`ExecutionError`	Database returned an error	`Execution failed on PostgreSQL: Table not found. Verify that telemetry data has been ingested.`
`ValidationWarning`	Non-fatal advisory (in `warnings` array)	`ANOMALY without FACET compares heterogeneous spans. Add FACET action_name for meaningful z-scores.`

ValidationWarning is not thrown as an error — it is included in the warnings array alongside a successful result, with a code, message, and suggestion field.

Notes

All timestamps in results are UTC ISO 8601
Default LIMIT is 100 if not specified. Exempt query types: scalar aggregations, FACET, TRACE, MESSAGE FLOW, PATH DEVIATION, JOINT DEVIATION, ANOMALY, SHOW JOINTS
FOR clauses filter on resource attributes: FOR ROBOT → robot_id, FOR VERSION → service.version, FOR ENVIRONMENT → deployment.environment, FOR SESSION → ros.session.id

Data source statements​

FROM <source>​

SELECT <fields> FROM <source>​

Filtering​

WHERE <condition>​

WHERE position WITHIN <radius> OF <center>​

SINCE <time>​

LIMIT <n>​

OFFSET <n>​

ORDER BY <field> [DESC|ASC]​

Scoping​

FOR ROBOT <robot_id>​

FOR FLEET​

FOR VERSION <semver>​

FOR ENVIRONMENT <env>​

FOR SESSION <session_id>​

Composing FOR clauses​

Cross-signal correlation​

DURING(<subquery>)​

Message causality​

TRACE <trace_id>​

MESSAGE FLOW FROM TOPIC <topic> [TO NODE|TOPIC <dest>]​

Health and anomaly​

ANOMALY(<field>) COMPARED TO <baseline>​

PATH DEVIATION​

JOINT DEVIATION​

Recordings​

FROM recordings​

SHOW commands​

SHOW DEPLOYMENTS​

SHOW SPAN SUMMARY​

SHOW PLANS​

SHOW JOINTS​

Pipeline syntax​

Grouping and comparison​

FACET <field>​

COMPARE TO <time>​

COMPARE TO VERSION <semver>​

COMPARE VERSION <v1> TO VERSION <v2>​

Aggregate function reference​

Output format hints​

Inferred format hints​

Visualization config​

FORMAT clause (explicit override)​

Error types​

Notes​