Your Automation's Numbers Are Probably Wrong: An Upsert Bug and 89% Bot Clicks

We ran a set of scheduled agents for four months before noticing that both headline numbers on our own dashboard were wrong. Not marginally wrong. One was low by roughly 6x, the other high by roughly 9x, and because they were wrong in opposite directions the summary row looked plausible enough to keep ignoring.

The agents are unremarkable. One collector pulls new listings every four hours from a freelance marketplace, a public procurement portal, and a newsletter site. One redirect endpoint writes a row every time an outbound link is clicked. Both wrote to Postgres. Both had tests. Neither test checked the behavior that broke.

The upsert that overwrote instead of incremented

The collector writes a per-run delta into a rollup table keyed on (day, source):

insert into daily_counts (day, source, n)
values ($1, $2, $3)
on conflict (day, source)
do update set n = excluded.n;

Read that out loud and it sounds right: on conflict, set n to the new n. That is precisely what it does. The problem is that “the new n” is one run’s delta, not the day’s total. Six runs a day, each one overwriting the previous. The stored value was always the most recent run’s count.

That is also why it survived so long. The chart was stable — a flat 38 to 45 rows per day for weeks. Stability read as correctness. A counter that jitters gets investigated; a counter that sits still gets trusted.

We caught it during an unrelated monthly reconciliation. Counting the raw listings table directly over a 30-day window returned 5,180 rows. Summing n from daily_counts over the same window returned 843. The ratio, 6.1, is the number of scheduled runs per day. The fix is one clause:

do update set n = daily_counts.n + excluded.n;

But swapping overwrite for accumulate trades one failure mode for another. The overwriting version was accidentally idempotent — replaying a run changed nothing. The accumulating version double-counts on every replay, and replays happen: a retried run after a timeout, a manual backfill, a deploy that restarts the job mid-window. Neither statement is correct on its own. What makes either one safe is having a raw event table you can recompute from.

89 percent of the clicks were not people

The click counter failed in the opposite direction: it counted everything that arrived.

The redirect handler logged a row per hit with slug and timestamp. Correct SQL, correct schema, no bug in the ordinary sense. We added three fields to the raw log — user agent, referring path, and whether the edge runtime saw the request coming from a datacenter network — and then reclassified 30 days of traffic. 3,214 logged clicks:

• 1,961 (61%) announced themselves. Crawler user agents, link-preview fetchers from chat and social platforms, uptime monitors.
• 611 (19%) did not announce themselves but were obvious in aggregate: no referrer, datacenter network, and arriving in bursts across a dozen different slugs within the same second. Prefetchers and preview generators with a generic browser UA.
• 289 (9%) were us. Our deploy smoke test hits a redirect target, and an uptime check had been pointed at one for months.
• 353 (11%) had a referrer from one of our own article URLs, a browser user agent, and no burst siblings.

Every downstream number computed on 3,214 was wrong. Click-through rate looked flat and unresponsive to anything we published, which is the signature of a denominator dominated by traffic that does not care what you write. Conversion rate looked bad by a factor of nine. We had spent real time trying to “fix” a rate that was an artifact of counting robots.

The 9 percent that was our own monitoring is the part worth being embarrassed about. It is free to remove and it had been inflating the number since the day we set up the uptime check.

Three checks that would have caught both

Both failures came from the same root cause: the aggregate was the only artifact, so there was nothing to check it against.

Keep raw events append-only and derive every aggregate. If you cannot rebuild a number from scratch, you cannot audit it, and you cannot fix it retroactively when the definition turns out to be wrong. The 30-day reclassification of clicks was only possible because the raw rows still existed. Aggregates written directly, with no underlying event log, are unfalsifiable.

Write one test per counter that performs the write twice. Run the upsert with the same input two times and assert what the stored value should be — 2n for a delta counter, n for a full-state counter. That single test catches both the overwrite-instead-of-increment bug and its mirror image, the job that double-counts on retry. It is a five-line test and it is the only one that matters for this class of failure.

Classify at write time, filter at read time. Store a bot_reason column rather than dropping the row. If you discard traffic at ingest you can never revisit the rule, and the rule will be wrong — our burst-detection heuristic was too aggressive on its first pass and flagged a handful of genuine sessions from a shared corporate network.

We also added a weekly reconciliation job: recompute each aggregate from raw and alert on more than 1 percent drift. It found a third discrepancy within two weeks. The collector stamped day in UTC, the dashboard grouped by local time, and roughly 4 percent of rows landed in the wrong bucket. Small, but it was the same shape of problem, and nothing else would have surfaced it.

The uncomfortable part is that neither failure produced an error. No exception, no failed run, no alert. Scheduled agents that crash get fixed within a day, because the failure is loud. Scheduled agents that write a confidently incorrect number run for months, and every decision made against that number inherits the error silently.