Skip to main content
Version: 0.5

Schema Reference

ROSQL expects telemetry data in the OpenTelemetry schema conventions for ROS2. This document defines the complete schema: table definitions, span attributes, field mappings, and schema profiles.

If you're building a ROS2-to-OTel bridge or setting up your own telemetry storage, implement these conventions to get full ROSQL feature support.

The table names are the same across all backends. What differs is the physical layout — column naming conventions for SQL databases, or directory structure for the Parquet backend.

Span attributes​

Span attributes are key-value pairs stored in the span_attributes column of otel_traces. They carry ROS2-specific context — which node ran, which action was executing, which topic was published — and are what ROSQL's field names like action_name, topic, and node resolve to at query time. Getting these right is what makes distributed tracing across a ROS2 system actually queryable.

ROS2 action executions​

AttributeTypeExampleUsed by ROSQL for
ros.nodestring/bt_navigatorWHERE node = '...'
ros.action.namestring/navigate_to_poseWHERE action_name = '...'
ros.action.typestringnav2_msgs/action/NavigateToPoseAction type filtering
ros.action.goal_idstringUUIDGoal correlation
ros.action.statusstringsucceeded / aborted / canceledAction status filtering

ROS2 pub/sub tracing​

AttributeTypeExampleUsed by ROSQL for
ros.topicstring/cmd_velTopic filtering, MESSAGE FLOW
ros.message_typestringgeometry_msgs/msg/TwistMessage type filtering
ros.publisher_nodestring/controller_serverPublisher attribution
ros.subscriber_nodestring/motor_driverSubscriber attribution
ros.session.idstringsess_abc123FOR SESSION scoping

Universal scope resource attributes​

These resource attributes are set at the SDK/bridge level and enable FOR clause scoping across all query types.

AttributeTypeExampleUsed by ROSQL for
robot.idstringrobot_42FOR ROBOT scoping
service.versionstringv1.2.3FOR VERSION scoping
deployment.environmentstringproductionFOR ENVIRONMENT scoping
ros.session.idstringsess_abc123FOR SESSION scoping

ParentSpanId convention for TRACE​

The publish span's SpanId must be set as the ParentSpanId of the corresponding subscribe span. This creates the causal chain that TRACE traverses recursively via a CTE.

Publisher span (SpanId: "abc")
  └── Subscriber span (ParentSpanId: "abc", SpanId: "def")
        └── Downstream span (ParentSpanId: "def", SpanId: "ghi")

Implementing this correctly requires middleware-level instrumentation — it cannot be done purely at the application level without code changes to every node.

Metric naming conventions​

Metric nameUnitDescription
ros2.topic.message_rateHzTopic publish rate
ros2.topic.bandwidthB/sTopic bandwidth
ros2.topic.messages_received—Messages received by subscribers
ros2.topic.messages_captured—Messages captured (e.g. by recorder)
ros2.topic.messages_filtered—Messages filtered/dropped
ros2.action_servers.count—Active action server count
ros2.services.count—Active service count
ros2.action.queued_goals—Queued action goals
ros2.action.active_goals—Active action goals
ros2.action.completion_rateHzAction completion rate
system.cpu.utilization%Total CPU utilization
system.memory.utilization%Memory utilization
system.memory.usageBMemory usage in bytes
system.filesystem.utilization%Disk utilization
system.filesystem.usageBDisk usage in bytes
system.disk.ioB/sDisk I/O throughput
system.disk.operationsops/sDisk IOPS
system.network.ioB/sNetwork I/O throughput
system.network.packetspackets/sNetwork packet rate
system.network.latencymsNetwork latency
system.network.jittermsNetwork jitter
system.network.packet_loss%Packet loss rate
system.temperature°CSystem temperature
system.battery.charge%Battery state of charge
system.battery.voltageVBattery voltage
system.battery.currentABattery current draw
system.battery.temperature°CBattery temperature
process.cpu.utilization%Per-process CPU utilization
process.memory.usageBPer-process memory usage

Required tables​

These three tables are required for basic ROSQL functionality.

otel_traces​

Used by: FROM traces, TRACE, MESSAGE FLOW, DURING(), ACTION_SUCCESS_RATE, MOVING_AVG, DERIVATIVE, SHOW DEPLOYMENTS, SHOW SPAN SUMMARY, SHOW PLANS

CREATE TABLE otel_traces (
    timestamp            TIMESTAMPTZ NOT NULL,
    trace_id             TEXT NOT NULL,
    span_id              TEXT NOT NULL,
    parent_span_id       TEXT NOT NULL DEFAULT '',
    span_name        TEXT NOT NULL,
    span_kind            TEXT NOT NULL DEFAULT 'INTERNAL',
    service_name         TEXT NOT NULL DEFAULT '',
    duration             BIGINT NOT NULL,          -- nanoseconds
    status_code          TEXT NOT NULL DEFAULT 'OK',
    span_attributes      JSONB NOT NULL DEFAULT '{}',
    resource_attributes  JSONB NOT NULL DEFAULT '{}'
);

