3 · Performance, database & SEO

TL;DR

Objective — measure Core Web Vitals (inspect before changing), audit the live database for index coverage and N+1 queries, and fix anything that blocks Google from indexing public pages — because speed and discoverability decide whether real users (and Google) stick around.

User part + done-when

👤 User part — running PageSpeed Insights in a browser to read the mobile + desktop scores; the agent can read the code and propose fixes, but a person triggers the live measurement.

Done when Core Web Vitals hit their targets, the live database is indexed with no N+1 hotspots, and no 5xx/404/noindex/canonical issue blocks Google from public pages.  ·  ⏱ ~1.5 hr  ·  🔀 mixed (👤 PageSpeed run, 🤖 DB + SEO).

Background

Speed and discoverability — the audits that decide whether real users (and Google) stick around. Inspect and document before changing anything: on a CodeCanyon tree you never rewrite Blade views or run package updates unattended.

Steps

1. Measure Core Web Vitals

Measure with PageSpeed Insights (mobile first, then desktop), then inspect the codebase before changing anything.

Metric	Target
Performance score	Mobile > 80 · Desktop > 90
LCP	< 2.5s
CLS	< 0.1
TTFB	< 800ms (< 200ms ideal)

Who does this

👤 User runs PageSpeed Insights in the browser (mobile first, then desktop) and reads the scores. 🤖 Agent inspects the codebase and proposes the fixes.

1. Run PageSpeed Insights, then inspect the code before changing anything. Common wins: compress oversized images and serve WebP, set width/height on every <img> to kill layout shift, mark the LCP image fetchpriority="high" (and never lazy-load it), and enable CDN caching/compression.

   <img src="hero.webp" fetchpriority="high" width="1920" height="1080" alt="Product dashboard">
   <img src="feature.webp" loading="lazy" width="800" height="600" alt="Reporting view">

   Find oversized images and triage by the severity tree, then compress with the CLI tools:

   # Critical: > 1MB ; High: 500KB–1MB ; Medium: 200KB–500KB
   find public storage -type f \( -name "*.jpg" -o -name "*.jpeg" -o -name "*.png" \) -size +500k \
     -exec ls -lh {} \; 2>/dev/null

   Source size	Severity	Target
   > 1MB	CRITICAL	< 200KB
   500KB–1MB	HIGH	< 150KB
   200KB–500KB	MEDIUM	< 100KB
   < 200KB	Acceptable	consider WebP

   cp input.png input.png.bak                              # back up before any rewrite
   convert input.png -resize 1920x -quality 85 -strip output.png   # ImageMagick resize+compress
   convert input.png -quality 80 output.webp               # WebP conversion
   pngquant --quality=65-80 --output output.png input.png  # lossy PNG
   jpegoptim --size=200k image.jpg                          # JPEG to target size

   Inspect → Plan → Approve → Fix

   On a CodeCanyon tree, document oversized images and propose the fix plan first — never batch-rewrite assets unattended.

   Configure the CDN (Cloudflare) — the 8-point pass, then verify Brotli + cache HIT:

   1. Speed → Settings: enable Speed Brain, Cloudflare Fonts, Early Hints
   2. Speed → Protocol Optimization: HTTP/2, HTTP/3, 0-RTT
   3. Speed → Smart Shield: Smart Tiered Cache + Connection Reuse
   4. Caching → Configuration: Standard; Browser Cache TTL 1 month; Always Online ON
   5. SSL/TLS: Full (strict); Always Use HTTPS; TLS 1.3
   6. Network: IPv6 ON, WebSockets ON, IP Geolocation ON
   7. Scrape Shield: Email Obfuscation ON
   8. Caching → Purge Everything (then wait 2–5 min)

   curl -I "https://<YOUR_DOMAIN>" 2>/dev/null | grep -iE "cf-cache|cache-control|content-encoding"
   # Expected: cf-cache-status: HIT (after warm-up) and content-encoding: br (Brotli)

   Classify image debt before editing templates:

   find public resources -type f \( -iname "*.png" -o -iname "*.jpg" -o -iname "*.jpeg" \) -size +500k -print
   find public resources -type f \( -iname "*.blade.php" -o -iname "*.php" \) \
     -exec perl -ne 'print "$ARGV:$.:$_" if /<img\b/ && !/\b(width|height)=/' {} +
   # Expected: no oversized uncompressed hero images; every rendered image has width/height

   Use this severity guide: P0 = LCP hero is oversized/lazy-loaded/missing dimensions, P1 = repeated public images >500 KB, P2 = admin-only decorative images. Prefer WebP/AVIF plus the original fallback when browser coverage matters.

   Then verify the CDN layer instead of assuming it works:

   CDN setting	Required launch value
   SSL mode	Full or Full (strict)
   Always Use HTTPS	On
   Brotli	On
   HTTP/2 / HTTP/3	On when available
   Cache static assets	Long TTL for versioned build/ assets
   Bypass dynamic pages	Admin, checkout, account, CSRF-sensitive POSTs
   Image polish	Only if it does not rewrite broken vendor URLs
   Development Mode	Off

   curl -sI --compressed https://<YOUR_DOMAIN>/build/<KNOWN_ASSET>.css \
     | grep -iE 'content-encoding|cache-control|cf-cache-status'
   # Expected: br/gzip content-encoding and a cacheable static-asset response

   • ✅ Mobile/desktop scores meet the table targets; LCP < 2.5s, CLS < 0.1, TTFB < 800ms.

