Database standards

Naming, migrations, keys and the rules we hold each other to in review.

Development standardUpdated 21 days ago (29 Sept 2026) by Developer B

Download .md Done editing

Title

Type

How we build in this area: the conventions and decisions the team works to.

Summary

Body

# 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

Markdown with tables, code blocks and task lists.