PHPackages                             karimalihussein/laravel-query-sentinel - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. karimalihussein/laravel-query-sentinel

ActiveLibrary[Database & ORM](/categories/database)

karimalihussein/laravel-query-sentinel
======================================

A driver-agnostic, CI-ready, extensible Laravel package for analyzing database query performance

v1.0.1(4mo ago)01[1 PRs](https://github.com/karimalihussein/laravel-query-sentinel/pulls)MITPHPPHP ^8.2CI passing

Since Feb 23Pushed 3mo agoCompare

[ Source](https://github.com/karimalihussein/laravel-query-sentinel)[ Packagist](https://packagist.org/packages/karimalihussein/laravel-query-sentinel)[ Docs](https://github.com/karimalihussein/laravel-query-sentinel)[ RSS](/packages/karimalihussein-laravel-query-sentinel/feed)WikiDiscussions main Synced today

READMEChangelogDependencies (9)Versions (5)Used By (0)

Laravel Query Sentinel
======================

[](#laravel-query-sentinel)

[![CI](https://github.com/karimalihussein/laravel-query-sentinel/actions/workflows/ci.yml/badge.svg)](https://github.com/karimalihussein/laravel-query-sentinel/actions/workflows/ci.yml)[![Security](https://github.com/karimalihussein/laravel-query-sentinel/actions/workflows/security.yml/badge.svg)](https://github.com/karimalihussein/laravel-query-sentinel/actions/workflows/security.yml)

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
-----------------

[](#table-of-contents)

- [Requirements](#requirements)
- [Installation](#installation)
- [Quick Start](#quick-start)
- [Configuration](#configuration)
- [Analysis Modes](#analysis-modes)
    - [Mode 1: Raw SQL Analysis](#mode-1-raw-sql-analysis)
    - [Mode 2: Query Builder / Eloquent Analysis](#mode-2-query-builder--eloquent-analysis)
    - [Mode 3: Closure Profiling](#mode-3-closure-profiling)
    - [Mode 4: Class Method Profiling](#mode-4-class-method-profiling)
    - [Mode 5: Full Deep Diagnostics](#mode-5-full-deep-diagnostics)
- [Interactive Query Scanning](#interactive-query-scanning)
    - [Setting Up DiagnoseQuery](#setting-up-diagnosequery)
    - [Running the Scanner](#running-the-scanner)
    - [How It Works](#how-it-works)
    - [Writing Diagnosable Methods](#writing-diagnosable-methods)
- [Automatic Profiling with Attributes](#automatic-profiling-with-attributes)
    - [Controller Profiling (Middleware)](#controller-profiling-middleware)
    - [Service Class Profiling (Container Proxy)](#service-class-profiling-container-proxy)
    - [Sampling and Thresholds](#sampling-and-thresholds)
    - [Fail on Critical](#fail-on-critical)
    - [Structured Logging](#structured-logging)
- [Console Commands](#console-commands)
- [Deep Diagnostic Features](#deep-diagnostic-features)
    - [22-Step Analysis Pipeline](#22-step-analysis-pipeline)
    - [Cardinality Drift Detection](#cardinality-drift-detection)
    - [Anti-Pattern Detection](#anti-pattern-detection)
    - [Index Synthesis](#index-synthesis)
    - [Confidence Scoring](#confidence-scoring)
    - [Concurrency Risk Analysis](#concurrency-risk-analysis)
    - [Memory Pressure Analysis](#memory-pressure-analysis)
    - [Regression Baselines](#regression-baselines)
    - [Hypothetical Index Simulation](#hypothetical-index-simulation)
    - [Workload Pattern Detection](#workload-pattern-detection)
    - [Plan Stability Analysis](#plan-stability-analysis)
- [Report Reference](#report-reference)
    - [Report Object (Single Query)](#report-object-single-query)
    - [DiagnosticReport Object (Full Diagnostics)](#diagnosticreport-object-full-diagnostics)
    - [ProfileReport Object (Multiple Queries)](#profilereport-object-multiple-queries)
    - [Grading System](#grading-system)
    - [Scoring Components](#scoring-components)
    - [Metrics Extracted](#metrics-extracted)
- [Built-in Rules](#built-in-rules)
- [Custom Rules](#custom-rules)
- [Extension Points](#extension-points)
- [Architecture](#architecture)
- [Testing](#testing)
- [License](#license)

---

Requirements
------------

[](#requirements)

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

Installation
------------

[](#installation)

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

```
composer require --dev karimalihussein/laravel-query-sentinel
```

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

```
php artisan vendor:publish --tag=query-sentinel-config
```

Two facades are registered automatically:

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

Quick Start
-----------

[](#quick-start)

```
use QuerySentinel\Facades\QuerySentinel;

// Analyze a raw SQL query
$report = QuerySentinel::analyzeSql('SELECT * FROM users WHERE email = ?');

echo $report->grade;          // 'A'
echo $report->compositeScore; // 92.5
echo $report->passed;         // true

// Analyze an Eloquent builder (without executing it)
$builder = User::where('status', 'active')->select('id', 'name');
$report = QuerySentinel::analyzeBuilder($builder);

// Profile all queries in a closure (transaction-wrapped, rolled back)
$profile = QuerySentinel::profile(function () {
    $users = User::with('posts', 'comments')->paginate(15);
});

echo $profile->totalQueries;      // 3
echo $profile->nPlusOneDetected;  // false
echo $profile->worstGrade();      // 'B'

// Full deep diagnostics (22-step pipeline)
$diagnostic = QuerySentinel::diagnose('SELECT * FROM orders WHERE status = "pending"');

echo $diagnostic->effectiveGrade();       // Confidence-adjusted grade
echo $diagnostic->memoryPressure;         // Memory footprint analysis
echo $diagnostic->concurrencyRisk;        // Lock contention risk
echo count($diagnostic->findings);        // Severity-sorted findings
```

---

Configuration
-------------

[](#configuration)

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

```
return [

    // Database driver: 'mysql', 'pgsql', or 'sqlite'
    'driver' => env('QUERY_SENTINEL_DRIVER', 'mysql'),

    // Database connection name (null = default)
    'connection' => env('QUERY_SENTINEL_CONNECTION'),

    // Scoring weights (must sum to 1.0)
    'scoring' => [
        'weights' => [
            'execution_time'  => 0.30,
            'scan_efficiency' => 0.25,
            'index_quality'   => 0.20,
            'join_efficiency' => 0.15,
            'scalability'     => 0.10,
        ],
        'grade_thresholds' => [
            'A' => 90, 'B' => 75, 'C' => 50, 'D' => 25, 'F' => 0,
        ],
    ],

    // Rules to enable
    'rules' => [
        'enabled' => [
            \QuerySentinel\Rules\FullTableScanRule::class,
            \QuerySentinel\Rules\TempTableRule::class,
            \QuerySentinel\Rules\WeedoutRule::class,
            \QuerySentinel\Rules\DeepNestedLoopRule::class,
            \QuerySentinel\Rules\IndexMergeRule::class,
            \QuerySentinel\Rules\StaleStatsRule::class,
            \QuerySentinel\Rules\LimitIneffectiveRule::class,
            \QuerySentinel\Rules\QuadraticComplexityRule::class,
            \QuerySentinel\Rules\NoIndexRule::class,
        ],
    ],

    // Performance thresholds
    'thresholds' => [
        'max_execution_time_ms' => 1000,
        'max_rows_examined'     => 100_000,
        'max_loops'             => 10_000,
        'max_cost'              => 1_000_000,
        'max_nested_loop_depth' => 4,
    ],

    // Scalability projection targets
    'projection' => [
        'targets' => [1_000_000, 10_000_000],
    ],

    // Attribute-based automatic profiling (#[QueryDiagnose])
    'diagnostics' => [
        'enabled'              => env('QUERY_SENTINEL_DIAGNOSTICS_ENABLED', true),
        'global_sample_rate'   => (float) env('QUERY_SENTINEL_SAMPLE_RATE', 1.0),
        'default_threshold_ms' => (int) env('QUERY_SENTINEL_THRESHOLD_MS', 0),
        'classes' => [
            // Service classes to auto-profile:
            // \App\Services\LeadQueryService::class,
        ],
    ],

    // Interactive query scanning (#[DiagnoseQuery])
    'scan' => [
        'paths' => ['app', 'Modules'],
    ],

    // Deep analysis feature configs
    'cardinality_drift' => [
        'warning_threshold'  => 0.5,
        'critical_threshold' => 0.9,
    ],

    'anti_patterns' => [
        'or_chain_threshold'          => 3,
        'missing_limit_row_threshold' => 10000,
    ],

    'index_synthesis' => [
        'max_recommendations'   => 3,
        'max_columns_per_index' => 5,
    ],

    'memory_pressure' => [
        'high_threshold_bytes'     => 268435456,   // 256MB
        'moderate_threshold_bytes' => 67108864,     // 64MB
        'concurrent_sessions'      => 10,
    ],

    'hypothetical_index' => [
        'enabled'              => false,
        'max_simulations'      => 3,
        'timeout_seconds'      => 5,
        'allowed_environments' => ['local', 'testing'],
    ],

    'workload' => [
        'enabled'                 => true,
        'frequency_threshold'     => 10,
        'export_row_threshold'    => 100_000,
        'network_bytes_threshold' => 52428800,  // 50MB
    ],

    'regression' => [
        'enabled'                  => true,
        'storage_path'             => null,  // defaults to storage_path('query-sentinel/baselines')
        'max_history'              => 10,
        'score_warning_threshold'  => 10,
        'score_critical_threshold' => 25,
        'time_warning_threshold'   => 50,
        'time_critical_threshold'  => 200,
        'noise_floor_ms'           => 3,
        'minimum_measurable_ms'    => 5,
    ],
];
```

### Environment Variables

[](#environment-variables)

VariableDefaultDescription`QUERY_SENTINEL_DRIVER``mysql`Database driver (`mysql`, `pgsql`)`QUERY_SENTINEL_CONNECTION``null`Database connection name`QUERY_SENTINEL_DIAGNOSTICS_ENABLED``true`Enable attribute-based profiling`QUERY_SENTINEL_SAMPLE_RATE``1.0`Global profiling sample rate (0.0-1.0)`QUERY_SENTINEL_THRESHOLD_MS``0`Global minimum cumulative time to log`QUERY_SENTINEL_FAIL_ON_CRITICAL``false`Throw exception on critical findings---

Analysis Modes
--------------

[](#analysis-modes)

### Mode 1: Raw SQL Analysis

[](#mode-1-raw-sql-analysis)

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

```
use QuerySentinel\Facades\QuerySentinel;

$report = QuerySentinel::analyzeSql(
    "SELECT u.*, COUNT(p.id) as post_count
     FROM users u
     LEFT JOIN posts p ON p.user_id = u.id
     WHERE u.status = 'active'
     GROUP BY u.id
     ORDER BY post_count DESC
     LIMIT 20"
);

echo $report->grade;                        // 'B'
echo $report->compositeScore;               // 78.4
echo $report->result->metrics['rows_examined'];  // 15000
echo $report->result->metrics['has_filesort'];   // true

foreach ($report->recommendations as $rec) {
    echo "- {$rec}\n";
}
```

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

### Mode 2: Query Builder / Eloquent Analysis

[](#mode-2-query-builder--eloquent-analysis)

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

```
$builder = User::query()
    ->where('status', 'active')
    ->where('created_at', '>=', now()->subDays(30))
    ->select('id', 'name', 'email');

$report = QuerySentinel::analyzeBuilder($builder);
echo $report->grade;  // 'A'
```

### Mode 3: Closure Profiling

[](#mode-3-closure-profiling)

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

```
$profile = QuerySentinel::profile(function () {
    $users = User::with(['posts', 'comments'])->where('active', true)->get();
    foreach ($users as $user) {
        $user->updateQuietly(['last_seen' => now()]);
    }
});

echo $profile->totalQueries;       // 12
echo $profile->analyzedQueries;    // 3 (SELECTs only)
echo $profile->nPlusOneDetected;   // false
echo $profile->worstGrade();       // 'C'
echo $profile->slowestQuery->result->executionTimeMs;  // 18.5
```

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

### Mode 4: Class Method Profiling

[](#mode-4-class-method-profiling)

Profile a class method resolved from the Laravel container.

```
$profile = QuerySentinel::profileClass(
    \App\Services\LeadQueryService::class,
    'getFilteredLeads',
    [$filterDTO, $page = 1],
);

echo $profile->totalQueries;
echo $profile->worstGrade();
```

### Mode 5: Full Deep Diagnostics

[](#mode-5-full-deep-diagnostics)

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

```
$diagnostic = QuerySentinel::diagnose(
    "SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50"
);

// Confidence-adjusted results
echo $diagnostic->effectiveGrade();          // 'B' (may differ from base grade)
echo $diagnostic->effectiveCompositeScore(); // 82.3

// Deep analysis sections (all nullable — available when analyzers are enabled)
$diagnostic->environment;          // Server config (buffer pool, InnoDB settings)
$diagnostic->executionProfile;     // Nested loops, B-tree depths, complexity
$diagnostic->cardinalityDrift;     // Estimation accuracy per table
$diagnostic->antiPatterns;         // 10 SQL anti-pattern detections
$diagnostic->indexSynthesis;       // ERS-ordered index recommendations with DDL
$diagnostic->confidence;           // 8-factor confidence score (0-1.0)
$diagnostic->concurrencyRisk;      // Lock scope, deadlock risk, contention
$diagnostic->memoryPressure;       // Sort/join/temp buffers, network transfer
$diagnostic->regression;           // Score/time/rows changes vs baseline
$diagnostic->hypotheticalIndexes;  // Simulated index impact (local/testing)
$diagnostic->workload;             // Export/burst/transfer patterns

// Severity-sorted findings with root-cause awareness
foreach ($diagnostic->findings as $finding) {
    echo "[{$finding->severity->value}] {$finding->title}\n";
    echo "  {$finding->description}\n";
    if ($finding->recommendation) {
        echo "  -> {$finding->recommendation}\n";
    }
}
```

---

Interactive Query Scanning
--------------------------

[](#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

[](#setting-up-diagnosequery)

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

```
use QuerySentinel\Attributes\DiagnoseQuery;
use Illuminate\Database\Eloquent\Builder;

class OrderService
{
    #[DiagnoseQuery(label: 'Pending orders', description: 'Orders awaiting fulfillment')]
    public function pendingOrdersQuery(): Builder
    {
        return Order::query()
            ->where('status', 'pending')
            ->where('created_at', '>=', now()->subDays(30))
            ->with('customer')
            ->orderByDesc('created_at');
    }

    #[DiagnoseQuery(label: 'Revenue report')]
    public function revenueReportQuery(): Builder
    {
        return Order::query()
            ->selectRaw('DATE(created_at) as date, SUM(total) as revenue')
            ->where('status', 'completed')
            ->groupByRaw('DATE(created_at)')
            ->orderByDesc('date');
    }
}
```

### Running the Scanner

[](#running-the-scanner)

```
# Interactive mode — pick a method from the list
php artisan query:scan

# List all discovered methods
php artisan query:scan --list

# Filter by class, method, or label name
php artisan query:scan --filter=Order

# JSON output (for scripting)
php artisan query:scan --list --json

# Use a specific database connection
php artisan query:scan --connection=reporting

# Fail in CI if warnings found
php artisan query:scan --fail-on-warning
```

Example interactive session:

```
$ php artisan query:scan

Scanning for #[DiagnoseQuery] methods...
Found 3 diagnosable method(s):

 Select a method to diagnose:
  [0] Pending orders  (OrderService.php:15) — Orders awaiting fulfillment
  [1] Revenue report  (OrderService.php:28)
  [2] Active users query  (UserService.php:42)
 > 0

Diagnosing App\Services\OrderService::pendingOrdersQuery...

  ----------------------------------------------------------------------
  Diagnosed Method:
  Class:   App\Services\OrderService
  Method:  pendingOrdersQuery
  File:    /app/Services/OrderService.php:15
  Label:   Pending orders
  ----------------------------------------------------------------------

  Extracted SQL:
  ----------------------------------------------------------------------
  SELECT * FROM `orders` WHERE `status` = 'pending' AND ...
  ----------------------------------------------------------------------

Running EXPLAIN ANALYZE...

=========================================================
  PERFORMANCE ADVISORY REPORT
=========================================================

  Status:     PASS — No issues detected
  Grade:      A (94.2 / 100)
  Time:       1.45ms
  ...

```

### How It Works

[](#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

[](#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:

```
class ClientService
{
    // Production method — takes required parameters
    public function getFilteredClients(ClientFilterDTO $dto): LengthAwarePaginator
    {
        return $this->buildFilteredQuery($dto)->paginate($dto->perPage);
    }

    // Diagnosis method — no required params, returns Builder
    #[DiagnoseQuery(label: 'Filtered clients', description: 'Client search with date range')]
    public function buildDiagnosableQuery(): Builder
    {
        return Client::query()
            ->where('active', true)
            ->where('created_at', '>=', now()->subMonth())
            ->whereNotNull('email')
            ->orderByDesc('created_at');
    }
}
```

Configure which directories to scan:

```
// config/query-diagnostics.php
'scan' => [
    'paths' => ['app', 'Modules'],  // Relative to base_path()
],
```

---

Automatic Profiling with Attributes
-----------------------------------

[](#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)

[](#controller-profiling-middleware)

Register the middleware:

```
// app/Http/Kernel.php (Laravel 10)
protected $routeMiddleware = [
    'query.diagnose' => \QuerySentinel\Interception\QueryDiagnoseMiddleware::class,
];
```

Apply to routes:

```
Route::middleware(['auth:sanctum', 'query.diagnose'])->group(function () {
    Route::get('/leads', [LeadsController::class, 'index']);
});
```

Add the attribute:

```
use QuerySentinel\Attributes\QueryDiagnose;

class LeadsController extends Controller
{
    #[QueryDiagnose]
    public function index(LeadFilterDTO $dto)
    {
        return LeadResource::collection(
            $this->service->getFilteredLeads($dto)
        );
    }

    #[QueryDiagnose(thresholdMs: 100, sampleRate: 0.25)]
    public function search(Request $request)
    {
        // Profiled 25% of the time, logged only if queries take > 100ms
        return $this->service->search($request->input('q'));
    }
}
```

Methods **without** the attribute pass through with zero overhead.

### Service Class Profiling (Container Proxy)

[](#service-class-profiling-container-proxy)

Register service classes in config:

```
'diagnostics' => [
    'classes' => [
        \App\Services\LeadQueryService::class,
        \App\Services\ReportService::class,
    ],
],
```

Add attributes to methods:

```
use QuerySentinel\Attributes\QueryDiagnose;

class LeadQueryService
{
    #[QueryDiagnose(thresholdMs: 50)]
    public function getFilteredLeads(LeadFilterDTO $dto): LengthAwarePaginator
    {
        return Client::query()
            ->with(['submissions', 'branch'])
            ->filter($dto)
            ->paginate($dto->perPage);
    }
}
```

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-and-thresholds)

**Sampling** controls how often profiling activates:

```
#[QueryDiagnose(sampleRate: 0.05)]  // Profile 5% of invocations
```

Effective rate: `min(methodRate, globalRate)`.

**Thresholds** filter logging noise:

```
#[QueryDiagnose(thresholdMs: 200)]  // Only log if cumulative time >= 200ms
```

Effective threshold: `max(methodThreshold, globalDefault)`.

Attribute ParamConfig KeyCombination Logic`sampleRate``diagnostics.global_sample_rate``min(method, global)` — most restrictive wins`thresholdMs``diagnostics.default_threshold_ms``max(method, global)` — highest bar wins### Fail on Critical

[](#fail-on-critical)

Throw `PerformanceViolationException` on critical performance issues:

```
#[QueryDiagnose(failOnCritical: true)]
public function criticalEndpoint() { ... }
```

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

```
try {
    $service->criticalEndpoint();
} catch (PerformanceViolationException $e) {
    $e->report;  // ProfileReport
    $e->class;   // 'App\Services\LeadQueryService'
    $e->method;  // 'criticalEndpoint'
}
```

### Structured Logging

[](#structured-logging)

Profiled invocations are logged as structured JSON:

```
#[QueryDiagnose(logChannel: 'performance')]
```

```
{
  "type": "query_sentinel_profile",
  "class": "App\\Services\\LeadQueryService",
  "method": "getFilteredLeads",
  "total_queries": 5,
  "cumulative_time_ms": 45.23,
  "grade": "B",
  "n_plus_one": false,
  "analyzed_at": "2026-02-27T14:30:00+00:00"
}
```

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

---

Console Commands
----------------

[](#console-commands)

### `query:diagnose` — Analyze Raw SQL

[](#querydiagnose--analyze-raw-sql)

```
# Full deep diagnostic report
php artisan query:diagnose "SELECT * FROM users WHERE email = 'test@example.com'"

# JSON output (CI-friendly)
php artisan query:diagnose "SELECT * FROM users WHERE id = 1" --json

# Shallow analysis (skip deep analyzers)
php artisan query:diagnose "SELECT * FROM users" --shallow

# Fail on warnings (CI gate)
php artisan query:diagnose "SELECT * FROM users" --fail-on-warning

# Specific database connection
php artisan query:diagnose "SELECT * FROM users" --connection=reporting
```

### `query:scan` — Interactive Builder Diagnosis

[](#queryscan--interactive-builder-diagnosis)

```
# Interactive selection
php artisan query:scan

# List all discovered methods
php artisan query:scan --list

# Filter + JSON
php artisan query:scan --filter=Order --json

# CI mode
php artisan query:scan --fail-on-warning
```

### Console Report Output

[](#console-report-output)

```
=========================================================
  PERFORMANCE ADVISORY REPORT
=========================================================

  Status:     PASS — No issues detected
  Grade:      A (92.5 / 100)
  Time:       1.23ms
  Findings:   0 critical  0 warnings  1 optimizations  1 info
  Driver:     mysql

  EXPLAIN ANALYZE Summary:
  ----------------------------------------------------------------------
  Total Execution Time:  1.23ms
  Rows Returned:         15
  Rows Examined:         150
  Selectivity:           10.0x
  Access Type:           REF
  Complexity:            O(log n)
  ----------------------------------------------------------------------

  Execution Plan Analysis:
  ----------------------------------------------------------------------
  Index Used:            YES
  Covering Index:        YES
  Weedout:               NO (good)
  Temporary Table:       NO (good)
  Filesort:              NO (good)
  Table Scan:            NO (good)
  Early Termination:     YES
  Indexes:  idx_users_email
  ----------------------------------------------------------------------

  Weighted Performance Score:
  ----------------------------------------------------------------------
  Composite Score:       92.5 / 100
  Grade:                 A
    execution_time     95/100  [|||||||||||||||||||.]  (30% weight)
    scan_efficiency    90/100  [||||||||||||||||||..]  (25% weight)
    index_quality      95/100  [|||||||||||||||||||.]  (20% weight)
    join_efficiency   100/100  [||||||||||||||||||||]  (15% weight)
    scalability        85/100  [|||||||||||||||||...]  (10% weight)
  ----------------------------------------------------------------------

  Scalability Estimation:
  ----------------------------------------------------------------------
  Table Size (rows):     10,000
  Risk:                  LOW
    at 1M:  GOOD  (projected 12.3ms)
    at 10M: MODERATE  (projected 123.0ms)
  ----------------------------------------------------------------------

```

### CI Integration

[](#ci-integration)

```
# .github/workflows/query-check.yml
- name: Check query performance
  run: |
    php artisan query:diagnose \
      "SELECT * FROM leads WHERE status = 'active'" \
      --fail-on-warning --json
```

---

Deep Diagnostic Features
------------------------

[](#deep-diagnostic-features)

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

### 22-Step Analysis Pipeline

[](#22-step-analysis-pipeline)

StepPhaseWhat It Does1BaseEXPLAIN ANALYZE + parse metrics + score + rules2EnvironmentCollect MySQL config (buffer pool, InnoDB, cache warmth)3Execution ProfileNested loop depth, B-tree depths, physical reads, complexity4Index CardinalityPer-table index statistics and selectivity5Cardinality DriftEstimated vs actual rows divergence6Join AnalysisJoin strategy, fan-outs, join order7Anti-Patterns10 SQL anti-patterns (SELECT \*, leading wildcard, etc.)8Index SynthesisERS-ordered composite index recommendations9Memory PressureSort/join/temp buffers, concurrency-adjusted footprint10Concurrency RiskLock scope, deadlock risk, contention scoring11Plan StabilityPlan flip risk, volatility score, optimizer hints12Regression SafetyImplicit type conversions, collation mismatches13Confidence Score8-factor trustworthiness rating14Regression BaselinesScore/time/rows changes vs historical baseline15Hypothetical IndexesBefore/after EXPLAIN simulation (local/testing)16Workload PatternsRepeated exports, API bursts, large transfers17ComplexityScan + sort complexity classification18Explain WhyHuman-readable insight (index choice, filesort reason, etc.)19Root-cause suppressionRemove misleading generic findings20Finding deduplicationMerge overlapping recommendations21Confidence gatingDowngrade severity when confidence is low22Consistency validationLog-only internal coherence check### Cardinality Drift Detection

[](#cardinality-drift-detection)

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

```
$diagnostic->cardinalityDrift;
// [
//     'composite_drift_score' => 0.35,
//     'per_table' => [
//         'orders' => [
//             'estimated_rows' => 1000,
//             'actual_rows' => 5200,
//             'drift_ratio' => 0.81,
//             'direction' => 'under_estimated',
//             'severity' => 'warning',
//         ],
//     ],
//     'tables_needing_analyze' => ['orders'],
// ]
```

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

### Anti-Pattern Detection

[](#anti-pattern-detection)

Static SQL analysis for 10 common performance anti-patterns:

PatternSeverityWhy It Matters`SELECT *`WarningPrevents covering index optimizationFunctions on indexed columnsWarningBreaks index usage (e.g., `WHERE YEAR(created_at) = 2026`)Excessive OR chainsWarningInefficient range scans (threshold: 3+)Correlated subqueriesWarningExecutes once per outer row`NOT IN` with subqueryWarningNULL handling issues, anti-join problemsLeading wildcard LIKEWarningForces full table scan (`LIKE '%term'`)Missing LIMIT on large resultOptimizationUnbounded memory consumption`ORDER BY RAND()`WarningO(n log n) full sortRedundant DISTINCTOptimizationUnnecessary with PRIMARY/UNIQUE keyImplicit type conversionWarningPrevents index usageConfig: `anti_patterns.or_chain_threshold` (default 3), `anti_patterns.missing_limit_row_threshold` (default 10000).

### Index Synthesis

[](#index-synthesis)

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

```
$diagnostic->indexSynthesis;
// [
//     'recommendations' => [
//         [
//             'table' => 'orders',
//             'columns' => ['status', 'created_at', 'total'],
//             'type' => 'covering',
//             'ddl' => 'CREATE INDEX idx_orders_status_created_total ON orders(status, created_at, total)',
//             'estimated_improvement' => 'high',
//             'rationale' => 'Covers WHERE equality + range + SELECT columns',
//         ],
//     ],
// ]
```

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

### Confidence Scoring

[](#confidence-scoring)

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

FactorWeightMeasuresEstimation accuracy25%1.0 minus composite drift scoreSample size20%Actual rows (1.0 at 1000+ rows)EXPLAIN ANALYZE available15%1.0 if supported, 0.3 otherwiseCache warmth10%1.0 if buffer pool > 50% utilizedStatistics freshness10%Ratio of non-stale tablesPlan stability10%1.0 if stable, 0.5 if flip riskQuery complexity5%0.7 if > 3 joinsDriver capabilities5%Full support = 1.0Labels: **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

[](#concurrency-risk-analysis)

Evaluates lock contention, deadlock potential, and isolation impact:

```
$diagnostic->concurrencyRisk;
// [
//     'lock_scope' => 'none',          // none, row, gap, range, table
//     'deadlock_risk' => 0.0,          // 0-1.0
//     'deadlock_risk_label' => 'low',  // low, moderate, high
//     'contention_score' => 0.0,
//     'isolation_impact' => 'MVCC consistent read — no locking',
//     'recommendations' => [],
// ]
```

### Memory Pressure Analysis

[](#memory-pressure-analysis)

Estimates query memory footprint under concurrency:

```
$diagnostic->memoryPressure;
// [
//     'memory_risk' => 'moderate',
//     'total_estimated_bytes' => 67108864,
//     'buffer_pool_pressure' => 0.15,
//     'network_pressure' => 'MODERATE',
//     'components' => [
//         'sort_buffer' => 2097152,
//         'join_buffers' => 524288,
//         'temp_table' => 8388608,
//     ],
//     'concurrency_adjusted' => [
//         'concurrent_sessions' => 10,
//         'concurrent_execution_memory' => 109051904,
//         'concurrent_network_transfer' => 524288000,
//     ],
// ]
```

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

### Regression Baselines

[](#regression-baselines)

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

```
$diagnostic->regression;
// [
//     'has_baseline' => true,
//     'baseline_count' => 5,
//     'trend' => 'stable',           // stable, improving, degrading
//     'regressions' => [],           // Score/time/rows degradations
//     'improvements' => [
//         ['metric' => 'execution_time', 'baseline_value' => 12.5, 'current_value' => 8.3, 'change_pct' => -33.6],
//     ],
// ]
```

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

[](#hypothetical-index-simulation)

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

```
// Enable in config
'hypothetical_index' => [
    'enabled' => true,
    'allowed_environments' => ['local', 'testing'],
],
```

```
$diagnostic->hypotheticalIndexes;
// [
//     'simulations' => [
//         [
//             'index_ddl' => 'CREATE INDEX idx_orders_status_created ON orders(status, created_at)',
//             'before' => ['access_type' => 'ALL', 'rows' => 50000],
//             'after' => ['access_type' => 'ref', 'rows' => 150],
//             'improvement' => 'significant',
//             'validated' => true,
//         ],
//     ],
//     'best_recommendation' => 'CREATE INDEX idx_orders_status_created ON orders(status, created_at)',
// ]
```

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

### Workload Pattern Detection

[](#workload-pattern-detection)

Tracks query execution patterns over time to detect systemic issues:

PatternSeverityTriggers When`REPEATED_FULL_EXPORT`Critical100K+ row query executed 10+ times with 3+ full exports`HIGH_FREQUENCY_LARGE_TRANSFER`Warning>50MB network transfer, 10+ executions`API_MISUSE_BURST`Warning5+ executions within 60 secondsConfig: `workload.frequency_threshold` (default 10), `workload.export_row_threshold` (default 100K).

### Plan Stability Analysis

[](#plan-stability-analysis)

Detects optimizer plan flip risk from estimation deviations:

```
$diagnostic->stabilityAnalysis;
// [
//     'volatility_score' => 25,        // 0-100
//     'volatility_label' => 'stable',  // stable ( [
//         'is_risky' => false,
//         'deviations' => [],
//     ],
//     'optimizer_hints' => [],          // USE INDEX, FORCE INDEX, STRAIGHT_JOIN
//     'statistics_drift' => [],
// ]
```

---

Report Reference
----------------

[](#report-reference)

### Report Object (Single Query)

[](#report-object-single-query)

Returned by `analyzeSql()` and `analyzeBuilder()`:

```
$report->grade;            // string — 'A', 'B', 'C', 'D', or 'F'
$report->compositeScore;   // float — 0.0 to 100.0
$report->passed;           // bool — true if no critical findings
$report->summary;          // string — human-readable summary
$report->recommendations;  // string[] — actionable suggestions
$report->scalability;      // array — growth projections
$report->mode;             // string — 'sql', 'builder', or 'profiler'
$report->analyzedAt;       // DateTimeImmutable

$report->toArray();
$report->toJson(JSON_PRETTY_PRINT);
$report->findingCounts();  // ['critical' => 0, 'warning' => 1, ...]
```

### DiagnosticReport Object (Full Diagnostics)

[](#diagnosticreport-object-full-diagnostics)

Returned by `diagnose()`:

```
$diagnostic->report;              // Report — base analysis
$diagnostic->findings;            // Finding[] — severity-sorted
$diagnostic->environment;         // ?EnvironmentContext
$diagnostic->executionProfile;    // ?ExecutionProfile
$diagnostic->indexAnalysis;       // ?array
$diagnostic->joinAnalysis;        // ?array
$diagnostic->stabilityAnalysis;   // ?array
$diagnostic->safetyAnalysis;      // ?array
$diagnostic->cardinalityDrift;    // ?array
$diagnostic->antiPatterns;        // ?array
$diagnostic->indexSynthesis;      // ?array
$diagnostic->confidence;          // ?array
$diagnostic->concurrencyRisk;     // ?array
$diagnostic->memoryPressure;      // ?array
$diagnostic->regression;          // ?array
$diagnostic->hypotheticalIndexes; // ?array
$diagnostic->workload;            // ?array

$diagnostic->effectiveGrade();          // Confidence-capped grade
$diagnostic->effectiveCompositeScore(); // Confidence-capped score
$diagnostic->findingsByCategory('anti_pattern');
$diagnostic->findingCounts();           // By severity
$diagnostic->worstSeverity();

$diagnostic->toArray();
$diagnostic->toJson(JSON_PRETTY_PRINT);
```

### ProfileReport Object (Multiple Queries)

[](#profilereport-object-multiple-queries)

Returned by `profile()` and `profileClass()`:

```
$profile->totalQueries;       // int
$profile->analyzedQueries;    // int — SELECT queries analyzed
$profile->cumulativeTimeMs;   // float
$profile->slowestQuery;       // ?Report
$profile->worstQuery;         // ?Report — lowest score
$profile->duplicateQueries;   // array — normalized SQL => count
$profile->nPlusOneDetected;   // bool
$profile->individualReports;  // Report[]
$profile->skippedQueries;     // string[] — non-SELECT queries

$profile->worstGrade();
$profile->hasCriticalFindings();
```

### Grading System

[](#grading-system)

GradeScore RangeMeaning**A+**98 - 100Perfect — optimal execution plan**A**90 - 97Excellent — well-optimized query**B**75 - 89Good — minor optimization opportunities**C**50 - 74Fair — notable performance issues**D**25 - 49Poor — significant performance problems**F**0 - 24Critical — severe performance issuesScore 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

[](#scoring-components)

ComponentDefault WeightWhat It Measures`execution_time`30%Query execution speed (3-regime model)`scan_efficiency`25%Ratio of rows returned vs rows examined`index_quality`20%Index usage, covering index, access type`join_efficiency`15%Join type quality and nested loop depth`scalability`10%Complexity class projection at scale### Metrics Extracted

[](#metrics-extracted)

MetricTypeDescription`execution_time_ms`floatEXPLAIN ANALYZE execution time`rows_examined`intTotal rows read from storage`rows_returned`intRows returned to client`selectivity_ratio`floatrows\_examined / rows\_returned`complexity`string`O(1)`, `O(log n)`, `O(n)`, `O(n log n)`, `O(n²)``has_table_scan`boolFull table scan detected`has_filesort`boolExternal sort operation`has_temp_table`boolTemporary table created`has_disk_temp`boolTemp table spilled to disk`has_weedout`boolSemi-join weedout optimization`has_index_merge`boolIndex merge optimization`has_covering_index`boolQuery served entirely from index`has_early_termination`boolLIMIT-optimized early stop`is_index_backed`boolUses any index`is_intentional_scan`boolFull dataset retrieval (no WHERE, no LIMIT)`indexes_used`string\[\]Index names used`tables_accessed`string\[\]Table names accessed---

Built-in Rules
--------------

[](#built-in-rules)

RuleSeverityTriggers When`FullTableScanRule`CriticalFull table scan on > 10,000 rows`NoIndexRule`CriticalNo index used at all`TempTableRule`Critical/WarningTemporary table created (critical if on disk)`QuadraticComplexityRule`CriticalO(n^2) complexity detected`DeepNestedLoopRule`WarningNested loop depth exceeds threshold (default 4)`StaleStatsRule`WarningTable statistics appear outdated`LimitIneffectiveRule`WarningLIMIT clause doesn't prevent full scan`IndexMergeRule`InfoIndex merge optimization detected`WeedoutRule`InfoSemi-join weedout strategy detected---

Custom Rules
------------

[](#custom-rules)

Extend `BaseRule`:

```
use QuerySentinel\Rules\BaseRule;

class SlowQueryRule extends BaseRule
{
    public function evaluate(array $metrics): ?array
    {
        $time = $metrics['execution_time_ms'] ?? 0;

        if ($time > 500) {
            return $this->finding(
                severity: 'critical',
                title: 'Slow query detected',
                description: sprintf('Query took %.0fms.', $time),
                recommendation: 'Add indexes or optimize the query.',
            );
        }

        return null;
    }

    public function key(): string { return 'slow_query'; }
    public function name(): string { return 'Slow Query Detection'; }
}
```

Register in config:

```
'rules' => [
    'enabled' => [
        // Built-in rules...
        \App\QueryRules\SlowQueryRule::class,
    ],
],
```

---

Extension Points
----------------

[](#extension-points)

### Custom Drivers

[](#custom-drivers)

Implement `DriverInterface` for other databases:

```
use QuerySentinel\Contracts\DriverInterface;

$this->app->singleton(DriverInterface::class, MyCustomDriver::class);
```

### Custom Scoring Engine

[](#custom-scoring-engine)

Implement `ScoringEngineInterface`:

```
use QuerySentinel\Contracts\ScoringEngineInterface;

$this->app->singleton(ScoringEngineInterface::class, MyCustomScoringEngine::class);
```

---

Architecture
------------

[](#architecture)

```
src/
├── Adapters/                  # Input adapters (Builder, Profiler, ClassMethod, SQL)
├── Analyzers/                 # 16 deep analyzers (cardinality, anti-patterns, memory, etc.)
├── Attributes/
│   ├── DiagnoseQuery.php      #   CLI scanning attribute (#[DiagnoseQuery])
│   └── QueryDiagnose.php      #   Runtime profiling attribute (#[QueryDiagnose])
├── Console/
│   ├── DiagnoseQueryCommand.php  # query:diagnose (raw SQL)
│   ├── ScanCommand.php           # query:scan (interactive builder diagnosis)
│   └── ReportRenderer.php        # 19-section console formatter
├── Contracts/                 # Interfaces (Driver, Analyzer, Scoring, etc.)
├── Core/
│   ├── Engine.php             #   Unified entry (5 modes + 22-step diagnose pipeline)
│   ├── ProfileReport.php      #   Multi-query aggregate report
│   └── QueryAnalyzer.php      #   Core 9-step analysis pipeline
├── Drivers/                   # MySQL, PostgreSQL, SQLite
├── Enums/                     # Severity, ComplexityClass
├── Exceptions/                # UnsafeQuery, PerformanceViolation, EngineAbort
├── Facades/                   # QuerySentinel, QueryDiagnostics
├── Interception/              # Runtime profiling (MethodInterceptor, Middleware, QueryCaptor)
├── Logging/                   # Structured JSON logging
├── Parsers/                   # EXPLAIN plan parser
├── Rules/                     # 9 built-in rules + RuleRegistry
├── Scanner/
│   ├── AttributeScanner.php   #   Discovers #[DiagnoseQuery] methods
│   └── ScannedMethod.php      #   Discovered method DTO
├── Scoring/                   # DefaultScoringEngine, ConfidenceScorer
├── Support/                   # Finding, DiagnosticReport, ExecutionGuard, SqlParser, etc.
└── QueryDiagnosticsServiceProvider.php

```

### Design Principles

[](#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
-------

[](#testing)

```
# Run all tests (849 tests, 2270 assertions)
vendor/bin/phpunit

# Run by suite
vendor/bin/phpunit --testsuite=Unit
vendor/bin/phpunit --testsuite=Feature

# Run specific test
vendor/bin/phpunit --filter=AttributeScannerTest

# Code style
vendor/bin/pint

# Static analysis (PHPStan level 6)
vendor/bin/phpstan analyse
```

---

License
-------

[](#license)

MIT

###  Health Score

36

—

LowBetter than 79% of packages

Maintenance78

Regular maintenance activity

Popularity1

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity50

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~0 days

Total

2

Last Release

132d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/83882f5e1ff08306ac244f8fa8d47cda30f290c2203d1360cba672ff1b94d036?d=identicon)[karimalihussein](/maintainers/karimalihussein)

---

Top Contributors

[![karimalihussein](https://avatars.githubusercontent.com/u/65453298?v=4)](https://github.com/karimalihussein "karimalihussein (55 commits)")

---

Tags

laraveldatabaseperformancemysqlpostgresquerydiagnosticssentinelexplain

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StyleLaravel Pint

Type Coverage Yes

### Embed Badge

![Health badge](/badges/karimalihussein-laravel-query-sentinel/health.svg)

```
[![Health](https://phpackages.com/badges/karimalihussein-laravel-query-sentinel/health.svg)](https://phpackages.com/packages/karimalihussein-laravel-query-sentinel)
```

###  Alternatives

[laravel/ai

The official AI SDK for Laravel.

1.0k3.2M203](/packages/laravel-ai)[psalm/plugin-laravel

Psalm plugin for Laravel

3355.3M346](/packages/psalm-plugin-laravel)[illuminate/queue

The Illuminate Queue package.

21332.6M1.6k](/packages/illuminate-queue)[spatie/laravel-health

Monitor the health of a Laravel application

87512.0M167](/packages/spatie-laravel-health)[api-platform/laravel

API Platform support for Laravel

58171.8k14](/packages/api-platform-laravel)[itpathsolutions/dbstan

Database Standardization and Analysis Tool for Laravel

492.9k](/packages/itpathsolutions-dbstan)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)