tpo-database-design-rules

Start by identifying product entities such as User, Organization, and Project, then define ownership, lifecycle states, relationships, and sensitivity for each. Use the entity design template and data model planning template to document tables, indexes, migrations, and query patterns.

Use plural snake_case for tables like users or project_members, snake_case for columns like created_at, id as the primary key, and [entity]_id for foreign keys. Pick one naming style and apply it consistently across the schema.

Every schema change must be a version-controlled, reviewed, and reversible migration. Before approval, verify it runs on production-sized data, avoids locking critical tables, backfills new not-null fields, has a rollback plan, and is tested in staging.

Use foreign keys in relational databases to protect referential integrity, since application validation can be bypassed by bugs or scripts. Always define explicit delete behavior such as RESTRICT, CASCADE, SET NULL, or soft delete.

Reject designs that store everything in JSON without reason, lack foreign keys for critical relationships, guess indexes without query patterns, leave deletes undefined, or skip audit logs for sensitive actions. Sensitive data must always have access-control and retention rules.