delzarsolutionsllc Database Schema
The schema is the product. Migrations are how you tell the truth about it over time.
Method
- List the invariants: uniqueness, ownership, deletion, money, time.
- Tables named for the noun. Columns that need an index are the ones in WHERE/JOIN you will actually run — prove it with a query, not a guess.
- Foreign keys on by default. Soft-delete only when you can state the restore story.
- NOT NULL unless you have a real unknown. Default values that encode business rules belong in code and the constraint.
- Migrations are expand → backfill → contract. Never drop+rename in one step on a live table.
Red flags
- ENUM columns you will regret
- JSON blobs for data you will query
- Missing unique constraints "because the app checks"
- TIMESTAMP without time zone
Output the DDL, the indexes, the down migration, and the query you expect to be fast.