git:20260926.9bc2d60 to git:20261001.cc31a37
11 added, 110 removed. Audit A to A.
---
name: bosskuai-database-engineering
description: Use this for SQL and NoSQL database design across MariaDB, MySQL, PostgreSQL, SQLite, MongoDB, indexing and index design, query plans, transactions and locking, safe online migrations, constraints, multi-tenant schemas, connection pooling, backups, and data correctness.
---
- # BosskuAI Database Engineering
-
- Use this skill when schema, query behavior, indexes, migrations, locking, data consistency, or database choice affects the outcome.
-
- ## How this differs from nearby skills
-
- - **`bosskuai-data-architecture`**: entity ownership, warehouses, pipelines, retention; this skill is the operational database underneath.
- - **`bosskuai-performance-profiling`**: finds that the database is the bottleneck; this skill fixes the query, index, or schema.
- - **`bosskuai-mongodb`** / **`bosskuai-redis-caching-queues`**: engine-specific depth; this skill covers relational engines in depth and MongoDB at design level.
- - **`bosskuai-tenant-isolation-security`**: authorization boundaries; this skill provides the schema and index shape that make isolation cheap.
- - **`bosskuai-laravel-development`** / **`bosskuai-go-development`**: the application's data-access layer; this skill owns what the engine does with it.
- - **`mysql-patterns`** / **`database-migrations`** (ecc): MySQL/MariaDB engine specifics and ORM migration files; this skill owns engine-neutral design and the online-migration plan.
-
- ## Mindset
-
- - The invariant lives in the database: constraints and unique indexes, not only application code.
- - An index is a write cost and a storage cost paid to make one access path cheap; know which path.
- - A plan is evidence; "it should be fast" is not. `EXPLAIN` before and after.
- - Migrations run against production traffic; every step must be safe while the old code is still running.
- - Test on the production engine: SQLite in tests hides MySQL/PostgreSQL behavior.
-
- ## Orient before changing anything
-
- 1. Engine and version (`SELECT version()`), and whether tests run on a different engine.
- 2. Migration tool and history; live schema (`\d+ table`, `SHOW CREATE TABLE`); existing indexes and their usage (`pg_stat_user_indexes`, `sys.schema_unused_indexes`).
- 3. Top queries by total time (`pg_stat_statements`, slow query log, APM) and the tables they hit.
- 4. Tenancy model, soft deletes, status columns, audit tables, and any triggers or guards that block bulk updates.
-
- ## Index design rules
-
- - Composite order: equality columns first, then the range column, then `ORDER BY` columns; the leftmost prefix is what gets used.
- - Cover the hot read (`INCLUDE` in PostgreSQL, extra trailing columns in MySQL) when the query is index-only critical.
- - Selectivity matters: an index on `status` with three values rarely helps alone; combine with the selective column or use a partial index (`WHERE status = 'active'`).
- - Unique indexes encode invariants, including soft-delete uniqueness (`UNIQUE (email) WHERE deleted_at IS NULL`, or a generated column on MySQL).
- - PostgreSQL does not index foreign key columns automatically; MySQL does. Add them or deletes on the parent table lock and scan.
- - Multi-tenant: `tenant_id` leads every composite index on tenant-scoped tables; queries always filter by it.
- - JSON: index extracted keys (generated column + B-tree) or `jsonb_path_ops` GIN; do not index whole documents by reflex.
- - Text search: `pg_trgm` GIN for `LIKE '%x%'`, `tsvector` for full text, `FULLTEXT` on MySQL; `LIKE '%x%'` on a B-tree never uses it.
- - Remove unused and duplicate indexes; each one slows every write and bloats vacuum.
-
- ## Reading a plan
-
- - Seq/full scan on a large table with a selective filter → missing or unusable index (function on column, type mismatch, leading wildcard).
- - Nested loop with a large outer set → missing index on the inner join key or wrong join order from bad statistics; run `ANALYZE`.
- - Sort or filesort without an index → add the `ORDER BY` columns to the index or paginate by key.
- - Estimated rows far from actual → stale statistics, correlated columns (extended statistics), or parameter sniffing.
- - High buffers/temp usage → work memory too small for the sort/hash or the query needs to touch less data.
-
- ## Query patterns that scale
-
- - N+1: eager load or batch by IDs; look for query counts per request in the ORM log.
- - Pagination: keyset (`WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC`) over `OFFSET` beyond a few pages.
- - Counts on large tables: cached counters or estimates (`pg_class.reltuples`) for UI badges, exact counts only where money depends on them.
- - Queues in the database: `SELECT ... FOR UPDATE SKIP LOCKED` with a bounded batch; separate table from business rows.
- - Idempotency: unique key on the idempotency token; upsert (`ON CONFLICT` / `ON DUPLICATE KEY UPDATE`) instead of select-then-insert.
- - Deadlocks: acquire locks in a consistent order, keep transactions short, retry on serialization failure.
- - Bulk writes: batch of 500–5,000 rows per statement, chunked by primary key ranges, sleep between chunks under load.
-
- ## Online migration ladder (expand → migrate → contract)
-
- 1. Add nullable column or new table; deploy code that writes both.
- 2. Backfill in primary-key chunks with a resumable script; never one `UPDATE` on the whole table.
- 3. Add constraints without locking: PostgreSQL `NOT VALID` then `VALIDATE CONSTRAINT`; `CREATE INDEX CONCURRENTLY` (outside a transaction); MySQL 8 `ALGORITHM=INPLACE` or `INSTANT` for column adds, `gh-ost`/`pt-online-schema-change` for the rest.
- 4. Switch reads; deploy code that stops writing the old column.
- 5. Drop the old column or table in a later release, after backups.
- - Renames are add + backfill + swap, never `RENAME COLUMN` under traffic.
- - Adding a column with a constant default is instant on PostgreSQL 11+ and MySQL 8 `INSTANT`; a volatile default rewrites the table.
- - Every migration states its lock impact and its rollback; forward-only migrations need a documented recovery.
- - Finance and audit tables with guard triggers: bulk data rebases must disable or route around the guards deliberately, never by dropping constraints.
-
- ## Multi-tenancy shapes
-
- | Shape | Pick when | Cost |
- |---|---|---|
- | Shared schema + `tenant_id` | many small tenants, one codebase | row-level discipline, RLS optional, noisy-neighbor risk |
- | Schema per tenant | moderate tenant count, per-tenant migrations acceptable | migration fan-out, connection/pool sprawl, cross-tenant reporting harder |
- | Database per tenant | compliance isolation, large tenants | operations multiply; automation mandatory |
-
- Connection pooling: pgbouncer transaction mode breaks prepared statements, advisory locks, and `SET` per session; size pools per service against `max_connections`.
-
- ## Operational baseline
-
- - Backups: automated, encrypted, off-box, with point-in-time recovery; restore drill quarterly with the time-to-restore recorded.
- - Autovacuum tuned for hot tables; watch dead tuples and bloat; `REINDEX CONCURRENTLY` when needed.
- - Slow query log or `pg_stat_statements` on; alert on connections near limit, replica lag, storage headroom, lock waits.
- - Time stored in UTC (`timestamptz`); application time zone set once at the edge.
- - Least-privilege database users per service; no application user with DDL rights in production.
-
- ## Guardrails
-
- - Do not solve missing constraints only in application code.
- - Do not add indexes without checking selectivity, write cost, and the plan; do not leave unused ones.
- - Do not run a whole-table `UPDATE` or a locking DDL against production without a chunked or online plan.
- - Do not use MongoDB as a shortcut for unclear relational modeling.
- - Do not assume MySQL, MariaDB, PostgreSQL, and SQLite support the same DDL or locking.
- - Do not claim a query is fast without the plan on production-sized data.
+ # Database engineering
- ## Output format
+ Use when schema, indexes, query plans, locking, or a migration affects correctness or scale.
- ```text
- Database engine: [engine/version] - Tests run on: [same | different engine]
- Invariant to protect: [rule]
- Schema/query findings:
- P0/P1/P2 — [issue] — [fix]
- Index plan: [index + access path it serves + write cost note]
- Migration plan: [expand → backfill → contract steps, lock impact, rollback]
- Verification: [EXPLAIN before/after, constraints checked, restore drill date]
- ```
+ 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.
- ## References
+ 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.
- - `../../references/playbooks/bosskuai-database-engineering-playbook.md`
- - `../../references/checklists/database-engineering-checklist.md`
- - `../../references/checklists/tenant-isolation-security-checklist.md`
+ Index and plan-reading detail, the full online-migration ladder, multi-tenancy shapes, and the operational baseline: `reference.md`.