Node.js + Prisma + Postgres

# CLAUDE.md — Node.js + Prisma + Postgres

## Schema

- `prisma/schema.prisma` is the source of truth. Every change goes through `prisma migrate dev`.
- Datasource: Postgres. `provider = "postgresql"`.
- For Vercel/serverless, set both `url` and `directUrl`:
  ```prisma
  datasource db {
    provider  = "postgresql"
    url       = env("DATABASE_URL")        // pooled (pgbouncer)
    directUrl = env("DIRECT_URL")          // direct (for migrations)
  }
  ```

## Singleton client

- One Prisma client per process. Stash on `globalThis` in dev to survive HMR:
  ```ts
  const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient }
  export const db = globalForPrisma.prisma ?? new PrismaClient({ log: ["error"] })
  if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db
  ```
- `db.$connect()` is lazy; you usually don't need to call it explicitly.

## Migrations

- Local: `prisma migrate dev --name <change>`. Reviewed and committed.
- CI: `prisma migrate deploy`. Idempotent; safe to run on every deploy.
- Never edit a merged migration. Add a new one to fix forward.
- For data-only migrations, write a script that runs separately — don't mix DDL and large data updates.

## Queries

- `select` to narrow returns. Default returns all scalars — wasteful and leaks fields.
- `include` for related records. Pick `select` over `include` when you only need a few fields of the relation.
- `findUnique` for primary-key/unique lookups. `findFirst` only when you need ordering or filters.
- Cursor pagination over `skip`+`take` for large tables.

## Transactions

- Use `db.$transaction` for multi-step writes. Pass an array of promises (sequential) or a callback (interactive):
  ```ts
  await db.$transaction(async (tx) => {
    const order = await tx.order.create({ data })
    await tx.audit.create({ data: { orderId: order.id, action: "create" } })
  })
  ```
- Set `timeout` on long transactions to avoid holding locks.

## Connection pooling

- For serverless, **always** front Prisma with pgbouncer (transaction mode) or use Prisma Accelerate.
- `connection_limit` in the pooled URL controls per-instance pool size. Multiply by replicas to size the DB.
- Direct connections only for migrations and admin scripts.

## Performance

- Add an index for every `where` field you use in production. Prisma doesn't infer them — declare with `@@index`.
- `EXPLAIN ANALYZE` on slow queries. Prisma generates the SQL; you can paste it into psql.
- Batch reads: `findMany` with an `in` clause beats N round-trips.

## Don't

- Don't share a Prisma client across short-lived serverless functions without a connection pooler — you'll exhaust the DB.
- Don't return Prisma models directly from public APIs. Map to a DTO so internal field changes don't break consumers.
- Don't use `db.$queryRawUnsafe`. The safe variants prevent SQL injection.
- Don't run migrations from app boot in multi-replica deploys. Race conditions silently corrupt schemas.