# Performance and load audit

**Question this answers:** which features of the system put the heaviest load on the server, and what to change so it stays fast without being overloaded.

**How it was done:** a read of the code paths that run most often (the captain's location ping, the dashboard's live screens, the order list, the queue and the scheduled jobs), plus the table indexes defined in the migrations. **Nothing here was measured under load.** Every "cost at scale" figure is an estimate worked out from the code and the configured intervals, and is marked as one. The database server was not answering when this was written, so there are no row counts. Section 6 says how to turn these estimates into measurements before spending effort on any of them.

Two environment facts are from the local `.env` and **must be checked against production**: `QUEUE_CONNECTION=database`, `SESSION_DRIVER=database`, `REDIS_CLIENT=predis`.

---

## 1. Summary

The system is not heavy per request. Its load comes from **a few things that repeat constantly**, each cheap alone and expensive together, and from **one thing that blocks**:

1. **Administrator alerts are sent over the network inside the request that raises them**, once per administrator, sometimes while a database transaction is open (finding 1). This is the only item here that makes a single request slow, and it grows with the number of administrators.
2. **Every GPS ping** costs about eight SQL statements and one extra table write, and one of those queries gets slower the more orders a captain has ever carried (finding 3).
3. **The dashboard's order list** runs an unindexable sort and a full count on every refresh, and every open dashboard refreshes it on every order event (finding 2).
4. **One queue does everything**: dashboard updates, store webhooks, push notifications, route recalculation, IP lookups and image conversions all wait in the same line, on a database table (finding 4).
5. **The live map re-reads the whole fleet from SQL every 10 seconds per open tab**, including tabs nobody is looking at (finding 5).
6. **Several hot queries have no index that fits** (finding 6).

**Do these first, in this order.** Each is small and none changes behaviour:

| # | Action | Effort | What it removes |
|---|---|---|---|
| 1 | Queue the administrator alert listeners and move the inline calls in `OrderService` to an after-commit job (finding 1) | ~10 files, mostly one line each | A network call per administrator inside every order creation and captain action |
| 2 | Add the composite indexes in section 5.1 | 1 migration | The per-ping order scan, the live-fleet subquery cost, captain list scans |
| 3 | Move the queue to Redis and split it into named queues (section 5.3) | Config + 4 supervisor programs | Head-of-line blocking, the polling load on the `jobs` table |
| 4 | Cache the live fleet for 3 seconds and stop it polling in background tabs (finding 5) | ~20 lines | Most of the live-map load |
| 5 | Replace the order list's expression sort with an indexed column (finding 2) | 1 migration + 1 query | The heaviest read query in the dashboard |
| 6 | Update a token's `last_used_at` at most once a minute (finding 3) | ~15 lines | One write per request, on every request |

---

## 2. What runs, and how often

| Source | Frequency | Per run |
|---|---|---|
| Captain location ping | every **8 s** moving, **45 s** stationary, per online captain | ~8 SQL + 4 to 6 Redis (finding 3) |
| Live map refresh | every **10 s** per open tracking tab, **also in the background** | one query over every on-duty captain (finding 5) |
| Dashboard refresh on an order event | every order change, per open tab, up to 4 a second | the order list, the stats, the captain list (finding 2) |
| Store portal | orders list **30 s**, order detail **15 s**, per open tab | list or detail query |
| `dispatch:flush-gps` | every 30 s | bulk insert of buffered pings |
| `orders:expire-overdue-offers` | every minute | indexed lookup |
| `orders:watch-supplier-delays` | every 5 minutes | join on accepted orders |
| `orders:watch-unassigned` | every 10 minutes | pending orders with legs |
| `integration:health` | every 15 minutes | store health check |
| `dispatch:reconcile-capacity` | hourly | recount of every captain's active orders |
| Daily at 03:00 to 03:50 | once | prune GPS, push log, integration log, login history |

None of the scheduled jobs is a problem. The three that matter are in the first three rows.

---

## 3. Findings

Ranked by how much load they cause, not by how hard they are to fix. "Estimate" always means worked out from the code, not measured.

### Finding 1. Administrator alerts are sent synchronously, inside the request that caused them, sometimes inside a database transaction

