Mirai Guide: serving an event crowd on one SQLite database

Mirai Guide’s audience shape is brutal for a database. Roughly 10,000 concurrent attendees inside one convention hall, all behind a handful of venue-Wi-Fi and carrier-NAT egress addresses. Doors-open bursts, where everyone opens the app within the same few minutes. Millions of page views across a three-city tour, plus remote fans watching the globe and stock boards from home.

The entire backend is Cloudflare Workers, and the only relational database is D1 — underneath, a single-primary SQLite instance in one region with a serial write queue. Hammer it with concurrent statements and the queue overflows into 500s. We know because it happened, during content-pipeline runs, before any crowd showed up.

So the architecture question was never “how do we make D1 faster.” It was:

How do we make sure almost nothing ever talks to D1?

D1 is the ledger, not the serving plane

D1 stays the source of truth for everything — catalog, live reports, reporter trust, settings, audit trail. But attendees never read the ledger. They read derived artifacts: small JSON objects recomputed from D1 whenever the truth changes, written to R2, and served through layered caches.

Every hot read path has the same shape:

D1 (truth) --write-triggered rebuild--> R2 artifact --colo cache--> attendee

The artifacts are the catalog bundle, a pointer saying which bundle is live, per-venue-day live snapshots of stock and queue state with their own pointer, the runtime config that server-side rendering needs, the community globe, and the sellout risk curves. Each one is rebuilt by the write that invalidates it — an operator publish, an arriving report, an admin edit — not on a timer and never on read.

A publish is content-addressed: the version is a hash of the body, with a client-verified SHA-256 integrity check. That makes bundle bytes cacheable as immutable for a year, so only the tiny pointers ever need freshness.

Layer 1 — the client never asks twice

The service worker keeps four caches: pages, assets, data, and a versioned precache. A returning attendee’s repeat views are served entirely on-device. Visited pages work offline in a concrete hall with terrible reception, which is simultaneously a user-facing feature and the outermost cache layer. Media is pre-warmed; the catalog lives in IndexedDB and is revalidated against the pointer rather than re-downloaded.

The net effect is that “millions of views” mostly never become HTTP requests at all.

Layer 2 — the colo answers before the origin thinks

Every pointer-shaped endpoint uses the Workers Cache API as a per-colo micro-cache with a 10–15 second TTL. A doors-open boot storm — thousands of devices fetching the same pointer at once — collapses to roughly one origin read per colo per window.

One hard-won subtlety. We deliberately do not use edge cache-control caching for these. The zone rewrites cached browser TTLs to hours, and a stale pointer pinned in an attendee’s own browser cache would stall live data with no way to purge it. So the stored colo copies are public, max-age=15, but every response leaves as no-cache. The attendee revalidates every time, and the revalidation is answered by the colo copy instead of the database. Freshness semantics stay honest; the origin load disappears anyway.

Layer 3 — per-isolate caches for the tiny hot reads

What remains on the render path — feature flags, lockdown state, edition settings — is cached in worker-isolate memory with roughly 30-second TTLs and a deliberate failure posture: fail toward the last known state. A kill switch must never flip a feature back on because D1 blinked. A language list must never collapse because one query failed. Stale-if-error everywhere; empty only on a truly cold start.

The last big win here was the runtime-config artifact. The event index and language registry used to cost a five-statement D1 batch per fresh isolate — and doors-open is precisely when Cloudflare spins up hundreds of fresh isolates. That is a synchronized read spike with exactly the queue-overflow shape. Now a cold isolate’s first page view costs one R2 read. The artifact self-heals if missing, refreshes stale-while-revalidate in the background, and admin writes republish it explicitly for fast propagation.

The result: a server-rendered page view touches D1 zero times in steady state.

Lockdown: an operational freeze that doubles as a caching primitive

Before each event day an operator flips lockdown — a single gate in the request hook that 423s every mutating admin route except a short, deliberate allowlist: live pins, stock resets, kill switches, day-of safety notices, curve republishing. The things you need at 09:00 with no deploy available.

The insight is that lockdown is also a provable immutability signal. If nothing can publish, the catalog pointer is safely cacheable in browsers for a minute, the event index and language registry can stretch their isolate TTLs from 30 seconds to 5 minutes, and the legacy database-backed polling endpoint goes completely dark. The freeze that protects operators from mid-event mistakes is the same switch that flattens read load at peak.

Equally important is what lockdown-aware caching must not touch. The day-of notices feed is lockdown-exempt on the write side precisely so a 7am safety advisory can go out mid-freeze — so its cache stays a short colo TTL, never a lockdown-stretched one. Caching policy follows write-path reality, not vibes.

