---
title: Schema Reference
description: Complete OTel schema reference for ROSQL — table definitions, field mappings, span attributes, and profiles
sidebar_position: 7
keywords: [rosql, schema, opentelemetry, otel, ros2, tables, postgresql, ddl, span attributes]
---

# 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.

---

## Span attributes

### ROS2 action executions

| Attribute | Type | Example | Used by ROSQL for |
|-----------|------|---------|-------------------|
| `ros.node` | string | `/bt_navigator` | `WHERE node = '...'` |
| `ros.action.name` | string | `/navigate_to_pose` | `WHERE action_name = '...'` |
| `ros.action.type` | string | `nav2_msgs/action/NavigateToPose` | Action type filtering |
| `ros.action.goal_id` | string | UUID | Goal correlation |
| `ros.action.status` | string | `succeeded` / `aborted` / `canceled` | Action status filtering |

### ROS2 pub/sub tracing

| Attribute | Type | Example | Used by ROSQL for |
|-----------|------|---------|-------------------|
| `ros.topic` | string | `/cmd_vel` | Topic filtering, `MESSAGE PATHS` |
| `ros.message_type` | string | `geometry_msgs/msg/Twist` | Message type filtering |
| `ros.publisher_node` | string | `/controller_server` | Publisher attribution |
| `ros.subscriber_node` | string | `/motor_driver` | Subscriber attribution |

### ParentSpanId convention for MESSAGE JOURNEY

The publish span's `SpanId` must be set as the `ParentSpanId` of the corresponding subscribe span. This creates the causal chain that `MESSAGE JOURNEY` traverses.

```
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 name | Unit | Description |
|-------------|------|-------------|
| `ros2.topic.rx_rate_hz` | Hz | Topic receive rate |
| `ros2.topic.rx_bandwidth_bps` | B/s | Topic bandwidth |
| `ros2.topic.last_message_age_ms` | ms | Age of last received message |
| `system.cpu.total_usage_pct` | % | Total CPU usage |
| `system.memory.usage_pct` | % | Memory usage |

---

## Required tables

These three tables are required for basic ROSQL functionality.

### `otel_traces`

```sql
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_col        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 '{}'
);
```

Used by: `FROM traces`, `MESSAGE JOURNEY`, `MESSAGE PATHS`, `TRACE`, `HEALTH()`, `ANOMALY()`, `DURING()`, `CORRELATE`, `SINCE last action failure`

### `otel_logs`

```sql
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 '{}'
);
```

Used by: `FROM logs`, `HEALTH()`

### `otel_metrics`

```sql
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 ''
);
```

Used by: `FROM metrics`, `HEALTH()`, `CORRELATE`

---

## Optional tables

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

### `topic_messages`

```sql
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 ''
);
```

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

### `mcap_metadata`

```sql
CREATE TABLE mcap_metadata (
    robot_id             TEXT NOT NULL,
    session_id           TEXT NOT NULL,
    start_time           TIMESTAMPTZ NOT NULL,
    end_time             TIMESTAMPTZ NOT NULL,
    s3_key               TEXT NOT NULL,
    topics               TEXT[] NOT NULL DEFAULT '{}'
);
```

Used by: `FROM recordings`, `SHOW RECORDING`

---

## ROSQL field mappings

### From `otel_traces`

| ROSQL field | Column | Storage unit |
|-------------|--------|-------------|
| `trace_id` | `trace_id` | — |
| `span_id` | `span_id` | — |
| `parent_span_id` | `parent_span_id` | — |
| `span_name` | `span_name_col` | — |
| `service` | `service_name` | — |
| `duration` | `duration` | nanoseconds |
| `status` | `status_code` | OK / ERROR |
| `node` | `span_attributes->>'ros.node'` | — |
| `action_name` | `span_attributes->>'ros.action.name'` | — |
| `action_status` | `span_attributes->>'ros.action.status'` | — |
| `topic` | `span_attributes->>'ros.topic'` | — |

### From `otel_metrics`

| ROSQL field | Resolved as | Storage unit |
|-------------|------------|-------------|
| `metric_name` | `metric_name` | — |
| `metric_value` | `value` | varies |
| `publish_rate` | `value` WHERE `metric_name = 'ros2.topic.rx_rate_hz'` | Hz |
| `bandwidth` | `value` WHERE `metric_name = 'ros2.topic.rx_bandwidth_bps'` | B/s |
| `cpu_usage` | `value` WHERE `metric_name = 'system.cpu.total_usage_pct'` | % |
| `memory_usage` | `value` WHERE `metric_name = 'system.memory.usage_pct'` | % |

### From `otel_logs`

| ROSQL field | Column |
|-------------|--------|
| `message` | `body` |
| `severity` | `severity_text` |
| `severity_number` | `severity_number` |

---

## Schema profiles

| Profile | Convention | Example | Default for |
|---------|-----------|---------|-------------|
| `otel-postgres` | Lowercase | `trace_id`, `status_code` | PostgreSQL, MySQL |
| `otel-clickhouse` | PascalCase | `TraceId`, `StatusCode` | ClickHouse |

```bash
rosql compile "FROM traces WHERE status = 'ERROR'" \
  --backend postgres \
  --schema otel-postgres
```

Custom schema profiles: tracked in [#22](https://github.com/RobotOpsInc/rosql/issues/22).

---

## Topic aliases

| ROSQL source | Resolves to |
|-------------|------------|
| `FROM odom` | `FROM topics WHERE topic_name = '/odom'` |
| `FROM joint_states` | `FROM topics WHERE topic_name = '/joint_states'` |
| `FROM battery` | `FROM topics WHERE topic_name = '/battery_state'` |
| `FROM cmd_vel` | `FROM topics WHERE topic_name = '/cmd_vel'` |
| `FROM imu` | `FROM topics WHERE topic_name = '/imu/data'` |
