WHERE referral_code = 'friend'
public.referral_headlines
?ref=... parameters.
| Referral Code | Campaign Name | Hero Headline | Subheadline | Primary CTA | Conversions | Status | Actions |
|---|
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).
// Cloudflare Caching Default
CacheKey = full_request_url;
// 10,000 unique URLs = 10,000 cold fetches to DB!
// 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');
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.
<!-- Head lacks canonical -->
<title>CoffeeHouse - Your Friends Are Here</title>
<!-- Search engines treat ?ref=friend as separate duplicate page -->
<!-- Always point search crawlers to clean root URL -->
<link rel="canonical" href="https://coffeehouse.example/" />
<meta name="robots" content="index, follow" />
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.
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
});
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 });
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.
<h1>Default Coffee</h1>
<script>
fetch('/api/ref').then(r => h1.innerText = r.title);
// Visitor sees jarring flicker after initial paint
</script>
// 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;
}
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.
const ref = params.get('ref');
headline.innerHTML = `Welcome ${ref} visitor!`;
// VULNERABLE: Attackers inject scripts via URL!
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.');
});
});