Reporting that doubled spend and paused the fleet
Eastern time stamped as UTC, plus a dedupe key on the derived hour, duplicated rows. Summed spend doubled from $24.46 to $48.92 and the fleet paused.
On this page
A spend reconciliation bug on this seat doubled reported spend from $24.46 to $48.92 and paused all ten campaigns on a daily cap they had not reached.
The wallet was over-debited too. The first version of this note said it was not, and that was wrong (correction below). The pause came from the daily-cap path reading the doubled sum.
Two mistakes, one pause
Beeswax returns bid_hour as a naive wall clock in the zone the report was requested in. This seat requests America/New_York via BUZZ_REPORTING_TIMEZONE. The old parser appended Z and called the hour UTC. Every delivery row was stamped four hours early, five in EST.
Fixing the timezone was correct and changed every derived bucket_start_at. The dedupe key was built from bucket_start_at. The next sync therefore inserted a second row for auctions already recorded, instead of updating them. Ten campaigns each gained a duplicate hour. sum(spend_cents) filtered away from beeswax_snapshot doubled from the true $24.46 to $48.92.
Both the daily-cap guard and the impression debit read that sum. The fleet looked like it had spent about 2x reality. All ten campaigns paused.
The debit followed the same path. Each wallet debit is keyed on the rollup's dedupe key, so a changed rollup key is also a new debit key. Each of the ten campaigns had its first hour debited twice: ten duplicate debits, $24.46 in all, on our own house account. The cross-source covered-spend guard did not catch it, because the stale row was still in place and made the snapshot look fully used. No customer account was involved.
Do not put a derived value in a dedupe key
The fix is provider_report_hour_label: the dedupe key now uses Beeswax's own reported hour string. The conversion to UTC can be corrected or re-zoned any number of times and rows update in place. Apply also deletes two computable legacy keys, the correct-derived one and the one the original skewed parser would have produced by appending Z. That is what heals stranded rows without a cleanup script and without fuzzy matching on amounts.
Never widen that delete to match on impressions or spend. A campaign can legitimately serve identical numbers in two different hours.
Two sources, one auction
A related double-count lives in the source column. beeswax is hourly Reporting API rows. beeswax_snapshot is one lifetime row per line item. Those two sources cover the same auctions. Every product read uses greatest(max(spend) filter (source = 'beeswax_snapshot'), sum(spend) filter (source <> 'beeswax_snapshot')). A plain sum(spend_cents) across all rows roughly doubles spend. An ad-hoc verification query reported $73.38 against a $20.00/day fleet whose real spend was $24.46. That is the same class of mistake as Supply Audit summing inventory_agg and domain_agg.
Snapshot rows also store clicks as zero on purpose. They are not click proof. Zero clicks that were a field name is the sibling incident on the click column.
What we will not simplify
Keep the cross-source covered-spend guard: it stops reporting and snapshot spend from being charged twice. But it is not a defence against a dedupe-key change, and this incident proves it. Do not replace it with a single sum, and never build a dedupe key, or anything keyed on one, from a derived timestamp. If spend suddenly doubles and every campaign hits its cap in the same hour, look at hour labels and source rows before you look at demand.
A pause that protects a cap the fleet did not hit is still a pause. The lesson is not that a safety check saved the day. The lesson is that a derived clock in a unique key will invent money.