database

Database Standards

Priority: P0 — PostgreSQL Correctness

Schema & Access

• Isolate persistence logic behind repositories or database services. Do not scatter raw database access across handlers.
• Model relations explicitly. Avoid redundant raw ID mirror fields when the ORM relation already expresses the dependency.
• Enforce integrity in the database with primary keys, foreign keys, NOT NULL, CHECK, and UNIQUE constraints.
• Prefer timestamptz for timestamps, text or unbounded varchar for strings, and numeric or integer minor units for money.

Migrations

• Never use synchronize: true in production.
• Use explicit migrations for every schema change.
• Follow expand-contract for destructive changes: add, backfill, deploy, then remove.
• Generated ORM migrations do not reliably capture Row-Level Security. Write RLS policies as explicit SQL.
• On hot tables, avoid blocking changes and prefer concurrent index creation where supported.

Queries & Performance

• Paginate every list query.
• Index every hot filter, join, and foreign-key column. Columns referenced by RLS predicates must be indexed.
• Use transactions for multi-step mutations.
• Prevent N+1 access patterns with joins, batching, or ORM query builders.
• Avoid SELECT * in application queries; project only the columns you need.

Priority: P0 — Redis Safety

Data Design

• Redis is a cache, queue, or coordination layer. It must not be the sole source of truth for critical business data.
• Namespace keys with colons, for example app:user:123.
• Every non-permanent key needs a TTL or a deliberate eviction strategy.
• Add TTL jitter for high-volume cache keys to avoid synchronized expirations.
• Prefer hashes for field-addressable objects over large JSON blobs.

Commands & Operations

• Never use KEYS in production. Use SCAN and related cursor-based commands.
• Use UNLINK instead of DEL for large-key deletion.
• Bound ZRANGE, LRANGE, and HGETALL usage. Do not fetch unbounded collections.
• Use pipelines for bulk operations and EVALSHA for atomic multi-step cache logic.
• Keep application and Redis in the same region and connect through stable hostnames.