# AI-Coding-Regeln für PostgreSQL-Apps

> AGENTS.md-Regeln für PostgreSQL-basierte Apps, die Abfragesicherheit, Migrationsdisziplin, Indexierungskonventionen abdecken und verhindern, dass Agents N+1-Abfragen schreiben.

**Type:** Rule  
**Tools:** Cursor, Claude Code, Codex, Windsurf  
**Stack:** PostgreSQL, TypeScript, Next.js  
**Updated:** 2026-06-08

---

Legen Sie dies als `AGENTS.md` im Stammverzeichnis Ihres Repos ab. Es gilt für jedes Projekt, in dem PostgreSQL der primäre Datenspeicher ist, unabhängig davon, ob über Prisma, Drizzle, `postgres.js` oder reines SQL darauf zugegriffen wird.

## AGENTS.md

```md title="AGENTS.md"
# Project Rules — PostgreSQL Apps

## Hard rules — data safety
- NEVER generate SQL that concatenates user input into a query string. Always use
  parameterised queries (`$1`, `$2` placeholders) or the ORM's parameter binding.
  SQL injection via agent-generated queries is a real attack vector.
- NEVER run `DROP TABLE`, `TRUNCATE`, or `DELETE FROM <table>` without a `WHERE`
  clause in application code. These must be wrapped in a transaction that can be
  rolled back, and must require explicit developer confirmation before execution.
- NEVER write a migration that removes a column without first deploying a version of
  the application that stops reading that column. Column removal is a two-deploy
  operation: (1) stop reading; (2) drop.
- All schema changes must be applied via migration files (Drizzle Kit, Flyway,
  `migrate`, or Prisma Migrate). Never apply schema changes by running `ALTER TABLE`
  directly against production.

## Query conventions
- Every query that returns a list MUST have a `LIMIT`. There is no acceptable reason
  for a user-facing endpoint to return an unbounded result set.
- Use `EXPLAIN ANALYZE` to check query plans for new queries on tables with more than
  10,000 rows before merging. Agents should output the plan in a comment block when
  writing a non-trivial query.
- Avoid `SELECT *`. Name every column you need. This makes queries self-documenting
  and prevents accidental exposure of sensitive columns (hashed passwords, tokens).
- N+1 patterns are forbidden. If you load a list of records and then query related
  data per row in a loop, refactor to a single JOIN query or a batched `IN` query.
- Use `JOIN` instead of correlated subqueries for relation lookups — correlated
  subqueries run once per row and are almost always slower.

## Indexing rules
- Every foreign key column MUST have an index. PostgreSQL does not create these
  automatically (unlike MySQL). Missing FK indexes cause sequential scans on join.
- Columns used in `WHERE` clauses on high-read tables should have indexes. When
  adding a query that filters by a new column, check whether an index exists and
  add one in the same migration if it does not.
- Use partial indexes (`WHERE deleted_at IS NULL`) for soft-delete patterns — they
  are far smaller and faster than full-table indexes when most rows are soft-deleted.
- Never create an index on a column with very low cardinality (e.g. a boolean
  `is_active` with 90% `true`). PostgreSQL will ignore the index and scan anyway.

## Connection and pooling
- Import the database client from `src/lib/db.ts` (the singleton or pool). Never
  instantiate a new client per request — this exhausts connections instantly under load.
- In serverless environments (Next.js API routes, Edge Functions), use a connection
  pooler (PgBouncer, Neon connection pooling, Supabase pgbouncer mode) between the
  application and PostgreSQL. Direct connections from serverless are not sustainable.
- Set `statement_timeout` and `lock_timeout` on the connection or session for
  long-running operations to prevent them from blocking the entire application.

## Transactions
- Any operation that writes to more than one table must be wrapped in a transaction.
  Partial writes leave the database in an inconsistent state.
- Keep transactions as short as possible. Do not make network requests (HTTP calls,
  external APIs) inside a transaction — this holds locks for the duration of the
  network call.

## Definition of done
- No string-interpolated SQL in the codebase (`grep -r "query\`" src/` should return
  only parameterised template tag usage, not concatenation).
- All new tables have primary key and foreign key indexes.
- All `findMany` / `SELECT` queries have `LIMIT`.
- Migration files are committed and named descriptively.
- `tsc --noEmit` passes (typed query results match schema types).
```

## Warum diese Regeln

- **Kein SQL mit Zeichenkettenverkettung, jemals** ist die grundlegende Regel. Parametrisierte Abfragen sind nicht nur eine bewährte Praxis – sie sind die einzige Verteidigung gegen SQL-Injection, und Agents unter Zeitdruck werden verkettende Abfragen schreiben, wenn sie nicht wissen, wie sie eine Abfrage mit der Parametersyntax eines bestimmten ORMs ausdrücken sollen.
- **Das Entfernen einer Spalte ist ein Zwei-Deploy-Vorgang** verhindert den häufigsten Fehler bei Migrationen ohne Ausfallzeit. Wenn die Anwendung noch eine Spalte liest, während die Migration sie löscht, schlägt jede gerade laufende Anfrage zum Zeitpunkt der Migration fehl. Agents berücksichtigen beim Generieren von Schemaänderungen nicht von Natur aus Deployment-Fenster.

## Geeignet für

- Jede Produktionsanwendung, die PostgreSQL verwendet und bei der Datenintegrität und Abfrageleistung wichtig sind: SaaS-Apps, Content-Plattformen, E-Commerce-Backends, Analyse-Pipelines.

## Nicht geeignet

- SQLite-basierte Apps oder Projekte, bei denen die Datenbank wirklich flüchtig ist (Test-Fixtures, lokale Dev-Seeds) – die Migrationsdisziplin und Indexierungsregeln sind unnötiger Overhead.