v2 · Store & retention

In plain words

Everything lands in one ClickHouse, in a handful of tables. The table's base shape belongs to the writer: the OTel exporter inserts a fixed set of columns, with all custom fields (duration, status, identity) inside two key-value maps. Our tenant and metric fields are computed columns — ClickHouse fills them at insert by reaching into those maps — so every table still sorts customer-first and reads stay cheap. This is the same pattern SigNoz and ClickStack use.

How long data lives is the customer's plan, which Starbase already knows — Hobby 7 days, Pro 30, Enterprise 90. Starbase keeps a small project → days table here; each row looks up its own lifetime at insert, and ClickHouse expires it on that timer. One source of truth, no cron, and it works the same for logs, metrics, and request rows.

How to build it

1 · The retention mapping — create this first. The tables below reference it at insert, and a table whose computed column calls dictGet fails to attach if the dictionary is missing — so in bring-up order this precedes the tables. Starbase upserts (project_id, days) whenever a plan changes; sourcing the dictionary from a local table means the store never depends on Starbase being up. (In regions without DO peering, the upsert rides the Mass Relay channel, §39.3 #43.)

Retention source + dictionary · retention.sql — run once in ClickHouse on the telemetry droplet, BEFORE the tables

-- Starbase upserts (project_id, days) here on every plan change; project_id is hyphen-stripped 32-hex
CREATE TABLE otel.proj_retention_src
( project_id String, days UInt16 )
ENGINE = ReplacingMergeTree ORDER BY project_id;

-- the in-memory lookup the tables call at insert; refreshes from the local table every 5–10 min
CREATE DICTIONARY otel.proj_retention
( project_id String, days UInt16 DEFAULT 90 )
PRIMARY KEY project_id
SOURCE(CLICKHOUSE(TABLE 'proj_retention_src' DB 'otel'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

2 · The two log tables. otel_http (one row per request — the L7 metric source) and otel_logs (app + build output). Each carries the exporter's stock columns — names and types must match its INSERT exactly (create_schema: false) — plus our MATERIALIZED columns, which the exporter never sees but ClickHouse computes on every insert. Engine, sort order, and TTL are ours to choose: project_id leads the ORDER BY (cheap per-tenant reads), and the TTL reads the per-row retention_days.

Request rows: Envoy stamped the fields into the log attributes (Collect · step 3), so the derived columns read LogAttributes — including the identity split from the HTTPRoute name (<project32><service32>-<env>, §20.2), done once here, not per scrape:

L7 events table · otel_http.sql — run once in ClickHouse on the telemetry droplet

CREATE TABLE otel.otel_http
(
  -- the exporter's stock columns: names & types must match its INSERT — don't rename or retype
  Timestamp          DateTime64(9),
  TraceId            String,
  SpanId             String,
  TraceFlags         UInt8,
  SeverityText       LowCardinality(String),
  SeverityNumber     UInt8,
  ServiceName        LowCardinality(String),
  Body               String,
  ResourceSchemaUrl  LowCardinality(String),
  ResourceAttributes Map(LowCardinality(String), String),
  ScopeSchemaUrl     LowCardinality(String),
  ScopeName          String,
  ScopeVersion       LowCardinality(String),
  ScopeAttributes    Map(LowCardinality(String), String),
  LogAttributes      Map(LowCardinality(String), String),
  EventName          String,

  -- ours: computed at insert from the attribute map (MATERIALIZED = never part of the INSERT)
  route_name     String                 MATERIALIZED LogAttributes['route_name'],
  project_id     String                 MATERIALIZED substring(LogAttributes['route_name'], 1, 32),
  service_id     String                 MATERIALIZED substring(LogAttributes['route_name'], 33, 32),
  environment    String                 MATERIALIZED substring(LogAttributes['route_name'], position(LogAttributes['route_name'], '-') + 1),
  method         LowCardinality(String) MATERIALIZED LogAttributes['method'],
  path           String                 MATERIALIZED LogAttributes['path'],
  response_code  UInt16                 MATERIALIZED toUInt16OrZero(LogAttributes['response_code']),
  response_flags String                 MATERIALIZED LogAttributes['response_flags'],
  duration_ms    Float64                MATERIALIZED toFloat64OrZero(LogAttributes['duration']),
  bytes_sent     UInt64                 MATERIALIZED toUInt64OrZero(LogAttributes['bytes_sent']),
  bytes_received UInt64                 MATERIALIZED toUInt64OrZero(LogAttributes['bytes_received']),
  retention_days UInt16                 MATERIALIZED dictGetOrDefault('otel.proj_retention', 'days', substring(LogAttributes['route_name'], 1, 32), toUInt16(90))
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)                              -- daily; per-tenant retention rides the TTL, not the partition
ORDER BY (project_id, environment, service_id, Timestamp)  -- tenant-first → cheap per-tenant reads
TTL toDateTime(Timestamp) + toIntervalDay(retention_days) DELETE;

App logs: identity was stamped from the pod's labels by k8sattributes and lands in ResourceAttributes. Labels carry hyphenated UUIDs, so the expressions strip hyphens — both tables then share the same 32-hex project_id/service_id form:

App logs table · otel_logs.sql — run once in ClickHouse on the telemetry droplet

CREATE TABLE otel.otel_logs
(
  -- the exporter's stock columns: names & types must match its INSERT — don't rename or retype
  Timestamp          DateTime64(9),
  TraceId            String,
  SpanId             String,
  TraceFlags         UInt8,
  SeverityText       LowCardinality(String),
  SeverityNumber     UInt8,
  ServiceName        LowCardinality(String),
  Body               String,
  ResourceSchemaUrl  LowCardinality(String),
  ResourceAttributes Map(LowCardinality(String), String),
  ScopeSchemaUrl     LowCardinality(String),
  ScopeName          String,
  ScopeVersion       LowCardinality(String),
  ScopeAttributes    Map(LowCardinality(String), String),
  LogAttributes      Map(LowCardinality(String), String),
  EventName          String,

  -- ours: identity from the pod labels (k8sattributes → ResourceAttributes), hyphen-stripped to 32-hex
  project_id     String MATERIALIZED replaceAll(ResourceAttributes['project_id'], '-', ''),
  service_id     String MATERIALIZED replaceAll(ResourceAttributes['service_id'], '-', ''),
  environment    String MATERIALIZED ResourceAttributes['environment'],
  cluster_id     String MATERIALIZED ResourceAttributes['cluster_id'],
  namespace      String MATERIALIZED ResourceAttributes['k8s.namespace.name'],
  retention_days UInt16 MATERIALIZED dictGetOrDefault('otel.proj_retention', 'days', replaceAll(ResourceAttributes['project_id'], '-', ''), toUInt16(90))
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (project_id, environment, service_id, Timestamp)
TTL toDateTime(Timestamp) + toIntervalDay(retention_days) DELETE;

3 · The resource-metric tables. cAdvisor meters land in the exporter's otel_metrics_gauge / otel_metrics_sum tables. Their schema is the exporter's (identity sits in the Attributes map), so let the gateway create them itself on first start (create_schema: true — it skips our pre-created tables via IF NOT EXISTS), then attach retention:

Resource-metric retention · metrics-retention.sql — run once in ClickHouse AFTER the gateway's first start created the tables

ALTER TABLE otel.otel_metrics_gauge
  ADD COLUMN retention_days UInt16 MATERIALIZED dictGetOrDefault('otel.proj_retention', 'days', replaceAll(Attributes['project_id'], '-', ''), toUInt16(90)),
  MODIFY TTL toDateTime(TimeUnix) + toIntervalDay(retention_days) DELETE;

ALTER TABLE otel.otel_metrics_sum
  ADD COLUMN retention_days UInt16 MATERIALIZED dictGetOrDefault('otel.proj_retention', 'days', replaceAll(Attributes['project_id'], '-', ''), toUInt16(90)),
  MODIFY TTL toDateTime(TimeUnix) + toIntervalDay(retention_days) DELETE;

(The metrics exporter is alpha — verify the column names on your pinned Collector version.)

4 · Users — who touches ClickHouse. Least privilege, one account per job:

ClickHouse users · users.sql — run once in ClickHouse on the telemetry droplet

CREATE USER otel_ingest IDENTIFIED BY '<from-secret>';     -- the OTel gateway: the ONLY telemetry writer
GRANT INSERT ON otel.* TO otel_ingest;
GRANT CREATE TABLE ON otel.* TO otel_ingest;               -- lets create_schema:true create the otel_metrics_* tables

CREATE USER starbase_read IDENTIFIED BY '<from-secret>';   -- Starbase query broker (Read & query): SELECT only
GRANT SELECT ON otel.* TO starbase_read;

CREATE USER starbase_write IDENTIFIED BY '<from-secret>';  -- Starbase: retention upserts only
GRANT INSERT ON otel.proj_retention_src TO starbase_write;
GRANT SYSTEM RELOAD DICTIONARY ON *.* TO starbase_write;   -- optional: apply a plan change immediately, not at next refresh

Writer	Writes	How
OTel gateway (otel_ingest)	otel_logs · otel_http · otel_metrics_*	the sole telemetry writer (Collect)
Starbase (starbase_write)	proj_retention_src	plan → days upserts (in regions without DO peering these ride the Mass Relay channel, §39.3 #43)
Shuttle (billing audit — post-MVP)	§36's audit table	written in-region via the gateway (§39.3 #71) — outside this pipeline; Postgres stays the invoicing source (MVP keeps raw snapshots in Postgres only)

Build logs never land in ClickHouse (2026-07-09) — the Worker relays them live and archives one object per build to DO Spaces (§16.6).

5 · Rollups — add them when raw gets slow, and here's exactly how. Dashboards query raw rows at MVP — nothing to pre-compute. The trigger to add a rollup is concrete: a tenant's raw latency-panel query passing ~2 s, or a single tenant exceeding ~50 M request rows/day. Then create the 1-minute aggregate table and the materialized view that feeds it on every insert:

1-minute L7 rollup · otel_http_1m.sql — run in ClickHouse when the trigger hits (post-MVP)

CREATE TABLE otel.otel_http_1m
(
  minute      DateTime,
  project_id  String,
  environment String,
  service_id  String,
  latency_q   AggregateFunction(quantiles(0.50, 0.95, 0.99), Float64),
  requests    SimpleAggregateFunction(sum, UInt64),
  errors      SimpleAggregateFunction(sum, UInt64),
  bytes       SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toDate(minute)
ORDER BY (project_id, environment, service_id, minute)
TTL minute + toIntervalDay(90) DELETE;   -- rollups are tiny; flat 90d for all plans is fine

-- fires on every insert into otel_http; computes from the raw attribute map, NOT the derived
-- columns (MATERIALIZED columns aren't reliably visible to MVs — verify on your CH version)
CREATE MATERIALIZED VIEW otel.otel_http_1m_mv TO otel.otel_http_1m AS
SELECT
  toStartOfMinute(Timestamp)                                     AS minute,
  substring(LogAttributes['route_name'], 1, 32)                  AS project_id,
  substring(LogAttributes['route_name'], position(LogAttributes['route_name'], '-') + 1) AS environment,
  substring(LogAttributes['route_name'], 33, 32)                 AS service_id,
  quantilesState(0.50, 0.95, 0.99)(toFloat64OrZero(LogAttributes['duration']))           AS latency_q,
  count()                                                        AS requests,
  countIf(toUInt16OrZero(LogAttributes['response_code']) >= 500) AS errors,
  sum(toUInt64OrZero(LogAttributes['bytes_sent']) + toUInt64OrZero(LogAttributes['bytes_received'])) AS bytes
FROM otel.otel_http
GROUP BY minute, project_id, environment, service_id;

The dashboard switches long windows to the rollup (short/live windows keep reading raw):

Rollup read · Starbase query broker → ClickHouse (long windows)

SELECT minute,
       quantilesMerge(0.50, 0.95, 0.99)(latency_q) AS p50_p95_p99,
       sum(requests) / 60                          AS rps,
       sum(errors) / sum(requests)                 AS error_rate,
       sum(bytes)                                  AS throughput_bytes
FROM otel.otel_http_1m
WHERE project_id = {project_id:String} AND environment = {environment:String}
  AND minute >= now() - INTERVAL 7 DAY
GROUP BY minute ORDER BY minute;

Two facts to know: an MV only sees rows inserted after it exists — backfill history once with INSERT INTO otel.otel_http_1m SELECT … FROM otel.otel_http WHERE Timestamp < '<mv-created>' GROUP BY …; and the MV runs per insert batch, which is why the gateway's batching also bounds rollup overhead.

Gotchas

• Dictionary before tables. A table whose computed column calls dictGet errors on attach if the dictionary is missing — bring-up order is dictionary → tables → collectors (Bootstrap). dictGetOrDefault(…, 90) keeps unknown projects at 90 days instead of failing.
• Expiry is lazy. TTL deletes run during background merges — day-8 Hobby rows lingering a little is normal, not a bug. OPTIMIZE TABLE … FINAL forces it if ever needed.
• Plan changes affect new rows only. retention_days is stamped at insert; an upgrade re-times data written after the change — it doesn't retro-extend (or retro-shorten) existing rows.
• Pin the Collector; stage upgrades. The exporter checks the table's columns at startup and adapts its INSERT — an upgrade can change what it writes. Same discipline as the EG-bump rule.
• Daily partitions on purpose. Partitioning per project would split every batch into one part per project and recreate "too many parts" — per-tenant retention rides the row-level TTL instead.
• If Envoy's JSON lands in Body instead of the attributes (unverified in EG docs — see Collect · step 3), switch the otel_http expressions to JSONExtract(Body, '<field>', 'String'). Same columns, same queries.

Reference

Who writes these tables: Collect · who reads them: Read & query. Retention policy FR-051; HTTPRoute name §20.2; labels §24.1; billing split (Postgres invoices / ClickHouse audit) §36.