Evidence-Grounded Eloquent Query Performance Investigation Brief

Investigate the supplied Laravel performance target and produce an evidence-grounded Eloquent query performance brief. Trace the complete data-access path, distinguish measured problems from hypotheses, compare optimization trade-offs, and define safe verification. Do not edit files or execute commands unless the supplied permissions explicitly authorize those actions.

## Investigation inputs

### Blocking prerequisites

- Target and reproducible trigger: [Investigation target]
- Relevant application, schema, and query-path material: [Code and schema evidence]
- Safe environment, available tools, and execution permissions: [Test and verification environment]
- Files, commands, databases, and changes that are permitted or prohibited: [Permissions and change boundaries]

If the target cannot be identified, the relevant code cannot be inspected, or no safe verification environment is available, stop before diagnosis and request the missing prerequisite. A static review may proceed without runtime access only if it is explicitly labeled as static and unmeasured.

### Supporting context

- Laravel, PHP, database engine and version, queue/runtime topology, table cardinalities, tenant model, and representative data characteristics: [Runtime and data profile]
- Existing traces, query logs, response or job timings, memory measurements, test results, service-level objective, and target threshold: [Baseline evidence and performance target]

When supporting context is absent, preserve it as unknown. Do not invent cardinality, selectivity, query counts, execution plans, latency, memory use, cache behavior, or production equivalence. Ask for clarification when conflicting inputs could change the diagnosis—for example, different database engines, Laravel versions, tenant scopes, or pagination semantics. Otherwise, continue with bounded hypotheses and state what evidence would confirm or reject each one.

## Codex access and action rules

Codex may inspect supplied files and, when repository or shell access is actually available, trace references and run only explicitly permitted, non-destructive commands in the approved environment. If access is unavailable, analyze only the material supplied and provide commands for a human to run. Never imply that Codex opened a file, executed a query, measured a baseline, changed code, or passed a test unless the corresponding evidence exists in the session.

Do not mutate production data or schema, deploy, merge, approve, clear shared caches, restart services, run broad load tests, or use destructive Git or database commands. Do not run `EXPLAIN ANALYZE`, unbounded diagnostic queries, full-table scans, or high-volume profiling against production without explicit authorization and an assessed resource budget. Redact credentials, tokens, personal data, raw session content, and sensitive query bindings from the brief.

Treat migrations, new indexes, cache introduction, queue changes, pagination-semantic changes, denormalization, and production profiling as separate proposals requiring human review. Preserve authorization, policies, global scopes, tenant isolation, soft-delete behavior, visibility filters, ordering, serialization shape, totals, and consistency requirements. Stop and escalate if a proposed optimization could weaken any of them or if the available environment is not safe for verification.

## Evidence vocabulary

Assign each material item an evidence ID and classify it as one of the following:

- **Supplied fact:** stated in the inputs but not independently observed.
- **Static observation:** directly supported by a cited file, symbol, migration, configuration value, or test.
- **Execution evidence:** produced by an authorized command, trace, query log, plan, or measurement; include environment and command or capture method.
- **Hypothesis:** plausible explanation awaiting a discriminating check.
- **Assumption:** temporary premise required to continue.
- **Unknown:** unavailable information that affects confidence.
- **Conflict:** incompatible evidence that must be reconciled.

Cite file paths and symbols for static observations. For runtime evidence, record environment, dataset representativeness, warm or cold state, sample count, statistic used, query capture method, and timestamp when available. Do not treat Laravel Debugbar, Telescope, Clockwork, database logs, APM traces, or test query listeners as interchangeable; note their capture scope and overhead.

Use only these work states: **proposed**, **authorized but not executed**, **executed with evidence**, **blocked**, and **not applicable**. Reserve **measured**, **verified**, **fixed**, and **tests passed** for executed work with cited before-and-after or test evidence. Never claim deployment, approval, merge, or production validation unless explicit evidence is supplied.

## Investigation workflow

### 1. Establish the measurement contract

Define the user-visible or operational trigger, expected behavior, performance metric, threshold, representative dataset, concurrency assumptions, cache state, database state, number of samples, and comparison method. Separate application latency from database time, serialization time, network time, queue delay, and client rendering where evidence permits. If no usable baseline exists, design one rather than describing improvement.

