What this prompt does
This turns Claude Code into a database performance auditor that reads your actual repository instead of guessing from generic rules. It runs four ordered phases: it traces relationships and controller/service methods to catch N+1 queries, parses your migrations to reconstruct the real schema and cross-references it against every where, orderBy, groupBy, join, and having clause to find missing indexes, scans for anti-patterns like SELECT *, unbounded whereIn, and get() where cursor() or chunk() belongs, then applies ORM-specific checks for your stack.
It works because the phases mirror how a real audit proceeds — you can't judge an index without the schema, and you can't size an N+1 without tracing the call path. The forced output table (Severity, File:Line, Issue, Current Code, Fixed Code, Impact Estimate) stops the model at vague advice: every finding must point to a line and ship a fix. The eager-load suggestions, generated index migrations, and A-F health grade are all things you can act on immediately, not a lecture on best practices.
When to use it
- A page or endpoint got slow and your query count is creeping up under load.
- Before a launch or traffic spike, to catch the N+1s that only hurt at scale.
- After inheriting a codebase and you need a fast map of its worst data-access debt.
- Your slow query log keeps flagging tables, and you want the missing composite indexes written for you.
- Reviewing a large feature branch that added new relationships, loops, or reports.
- Memory spikes on a job that loads big result sets with
get()instead of streaming.
Example output
Query Health: C+
| Severity | File:Line | Issue | Current | Fixed | Impact |
|----------|-----------|-------|---------|-------|--------|
| Critical | OrderController.php:42 | N+1 on $order->customer in loop | foreach($orders as $o){ $o->customer->name } | Order::with('customer')->get() | ~51 → 2 queries/page |
| High | 2026_..._orders.php | No index on (status, created_at) — used in 7 where+orderBy | — | $table->index(['status','created_at']) | full scan on 1.2M rows |
| Medium | ReportService.php:88 | get() loads 40k rows into memory | Invoice::get() | Invoice::cursor() | avoids OOM on exports |
Generated migration: database/migrations/2026_add_orders_status_created_index.php
Pro tips
- Set
[model_directory]and[migration_directory]to real paths (e.g.app/Models,database/migrations) so Phase 1 and 2 read source instead of inventing structure. A wrong path quietly produces shallow results. - Use
[additional_concern]as your escape hatch for stack-specific traps: "loadCount()/->count()on a relation inside a loop", "polymorphic morph maps", "Scout reindex queries", or "chunkByIdvschunkon deleting jobs". - Pair it with proof: run
DB::enableQueryLog()(or your framework's profiler) on the flagged endpoint to confirm the before/after query counts the model estimated — never ship an index purely on its say-so. - Treat generated index migrations as drafts. Review cardinality and column order yourself; for an equality-plus-range query, put the equality column first, and a composite index only helps when its leading column matches the filter you actually use.
- Run it per-domain on large repos (one module at a time). Scoping
[framework]plus a tighter directory beats a single whole-repo pass that runs out of attention before Phase 4.