2. Audit the live database

Audit the live database (this complements the static migration analysis).

1. Confirm index coverage — every foreign-key and commonly-searched column (email, slug, uuid, status, type, created_at) should be indexed, using the live database rather than only reading migrations.

   SELECT k.TABLE_NAME, k.COLUMN_NAME, k.CONSTRAINT_NAME
   FROM information_schema.KEY_COLUMN_USAGE k
   LEFT JOIN information_schema.STATISTICS s
     ON s.TABLE_SCHEMA = k.TABLE_SCHEMA
    AND s.TABLE_NAME = k.TABLE_NAME
    AND s.COLUMN_NAME = k.COLUMN_NAME
   WHERE k.TABLE_SCHEMA = DATABASE()
     AND k.REFERENCED_TABLE_NAME IS NOT NULL
     AND s.INDEX_NAME IS NULL;
   
   SELECT c.TABLE_NAME, c.COLUMN_NAME
   FROM information_schema.COLUMNS c
   WHERE c.TABLE_SCHEMA = DATABASE()
     AND c.COLUMN_NAME IN ('email', 'slug', 'uuid', 'status', 'type', 'created_at')
     AND NOT EXISTS (
       SELECT 1 FROM information_schema.STATISTICS s
       WHERE s.TABLE_SCHEMA = c.TABLE_SCHEMA
         AND s.TABLE_NAME = c.TABLE_NAME
         AND s.COLUMN_NAME = c.COLUMN_NAME
     );

   For app-specific search screens, run the actual search/filter endpoints in Tinker or the browser and capture the SQL plan for the slow query before adding an index.

   • ✅ Every FK and commonly-searched column carries an index.

2. Hunt N+1 queries with Debugbar (dev-only); fix with eager loading.

   // Before (N+1)
   $users = User::all();
   // After
   $users = User::with('company')->get();

   • ✅ No N+1 hotspots remain; offending queries are eager-loaded.

3. Confirm hygiene with concrete checks — no production data in staging, sensitive columns are encrypted at rest, and the DB backup runs.

   No real user/payment data leaked into staging (run in php artisan tinker):

   User::where('email', 'NOT LIKE', '%@example.%')->count();   // Expected: 0 in staging/local
   DB::table('payments')->whereNotNull('card_last_four')->count(); // Expected: 0

   Encryption at rest — the raw stored value must be ciphertext, not plaintext (run in tinker against a sensitive record):

   $record->getRawOriginal('value');  // Expected: encrypted gibberish, NOT the plaintext secret

   Backup runs (or a conscious skip):

   if php artisan list 2>/dev/null | grep -q '^  backup:run'; then
     php artisan backup:run --only-db
   else
     echo "SKIP: spatie/laravel-backup not installed — confirm your backup path in Phase 7 instead"
   fi

   • ✅ Staging shows 0 non-test users and 0 stored card data, getRawOriginal() returns ciphertext for sensitive columns, and the DB backup runs clean.

3. Fix SEO & indexing

Fix anything that blocks Google from indexing public pages: 5xx errors (P0), 404s (P1), missing canonicals, stray noindex, broken links, and robots.txt.

1. Add a canonical to the layout <head>.

   <link rel="canonical" href="{{ strtok(url()->current(), '?') }}">

   • ✅ Every public page emits a canonical URL.

2. Resolve indexing blockers across the public surface. Keep noindex on private pages (admin, checkout, search results); strip it from public ones (home, pricing, blog). Ensure robots.txt allows public pages, blocks /admin/, and references the sitemap. Give every image descriptive alt text and dimensions; complete the meta/OG tag set.

   • ✅ No 5xx/404/stray-noindex on public pages; robots.txt + sitemap correct; meta/OG complete.

3. Audit Google Search Console and fix by priority. Open GSC → Indexing → Pages, export the issue list, then categorize and fix in priority order:

   Issue	Priority
   Server error (5xx)	P0 — immediate
   Not found (404)	P1 — this week
   Duplicate without canonical	P2 — this week
   Excluded by noindex	P3 — verify intentional
   Crawled — not indexed	P4 — content improvement

   Charset must be first. Ensure <meta charset="UTF-8"> is the first meta tag in <head> (a late charset can force a re-parse and corrupt early markup):

   curl -s "https://<YOUR_DOMAIN>" | head -c 1024 | grep -i "charset"
   # Expected: charset appears within the first 1KB, before other meta tags

   Collapse redirect chains — a fixed URL should reach its destination in at most one hop:

   curl -sIL "https://<YOUR_DOMAIN>/<old-url>" | grep -iE "^HTTP|^location"
   # Expected: at most one 301 → final 200. Point intermediate redirects straight at the destination.

   Then use URL Inspection → “Request Indexing” for each fixed URL.

   • ✅ GSC shows no unresolved errors on public pages (P0/P1 cleared first), charset is the first meta tag, no multi-hop redirect chains, and fixed URLs are submitted for re-indexing.

Checklist

Do not mark this step done until every box below is checked.

• 👤 Core Web Vitals on target — Mobile > 80 · Desktop > 90; LCP < 2.5s; CLS < 0.1; TTFB < 800ms.
• 🤖 Database indexed — FK + searched columns indexed; no N+1 hotspots.
• 🤖 DB hygiene confirmed — no prod data in staging, sensitive columns encrypted, backup runs.
• 🤖 SEO unblocked — canonicals added, noindex/robots.txt/sitemap correct, meta/OG complete.

→Next step

Accessibility & cookies