Skip to content

ADR: Medallion tiers for ClickHouse event search

  • Status: Accepted
  • Date: 2026-08-30
  • Supersedes: Section 7 of the control-room event-search specification

Context

Control-room event search is moving to ClickHouse. Change Data Capture (CDC) copies events from MongoDB through ClickPipes in the cloud and self-hosted PeerDB in local development.

PeerDB writes each MongoDB document to a doc field. It also writes the source _id and several _peerdb_* bookkeeping fields. PeerDB does not decode or transform the document.

CDC correctness and search performance need different sort keys:

  • CDC needs the source _id. A ReplacingMergeTree can collapse updates and deletes only when all versions use the same ORDER BY key.
  • Event search needs (control_room_uuid, recorded_at, event_uuid) so related events are stored in query order.

One table cannot use both sort keys. The original event-search specification used the search key for the decoded CDC table, which would handle deletes incorrectly.

Decision

Use three medallion tiers: bronze, silver, and gold.

Tier Object Owner Engine and key Purpose
Bronze: raw log _peerdb_raw_mirror_<uuid> PeerDB Append-only MergeTree Stores every insert, update, and delete event.
Bronze: current raw document controlrooms_event PeerDB ReplacingMergeTree(_peerdb_version, _peerdb_is_deleted) ordered by _id Stores the untyped doc and PeerDB fields.
Silver: decoded event controlrooms_event_decoded Sky Ledge, through Flyway ReplacingMergeTree(_peerdb_version, _peerdb_is_deleted) ordered by _id Decodes UUIDs, timestamps, and searchable fields.
Gold: search projection controlrooms_event_gold Sky Ledge, through Flyway MergeTree ordered by (control_room_uuid, recorded_at, event_uuid) Provides the table used by event-search queries.

The gold table is populated by a refreshable materialized view. On each refresh, it reads the current, non-deleted rows from silver:

FROM controlrooms_event_decoded FINAL
WHERE _peerdb_is_deleted = 0

PeerDB creates a real bronze MergeTree log and an _id-ordered raw-document table. There is no bronze table using Engine = Null.

Key principles

PeerDB owns ingestion; Flyway owns transformation

PeerDB cannot transform documents. Bronze is therefore owned by PeerDB, while the silver decoding and gold search projection are maintained as Flyway migrations.

Silver remains ordered by _id

A MongoDB delete contains only the source key and an empty doc. If silver used the search key, the tombstone would decode without its room, date, or event UUID.

The tombstone would then land under a different sort key from the live row. ClickHouse could not collapse the two versions, so deleted events could continue to appear in searches.

Ordering silver by _id keeps every version of an event together. The gold tier applies the search-friendly ordering after silver has removed deleted and older versions.

Local and cloud document types differ

Self-hosted PeerDB v0.37.5 stores doc as a ClickHouse String. Cloud ClickPipes stores it as JSON. This is a PeerDB deployment difference, not a ClickHouse version difference.

Use ClickHouse JSONExtract* functions to decode the local String. Local development uses ClickHouse 25.8 to match the cloud and Testcontainers environments.

MongoDB UUIDs require legacy decoding

MongoDB stores these binary UUIDs using JAVA_LEGACY byte order. Decode them by reversing each eight-byte half, converting the result to hexadecimal, and formatting it as a UUID.

Ownership

  • PeerDB owns the raw CDC log and bronze raw-document table.
  • Sky Ledge owns the silver and gold definitions through Flyway.

Flyway may also create the empty bronze table before the mirror starts. Local testing with self-hosted PeerDB v0.37.5 confirmed that PeerDB adopts an empty pre-created destination table and adds its bookkeeping columns.

PeerDB rejects a pre-created destination when it contains data and the initial snapshot is enabled. It also rejects a destination that is a view.

Creating the bronze table through Flyway would keep its definition versioned and consistent between local and cloud environments. The equivalent behavior still needs confirmation with cloud ClickPipes.

Consequences

  • Card 1.3 must replace its single query-ordered SharedReplacingMergeTree with the silver decode and gold refreshable view described here.
  • Card 1.3 may also create the empty bronze table through Flyway.
  • Cards 1.4–1.6 and the 2.x query cards must read from gold.
  • ClickHouse Cloud replaces MergeTree engines with their Shared* equivalents, allowing the same Flyway migrations to run locally and in the cloud.

Trade-offs

The design handles deletes correctly and provides a query-ordered search table. It also keeps local and cloud environments on the same tier structure.

The cost is three data objects instead of one. Gold is also eventually consistent because it updates on the materialized view’s refresh interval.

Supersedes

This decision replaces Section 7 of the control-room event-search specification, which ordered the decoded CDC table by the search key.