Database standards

How we build in this area. Deviations are fine, but say why in the pull request.

Naming

• Tables are singular: member, skill, assessment.
• Primary key is always id.
• Foreign keys are <table>_id.
• Timestamps are created_at / updated_at.
• Booleans read as a statement: is_primary, archived.

Keys

• Use a generated id (cuid) as the primary key. Do not make a natural key the primary key — emails and names change.
• Put a unique constraint on anything that must be unique anyway, such as (member_id, skill_id).

Migrations

• Every schema change ships as a migration. No manual edits to a live database.
• A migration must be safe to run while the old code is still serving:
  1. add the column as nullable,
  2. backfill,
  3. make it required in a later migration.
• Never rename and reuse a column in one step.

Queries

• No N+1. If you are querying inside a loop, stop and write a join.
• Always know what your WHERE clause matches before an UPDATE or DELETE.
• Index foreign keys and anything you filter or sort by regularly.
• Measure before and after adding an index. An index is not free — it costs on every write.

Transactions

• Wrap multi-statement writes that must succeed or fail together.
• Keep transactions short. Never hold one open across a network call.

Review checklist

• Migration is reversible, or the risk is stated
• New foreign keys are indexed
• No query inside a loop
• Deletes are either soft or have a considered cascade
• The query plan was checked for anything on a hot path