**Where:** `app/Listeners/Admin/NotifyAdminsNewOrder.php`, `NotifyAdminsOrderCancelled.php`, `app/Listeners/Order/NotifyGoodsToReturn.php` (none of them is queued), `app/Services/AdminNotification/AdminNotificationService.php::notifyAdmins()` (the loop at line 508) and `AdminWebPush::send()`, and the direct calls in `app/Services/Order/OrderService.php` (lines 949, 1710, 1839, 1847).

**What happens.** When an alert is raised, the code loops over every administrator who should hear it and, **for each one, in the same request**:

1. inserts a notification row,
2. runs a query to see whether the administrator has a browser registered,
3. loads the administrator and their device tokens, and
4. if they have one, **sends a Firebase web push over HTTP and waits for the answer** (`NotificationSender::sendNow`). The Firebase client timeout is 30 seconds.

Otherwise it queues a broadcast, which is cheaper.

**Which requests pay for it:**

| Request | Alert it runs inline |
|---|---|
| A store creates an order (`POST /api/integration/...`) or the office creates one | new order |
| An order is cancelled | cancelled order |
| A captain confirms a stop that differs from the order | supplier cost overrun (**inside the database transaction** that records the payment) |
| A captain reports a failed supplier stop | supplier failed |
| A captain delivers with a cash or handover difference | cash variance, handover mismatch |
| An order is called off after goods were collected | goods to return |

**Estimate.** A Firebase call usually takes 100 to 400 ms. With 10 administrators who have the dashboard registered for push, one new order adds **1 to 4 seconds to the store's own API call**, holds a PHP worker for all of it, and does this for every order. The captain's "collected" request is worse: the cost-overrun alert runs **while the transaction holds the order and ledger locks**, so a slow Firebase call keeps those rows locked and makes other requests for the same captain wait.

**Fix.**
1. Make the alert listeners queued: `implements ShouldQueue` with `public string $queue = 'realtime';` on `NotifyAdminsNewOrder`, `NotifyAdminsOrderCancelled`, `NotifyAdminsDeliveryFailed`, `NotifyAdminsNewApplication`, `NotifyAdminsDocumentsResubmitted` and `NotifyGoodsToReturn`. The listeners already receive an event with everything they need.
2. For the calls made directly in `OrderService`, dispatch a small job (`SendAdminAlertJob::dispatch(...)->afterCommit()`) instead of calling the service. The alert must wait for the commit anyway: telling the office about a payment that then rolled back is wrong.
3. Inside `notifyAdmins()`, insert all administrators' rows with one `insert()` and send the web pushes from one queued job per chunk of administrators, not one blocking call each.
4. Add a short timeout (3 to 5 seconds) for the web push client, separate from the 30-second timeout the captain push uses.

**Expected gain:** order creation by stores and every captain action stop depending on how many administrators exist or how fast Firebase is. After finding 4 (the order list), this is the change users will notice most.

---

### Finding 2. The dashboard order list: unindexable sort, full count, refetch on every event

**Where:** `app/Repositories/Order/OrderRepository.php`, `paginateWithFilters()` (the sort is at line 396) and `src/features/realtime/realtime-provider.tsx` in the dashboard.

**What happens.** The list sorts by `COALESCE(parent_order_id, id) DESC, (leg = 'collection') ASC, id`. A sort on an expression cannot use any index, so MySQL builds the whole filtered result and sorts it **for every page**. `paginate()` also runs a separate `COUNT(*)` over the same filter. The search box adds `LIKE '%term%'` on three columns, which cannot use an index either. The list eager-loads six relations on top.

**Why it multiplies.** On every `.order.touched` event each open dashboard marks `orders`, `stats` and `drivers` stale and refetches whatever is on screen. The refetch is debounced by 250 ms, so one tab can ask up to four times a second. The existing cache (`flushCache()`) is cleared by every order write, so under steady order traffic it is cleared before it is reused.

**Estimate.** With 10 dispatchers on the orders list and one order event a second, that is about 10 sorted-and-counted list queries a second on top of the stats and captain refetches. The cost grows with the size of the orders table, so it is invisible now and bad in a year.

**Fix.**
1. Replace the expression with a real column. Add an indexed `sort_key` column, set by the order observer, and sort by `sort_key DESC, id DESC`. See section 5.2.
2. Use `simplePaginate()` for the dashboard list, or cache the total for a few seconds, so a count is not run on every page.
3. In the dashboard, when an order event arrives, **update the matching row in place** from the event's data (`setQueryData`) and refetch only when the changed order is not on the current page. That removes the refetch storm instead of making each refetch cheaper.
4. Raise the debounce from 250 ms to 1500 ms. Nobody reads a list that fast.
5. For search, match `order_number` by prefix (`LIKE 'ORD-12%'`, which can use the unique index) and use a full-text or trigram index only for customer name and phone if people really search by them.

