Data integrity watchdogs for a multi-account marketplace platform

A marketplace sync that stops producing rows looks identical to a slow sales day. No exception, no alert, no red status anywhere — the dashboard just flattens, and whether that's a problem or a Tuesday is unknowable from the inside.

Across a portfolio of seller accounts on four marketplaces, the platform's numbers had drifted from the marketplaces' own. Not dramatically, and not in one place. A handful of separate causes, each individually plausible, none of which had produced a single error:

• Orders merged together because an identifier assumed to be globally unique wasn't
• A human-facing order reference assigned by a read-modify-write race, so hundreds of orders shared a number
• "Refunded" treated as "cancelled" in two different adapters, killing live orders and erasing real revenue
• Line revenue recorded at list price rather than what the buyer actually paid, so per-product reporting was inflated while order totals stayed correct
• Orders that had been removed upstream living on locally forever

The pattern behind all of them mattered more than any one bug: each marketplace adapter had independently re-implemented the same three concerns — status mapping, line economics and identifier assignment — with no shared contract and no invariant asserting they agreed. Fixing the five bugs would have bought a few months. The class of bug would have come back.

Five layers, each catching what the one before it misses, all inside the scheduler that already existed. No new workers, no new infrastructure.

1. Prevent at write. Constraints that make the bad state unrepresentable rather than merely unlikely: a uniqueness key scoped to the owning account instead of a global one, an atomic counter for order references (a row lock serialises assigners and keeps the sequence gap-free), and a duplicate-enrolment guard keyed on a verified marketplace identity.

2. Self-heal daily. Known-recoverable states get repaired automatically instead of queued for a human: orders confirmed gone upstream are retired (never hard-deleted), orders that arrived without a price are re-fetched after they've had time to settle, and the incremental pull was switched to filter on last-modified date so late cancellations and edits re-enter the window on their own.

3. Reconcile weekly against marketplace truth. Periodically the system stops trusting itself and asks each marketplace directly what it has, then compares. This is the only layer that can catch a bug in the sync logic itself, because it uses a source outside the system under test.

4. Alert on things that need a person. A cancellation arriving on an order already in production, already batched, or already labelled is a real-money event and goes to a human immediately through the existing notification channel — not to a log file.

5. Watch symptoms nightly. Around fifteen SQL checks asserting things that cannot both be true — item counts against line counts, subtotals against totals, shipped orders with no value, currency gaps, stale syncs, expiring tokens, heartbeat. Results persist, render on a Data Integrity page with a plain-English explanation and the exact query to list offending rows, and notify only on new or worsening problems.

The nightly layer checks invariants, not known causes. It doesn't ask "did that collision bug come back?" — it asks "can these two numbers both be true?"

That distinction is the whole value. A check written against a known bug catches only that bug. A check written against an invariant catches every future bug that violates it, including ones nobody has imagined. In practice this layer found problems that were never on anyone's list, which is precisely what it was for.

Two supporting choices made it usable rather than ignorable:

• Notify on change, not on state. A check that fires every night because a known condition persists trains everyone to ignore the channel. Alerts fire on new or worsening problems only.
• Make each finding self-serve. Every check ships with an explanation and the exact SQL to list the offending rows, so investigation starts immediately — and reads just as well to an AI assistant asked to look into it as to a person.

Two findings are worth stating plainly, because both are lessons rather than wins.

Two of the watchdog's own critical alerts were false. One compared a gross figure against a net one, so every discounted order looked broken. The other flagged shipped orders with no total — which the marketplace genuinely returns for free replacements, confirmed by probing the API directly. Dozens of false alarms between them.

A check that cries wolf is worse than no check, because people stop reading it. Both were replaced with true invariants, narrowed to the cases where the full breakdown is actually captured.

A repair job reported complete success while changing nothing. It wrote its audit events, counted its successes, returned zero failures — and the numbers it was meant to fix did not move. The cause was a single line: refreshing an object from the database between mutating it and reading it back, which discarded the pending changes. It looked like success for two days.

The lesson generalised into the design: a job's self-reported success is not evidence. Every repair is now verified by re-measuring the invariant it claims to fix, which is exactly what the nightly watchdog does. A silent no-op is more dangerous than a crash, because a crash tells you something.

Related: long-running maintenance jobs were being killed by deploys restarting their container, leaving half-repaired data indistinguishable from unrepaired data. They now run detached with a heartbeat and a persisted final report, and an interrupted job is itself one of the nightly checks.

The measurable change is not a percentage — it's who finds out first.

• Silent failures surface in hours instead of months, through a system that checks itself rather than a client noticing a number looks wrong.
• Root causes are fixed once, centrally. Status vocabulary now has a single source of truth across all four marketplaces, unit-tested, with a test asserting no adapter reintroduces the substring matching that caused the original bug.
• Investigation starts from evidence. Each sync retains the marketplace's raw payload for a short window, so debugging begins with what was actually sent rather than with a reconstruction.
• New bug classes get caught by existing checks, because invariants don't need to know what went wrong to notice that something did.

The work continues — each new finding tends to add one invariant rather than one patch, which is the direction that compounds.