Use --schema otel-postgres (default for PostgreSQL and MySQL).

otel_logs​

Used by: FROM logs

CREATE TABLE otel_logs (
    timestamp            TIMESTAMPTZ NOT NULL,
    trace_id             TEXT NOT NULL DEFAULT '',
    span_id              TEXT NOT NULL DEFAULT '',
    severity_text        TEXT NOT NULL DEFAULT 'INFO',
    severity_number      INTEGER NOT NULL DEFAULT 9,
    service_name         TEXT NOT NULL DEFAULT '',
    body                 TEXT NOT NULL DEFAULT '',
    resource_attributes  JSONB NOT NULL DEFAULT '{}',
    log_attributes       JSONB NOT NULL DEFAULT '{}'
);

otel_metrics​

Used by: FROM metrics, TOPIC_RATE

CREATE TABLE otel_metrics (
    timestamp            TIMESTAMPTZ NOT NULL,
    metric_name          TEXT NOT NULL,
    value                DOUBLE PRECISION NOT NULL,
    attributes           JSONB NOT NULL DEFAULT '{}',
    service_name         TEXT NOT NULL DEFAULT ''
);

Optional tables​

These tables enable additional ROSQL features. If absent, ROSQL returns a clear DataSourceUnavailable error with guidance.

topic_messages​

Used by: FROM topics, FROM odom (and other topic aliases)

CREATE TABLE topic_messages (
    robot_id             TEXT NOT NULL,
    topic_name           TEXT NOT NULL,
    timestamp            TIMESTAMPTZ NOT NULL,
    fields               JSONB NOT NULL DEFAULT '{}',
    message_type         TEXT NOT NULL DEFAULT ''
);

mcap_metadata​

Used by: FROM recordings, FROM recordings WHERE topic = '...'

CREATE TABLE mcap_metadata (
    robot_id             TEXT NOT NULL,
    session_id           TEXT NOT NULL,
    start_time           TIMESTAMPTZ NOT NULL,
    end_time             TIMESTAMPTZ NOT NULL,
    file_uri             TEXT NOT NULL,
    topics               TEXT[] NOT NULL DEFAULT '{}',
    message_types        JSONB NOT NULL DEFAULT '{}'  -- topic → message_type map
);

robot_joint_map​

Stores URDF-derived joint metadata per robot model. Required for SHOW JOINTS.

CREATE TABLE robot_joint_map (
    robot_model          TEXT NOT NULL,
    valid_from           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    valid_to             TIMESTAMPTZ,                          -- NULL = currently active
    version              TEXT NOT NULL DEFAULT '',
    robot_ids            TEXT[] NOT NULL DEFAULT '{}',         -- robots using this model
    joint_map            JSONB NOT NULL DEFAULT '[]'           -- array of joint descriptors
);

joint_map element schema:

{
  "joint_name": "shoulder_pan_joint",
  "joint_index": 0,
  "joint_type": "revolute",
  "lower_limit": -3.14159,
  "upper_limit": 3.14159
}

Used by: SHOW JOINTS, JOINT DEVIATION

Additional tables (optional)​

These tables power the additional data sources. If absent, ROSQL returns a DataSourceUnavailable error. For DuckDB/Parquet, provide each as a .parquet file registered under the table name — the same pattern as robot_joint_map.

ROSQL sourceTableDescription
FROM tftf_statesROS2 TF2 coordinate frame transforms
FROM heartbeatsrobot_heartbeatsRobot liveness and status heartbeats
FROM system_logssystem_logsOS/kernel-level log events
FROM diagnosticsotel_metricsROS2 diagnostic messages (maps to the otel_metrics table)
FROM eventsros2_eventsROS2 lifecycle and deployment events

tf_states​

Stores ROS2 TF2 transform broadcasts — one row per transform stamped message.

CREATE TABLE tf_states (
    timestamp      TIMESTAMPTZ NOT NULL,
    robot_id       TEXT NOT NULL,
    parent_frame   TEXT NOT NULL,          -- e.g. 'map', 'odom'
    child_frame    TEXT NOT NULL,          -- e.g. 'base_link', 'camera_link'
    translation_x  DOUBLE PRECISION NOT NULL DEFAULT 0,
    translation_y  DOUBLE PRECISION NOT NULL DEFAULT 0,
    translation_z  DOUBLE PRECISION NOT NULL DEFAULT 0,
    rotation_x     DOUBLE PRECISION NOT NULL DEFAULT 0,  -- quaternion x
    rotation_y     DOUBLE PRECISION NOT NULL DEFAULT 0,
    rotation_z     DOUBLE PRECISION NOT NULL DEFAULT 0,
    rotation_w     DOUBLE PRECISION NOT NULL DEFAULT 1
);