### 2. Trace the Laravel execution path

Map the route, middleware, controller or invokable action, form or DTO input, service or repository, model builders, local and global scopes, policies, tenant constraints, relationship methods, accessors or casts, API resources, Blade components, Livewire or Inertia serialization, events, observers, queued jobs, and tests that influence the target.

Record where a query builder is created, cloned, constrained, executed, and hydrated. Check for hidden query triggers in accessors, appended attributes, resource conditionals, policy checks, collection callbacks, view loops, model events, and serialization. Include package-owned behavior only when evidenced.

### 3. Build a query inventory

For each distinct query fingerprint, record call site, execution count, bindings shape with sensitive values removed, selected columns, joins or subqueries, filters, order, limit, returned rows, scanned rows when known, duration distribution, and consumers. Look specifically for:

- relationship or polymorphic N+1 behavior, including nested resources and `morphTo` loading;
- lazy loading hidden by accessors, policies, Blade, resources, or appended attributes;
- repeated `count`, `exists`, aggregate, authorization, tenant, or lookup queries;
- correlated `withCount` or `withExists` subqueries whose cost scales poorly;
- unbounded `get`, `all`, relationship hydration, large `IN` lists, and memory-heavy collection transforms;
- accidental over-fetching, duplicated model hydration, broad eager loads, and selected-column mistakes that omit relationship keys;
- non-sargable predicates, leading-wildcard searches, functions or casts on indexed columns, implicit type conversions, and `OR` conditions that defeat useful access paths;
- expensive joins, `whereHas` or nested existence checks, `distinct`, grouping, sorting, temporary tables, and filesorts;
- offset pagination degradation and cursor pagination incompatibility with non-unique or unstable ordering;
- lock waits, connection-pool pressure, read/write routing, transaction scope, replica lag, or queue amplification when relevant.

Do not label a code pattern an N+1 incident without execution evidence or a precise static trigger showing query count scales with result size. If only static evidence exists, call it an N+1 risk and prescribe a query-count check.

### 4. Evaluate Eloquent remedies

Compare the smallest behavior-preserving options: constrained `with`, nested eager loading, `morphWith`, `loadMissing`, `withCount`, `withExists`, aggregate joins, precomputed maps, moving invariant work outside loops, column projection, chunking, lazy iteration, simple pagination, or cursor pagination. Check each option for query count, total database work, row multiplication, memory use, model-event behavior, serialization shape, and scope preservation.

Verify that projected parent and child columns retain primary and foreign keys needed for relationship matching. Avoid replacing an N+1 pattern with one enormous eager load or an aggregate query that performs worse at real cardinality. Treat `Model::preventLazyLoading` as a development or test diagnostic whose environment behavior must be reviewed, not as proof that all query paths are safe.

For pagination, compare offset, simple, and cursor semantics. Cursor pagination requires deterministic ordering with a unique tie-breaker and may affect page-number navigation, totals, deep links, and concurrent-write behavior. Record these product-level trade-offs instead of presenting it as a mechanical replacement.

### 5. Evaluate database access paths

Inspect migrations or authoritative schema evidence before recommending an index. Reconcile model casts and query bindings with actual column types and collations. For each candidate index, connect its column order to equality predicates, range predicates, joins, and ordering; consider the database engine's leftmost-prefix behavior, selectivity, covering potential, existing overlapping indexes, index width, write amplification, storage, lock or online-build implications, and rollback strategy.

Interpret `EXPLAIN` in the context of the named database engine and version. Capture plan fields relevant to that engine, estimated versus actual rows only when safely available, access method, chosen and possible indexes, join order, sort or temporary operations, and predicate filtering. A plan estimate alone does not prove latency improvement. Never recommend dropping an index solely because it was not selected in one captured plan.

### 6. Evaluate caching only after query remedies

For every cache candidate, define the value, key dimensions, tenant and user isolation, authorization sensitivity, TTL or freshness contract, invalidation events, transaction timing, tag support, stampede control, negative caching behavior, payload size, backend limits, observability, failure behavior, and rollback. Reject caching when invalidation cannot preserve required correctness, when keys could leak cross-tenant data, or when it would conceal an unbounded query.

### 7. Rank and sequence remedies

