| name | clickhouse-reference-architecture |
| description | Production reference architecture for ClickHouse-backed applications —
project layout, data flow, multi-tenant patterns, and operational topology.
Use when designing a new ClickHouse system, reviewing an existing analytics
architecture, or establishing standards for ClickHouse integrations.
Trigger with "clickhouse architecture", "clickhouse project structure",
"clickhouse design", "clickhouse multi-tenant", "clickhouse reference".
|
| allowed-tools | Read, Grep |
| version | 1.7.0 |
| license | MIT |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| tags | ["saas","database","analytics","clickhouse","olap"] |
| compatibility | Designed for Claude Code |
ClickHouse Reference Architecture
Overview
Production-grade architecture for ClickHouse analytics platforms covering project
layout, data flow, multi-tenancy, and operational patterns. Work through the five
steps below to get the high-level shape, then drill into the linked reference files
for the full DDL, client code, and tenancy trade-offs.
Prerequisites
- Understanding of ClickHouse fundamentals — table engines,
ORDER BY sort keys,
and partitioning.
- A TypeScript/Node.js project (the client examples use
@clickhouse/client).
- When reviewing an existing codebase,
Grep for createClient( to locate the
current client module and Read the SQL files under clickhouse/schemas/.
Instructions
Step 1: Project Structure
Keep SQL DDL as the source of truth under clickhouse/schemas/, named query
functions under clickhouse/queries/, and ingestion/API/jobs in sibling modules.
my-analytics-platform/
├── src/
│ ├── clickhouse/
│ │ ├── client.ts # Singleton client with health checks
│ │ ├── schemas/ # SQL DDL files (source of truth)
│ │ │ ├── 001-events.sql
│ │ │ ├── 002-users.sql
│ │ │ └── 003-materialized-views.sql
│ │ ├── queries/ # Named query functions
│ │ └── migrations/ # Schema migrations (runner.ts + *.sql)
│ ├── ingestion/ # webhook-receiver, kafka-consumer, buffer
│ ├── api/ # routes.ts, middleware.ts (auth, rate limit)
│ └── jobs/ # daily-rollup.ts, cleanup.ts (TTL enforcement)
├── tests/ # unit/ + integration/
├── docker-compose.yml # Local ClickHouse
├── init-db/ # Docker init scripts
└── config/ # development / staging / production .env
Step 2: Data Flow Architecture
Data moves in one direction: sources → a batching ingestion layer → ClickHouse
(raw MergeTree → materialized views → aggregate tables) → an API that reads only
the aggregate tables → dashboards.
Data Sources (Webhooks, API, Kafka, S3)
│
Ingestion Layer (Buffer + batch, 10K+ rows/insert)
│
ClickHouse Server
Raw Event Tables (MergeTree, append-only)
│ auto-aggregate on INSERT
Materialized Views (hourly, daily, tenant-level)
│
Aggregate Tables (AggregatingMergeTree)
│
API Layer (queries aggregate tables, never raw events)
│
Dashboards / Client Apps
Step 3: Schema Design (3-Layer Pattern)
Three layers — raw append-only events, hourly aggregation, and a daily rollup for
dashboards — with materialized views auto-populating each aggregate on INSERT.
The essential raw-table skeleton:
