# Performance Baseline

Date: 2026-10-14
Environment: local Windows development workspace
Application: Laravel 12 / PHP 8.3.1 / MySQL 5.7.24
Database: `exponents_marketing`
Cache store: `file`

## Scope and methodology

This is the Checkpoint 1 baseline for the full Marketing Ops application. No
performance target is claimed as met by this document.

Authenticated GET routes were measured through Laravel's HTTP kernel with an
active administrator. The benchmark records response status, backend duration,
Laravel query count/time, memory delta, and response size. It does not measure
browser rendering or network transfer time.

Reproduce a route measurement with:

```bash
php scripts/performance/benchmark-route.php --route="/api/v1/workload?preset=28d&site=all&compare=none" --iterations=3
```

Reproduce the dataset and index inventory with:

```bash
php scripts/performance/dataset-summary.php
php artisan route:list
php artisan migrate:status
```

The benchmark utility accepts only registered GET routes. It does not mutate
application data and does not print credentials or environment secrets.

## Application inventory

`php artisan route:list` currently reports 123 routes.

| Module | Page route | Read API | Main implementation |
|---|---|---|---|
| Dashboard | `/marketing` | `/api/v1/dashboard` | `DashboardController`, `dashboard.blade.php` |
| Workload | `/marketing/workload` | `/api/v1/workload` | `WorkloadController`, `workload.blade.php` |
| Execution Pipeline | `/marketing/pipeline` | `/api/v1/pipeline` | `PipelineController`, `pipeline.blade.php` |
| Content SEO | `/marketing/content-seo` | `/api/v1/content-seo` | `ContentSeoController`, `content-seo.blade.php` |
| Content Planning | `/marketing/content-planning` | `/api/v1/content-planning`, `/options` | `ContentPlanningController`, `content-planning.blade.php` |
| Tasks | `/marketing/tasks` | `/api/v1/tasks`, `/options`, CRUD/progress | `TaskController`, `tasks.blade.php` |
| Traffic Report | `/marketing/traffic-report` | `/api/v1/traffic-report` | `TrafficReportController`, `traffic-report.blade.php` |
| Clusters | `/marketing/clusters` | `/api/v1/clusters` | API/Admin `ClusterController`, `clusters.blade.php` |
| Cluster Detail | `/marketing/clusters/{cluster}` | overview, performance, keyword analytics, queries, discovery, insights, activity, URL/keyword CRUD | API `ClusterController`, `ClusterAnalyticsService`, `cluster-detail.blade.php` |
| SEO Lead | `/marketing/seo-lead` | `/api/v1/seo-lead` | `SeoLeadController`, `seo-lead.blade.php` |
| URL Manager | `/admin/url-manager` | server-rendered CRUD, bulk, import/export, sync | `UrlManagerController` |
| Keyword Manager | `/admin/keyword-manager` | server-rendered CRUD plus server-side GSC DataTable | `KeywordManagerController` |
| Keyword categories | `/admin/keyword-categories` | server-rendered CRUD | `KeywordCategoryManagerController` |
| Cluster management | `/admin/clusters` | CRUD, URL/query discovery, keywords, targets | Admin controllers and `admin/clusters/form.blade.php` |
| Assignments | `/admin/assignments` | server-rendered CRUD | `AssignmentController` |
| Users | `/admin/users` | server-rendered CRUD | `UserController` |
| Data sync | `/admin/data-sync` | GSC/GA4 queue dispatch and status polling | `DataSyncController` |

Shared services include `DateRangeService`, `ClusterAccessService`,
`SearchConsoleService`, `ClusterAnalyticsService`,
`ClusterPlanningMetricsService`, `ClusterUrlAssignmentService`, and the GSC/GA4
sync services. GSC and GA4 scheduled refreshes are defined in
`routes/console.php`; queue jobs are `SyncGscBigQueryJob` and
`SyncGa4BigQueryJob`.

## Dataset

| Table | Rows | Date range | Approximate data + index size |
|---|---:|---|---:|
| `gsc_api_search_analytics` | 9,034,110 | 2025-05-16 to 2026-08-05 | 6.27 GB |
| `gsc_api_daily_query_metrics` | 3,576,929 | 2025-05-16 to 2026-08-05 | 1.82 GB |
| `gsc_url_query_daily_metrics` | 1,189,903 | 2025-05-16 to 2026-10-10 | 1.11 GB |
| `gsc_url_impressions` | 843,302 | 2026-08-06 to 2026-10-10 | 1.17 GB |
| `gsc_api_daily_page_metrics` | 510,167 | 2025-05-16 to 2026-08-05 | 291 MB |
| `ga4_landing_page_metrics` | 8,871 | 2026-08-06 to 2026-09-15 | 6.6 MB |
| `url_inventory` | 2,715 | n/a | 2.2 MB |
| `clusters` / keywords / URLs | 11 / 81 / 42 | n/a | small |

Cluster 3 exists. The local data covers the requested 180-day current period
and previous period.

## Measured backend results

These are individual development-machine samples, not p50/p95 results. “First”
is the first observed request in the benchmark process. “Second” benefits from
OS, database, and application warm-up. The explicitly noted cache-miss variant
used `country=ALL`, which has the same application semantics as `country=all`
but a different cache-key argument.

