Case files

Real problems from production

What was about to break, how I traced it, what shipped. Plus the playbook I run when the stakes are money and real-time traffic.

11 case files7 playbook entriesJump to playbook

Problem

Affiliate networks fire several deposit postbacks per click — concurrently and out of order. Running totals, FTD and redeposit events could double-count or vanish. On top of that, one partner killed requests after 5 s while the handler waited 1–6 s on the tracker.

Fix

Per-click Redis lock (SET NX EX, 10 s TTL, bounded retries) around a Zod-validated click state. If state is missing it is rebuilt from ClickHouse deposit logs. State and log are committed first, the tracker forward runs asynchronously.

The detail

A redeposit arriving before any known deposit still flags the click as newly qualified, so the qualification event is never lost. Replaced execFile(curl) with native fetch — another ~100 ms of process-fork overhead gone.

Result

Deposit postback latency 1–6 s → under 200 ms; lead postbacks 1–5 s → under 10 ms. Partner timeouts disappeared.

// illustrative, rewritten from scratch

s2s/deposit.service.ts

async handleDeposit(pb: DepositPostback) {
  return this.locks.withLock(`s2s-lock:${pb.clickId}`, { ttl: 10, retries: 5 }, async () => {
    const state = (await this.state.get(pb.clickId))
      ?? (await this.rebuildFromClickHouse(pb.clickId))

    const usd = await this.fx.toUsd(pb.amount, pb.currency, pb.occurredAt)
    const next = ClickState.parse(applyDeposit(state, usd))

    await Promise.all([
      this.state.set(pb.clickId, next),
      this.ch.insert('deposit_logs', [toLogRow(pb, next, usd)]),
    ])

    setImmediate(() => {
      this.tracker.forward(pb, next).catch(err => this.log.warn({ err, clickId: pb.clickId }, 'tracker forward failed'))
    })

    return { ok: true, ftd: next.isNewlyQualified, n: next.depositCount }
  })
}

NestJS
Fastify
Redis
ClickHouse
Zod

Problem

The same click arrived from the tracker (no ad id) and from the Facebook sync (with ad id). Because ad id was part of the sorting key, ReplacingMergeTree treated them as different rows — every metric downstream was inflated.

Fix

Re-keyed the table on clickid only, monthly partitions, version column for last-write-wins. Sync now just inserts in chunks and lets the engine dedupe; reads use FINAL scoped per partition. Shipped a migration with two paths: full re-sync or in-place keep-data.

The detail

Reconciliation evolved: first greatest() to ignore stale zeros from the tracker API, then trusting the tracker value so late chargebacks correct numbers down as well as up.

Result

One row per click, metrics match the tracker, no DELETE storms on the cluster.

// illustrative, rewritten from scratch

migrations/clicklog_order_by.sql

