bosskuai-database-engineering git:20261012.17c6803A

Immutable. This exact content never changes and is served at /api/v1/blob/f2d38b1da254911b.

---
name: bosskuai-database-engineering
description: "Use when SQL or NoSQL database design affects correctness or scale (MariaDB, MySQL, PostgreSQL, SQLite, MongoDB): indexing, query plans, transactions and locking, safe online migrations, constraints, multi-tenant schemas, connection pooling, backups."
---

# Database engineering

Use when schema, indexes, query plans, locking, or a migration affects correctness or scale.

1. **Orient before changing anything**: engine and version — confirm tests run on the *same* engine, not SQLite standing in for MySQL/PostgreSQL — the live schema and migration history, existing indexes and their usage stats, and the top queries by total time.
2. **Name the invariant you're protecting** and encode it in the database as a unique or check constraint, not only in application code.
3. **Get the plan before touching an index.** `EXPLAIN`/`EXPLAIN ANALYZE` first; a query is not "fast" or "slow" without one, before or after.
4. **Design composite indexes equality columns first, then the range column, then `ORDER BY`**; lead every tenant-scoped index with `tenant_id`; check selectivity before adding; remove unused and duplicate indexes.
5. **Never run a whole-table `UPDATE` or a locking DDL directly against a production-sized table.** Use expand → chunked backfill → non-locking constraint → switch reads → contract, and state each step's lock impact and rollback.
6. **Scope every tenant query and composite index by `tenant_id`**, and check ownership on update/delete, not only on read.
7. **Re-run the plan after the change** and compare before/after; confirm backups and the restore path still work if schema changed.

For availability and recovery work, also check:

- **Restore, not just backup.** A backup never restored is a hope. Schedule restore tests and record the real time-to-restore against the agreed RPO and RTO.
- **Drill failover** on a schedule, and fix what the drill exposes. Do not promote a lagging replica without knowing what data would be lost.
- **Replication lag is a correctness issue:** monitor it, and gate reads that need read-after-write on it.
- **Budget locks for each migration:** check the engine's actual lock mode (even metadata-only changes can need an exclusive lock), set a short lock timeout, keep scans and backfills out of the locking transaction, and have a retry plan.
- **Protect the database from clients:** use a pooler and per-service connection limits so one buggy client cannot exhaust connections.
- **Heavy or destructive operations** (migrations, failover, large deletes) get a written back-out plan and a blast-radius estimate first.

Check before you finish: tests ran on the production engine or the gap is stated, every new foreign key on PostgreSQL has an explicit index, no invariant lives only in app code, and no migration shipped without a stated rollback.

Index and plan-reading detail, the full online-migration ladder, multi-tenancy shapes, and the operational baseline: `reference.md`.

Availability and recovery checks adapted in part from engineering/engineering-database-reliability-engineer.md (agency-agents, MIT).