mkdatabaselisted

# Database — Schema, Migrations, Query Optimization Provides reference-backed guidance for database design tasks. PostgreSQL is the primary target; most patterns apply to MySQL and SQLite with minor syntax differences. ## When to Use - Designing a new schema or adding tables/columns - Writing migration files (up + down) - Optimizing slow queries - Adding indexes - Triggers: "database schema", "migration", "query optimization", "indexing", "SQL", "N+1" ## Phase Anchor **Phase: 1 (Plan)** for schema design and migration planning **Phase: 3 (Build)** for implementation and query writing **Handoff:** Developer implements, reviewer validates per `references/migration-patterns.md` safety checklist ## Process ### Step 1: Identify Task Type Determine which task is being requested: | Task | Load Reference | |------|---------------| | Schema design (new tables, relationships) | `references/schema-design.md` | | Migration (up/down, rollback, zero-downtime) | `references/migration-patterns.md` | | Query writing or optimization | `references/query-optimization.md` | | Multiple tasks | Load all relevant references | ### Step 2: Identify Database Type Check the project for database markers: | Marker | Database | |--------|----------| | `postgres://` or `postgresql://` in env/config | PostgreSQL | | `mysql://` or `mysql2` package | MySQL | | `sqlite3` package or `.sqlite` file | SQLite | | `mongodb://` or `mongoose` | MongoDB | If PostgreSQL or unknown → use PostgreSQL syntax (m