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

1. 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.
2. 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

1. Declare FK constraints. Always declare FOREIGN KEY constraints in the schema; never rely on application-layer join logic to enforce referential integrity.
2. 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.
3. 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

1. Index every FK column. Always add an index on every foreign-key column; unindexed FKs cause full-table scans on joins and cascade operations.
2. 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.
3. 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