clickhouse-reference-architecture

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 <[e-mail]>
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: