/ project
rok-data-pipelineflagship
2.4M game profiles a day, taken out of a live client's memory and served to the web in tens of milliseconds — a reverse-engineered fetch layer, a multi-machine harvesting fleet, and a ClickHouse serving model built so the whole thing costs almost nothing to run.
ProblemThe numbers that actually decide a Rise of Kingdoms KvK — a governor's lifetime total kill points, the T1–T5 kill breakdown, lifetime deaths and healing — exist in no public API. Lilith's official endpoint returns only timeframe stats, and it 403s for any kingdom you don't have a character in. Those totals live in exactly one place: the memory of a running game client. Getting one governor out is a reverse-engineering problem. Getting every governor in every kingdom, every day, is a systems problem.
What it doesTwo halves. The fetcher runs a Lua chunk inside the live PC client by briefly hijacking an engine thread, calls the game's own by-ID profile fetch, and hooks the reply handler so the profile card never opens — which is what makes hundreds of back-to-back fetches crash-free instead of fatal. A coordinator then hands kingdoms to a fleet of sandboxed clients across several machines over Tailscale, each self-sizing its scan. The serving half ingests the results into ClickHouse and precomputes every aggregate in refreshable materialized views, so a page view is an indexed lookup rather than a query over history.
ScaleA full sweep is 4,000 kingdoms at 600 governors each — 2.4M profiles a day, appended to a history that is never truncated, which compounds to roughly 876M rows a year at 49 columns apiece. That number is what shapes both halves. A per-IP rate limit paces the fetch at ~1.2s per governor, so one sweep is ~800 hours of single-client time and only exists as a fleet — which makes not fetching the real optimisation. And on the serving side it is precisely the volume where a row-store starts costing real money, so the whole data model is built for a columnar engine: month partitions, dictionary-encoded columns, wide rows kept narrow, and every aggregate precomputed on the ingest cadence. The result serves in tens of milliseconds off one 2GB machine that suspends when idle.
What I learnedMost of the project was being wrong in public. PROGRESS.md runs 27 sections across a dozen sessions, including a section that declares arbitrary-kingdom fetch solved and the next one that retracts it with proof. The wins came from reading, not guessing: passive disassembly found the crash root cause that a debugger couldn't (the anti-tamper layer fights debuggers but ignores ReadProcessMemory), and the fix that finally shipped was noticing why the client crashed on a second fetch rather than out-engineering it.
Architecture
Two repos, one pipeline. rok-data-fetcher gets lifetime governor statistics out of a live Rise of Kingdoms client and into CSVs; kvk-web turns those CSVs into a stats site that answers in tens of milliseconds on a hosting bill small enough to ignore. The seam between them is a ClickHouse table, and it carries millions of rows a day - see Scale, below, which is the constraint most of the design is answering.
The fetch side exists because the data has no other source. Community stat sites and Lilith's official Game Tools API expose only timeframe stats - kills inside a date window - and the API returns KINGDOM_FORBIDDEN for any kingdom the calling account has no character in. Lifetime total kill points, the T1-T5 kill breakdown, lifetime deaths and healing are held only by the running client, in the same table that backs the in-game Governor Profile card.
── FETCH · rok-data-fetcher (Windows) ───────────────────────────────
[coordinator.py] two-tier work queue (SQLite, stdlib only)
│ claim / complete over Tailscale
▼
[runner.py] ──► [fleet.py] ──► N sandboxed game clients, in parallel
│
[inject_loadbuffer.py]
hijack an engine thread, then call
luaL_loadbuffer + lua_pcall directly
│
▼
PlayerInfoHandler:FetchPlayerInfo(nil, <id>, 2)
with OnFetchPlayerInfoAck hooked to parse the reply
and return — the profile card never opens
│
▼ CSV per kingdom, checkpointed
── SERVE · kvk-web ──────────────────────────────────────────────────
[db/ingest.py] ──► [ClickHouse Cloud · us-east-1]
REFRESH EVERY 12 HOUR materialized views:
current_scan · matchup_delta · kingdom_movement
│
└──► POST /api/revalidate the moment a batch lands
│
[Next.js on Fly · iad · ISR 43200s] ──► [Cloudflare edge] ──► reader
most reads never reach Fly; ClickHouse idles between batchesThe injection engine runs a Lua chunk inside the game by briefly hijacking one of its own threads and calling the standard Lua C API directly, saving and restoring the thread context so the client keeps running afterwards. The client is Unity/il2cpp, but the game logic is Lua behind an XLua bridge, and EngineDll.dll exports the full Lua 5.1 C API by name - so every function needed resolves as base + RVA, stable across restarts.
On the serving side the load-bearing idea is that a request should never compute anything. Scans land in ClickHouse, materialized views precompute the aggregates on a 12-hour refresh, pages render with ISR on a matching 12-hour window, and Cloudflare caches the HTML at the edge. A reader coming in from a Discord link gets an answer that was computed hours ago.
Scale
4,000
Kingdoms in the sweep range
600
Governors deep per kingdom
2.4M
Profiles per full daily sweep
~876M
Rows a year, at that cadence
49
Stat columns per row
~800 hrs
One client's time per sweep
The target sweep is 4,000 kingdoms at 600 governors each - 2.4M profiles per pass. Run daily and appended to a history that is never truncated, that is roughly 876M rows a year, each carrying 49 columns of lifetime stats. Both halves of the system are shaped by that number more than by anything else.
On the fetch side the binding constraint is a per-IP rate limit on the profile call, which paces out to about 1.2 seconds per governor. A full sweep is therefore ~800 hours of single-client time - about 33 days - so it can only exist as a fleet, and the highest-leverage optimisation is not fetching faster but fetching less. That is what adaptive scanning is for: stop at the City-Hall-25 boundary where the real accounts end, and leave the tail to a backfill that runs on idle capacity. Every profile skipped is the win.
On the serving side, a row count like that is exactly where a conventional row-store starts costing real money - you are storing an append-only log of wide numeric rows and asking analytical questions of it, which is the workload OLTP engines are worst at. ClickHouse is a much better fit for it, but the cost win is not automatic; it comes from modelling for the engine. Wide numeric rows compress hard columnwise, alliance_tag is LowCardinality so it dictionary-encodes in the hot path, scans is partitioned by month so queries prune whole partitions instead of reading history, and the ~250-byte avatar URL that would otherwise ride on all 2.4M rows a day lives in its own one-row-per-governor table.
The last piece is when the compute happens. Every serving aggregate is a refreshable materialized view rebuilt on the 12-hour ingest cadence, so a page view costs an indexed lookup rather than a scan, and the engine does real work twice a day instead of once per request. Because the pages and the edge cache are pinned to the same cadence and refreshed by an event rather than a timer, the database has nothing to answer between batches and idles. The whole site runs on one 2GB Fly machine that suspends when nobody is reading.
Technical decisions
Re-key the log rather than scan it
scans is ordered by (kingdom, captured_at, governor_id), which is right for the kingdom pages and useless for a governor page - asking for one governor's history against that key scans everything. Rather than add a second index to a table growing by millions of rows a day, governor_history is an incremental materialized view of the same data re-keyed by (governor_id, captured_at, kingdom). It fires on insert, so it costs nothing at rest and grows with the log, and a per-governor trend becomes an indexed range read. Same rows, second ordering, no scan.
Refreshable views for aggregates, incremental for projections
the two kinds of materialized view solve different problems and the split is deliberate. Anything that aggregates across the whole corpus - current_scan, matchup_delta, kingdom_movement - is a refreshable view rebuilt on the ingest cadence, because recomputing it twice a day is far cheaper than maintaining it per insert. Anything that is a pure projection of the log - governor_history - is incremental, because it fires per row and there is nothing to recompute. Getting that backwards is how you pay for a rebuild that never needed to happen.
Idempotent ingest, because retries are normal
the fleet re-queues any kingdom a client fails to finish, so the same scan can be uploaded more than once - and at this volume a double-counted batch is not something you would notice by eye. scans is a ReplacingMergeTree keyed on (kingdom, captured_at, governor_id) and versioned by uploaded_at, so a re-upload collapses into the row it replaces instead of duplicating it. Retry safety is a property of the schema rather than a discipline the pipeline has to maintain.
Publish a scan wave atomically
kingdoms are scanned one at a time, so covering one matchup takes 20-30 minutes. Serving each kingdom as it lands would mean the first-scanned kingdoms' gains jump while the rest sit stale - and a screenshot of that half-updated scoreboard gets used to argue the wrong winner. So each matchup serves data only up to the oldest of its members' newest captures: mid-wave everyone sees the previous complete wave, and the whole matchup advances at once when the last kingdom lands. A member idle more than 48 hours stops pinning the watermark, so one dead kingdom cannot freeze a matchup forever.
Hook the reply, never open the window
FetchPlayerInfo(nil, id, 2) returns a full profile but also opens the Governor Profile card, and firing a second fetch while a card is open crashes the client - which for a long time looked like a hard ceiling of one fetch per session. The fix was to stop treating it as an injection problem: OnFetchPlayerInfoAck is table-dispatched, so wrapping it to parse the profile and return without calling the original means no window ever opens. Hundreds of back-to-back fetches, zero crashes. This one insight is what turned a proof of concept into a pipeline.
Call luaL_loadbuffer directly, not the game's own loader
the obvious entry point, lua_doMemBuffer, crashed the client on every attempt. Disassembly showed why: it doesn't wrap luaL_loadbuffer - it calls a custom loader that allocates a C++ reader object and makes vtable-based virtual calls to pull each buffer chunk, and those depend on engine context that doesn't exist during an out-of-band thread hijack. luaL_loadbuffer is a seven-instruction wrapper around a reader with no virtual calls and no global state, so it cannot fail the same way. Bypassing the game's own convenience function fixed it in one change.
Read passively; inject as little as possible
the client ships NetEase Protect, which does anti-debug, thread and module enumeration, and integrity scanning - and ignores external ReadProcessMemory entirely. So every diagnostic that could be passive was: the root-cause disassembly, the memory scans, the wire captures. Injection is reserved for the single call that has to happen inside the process. That split is also why the root cause was findable at all - attaching a debugger, the conventional move, was the one thing the environment actively defended against.
Adaptive two-tier scanning over a rigid top-N
the binding constraint is a per-IP rate limit on the profile fetch, so at 4,000 kingdoms every profile not fetched is the win. A fixed top-150 is wrong on both ends - it burns throttled fetches on dead kingdoms' alt accounts and truncates real players in stacked ones. Tier 1 scans top-down by power and stops at a run of sub-City-Hall-25 governors, so each kingdom sizes its own scan; tier 2 backfills the cold tail on spare capacity, skipping anyone a recent daily already covered.
A shared pull queue instead of a schedule
the coordinator is a work queue, not a dispatcher: every box pulls the next kingdom the moment it is free. Capacity balancing then falls out for free - a machine with six clients drains roughly twice the work of one with three, with nothing configured - and a kingdom a box can't finish is simply re-queued. The daily census works the same way: the first runner up claims a lease and re-seeds, so there is no cron and no scheduler anywhere in the system. The runner is the clock.
Clients are consumable; make every stage resumable
repeated thread hijacking degrades a session - after a few hundred fetches replies just stop arriving. Rather than fight that, the fleet treats a client as expendable: detect the stall, terminate the Sandboxie box, relaunch, resume. That also resets the game's per-session rate limit, so recycling is a feature twice over. The CSV is checkpointed atomically after every round and a resumed run keeps each row's own fetch timestamp, so an interrupted overnight sweep needs nothing but the same command again.
Precompute and serve, never compute per request
on kvk-web this is enforced rather than encouraged. Every page must build as static or ISR - npm run build runs a checker that fails the build if any route outside app/api/ went dynamic - and aggregation happens in ClickHouse, never by pulling rows into Node. Reading searchParams in a page Server Component silently opts the whole route out of static rendering, which is a bug next dev will happily hide until it 500s in production.
12-hour cache windows, plus an event-driven poke
the data only changes when an ingest batch lands, so every cache window in the system is set to that cadence - ISR at 43,200s, s-maxage on every API route the same - and the pipeline POSTs a guarded /api/revalidate the instant a batch completes, which busts ISR and purges Cloudflare. Freshness comes from the event, not from polling. That's the whole cost win: a short window would let any traffic (an uptime monitor is enough) re-hit ClickHouse every minute and keep a metered Cloud service permanently awake. Instead it idles between batches, and the Fly machine suspends.
What broke / what I learned
PROGRESS.md is 27 sections long and the honest version of this project. Section 18 announces that arbitrary-kingdom fetch is solved end to end; section 19 opens with CORRECTION and proves the mechanism in section 18 was wrong. That correction cost a session of crash-and-relaunch cycles to establish, and what it found was a genuine wall: the injection path can send a request for any kingdom and the server answers, but the client cannot materialize a foreign kingdom's entry objects in-process - it has no supporting data for a kingdom it isn't in - so touching the reply faults natively, uncatchable by pcall. Holding the raw reference is safe; reading one field is fatal.
So I went the long way around: capture the encrypted reply off the wire and decrypt it with the keystream read out of the client's memory. That worked - the cipher came apart, a leaderboard reply was decrypted from the memory keystream end to end - and it was still the wrong answer. The shippable solution turned out to be much smaller and came from a question about game behaviour rather than about the binary: why does the second fetch crash? Because the first one left a profile card open. Hook the ack, never open the card, and the whole elaborate wire-decode subproject becomes unnecessary. The lesson I actually keep from this is that I spent days deepening an approach before I had finished understanding the failure it was meant to route around.
The web half taught the inverse lesson - that the cheap-looking thing has a cost curve, and that scale finds it for you. The first version loaded all of current_scan into an in-memory snapshot on the Node process, which is genuinely the fastest possible read right up until the table passes ~600k rows and OOMs production. It didn't fail gradually; it worked perfectly at the volume I developed against and fell over at the volume it shipped into. Replacing it with bounded, keyset-paginated, indexed queries per request was slower per query and correct, because the edge cache and ISR were already absorbing the request rate the snapshot was optimising for. Both halves of this project failed the same way: I optimised a layer before checking whether it was the layer under pressure.
I stopped working on it because scans take a long time and free tools already cover most of what this does. That was the right call and it doesn't subtract from what it was - the only project I've built that spans reverse-engineering a hostile binary, an unattended multi-machine fleet, a columnar data model, and a front end with a hard latency budget, where every layer had to be right for the last one to work.
Archived, and honest about its edges. The injection uses hard-coded EngineDll.dll offsets for one game build and needs re-deriving after an update; a governor who has never been scanned isn't in the roster yet, so brand-new accounts are the one coverage gap; and arbitrary-kingdom fetch at arbitrary times - the wire-capture route - was proven but never made robust, because the profile-fetch path made it unnecessary. This was reverse engineering of my own game client, on my own machine, for data the game already shows me.