Rank findings by evidence strength, expected impact, behavior risk, operational risk, implementation effort, and verification cost. Prefer reversible code-level changes backed by focused tests before schema or cache changes. Separate independent remedies so their effects can be measured. Include stop conditions and a rollback trigger for every executable recommendation.

### 8. Design verification and acceptance

For each proposed change, specify the exact safe command or capture procedure, prerequisites, expected observation, actual observation if executed, evidence ID, acceptance threshold, and unresolved state. Use project-discovered PHPUnit or Pest test paths and actual route or command names; do not fabricate them. Verification should cover, where applicable:

- stable query fingerprints and reduced query-count growth across at least two result sizes;
- before-and-after latency and memory under equivalent dataset, cache, runtime, and sampling conditions;
- plan changes and row-access evidence for index proposals;
- response schema, ordering, filters, totals, null handling, and pagination boundaries;
- policy, tenant, soft-delete, visibility, and user-specific behavior;
- cache hit, miss, invalidation, isolation, stale-data, stampede, and backend-failure behavior;
- job chunk boundaries, retries, idempotency, and peak memory;
- targeted tests plus the smallest relevant regression suite.

If results are noisy, contradictory, not comparable, or below the declared threshold, report the remedy as unverified and prescribe reconciliation. Do not convert an absent regression into proof of performance improvement.

## Required deliverable

Produce the following markdown sections.

### A. Intake and investigation state

State the target, trigger, blocking gaps, useful unknowns, permissions, environment safety, and whether the brief is static-only or includes execution evidence. Include a ledger with columns: Evidence ID, Classification, Source or command, Environment, Observation, Limitations.

### B. Measurement contract

Use columns: Metric, Baseline method, Dataset and cache state, Sample plan, Target threshold, Existing evidence, Comparability risks. Record absent baselines explicitly.

### C. Laravel execution and query-trigger map

Use columns: Stage, File and symbol, Builder or relationship, Execution trigger, Scope or authorization effect, Evidence ID, Performance concern. Trace through rendering, serialization, events, or jobs when relevant.

### D. Query fingerprint inventory

Use columns: Query ID, Call site, Pattern and execution count, Rows returned or examined, Timing evidence, Scaling factor, Hydration or memory effect, Evidence ID. Redact sensitive bindings and mark unavailable values unknown.

### E. Findings and discriminating checks

Use columns: Finding ID, Classification, Evidence IDs, Mechanism, Expected impact, Confidence, Behavior risk, Check that confirms or rejects it. Separate confirmed issues, hypotheses, assumptions, unknowns, and conflicts.

### F. Eloquent remedy decision matrix

Use columns: Finding ID, Candidate remedy, Query and memory trade-off, Scope or serialization risk, Pagination or consistency effect, Required files, Verification, Recommendation. Include rejected alternatives and why they are inferior for this target.

### G. Database plan and index review

Use columns: Query ID, Current plan evidence, Candidate index or rewrite, Column-order rationale, Overlap and write cost, Build and rollback risk, Required approval, Decision state. Keep schema changes proposed unless execution was explicitly authorized and evidenced.

### H. Cache suitability and isolation review

Use columns: Candidate, Key dimensions, Freshness contract, Invalidation events, Tenant and permission isolation, Stampede or failure handling, Observability, Decision. State clearly when caching is unsuitable.

### I. Prioritized remediation sequence

Provide numbered, independently measurable steps. For each step include finding IDs, work state, permitted action, expected effect, behavior risk, stop condition, rollback trigger, and human approval point. Begin with baseline and characterization evidence.

### J. Verification and acceptance matrix

Use columns: Change or hypothesis, Exact command or procedure, Preconditions, Expected observation, Actual observation, Evidence ID, Acceptance threshold, Result state. If nothing was run, every actual observation must say not executed and results must remain proposed or blocked.

### K. Completion and handoff register

List files changed, commands run, tests run, measurements captured, migrations proposed, cache changes proposed, approvals obtained, and deployments performed. For each item give its work state and evidence ID. Use `none` or `not executed` where appropriate; never infer completion. Finish with unresolved risks, evidence still needed, confidence by finding, responsible human decisions, and the safest next action.

Before returning the brief, reconcile all findings with their evidence IDs, ensure every advertised improvement has an acceptance check, and remove any completion verb that is unsupported by execution evidence.