Prisma + Postgres prompt

# Prisma + Postgres

You are working in a TypeScript project (often Next.js 16) that uses Prisma ORM 7 with PostgreSQL. Write code for the versions in `package.json`: Prisma 7 moved the connection URL out of the schema, generates the client into your project, requires a driver adapter and dropped several APIs you may remember.

## Before you write code

- Check `package.json` for `prisma`, `@prisma/client` and `@prisma/adapter-pg` (all 7.x, same version), and the scripts that run `prisma generate` and migrations.
- Read `prisma.config.ts` (schema path, datasource URL, migrations path and seed command) and the schema (`prisma/schema.prisma` or a `prisma/` folder of `.prisma` files) before writing a query.
- Find where the client is created (`lib/db.ts`, `lib/prisma.ts`) and import it from there. Find the generator's `output`: the client is imported from that folder, not from `@prisma/client`.
- Read `prisma/migrations/` to see how the schema got here, and the generated model types (`<output>/models/`) when unsure of a field.

## Config, generator and client

```ts
// prisma.config.ts
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: { path: "prisma/migrations", seed: "tsx prisma/seed.ts" },
  datasource: { url: env("DATABASE_URL") },
});
```

```prisma
generator client {
  provider = "prisma-client"
  output   = "../generated/prisma"
}

datasource db {
  provider = "postgresql"
}
```

- The CLI's connection lives in `prisma.config.ts`, never as `url = env(...)` in the schema. Prisma 7 does not load `.env` for you: load it in the config (`dotenv/config`) or through your runtime.
- `datasource.url` is what migrations use: a direct connection, not a transaction-mode pooler. The app connects through the adapter, which may use a pooled URL.
- Use the `prisma-client` generator with an `output` path. Git-ignore the output and run `prisma generate` in `postinstall` or the build, and again after every schema change: `migrate dev` no longer runs it.

```ts
// lib/db.ts
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "@/generated/prisma/client";

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
  });

if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma;
```

- One client per process. The `globalThis` cache stops hot reload from opening a new pool on every edit. Never create a client per request or inside a component.
- A driver adapter is required (or `accelerateUrl` for Accelerate). The constructor has no `datasources` or `datasourceUrl` option. Pool size and timeouts are `pg` options passed to `PrismaPg` (`max`, `connectionTimeoutMillis`); on serverless, keep `max` small and connect through a pooler.
- Import types and enums from `<output>/client` on the server, and from `<output>/browser` in code that may reach the browser; it has no `PrismaClient`.
- `$use` middleware is gone: use `$extends` (query extensions, computed fields) and keep extensions on the one shared client. Hide sensitive columns everywhere with the constructor's `omit` option.

## Schema design

- IDs: `String @id @default(uuid(7)) @db.Uuid` (time-ordered) or `cuid(2)`; an `Int @id @default(autoincrement())` only when IDs never leave the database.
- Every table gets `createdAt DateTime @default(now())` and `updatedAt DateTime @updatedAt`.
- Relations: name both sides, set `onDelete` (`Cascade` for owned rows, `Restrict` or `SetNull` otherwise), and add `@@index([authorId])` on every foreign key: Postgres does not index them for you.
- Use `@unique` and compound `@@unique([orgId, slug])` for real invariants and rely on the error (`P2002`) rather than checking first. Add `@@index` for the columns you filter and sort by, in that order.
- Use `enum` for closed sets of states. Store money as integer cents or `Decimal @db.Decimal(12, 2)`, never `Float`.
- Map to snake_case names with `@map` and `@@map` when the database already uses them; keep model and field names camelCase in code.

## Queries

- `select` only the fields the caller needs, especially for lists and anything sent to a client. Define reusable shapes with `satisfies Prisma.UserSelect` and type results with `Prisma.UserGetPayload<{ select: typeof userCard }>`.
- Avoid N+1: fetch relations in the same query with `select`/`include`, or batch with `where: { id: { in: ids } }`. Never `await` a query inside a loop over rows.
- Paginate with cursors for feeds and large tables:

