Playable Offline, No Lost Scores Online — an Idempotent Outbox and One Worker Boundary Pipeline

KeyDo’s score reporting used to be one line: submitScore(...).catch(() => {}) — send it, and if it fails, oh well. Scores played offline or dropped by a flaky network were all lost. On the other end, the public /api had no request-body limit, origin check, or rate limiting, a wide abuse surface. This remediation tightened both ends: playable offline, scores stashed locally and resent on reconnect with no loss and no duplication; a boundary pipeline in front of the Worker.

Offline: only the static shell, no caching the API

Offline uses a PWA (Service Worker), but with a deliberate boundary: cache only the static shell, never the business API.

A build-time Vite plugin scans the output, collects index.html, the manifest, icons, and all JS/CSS/fonts into a precache list, and computes a sha256 to make a content-hashed cache name keydo-precache-<digest> — the cache name changes only when the build changes, a natural version switch. The Service Worker has three parts:

self.addEventListener('install', (event) => {
  event.waitUntil((async () => {
    const cache = await caches.open(CACHE_NAME)
    try { await cache.addAll(PRECACHE_URLS) }          // atomic precache: one failure rolls the whole back
    catch (e) { await caches.delete(CACHE_NAME); throw e }
  })())
})
// fetch: handle only GET/same-origin, and explicitly skip /api (don't cache the business API offline)
if (url.pathname === '/api' || url.pathname.startsWith('/api/')) return
if (request.mode === 'navigate') {                      // navigation: on network failure fall back to cached index.html
  event.respondWith(fetch(request).catch(() => caches.match('/index.html')))
}

The key line is API requests are explicitly skipped. Offline only guarantees “reopen can enter the four modes,” and score sync is handed to the outbox below — never caching stale scores or rankings as offline data. Caching a stale leaderboard is worse than not caching.

Score sync: dual-side idempotent, no loss no dup by nature

Reliable sync rests on both the client and the server being idempotent:

A mergeable outbox on the client. Each identity stores one outbox entry per mode + week, folding multiple entries of the same key into one best value by “value descending.” This is a per-key “grow-only best value” structure — repeat enqueues and retries all converge to “keep the better one per key,” so replay is safe.

The server accepts only the better. The leaderboard table’s primary key is (user_id, mode), one row per user per mode, written by a conditional UPSERT:

INSERT INTO scores (...) VALUES (...)
ON CONFLICT (user_id, mode) DO UPDATE SET ...
WHERE excluded.value > scores.value      -- idempotent: a duplicate or lower submission is a no-op

Stack the two, and a score submitted any number of times yields the same result. And “the server wrote it, but the response was lost in the network” is fine too — the client resends after a timeout, and the server’s WHERE value > ... makes the resend a no-op. This is idempotency’s value over “send once and delete”: unsure whether it went through, so resend without worry.

Cross-week has another anti-replay: the client sends its own week number computed in UTC+8, and the server updates the weekly board only when it equals the server’s current week, else only updates cumulative and returns weeklyAccepted: false. A last-week score accumulated over days offline won’t pollute this week’s board after reconnect.

Retry: classify errors + single-flight + exponential backoff

Not every failure should be retried. classifySyncError splits errors in two:

if (code === 'NETWORK_ERROR' || code === 'INVALID_RESPONSE'
    || status === 409 || status === 429 || (status >= 500 && status <= 599))
  return { kind: 'retryable' }          // only these auto-retry
if (status >= 400 && status <= 499)
  return { kind: 'blocked' }            // other 4xx marked blocked, never periodically replayed

409 (version conflict), 429 (rate-limited), 5xx, network errors, bad response format — these are “retry is meaningful”; while other 4xx (e.g. the request itself is illegal) won’t work retried ten thousand times, so it’s marked blocked and no longer periodically flushed. The coordinator is single-flight: only one sync round per identity at a time, and triggers within 250ms merge into one; auto-retry uses exponential backoff capped at 5 minutes, while a user’s manual “retry” isn’t bound by that wait. It also listens for the online event to auto-resend on reconnect. The persisted pending stores only stable error codes and retry counts, no bearer or save body.

The Worker boundary: the order is the layers of protection

That server request pipeline — each step’s order is itself the layers of protection:

Same-origin check: if an Origin header is present it must be same-origin, else 403; the response side actively deletes all access-control-allow-* headers, no CORS pass;
Auth: the bearer must match ^Bearer ([a-fA-F0-9-]{36})$, else 401;
Rate limit: first by IP then by identity, over the limit uniformly 429 + Retry-After: 60. In order, consume the IP quota before reading the body — so an invalid large body eats the rate-limit quota before parsing, keeping the parse cost out; a new save also goes through a stricter limit (2 per 60s);
Bounded JSON: force Content-Type: application/json (else 415), check Content-Length first (413), then stream-accumulate bytes and reader.cancel() immediately on exceeding the limit, not reading the whole stream into memory; decode with TextDecoder('utf-8', {fatal: true}) to reject illegal bytes;
No error leak: any unexpected error is uniformly 500 service temporarily unavailable, with internal detail carrying a requestId to logs, and the response body never containing an exception message or SQL.

An honest note: this rate limiting is best-effort (edge nodes isolated, eventually consistent), not a precise global quota; what truly limits write amplification is the CAS + one-row-per-identity-per-mode + 100 KiB cap layers. Rate limiting keeps obvious abuse out the door, it isn’t precise accounting.

Aside: tests must be isolated

A bit of test hygiene along the way: the HTTP tests used to share a preset identity and a fixed score of 88, coupling test cases through that shared state. Changed so each case uses its own independent identity and creates its own score first, sharing no database state between cases. After the change, one assertion went from 88 to 91 (the score it wrote itself). The point is to eliminate cross-case ordering coupling — especially for “idempotent” and “best value” assertions that depend on persistent state, single or parallel runs must be stable.

Takeaways

Offline serves only the static shell; the business API always goes to the network. Caching a stale leaderboard is worse than not caching; the SW explicitly skips /api, and score sync goes to an idempotent outbox, not cached API responses;
Dual-side idempotent, and no-loss-no-dup is free. A client-side per-key grow-only best value + a server-side WHERE value > ... conditional write — repeat submissions, replays, even “resend after a lost response” all converge to the same result. Idempotency lets you resend without worry;
Not every failure should be retried. Split errors into retryable (network / 429 / 5xx / 409) and blocked (other 4xx); don’t periodically flush an “illegal request” error. Single-flight + capped exponential backoff + a manual-retry exemption is a “sufficient, not annoying” default;
The request boundary’s order is the layers of protection. Same-origin → auth → rate limit → bounded parse → no error leak; charge the rate-limit quota before reading the body, cancel the stream immediately on exceeding, so attack cost is blocked at the outermost layer;
Rate limiting is best-effort, not a precise quota. Honestly accept edge rate limiting’s eventual consistency, and hand the real burden of “preventing write amplification” to CAS and the size cap, rather than pretending rate limiting is a global ledger.