DB Schema

Sub-Skill: Database Schema Design

Purpose: Prevent data-integrity bugs, race conditions, and irreversible migrations by enforcing database-level constraints and safe migration patterns before code reaches production.

Rules

Naming Conventions

1. Snake case everywhere. Always use snake_case for table names, column names, and index names; never use camelCase or PascalCase in SQL identifiers.
2. Plural table names. Always name tables in the plural (users, orders, line_items); singular names cause inconsistency across ORMs and query builders.
3. Consistent FK column naming. Always name foreign-key columns <referenced_table_singular>_id (e.g., user_id, order_id) so the reference is self-documenting.

Primary Keys

4. Surrogate primary key. Always define an explicit surrogate primary key (id) on every table; never rely on natural keys as the sole PK unless the domain guarantees immutability.
5. UUID vs serial. Prefer uuid PKs for distributed systems or public-facing IDs; prefer bigserial / bigint GENERATED ALWAYS AS IDENTITY for high-insert internal tables where sort order matters.

Foreign Keys & Referential Integrity

6. Declare FK constraints. Always declare FOREIGN KEY constraints in the schema; never rely on application-layer join logic to enforce referential integrity.
7. Explicit ON DELETE behaviour. Always specify ON DELETE behaviour (CASCADE, SET NULL, or RESTRICT) on every FK; omitting it defaults to RESTRICT, which can cause surprising errors at runtime.
8. Missing FK is a data-integrity bug. Never ship a column that references another table without a corresponding FK constraint — orphaned rows accumulate silently. Reference: ERR-2026-022

Indexes

9. Index every FK column. Always add an index on every foreign-key column; unindexed FKs cause full-table scans on joins and cascade operations.
10. Covering indexes for hot queries. Use covering indexes (include all columns in the SELECT) for read-heavy queries rather than adding redundant columns to the base table.
11. Unique constraints at DB level. Always enforce uniqueness at the database level rather than relying on application-layer deduplication; race conditions between concurrent writes bypass app checks. Reference: ERR-2026-022

Nullability & Defaults

12. Explicit NOT NULL. Always mark columns NOT NULL unless NULL carries distinct semantic meaning (unknown vs. absent); nullable columns complicate query logic and ORM mapping.
13. Meaningful defaults. Always supply a DEFAULT for columns that have a sensible zero-value (0, '', false, now()); avoid forcing callers to supply values the DB can compute.

Timestamps & Soft Delete

14. Standard audit columns. Always include created_at TIMESTAMPTZ NOT NULL DEFAULT now() and updated_at TIMESTAMPTZ NOT NULL DEFAULT now() on every table; omitting them makes debugging and auditing impossible.
15. Soft delete with deleted_at. Prefer deleted_at TIMESTAMPTZ over hard deletes for user-facing entities; ensure all queries filter WHERE deleted_at IS NULL or use a view/partial index.

Enum Columns

16. DB-native enum or check constraint. Always enforce enum-like columns with a CHECK constraint or a native ENUM type; never store unconstrained strings and validate only in the application.

Migrations

17. Up and down migrations. Always write both an up and a down migration; a rollback path is mandatory before any migration reaches production.
18. Idempotent migrations. Always guard DDL statements with IF NOT EXISTS / IF EXISTS so re-running a migration does not error; this is required for blue-green and multi-replica deployments.
19. Non-blocking index creation. Use CREATE INDEX CONCURRENTLY (Postgres) or equivalent for indexes on large tables; blocking index creation causes downtime.

Constraints Over App Logic

20. Check constraints for invariants. Always encode business invariants (e.g., price > 0, quantity >= 0) as CHECK constraints; application-layer validation is bypassed by direct DB writes, scripts, and migrations.

See also

• skills/code-quality/SKILL.md — general code-quality rules that apply to migration scripts
• skills/review-deployment/SKILL.md — deployment checklist that includes migration safety gates