Lost webhooks: how to catch the drift with a nightly reconcile

Webhooks are the right way to stay current and the wrong way to stay correct. They are fire-and-forget by nature: if the sending side crashes before it fires, or fires into a timeout, there is no error on either end. The event simply never existed.

In a WooCommerce shop the ways this happens are mundane. A plugin fatals during checkout after the order saves. A deploy restarts PHP mid-request. The queue holds an id for a post that a second plugin deletes. Each is rare; across a year of orders, none of them is rare.

Three mechanisms, not one

Any shop integration that intends to be correct needs all three, and they do different jobs:

1. Backfill for what already existed when you connected. It runs once, in a defined entity order, with totals sent up front so progress has an honest denominator.
2. Webhooks for changes as they happen. Fast, and lossy.
3. Reconcile, nightly, for the difference between what you think you have and what is actually there.

Shops that build the first two and skip the third do not find out. That is the whole problem: drift is invisible from the inside, because the records you are missing are exactly the ones you have no record of.

How a reconcile should work

The shop computes a checksum per entity type over the fields that matter, in a defined order, and sends totals and checksums. You compute the same thing over your copy. Where they agree, nothing happens. Where they disagree, only that entity type is re-imported.

• Chunk it. Checksums per bucket — by id range or by month — so a mismatch localises to a few hundred rows rather than the whole catalogue.
• Run it when the shop is quiet, and in the shop's own timezone rather than yours.
• Record the result every night, including the clean ones. A reconcile that only logs failures cannot tell you when it stopped running.

The three bugs that make it lie

Each of these produces checksums that disagree for ever while everything looks fine, which trains everybody to ignore the alert.

1. Timestamps read in the session timezone. MySQL's UNIX_TIMESTAMP() interprets a datetime in the session's timezone, so a server not running in UTC shifts every _gmt column by the offset. Convert in application code instead.
2. Sorting in the database. Postgres orders text by collation, where "2" can precede "10"; PHP sorts bytes. Neither side may use its own database for the canonical order — both sort the assembled rows themselves.
3. Counting rows you do not send. If the shop counts every user including administrators, but only sends customers, the totals disagree permanently. Count exactly what is transmitted.

The failure mode nobody expects: suppression that never ends

A common design suppresses webhooks while a backfill is running, so the two do not fight. It is correct until a backfill stalls — no cron, a crashed worker — and then "running" is a state nothing ever leaves. Live sync stops, silently, for ever.

Make suppression per entity type, and expire it after a period of no progress. Ten minutes is generous. A backfill that has done nothing for ten minutes is not a backfill any more.

What to put on a screen

• Last successful reconcile, per shop, with the time. An integration screen that says "connected" without saying when anything last arrived is decoration.
• Entities re-imported last night. Zero is the expected answer. A number that is small and non-zero every night means a hook is consistently failing, which is findable.
• Last event received. The fastest possible detector of a shop that quietly stopped talking to you.