postgres-conventions

name	postgres-conventions
description	PostgreSQL convention lens consulted BEFORE writing schema/migrations — indexing, constraints, row-level security, and timestamp/type hygiene. Appended to impl-conventions.txt by the stack-conventions pack. Also usable standalone.

postgres-conventions

When: consulted by plan-writer when the plan adds a table, column, index, or migration, and by implement-feature while writing the schema/migration, in projects where the stack-conventions pack is installed. Complements the migration-safety review lens (that catches unsafe change shapes after the fact; this shapes the schema up front).

Scope: schema and migration the change introduces. Advisory knowledge, makes no edits.

Conventions

• Keys: every table has an explicit primary key. Prefer a surrogate (bigint generated always as identity, or uuid) unless a natural key is truly stable. Foreign keys carry an explicit references + on delete policy — never leave the cascade behaviour implicit.
• Indexes: index every foreign key and every column a query filters/sorts on in a hot path. Composite index column order = most-selective / equality-first, range last. Add a unique index for any real uniqueness invariant (don't enforce it only in app code). Name indexes predictably (idx_<table>_<cols>).
• Row-level security: for multi-tenant / per-user tables, enable RLS (alter table … enable row level security) and write policies keyed on the tenant/owner column. Whitelist the allowed principal (policy using (tenant_id = current_tenant())) — never a deny-list of bad actors. RLS is enforcement, not a substitute for authorization checks in the app; it is the backstop when a query forgets one.
• Constraints over app-only rules: not null, check, unique, and FK constraints encode invariants where they cannot be bypassed. If a rule matters, the DB should hold it too.
• Types & time: timestamptz (never bare timestamp) for instants; store UTC. text over varchar(n) unless a length limit is a real domain rule. numeric for money, never float. Enums via a lookup table or PG enum — not free-form strings.
• Migrations: additive and reversible. New non-null column ships with a default or a backfill + a later set not null; never a bare not null add on a populated table. One logical change per migration.

Applying

• In plan-writer: record indexes, constraints, RLS policies, and the code↔snake_case mapping (see naming-conventions) in Technical Decisions, not left to implement-time guesswork.
• In implement-feature: match the existing migration tool and file convention already in the repo before writing a new migration.