Postgres batches fail as a team, so our card prices started lying

A price on a card page was wrong. Not stale, not rounded oddly. Wrong. The card said it had been checked today, and the number next to it was a leftover from months ago.

The cause was quiet: every price history batch for that card was dying on a duplicate-key collision, and nothing downstream ever looked. We just shipped the fix to Binderdex, and the pattern is worth stealing even if you never touch card prices.

What was actually happening

Price histories arrive as batches of daily rows. The write was a big "insert these, and on a conflict with an existing row, update it" upsert. That's normal. What wasn't normal: the history write swallowed batch errors. A batch could fail entirely, and the pipeline would still mark the card as checked and move on. The current-price write used its own inline validation, so it stayed healthy. Only history quietly stopped growing.

Characterize the bug first

Before fixing anything we wrote tests that pinned today's broken behavior. Not "this should be X", but "this is currently Y". Then we reproduced it against a real local Postgres: a payload with two entries whose conflict tuples collide (one with a date string, one with a timestamp, one graded, one ungraded) inserted zero rows and returned errors: []. Zero rows, empty errors. The database refused the batch, the app shrugged.

The fix, one commit at a time

It landed in three separate commits over the night. First, toCents(): one function that turns dollars into whole cents and rejects NaN, zero, negative, non-finite, and sub-cent prices. History rows with a null price are dropped before they ever reach the insert. Second, dedupe on each table's real conflict key: variant + timestamp for ungraded, variant + grader + grade + timestamp for graded, last entry wins. Third, errors now propagate. A failed batch puts "batch N of M failed: cause" into the result object, the current-price and alert writes are skipped, and the card stays in the refresh queue instead of being marked done. On the same payload that used to lose every row, it now writes two and returns empty errors. Same fixture, different story.

The takeaway for any batch writer

If a batch write can fail silently and something downstream marks the work "done", you don't have a batch pipeline. You have a memory hole.

1. Validate once, centrally: one money-parsing function instead of inline filters scattered across call sites.
2. Fail loud, fail early: surface batch errors in your result type rather than a log line nobody reads.
3. Gate "done" on success: don't bump the timestamp until the writes actually happened.
4. Characterize before fixing: pin the broken behavior in tests first, then flip them when you fix it.

One SQL change, checked with xmin

Also in the window: the sealed price-change refresh used to run three UPDATE statements (7, 30 and 60-day windows), each rewriting every row three times with no change guard. It's now one statement with CTEs, and the guard is the part worth copying. Only rows whose stored values actually differ get rewritten:

update sealed_prices p set
  change_7d = c.pct_7d,
  change_30d = c.pct_30d,
  change_60d = c.pct_60d
from sealed_changes c
where p.id = c.id
  and (p.change_7d, p.change_30d, p.change_60d)
      is distinct from (c.pct_7d, c.pct_30d, c.pct_60d);

To prove the rewrite was safe, we ran the old and new versions side by side on a copied fixture database and compared every value. Then we ran the new one twice and checked xmin: the second run rewrote zero rows. Equivalence and idempotence in two queries.

Splitting a 9,000-line file the safe way

Overnight we also split a 9,112-line tool-renderer file into 13 per-tool modules, loaded lazily so each page only ships the tool it shows. The part worth copying: before touching anything, we snapshot-tested the server-rendered HTML of all 16 tool pages against a production build. After the split: identical markup, with only per-request trace ids differing. A codemod mapped every declaration to the tools that use it, and the split fell out mechanically. It also exposed about 1,100 lines of dead UI code that had been riding along invisibly.

One honest footnote: a backfill path that calls the same write still ignores the new errors field. It always ignored failures, so nothing regressed, but it's tracked for a follow-up. Knowing where you're still weak beats pretending you're not.