databaselisted

# database ## When to use Use when designing schemas, optimizing queries, adding indexes, or troubleshooting database performance. Do NOT use when: - Writing framework-specific ORM models (use the matching skill — e.g. `eloquent` for Laravel, `symfony-workflow` for Doctrine, framework-native skill for Prisma / TypeORM / SQLAlchemy / GORM / Diesel) - Creating migrations only — use the framework-specific migration skill (`laravel-migration` for Laravel, framework-native for others) ## Procedure: Optimize a query ### Step 0: Inspect 1. Read project docs in `agents/reference/docs/` for database architecture. 2. Check `config/database.php` for connection definitions. 3. Detect engine: check `.env` driver and `docker-compose.yml`. ### Step 1: Diagnose Run `EXPLAIN` / `EXPLAIN ANALYZE`: ```sql EXPLAIN ANALYZE SELECT * FROM projects WHERE customer_id = 42 AND status = 'active'; ``` Check for: full table scans (`type=ALL`), missing indexes (`key=NULL`), filesort, temporary tables. ### Step 2: Fix - Add missing indexes (most selective column first in composites) - Rewrite anti-patterns (subquery → JOIN, `OFFSET` → cursor, `SELECT *` → specific columns) - Add eager loading for N+1 queries - Always paginate list endpoints ### Step 3: Verify Re-run `EXPLAIN` and confirm improved plan. ## Schema awareness (anti-hallucination) **Never guess table or column names.** Verify before writing queries/migrations: 1. **Read migrations** — source of truth 2. **Read models** — `$tabl