# Query Budget & N+1 Forensics — Phase 5

**Phase:** 5 — Query Budget, N+1 Detection & Elimination (Playbook Phase 8.3)
**Date:** 2026-08-28

---

## 1. Baseline & Discovery

**Initial state at Phase 5 entry:**
- Architecture Suite: 37 tests / 604 assertions / PASS
- PHPUnit: 213 tests / 1277 assertions / 0 failures
- PHPStan: Level 6 / 0 errors
- OpenAPI parity: 183/183 operations PASS
- Schema drift: clean (104 tables / 8 views / 4 triggers / 5 procedures / 4 events)

---

## 2. Query Count Semantics

**What constitutes a query:**
- Any SQL statement sent to MariaDB/MySQL via `PDOStatement::execute()`.
- Measured at the transport execution boundary: `App\Core\Database::query()`.
- Captures: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `DDL`, and stored procedure executions.

**What does NOT count as a query:**
- Memory operations (PHP array transformations, collection mapping).
- Service/repository PHP method calls that do not execute a DB statement.
- Redis cache operations.
- Transactions savepoints / begin / commit unless they issue a physical statement.

---

## 3. Instrumentation & Architecture Design

**Testing-only query counter:**
- `Database::$queryListener`: A `private static ?callable $queryListener = null` property on `App\Core\Database`.
- In `APP_ENV=production` and `APP_ENV=development`: The listener remains `null` by default. Zero production runtime overhead, zero query collection memory leaks, zero query string exposure.
- In `APP_ENV=testing`: `IntegrationTestCase::startQueryLog()` registers a lightweight listener to capture query strings, bindings, and normalized query fingerprints.
- `IntegrationTestCase::assertQueryCountLessThanOrEqual($max, $callback)` provides actionable diagnostics on budget violations, grouping and ranking repeated query patterns.
- Verified by architecture test: `Tests\Architecture\QueryCounterArchitectureTest`.

**Query Fingerprinting:**
- Normalizes query text: strips SQL comments, collapses whitespace, collapses variable-length `IN (?, ?, ?)` clauses to `IN (?)`, and replaces numeric and string literals with placeholder `?`.
- Repeated fingerprints signal candidate $N+1$ loops.

---

## 4. Admin Endpoint Inventory & Audit

| Category | Endpoint | HTTP Method | Implementation Architecture | N+1 Detected |
|---|---|---|---|---|
| Customer | `/admin/customers` | GET | `AdminCustomerProfileControllerHandler::index` | **YES** ($2N$ queries) |
| Brand Pricing | `/admin/brand-rates/summary` | GET | `AdminBrandPricingControllerHandler::ratesSummary` | **YES** ($N$ queries) |
| Orders | `/admin/orders` | GET | `AdminOrderController::index` | NO ($O(1)$) |
| Partners | `/admin/partners` | GET | `AdminPartnerControllerHandler::index` | NO ($O(1)$ bulk balances) |
| Products | `/admin/products` | GET | `AdminProductControllerHandler::index` | NO ($O(1)$ count + page) |
| Inventory | `/admin/inventory` | GET | `AdminInventoryControllerHandler::index` | NO ($O(1)$ count + page) |
| Purchases | `/admin/purchases` | GET | `AdminPurchaseController::index` | NO ($O(1)$ bulk items/returns) |
| Warranties | `/admin/warranties/stats` | GET | `AdminWarrantyController::stats` | NO ($O(1)$ status counts) |
| Financial | `/admin/financial/daily-recap` | GET | `AdminFinancialControllerHandler::dailyRecap` | NO ($O(1)$ aggregation) |
| Checks | `/admin/checks` | GET | `AdminCheckController::list` | NO ($O(1)$ count + page) |
| Sale Returns | `/admin/sale-returns` | GET | `AdminSaleReturnController::index` | NO ($O(1)$ bulk items) |

---

## 5. N+1 Findings, Root Causes & Remediations

### Finding 1: Customer List Customer Grade & Amperage Calculation ($N+1$)

- **Endpoint:** `GET /admin/customers` (`AdminCustomerProfileControllerHandler::index`)
- **Initial Query Count:** For $N=10$ customers, executed **23 queries**:
  - $1\times$ `COUNT(*)` from `customers`
  - $1\times$ paginated `SELECT` from `customers`
  - $1\times$ `getBulkRialBalances` ($IN$ query)
  - $10\times$ `getTotalAmperagePurchased` (query per customer against `order_items` + `orders` + `products` + `amperages`)
  - $10\times$ `getCustomerGrade` (`SELECT name FROM customer_grade_tiers WHERE min_amperage <= ? ORDER BY min_amperage DESC LIMIT 1`)
