Database Engineering
Schema design, safe migration generation, query optimization, and data lifecycle management. Multi-DB: PostgreSQL, MySQL, SQLite, MongoDB. Multi-ORM: SQLAlchemy, Prisma, TypeORM, Drizzle, Entity Framework, Diesel.
When to Use
- Designing or modifying database schemas.
- Planning safe migrations with rollback.
- Optimizing slow queries.
- Defining retention policies or archival strategies.
- NOT for infrastructure provisioning -- use
/ai-infra.
Modes
design -- Schema Design
- Analyze data model -- entities, relationships, access patterns, data volume, growth projections.
- Apply normalization -- 3NF+ by default. Document denormalization decisions with rationale.
- Design schema -- tables, indexes, constraints, partitioning for large tables.
- Validate referential integrity -- every FK has a matching PK, cascade rules defined.
- Output: DDL script + entity relationship description.
migrate -- Safe Migrations
- Assess impact -- locking impact, backward compatibility, data volume affected.
- Use expand-contract -- for breaking changes (add new, migrate data, drop old).
- Generate forward migration -- with explicit transaction boundaries.
- Generate rollback migration -- ALWAYS required. No migration ships without rollback.
- Test migration -- verify on representative data volume.
- Output: forward script, rollback script, execution plan.
optimize -- Query Optimization
- Analyze execution plan --
EXPLAIN ANALYZE (PostgreSQL), EXPLAIN (MySQL).
- Identify bottlenecks -- sequential scans, missing indexes, N+1 patterns.
- Recommend indexes -- composite indexes based on query patterns, partial indexes for filtered queries.
- Connection pool tuning -- pool size, timeout, idle connection management.
- Output: optimized query, index recommendations, before/after execution plan.
lifecycle -- Data Lifecycle
- Retention policies -- define per-table retention based on regulatory requirements.
- Archival strategies -- partition-based archival, cold storage migration.
- GDPR compliance -- right to erasure procedures, data anonymization.
- Multi-DB architecture -- read replicas, caching layers, write distribution.
- Output: lifecycle policy document, archival procedures.
Quick Reference
/ai-schema design # schema design with normalization
/ai-schema migrate # safe migration with rollback
/ai-schema optimize # query optimization with EXPLAIN
/ai-schema lifecycle # retention and archival policies
Common Mistakes
- Shipping migrations without rollback scripts -- always generate both.
- Adding indexes without checking write impact -- indexes speed reads but slow writes.
- Denormalizing without documenting why -- future developers will re-normalize.
- Running DDL without
--dry-run first -- destructive DDL requires explicit user approval.
Integration
- Migration files integrate with ORM migration systems (Alembic, Prisma Migrate, EF Migrations).
- Schema changes trigger
/ai-security for injection pattern review.
- Destructive DDL (DROP, TRUNCATE) requires explicit user approval.
References
.ai-engineering/manifest.yml -- governance rules for destructive operations.
$ARGUMENTS
1---2name: schema-43description: Use when designing schemas, writing migrations, optimizing queries, or managing data lifecycle across PostgreSQL, MySQL, SQLite, and MongoDB.4---5
6
7
8# Database Engineering
9
10Schema design, safe migration generation, query optimization, and data lifecycle management. Multi-DB: PostgreSQL, MySQL, SQLite, MongoDB. Multi-ORM: SQLAlchemy, Prisma, TypeORM, Drizzle, Entity Framework, Diesel.
11
12## When to Use
13
14- Designing or modifying database schemas.
15- Planning safe migrations with rollback.
16- Optimizing slow queries.
17- Defining retention policies or archival strategies.
18- NOT for infrastructure provisioning -- use `/ai-infra`.
19
20## Modes
21
22### design -- Schema Design
23
241. **Analyze data model** -- entities, relationships, access patterns, data volume, growth projections.
252. **Apply normalization** -- 3NF+ by default. Document denormalization decisions with rationale.
263. **Design schema** -- tables, indexes, constraints, partitioning for large tables.
274. **Validate referential integrity** -- every FK has a matching PK, cascade rules defined.
285. **Output**: DDL script + entity relationship description.
29
30### migrate -- Safe Migrations
31
321. **Assess impact** -- locking impact, backward compatibility, data volume affected.
332. **Use expand-contract** -- for breaking changes (add new, migrate data, drop old).
343. **Generate forward migration** -- with explicit transaction boundaries.
354. **Generate rollback migration** -- ALWAYS required. No migration ships without rollback.
365. **Test migration** -- verify on representative data volume.
376. **Output**: forward script, rollback script, execution plan.
38
39### optimize -- Query Optimization
40
411. **Analyze execution plan** -- `EXPLAIN ANALYZE` (PostgreSQL), `EXPLAIN` (MySQL).
422. **Identify bottlenecks** -- sequential scans, missing indexes, N+1 patterns.
433. **Recommend indexes** -- composite indexes based on query patterns, partial indexes for filtered queries.
444. **Connection pool tuning** -- pool size, timeout, idle connection management.
455. **Output**: optimized query, index recommendations, before/after execution plan.
46
47### lifecycle -- Data Lifecycle
48
491. **Retention policies** -- define per-table retention based on regulatory requirements.
502. **Archival strategies** -- partition-based archival, cold storage migration.
513. **GDPR compliance** -- right to erasure procedures, data anonymization.
524. **Multi-DB architecture** -- read replicas, caching layers, write distribution.
535. **Output**: lifecycle policy document, archival procedures.
54
55## Quick Reference
56
57```
58/ai-schema design # schema design with normalization
59/ai-schema migrate # safe migration with rollback
60/ai-schema optimize # query optimization with EXPLAIN
61/ai-schema lifecycle # retention and archival policies
62```
63
64## Common Mistakes
65
66- Shipping migrations without rollback scripts -- always generate both.
67- Adding indexes without checking write impact -- indexes speed reads but slow writes.
68- Denormalizing without documenting why -- future developers will re-normalize.
69- Running DDL without `--dry-run` first -- destructive DDL requires explicit user approval.
70
71## Integration
72
73- Migration files integrate with ORM migration systems (Alembic, Prisma Migrate, EF Migrations).
74- Schema changes trigger `/ai-security` for injection pattern review.
75- Destructive DDL (DROP, TRUNCATE) requires explicit user approval.
76
77## References
78
79- `.ai-engineering/manifest.yml` -- governance rules for destructive operations.
80$ARGUMENTS
majiayu000