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. AReplacingMergeTreecan collapse updates and deletes only when all versions use the sameORDER BYkey. - 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 FINALWHERE _peerdb_is_deleted = 0PeerDB 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
SharedReplacingMergeTreewith 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
MergeTreeengines with theirShared*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.