**Expected gain:** the largest of any single fix in this document.

---

### Finding 3. Every captain ping costs about eight SQL statements and a write to the token table

**Where:** `app/Services/Driver/DriverLocationService.php::update()` (line 55), `app/Services/Dispatch/EffectiveLocationResolver.php`, `app/Services/Dispatch/RoutePlan/DeviationDetector.php`, `vendor/laravel/sanctum/src/Guard.php` (line 57).

**What one ping does** (counted from the code):

| Step | SQL |
|---|---|
| Sanctum finds the token, then its captain | 2 selects |
| Sanctum records `last_used_at` | **1 update, on every request** |
| Read the previous location | 1 select |
| Write the new location | 1 upsert |
| Is the captain online | 1 select |
| What order is the captain on now | 1 select (see below) |
| The current route, for the deviation check | 1 select |
| Redis: GPS point, history buffer, geo index | 3 to 5 commands |

**Estimate.** 200 moving captains is 25 pings a second, so roughly 200 SQL statements and 75 writes a second, around the clock while they work. That is fine for a tuned MySQL and poor for the local 16 MB buffer pool or a shared host.

**Two parts are worth fixing.**

**(a) The order lookup gets slower as a captain's history grows.** `OrderRepository::currentOrderFor()` filters on `driver_id` and `order_status`, then orders by a `CASE` expression. The table has an index on `driver_id` and one on `order_status`, but **not together**. MySQL reads every order that captain has ever carried, filters them, and sorts what is left. A captain with 5,000 delivered orders pays for 5,000 rows on every ping. The index in section 5.1 turns this into a lookup of the two or three active rows. The same missing index also affects the live-fleet subquery (finding 5) and the captain's own order list.

**(b) Sanctum writes `last_used_at` on every authenticated request.** That is a table update for each ping, each order list refresh and each notification poll. The inactivity rule only needs minute-level accuracy, because `DRIVER_INACTIVITY_MINUTES` is 1440. Update the column at most once a minute: section 5.4 has the code.

**Also:** the deviation check decodes the route's polyline and measures the distance to every segment on **every ping**, in PHP. On a long route that is real CPU. Cache the decoded polyline per route version (it already carries a version) so it is decoded once per route, not once per ping.

**Expected gain:** about a third fewer queries per ping, and the one that grows with history is gone.

---

### Finding 4. One database queue carries everything

**Where:** `config/queue.php` (default `database`, `retry_after` 90), `docs/runbook.md` (one supervised `queue:work`), every `ShouldQueue` listener and `ShouldBroadcast` event.

**What waits in the same line:**

- every dashboard update (`DashboardOrderTouched` is a queued broadcast, one per order event)
- store webhooks (up to 30 s each, then retried by hand)
- push notifications to captains (FCM calls, up to 30 s)
- route recalculation (calls an external routing service)
- IP location lookups (two external calls, 3 s timeout each)
- image conversions and the move to cloud storage
- admin notification broadcasts, one per administrator per event

**Why it hurts.** A slow store webhook or a slow IP lookup delays the dashboard update queued behind it, and the dashboard then looks frozen even though the server is fine. The `database` driver also polls the `jobs` table continuously and takes a row lock to claim each job, which is database load that does no useful work.

**Estimate.** One order status change queues about three to six jobs (dashboard broadcast, store webhook, route recalculation, collection progress, and captain or admin notifications). At 1,000 orders a day with about five transitions each, that is 15,000 to 30,000 jobs a day through one worker.

**Fix.**
1. Switch `QUEUE_CONNECTION` to `redis` (Redis is already a hard dependency).
2. Split into named queues and give each its own workers (section 5.3):
   - `realtime` for dashboard broadcasts and route recalculation, with at least 2 workers and nothing slow allowed in it
   - `push` for FCM
   - `webhooks` for store webhooks
   - `media` for conversions and cloud moves, with 1 worker
   - `default` for everything else
3. Keep `retry_after` above each queue's longest job timeout.