- **Root Cause:** Per-customer loop calling individual `getTotalAmperagePurchased($cid)` and individual DB tier lookup `getCustomerGrade($amperage)`.
- **Remediation:**
  1. Added `CustomerBalanceService::getBulkTotalAmperagePurchased(array $customerIds)`: Uses `WHERE o.customer_id IN (...) GROUP BY o.customer_id` (1 query for all $N$ customers).
  2. Added `CustomerBalanceService::getAllGradeTiers()` and `matchGrade($amperage, $tiers)`: Loads all grade tiers once (1 query) and matches tiers in-memory.
- **Optimized Query Count:** **5 queries** total (constant $O(1)$ regardless of page size $N$).

### Finding 2: Brand Rates Summary ($N+1$)

- **Endpoint:** `GET /admin/brand-rates/summary` (`AdminBrandPricingControllerHandler::ratesSummary`)
- **Initial Query Count:** For $N=57$ active brands, executed **58 queries**:
  - $1\times$ `SELECT` all active brands
  - $57\times$ `BrandRateService::getCurrentRatesForBrand($brandId)` in a `foreach ($brands as &$brand)` loop
- **Root Cause:** Individual per-brand query for current rates.
- **Remediation:**
  1. Added `BrandRateRepository::getBulkCurrentRatesForBrands(array $brandIds)`: Loads all current rates for the entire brand set in a single `WHERE bar.brand_id IN (...) AND bar.is_current = 1` query.
  2. Added `BrandRateService::getCurrentRatesForBrands(array $brandIds)`.
  3. Updated `AdminBrandPricingControllerHandler::ratesSummary` to use the bulk method.
- **Optimized Query Count:** **2 queries** total (constant $O(1)$ for all $N$ brands).

---

## 6. Before & After Measurements & Established Budgets

| Endpoint | Dataset Size ($N$) | Before | After | Enforced Budget | Scaling Complexity |
|---|---|---|---|---|---|
| `GET /admin/customers` | $N=10$ | 23 | 5 | $\le 6$ | $O(1)$ |
| `GET /admin/customers` | $N=50$ | 103 | 5 | $\le 6$ | $O(1)$ |
| `GET /admin/brand-rates/summary` | $N=57$ | 58 | 2 | $\le 3$ | $O(1)$ |
| `GET /admin/orders` | $N=20$ | 2 | 2 | $\le 3$ | $O(1)$ |
| `GET /admin/partners` | $N=50$ | 6 | 6 | $\le 7$ | $O(1)$ |
| `GET /admin/products` | $N=20$ | 2 | 2 | $\le 3$ | $O(1)$ |
| `GET /admin/inventory` | $N=50$ | 2 | 2 | $\le 3$ | $O(1)$ |
| `GET /admin/purchases` | $N=20$ | 3 | 3 | $\le 5$ | $O(1)$ |
| `GET /admin/warranties/stats` | — | 3 | 3 | $\le 4$ | $O(1)$ |
| `GET /admin/financial/daily-recap` | — | 5 | 5 | $\le 6$ | $O(1)$ |
| `GET /admin/checks` | $N=20$ | 2 | 2 | $\le 3$ | $O(1)$ |

---

## 7. EXPLAIN Evidence

**1. Customer Bulk Amperage Query:**
```sql
EXPLAIN SELECT o.customer_id, COALESCE(SUM(a.amperage * oi.quantity), 0) AS total_amperage
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
JOIN amperages a ON p.amperage_id = a.id
WHERE o.customer_id IN (1, 2, 3, 4, 5) AND o.status != 'cancelled'
GROUP BY o.customer_id;
```
- Plan: `o` uses `idx_customer_status_created` (range scan on customer IDs), `oi` uses `idx_order` (ref on order ID), `p` uses `PRIMARY` (eq_ref). Efficient index usage across all joins.

**2. Brand Rates Bulk Query:**
```sql
EXPLAIN SELECT bar.id, bar.brand_id, bar.rate_type, ...
FROM brand_ampere_rates bar
WHERE bar.brand_id IN (1, 2, 3) AND bar.is_current = 1;
```
- Plan: Uses `idx_bar_brand_current` (`key_len=5`, index range scan covering `brand_id` and `is_current`). Zero table scan.

---

## 8. Financial, Transaction & Safety Guarantees

- **No financial calculation altered:** Ledger sums (`party_ledger`, `rial_ledger`), debits, credits, and check states remain strictly preserved.
- **No transaction or lock removed:** All `SELECT ... FOR UPDATE` calls in mutating paths (`CheckService`, `BrandRateService`, `OrderCommandService`, `DatabaseJobQueue`) remain fully intact.
- **No API contract changes:** All response JSON shapes and pagination metadata remain 100% identical.

---

## 9. Test Suite Verification

- **Query Budget Integration Suite:** `Tests\Integration\Scale\AdminQueryBudgetTest` (11 tests / 33 assertions / PASS).
- **Query Counter Architecture Suite:** `Tests\Architecture\QueryCounterArchitectureTest` (2 tests / 2 assertions / PASS).
