Add (created_at, customer_id) index to mobile_api_requests for dashboard perf

## Why `mobile_api_requests` (created + migrated HERE in tdr/thedarkroom at `laravel/app/database/migrations/2020_09_19_122413_create_mobile_api_requests_table.php`; written at runtime by the **tdr-fos** app's `BaseController`) has grown to ~22M rows (~4.3 GB data, ~3.1 GB index; id range ~8.3M–30M). The tdr-fos admin dashboard (`app/Http/Controllers/DashboardController.php`) runs `count(distinct customer_id)` aggregates filtered by `created_at`: - `index()` → `uniqueUsersToday` - `apiStats()` → `getActiveUsers()` → `countActiveUsers()` ×3 (last 5min / 15min / 1hr) The table has **no index on `created_at`** (existing indexes: `endpoint`, `customer_id`, `order_id`, `success`, `error_message` — see the create migration). So each aggregate is a large/full scan taking **40–66s**. The dashboard polls its stats endpoint on a timer; MySQL does not cancel a query when the HTTP client times out, so the slow queries **pile up** and saturate `tdr_laravel_production` — the same DB the live mobile API uses. This took the dashboard down (timeouts) and loaded the production DB. (Diagnosed in a tdr-fos session on 2026-07-02; queries only cleared once the external poller stopped.) ## What Add ONE composite index on `mobile_api_requests`: INDEX idx_created_customer (created_at, customer_id) - **Covering** for the worst query (`count(distinct customer_id) where created_at >= ? and customer_id is not null` is served entirely from the index — no row lookups). - The leading `created_at` column also speeds every other dashboard range query (top endpoints, request volume, slowest endpoints, version distribution — all filter `created_at >= ?`). - Do NOT also add a bare `created_at` index — the composite's leading column covers range-only use. `customer_id` keeps its existing single-column index (leave unchanged). ## How **Location / convention:** new migration in tdr/thedarkroom → `laravel/app/database/migrations/` (Laravel 4.2). Mirror existing style (e.g. `2020_11_11_013333_add_index_to_queues_table.php`): filename `YYYY_MM_DD_HHMMSS_add_created_customer_index_to_mobile_api_requests.php`, class `AddCreatedCustomerIndexToMobileApiRequests extends Migration`, with `up()` / `down()`. **Recommended migration body — raw online DDL + existence guard** (explicit online algorithm; idempotent so it no-ops if the index was pre-created manually off-peak): public function up() { $exists = DB::table('information_schema.statistics') ->where('table_schema', DB::connection()->getDatabaseName()) ->where('table_name', 'mobile_api_requests') ->where('index_name', 'idx_created_customer') ->exists(); if (! $exists) { DB::statement( 'ALTER TABLE `mobile_api_requests` ' . 'ADD INDEX `idx_created_customer` (`created_at`, `customer_id`), ' . 'ALGORITHM=INPLACE, LOCK=NONE' ); } } public function down() { DB::statement('ALTER TABLE `mobile_api_requests` DROP INDEX `idx_created_customer`'); } (`$table->index(['created_at','customer_id'], 'idx_created_customer')` also works — MySQL 5.6+ defaults ADD INDEX to online INPLACE — but the raw statement makes the online algorithm explicit and the guard makes it safe to re-run.) **Cost / risk (table ~22M rows):** - Online: `ALGORITHM=INPLACE, LOCK=NONE` keeps the table readable AND writable during the build — no downtime; the mobile API keeps inserting. - Time: a few minutes (~2–6 warm buffer pool/SSD; up to ~10–15 under load / cold cache). - New index ≈ 0.5–0.7 GB; ensure a couple GB free disk (build also writes a temp online-alter log). - Table is append-heavy (every mobile API request inserts). Concurrent writes during the build are buffered in `innodb_online_alter_log`; a long build under heavy writes could overflow it and roll back (harmless, just retry). Mitigate: **run off-peak**; optionally bump `innodb_online_alter_log_max_size` to 512M for the operation. - **Preferred rollout:** run the `ALTER` manually off-peak (or via `pt-online-schema-change` if extra cautious), then let the guarded migration record itself as applied on the next deploy (no-op since the index already exists). Avoids a multi-minute stall inside `php artisan migrate` during deploy. **Verify after:** - `SHOW INDEX FROM mobile_api_requests` lists `idx_created_customer`. - `EXPLAIN` the count-distinct query shows it using the new index (range on `created_at`, `Using index`). - Dashboard active-users queries drop from ~60s to sub-second. ## Cross-repo coordination - The queries live in **tdr-fos** (`DashboardController`). tdr-fos will SEPARATELY add stats caching + a slower poll interval so the dashboard cannot hammer the DB even absent the index. This brief covers ONLY the schema/index change here in tdr/thedarkroom. - After this ships, work resumes in tdr-fos to deploy the app-side changes and re-enable the dashboard.