# Next.js + ClickHouse + Prisma (Analytics & Observability) — AI Agent Guidelines & Architecture Rules

> Architecture guidelines for high-volume event ingestion and analytical queries using ClickHouse alongside a PostgreSQL/Prisma control plane.
> Technologies: Next.js, ClickHouse, Prisma, PostgreSQL, TypeScript

---

## AGENTS.md
```markdown
# Project Architecture & Guidelines (Next.js + ClickHouse + Prisma)

## 1. System Architecture
- **Framework**: Next.js 15 (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.
```

---

## CLAUDE.md
```markdown
# CLAUDE.md — Next.js + ClickHouse + Prisma

Refer to @AGENTS.md for complete database configuration and architectural constraints.

## Common Commands
- `bun run dev` - Start local Next.js development server
- `bun run build` - Build production Next.js bundle
- `bunx prisma migrate dev` - Apply Postgres control-plane migrations locally
- `docker compose up clickhouse` - Start a local ClickHouse instance for development

## Claude Specific Directives
- Never write mutable entity state to ClickHouse; it belongs in the Prisma/PostgreSQL control plane.
- Batch ClickHouse inserts; never emit one INSERT per event.
```

---

## .cursor/rules/stack.mdc
```markdown
---
description: Next.js + ClickHouse + Prisma analytics architecture rules
globs: ["**/*.ts", "**/*.tsx"]
alwaysApply: true
---

# Next.js + ClickHouse + Prisma

- Two-database split: ClickHouse for append-only high-volume events, PostgreSQL (via Prisma) for mutable control-plane entities.
- Ingestion goes through a dedicated batched endpoint, not the dashboard API routes; never one INSERT per event.
- Raw parameterized SQL for ClickHouse queries, no ORM; MergeTree tables partitioned/ordered by the dominant query filter.
- Pre-aggregate expensive rollups into materialized views instead of scanning raw events per dashboard load.
- ClickHouse and Prisma migrations are separate, versioned, and never run ad hoc against production.
- Validate `project_id`/foreign references at ingestion time -- ClickHouse enforces no referential integrity.
```

---

## Architecture Overview & Best Practices
## 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.