# Performance Analysis: Request Path and DB Hotspots
Generated: 2026-06-14

## Executive Summary
- Bottleneck Type: Mixed (Middleware write amplification, CPU-heavy chart builds, repeated DB reads, per-request writes)
- Current Performance: Not benchmarked in this run; findings are static-code verified hotspots.
- Expected Improvement: 20-45% p95 latency reduction on chart/AI-heavy routes with low-risk fixes.

## Top 5 Bottlenecks

### 1) Global middleware write amplification on almost every web request
- Severity: High
- Impact: High p95 increase under concurrent traffic; extra DB pressure and lock contention.
- Evidence:
  - `TrackVisit` is globally attached in web middleware chain (`bootstrap/app.php:22`).
  - Request phase runs `firstOrCreate` + `update` (`app/Http/Middleware/TrackVisit.php:64`, `app/Http/Middleware/TrackVisit.php:92`).
  - Terminate phase still writes `PageView::create` + `increment` (`app/Http/Middleware/TrackVisit.php:137`, `app/Http/Middleware/TrackVisit.php:149`).
- Why it hurts: 2 writes/request minimum for tracked pages, plus create path on new visitors; scales linearly with traffic.

### 2) Heavy chart construction repeatedly triggered in AI request path
- Severity: High
- Impact: CPU-bound latency spikes on AI endpoints; duplicated compute and DB reads per message.
- Evidence:
  - `AiService` calls `pageBuilder->build()` in multiple branches (`app/Services/Ai/AiService.php:87`, `:410`, `:462`, `:478`, `:539`, `:704`).
  - `ChartPageBuilderService` itself loads custom points and builds large chart contexts (`app/Services/Chart/ChartPageBuilderService.php:41`, `:61`).
- Why it hurts: complex chart calculations + repeated fallback rebuilds in a single request path.

### 3) Duplicate custom-point DB reads in panel path (controller + builder)
- Severity: Medium-High
- Impact: Extra query count on hot panel route; worsens under high authenticated concurrency.
- Evidence:
  - Controller computes custom point versioning and loads point lists (`app/Http/Controllers/Frontend/IndexController.php:89`, `:91`, `:139`, `:157`).
  - Builder independently re-queries same global/user custom points (`app/Services/Chart/ChartPageBuilderService.php:41`, `:61`).
- Why it hurts: duplicate reads and transform work for same payload in same request lifecycle.

### 4) Per-request session write in locale middleware
- Severity: Medium
- Impact: Increased session I/O and lock time for anonymous+authenticated traffic.
- Evidence:
  - `Session::put('locale', ...)` executed in middleware (`app/Http/Middleware/SetLocale.php:29`).
  - Middleware is globally attached (`bootstrap/app.php:21`).
- Why it hurts: unnecessary write even when locale unchanged; can serialize requests for same session backend.

### 5) High-frequency authenticated write endpoint for panel time-gate
- Severity: Medium
- Impact: Write churn and row-level contention for active users if front-end posts frequently.
- Evidence:
  - Endpoint exposed in web routes (`routes/web.php:183`).
  - Controller writes user row on each call (`app/Http/Controllers/Frontend/IndexController.php:283`).
- Why it hurts: frequent updates to same user row can create lock pressure and replication lag under load.

## Low-Risk Improvements (Exact Targets)
1. Reduce TrackVisit synchronous writes and sample page-view persistence.
- Targets:
  - `app/Http/Middleware/TrackVisit.php`
  - `bootstrap/app.php`
- Change:
  - Replace `firstOrCreate` + `update` with lightweight cache gate (e.g., only update last_visit_at every N seconds).
  - Queue/batch `PageView` writes (or sample by route class/traffic tier).

2. Memoize panel build once per AI request path and avoid branch re-builds.
- Targets:
  - `app/Services/Ai/AiService.php`
- Change:
  - Create single lazy `getPanelData()` closure/value object per request.
  - Reuse `panelData` and `chartData` across fallback/aspect/custom-point branches.

3. Remove duplicate custom-point queries by passing prepared points into builder.
- Targets:
  - `app/Http/Controllers/Frontend/IndexController.php`
  - `app/Services/Chart/ChartPageBuilderService.php`
- Change:
  - Resolve global/user custom points once and inject into builder options/context.

4. Guard locale session writes (write only on change).
- Targets:
  - `app/Http/Middleware/SetLocale.php`
- Change:
  - Compare current session value; skip `Session::put` if unchanged.

5. Throttle/debounce time-gate writes and persist on coarse intervals.
- Targets:
  - `routes/web.php`
  - `app/Http/Controllers/Frontend/IndexController.php`
- Change:
  - Add rate limit middleware for `POST /panel/time-gate-state`.
  - Persist only when delta (seconds) exceeds threshold or blur state toggles.

## Potential Regressions to Watch
1. Visit analytics fidelity drop if page-view sampling/batching introduced.
- Watch metrics: page_views/day, unique visitors, bot/human split.

2. AI response context drift if panel-data memoization uses stale params.
- Watch metrics: wrong chart key usage, incorrect aspect hints.

3. Locale mismatch edge cases if session writes are skipped too aggressively.
- Watch metrics: locale-flip complaints after login/logout transitions.

4. Delayed panel gate enforcement with coarse persistence.
- Watch metrics: discrepancy between client-side timer and persisted usage.

5. Cache coherency issues when de-duplicating custom point reads.
- Watch metrics: newly added custom points not appearing immediately in panel/AI.

## Additional Notes
- Visitors table has unique `session_key` which helps lookup (`database/migrations/2026_02_08_130340_create_visitors_table.php:13`), but write volume remains primary concern.
- Page views have useful composite index (`database/migrations/2026_02_08_130347_create_page_views_table.php:26`), so ingest frequency is the dominant bottleneck rather than missing basic index.