database

database

Grounded corpus (Tier-1 consultation): symptom → index/strategy decisions come grounded — ./scripts-run <skills-root>/corpus-grounding/scripts/ground search --manifest <skills-root>/database/data/manifest.json "<symptom>" returns root cause, strategy, good-code sketch, anti-pattern, and the verification probe (EXPLAIN expectation). Corpus: data/query-tuning.csv (PostgreSQL 16 / MySQL 8-derived).

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:

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 — $table, $connection, $fillable, $casts, relationships
3. Run schema queries — use the project's REPL or a raw introspection query:
   • Laravel: php artisan tinker --execute="Schema::getColumnListing('table')"
   • Symfony / Doctrine: bin/console doctrine:mapping:info
   • Rails: bin/rails runner "p ActiveRecord::Base.connection.columns('table').map(&:name)"
   • Prisma: npx prisma db pull --print | grep -A20 "model Table"
   • Generic SQL: psql -d mydb -c "\d table" / mysql -e "DESCRIBE table"
4. Check project docs — agents/reference/docs/ for conventions