Next.js + ClickHouse + Prisma (Analytics & Observability)

Curated rule Analytics & Dashboards · Data & Storage Infra Updated Oct 2026 Which file does my tool read?

Architecture guidelines for high-volume event ingestion and analytical queries using ClickHouse alongside a PostgreSQL/Prisma control plane.

Next.js ClickHouse Prisma PostgreSQL TypeScript

Formats: 4 files
AGENTS.md: 37 lines
CLAUDE.md: 15 lines
Languages: TypeScript
Updated: Oct 2026
Used by: 4 projects

Install

Writes .claude/skills/nextjs-clickhouse-analytics/SKILL.md

$ curl -s --create-dirs -o .claude/skills/nextjs-clickhouse-analytics/SKILL.md https://stackitfast.com/rules/nextjs-clickhouse-analytics/SKILL.md

Rule files

AGENTS.md· 37 lines · 3.5 KB

1	# Project Architecture & Guidelines (Next.js + ClickHouse + Prisma)
2
3	## 1. System Architecture
4	- Framework: Next.js 16 (App Router) for the dashboard/API surface.
5	- Analytical Store: ClickHouse for high-volume event data (page views, LLM traces, metrics) — columnar storage built for fast aggregation over billions of rows.
6	- Control Plane: PostgreSQL via Prisma for everything that isn't an event: users, projects, API keys, billing state.
7	- Two-database split: ClickHouse is write-heavy and append-only; PostgreSQL is the source of truth for entities that get updated in place. Never store mutable entity state in ClickHouse.
8
9	## 2. Event Ingestion Pipeline
10	- Events land through a dedicated ingestion endpoint (`/api/ingest` or an edge function), not through the same API routes that serve dashboard reads — ingestion needs to be fast, unauthenticated-by-API-key, and resilient to bursts.
11	- Batch inserts into ClickHouse (buffer client-side or via a queue) rather than one `INSERT` per event; ClickHouse is optimized for large batch writes, not high-frequency single-row inserts.
12	- Use ClickHouse's `MergeTree` engine family with a partition key on date/time and an order key matching the most common query filter (e.g. `(project_id, timestamp)`).
13
14	## 3. Query Layer & Aggregation
15	- Write raw SQL (via `clickhouse-client`/`@clickhouse/client`) for ClickHouse queries — an ORM abstraction adds little value for analytical SQL and obscures the columnar query patterns that make ClickHouse fast.
16	- Pre-aggregate expensive rollups (daily/hourly counts) into materialized views instead of re-scanning raw events on every dashboard load.
17	- Parameterize every query; never string-concatenate user-controlled filter values into ClickHouse SQL.
18
19	## 4. Prisma Control-Plane Conventions
20	- Prisma owns PostgreSQL migrations (`prisma migrate dev` / `prisma migrate deploy`) for the control-plane schema only — ClickHouse schema changes are separate versioned `.sql` files run through a migration tool like `clickhouse-migrations`.
21	- Foreign-key-style references from ClickHouse events to Postgres entities (`project_id`) are logical only — ClickHouse doesn't enforce referential integrity, so validate `project_id` exists at ingestion time.
22
23	## 5. Common Pitfalls / Coding Standards
24	- ❌ Running one `INSERT` per event against ClickHouse — batch or use an async insert buffer.
25	- ❌ Storing frequently-updated entity fields (user email, plan tier) in ClickHouse rows, which are effectively immutable once written.
26	- ✅ Set a TTL on raw event tables if only aggregated data needs to be retained long-term, to control storage growth.
27
28	## 6. Testing Conventions
29	- Integration tests against a real ClickHouse instance (Docker container) for ingestion and query-layer correctness — mocking ClickHouse's SQL dialect tends to hide real bugs.
30	- Unit tests for aggregation/rollup logic with fixture event data and known expected outputs.
31	- Load-test the ingestion endpoint specifically; it has different failure modes (burst traffic, partial batch failures) than the dashboard API.
32
33	## 7. Git Workflow & PR Conventions
34	- Conventional Commits scoped to the layer, e.g. `perf(clickhouse): add materialized view for daily active users`.
35	- ClickHouse schema migrations and Prisma migrations ship in separate, clearly labeled files even when part of the same PR.
36	- Require `tsc --noEmit` and a ClickHouse integration test pass in CI before merge.
37	- Squash-merge; run both migration types as explicit deploy steps, never ad hoc against production.