CREATE TABLE binom_clicklog_v2
(
  clickid     String,
  click_date  DateTime,
  campaign_id UInt32,
  fb_ad_id    String DEFAULT '',
  revenue     Decimal(18, 4),
  updated_at  DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
PARTITION BY toYYYYMM(click_date)
ORDER BY clickid;

SELECT campaign_id, sum(revenue)
FROM binom_clicklog_v2 FINAL
WHERE click_date >= now() - INTERVAL 1 MONTH
GROUP BY campaign_id
SETTINGS do_not_merge_across_partitions_select_final = 1;

ClickHouse
ReplacingMergeTree
BullMQ

Problem

Dashboard and pivot queries ran with 32 threads and no limits. A single heavy report could eat all ~11 GiB of RAM — after that even SELECT 1 failed and every service depending on analytics went dark.

Fix

Bounded every read query: fewer threads, hard execution timeout, per-query memory cap sized so two heavy reports fit side by side, partial-merge joins and spill-to-disk for big GROUP BY / ORDER BY. Ingestion queries deliberately left untouched.

The detail

Memory cap chosen from instance size, not a guess: 5 GB × 2 concurrent reports + headroom for merges and inserts.

Result

Heavy reports degrade gracefully (spill or time out) instead of OOM-killing the server.

// illustrative, rewritten from scratch

pivot/read-settings.ts

export const READ_QUERY_SETTINGS = {
  max_threads: 8,
  max_execution_time: 55,
  max_memory_usage: 5_000_000_000,
  join_algorithm: 'partial_merge',
  max_bytes_before_external_group_by: 2_000_000_000,
  max_bytes_before_external_sort: 2_000_000_000,
} as const

export function pivotQuery(sql: string, params: Record<string, unknown>) {
  return ch.query({
    query: sql,
    query_params: params,
    clickhouse_settings: READ_QUERY_SETTINGS,
  })
}

ClickHouse
NestJS

Problem

Refresh-token rotation with reuse detection is great — until a user has 6 tabs open. Parallel refreshes looked like token theft and nuked every session. Separately, login returned 500 whenever Redis blinked.

Fix

Rotation runs under a per-session lock and leaves a tombstone pointing to the child session for the rest of the session’s life: within a 30 s grace window a parallel refresh gets the child instead of tripping reuse detection; after it, any reuse is treated as theft. Real reuse revokes all sessions; force-logout wipes tombstones too, so the grace window can’t bypass revocation.

The detail

Absolute session lifetime survives rotation (TTL is never extended). With Redis down, an ephemeral session is issued with a signed 24 h expiry. Login compares against a dummy bcrypt hash for unknown users — no timing oracle.

Result

Multi-tab users stay logged in, stolen tokens still burn every session, auth survives Redis outages.

// illustrative, rewritten from scratch

auth/session-store.service.ts

async rotate(oldSid: string) {
  return this.locks.withLock(`rotate:${oldSid}`, { ttl: 5 }, async () => {
    const rec = await this.redis.getJson<Session>(key(oldSid))
    if (!rec || rec.absoluteExp < Date.now()) throw new UnauthorizedException()

    if (rec.rotatedTo) {
      if (Date.now() - rec.rotatedAt! <= ROTATION_GRACE_SEC * 1000) return this.get(rec.rotatedTo)
      await this.revokeAllForUser(rec.userId)
      throw new UnauthorizedException('refresh token reuse detected')
    }

    const child = await this.create(rec.userId, { absoluteExp: rec.absoluteExp })
    await this.redis.setJson(key(oldSid), { ...rec, rotatedTo: child.sid, rotatedAt: Date.now() }, { pxat: rec.absoluteExp })
    return child
  })
}

NestJS
Redis
JWT
bcrypt

Problem

The usage parser took the max number from any throttle header — including a “minutes to regain access” field — reporting phantom 95% usage and freezing syncs. Split-and-retry used Promise.all, so a failing range fanned out into 2^depth parallel calls per token: a fast way to get ad accounts checkpointed.

Fix

Parsed each header by its own schema (app, ad-account, nested business-use-case, insights throttle) into { maxUsage, regainAccessMs }. Honour explicit blocks (capped at 5 min), step back above 75%. Made recursion serial with spacing, surfaced lost days as errors instead of silent success, and made budget sync incremental by cursor.

The detail

Later the whole Graph fetch path (−1,313 lines) was retired in favour of first-party ingestion from a Chrome extension — see CASE-0x06.

Result

False cooldowns gone, no more burst patterns per token, then the dependency on Graph tokens removed entirely.

// illustrative, rewritten from scratch

fb/rate-limit.ts

export function parseRateLimit(h: Headers): { maxUsage: number, regainAccessMs: number } {
  const app = json(h.get('x-app-usage'))
  const acc = json(h.get('x-ad-account-usage'))
  const buc = Object.values(json(h.get('x-business-use-case-usage')) ?? {}).flat()

  const usages = [
    app?.call_count, app?.total_time, app?.total_cputime,
    acc?.acc_id_util_pct,
    ...buc.flatMap(b => [b.call_count, b.total_time, b.total_cputime]),
  ].filter(Number.isFinite)

  const regainMin = Math.max(0, ...buc.map(b => b.estimated_time_to_regain_access ?? 0))

  return { maxUsage: Math.max(0, ...usages), regainAccessMs: Math.min(regainMin * 60_000, 300_000) }
}

Meta Marketing API
NestJS
Zod

Problem

Server-side Graph tokens were rate-limited, fragile and risky for accounts. The ground truth lives in buyers’ live Ads Manager sessions inside anti-detect browser profiles — and it has to be matched to tracker conversions.

Fix

Backend for a Chrome extension that collects Ads Manager data: API-key-guarded ingest endpoints for spend, insights, accounts, pages and businesses, a sync-plan / sync-finish protocol and a server-side coverage checkpoint so the extension only backfills missing days.

The detail

Timezone-aware grouping per ad account’s IANA zone, buyer-id normalisation (“007” = “7” = 7), Zod caps on payload shape, 60 s result cache with a fresh=1 bypass, a kill switch and a diagnostics panel with fill-rate history.

Result

Spend and insights flow in from real sessions, no Graph token babysitting, ops can pause ingest in one click.

// illustrative, rewritten from scratch

fb-ext/sync-plan.ts

@Public()
@UseGuards(ExtApiKeyGuard)
@Post('sync-plan')
async syncPlan(@Body() body: SyncPlanDto) {
  if (await this.flags.isOff('fb-ext-ingest')) return { days: [], paused: true }

  const covered = await this.snapshots.coverage(body.adAccountId)
  const wanted = daysBetween(body.from, body.to, body.timezone)

  return {
    days: wanted.filter(d => !covered.has(d)),
    batch: { maxAdIds: 5000, maxGroups: 60 },
  }
}

NestJS
Chrome Extension
ClickHouse
Postgres
Zod

Problem

Authors get a 30-min grace window to edit a task before designers grab it. An audit found four bugs: a paused grace key with no TTL (crash = locked forever), non-atomic check-then-mark, acquire and pause as two separate writes, and unvalidated tokens.

Fix

Modelled grace as a tagged union (paused with remaining ms / active with unlock time). Paused keys get a TTL renewed by heartbeat, tokens are reserved atomically with SET NX EX, pause moved inside acquire, tokens validated as UUIDs.

The detail

Paused TTL = lock TTL (300 s) + buffer (60 s): if the browser dies, the lock dies with it within ~6 minutes.

Result

No more permanently stuck tasks; concurrent edits can’t corrupt lock state.

// illustrative, rewritten from scratch

creatives/edit-lock.ts

type Grace =
  | { kind: 'paused', remainingMs: number }
  | { kind: 'active', unlockAt: number }

const Token = z.string().uuid()

async reserveToken(token: string) {
  const ok = await this.redis.set(`edit-token:${Token.parse(token)}`, '1', 'EX', 600, 'NX')
  if (ok !== 'OK') throw new ConflictException('token already used')
}

async acquire(taskId: string, userId: string): Promise<Grace> {
  const res = await this.redis.eval(ACQUIRE_AND_PAUSE_LUA, 2, lockKey(taskId), graceKey(taskId),
    userId, LOCK_TTL_SEC, LOCK_TTL_SEC + GRACE_BUFFER_SEC, Date.now())
  return GraceSchema.parse(JSON.parse(res as string))
}

Redis
NestJS
Zod

Problem

A media-buying company has buyers, team leads, heads, finance, HR, designers — and constant exceptions. Hard-coded role checks were becoming a liability.

Fix

Single source of truth of 60 permissions shared by API and UI, role inheritance (buyer → team lead → head of media buying), a guard that checks role grants then per-user overrides, every grant/revoke audited with affected users. Sensitive fields encrypted with AES-256-GCM.

The detail

Ciphertext stored as iv | authTag | data with a scrypt-derived key; the app refuses to boot without key config — no silent plaintext fallback.

Result

New roles and exceptions are data, not deploys; every permission change is traceable.

// illustrative, rewritten from scratch

common/roles.guard.ts

canActivate(ctx: ExecutionContext) {
  const need = this.reflector.get<Permission[]>(PERMS, ctx.getHandler()) ?? []
  if (!need.length) return true

  const user = ctx.switchToHttp().getRequest().user as AuthUser
  const granted = new Set([...inherited(user.role), ...user.overrides.granted])
  user.overrides.revoked.forEach(p => granted.delete(p))

  return need.every(p => granted.has(p))
}

const inherited = (role: Role): Permission[] =>
  [...ROLE_PERMS[role], ...(ROLE_PARENT[role] ? inherited(ROLE_PARENT[role]) : [])]

NestJS Guards
Drizzle
Node crypto

Problem

Many landings share one origin. A naive service worker either never updates (stale offers in the wild) or wipes caches of neighbouring landings on activate.

Fix

Each publish generates its own sw.js with a cache name of slug + version, so the byte change forces an update; on activate it deletes only caches with its own slug prefix. Pixels for Meta, TikTok, GA4, Google Ads and Bing injected with sanitised IDs; CTA clicks fire Lead events.

The detail

Built-in publish-time audit (no Lighthouse): HTML/JS weight, layer and pixel count → 0–100 score with A–F grade, exported as a ZIP.

Result

Buyers ship installable, trackable prelanders in minutes; old versions never linger.

// illustrative, rewritten from scratch

landings/sw-template.ts

export function swTemplate(slug: string, version: number, assets: string[]) {
  const prefix = `lp-shell-${Slug.parse(slug)}-`
  return `
const PREFIX = ${JSON.stringify(prefix)}
const CACHE = PREFIX + 'v${version}'
self.addEventListener('install', e => {
  e.waitUntil(caches.open(CACHE).then(c => c.addAll(${JSON.stringify(assets)})))
  self.skipWaiting()
})
self.addEventListener('activate', e => {
  e.waitUntil(caches.keys()
    .then(keys => Promise.all(keys.filter(k => k.startsWith(PREFIX) && k !== CACHE).map(k => caches.delete(k))))
    .then(() => self.clients.claim()))
})`
}

Service Worker
Nuxt
NestJS

Problem

Creative generation jobs run for minutes. Mobile networks drop, tabs sleep — users came back to frozen progress bars or duplicated jobs.

Fix

SSE stream that sends a full snapshot of active jobs on every (re)connect, then incremental events; 20 s keepalive pings, max 4 streams per session; client reconnects with capped exponential backoff.

The detail

Backoff = min(15 s, 800 ms · 2ⁿ). ZIP export runs in a Web Worker with fflate — media stored uncompressed, text at level 6, result passed back as a transferable buffer.

Result

Progress is always correct after a reconnect, the UI never blocks on export.

// illustrative, rewritten from scratch

server/api/stream.get.ts

export default defineEventHandler(async (event) => {
  const session = await requireSession(event)
  if (streams.count(session.id) >= 4) throw createError({ statusCode: 429 })

  const stream = createEventStream(event)
  await stream.push({ event: 'snapshot', data: JSON.stringify(jobs.active(session.id, 200)) })

  const off = jobs.subscribe(session.id, e => stream.push({ event: e.type, data: JSON.stringify(e) }))
  const ping = setInterval(() => stream.push({ event: 'ping', data: '' }), 20_000)

  stream.onClosed(() => { off(); clearInterval(ping); streams.release(session.id) })
  return stream.send()
})

Nuxt 4
Nitro
SSE
Web Workers
fflate

Problem

Application-level checks are one bug away from an unbalanced ledger. For a platform holding user funds, “we validate in code” is not enough.

Fix

Append-only double-entry journal: amounts as bigint with 18 decimals, every posting idempotent by key (deposit:{id}, withdrawal_refund:{id}), balance projection upserted in the same transaction. Postgres constraint triggers reject unbalanced entries and any UPDATE/DELETE — corrections are reversing entries only.

The detail

DEFERRABLE constraint trigger checks balance at commit, so multi-line postings can be written row by row inside one transaction. TRUNCATE is blocked too — row triggers alone don’t catch it. Idempotent insert returns the existing entry id on replay instead of an empty result.

Result

An unbalanced or edited ledger is impossible by construction, not by convention.

// illustrative, rewritten from scratch

migrations/0001_ledger_guards.sql

CREATE CONSTRAINT TRIGGER journal_entries_balanced
AFTER INSERT ON journal_lines
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION assert_entry_balanced();

CREATE TRIGGER journal_lines_append_only
BEFORE UPDATE OR DELETE ON journal_lines
FOR EACH ROW EXECUTE FUNCTION raise_append_only();

CREATE TRIGGER journal_lines_no_truncate
BEFORE TRUNCATE ON journal_lines
FOR EACH STATEMENT EXECUTE FUNCTION raise_append_only();

INSERT INTO journal_entries (idempotency_key, kind)
VALUES ('deposit:8f2c', 'deposit')
ON CONFLICT (idempotency_key)
DO UPDATE SET idempotency_key = EXCLUDED.idempotency_key
RETURNING id, (xmax = 0) AS inserted;

PostgreSQL
NestJS
Drizzle

§ PBPlaybook

How I approach recurring hard problems in fintech, real-time and data-heavy systems.

PB-01 · fintech

Money endpoints that survive retries

Threat: Client times out, retries, and the user gets charged or paid out twice.

01Client-generated idempotency key per operation; same key on every retry.
02Atomically reserve the key (INSERT … ON CONFLICT / Redis SET NX) and mark it processing before any side effect.
03Persist the final response next to the key and replay it on duplicates — status code included.
04Double-entry ledger as the source of truth: balances are derived, never mutated in place.

idempotency.interceptor.ts

async intercept(ctx: ExecutionContext, next: CallHandler) {
  const req = ctx.switchToHttp().getRequest<FastifyRequest>()
  const idem = IdemKey.safeParse(req.headers['idempotency-key'])
  if (!idem.success) throw new BadRequestException('Idempotency-Key header required')

  const key = `idem:${req.user.id}:${idem.data}`
  const fingerprint = sha256(`${req.method} ${req.routeOptions.url} ${stableJson(req.body)}`)

  const reserved = await this.redis.set(key, JSON.stringify({ state: 'processing', fingerprint }), 'EX', 86_400, 'NX')
  if (reserved !== 'OK') {
    const prev = IdemRecord.parse(JSON.parse((await this.redis.get(key)) ?? '{}'))
    if (prev.fingerprint !== fingerprint) throw new UnprocessableEntityException('key reused with a different payload')
    if (prev.state === 'processing') throw new ConflictException('original request still in flight')
    ctx.switchToHttp().getResponse<FastifyReply>().status(prev.status)
    return of(prev.body)
  }

  return next.handle().pipe(
    concatMap(async (body) => {
      const status = ctx.switchToHttp().getResponse<FastifyReply>().statusCode
      await this.redis.set(key, JSON.stringify({ state: 'done', fingerprint, status, body }), 'EX', 86_400)
      return body
    }),
    catchError(err => from(this.redis.del(key)).pipe(concatMap(() => throwError(() => err)))),
  )
}

PB-02 · realtime

Real-time at 20k+ concurrent connections

Threat: Peak event traffic melts a single gateway and slow clients drag everyone down.

01Stateless gateway nodes terminating WebSockets, horizontal scale behind sticky load balancing.
02Redis pub/sub (or streams) as the fanout backbone between gateways.
03Coalesce and throttle updates per client; drop stale frames for slow consumers instead of buffering forever.
04Snapshot + sequence cursor on reconnect, so nobody misses or double-applies an update.

gateway/fanout.ts

type Client = { room: string, pending: Map<string, Update>, lastSeq: number }
const clients = new Map<WebSocket, Client>()
const SLOW_CONSUMER_BYTES = 1_000_000

sub.on('message', (room: string, raw: string) => {
  const update = Update.parse(JSON.parse(raw))
  for (const [ws, c] of clients) if (c.room === room) c.pending.set(update.key, update)
})

setInterval(() => {
  for (const [ws, c] of clients) {
    if (!c.pending.size || ws.bufferedAmount > SLOW_CONSUMER_BYTES) continue
    const updates = [...c.pending.values()]
    c.pending.clear()
    c.lastSeq = Math.max(c.lastSeq, ...updates.map(u => u.seq))
    ws.send(JSON.stringify({ t: 'diff', seq: c.lastSeq, updates }))
  }
}, 100).unref()

wss.on('connection', (ws) => {
  ws.on('message', async (raw) => {
    const msg = ClientMsg.parse(JSON.parse(raw.toString()))
    if (msg.t !== 'join') return
    clients.set(ws, { room: msg.room, pending: new Map(), lastSeq: msg.lastSeq ?? 0 })
    ws.send(JSON.stringify({ t: 'snapshot', ...(await snapshotSince(msg.room, msg.lastSeq ?? 0)) }))
  })
  ws.on('close', () => clients.delete(ws))
})

PB-03 · data

Analytics ingestion that can be replayed

Threat: Millions of raw events, retries and backfills produce duplicates and tiny-part merge storms.

01Buffer and flush on size or time threshold — big batches, few parts.
02Deterministic dedup keys (e.g. click id) + ReplacingMergeTree, so replays converge to the same state.
03Monthly partitions and materialized views for the hot aggregates; raw stays raw.
04Pivot queries built from a whitelist of metrics — flexible for users, safe for the cluster.

ingest/batcher.ts

export class ClickHouseBatcher<T> {
  private buf: T[] = []

  constructor(private table: string, private size = 5_000, private intervalMs = 5_000) {
    setInterval(() => this.flush(), intervalMs).unref()
  }

  push(row: T) {
    this.buf.push(row)
    if (this.buf.length >= this.size) void this.flush()
  }

  async flush() {
    if (!this.buf.length) return
    const rows = this.buf.splice(0, this.size)
    const token = sha256(stableJson(rows))
    try {
      await retry(() => ch.insert({
        table: this.table,
        values: rows,
        format: 'JSONEachRow',
        clickhouse_settings: { insert_deduplication_token: token },
      }), { attempts: 5, backoff: n => 250 * 2 ** n + Math.random() * 250 })
    } catch (err) {
      this.buf.unshift(...rows.slice(0, MAX_BUFFER - this.buf.length))
      throw err
    }
  }
}

PB-04 · backend

Postbacks & webhooks without silent loss

Threat: Process dies between DB commit and dispatch — the conversion exists, the postback never leaves.

01Transactional outbox: the event row is written in the same transaction as the business change.
02Relay worker publishes at-least-once; consumers are idempotent, so the effect is exactly-once.
03Exponential backoff with jitter, dead-letter queue, and a replay button for ops.
04Per-click state behind short-TTL distributed locks to avoid racing revenue updates.

conversions/outbox.ts

await db.transaction(async (tx) => {
  const [conv] = await tx.insert(conversions).values(input).returning()
  await tx.insert(outbox).values({
    topic: 'postback.send',
    dedupKey: `conv:${conv.id}`,
    payload: toPostback(conv),
  })
})

export const relay = () => db.transaction(async (tx) => {
  const batch = await tx.select().from(outbox)
    .where(isNull(outbox.sentAt))
    .orderBy(outbox.id)
    .limit(500)
    .for('update', { skipLocked: true })

  if (!batch.length) return 0

  await queue.addBulk(batch.map(e => ({
    name: e.topic,
    data: e.payload,
    opts: { jobId: e.dedupKey, attempts: 8, backoff: { type: 'exponential', delay: 2_000, jitter: 0.5 } },
  })))

  await tx.update(outbox).set({ sentAt: new Date() }).where(inArray(outbox.id, batch.map(e => e.id)))
  return batch.length
})

PB-05 · reverse-engineering

Integrating with an API that does not exist

Threat: The data lives behind a private, undocumented, frequently changing interface.

01Capture real traffic (DevTools / CDP, mitmproxy) and map auth, signatures and pagination.
02Deobfuscate client JS via AST transforms to find how tokens and params are built.
03Wrap it in a typed client with contract tests that fail loudly when the upstream shape drifts.
04Respect rate limits, back off on anomalies, and keep a fallback path in the product.

clients/private-api.ts

const Report = z.object({
  items: z.array(z.object({ id: z.string(), spend: z.coerce.number(), day: z.string().date() })),
  next: z.string().nullable(),
})

function sign(method: string, path: string, body: string, ts: number) {
  const payload = [method, path, ts, createHash('md5').update(body).digest('hex')].join('\n')
  return createHmac('sha256', BUNDLE_SALT).update(payload).digest('base64url')
}

export async function* reports(session: Session, from: string, to: string) {
  let cursor: string | null = null
  do {
    const body = JSON.stringify({ from, to, cursor })
    const ts = Math.floor(Date.now() / 1000)
    const res = await limiter.schedule(() => session.fetch('/internal/v2/reports', {
      method: 'POST',
      body,
      headers: { 'content-type': 'application/json', 'x-ts': String(ts), 'x-sig': sign('POST', '/internal/v2/reports', body, ts) },
    }))
    if (res.status === 429) { await sleep(Number(res.headers.get('retry-after') ?? 30) * 1000); continue }

    const page = Report.safeParse(await res.json())
    if (!page.success) throw new ContractDrift('reports', page.error)
    yield* page.data.items
    cursor = page.data.next
  } while (cursor)
}

PB-06 · performance

Cutting p95 latency without new hardware

Threat: Hot endpoints slow down as tables grow and caches stampede on expiry.

01Measure first: EXPLAIN ANALYZE on the real slow queries, not guesses.
02Partition big tables by time, add covering indexes for the hot paths.
03Cache-aside with request coalescing and jittered TTLs to kill stampedes.
04Ship the frontend as SSR/edge where it matters — FCP is latency too.

cache/coalesce.ts

const inflight = new Map<string, Promise<unknown>>()

export async function cached<T>(key: string, ttl: number, load: () => Promise<T>): Promise<T> {
  const hit = await redis.get(key)
  if (hit) return JSON.parse(hit)

  if (!inflight.has(key)) {
    inflight.set(key, load()
      .then(async (v) => {
        await redis.set(key, JSON.stringify(v), 'EX', Math.round(ttl * (0.9 + Math.random() * 0.2)))
        return v
      })
      .finally(() => inflight.delete(key)))
  }
  return inflight.get(key) as Promise<T>
}

PB-07 · seo

Multi-GEO SEO that Google actually trusts

Threat: Dozens of GEO and language versions compete with each other, get flagged as duplicates and rank the wrong locale.

01SSR / SSG for every indexable page — crawlers get full HTML, users get fast LCP.
02hreflang clusters with reciprocal links and x-default, mirrored in the sitemap so every locale points to every other.
03Self-referencing canonicals per locale; never canonicalise one GEO to another.
04JSON-LD structured data and Core Web Vitals budgets (LCP, INP, CLS) checked in CI, not after launch.

server/routes/sitemap.xml.ts

const LOCALES = ['en', 'de', 'pl', 'pt-br', 'es-mx'] as const

export default defineEventHandler(async (event) => {
  const pages = await listIndexablePages()
  const url = (locale: string, path: string) => new URL(`/${locale}${path}`, SITE).toString()

  const entries = pages.flatMap(page => LOCALES.map(locale => `
  <url>
    <loc>${url(locale, page.path)}</loc>
    <lastmod>${page.updatedAt.toISOString()}</lastmod>
${LOCALES.map(alt => `    <xhtml:link rel="alternate" hreflang="${alt}" href="${url(alt, page.path)}"/>`).join('\n')}
    <xhtml:link rel="alternate" hreflang="x-default" href="${url('en', page.path)}"/>
  </url>`))

  setHeader(event, 'content-type', 'application/xml; charset=utf-8')
  return `<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xhtml="http://www.w3.org/1999/xhtml">${entries.join('')}
</urlset>`
})

Got a problem like these?

Let’s talk