```ts
const page = await prisma.post.findMany({
  where: { published: true },
  orderBy: [{ createdAt: "desc" }, { id: "desc" }],
  take: 20,
  ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
  select: { id: true, title: true, createdAt: true },
});
```

  Order by a unique column last so pages never repeat or skip rows. Offset (`skip`) is fine only for small, bounded page counts.
- Use atomic updates (`{ increment: 1 }`, `updateMany` with a `where` that encodes the precondition) instead of read-modify-write.
- `findUnique` returns `null`; `findUniqueOrThrow` throws `P2025`. Catch `Prisma.PrismaClientKnownRequestError` (`Prisma` from `<output>/client`) and map `P2002` (unique), `P2003` (foreign key) and `P2025` (not found) to user-facing results.

## Transactions and raw SQL

- Nested writes are already one transaction. For independent writes that must succeed together, `prisma.$transaction([a, b])`.
- For logic between queries, use an interactive transaction and only `tx` inside it:

```ts
await prisma.$transaction(
  async (tx) => {
    const from = await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
    if (from.balance < 0) throw new Error("Insufficient funds"); // rolls back
    await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
  },
  { isolationLevel: Prisma.TransactionIsolationLevel.Serializable, timeout: 10_000 }
);
```

  Keep it short: no HTTP calls, emails or file uploads inside, since it holds a connection and locks. Retry serialization failures (`P2034`).
- Raw SQL only through tagged templates, which parameterize values: ``prisma.$queryRaw<Row[]>`SELECT … WHERE id = ${id}` ``, `$executeRaw` for writes, `Prisma.sql` and `Prisma.join` to compose. Never `$queryRawUnsafe` or `$executeRawUnsafe` with interpolated input.
- For typed raw queries, use TypedSQL (still a preview feature): `previewFeatures = ["typedSql"]` in the generator, one query per file in `prisma/sql/*.sql`, `prisma generate --sql`, then `prisma.$queryRawTyped(getActiveUsers(minAge))` with the function imported from `<output>/sql`.

## Migrations and seeding

- Development: edit the schema, run `prisma migrate dev --name <change>`, read the generated SQL, commit `prisma/migrations/` with the schema change. Use `--create-only` to edit the SQL first: renames (Prisma drops and re-adds a renamed column), defaults and backfills for new required columns.
- Production and CI: `prisma migrate deploy`, before the new code serves traffic. Never `migrate dev`, `migrate reset` or `db push` against a shared or production database; `db push` is for throwaway prototypes.
- Make breaking changes in steps (add column, backfill, switch code, then drop) so old and new code both work during a deploy.
- Never edit a migration that has been applied anywhere; add a new one.
- Seed with the command in `prisma.config.ts` (`migrations.seed`), run explicitly with `prisma db seed`; migrations do not seed. Write seeds with `upsert` so they can run twice, and give scripts their own client.

## Server-only data access

- Query only from server code: Server Components, Server Functions, Route Handlers and a data layer marked `import "server-only"`. Never import the client into a Client Component or a file it imports.
- Authorize before querying, and scope by the caller's identity in the `where` (`{ id, ownerId: user.id }`), not after loading the row.
- Return plain data across the server-client boundary: `select` the fields, and convert `Decimal` and `BigInt` (`.toString()`, `Number()`) before passing them to Client Components.
- Validate input (Zod) before it reaches `where` or `data`; never spread request bodies into `data`.

## Do not

- Do not put `url` in the schema's datasource, import `PrismaClient` from `@prisma/client` when the project uses the `prisma-client` generator, or construct a client without an adapter.
- Do not use `$use`, `datasources`, `rejectOnNotFound` or `$queryRawUnsafe` with user input.
- Do not run `db push` or `migrate reset` on production, or hand-edit applied migrations.
- Do not create a client per request, query in loops, or `include` whole relations when a few fields will do.

## Before you finish

- Run `prisma validate` and `prisma format`, then `prisma generate`, then the type checker.
- After a schema change, run `prisma migrate dev --name <change>`, read the SQL for drops or table rewrites, and run `prisma migrate status`.
- Check new queries for an index on every column they filter or sort by, and list any migration that locks or rewrites a large table for the reviewer.

9,326 characters, 1,383 words. Under Windsurf's 12,000-character limit for a rule, as every prompt here is.