Download the PHP package karimalihussein/laravel-query-sentinel without Composer

Laravel Query Sentinel

Enterprise-grade SQL performance diagnostics engine for Laravel. Runs EXPLAIN ANALYZE, scores queries across 5 weighted dimensions, detects 10 SQL anti-patterns, synthesizes index recommendations, estimates memory pressure under concurrency, tracks regressions over time, and simulates hypothetical indexes — all from a single diagnose() call or an interactive Artisan command.

Table of Contents

• Requirements
• Installation
• Quick Start
• Configuration
• Analysis Modes
  • Mode 1: Raw SQL Analysis
  • Mode 2: Query Builder / Eloquent Analysis
  • Mode 3: Closure Profiling
  • Mode 4: Class Method Profiling
  • Mode 5: Full Deep Diagnostics
• Interactive Query Scanning
  • Setting Up DiagnoseQuery
  • Running the Scanner
  • How It Works
  • Writing Diagnosable Methods
• Automatic Profiling with Attributes
  • Controller Profiling (Middleware)
  • Service Class Profiling (Container Proxy)
  • Sampling and Thresholds
  • Fail on Critical
  • Structured Logging
• Console Commands
• Deep Diagnostic Features
  • 22-Step Analysis Pipeline
  • Cardinality Drift Detection
  • Anti-Pattern Detection
  • Index Synthesis
  • Confidence Scoring
  • Concurrency Risk Analysis
  • Memory Pressure Analysis
  • Regression Baselines
  • Hypothetical Index Simulation
  • Workload Pattern Detection
  • Plan Stability Analysis
• Report Reference
  • Report Object (Single Query)
  • DiagnosticReport Object (Full Diagnostics)
  • ProfileReport Object (Multiple Queries)
  • Grading System
  • Scoring Components
  • Metrics Extracted
• Built-in Rules
• Custom Rules
• Extension Points
• Architecture
• Testing
• License

Requirements

• PHP 8.2+
• Laravel 10, 11, or 12
• MySQL 8.0.18+ (for EXPLAIN ANALYZE) or PostgreSQL

Installation

Development only — Install as a dev dependency. It will not be present in production when you run composer install --no-dev.

The service provider is auto-discovered. To publish the configuration:

Two facades are registered automatically:

• QuerySentinel (primary)
• QueryDiagnostics (backward-compatible alias)

Quick Start

Configuration

After publishing, edit config/query-diagnostics.php:

Environment Variables

Analysis Modes

Mode 1: Raw SQL Analysis

Analyze a raw SQL string. Validated for safety (only SELECT/WITH), sanitized, and run through EXPLAIN ANALYZE.

Safety: Only read-only SQL is accepted. Destructive statements throw UnsafeQueryException.

Mode 2: Query Builder / Eloquent Analysis

Analyze a Builder instance without executing it. SQL and bindings are extracted via toSql() / getBindings().

Mode 3: Closure Profiling

Profile all database queries inside a closure. Captures via DB::listen(), wraps in transaction (rolled back), and analyzes each SELECT.

Safety: Transaction is always rolled back. No writes persist.

Mode 4: Class Method Profiling

Profile a class method resolved from the Laravel container.

Mode 5: Full Deep Diagnostics

Run the full 22-step diagnostic pipeline with all deep analyzers.

Interactive Query Scanning

The query:scan command discovers methods annotated with #[DiagnoseQuery], presents an interactive list, and runs full EXPLAIN ANALYZE diagnostics on the selected query builder.

Setting Up DiagnoseQuery

Add the #[DiagnoseQuery] attribute to methods that return a Query Builder:

Running the Scanner

Example interactive session:

How It Works

1. Scan — Finder locates PHP files containing DiagnoseQuery in configured paths (app/, Modules/)
2. Reflect — PHP Reflection discovers annotated methods and extracts metadata
3. Select — Developer picks a method from the interactive list
4. Resolve — Class is resolved from the Laravel container (DI works normally)
5. Execute — Method is called inside DB::beginTransaction() to get the Builder
6. Rollback — Transaction is immediately rolled back (no side effects)
7. Extract — SQL and bindings are extracted from the Builder via toSql() / getBindings()
8. Diagnose — Full Engine::diagnose() pipeline runs EXPLAIN ANALYZE + all deep analyzers
9. Report — Full diagnostic report is rendered to the console

Writing Diagnosable Methods

The annotated method must:

• Return an Eloquent\Builder or Query\Builder instance
• Not execute the query (no ->get(), ->paginate(), ->first())
• Have no required parameters (all params must be optional or have defaults)

If your production method takes parameters, create a dedicated diagnosis method:

Configure which directories to scan:

Automatic Profiling with Attributes

The #[QueryDiagnose] attribute enables zero-code-change runtime profiling. Place it on any controller or service method to automatically capture, analyze, and log query performance during normal execution.

Two different attributes for two different purposes:

