Puzzle #11 ยท CoffeeHouse Dynamic Referral Router
DATABASE-DRIVEN HEADLINE ENGINE VIA QUERY PARAMETER
Database: Online (7 Rows)
Back to Showcase
Conversion Rate Optimization ยท Personalization Architecture

Referral-Driven Dynamic Headline Website

Build a website where the hero headline dynamically changes based on where the visitor arrived from, with all marketing copy stored in and queried from a relational database.

Challenge Clue Decoded
Use a Query parameter: Read incoming URLs via new URLSearchParams(window.location.search).get('ref'), sanitize the key, and fetch matching copy from the database table referral_headlines.
Test Referrals:
https://coffeehouse.example/
CoffeeHouse
โœจ Friend Exclusive: 20% Off First Pour

Your Friends Are Already Here

Join thousands enjoying the finest coffee experience

50+ Locations
10k+ Happy Customers
4.9 Rating โ˜…
Premium Coffee Experience
Param: ref="friend"
Database Query: WHERE referral_code = 'friend'
Cache: HIT (Edge KV)
Resolution Latency: 1.4ms
Active Campaign
PostgreSQL Table: public.referral_headlines
Changes made here persist in local state and immediately alter what visitors see when using matching ?ref=... parameters.
Referral Code Campaign Name Hero Headline Subheadline Primary CTA Conversions Status Actions
Interactive SQL Console & Query Runner
QUICK QUERIES:
Ready ยท Connected to PostgreSQL In-Memory Engine Execution: 0.8ms
Growth Marketing Campaign URL Builder
Generate tracking links for influencers, social ads, and email campaigns. Test how parameter normalization behaves in real time.
https://coffeehouse.example/?ref=friend&utm_medium=social&utm_campaign=summer_roast_launch
TRAP 1

CDN Edge Cache Invalidation & Query Parameter Bloat

CDNs treat unique query parameter strings as distinct cache keys. If 10,000 visitors arrive with varying parameters like ?ref=friend&utm_id=8923, naive CDN configurations miss the cache on 100% of requests, hammering the database and destroying Time to First Byte (TTFB).

โŒ Broken Pattern (Full URL Caching) // Cloudflare Caching Default
CacheKey = full_request_url;
// 10,000 unique URLs = 10,000 cold fetches to DB!
โœ“ Production Fix (Normalized Edge Routing) // Edge Middleware extracts & normalizes 'ref'
const ref = normalizeRef(req.query.get('ref'));
// Rewrite internally to cached bucket: /static-ch?ref=friend
res.headers.set('Cache-Control', 's-maxage=3600, stale-while-revalidate=86400');
TRAP 2

SEO Canonicalization & Duplicate Content Penalties

Googlebot crawls links containing ?ref=... parameters. Without an explicit canonical link tag in the document <head>, Google indexes multiple copies of your landing page with minor headline differences, fragmenting page rank authority.

โŒ Broken Pattern (No Canonical Tag) <!-- Head lacks canonical -->
<title>CoffeeHouse - Your Friends Are Here</title>
<!-- Search engines treat ?ref=friend as separate duplicate page -->
โœ“ Production Fix (Canonical Root Pointer) <!-- Always point search crawlers to clean root URL -->
<link rel="canonical" href="https://coffeehouse.example/" />
<meta name="robots" content="index, follow" />
TRAP 3

Database Cold Starts & Latency Spikes on Viral Surges

Directly executing SELECT * FROM referral_headlines WHERE referral_code = $1 on every un-cached visitor connection incurs 50ms to 250ms of network overhead and database pool exhaustion during marketing spikes.

โŒ Broken Pattern (Direct DB Hit on Every Hit) app.get('/', async (req, res) => {
  const row = await db.query('SELECT * FROM headlines WHERE code = $1', [ref]);
  // 20,000 req/sec crushes the database connection pool
});
โœ“ Production Fix (Edge KV / In-Memory LRU Cache) const cached = await edgeKV.get(`ref:${ref}`);
if (cached) return render(cached);
const row = await db.query(...);
await edgeKV.put(`ref:${ref}`, row, { ttl: 300 });
TRAP 4

Flash of Unpersonalized Content (FOUP) & Layout Shifts

When performing client-side DOM replacement asynchronously, users see the generic default headline for 200ms before it suddenly flips to the personalized version. This causes visual jitter, degrades Cumulative Layout Shift (CLS), and betrays the personalization illusion.

โŒ Broken Pattern (Late Client-Side Pop) <h1>Default Coffee</h1>
<script>
fetch('/api/ref').then(r => h1.innerText = r.title);
// Visitor sees jarring flicker after initial paint
</script>
โœ“ Production Fix (SSR / Edge Stream or Smooth Crossfade) // Edge injected HTML stream or smooth opacity crossfade:
.ch-headline {
  min-height: 1.2em; /* Preserves vertical bounding box */
  transition: opacity 0.2s ease, transform 0.2s ease;
}
TRAP 5

Ref Spoofing, Reflected XSS & Malicious Payloads

Never render raw query parameter strings directly into the DOM with innerHTML. Attackers craft malicious links like /?ref=<img src=x onerror=alert(1)> to execute arbitrary JavaScript in the victim's session.

