LiDAR, video and sensor files managed alongside time-series in one client

[bluestreak01]

Summary

Unified management of scalar time-series and binary file data (LiDAR scans, camera images, audio, point clouds, model checkpoints) through the QuestDB client. The client routes files to object storage and scalar data to QuestDB, while QuestDB tracks file metadata and manages lifecycle. On egress, the client resolves file references in query results and fetches files from object storage transparently.

The application sends all data through one API. The client handles routing, storage, synchronization, and retrieval.

The Problem

Physical AI systems produce two fundamentally different kinds of data:

Type	Examples	Size per item	Rate	Natural storage
Scalar telemetry	Joint angles, temperatures, forces, voltages	Bytes	1 kHz+	Database
Dense binary	LiDAR point clouds, camera frames, audio, depth maps, radar returns	KB – MB	10–60 Hz	Object storage

Today these are managed separately. Teams build custom pipelines: ROS bags → scripts → S3 + metadata in Postgres. MCAP files → custom indexer → data lake + separate TSDB. This takes months and the result is fragile — file references break, retention isn't coordinated, temporal correlation requires glue code.

Architecture

INGESTION                                         EGRESS

Application                                       Consumer (ML pipeline, replay tool)
    │                                                  │
    ├── client.send("joints", ts, row)                 │
    ├── client.send("imu", ts, row)                    ▼
    ├── client.send_file("lidar", ts,             client.query(sql)
    │       scan_bytes, {format, frame_id})            │
    ├── client.send_file("camera_front", ts,           ├── scalar columns ◄── QuestDB
    │       jpeg_bytes, {resolution})                  ├── resolve object_path refs
    │                                                  └── fetch files ◄── object store
    ▼                                                       │
┌────────────────────────────────┐                     unified result:
│  Client                       │                     (ts, position, torque,
│                                │                      lidar_bytes, camera_bytes)
│  send() → mmap log ──► QuestDB│
│                                │
│  send_file():                  │
│    1. file → object store      │
│    2. metadata row → QuestDB   │
│       (ts, object_path, size,  │
│        content_type, attrs)    │
└────────────────────────────────┘

Ingestion: send_file()

Client API

# Scalar data — goes to QuestDB
client.send("joint_telemetry", ts, {
    "robot_id": "robot-7",
    "joint": "shoulder_left", 
    "position": 0.47,
    "velocity": 1.2,
    "torque": 3.8
})

# File data — goes to object store, metadata to QuestDB
client.send_file("lidar_scans", ts, scan_bytes, {
    "robot_id": "robot-7",
    "format": "pcd",
    "frame_id": "base_link",
    "num_points": 65536
})

What the client does for send_file()

1. Writes file to local staging (or directly to object store if connected)
2. Generates object path using deterministic convention:

   {bucket}/{table}/{robot_id}/{date}/{ts}_{hash}.{ext}
   

3. Uploads to object store (local directory on edge, S3/GCS/Azure remotely)
4. Writes metadata row to QuestDB:

   INSERT INTO lidar_scans (ts, robot_id, object_path, size_bytes, content_type, format, frame_id, num_points)
   VALUES (ts, 'robot-7', 's3://bucket/lidar_scans/robot-7/2026-01-15/...pcd', 262144, 'application/pcd', 'pcd', 'base_link', 65536)
   

