Database

Model schemas for clarity, migrate in safe increments, and optimize queries around real access patterns rather than guesswork.

When to Activate

Object	Convention
Tables	plural `snake_case`
Columns	singular `snake_case`
Primary keys	`id`
Foreign keys	`<table>_id`
Join tables	alphabetical plural pair, e.g. `roles_users`

Change	Safe Pattern	Avoid
Drop column	add replacement, backfill, dual-read, dual-write, drop later	destructive one-step drop
Rename column	add new column and migrate gradually	instant rename in hot path
Add constraint	backfill invalid rows first	applying constraint to dirty data

BAD

ALTER TABLE users DROP COLUMN full_name;

GOOD

ALTER TABLE users ADD COLUMN display_name text;
UPDATE users SET display_name = full_name WHERE display_name IS NULL;

Query Pattern	Index
Equality on foreign key	single-column btree
Filter + sort	composite index ordered by filter then sort
Case-insensitive search	functional index matching query function

Signal	Fix
One query per row in a list view	eager load or batch fetch
Repeated lookup in serializer loop	prefetch related data
ORM debug logs showing repeated shapes	consolidate access path

Preferred	Avoid
Short transaction around consistent write set	Holding transaction open across network calls
Retry on known transient conflicts	Blind retries on all DB errors

Use Soft Delete When	Use Hard Delete When
Audit or recovery matters	Regulation or retention requires full erasure
References must remain historically intact	Data is ephemeral and unreferenced