---
name: nextjs-clickhouse-analytics
description: "Use when building, refactoring, or reviewing a Next.js + ClickHouse + Prisma (Analytics & Observability) project (Next.js, ClickHouse, Prisma, PostgreSQL, TypeScript). Architecture guidelines for high-volume event ingestion and analytical queries using ClickHouse alongside a PostgreSQL/Prisma control plane."
license: MIT
metadata:
  source: https://stackitfast.com/rules/nextjs-clickhouse-analytics
  version: "2026-10-04"
---

# Next.js + ClickHouse + Prisma (Analytics & Observability) — Agent Skill

## When to use this skill
- Any task that scaffolds, modifies, refactors, or reviews code in a Next.js + ClickHouse + Prisma (Analytics & Observability) codebase.
- Whenever the project depends on Next.js, ClickHouse, Prisma, PostgreSQL, TypeScript.
- Apply these guidelines before proposing architecture, database, or deployment changes.

## Guidelines
# Project Architecture & Guidelines (Next.js + ClickHouse + Prisma)

## 1. System Architecture
- **Framework**: Next.js 16 (App Router) for the dashboard/API surface.
- **Analytical Store**: ClickHouse for high-volume event data (page views, LLM traces, metrics) — columnar storage built for fast aggregation over billions of rows.
- **Control Plane**: PostgreSQL via Prisma for everything that isn't an event: users, projects, API keys, billing state.
- **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.

## 2. Event Ingestion Pipeline
- 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.
- 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.
- 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)`).

## 3. Query Layer & Aggregation
- 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.
- Pre-aggregate expensive rollups (daily/hourly counts) into materialized views instead of re-scanning raw events on every dashboard load.
- Parameterize every query; never string-concatenate user-controlled filter values into ClickHouse SQL.

## 4. Prisma Control-Plane Conventions
- 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`.
- 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.

## 5. Common Pitfalls / Coding Standards
- ❌ Running one `INSERT` per event against ClickHouse — batch or use an async insert buffer.
- ❌ Storing frequently-updated entity fields (user email, plan tier) in ClickHouse rows, which are effectively immutable once written.
- ✅ Set a TTL on raw event tables if only aggregated data needs to be retained long-term, to control storage growth.

## 6. Testing Conventions
- 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.
- Unit tests for aggregation/rollup logic with fixture event data and known expected outputs.
- Load-test the ingestion endpoint specifically; it has different failure modes (burst traffic, partial batch failures) than the dashboard API.

## 7. Git Workflow & PR Conventions
- Conventional Commits scoped to the layer, e.g. `perf(clickhouse): add materialized view for daily active users`.
- ClickHouse schema migrations and Prisma migrations ship in separate, clearly labeled files even when part of the same PR.
- Require `tsc --noEmit` and a ClickHouse integration test pass in CI before merge.
- Squash-merge; run both migration types as explicit deploy steps, never ad hoc against production.

## Source
Maintained at https://stackitfast.com/rules/nextjs-clickhouse-analytics — also available as AGENTS.md, CLAUDE.md, and Cursor .mdc.