STACK IT FAST
ALL RULES & SKILLS

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

Raw .MD nextjs-clickhouse-analytics
CURATED RULE AGENTS.MD + CLAUDE.MD + .MDC + SKILL.MD

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

Next.js ClickHouse Prisma PostgreSQL TypeScript
CLI & AI Agent Import Verified in 4 production projects
AGENTS.md
Paste in your project root
SKILL.md
Installs to .claude/skills/nextjs-clickhouse-analytics
INTERACTIVE RULE & SKILL VIEWER (AGENTS.MD, CLAUDE.MD, .MDC & SKILL.MD)
Optimized for:CursorClaude CodeWindsurfAGY
AGENTS.md·37 lines · 3.5 KB
1# Project Architecture & Guidelines (Next.js + ClickHouse + Prisma)
2
3## 1. System Architecture
4- **Framework**: Next.js 15 (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.
ARCHITECTURE NOTES & IMPLEMENTATION GUIDE
Export as Markdown

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.

MORE AI AGENT CODING RULES & SKILLS
View All Rules & Skills
4 FILE FORMATS Rust, Axum

Rust + Axum + PostgreSQL (sqlx & Tokio)

High-concurrency systems architecture for Rust 2024, Axum 0.8, Tokio async runtime, compile-time SQL verification with sqlx, and Tower middleware.

6 techs Copy Rules
4 FILE FORMATS Astro, Tailwind CSS

Astro SSR + Tailwind CSS + Cloudflare

Architecture rules for Astro server-side rendering, React client islands, Tailwind CSS, and Cloudflare Pages/Workers edge deployment.

5 techs Copy Rules
4 FILE FORMATS Bun, Hono

Bun + Hono + SQLite (libSQL & Drizzle ORM)

Ultra-fast edge API architecture using Bun.serve(), Hono v4, Drizzle ORM with embedded SQLite / Turso libSQL, and Zod OpenAPI validation.

6 techs Copy Rules
4 FILE FORMATS Expo, React Native

Expo Router + React Native + NativeWind

Modern cross-platform mobile architecture for Expo SDK 52, Expo Router v4 file-based routing, React Native, NativeWind v4, and TanStack Query.

7 techs Copy Rules