Skip to main content

Store: ClickHouse

Transitive integrates ClickHouse, a highly scalable and extremely fast OLAP database. CLickHouse is used to store historic MQTTSync data and to provide capabilities with a designated databse for storing their own data.

Historic MQTTSync Data​

Capabilities can register topics in their own MQTTSync namespace that they would like to have stored in the database. Such requests specify a time-to-live (TTL) after which the records will automatically get deleted.

For instance, the Health Monitoring capability registers all /diagnostics/# topics with a 3-day TTl. This means that all diagnostic data published by the robot is timestamped each time it changes and stored in ClickHouse for three days. This is what is used to visualize the last 24 hours of each diagnostic sensor value in the UI using Sparkline inline-plots, and its the data that drives the Grafana visualization the user sees when clicking these plots.

Here is some example data from the ClickHouse table where this history is stored.

TimestampTopicPartsPayload
2026-03-30 00:00:06.668000['cfritz','d_f5b1b62bd4','@transitive-robotics','health-monitoring','0.10.1','diagnostics','temperature/nct6798-isa-0290','values','fan1']"755"
2026-03-30 00:00:06.668000['cfritz','d_f5b1b62bd4','@transitive-robotics','health-monitoring','0.10.1','diagnostics','temperature/nct6798-isa-0290','values','in2']"3.392"
2026-03-30 00:00:06.669000['cfritz','d_f5b1b62bd4','@transitive-robotics','health-monitoring','0.10.1','diagnostics','NVIDIA GeForce RTX 5070 Ti - 0','values','memory_free']"13981"
2026-03-30 00:00:06.669000['cfritz','d_f5b1b62bd4','@transitive-robotics','health-monitoring','0.10.1','diagnostics','temperature/k10temp-pci-00c3','values','Tccd1']"33.5"

As you can see the schema is very simple:

  • Timestamp: the time stamp of when the data was recorded in the database
  • TopicParts: an array of the parts of the MQTT topic being recorded, and
  • Payload: the payload (value) of the topic at the time of recording.

As in MQTT, the MQTTSync payload is JSON-encoded. This makes it possible to retrieve the original type back using JSON.parse or similar. In the example above all payloads are numbers, but they could equally have been strings, Booleans, arrays, or JSON objects.

Capability Specific Data​

In addition to the general, reusable mechanisms for registering and querying historic MQTT data, each cloud capability also gets its own designated ClickHouse database where it has full control of what it reads and writes.

Accessing ClickHouse Directly​

As a user, you have full read-access to all your data, even programmatically via ClickHouse clients or HTTP. You can find the URL and your credentials in your Security page in the Portal. Opening the link in the browser will take you to a simple, built-in query dashboard where you can enter and run SQL commands against your data to see it in action. For instance, try

SELECT * from mqtt_history
WHERE
  TopicParts[4] = '_robot-agent' AND
  TopicParts[7] = 'heartbeat'
ORDER BY Timestamp DESC
LIMIT 10

and you will get the last ten heartbeats received from your robots.

Access Control​

With both of these databases, the shared MQTT history table and the capability-specific databases, all access is controlled in the same way data access is defined and ensured elsewhere in the Transitive: by namespace.

  • Users: The above mentioned direct-access credentials give users access to all rows in all tables where the user ID matches theirs (e.g., above cfritz).
  • Capabilities: Capabilities have access to all users but only for their own namespace, i.e., a capability @SCOPE/NAME only sees rows there the 3rd and 4th field of the TopicParts array equal SCOPE and NAME, respectively.
  • JWT: As we will see in the section on Grafana, there are applications where we want to grant very limited access in other ways. For this purpose we, again, use JSON Web Tokens (JWTs) that specify a user, device, capability and time limit, signed with the JWT secret of the user in question. This is used, e.g., for creating time-limited links to Grafana visualizations, like the ones used by Health Monitoring.

These controls are based on ClickHouse's row policy access-rights mechanism, i.e., they are enforced directly at the database level for maximal security.