Reporting that doubled spend and paused the fleet

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 not over-debited. Impression debit uses a cross-source covered-spend guard that reconciles the reporting sum against the lifetime snapshot. That guard is load-bearing. 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.

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

The cross-source covered-spend guard is why the wallet survived a reporting bug that paused the fleet. Do not replace it with a single sum. Do not rebuild a dedupe key 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.