Scroll depth has to be idempotent, and mine was not

The scroll curve on the demo page said 140% of visitors reached the top of the page. Not a rounding artifact. A number that cannot exist, sitting in a chart, rendering perfectly.

Isotherm draws heatmaps over a frozen snapshot of a customer's admin panel: clicks, rage clicks, dead clicks, mouse dwell, and scroll reach. Scroll reach answers one question — what fraction of visitors ever laid eyes on the row at 60% down the page. It is a survival curve. It starts at 100% at depth 0 and never goes up. Mine went up.

What I built first#

The obvious schema. A table keyed by (page_version_id, device, depth), one row per 5% band, and a count column. On ingest, for a scroll event reporting depth d, increment every bucket from 0 to d. Read time was then trivial: divide each bucket by the session count and you have your curve. Write-heavy, read-cheap, which is the right shape for an ingest endpoint that has to be fast.

It is also wrong, and the reason lives in the SDK, in a decision I made deliberately and then failed to think through on the server.

What the SDK actually sends#

Here is trackScroll, from sdk/src/collectors/scroll.ts:

function report(): void {
  measure();
  if (deepest === reported) return;
  reported = deepest;
  const event: ScrollEvent = { t: 'scroll', d: deepest, ts: transport.now() };
  transport.push(event);
}

deepest is a running maximum, updated on an rAF-coalesced scroll handler. report() runs immediately on init (so a visitor who lands and never scrolls still gets credited with the first screen), then on a timer at config.flushInterval — 5,000 ms — and once more on teardown. The deepest === reported guard means the same number is never sent twice. What goes out is a strictly increasing sequence of distinct depths. Someone reading a long page emits d: 12, then d: 31, then d: 44, then d: 60.

Why not send only the final number? Because there is no teardown you can rely on. A tab killed by the OS under memory pressure never runs your cleanup. Neither does a crash, a force-quit, or Safari deciding it is done with a background tab. The visitor who scrolls to the bottom and then gets reaped is exactly the visitor whose data you most want, and a teardown-only design loses all of it. The README puts it as: "The last beacon is the one that matters. A visitor who clicks and immediately leaves is exactly the visitor you're trying to explain, and fetch() is cancelled on unload." The final flush rides sendBeacon on visibilitychange → hidden, which is the last event a page is reliably given — reliably given, not reliably survived. The process can still die before the interval that would have caught the deepest scroll ever fires.

So the SDK reports early and reports often. Here is the part I did not carry over to the server: each of those events is a statement of the session's whole state, not a delta. d: 60 does not mean "scrolled 60 more". It means "this session has now reached at least 60%", and it subsumes every earlier event. The four events above are four overlapping claims about one visitor.

The bucket schema treated them as four independent visitors' worth of evidence. Each one incremented every band from 0 up to its depth, so that single session bumped bucket 0 four times, bucket 30 twice, bucket 60 once. The shallow bands accumulated one increment per event; the session count in the denominator went up by one per session. On the demo page most visitors never scrolled at all and emitted exactly one event, while a minority scrolled and emitted three or four — an average of about 1.4 scroll events per session. Divide the top bucket by the sessions and you get 140%. The curve was not corrupt. It was faithfully reporting a ratio of two things that were not comparable.

The fix is not deduplication#

The tempting repair is to make the writes unique: give every scroll event an id, keep a seen-ids set, drop replays. I rejected that, and "just dedupe it" is the answer enough pipelines reach for that it is worth saying why.

It does not even address this bug. d: 12 and d: 60 are not duplicates; they are distinct events that happen to make overlapping claims. No seen-set drops either of them. Dedupe only helps against a byte-identical replay, which is the failure the SDK's own guard already prevents.

And it costs real state. A seen-set has to be sized, expired, and shared across every PHP worker that might handle a batch from that session, and it fails open in the direction that hurts: evict early, the replay lands, the count is silently wrong. It also does nothing about ordering. These are sendBeacon payloads over an unreliable network; a batch carrying d: 31 can absolutely arrive after the one carrying d: 60. A dedupe layer accepts the late shallow one happily, because it is not a duplicate. It is just stale.

The defect was never a duplicate. It was that the write was not idempotent. count = count + 1 is a function of how many messages arrived, and how many messages arrived is a property of the network. The network is not data.

So the write became a high-water mark. From app/Services/EventIngestor.php:

