Sub-20ms Search on 460k+ URLs: Headless Chords…

The Bottleneck

Our client, a market-leading chords and lyrics aggregator managing a catalog exceeding 460,000 unique song pages, was operating on a monolithic legacy stack where a single Laravel instance attempted to serve both frontend rendering and backend data management. This architecture caused critical infrastructure failures: monolithic XML sitemap generation triggered fatal memory spikes and server timeouts, preventing search engine crawlers from indexing the majority of the catalog; relational SQL LIKE queries on massive tables created CPU bottlenecks and table locks under concurrent load; bulk ingestion of legacy JSON catalogs routinely exhausted PHP memory limits due to Eloquent query logging; and long-running synchronization jobs with Google APIs exceeded Cloudflare gateway timeout thresholds, crashing admin workflows. The direct business impact was severe—thousands of high-intent pages remained invisible to organic search for months, server infrastructure costs escalated due to constant CPU pressure, and unencrypted OAuth tokens stored in plaintext introduced a catastrophic compliance risk.

Architecture & Automation

We decoupled the platform into a true headless architecture: a Laravel 12 API backend (PHP 8.2+, Docker/Sail, MySQL 8.4, Redis) exposing optimized endpoints to an Astro 5 frontend running in Node SSR standalone mode and deployed via PM2 on a VPS. This separation eliminated frontend-backend contention and allowed independent scaling of compute layers.

Search was entirely offloaded from MySQL to Meilisearch via Laravel Scout, delivering typo-tolerant, in-memory results in under 20ms. Redis caches high-cardinality categories and sitemap indexes to prevent redundant database hits during crawler bursts.

To solve the crawl budget bottleneck, we implemented an algorithmic chunked sitemap architecture. Instead of a monolithic file, a dynamic sitemap index routes crawlers to 232+ paginated endpoints (/sitemaps/[page].xml). Each chunk uses fast, index-only scans on the compound ['is_published', 'id'] index to determine start_id and end_id, then retrieves records via WHERE id BETWEEN—eliminating the linear degradation of traditional SQL OFFSET. All sitemap responses carry Cache-Control: public, max-age=3600 and a fallback layout if the API is unreachable.

Automated indexation is handled by a custom integration with the Google Search Console API and Google Indexing API. A secure OAuth flow stores access and refresh tokens encrypted at rest using AES-256-CBC via Laravel's Crypt service. Daily scheduled commands (seo:sync-urls, seo:submit-indexing, seo:inspect-urls) programmatically force Google to index new or updated pages within the 150 URLs/day quota. Because syncing 460k URLs to the local registry exceeds HTTP timeouts, the Filament 3.2 admin panel triggers these jobs asynchronously via shell_exec('... > /dev/null 2>&1 &'), instantly releasing the HTTP thread and preventing gateway crashes. Eloquent Observers (SongObserver, ArtistObserver) hook into model lifecycles to auto-invalidate sitemap caches and dispatch re-indexing requests on every create, update, or delete.

The Astro frontend handles all interactive music tooling client-side to protect Core Web Vitals. A YIN autocorrelation pitch detector streams microphone input through the Web Audio API, rendering a real-time canvas oscilloscope. The metronome and rhythm accompaniment engine uses raw oscillators, pink noise generators, and bandpass/highpass filters—plus Tone.js polyphonic backing tracks—without downloading static audio assets. Chord transposition, diagram generation (dynamic SVGs from simple patterns like "x32010"), and piano/bass voicing computations run entirely in TypeScript (ChordEngine.ts, ChordRenderer.ts), preserving a low Interaction to Next Paint (INP). Server-side, Astro injects JSON-LD MusicComposition schema and preconnects to the API domain to minimize TCP handshake latency.

┌──────────────────────────────────────────────────────────────────────┐
│                         CLIENT BROWSER                               │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐  │
│  │ YIN Pitch    │  │ Chord SVG    │  │ Web Audio Metronome /    │  │
│  │ Detector     │  │ Transposer   │  │ Backing Track Synth      │  │
│  │ (Web Audio)  │  │ (TypeScript) │  │ (Tone.js / Oscillators)  │  │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘  │
└──────────────────────────────┬─────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────────────┐
│                       ASTRO 5 SSR (NODE/PM2)                         │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐  │
│  │ SSR JSON-LD  │  │ Sitemap      │  │ Preconnect / Cache       │  │
│  │ Schema       │  │ Index (232+) │  │ Fallbacks                │  │
│  │ Injection    │  │ (Range Pgnt) │  │                          │  │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘  │
└──────────────────────────────┬─────────────────────────────────────┘
                               │ API REST
                               ▼
┌──────────────────────────────────────────────────────────────────────┐
│                     LARAVEL 12 API (DOCKER/SAIL)                     │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐  │
│  │ Meilisearch  │  │ Redis Cache  │  │ Eloquent Observers       │  │
│  │ Scout Driver │  │ (Categories,│  │ (Auto-clear sitemap /    │  │
│  │ (<20ms)      │  │  Sitemaps)   │  │  Trigger re-index)       │  │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘  │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐  │
│  │ Filament 3.2 │  │ Encrypted    │  │ Async Background Jobs    │  │
│  │ Admin Panel  │  │ OAuth Store  │  │ (shell_exec & detach)    │  │
│  └──────────────┘  └──────────────┘  └──────────────────────────┘  │
└──────────────┬───────────────┬─────────────────────────────────────┘
               │               │
               ▼               ▼
        ┌──────────┐    ┌──────────────────┐
        │ MySQL 8.4│    │ Google APIs      │
        │ (PDO)    │    │ (Indexing + GSC) │
        └──────────┘    └──────────────────┘

Measurable ROI

Sitemap Reliability: Monolithic XML generation causing fatal memory spikes and server timeouts, blocking crawler access to the catalog -> 232+ algorithmically chunked sitemaps using compound-index range pagination (WHERE id BETWEEN), zero timeouts and complete crawler coverage.
Search Latency: SQL LIKE queries on 460k rows spiking CPU and locking tables under concurrent traffic -> Meilisearch in-memory typo-tolerant search delivering sub-20ms response times with zero MySQL load.
Indexation Velocity: Organic discovery leaving thousands of high-intent pages unindexed for months -> Automated Google Indexing API pipeline submitting 150 URLs/day with AES-256-CBC encrypted OAuth credentials, forcing indexation within days instead of months.
Bulk Ingestion: Legacy JSON imports exhausting PHP memory via Eloquent query logs -> Streaming RecursiveDirectoryIterator parser with disabled query logging and deferred Scout sync, achieving zero-memory-exhaustion ingestion.
Admin Job Stability: Syncing 460k URLs to the Search Console registry exceeding Cloudflare 100-second gateway timeouts, crashing admin panels -> Asynchronous background shell execution triggered from Filament, releasing HTTP threads instantly and eliminating gateway errors.
Interactive Audio Performance: Heavy static audio file downloads degrading LCP and INP on music tools -> 100% procedural Web Audio DSP and synthesized Tone.js engines, eliminating external asset latency and preserving low Interaction to Next Paint (INP) scores.