Skip to content

project rulesDatabasespostgres

Postgres SQL Rules

Project rules for PostgreSQL SQL: naming, column types, constraints, indexes, reversible migrations and queries that stay safe and fast.

npx workspacetemplates add postgres-sql-rules --tool claude-code

Writes 1 file to your project root

CLAUDE.mdadd to your file23 lines · 1.4 KBFile
# Project rules: PostgreSQL

## Naming and types
- Use lowercase snake_case, plural table names and singular column names. Primary keys are `id`; foreign keys are `<table>_id`.
- Use `timestamptz` for times, `numeric` for money, `uuid` or `bigint generated always as identity` for keys, `text` with a check instead of arbitrary varchar limits, and `jsonb` only for truly flexible data.
- Never use `float` for currency, or `timestamp` without a zone.

## Constraints
- Declare `NOT NULL`, `UNIQUE`, `CHECK` and `FOREIGN KEY` in the schema. Choose `ON DELETE` behavior deliberately. Add `created_at` and `updated_at` columns.

## Indexes
- Index foreign keys and the columns used in frequent filters and sorts. Use partial and composite indexes where the query shape justifies them. Create them with `CREATE INDEX CONCURRENTLY` on live tables.
- Check plans with `EXPLAIN (ANALYZE, BUFFERS)` before claiming a query is fast.

## Migrations
- One change per migration, with a down step where possible. Add columns as nullable, backfill in batches, then add the constraint with `NOT VALID` and `VALIDATE`. Set a `lock_timeout`. Do not rewrite big tables in one transaction.

## Queries
- Always use bound parameters. List columns instead of `SELECT *`. Page with keyset pagination for large sets, and use `ON CONFLICT` for upserts.
- Wrap multi-statement changes in a transaction.

## Checks
- Run the migration on a copy of the schema and lint SQL with sqlfluff or squawk.

What it does

These rules set how an agent writes schemas and queries for PostgreSQL. The aim is a database that enforces its own rules, so the application cannot quietly store bad data.

They also cover migrations on live tables and common query mistakes, which are where most Postgres incidents start.

Example

you › Add an orders table linked to customers with a status and a total

agent › The agent writes a migration with a bigint identity key, a customer_id foreign key and index, a status check constraint, numeric total and timestamptz columns, then runs it against a scratch database.

Questions

Why prefer text over varchar(n)?
Postgres stores them the same way. A check constraint states the real limit and is easier to change later.
Does it cover ORMs?
The schema and migration advice applies whatever tool generates the SQL. Review generated migrations against these points.
  • MongoDB Rules

    Project rulesmongodb

    Free
  • MySQL Rules

    Project rulesmysql

    Free
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Migration Safety

    SkillDatabases

    Free