0003 - ClickHouse for tenant analytics (Tier 1 telemetry)

• Status: Accepted
• Date: 2026-07-03
• Deciders: nexus core team
• Tags: analytics, clickhouse, ingest, query, storage

Every gateway request produces an analytics record: model, provider, latency, token counts, cost lines, feedback, and trace spans. This is high-volume, append-mostly data read back as tenant-scoped lists, detail lookups, and aggregates (dashboards, per-minute metrics, monthly usage rollups). Postgres holds the transactional control-plane state (orgs, keys, wallets, registry) and is the wrong shape for wide analytical scans over billions of request rows. The two workloads need different storage engines without forcing either into the other’s consistency model.

• Columnar scans and aggregation performance for tenant-scoped analytics queries.
• Append-mostly ingestion driven by at-least-once event delivery, so writes must be idempotent under redelivery.
• Reads from more than one service: the query service serves both HTTP and gRPC consumers.
• Postgres stays authoritative for transactional control-plane state; analytics must not contend with it.

• ClickHouse as a dedicated analytics store — chosen.
• Postgres-only — rejected: analytical scans over request history compete with OLTP control-plane traffic, and row-oriented storage makes aggregate queries over wide request rows expensive at volume.
• DuckDB / embedded analytical store — rejected: embedded engines bind the data to one process, but the query service and ingest service both need concurrent access over the network.
• Elasticsearch — strong for full-text search, but weaker for the numeric aggregation workload, heavier operationally, and its license posture complicates redistribution.

Chosen option: ClickHouse, written by the ingest service and read by the query service.

• Schema lives in services/ingest/migrations/clickhouse/0001_ingest_init.sql: requests, request_cost_lines, request_feedback, metrics_by_minute, trace_spans, trace_events, trace_artifact_refs, and usage_monthly_rollup.
• Row tables use ReplacingMergeTree(version) keyed on request identity, so redelivered events overwrite rather than duplicate; metrics_by_minute uses SummingMergeTree for pre-aggregated per-minute counters.
• The ingest service acks a JetStream message only after the ClickHouse write is durable — the ack-after-durable-write contract described in the architecture data model documentation.
• The query service (services/query/) reads ClickHouse and exposes the data over both HTTP and gRPC.

• Two consistency models run side by side: Postgres is read-your-writes transactional; ClickHouse is eventually consistent until parts merge, and consumers of analytics endpoints must tolerate ingest lag.
• Ingest writes must remain idempotent: every insert carries the identity and version columns that ReplacingMergeTree dedupes on, and schema changes must preserve that keying.
• Paths that need exact numbers (billing, usage rollups) query with FINAL or otherwise force dedup, trading read cost for correctness.
• Operators run one more stateful service; the dev stack and deploy manifests carry ClickHouse alongside Postgres and NATS.