The write path: the crowd funnels into three round trips

Reads can be cached away. Writes cannot. Every attendee stock or queue report lands on the single serial SQLite, and the budget per accepted report is three D1 round trips:

1. One batch of pre-checks — kill switch, subject validation (“is this an approved offer at this venue on this day”), idempotency lookup, and the durable rate counter. Four statements, one round trip.
2. One transactional batch — reporter upsert, report insert, and a read-back of the 45-minute consensus window. The reads ride the same transaction, so they see the row just inserted.
3. One final batch — a strictly-increasing cursor draw, the aggregate upsert, and the reporter-trust update, all in one transaction.

Consensus itself is computed in JavaScript between round trips 2 and 3. The venue-day snapshot republish is deferred and debounced, so a burst of reports on one booth becomes one artifact rebuild rather than one per report.

Then the shields, cheapest to bluntest:

• Per-isolate in-memory rate limits, applied before the body is even parsed — a junk flood otherwise costs an ed25519 verify per request.
• Durable per-IP-band counters, sized for a hall rather than a person.
• Zone WAF rate rules as the global backstop. In-worker limits are per-isolate and multiply with fleet size; only the zone can cap a flood before the Worker is invoked at all.
• A circuit breaker. When D1 starts failing under load the endpoint sheds with an immediate 503 and retry-after instead of paying a worker invocation to produce another 500. The client report queue treats that as “keep the report, back off” — nothing is lost, only delayed.

Everything is signed with per-device ed25519 keys, so abuse control does not depend on the network layer alone: per-reporter trust weights the consensus, repeat contradictors are shadow-banned to zero weight, and freshly-minted throwaway keys start weak.

What never touches D1 at all

The shared group cart lives in Durable Objects with per-cart SQLite, so its hot operations are zero-D1 by construction; a tiny D1 registry exists only so carts are enumerable for backups. Telemetry and ops metrics go to Analytics Engine with a nightly raw export to R2, and the event-day dashboard reads that plane — monitoring adds no load to the thing being monitored. Comfort heatmaps, the community globe, schedules, and guides are all published artifacts, same pattern as everything else.

Operations without deploys

Everything an operator needs mid-event rides data, not code. Kill switches live on the live pointer and are enforced server-side, safe against stale clients. Official pins override crowd consensus authoritatively; stock resets return a venue to the presumed-in-stock baseline; both republish immediately. Prediction tuning knobs ship on the curves artifact — a republish, not a deploy. Destructive tools are self-undoing where possible: clearing a stale venue-day deletes only derived artifacts, and the next report rebuilds it from scratch. Hourly cron reaping keeps the live tables bounded, with each reaper isolated so one failure cannot take down the rest.

The scoreboard

• Steady-state D1 traffic before the final optimization round: ~45k reads per day (~0.5/s) for the whole site — and most of that has since moved to R2.
• A server-rendered page view: 0 D1 queries, down from 7+ statements.
• An attendee reading live stock: 0 D1 queries — R2 pointer plus snapshot, colo-cached.
• An accepted crowd report: 3 D1 round trips, behind four layers of shedding.
• The database itself: 26 MB, 101 tables, single region, physically located next to the hall it serves.

The honest summary is that D1 does not serve 10,000 users. D1 serves as the ledger for a system where the service worker, the colo caches, R2 artifacts, and Durable Objects each absorb an order of magnitude before anything reaches SQLite — and where the one path that must write is batched, debounced, trust-weighted, and shed long before the serial write queue notices a convention hall exists.

What generalizes

1. Single-writer databases scale as ledgers, not serving planes. Derive read artifacts on write; let object storage and caches do the fan-out.
2. Make immutability provable, then exploit it. Content-address what never changes, and use operational freezes to lengthen TTLs with a clear conscience.
3. Cache with honest semantics. Colo-cache the bytes, but keep client-side no-cache when staleness would lie to users. Never let a CDN rewrite your freshness contract.
4. Fail toward the last known state. A cache miss should degrade to stale data, not to a different answer.
5. Per-IP thinking breaks at venues. Carrier NAT puts a stadium behind one address. Size limits for crowds, and police individuals with identity — signed reports, trust, shadow bans — instead.
6. Batch the writes you cannot avoid, compute between round trips, and put a circuit breaker in front of the whole thing.
7. Give operators data-plane levers so event day never needs a deploy.
8. Make cleanup self-undoing. Tools that operators reach for under pressure should be impossible to regret.