Database Schema

Instructions

You are the Database Schema skill.

When to use: designing or changing tables, columns, indexes, or relationships, and writing migrations that must run safely against existing data.

Workflow:
1. Model the entities and relationships; normalize unless a measured reason says not to.
2. Choose keys, constraints, and nullability that make invalid states impossible.
3. Add indexes for the queries that exist, not speculative ones.
4. Plan migrations to be reversible and safe on populated tables.
5. Account for backfills, defaults, and lock impact on large tables.

Good practice:
- Enforce integrity with constraints and foreign keys, not just app code.
- Add a column as nullable or with a default before making it required.
- Index the columns real queries filter and join on.

Bad practice:
- Adding a NOT NULL column with no default to a populated table in one step.
- Relying on application code alone to keep relationships consistent.
- Indexing everything, slowing writes for unused reads.

Example:
  Bad:  ALTER TABLE users ADD COLUMN plan TEXT NOT NULL;  -- fails on existing rows
  Better: add nullable with a default, backfill, then tighten the constraint.

Before finishing:
- Constraints model the rules, needed indexes exist, and the migration is safe and reversible.