# Performance Optimization Plan

This plan is ordered by correctness, user impact, evidence from the baseline,
and rollout risk. Each batch must be reviewed and benchmarked before the next
batch begins.

## Batch 1 — Remove remote analytics from page requests

Priority: critical
Risk: medium
Expected benefit: eliminate current Dashboard, Content SEO, Content Planning,
and Traffic Report 500 responses; remove remote latency from user requests.

1. Introduce a local analytics service over synchronized GSC tables.
2. Port only the methods actually used by the four affected controllers.
3. Preserve historical/current source boundaries and weighted position rules.
4. Keep BigQuery access only in synchronization and explicit diagnostic paths.
5. Add comparison tests using fixed local rows before switching each controller.
6. Re-run the four route benchmarks after every controller conversion.

Rollback: switch one controller at a time; retain the remote service for sync
and diagnostic commands until all parity tests pass.

Progress:

- Dashboard conversion completed and verified on 2026-10-14.
- Content SEO conversion completed and verified on 2026-10-14. Its common
  28-day cold path meets the target; the 180-day cold path remains assigned to
  the shared SQL work in Batch 2.
- Content Planning conversion completed and verified on 2026-10-15. Its common
  28-day cold path meets the target; the 180-day cold path remains assigned to
  the shared SQL work in Batch 2.
- Traffic Report conversion completed and verified on 2026-10-15. The endpoint
  now uses three daily-aggregate scans and meets the 28-day cold target in the
  recorded sample. Its 180-day query grouping remains assigned to Batch 2.

## Batch 2 — Fix Cluster cold SQL paths

Priority: critical
Risk: medium
Expected benefit: reduce the 15–36 second cold paths toward the three-second
section target.

1. Capture `EXPLAIN` for the current and historical branches separately.
2. Replace the derived-union/group scan where possible with aggregation in each
   indexed source branch followed by a small merged result.
3. Verify whether the current source needs an evidence-based composite index
   beginning with property/query/date or query/property/date.
4. Verify whether the historical daily-query index should include site,
   query-hash, and date in the measured access order.
5. Move grouped-query filtering, ordering, and pagination into SQL where it does
   not alter category/assignment semantics.
6. Reconcile results against a fixed pre-change fixture before migration.

Traffic Report progress: a composite `site, dimension_hash, data_date` index was
measured and rolled back after it regressed the 28-day query and page groupings.
Daily totals plus monthly query/page rollups now serve long ranges with daily
boundary accuracy, reducing the measured 180-day cold request from 59.05
seconds to 12.62 seconds. The remaining cost is the long-range query-dimension
grouping; any further change should preserve the 28-day path and exact custom
range semantics. Post-backfill validation with a warm database buffer pool
measured the same 180-day request at 3.33 seconds cold at the Laravel cache
layer and 8.09 ms warm.

No index migration will be added until its query plan and write/storage cost are
recorded. Production rollout must use an online schema-change method appropriate
to the actual RDS/MySQL version and table size.

## Batch 3 — Cache versioning and invalidation

Priority: high
Risk: low-to-medium
Expected benefit: retain warm performance without serving stale analytics or
cross-scope data.

1. Add scoped data-version keys for GSC, GA4, cluster, URL, keyword, assignment,
   and permission mutations.
2. Include property/site, user/permission scope, all filters, pagination, sort,
   and relevant data versions in cache keys.
3. Increment versions only after committed writes or successful sync batches.
4. Add bounded TTL and lock-based stampede protection where supported.
5. Add cache hit/miss instrumentation without logging sensitive values.
6. Test that writes invalidate only affected scopes.

Rollback: versioned keys coexist with old keys and expire naturally.

## Batch 4 — SQL pagination and frontend request coordination

Priority: high
Risk: medium
Expected benefit: bounded payload/memory and sub-second common interactions.

1. Add consistent 10/25/50/100 server pagination to large tables.
2. Push supported search, numeric sort, and filters into SQL.
3. Keep independent pagination/sort state for each Cluster Detail table.
4. Add shared `AbortController`, request identity, timeout, and stale-response
   handling to the frontend request helper.
5. Debounce text search and deduplicate identical active GET requests.
6. Ensure every section resolves to data, empty, or actionable error state.

## Batch 5 — Multi-site/property isolation

Priority: critical before onboarding more properties
Risk: high
Expected benefit: correct 10+ property behavior and removal of leakage risk.

1. Define a canonical site/property mapping instead of relying on one configured
   Search Console property.
2. Require property scope in analytics joins, aggregates, cache keys, sync runs,
   authorization, and reports.
3. Make sync uniqueness and status per property.
4. Add cross-site fixtures proving that identical paths/queries cannot leak.
5. Simulate at least ten properties and record query plans and storage growth.

This batch requires explicit review of the data model before migration.

## Batch 6 — Queue reliability and capacity operations

Priority: medium
Risk: low-to-medium

1. Add bounded retry/backoff and per-property idempotency to sync jobs.
2. Add failed-job, duration, freshness, and row-delta monitoring.
3. Document Redis/ElastiCache, queue/SQS, RDS, and application-instance sizing
   from measured concurrency and working-set data.
4. Define retention, archive, partitioning feasibility, and reconciliation for
   high-cardinality GSC history.

## Batch 7 — Full regression and acceptance run

Priority: required before declaring completion

1. Cover every major module and CRUD workflow.
2. Exercise 7d, 28d, 90d, 180d, and custom ranges with all comparison modes.
3. Cover site, device, country, URL, keyword, role, and cross-site isolation.
4. Run fixed-dataset pre/post reconciliation for all analytics metrics.
5. Capture cold/warm backend and browser data-ready samples; report p50/p95
   where sample counts permit.
6. Produce `benchmark-results.md`, `regression-results.md`, and
   `aws-capacity-recommendations.md`.

## Review gates

Each batch stops at the following gate:

- relevant tests pass;
- before/after measurements are recorded;
- analytics parity is demonstrated;
- changed files and rollback path are documented;
- unresolved failures are stated without claiming the target is met.