# Performance and Query Audit — Phase 7A3

## Audited Paths

- Public quotation availability and submission use database-side `whereHas`/`EXISTS` eligibility checks; operators are returned as a small active list and no agent collection is loaded into PHP.
- Round-robin selection is operator-scoped, selects only candidate fields needed for deterministic ID ordering, and relies on membership/wallet `EXISTS` checks.
- Same-day reuse now uses a Malaysia-day UTC range instead of `whereDate`, allowing the submission phone/timestamp composite index to participate.
- Premium Leads uses one owner-scoped assigned-only query with eager-loaded submission/operator data, then groups the result into the nine fixed columns in memory. Search and filters remain database-side.
- Agent and admin lead detail history eager loads are independently capped: 50 activities, 20 notes, and 20 reminders.
- Notification shared props remain two minimal per-authenticated-request queries: unread count and latest five notifications. They are not globally cached because their data is user-specific and changes frequently.
- Billing is paginated; order detail caps payment attempts at 10. Admin listing is paginated at 25 and CSV reuses its filtered query through a lazy 500-row stream with eager-loaded display relations.
- Pending diagnostics already batch eligible-agent loading by operator, avoiding per-assignment queries.

## Confirmed Fixes

1. The Admin Round Robin Monitor previously queried agents, state, last agent, and pending count separately for each operator. It now loads active operators, agents with needed relations, state rows, and grouped pending counts in fixed batched queries.
2. The Admin Lead Credit Monitor previously loaded every agent with memberships and wallets solely to calculate summaries and operator breakdowns. Those values now use database counts and operator `withCount` aggregates; paginated display rows stay separate.
3. Lead detail pages previously loaded complete activity/note/reminder histories before trimming in PHP. Their relationship queries are now bounded at the database level.

## Indexes Added

- `users(takaful_operator_id, is_account_active, id)` for operator-scoped eligible-agent selection and deterministic round-robin ordering.
- `memberships(user_id, status, starts_at, ends_at)` for active-membership `EXISTS` checks.
- `quotation_submissions(normalized_phone, submitted_at)` for same-day reuse lookup. The lookup derives its day from the original submission timestamp in `Asia/Kuala_Lumpur`, then queries the corresponding UTC range; approval time and the database UTC calendar date are not business authority.
- `lead_assignments(takaful_operator_id, distribution_status)` for per-operator pending/assignment monitor counts.
- `lead_assignments(user_id, distribution_status, pipeline_status, assigned_at)` for agent Premium Leads ownership/status filters and ordering.

## Query-Count Regression Coverage

`PerformanceQueryAuditTest` uses test-only listeners and moderate fixtures. It asserts bounded query counts rather than brittle exact framework internals:

- Round Robin Monitor: at most 6 queries for 3 operators and 36 agents.
- Lead Credit Monitor: at most 11 queries for 36 agents across 3 operators.
- Premium Leads Kanban: at most 12 queries for 40 cards; adding cards does not add relationship queries.

## Deferred Scale Risks

- Premium Leads intentionally loads an agent's full active pipeline to populate all nine Kanban buckets. Very large per-agent portfolios may later need an explicit product-level pagination/windowing decision.
- Admin CSV export intentionally streams every matching record. Large exports may eventually need asynchronous jobs, but this audit does not add queue infrastructure.
- Search predicates using leading-wildcard `LIKE` are correct but will not use conventional B-tree indexes efficiently at very large scale; full-text/search infrastructure is deferred until measured demand exists.
- No Redis cache, queue redesign, read replica, infrastructure tuning, or permanent SQL logging was introduced.