shiro-neko/src/skills-md/db.md

name, description

name	description
db	Design schemas, write migrations, or fix query and data problems. Use when adding a table, writing a migration, debugging a slow query, or choosing keys and indexes.

Databases

The schema is the hardest thing to change in the whole system. Design it for the queries, not the object model.

Keys and constraints are the real schema

• Every table has a primary key; prefer a surrogate id unless a natural key is truly stable.
• Foreign keys and NOT NULL are not optional decoration — they are the constraints that stop bad data at the door instead of in application code six months later.
• Unique constraints belong on the thing that must be unique (email, slug), enforced by the database, not by a check-then-insert that races.

Migrations are one-way and additive where possible

• Never edit a migration that has run anywhere. Add a new one.
• Destructive changes (drop column, rename, change type) are two migrations: add the new shape, deploy code that writes both, then remove the old in a later release. A single migration that renames a column breaks every old copy of the app still running.
• Test a migration against real data volume. ALTER on ten rows is instant; on ten million it locks the table.

Indexes follow the queries

Index the columns you filter and join on, in the order the query uses them. A composite index (a, b) serves WHERE a and WHERE a, b but not WHERE b alone. Read the query plan (EXPLAIN) before adding one — a guess is an index that costs writes and serves nothing.

The N+1 is the default bug

A query per row in a loop is the most common database performance defect. Fetch the set with a join or a batched WHERE id IN (...). If a page does one query per item, that is the fix before any caching.