| name | sql |
| description | SQL database patterns: schema migrations, query safety, index design, and transaction hygiene for this repo. |
| user-invocable | false |
| allowed-tools | [] |
SQL Domain Guide
This repo uses SQL databases. Apply these patterns.
Schema Migrations
- Every schema change is a migration file: never mutate the schema directly in production.
- Migrations must be reversible where possible — write a
down migration alongside up.
- Test migrations on a snapshot before applying to production.
- Never drop a column in the same migration that removes references to it — split into two deploys.
Query Safety
- Parameterize all user-supplied values. No string concatenation in queries.
SELECT * is banned in application code — list columns explicitly to avoid silent breakage on schema changes.DELETE and UPDATE without a WHERE clause must never appear in application code.- Wrap large data mutations in explicit transactions with rollback on error.
Index Design
- Add indexes for every foreign key if the ORM/framework doesn't do it automatically.
- Composite indexes: most selective column first.
- Partial indexes for filtered queries (e.g.,
WHERE deleted_at IS NULL).
- Do not add indexes speculatively — measure first with
EXPLAIN ANALYZE.
Transaction Hygiene
- Keep transactions short: no external HTTP calls inside an open transaction.
- Use
SERIALIZABLE isolation only when you can demonstrate the need — it increases contention.
- On deadlock: log the query, retry with backoff, escalate if persistent.
Migration Tools
- Diesel (Rust):
diesel migration run, diesel migration redo
- Alembic (Python):
alembic upgrade head, alembic downgrade -1
- Flyway:
flyway migrate, flyway validate
- Never mix migration tools on the same schema.