robot_heartbeats​

Stores periodic liveness signals from each robot. Used by lifecycle anchors (SINCE last robot restart).

CREATE TABLE robot_heartbeats (
    timestamp          TIMESTAMPTZ NOT NULL,
    robot_id           TEXT NOT NULL,
    status             TEXT NOT NULL DEFAULT 'online',  -- 'online' | 'degraded' | 'offline'
    uptime_ns          BIGINT NOT NULL DEFAULT 0,       -- robot uptime in nanoseconds
    battery_percentage DOUBLE PRECISION,
    attributes         JSONB NOT NULL DEFAULT '{}'      -- platform-specific metadata
);

system_logs​

Stores OS/kernel-level log events alongside ROS2 data — CPU, memory, network, systemd events.

CREATE TABLE system_logs (
    timestamp        TIMESTAMPTZ NOT NULL,
    robot_id         TEXT NOT NULL,
    severity_text    TEXT NOT NULL DEFAULT 'INFO',
    severity_number  INTEGER NOT NULL DEFAULT 9,
    host             TEXT NOT NULL DEFAULT '',
    body             TEXT NOT NULL DEFAULT '',
    log_attributes   JSONB NOT NULL DEFAULT '{}'
);

ros2_events​

Stores deployment rollouts and ROS2 node lifecycle events. Powers SHOW DEPLOYMENTS and deployment-scoped lifecycle anchors.

CREATE TABLE ros2_events (
    timestamp   TIMESTAMPTZ NOT NULL,
    robot_id    TEXT NOT NULL,
    event_type  TEXT NOT NULL,          -- 'deployment' | 'node_started' | 'node_error' | ...
    node_name   TEXT NOT NULL DEFAULT '',
    version     TEXT NOT NULL DEFAULT '',
    payload     JSONB NOT NULL DEFAULT '{}'
);

ROSQL field mappings​

ROSQL field names are resolved to physical column names based on the active schema profile. Use --schema otel-postgres (default) or --schema otel-clickhouse.

The Parquet backend uses otel-postgres column names.

From otel_traces​

ROSQL fieldColumnStorage unit
trace_idtrace_id—
span_idspan_id—
parent_span_idparent_span_id—
span_namespan_name—
serviceservice_name—
durationdurationnanoseconds
statusstatus_codeOK / ERROR
nodespan_attributes->>'ros.node'—
action_namespan_attributes->>'ros.action.name'—
action_statusspan_attributes->>'ros.action.status'—
topicspan_attributes->>'ros.topic'—
robot_idresource_attributes->>'robot.id'—
org_idresource_attributes->>'organization.id'—
versionresource_attributes->>'service.version'—
environmentresource_attributes->>'deployment.environment'—
session_idresource_attributes->>'ros.session.id'—

From otel_logs​

ROSQL fieldColumn
messagebody
severityseverity_text
severity_numberseverity_number
robot_idresource_attributes->>'robot.id'
org_idresource_attributes->>'organization.id'

From otel_metrics​

ROSQL fieldResolved asStorage unit
metric_namemetric_name—
metric_valuevaluevaries
publish_ratevalue WHERE metric_name = 'ros2.topic.message_rate'Hz
bandwidthvalue WHERE metric_name = 'ros2.topic.bandwidth'B/s
messages_receivedvalue WHERE metric_name = 'ros2.topic.messages_received'—
messages_capturedvalue WHERE metric_name = 'ros2.topic.messages_captured'—
messages_filteredvalue WHERE metric_name = 'ros2.topic.messages_filtered'—
action_servers_countvalue WHERE metric_name = 'ros2.action_servers.count'—
services_countvalue WHERE metric_name = 'ros2.services.count'—
queued_goalsvalue WHERE metric_name = 'ros2.action.queued_goals'—
active_goalsvalue WHERE metric_name = 'ros2.action.active_goals'—
completion_ratevalue WHERE metric_name = 'ros2.action.completion_rate'Hz
cpu_usagevalue WHERE metric_name = 'system.cpu.utilization'%
memory_usagevalue WHERE metric_name = 'system.memory.utilization'%
memory_bytesvalue WHERE metric_name = 'system.memory.usage'B
disk_usagevalue WHERE metric_name = 'system.filesystem.utilization'%
disk_bytesvalue WHERE metric_name = 'system.filesystem.usage'B
disk_iovalue WHERE metric_name = 'system.disk.io'B/s
disk_iopsvalue WHERE metric_name = 'system.disk.operations'ops/s
network_iovalue WHERE metric_name = 'system.network.io'B/s
network_packetsvalue WHERE metric_name = 'system.network.packets'packets/s
network_latencyvalue WHERE metric_name = 'system.network.latency'ms
network_jittervalue WHERE metric_name = 'system.network.jitter'ms
packet_lossvalue WHERE metric_name = 'system.network.packet_loss'%
temperaturevalue WHERE metric_name = 'system.temperature'°C
battery_chargevalue WHERE metric_name = 'system.battery.charge'%
battery_voltagevalue WHERE metric_name = 'system.battery.voltage'V
battery_currentvalue WHERE metric_name = 'system.battery.current'A
battery_temperaturevalue WHERE metric_name = 'system.battery.temperature'°C
process_cpuvalue WHERE metric_name = 'process.cpu.utilization'%
process_memoryvalue WHERE metric_name = 'process.memory.usage'B
robot_idresource_attributes->>'robot.id'—
org_idresource_attributes->>'organization.id'—