• #[DiagnoseQuery] — Interactive CLI scanning (returns a Builder, used with query:scan)
• #[QueryDiagnose] — Runtime profiling (captures queries during execution, logs results)

Controller Profiling (Middleware)

Register the middleware:

Apply to routes:

Add the attribute:

Methods without the attribute pass through with zero overhead.

Service Class Profiling (Container Proxy)

Register service classes in config:

Add attributes to methods:

When the service is resolved from the container, it is wrapped in a MethodInterceptor proxy that intercepts attributed methods and forwards everything else directly.

Sampling and Thresholds

Sampling controls how often profiling activates:

Effective rate: min(methodRate, globalRate).

Thresholds filter logging noise:

Effective threshold: max(methodThreshold, globalDefault).

Fail on Critical

Throw PerformanceViolationException on critical performance issues:

Triggers when: worst grade is D/F, any query > 500ms, full table scan, or N+1 detected.

Structured Logging

Profiled invocations are logged as structured JSON:

Log levels: error (D/F), warning (C or N+1), info (A/B).

Console Commands

query:diagnose — Analyze Raw SQL

query:scan — Interactive Builder Diagnosis

Console Report Output

CI Integration

Deep Diagnostic Features

When using Engine::diagnose() or query:diagnose / query:scan, the full 22-step pipeline runs automatically.

22-Step Analysis Pipeline

Cardinality Drift Detection

Compares optimizer row estimates against actual rows from EXPLAIN ANALYZE. Large deviations indicate stale statistics.

Config: cardinality_drift.warning_threshold (default 0.5), cardinality_drift.critical_threshold (default 0.9).

Anti-Pattern Detection

Static SQL analysis for 10 common performance anti-patterns:

Config: anti_patterns.or_chain_threshold (default 3), anti_patterns.missing_limit_row_threshold (default 10000).

Index Synthesis

Recommends optimal composite indexes using the ERS principle (Equality, Range, Sort, Select columns):

Config: index_synthesis.max_recommendations (default 3), index_synthesis.max_columns_per_index (default 5).

Confidence Scoring

Attaches a trustworthiness score (0-1.0) to the analysis based on 8 weighted factors:

Labels: high (90%+), moderate (70-89%), low (50-69%), unreliable (<50%).

When confidence is low, findings are automatically downgraded (Critical to Warning at <70%, Critical/Warning down one level at <50%).

Concurrency Risk Analysis

Evaluates lock contention, deadlock potential, and isolation impact:

Memory Pressure Analysis

Estimates query memory footprint under concurrency:

Network pressure levels: LOW (<50MB), MODERATE (50-100MB), HIGH (100-200MB), CRITICAL (>200MB).

Regression Baselines

Tracks query performance over time. Each diagnose() call saves a snapshot. Subsequent runs compare against the baseline to detect regressions.

Smart regression detection:

• Normalizes for data growth (if rows grew >20%, checks per-row cost instead)
• Ignores sub-millisecond timing jitter (noise floor: 3ms)
• Detects plan changes (access type downgrades like ref to ALL)

Config: regression.score_warning_threshold (default 10%), regression.time_warning_threshold (default 50%).

Hypothetical Index Simulation

Creates temporary indexes, runs EXPLAIN, compares before/after, then drops them. Only runs in local/testing environments.

Improvement levels: significant (access type improved), moderate (>50% row reduction), marginal (>10%), none.

Workload Pattern Detection

Tracks query execution patterns over time to detect systemic issues:

Config: workload.frequency_threshold (default 10), workload.export_row_threshold (default 100K).

Plan Stability Analysis

Detects optimizer plan flip risk from estimation deviations:

Report Reference

Report Object (Single Query)

Returned by analyzeSql() and analyzeBuilder():

DiagnosticReport Object (Full Diagnostics)

Returned by diagnose():

ProfileReport Object (Multiple Queries)

Returned by profile() and profileClass():

Grading System

Score modifiers:

• Context override promotes to A (95+) when: LIMIT-optimized + covering index + no filesort + <10ms
• Dataset dampening applies log10 formula for large unbounded result sets
• Confidence gating caps grade when analysis confidence is low

Scoring Components

Metrics Extracted

Built-in Rules

Custom Rules

Extend BaseRule:

Register in config:

Extension Points

Custom Drivers

Implement DriverInterface for other databases:

Custom Scoring Engine

Implement ScoringEngineInterface:

Architecture

Design Principles

• Framework-agnostic core — QueryAnalyzer operates on SQL strings with no Laravel dependency
• Lazy adapter loading — Adapters instantiated only when their Engine methods are called
• Safety first — ExecutionGuard blocks destructive SQL; ProfilerAdapter wraps in transaction+rollback
• Zero overhead — Non-attributed methods pass through with only a cached reflection lookup
• Confidence-aware — All findings are gated by analysis confidence; low confidence auto-downgrades severity
• Root-cause-aware — Generic index findings suppressed when the real issue is function wrapping or leading wildcard

Testing

License

MIT