โŒ Broken Pattern (Reflective XSS) const ref = params.get('ref');
headline.innerHTML = `Welcome ${ref} visitor!`;
// VULNERABLE: Attackers inject scripts via URL!
โœ“ Production Fix (Whitelist Sanitization + textContent) const safeRef = (params.get('ref') || '').replace(/[^a-z0-9_-]/gi, '');
const copy = databaseWhitelist.get(safeRef) || defaultCopy;
headline.textContent = copy.headline; // Safe text node assignment
-- PostgreSQL / Supabase Schema for Puzzle #11: Referral Headlines
CREATE TABLE IF NOT EXISTS public.referral_headlines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    referral_code VARCHAR(64) NOT NULL UNIQUE,
    campaign_name VARCHAR(128) NOT NULL,
    headline TEXT NOT NULL,
    subheadline TEXT NOT NULL,
    cta_primary VARCHAR(64) NOT NULL DEFAULT 'Explore Menu',
    cta_secondary VARCHAR(64) NOT NULL DEFAULT 'Find Store',
    badge_text VARCHAR(64) DEFAULT NULL,
    accent_color VARCHAR(32) DEFAULT '#b85d19',
    conversion_count INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Unique index for sub-millisecond lookups
CREATE INDEX IF NOT EXISTS idx_referral_headlines_code 
    ON public.referral_headlines (referral_code);

-- Enable Row Level Security (RLS)
ALTER TABLE public.referral_headlines ENABLE ROW LEVEL SECURITY;

-- Allow public read access to active referral campaigns
CREATE POLICY "Allow public read on active referral headlines"
    ON public.referral_headlines FOR SELECT
    USING (is_active = true);

-- Seed screenshot referral record
INSERT INTO public.referral_headlines (
    referral_code, campaign_name, headline, subheadline, cta_primary, cta_secondary, badge_text
) VALUES (
    'friend',
    'Word of Mouth & Friend Referrals',
    'Your Friends Are Already Here',
    'Join thousands enjoying the finest coffee experience',
    'Explore Menu',
    'Find Store',
    'Friend Exclusive Perk: 20% Off First Brew'
) ON CONFLICT (referral_code) DO NOTHING;
// middleware.ts (Next.js Edge Middleware for Vercel / Edge Runtime)
import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';

export async function middleware(request: NextRequest) {
  const url = request.nextUrl;
  const rawRef = url.searchParams.get('ref') || url.searchParams.get('utm_source');

  // Sanitize referral code
  const sanitizedRef = rawRef ? rawRef.toLowerCase().replace(/[^a-z0-9_-]/g, '') : 'default';

  // Clone headers and inject normalized referral header for SSR hydration
  const requestHeaders = new Headers(request.headers);
  requestHeaders.set('x-referral-source', sanitizedRef);

  const response = NextResponse.next({
    request: { headers: requestHeaders },
  });

  // Enable CDN caching while keeping cache keys aligned
  response.headers.set('Vary', 'x-referral-source');
  return response;
}

export const config = {
  matcher: ['/((?!api|_next/static|_next/image|favicon.ico).*)'],
};
// cloudflare-worker.js (Cloudflare Workers + Edge KV)
export default {
  async fetch(request, env, ctx) {
    const url = new URL(request.url);
    const ref = (url.searchParams.get('ref') || 'default').toLowerCase().replace(/[^a-z0-9_-]/g, '');

    // 1. Check Edge KV cache
    let copy = await env.HEADLINES_KV.get(`ref:${ref}`, { type: 'json' });

    // 2. Cache miss -> query Supabase / Postgres REST API
    if (!copy) {
      const res = await fetch(`${env.SUPABASE_URL}/rest/v1/referral_headlines?referral_code=eq.${ref}&select=*`, {
        headers: { 'apikey': env.SUPABASE_ANON_KEY }
      });
      const rows = await res.json();
      copy = rows[0] || (await env.HEADLINES_KV.get('ref:default', { type: 'json' }));
      
      // Cache for 10 minutes
      ctx.waitUntil(env.HEADLINES_KV.put(`ref:${ref}`, JSON.stringify(copy), { expirationTtl: 600 }));
    }

    // 3. Inject into HTML stream via HTMLRewriter
    const originResponse = await fetch(request);
    return new HTMLRewriter()
      .on('#ch-title', { element(e) { e.setInnerContent(copy.headline); } })
      .on('#ch-desc', { element(e) { e.setInnerContent(copy.subheadline); } })
      .transform(originResponse);
  }
};
// Client-side Query Parameter Resolver
document.addEventListener('DOMContentLoaded', () => {
  // 1. Parse query parameter (The Clue)
  const params = new URLSearchParams(window.location.search);
  const refCode = (params.get('ref') || params.get('utm_source') || 'default')
    .toLowerCase()
    .trim()
    .replace(/[^a-z0-9_-]/g, '');

  // 2. Fetch copy from database or fallback
  fetch(`/api/referral?code=${encodeURIComponent(refCode)}`)
    .then(res => res.json())
    .then(data => {
      if (data && data.headline) {
        document.getElementById('ch-title').textContent = data.headline;
        document.getElementById('ch-desc').textContent = data.subheadline;
        if (data.cta_primary) document.getElementById('ch-cta-text').textContent = data.cta_primary;
      }
    })
    .catch(() => {
      // Graceful fallback to default copy
      console.warn('Referral lookup failed; maintaining default copy.');
    });
});