Works with Cursor · Claude Code · Windsurf · AGY

Architecture notes

Architecture Overview

Standardized production guidelines for Next.js, ClickHouse, and Prisma/PostgreSQL — the two-database split used by analytics and observability platforms that need both transactional entity storage and high-volume event aggregation.

Verified Real-World Adoption

Umami (privacy-focused web analytics) and Langfuse (LLM observability) both run this exact split: Next.js + Prisma over PostgreSQL for the control plane, ClickHouse for the raw event/trace volume. The same architectural pattern, with different primary languages, powers PostHog, Sentry, and Plausible Analytics.

Key Architectural Nuances

Append-Only vs. Mutable Storage: ClickHouse rows are treated as immutable once written; anything that changes over time (user plan, project name) stays in PostgreSQL and is joined logically, not physically, at query time.
Batched, Async Ingestion: The ingestion path is architected separately from the dashboard read path, since bursty high-volume writes and low-latency authenticated reads have conflicting performance profiles.

Frequently asked questions

Does this AGENTS.md work with Cursor, Claude Code, and Windsurf?

Yes — AGENTS.md is the open, cross-tool standard read by Cursor, Claude Code, Windsurf, and 30+ other agents. A dedicated .mdc file is also included for Cursor's native .cursor/rules format.

Why run two databases instead of just PostgreSQL?

PostgreSQL is a row-oriented database optimized for transactional reads/writes of individual records. ClickHouse is column-oriented and optimized for scanning and aggregating billions of append-only rows in milliseconds. Once event volume gets large enough that dashboard queries on Postgres start timing out, splitting the write-heavy analytical workload into ClickHouse -- while keeping Postgres for everything transactional -- is the standard fix rather than trying to tune Postgres past its design center.

When should a project reach for this pattern instead of just PostgreSQL?

When event volume is in the millions-of-rows-per-day range and dashboard queries need to aggregate over that volume in near-real-time. Below that scale, PostgreSQL with a well-indexed events table (or a service like Tinybird) is simpler to operate and sufficient.

Used in production

Explore all stacks

Cal.com

Scheduling SaaS · 6-20 people

Splitting the tRPC-based web app from an independently versioned NestJS Platform API lets Cal.com serve both its own product and third-party developers from one monorepo, while Turborepo and per-integration app-store packages keep dozens of calendar/payment providers buildable and testable in isolation.

Cosmo (WunderGraph)

Federated GraphQL API Gateway · 6-20 People

Splitting the Go router (runtime query execution) from the TypeScript Controlplane (schema registry and management) lets Cosmo scale and deploy the performance-critical data path independently, while a dedicated ClickHouse-backed metrics service keeps high-volume usage analytics off the primary Postgres database.

Sentry

Error Tracking Platform · 20+ people

Pairing Django with Kafka and ClickHouse allows Sentry to ingest billions of events daily while providing sub-second analytical search across production traces.

Taxonomy

Open-Source SaaS Template · 1-5 people

Built to demonstrate the App Router, server components, and Next.js 13 data fetching patterns in one realistic, full-featured codebase.

Where this stack fits

Stack It First recommends it at:

Backend · Scale · Data & AI

Walk the backend roadmap

Every stack ships as files

More AI agent coding rules and skills

View all

rust-axum-postgres/AGENTS.mdRust + Axum + PostgreSQL API (sqlx & Tokio)Rust · Axum · PostgreSQL astro-static-content/AGENTS.mdAstro Static Site + Content Collections + TailwindAstro · Tailwind CSS · TypeScript bun-hono-sqlite/AGENTS.mdBun + Hono + SQLite (libSQL & Drizzle ORM)Bun · Hono · SQLite expo-react-native-tailwind/AGENTS.mdExpo Router + React Native + NativeWindExpo · React Native · NativeWind