| Route/section | First | Second | Queries | Payload | Result |
|---|---:|---:|---:|---:|---|
| Cluster Detail HTML shell, 180d compare | 113.80 ms | 3.79 ms | 0 | 225,281 B | 200; shell only |
| Cluster overview, 180d compare | 179.58 ms | 33.25 ms | 14 / 8 | 9,659 B | 200 |
| Cluster grouped queries, 180d compare | **15,447.15 ms** | 144.92 ms | 8 / 5 | 23,734 B | 200; cold target failed |
| Cluster list, 180d compare | **36,061.12 ms** | 117.38 ms | 6 / 5 | 8,914 B | 200; cold target failed |
| Cluster list cache-miss variant | **25,988.21 ms** | n/a | 6 | 8,917 B | 200; cold target failed |
| Cluster activity | 60.75 ms | 4.72 ms | 3 | 3,807 B | 200 |
| Workload, 28d | 224.81 ms | 16.36 ms | 5 | 2,001 B | 200 |
| Pipeline, 28d | 56.33 ms | 20.46 ms | 3 | 2,035 B | 200 |
| Tasks, 28d | 57.98 ms | 10.35 ms | 4 | 1,226 B | 200 |
| SEO Lead, 28d | 114.56 ms | 24.58 ms | 3 | 3,543 B | 200 |
| Dashboard, 28d | 2,650.32 ms | n/a | 5 | 328 B | **500** |
| Content SEO, 28d | 2,235.40 ms | n/a | 3 | 292 B | **500** |
| Content Planning, 28d | 2,229.02 ms | n/a | 6 | 335 B | **500** |
| Traffic Report, 28d | 2,190.48 ms | n/a | 2 | 333 B | **500** |

A cold Cluster performance-section request ran for more than 150 seconds and
was terminated. It is recorded as exceeding the three-second target; no exact
completion time is claimed.

The slowest measured Cluster list statement consumed 25,815 ms of the 25,834
ms total SQL time. It groups current and historical rows by `query_hash` across
a derived `UNION ALL` table for both comparison periods. Although each branch
has indexes, MySQL 5.7 materializes and groups the derived result. This is the
first SQL optimization target.

MySQL 5.7 does not support `EXPLAIN ANALYZE`. Checkpoint 2 must use `EXPLAIN`,
row estimates, handler statistics, and measured execution time before and after
any index or query change.

## Bottleneck ranking

1. Cluster list comparison aggregation: 25.8–35.9 seconds of SQL time.
2. Cluster grouped-query cold path: 15.0 seconds of SQL time.
3. Some Cluster performance cold paths exceed 150 seconds.
4. Dashboard, Content SEO, Content Planning, and Traffic Report depend on
   `SearchConsoleService`, whose normal request methods execute remote BigQuery
   queries. They returned 500 locally rather than reading only from MySQL.
5. Cache hits hide expensive cold paths. Cache entries have bounded TTL but no
   synchronized-data version or mutation invalidation.
6. Multi-site filtering is incomplete. Several analytics paths begin with the
   single configured property; Traffic Report documents that its site filter
   does not directly constrain the GSC hostname.
7. Several large lists are resolved and sorted in PHP before being sliced,
   including Cluster query rows and metric-filtered cluster lists.
8. Shared frontend GET requests have no timeout, cancellation, or request
   deduplication. Cluster Detail has request tokens, but other pages can still
   accept stale responses.

## Browser and log investigation

No Edge, Chrome, or in-app browser surface was available to the automation
environment. Browser Network timing, render timing, Console errors, duplicate
request detection, and loader completion remain unmeasured and must not be
reported as passing.

The authenticated backend benchmark reproduced the four 500 responses above.
Their controllers use the remote-backed `SearchConsoleService`; replacing this
request-time dependency is prioritized before browser acceptance testing.

## Existing safeguards and useful work

- Cluster Detail already has independently requested analytics sections,
  `Promise.allSettled`, section errors, and stale-result request tokens.
- Local daily GSC query/page and URL/query aggregate tables exist.
- Clicks and impressions are additive; CTR and position are calculated from
  aggregate totals.
- GSC MSV uses available synchronized days and `30.4375`.
- Cluster URL assignments enforce shared support URLs and one active global
  pillar per inventory URL.
- GSC and GA4 syncs are queued and write in bounded chunks.
- All migrations are applied.
- The current automated suite passes with SQLite explicitly enabled:

```text
php -d extension=pdo_sqlite vendor/bin/phpunit --configuration phpunit.xml.dist
OK (40 tests, 170 assertions)
```

The tests are concentrated on Cluster analytics, dates, keyword/URL assignment,
login, and URL Manager. They do not cover the required application-wide browser
and filter matrix.

## Checkpoint 1 conclusion

The application does not currently meet the stated acceptance criteria. The
HTML shell is not the bottleneck; cold analytics SQL and live remote analytics
dependencies are. Risky migrations and broad refactors remain deferred until
the ordered changes in `optimization-plan.md` are reviewed.