**Expected gain:** dashboard updates stop depending on how slow a store's server is. This is the fix that most improves what a user feels.

---

### Finding 5. The live map re-reads every on-duty captain from SQL every 10 seconds, per tab, even hidden

**Where:** `src/app/(dashboard)/tracking/page.tsx` (lines 98 to 101: `refetchInterval: LIVE_FLEET_POLL_MS` with `refetchIntervalInBackground: true`, and `LIVE_FLEET_POLL_MS = 10_000` in `src/services/tracking.ts`), `app/Repositories/Dispatch/LiveFleetRepository.php::onDuty()` (line 51).

**What it does.** One query joins captains, availability and locations, runs a correlated subquery per captain to count in-progress orders, then loads each vehicle. It has no limit and no pagination. The result is rebuilt from SQL on every request even though the same information already sits in Redis (the GPS point and the geo index the dispatch code uses).

**Why it multiplies.** Each open tracking tab does this every 10 seconds, and `refetchIntervalInBackground: true` keeps doing it in a tab nobody is looking at. Five dispatchers each leave it open in a background tab all day.

**Fix.**
1. Cache the response in Redis for 3 seconds (`Cache::remember('live-fleet', 3, ...)`). Every dashboard asks within that window, so the database sees one query per 3 seconds however many tabs are open.
2. Set `refetchIntervalInBackground: false`. A hidden map does not need fresh data.
3. Longer term: build the snapshot from Redis (it already holds each captain's latest point), and push changes over Reverb on one throttled channel instead of polling.

**Expected gain:** the live map's database load becomes constant instead of growing with the number of tabs.

---

### Finding 6. Indexes that are missing or cannot be used

From the migrations. "Missing" means no index that fits the query, not that the table has none.

| Table | Query that needs it | Problem | Fix |
|---|---|---|---|
| `orders` | `where driver_id = ? and order_status in (...)`: every ping, live fleet, captain order list, capacity checks | separate indexes on `driver_id` and `order_status`, none together | `(driver_id, order_status)` |
| `orders` | store orders by status, dashboard counts | | `(store_client_id, order_status)` |
| `orders` | list filtered by date, stats series | no index on `created_at` alone | `(created_at)` |
| `orders` | dashboard list sort | expression, no index possible | `sort_key` column, finding 2 |
| `order_declines` | captain's refusal history, newest first | `(order_id, driver_id)` only; the foreign key index covers `driver_id` | fine; add `(driver_id, id)` only if the history grows large |
| `push_deliveries` | captain notification history | grouped by `COALESCE(notification_id, uuid)` after the recipient index | acceptable at 30-day retention; revisit if a captain receives thousands |
| `personal_access_tokens` | token lookup | fine | prune expired rows (finding 13) |

**Date filters defeat indexes.** `whereDate('created_at', '>=', ...)` becomes `DATE(created_at) >= ...`, which cannot use an index. It is used in `OrderRepository` (lines 278 and 282), `LoginHistoryRepository`, and `PushDeliveryRepository` (four places). Replace each with a range on the raw column: `created_at >= '2026-10-01 00:00:00'` and `created_at < '2026-10-02 00:00:00'`.

---

### Finding 7. GPS history grows by hundreds of thousands of rows a day

**Where:** `config/dispatch.php` (`gps_history_retention_days` = 30), `app/Services/Dispatch/GpsHistoryFlusher.php`, table `driver_location_histories` (two indexes: `(driver_id, captured_at)` and `(captured_at)`).

**Estimate.** One row per accepted ping. A moving captain at 8 s is 450 rows an hour; 100 captains working 10 hours is about 450,000 rows a day and **about 13 million rows over the 30-day retention**, each carrying two index entries. (Correction made while fixing this: the daily prune was already batched, deleting 1,000 rows at a time, so it does not hold a long lock. The volume of the table is the problem, not the delete.)

**Fix.**
1. Ask whether 30 days is needed. 7 days answers almost every support question and cuts the table by three quarters.
2. Partition the table by day or week and drop old partitions (instant and lock-free) instead of deleting rows.
3. Store fewer points: skip a point that is within a few metres of the last stored one, and keep one in every 3 while moving in a straight line. The live position is unaffected, because it is kept in `driver_locations` and Redis.
4. ~~Prune in batches~~ Already done (`pruneOlderThan` deletes in chunks).

**Done:** a ping is now kept in the history only when the captain has moved at least 10 metres since the last ping, or when nothing has been kept for 5 minutes (`DISPATCH_GPS_HISTORY_MIN_DISTANCE_M`, `DISPATCH_GPS_HISTORY_MAX_GAP_S`). The live position is written for every ping either way.

---

### Finding 8. Administrator notifications fan out per administrator per event

**Where:** `AdminNotificationService::notifyAdmins()` (the loop at line 508). Finding 1 is about *where* this runs; this is about *how much* it does.

For each event the service writes one row **per administrator** and, per administrator, runs the reachability query, an administrator lookup and a device-token query before sending. With 30 administrators one new order is 30 inserts and about 90 further queries; 1,000 orders a day with a new-order alert each, plus cancellations and delays, is tens of thousands of rows and a few hundred thousand queries a day.

**Fix.** Load the administrators and their device tokens **once** before the loop (two queries, not three per administrator), insert the rows in one batch, and keep the per-administrator rows because they hold read state. For the live socket, send one broadcast that carries the list of recipient ids, and let the client show the toast only if it is on the list.

---

### Finding 9. Login location lookups call two external services with no cache

**Where:** `app/Services/LoginHistory/IpGeolocator.php` (`ipwho.is`, then `ip-api.com` over plain HTTP), queued by `app/Jobs/ResolveLoginLocation.php`.

**Problems.**
1. Every sign-in looks the IP up again, even one seen a minute ago. There is no cache.
2. Each lookup can occupy a queue worker for up to 6 seconds (two 3-second timeouts).
3. `ip-api.com` is free only over HTTP and is rate-limited to about 45 requests a minute. A burst of captains signing in at the start of a shift can exceed it, and the lookups that fail are recorded as failed.

**Fix.** Cache the result per IP for 24 hours (`Cache::remember("geo:$ip", 86400, ...)`), put this job on the `default` queue with a short timeout, and consider a keyed provider with a real quota for production.

---

### Finding 10. Searches use `LIKE '%term%'`

`LIKE` with a leading wildcard cannot use an index. It is used for orders (number, customer name, phone, addresses, supplier names), captains, administrators, stores, login history and push logs. For small tables this is fine. It matters for **orders** and **login history**, the two that grow without limit.

**Fix.** Search the order number by prefix. For names and phones, either require at least 3 characters and search by prefix, or add a MySQL `FULLTEXT` index on `(customer_name, customer_phone)` and query it with `MATCH ... AGAINST`. The captain's own order search is bounded by their own orders and is acceptable.

---

### Finding 11. Dispatch suggestions call an external routing service in the request

**Where:** `app/Services/Dispatch/SuggestionService.php::suggest()`, `routing_timeout_ms` = 1000.

Opening the assignment dialog runs the candidate search (Redis geo search plus a few queries per ring) and then asks the routing service for travel times, **inside the dispatcher's request**. The result is cached for 75 seconds, which helps repeat views of the same order. A slow routing service holds a PHP worker for up to a second per click.

**Fix.** The 1-second cap and the straight-line fallback already protect this well. Two small additions: raise the cache to 120 seconds for orders still waiting for a captain, and make sure the routing client uses a connection pool or keep-alive so each call does not pay a new TLS handshake.

---

### Finding 12. Store portal polls on timers

**Where:** `src/app/portal/(app)/orders/page.tsx` (30 s) and `.../orders/[uuid]/page.tsx` (15 s).

Each store user with a tab open sends a request every 15 to 30 seconds. The `AnswerNotModified` middleware saves bandwidth but not the database work.

**Correction made while fixing this.** The first version of this finding said the portal also polled in hidden tabs. It does not: the library's default is not to poll
a hidden tab, and the detail page already stops polling once an order reaches a final status. Only the live map (finding 5) polled in the background. The portal pages now
say `refetchIntervalInBackground: false` explicitly so that cannot change silently. A visible tab still polls every 15 to 30 seconds; pushing order changes to the
store over Reverb would remove that, and is a larger change left out of this round.

---

### Finding 13. Expired API tokens are never deleted

**Where:** `routes/console.php`. There is no `sanctum:prune-expired` entry.

Admin tokens expire after 30 days (`config/sanctum.php`, `expiration` 43200). Captain tokens are deleted when they go unused for 24 hours, but rows of expired administrator and store tokens stay forever, and the table is read on **every authenticated request**.

**Fix.** Add `Schedule::command('sanctum:prune-expired --hours=24')->daily();`.

---

### Finding 14. Smaller items

| Item | Detail | Fix |
|---|---|---|
| Redis client | `REDIS_CLIENT=predis` is a pure-PHP client. A ping makes 4 to 6 Redis calls. | Install the `phpredis` extension and set `REDIS_CLIENT=phpredis`. |
| Session driver | `SESSION_DRIVER=database`. The API uses tokens, so sessions are rarely used, but any web route that starts a session writes to the table. | Use `redis` or `array` for sessions. |
| Cache flush on every write | The repository cache (`flushCache()`) is cleared by every write to that table, so for busy tables it is mostly misses. | Keep it for slow-changing tables (permissions, roles, settings). Do not rely on it for orders. |
| Order detail | `OrderResource` for one order loads about 20 relations and walks the whole wrapped family in PHP. An open detail page refetches on every event. | Acceptable per request. If it shows up in the slow log, cache the family lookup per request and drop relations the screen does not show. |
| Notification history | The captain notification history groups by `COALESCE(notification_id, uuid)`. | Fine at 30 days. Index `(user_type, user_id, notification_id)` if it ever slows. |

---

## 4. Environment

Not code, but it decides whether the code above is a problem.

- **MariaDB buffer pool.** The local `my.ini` has `innodb_buffer_pool_size=16M` (the XAMPP default). Everything above is much slower with a pool that small. Production needs roughly 60 to 70 percent of the database host's memory.
- **`skip-name-resolve`.** The local database repeatedly stops answering new connections ("lost connection at handshake: reading initial communication packet"), which is a recurring problem on this machine. A common cause on Windows is a reverse-DNS lookup on every connection. Adding `skip-name-resolve` to `my.ini` is cheap and worth trying; **I have not confirmed it is the cause.**
- **PHP.** Run `php artisan optimize` on every deploy (config, route and event caches), enable OPcache, and size PHP-FPM workers to the peak request rate rather than leaving the default.
- **Scheduler.** `dispatch:flush-gps` is scheduled every 30 seconds, which Laravel only honours if the scheduler runs continuously (`schedule:work`) or the cron entry is not killed after the minute. Confirm this on the server.
- **Reverb.** One process serves all dashboards. If you add a second web server, enable Reverb's Redis scaling (`REVERB_SCALING_ENABLED`) so events reach every socket.
- **Never serve production with `artisan serve`.** The built-in server is single-threaded on Windows, so one slow request blocks every other.

---

## 5. Ready-to-use changes

### 5.1 Indexes (one migration)

```php
Schema::table('orders', function (Blueprint $table) {
    $table->index(['driver_id', 'order_status'], 'orders_driver_status_index');
    $table->index(['store_client_id', 'order_status'], 'orders_store_status_index');
    $table->index('created_at', 'orders_created_at_index');
});
```

Create them with `ALGORITHM=INPLACE, LOCK=NONE` on a large production table, outside the busiest hours.

### 5.2 Order list sort column (done)

The order is "newest family first, parent before its collection legs", which mixes a descending and an ascending key, and MariaDB 10.4 has no descending indexes. It is folded into a **single descending number**, `orders.sort_key` (`App\Support\OrderSortKey`):

```
family * 10000 + 9999 - (how far this row's id is past its family's first row)
```

It is an ordinary indexed column that the order observer sets when a row is created (and again if its parent changes); the migration fills existing rows in ranges of ids. The list is `ORDER BY sort_key DESC, id DESC`.

**A mistake in the first version of this section, kept here because it is instructive:** it proposed a *generated* column. MySQL and MariaDB refuse a generated column that reads an `AUTO_INCREMENT` column (`id`), and the SQLite used by the tests accepts it, so the tests passed and the real database refused the migration. It was found only by running the migration against MariaDB. Verified there afterwards: all existing orders keyed, and `EXPLAIN` reads 15 rows from `orders_sort_key_index` with no sort step.

One visible difference to check: none intended. The collection legs of a wrapped order still list in the order they were written, below their customer row.

### 5.3 Queue split

`.env`:

```
QUEUE_CONNECTION=redis
```

Name the queues where the jobs are dispatched (`->onQueue('realtime')` on broadcasts and `RecomputeRoutePlan`, `'webhooks'` on `SendStoreWebhook`, `'push'` on the push jobs, `'media'` on the media jobs), then run one supervisor program per group:

```
[program:queue-realtime]
command=php artisan queue:work redis --queue=realtime --sleep=1 --tries=1 --timeout=20
numprocs=2

[program:queue-default]
command=php artisan queue:work redis --queue=default,push --sleep=1 --tries=1 --timeout=60
numprocs=2

[program:queue-webhooks]
command=php artisan queue:work redis --queue=webhooks --sleep=1 --tries=1 --timeout=30
numprocs=2

[program:queue-media]
command=php artisan queue:work redis --queue=media --sleep=3 --tries=1 --timeout=120
numprocs=1
```

`REDIS_QUEUE_RETRY_AFTER` must stay above the largest `--timeout`.

### 5.4 Throttle `last_used_at`

Create a token model that skips the write when the last one is under a minute old, and register it in `AppServiceProvider::boot()` with `Sanctum::usePersonalAccessTokenModel(...)`:

```php
class ThrottledAccessToken extends \Laravel\Sanctum\PersonalAccessToken
{
    public function save(array $options = []): bool
    {
        if ($this->exists && $this->isDirty('last_used_at') && count($this->getDirty()) === 1
            && $this->getOriginal('last_used_at') !== null
            && now()->diffInSeconds($this->getOriginal('last_used_at')) < 60) {
            return true;
        }

        return parent::save($options);
    }
}
```

This is an untested sketch. Run `tests/Feature/Driver` afterwards (the inactivity-logout tests depend on this column), except `DriverDocumentResubmissionApiTest`, which is known to hang as a whole class.

### 5.5 Live fleet cache

```php
// In the controller or service that serves the live map
return Cache::remember('live-fleet', 3, fn () => $this->fleet->onDuty());
```

And in `tracking/page.tsx`: `refetchIntervalInBackground: false`.

### 5.6 Housekeeping

```php
Schedule::command('sanctum:prune-expired --hours=24')->daily();
```

And cache the geo lookup: `Cache::remember("geo:$ip", 86400, fn () => $this->lookup($ip))`.

---

## 6. How to measure before and after

Do these before changing anything, so each fix can be shown to help.

1. **Slow query log.** In MySQL: `slow_query_log=1`, `long_query_time=0.1`, `log_queries_not_using_indexes=1`. Run a normal working day and read the top ten by total time (`pt-query-digest` or `mysqldumpslow -s t`). The first ten will confirm or correct the order of this document.
2. **Queries per request.** Add a temporary `DB::listen` counter in a test route, or use Laravel Pulse (slow queries, slow requests, queue throughput). Check these four requests:
   - `POST /api/driver/location` (expect about 8 queries; target 6 or fewer)
   - `GET /api/dashboard/orders` (target: one query for the rows, none for a count)
   - `GET /api/dashboard/dispatch/live` (target: 0 to 1 per 3 seconds in total)
   - `GET /api/dashboard/orders/{uuid}`
3. **Queue depth and age.** With Redis queues: `php artisan queue:monitor realtime:50,default:200,webhooks:200`. The number that matters is **how long the oldest job has waited**, not how many there are.
4. **A repeatable load test.** A k6 script with two scenarios: 200 captains posting a location every 8 seconds, and 20 dashboards polling the order list and live map. Run it before and after each fix.

**Targets worth holding** (adjust to taste): location ping p95 under 100 ms; order list p95 under 200 ms with 100,000 orders; oldest `realtime` job under 2 seconds; database CPU under 60 percent at peak.

---

## 7. What is already well done

Worth saying, because it shapes what to leave alone:

- Pings are adaptive (8 s moving, 45 s stationary), and a ping that arrives out of order never touches the location row.
- GPS history is buffered in Redis and written in batches, not one insert per ping.
- Hidden dashboard tabs are marked stale instead of refetched, and event refetches are debounced.
- The dashboard overview (`DashboardStatsService`) is cached.
- Store webhooks run with `tries = 1` and a hand-scheduled backoff, so a dead store endpoint cannot trap a worker in retries.
- Dispatch suggestions cap the routing call at 1 second and fall back to straight-line estimates, so a slow map service cannot freeze assignment.
- Every long retention (push log, GPS, integration log, login history) has a scheduled prune.