private function recordScroll(int $pageId, string $sessionId, int $depth): void
{
    DB::table('page_sessions')
        ->where('page_id', $pageId)
        ->where('session_id', $sessionId)
        ->where('max_scroll', '<', $depth)
        ->update(['max_scroll' => $depth]);
}

One column on the row that already exists per (page, session). The where('max_scroll', '<', $depth) clause is the whole trick. d: 12 after d: 60 matches zero rows. A beacon the network duplicated matches zero rows. The final value depends only on the maximum of the set of depths received — not on how many arrived, not on the order they arrived in. That is the property I needed and did not have.

Several scroll events in one batch collapse the same way before they reach the database:

'scroll' => $scrollDepth = max($scrollDepth ?? 0, $this->clampInt($event['d'] ?? 0, 0, 100)),

The comment above that line in the shipped code says several scroll events in one batch "can only mean SPA route changes." That comment is wrong and I wrote it. sdk/src/index.ts calls transport.flush() on every pushState / replaceState / popstate precisely so an event never carries the wrong URL, which means two routes' scroll events cannot share a batch. The ordinary case is much duller: the 5-second report() queues a depth, then the teardown report() queues a deeper one, and both are still sitting in the queue when transport.flush(true) fires. Same page, same batch, no SPA involved. max() is right either way, but I should not have been right by accident.

The cost: the curve moves to read time#

Storing a maximum means there are no buckets, so HeatmapController::scroll() builds the curve from the maxima on every dashboard load:

$maxima = DB::table('page_sessions')
    ->whereIn('page_version_id', $versionIds)
    ->where('device', $device)
    ->pluck('max_scroll');

$out = [];
for ($depth = 0; $depth <= 100; $depth += 5) {
    $reached = $maxima->filter(fn ($max) => (int) $max >= $depth)->count();
    $out[] = ['depth' => $depth, 'pct' => round($reached / $sessions * 100, 1)];
}

Someone whose maximum was 60 saw every band up to 60. That is the entire derivation, and it is monotonic by construction: a session that clears the bar at 60 necessarily clears it at 55. The impossible chart is no longer representable, which is a stronger guarantee than a chart that merely happens to be correct.

Be clear about the trade. This pulls every session's max_scroll into PHP and makes 21 passes over the collection in memory. It is O(sessions) on the read path, on every dashboard open. SELECT max_scroll, COUNT(*) GROUP BY max_scroll would give the same curve from at most 101 rows, and that is where this goes when a page has a hundred thousand sessions. It is not there today because the reader is one admin looking at one page, not a public endpoint, and I would rather ship the version whose correctness is obvious from reading it. That limit is real and it will bite someone. It will bite them with a slow page, not a wrong number, and those are not the same class of failure.

Where the same rule did not apply#

Movement cells look identical and are the opposite case. mergeMoveCells() accumulates, on purpose. The statement is dialect-specific — the only one in the codebase, because Laravel's upsert() can only set columns to literal values:

$sql = match ($driver) {
    'mysql', 'mariadb' => "INSERT INTO move_cells {$columns} VALUES {$placeholders} ".
        'ON DUPLICATE KEY UPDATE count = count + VALUES(count)',
    default => "INSERT INTO move_cells {$columns} VALUES {$placeholders} ".
        'ON CONFLICT (page_version_id, device, grid_size, gx, gy) '.
        'DO UPDATE SET count = move_cells.count + excluded.count',
};

Note what is being added: not one, but the incoming batch's own count for that cell — clamped to 1..10,000 per cell, and folded together in PHP first so that identical cells inside one batch do not collide in a single INSERT before the conflict clause can run. That is correct because the SDK never restates a move cell. Each batch carries the samples accumulated since the last flush and then clears them. Movement messages are deltas, so summing them is the only sound thing to do. Scroll messages are state, so summing them is nonsense.

That is the actual rule, and it is not "never increment":

An accumulating write is only correct for messages that are deltas. A message that restates the sender's state must be merged with an idempotent operator — max, first-write-wins, last-write-wins — never added.

recordSession() is the same shape from the other side: insertOrIgnore, not select-then-insert, because every batch after the first in a session hits it and only the first should win. First-write-wins, last-write-wins, and max-wins are all idempotent. Read-modify-write is not, and it is the one that reads most naturally in a code review, which is why it keeps getting written.

What I got wrong was not the schema. It was believing that "re-send as you go, because teardown is a lie" was a client-side decision. It was a change to the delivery contract, and every write downstream of that contract had to be re-checked against it. I re-checked the one I happened to be editing.