Exact Analytics in 12KB: Hardening a Wallet-Uniqueness System

How many distinct wallets touched the network in the last hour. It sounds like a question with an easy answer until you try to answer it at scale, continuously, across five blockchain environments, without storing every wallet address that was ever active in every window you care about. SupraScan answers it with HyperLogLog sketches: a fixed, tiny amount of memory per time bucket gives an exact distinct-wallet count for any window you ask about, five minutes, an hour, a day, without the storage cost of ever keeping the underlying address list around. That mechanism working reliably in production, continuously, is the achievement. The part worth writing about in detail is what it actually took to get there: in one specific week, the system's reported numbers were wrong five separate times, for five genuinely different reasons, and finding each one is as much a part of "hardening a production system" as the sketch design itself.

Why sum isn't the same as union

The core mechanism is a sketch union: instead of storing every distinct wallet address seen in a time bucket, the system stores a small, fixed-size sketch that can answer "roughly how many distinct items have I seen" using a fraction of the memory a full set would need. The system counts unique senders and receivers this way, and it keeps a sketch for every 5-minute bucket, then needs longer windows, an hour, a day, built from those. The reason those longer windows can't just add up their constituent buckets' counts is structural, not a rounding preference: a wallet that shows up in more than one 5-minute bucket within the hour is a single wallet, and adding the per-bucket counts together counts it once for every bucket it happened to appear in, while simply taking the largest of the per-bucket counts undercounts everything down to whichever single bucket happened to be biggest. A union of the sketches themselves is the only operation that gives the actual right answer for the combined window, which is exactly why the system keeps merging 5-minute sketches upward into longer-lived hourly and daily ones rather than trying to reconstruct a rollup from already-computed counts.

That distinction is not academic. If bucket A has 55 unique senders and bucket B has 2 unique receivers, and some of those addresses appear in both, summing the two counts overcounts by exactly the overlap. Unioning the sketches gives the true combined count, 59 in that specific case, because a union correctly recognizes overlap the way a sum structurally cannot. The first fix in this saga replaced a cardinality sum with a true sketch union, paired with a corresponding change on the backend that consumes the corrected sketches. It's the fix that makes the system exact instead of approximately-too-high, and it's also the fix that makes everything downstream sensitive to a category of bug that a naive counter would never have exposed in the first place.

Five bugs, five different ways to catch them

Once the union fix was in place, four more real correctness bugs surfaced and were fixed over the following days, each caught by a completely different signal.

The second was a double-counting bug on reseal: an operation meant to update a cumulative counter was using simple addition instead of a "take the greater of the two values" comparison, so re-sealing a bucket that had already been sealed once doubled its stored total. This one was caught by an internal invariant checking itself: a 5-minute bucket showed more unique senders than the full hour that was supposed to contain it, which is structurally impossible if the counting is correct, and that impossibility is what surfaced the bug.

The third bundled two separate issues in one commit. The sender-count metric was reading from the wrong source column, quietly undercounting. Separately, a get-then-increment pattern across concurrent block-processing pods created a race window that could inflate peak metrics like maximum TPS or maximum gas price, because two pods could both read the same "current maximum" value before either had written back their update. The fix for the race was to replace the separate read and write with a single atomic compare-and-set, implemented as a Lua script, closing the window entirely rather than trying to make the race smaller. The shape of that fix, illustrative of the pattern rather than a verbatim excerpt, looks like this:

The point of running this as a single script isn't the comparison itself, it's that Redis executes a Lua script atomically. Two pods can call this at the same instant and still only one write happens for whichever value is actually higher, because there's no gap between the read and the write for a second caller to land in. A plain GET followed by a separate SET from application code always has that gap, no matter how small it looks in a benchmark.

The fourth was the least intuitive of the five. A bulk-insert call configured to ignore duplicate rows was, by the specific behavior of the ORM version in use, returning every input row regardless of which ones were actually new inserts versus duplicates silently skipped by the underlying conflict-do-nothing clause. That meant a "new wallets" counter had been reporting batch size, not actual new-wallet count, for an unknown period of time, and it stayed undetected until someone traced the number against ground truth: the same insert statement's own returning clause, which only reports rows that were genuinely inserted. Once that comparison was made, the gap was obvious. Before it, the metric looked plausible on every dashboard that displayed it.

The fifth, and most recent, was a data-quality bug rather than a counting-logic bug: event parsers were hardcoding a transaction's origin type to "user" regardless of what the transaction's actual origin was, which inflated sender counts specifically on digital-asset and NFT-related rows that weren't actually user-originated. It shipped alongside a cleanup that removed redundant, duplicate HLL calls sitting in the same code path.

The pipeline underneath, and the incidents it produced on its own

The counting mechanism sits inside a small pipeline of its own: a service increments Redis-backed counters per environment as events happen, and a leader-elected "sealing" process periodically flushes completed time buckets into a durable table. Two separate incidents came out of that pipeline, independent of the five counting bugs above.

The first: the leadership-election check for who becomes the sealing leader only ran once, at process startup. A quick pod restart left a stale leadership heartbeat behind, and no pod ever re-ran the election to notice the old leader was gone. Sealing stalled for roughly 29 hours on one environment before a later restart happened to trigger a fresh election and catch everything up. No data was actually lost, because the underlying Redis counters kept accumulating with their TTLs refreshed the whole time, but the reported metrics were stale for over a day before anyone would have seen it in a dashboard. The fix was straightforward once diagnosed: retry the leadership election on every seal cycle, not only once at startup.

The second was a load problem, not a data problem. During any catch-up or reprocessing run, every block being reprocessed carries a historical, not live, timestamp. That historical timestamp routed metric increments down a direct-to-database write path instead of the normal Redis-then-leader-seals path, and with many pods reprocessing concurrently, that meant hundreds of simultaneous inserts competing for the same handful of bucket rows, with most of them stuck lock-waiting. The practical effect was block ingestion throughput dropping to zero every time this write path triggered during a large catch-up. The workaround in place is to disable that direct-write path entirely during any catch-up or reprocessing run. A proper fix, moving those historical writes through the same leader-only accumulation path as live traffic, hadn't shipped as of this writing.

What this actually demonstrates

The algorithm here is not the hard part, and pretending otherwise would undersell what actually happened. HyperLogLog union is a known, well-documented technique. What's harder, and less often written about, is that a sketch-based counting system sits at the intersection of several failure modes that don't show up in a tutorial: reseal semantics, concurrent-writer races, ORM behavior that silently changes what a "successful insert" means, and a leader-election scheme that has to keep re-checking itself rather than assuming its first decision holds forever. Five real bugs in one week isn't a sign the system was poorly built. It's what building something that reports an exact number, under continuous concurrent load, from a fundamentally approximate data structure, actually looks like when you take correctness seriously enough to keep checking it.