# Next.js 15 + Drizzle ORM + Supabase — AI Agent Guidelines & Architecture Rules

> Production guidelines for Next.js App Router, Drizzle ORM with postgres-js, Supabase Transaction Pooler (prepare: false), and RLS security.
> Technologies: Next.js, Drizzle, Supabase, TypeScript, Tailwind CSS, PostgreSQL

---

## AGENTS.md
```markdown
# Project Architecture & Guidelines (Next.js + Drizzle + Supabase)

## 1. System Architecture
- **Framework**: Next.js 15 (App Router, Server Components by default).
- **Database & ORM**: PostgreSQL via Supabase, typed schema managed with Drizzle ORM (`drizzle-orm`, `drizzle-kit`, `postgres-js`).
- **Auth & Storage**: Supabase Auth (`@supabase/ssr` cookie handler) and Supabase Storage.
- **Connection Pooling**: Supabase Transaction Pooler (port 6543) via `postgres-js`.

## 2. Database Connection Rules (Critical Supabase Pooler Gotcha)
- When connecting to Supabase's Transaction Pooler (port 6543), prepared statements MUST be disabled (`prepare: false`). Otherwise queries will fail with `prepared statement "drizzle_s_1" already exists`.
- Always implement the singleton connection pattern in `src/db/index.ts`:
  ```typescript
  import { drizzle } from 'drizzle-orm/postgres-js';
  import postgres from 'postgres';
  import * as schema from './schema';

  const connectionString = process.env.DATABASE_URL!;
  const globalForDb = globalThis as unknown as { conn: postgres.Sql | undefined };

  const client =
    globalForDb.conn ??
    postgres(connectionString, {
      prepare: false, // REQUIRED for Supabase Transaction Pooler (:6543)
      max: process.env.NODE_ENV === 'production' ? 10 : 1,
    });

  if (process.env.NODE_ENV !== 'production') globalForDb.conn = client;

  export const db = drizzle(client, { schema });
  ```
- For Drizzle Kit migrations, provide the direct PostgreSQL connection URL (`DIRECT_URL` on port 5432) in `drizzle.config.ts`.

## 3. Schema Organization & Relational Queries
- Group modular tables in `src/db/schema/` (e.g., `users.ts`, `posts.ts`, `auth.ts`).
- Define explicit relations in `src/db/schema/relations.ts` using `relations()` from `drizzle-orm`.
- Use Drizzle Relational Queries (`db.query.<table_name>.findFirst({ with: { ... } })`) for typed nested fetches without manual joins.

## 4. Server Actions & Mutations
- Place server actions in `src/actions/` with `'use server'` at the top.
- Validate all inputs using `zod` schemas before executing database queries.
- Authenticate the calling session using Supabase SSR client before executing privileged operations.

## 5. Common Pitfalls to Avoid
- ❌ Forgetting `prepare: false` on Supabase pooled connections.
- ❌ Running `drizzle-kit push` against production databases; always use generated SQL migrations (`drizzle-kit generate` & `drizzle-kit migrate`).
- ❌ Fetching data in client component `useEffect`; fetch in React Server Components or Server Actions.

## 6. Testing Conventions
- Use Vitest for unit tests on Server Actions and Drizzle query helpers; mock the `db` singleton with a test-only in-memory or containerized Postgres.
- Use Playwright for e2e coverage of auth flows (Supabase SSR cookie handling is easy to break silently across route handler changes).
- Test RLS policies directly with SQL assertions against a Supabase local dev instance — application-level tests alone won't catch a missing policy.
- Run `tsc --noEmit` as a required check; Drizzle's inferred types catch most schema/query mismatches before runtime.

## 7. Git Workflow & PR Conventions
- Conventional Commits (`feat:`, `fix:`, `refactor:`) scoped to the route or table, e.g. `fix(db): add missing index on posts.author_id`.
- Generated Drizzle migrations (`drizzle-kit generate`) ship in the same PR as the schema change; never hand-edit generated SQL.
- Require `tsc --noEmit` and `bun run build` green before merge — a broken Server Action fails silently in dev but hard in production.
- Squash-merge; never `drizzle-kit push` directly against a shared or production database from a feature branch.
```

---

## CLAUDE.md
```markdown
# CLAUDE.md — Next.js + Drizzle + Supabase

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
- `bun run db:generate` - Generate Drizzle SQL migration files
- `bun run db:migrate` - Apply pending migrations to Supabase database
- `bun run db:studio` - Open Drizzle Studio visual GUI

## Claude Specific Directives
- When writing database queries, use Drizzle typed query builder (`db.query` or `db.select()`).
- Always remember `prepare: false` is required for Supabase transaction pooler connections.
```

---

## .cursor/rules/stack.mdc
```markdown
---
description: Next.js 15 + Drizzle ORM + Supabase architecture rules
globs: ["**/*.ts", "**/*.tsx"]
alwaysApply: true
---

# Next.js 15 + Drizzle ORM + Supabase

- Next.js App Router, Server Components by default, Drizzle ORM over `postgres-js`, Supabase Auth/Storage.
- Transaction Pooler (port 6543) requires `prepare: false` — omitting it throws "prepared statement already exists" under load.
- Singleton `db` client in `src/db/index.ts` using `globalThis` to survive HMR; migrations use the direct connection (`DIRECT_URL`, port 5432).
- Modular schema files in `src/db/schema/`; explicit `relations()` for typed nested queries via `db.query.<table>.findFirst({ with: {...} })`.
- Server Actions in `src/actions/` with `'use server'`; validate every input with `zod` before touching the database.
- Authenticate the Supabase SSR session before any privileged Server Action runs.
- `drizzle-kit generate` + `drizzle-kit migrate` only — never `drizzle-kit push` against production.
- Fetch in Server Components or Server Actions — never `useEffect` in a client component.
```

---

## Architecture Overview & Best Practices
## Architecture Overview

Standardized production guidelines for **Next.js 15 App Router**, **Drizzle ORM**, and **Supabase PostgreSQL**.

### Verified Real-World Adoption

Utilized by modern high-velocity platforms like **Supabase Studio**, **Dub.co**, and **CopilotKit**.

### Key Architectural Nuances

- **Prepared Statement Disabling**: Supabase's transaction pooler requires `prepare: false` to prevent statement naming collisions across ephemeral Lambda invocations.
- **Relational Query Builder**: Combines SQL performance with declarative, type-safe nested object queries.