# Regras de Codificação IA para Aplicações PostgreSQL

> Regras do AGENTS.md para aplicações com PostgreSQL, abrangendo segurança de consultas, disciplina de migração, convenções de indexação e prevenção de que agentes escrevam consultas N+1.

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

---

Coloque isso na raiz do seu repositório como `AGENTS.md`. Isso se aplica a qualquer projeto onde PostgreSQL seja o armazenamento de dados principal, seja acessado via Prisma, Drizzle, `postgres.js` ou SQL puro.

## 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).
```

## Por que essas regras

- **Nunca, jamais, concatenar strings em SQL** é a regra fundamental. Consultas parametrizadas não são apenas uma boa prática — são a única defesa contra injeção de SQL, e agentes sob pressão de tempo concatenarão quando não souberem como expressar uma consulta com a sintaxe de parâmetros de um ORM específico.
- **Remoção de colunas é uma operação de dois deploys** evita o erro mais comum em migrações com zero downtime. Se a aplicação ainda lê uma coluna quando a migração a remove, toda solicitação em andamento no momento da migração falha. Agentes naturalmente não raciocinam sobre janelas de deploy ao gerar mudanças de esquema.

## Boa adequação

- Qualquer aplicação em produção usando PostgreSQL onde integridade de dados e desempenho de consultas importam: aplicações SaaS, plataformas de conteúdo, backends de e-commerce, pipelines de análise.

## Não adequado

- Aplicações baseadas em SQLite ou projetos onde o banco de dados é verdadeiramente efêmero (fixtures de teste, seeds de desenvolvimento local) — a disciplina de migração e as regras de indexação são sobrecarga desnecessária.