From topic_messages (odom / position data)​

ROSQL geospatial operators (WITHIN) and JOINT DEVIATION extract nested values from the fields JSONB column using these conventions:

ROSQL pathfields JSON pathROS2 typeDescription
position.latitudefields->>'position.latitude'sensor_msgs/NavSatFixGPS latitude (degrees)
position.longitudefields->>'position.longitude'sensor_msgs/NavSatFixGPS longitude (degrees)
gps.latfields->>'position.latitude'sensor_msgs/NavSatFixGPS latitude alias
gps.lonfields->>'position.longitude'sensor_msgs/NavSatFixGPS longitude alias
pose.pose.position.xfields->>'pose.pose.position.x'nav_msgs/OdometryLocal frame X (metres)
pose.pose.position.yfields->>'pose.pose.position.y'nav_msgs/OdometryLocal frame Y (metres)
position.xfields->>'pose.pose.position.x'nav_msgs/OdometryLocal X alias
position.yfields->>'pose.pose.position.y'nav_msgs/OdometryLocal Y alias
orientation.yawcomputed from quaternionnav_msgs/OdometryYaw angle (radians)
position[N]fields->'position'->>Nsensor_msgs/JointStateJoint position at index N (radians)
velocity[N]fields->'velocity'->>Nsensor_msgs/JointStateJoint velocity at index N (rad/s)
effort[N]fields->'effort'->>Nsensor_msgs/JointStateJoint effort at index N (Nm)

orientation.yaw is derived from the Odometry quaternion via atan2(2*(qw·qz + qx·qy), 1 − 2*(qy² + qz²)).

For JointState fields, N is the zero-based array index. To access joints by name instead of index, use SHOW JOINTS FOR ROBOT '...' to inspect the robot's joint map, then use the named index. See Joint state array indexing.

Topic aliases​

ROSQL sourceResolves to
FROM odomFROM topics WHERE topic_name = '/odom'
FROM joint_statesFROM topics WHERE topic_name = '/joint_states'
FROM batteryFROM topics WHERE topic_name = '/battery_state'
FROM cmd_velFROM topics WHERE topic_name = '/cmd_vel'
FROM imuFROM topics WHERE topic_name = '/imu/data'

Error taxonomy​

ROSQL produces structured errors with actionable messages. No raw SQL or database error text is ever surfaced to the user.

Error typeWhen raisedWhat to do
ParseErrorSyntax or grammar error in the queryFix the query syntax. The error includes a suggestion field with a "did you mean?" hint.
UnitErrorInvalid unit or incompatible unit comparisonUse a compatible unit (e.g. 500 ms not 500 kg).
NotImplementedFeature parses correctly but is not yet implementedCheck the docs for the current feature set. A cookbook link is included in the error message.
ReservedSyntaxA reserved keyword is used (e.g. ALERT, DEFINE)These keywords are reserved for the Robot Ops platform, not the open-source ROSQL layer.
DataSourceUnavailableA required table is missing from the databaseVerify the table exists and telemetry is being ingested. The error names the exact missing table.
MutationRejectedINSERT, UPDATE, DELETE, or DROP attemptedROSQL is read-only.
CompilationErrorValid syntax but cannot be compiled (e.g. scope mismatch)Read the error message — it describes the specific constraint.
ExecutionErrorDatabase execution failedThe error names the data source and includes an actionable suggestion. Never leaks raw driver text.

Warnings (non-fatal)​

Some queries compile and execute successfully but include warnings alongside the result. Each warning has a code, message, and suggestion:

CodeRaised whenSuggestion
ANOMALY_NO_FACETANOMALY(...) is used without FACETAdd FACET robot_id or FACET action_name to compare like-for-like spans