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

Dump to the Evidence Report (source-discovery)

When the task touches schema-driven work, this dump is the DB surface of the source-discovery discipline. Record it to the gitignored session cache with provenance, framework-neutral (MySQL / Postgres / SQLite; ORM-agnostic):

• Capture tables, columns, types, primary/foreign/unique keys, indexes, relations, and the derived filter/sort/group-ability — each with observed_at / source (migration:line or the introspection command).
• In-codebase = local, read fresh, no card. A schema defined by repo migrations / models / ORM / app code (including schemaless stores the app controls — Mongoose / Prisma / Firestore rules) is always resolved locally and re-read each task. The migration is intended truth, the live DB is actual — surface any divergence as a drift signal.
• Only negative facts graduate to a committed card (agents/knowledge/): "searched, column/table does not exist" after an exhausted search. Positive structure stays in the session Evidence Report, re-read fresh — never a card.
• A DB-not-in-codebase (vendor SaaS / partner / legacy, schema not in the repo and not app-controlled) is the only DB that may be card-worthy.

Conventions

→ See guideline php/database.md for indexing, transactions, migrations, multi-connection patterns.

Output format

1. Migration file or query change with EXPLAIN analysis
2. Index recommendations with rationale

Gotcha

MySQL and MariaDB share a query-syntax world. They never share a migration one. Treat them as one engine for writing a SELECT, and as two engines for everything that changes a schema or reports on a plan: online-DDL semantics, lock behavior under ALTER, feature availability, and EXPLAIN output all diverge. Read the engine and its version from the project before making any claim in that second group; where the project does not declare one, say the engine is unknown rather than assuming.

• Check existing indexes before adding — duplicates waste write performance.
• Consider multi-tenant implications — queries may need customer DB scoping.
• EXPLAIN output varies between MariaDB and MySQL.
• Don't use TEXT in WHERE without prefix index.

Do NOT

• Do NOT guess table/column names — verify against migrations or models first.
• Do NOT add indexes without checking existing ones — duplicates waste write performance.
• Do NOT use float for money — use decimal.

Auto-trigger keywords

• database
• MariaDB
• MySQL
• migration
• indexing
• query optimization