The metadata table is a regular QuestDB table. Compound sort keys (#119), retention policies (#100), ASOF JOIN — everything applies to it.

File routing is explicit

The application calls send() for scalar data and send_file() for binary data. No content-type detection or size-based heuristics. The application knows what its data is.

Egress: file-aware queries

The gap in current egress (#108)

#108 (native SQL drivers) delivers high-performance binary protocol for scalar query results. But when a query result contains object_path columns referencing files in object storage, the consumer still has to:

1. Parse the paths from the result set
2. Open a separate connection to the object store
3. Download each file individually
4. Correlate files back to their result rows

The client should handle this transparently.

File-resolving query mode

# Regular query — returns object_path as strings
results = client.query("SELECT * FROM lidar_scans WHERE robot_id = 'robot-7'")
# results[0].object_path → "s3://bucket/lidar_scans/robot-7/..."

# File-resolving query — fetches files, returns bytes
results = client.query_with_files(
    "SELECT * FROM lidar_scans WHERE robot_id = 'robot-7'",
    resolve=["object_path"]  # columns to resolve
)
# results[0].object_path → bytes(...)  (actual file contents)

How it works

1. Client executes query against QuestDB via native protocol (QWP query egress: 200M rows/s into native drivers and Arrow #108)
2. Client identifies columns marked for resolution
3. Client batch-fetches files from object store (parallel, connection pooling)
4. Client returns unified result: scalar values + file bytes in one result set

Training data assembly

The most important use case — assembling multi-modal training datasets:

# One query produces a complete training dataset
results = client.query_with_files("""
    SELECT 
        j.ts, j.position, j.velocity, j.torque,
        l.object_path AS lidar_path,
        c.object_path AS camera_path
    FROM joint_telemetry j
    ASOF JOIN lidar_scans l ON (j.robot_id = l.robot_id)
    ASOF JOIN camera_front c ON (j.robot_id = c.robot_id)
    WHERE j.episode_id = 'ep_0047'
    ORDER BY j.ts
""", resolve=["lidar_path", "camera_path"])

# Each row has: scalar telemetry + aligned LiDAR bytes + aligned camera bytes
for row in results:
    training_sample = {
        "state": [row.position, row.velocity, row.torque],
        "lidar": decode_pointcloud(row.lidar_path),
        "image": decode_jpeg(row.camera_path),
    }

Without this, assembling multi-modal training data requires custom ETL pipelines, S3 listing scripts, and manual temporal alignment. With this, it's one SQL query.

Data Logistics

Edge to cloud file sync

On the edge device, files are written to local storage first (same store-and-forward pattern as #109 for scalar data):

Edge device:
  send_file() → local staging dir → async upload → remote object store
                                  → metadata row → local QuestDB → sync → remote QuestDB

Concern	Approach
Local staging	Files written to local directory before upload
Async upload	Background thread uploads to remote object store when connected
Retry on failure	Track upload state per file, retry from staging on reconnect
Priority	Table-level priority from metadata (#109) applies — safety camera frames before routine LiDAR bulk
Bandwidth awareness	Large files are chunked; upload pauses when bandwidth is needed for high-priority scalar data
Local cleanup	Delete local staging files after confirmed upload to remote

Object store abstraction

The client abstracts over storage backends:

Environment	"Object store" is	Config
Edge (disconnected)	Local directory	file:///data/objects/
Edge (connected)	Direct S3/GCS upload	s3://bucket/
Cloud	S3/GCS/Azure Blob	s3://bucket/
Development	Local directory	file:///tmp/questdb-objects/

Metadata-driven file organization

Object paths are deterministic and derived from table metadata:

s3://bucket/
  └── {table}/
      └── {partition_key}/        # e.g., robot_id
          └── {date}/
              └── {timestamp}_{content_hash}.{ext}

This structure enables:

• Object store lifecycle rules aligned with QuestDB retention
• Bulk operations by prefix (delete all files for a robot, a date, a table)
• External tools can browse the object store and understand the layout

Retention Cascading

When QuestDB drops metadata rows (via retention policies #100), the corresponding files in object storage must also be cleaned up:

-- Retention policy on metadata table
-- Dropping rows triggers file deletion from object store
ALTER TABLE lidar_scans SET STORAGE POLICY (
    DROP LOCAL 7d,     -- delete local files after 7 days
    DROP REMOTE 90d    -- delete from object store after 90 days
);

Event	Database action	Object store action
DROP LOCAL TTL expires	Drop partition from local QuestDB	Delete files from local staging
DROP REMOTE TTL expires	Drop metadata rows	Delete files from object store
Table dropped	Drop all metadata	Delete all files under table prefix
Manual DELETE	Remove metadata rows	Queue file deletion

The database is the single source of truth for lifecycle. No orphaned files, no dangling references.

Use Cases

• Robotics training data — Assemble multi-modal datasets (telemetry + LiDAR + camera) with one SQL query. ASOF JOIN aligns modalities temporally. Episode filtering via compound sort keys (Multi-series tables: cluster by symbol for fast per-series queries and better compression #119).
• Incident replay — Query all data around a failure event: scalar telemetry from QuestDB, camera footage and LiDAR scans from object storage, temporally aligned.
• Fleet-wide model evaluation — "Get all camera frames where the vision model confidence was below 0.5" — filter on scalar metrics, retrieve associated files.
• Autonomous vehicle data management — Petabytes of camera, LiDAR, radar data with metadata in QuestDB. Query by route, time, conditions. Download matching data for retraining.
• Industrial inspection — Thermal images, acoustic recordings, vibration waveforms with timestamps and equipment IDs. Query anomalous readings, retrieve associated scans.
• Defense / aerospace — Sensor fusion data from multiple modalities with coordinated lifecycle and access control.

Related

• Store-and-forward client for edge and robotics deployments #109 — Store-and-forward client (scalar data logistics; this issue extends the client with file routing)
• QWP query egress: 200M rows/s into native drivers and Arrow #108 — High-performance egress with native SQL drivers (scalar egress; file-resolving queries extend this)
• Cold storage on S3, GCS and Azure with targeted range reads #106 — Parquet file transfer to/from object stores (shared object store infrastructure)
• Storage policies for automated data lifecycle management #100 — Storage policies (retention cascading for files)
• Multi-series tables: cluster by symbol for fast per-series queries and better compression #119 — Compound sort keys (episode-based organization of file metadata)
• QWP: 5x faster ingestion over WebSocket and UDP #105 — Binary ingestion protocol (wire format for metadata rows)