Counting scans without tracking people

The design in one paragraph

When someone scans a dynamic code, the redirect worker increments a counter in a bucket. The bucket is the tuple (code, day, hour, local hour, country, region, city, device class, network, referrer host), and incrementing it is a single upsert: on conflict, add one. There is no per-scan row in the analytics store at all. A raw event table exists only as a seven-day debugging window, and it carries coarse country, device class, referrer and a timestamp: no IP, no user agent, no identifier of any kind, and it is pruned daily. Every chart, CSV, API response and email digest an owner sees is computed from the buckets.

-- the entire write path for one scan:
INSERT INTO scan_aggregates
  (code_id, day, hour, local_hour, country, region, city, device, network, referrer_host, n)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)
ON CONFLICT (code_id, day, hour, local_hour, country, region, city, device, network, referrer_host)
DO UPDATE SET n = n + 1;

Why aggregate-at-write, not aggregate-later

The usual architecture is an event table plus a nightly rollup job. We rejected it for a printed-code product. An event row that says “one scan, this country, this city, this device, this minute, on this code” is re-identifiable in combination: a pub’s menu code scanned once, from one town, on one evening, is very often one specific person, and no amount of later aggregation un-writes the row that existed in between. Aggregating at write means the correlatable row is never created. A breach of the analytics store yields tables of counts, and counts cannot be re-identified into visit histories.

This also settled retention. Anonymous buckets are not personal data, so an owner’s full scan history can be kept for the code’s lifetime without a data-hoarding problem, while the raw event window prunes to days instead of years. The product feature (history) and the privacy property (no history of people) stopped being in tension the moment the event row stopped being the product.

k-anonymity for geography

Town and city detail is what makes scan analytics sellable, and it is also the most identifying dimension we carry. So no geography is ever named unless it has at least five scans in the requested window; anything smaller folds into an “Other” bucket. The suppression happens at read time, per query, so the k in k-anonymity holds for whatever slice the owner asks for: this month, this code, this campaign. A single scan from a village is a person; five scans from a village is a pattern of traffic, and that line is drawn by the query layer, not by whoever is reading the chart.

What we do not store, and the scheme we rejected

No scanner identifier of any kind exists in the scan path: no IP address, hashed or otherwise, no cookie, no device fingerprint, no cross-code key. Two scans by the same person on the same day are indistinguishable, in our data, from two scans by strangers.

The rejected design is worth naming, because it is the obvious improvement and another generator ships it: a one-way, daily-rotating code derived from the visitor, used to count unique visitors and repeat scans without storing an IP. It is an honest attempt, and it is still a per-scanner pseudonymous identifier. It links one person’s scans within a day, it invites the question of whether the rotation can be reversed or brute-forced, and it re-opens every profiling argument for a store that had none of them to answer. We also run cookieless, DNT-honouring analytics on our own site with no consent banner; an identifier scheme in the scan path would undercut the position that makes possible.

Where we do need correlation, it is about owners and abuse, not scanners: API telemetry stores a salted SHA-256 hash of the IP, never the raw address, for rate limiting and attack investigation, keyed to accounts and keys rather than to the scanning public.

The engineering trap that turns buckets back into events

One implementation detail carries the whole design, and it fails silently. The bucket tuple is the table’s primary key, and SQLite treats NULL as distinct from itself inside a primary key. If an unknown dimension is stored as NULL, every scan with an unknown city appends a new row instead of incrementing the existing “unknown” bucket, and the table quietly degrades back into an event log with unbounded cardinality. Unknown dimensions are stored as empty strings, never NULL. Anyone copying this design should write that test first.

What the design costs

Honest accounting, because every choice here removes a feature someone asks for:

• No unique or repeat visitor counts. We report total scans. Any product claim of “unique visitors” from a scheme like ours would be a fabrication.
• No funnels or journeys across codes. We cannot say a person scanned the window card then the flyer; nothing links scans to each other.
• No re-engagement of scanners. The owner can be emailed about quiet codes; the scanner is, correctly, unreachable.
• Suppressed geography. Small-town detail simply does not render below k=5, and an owner in a village sees “Other” for their own high street.

We keep counts, time patterns, geography above the k threshold, devices, referrers and connection classes, which answers every question a printed-code owner has actually asked us. The capabilities above are the price, and printing them here is deliberate: a privacy posture that hides its costs is marketing, not a design.