Reconciling orders at 2 a.m.

Keforo App started as a small promise: every order a shop receives, from every channel it sells through, in one list, correct. Shopify, WooCommerce and the Google Sheet the team uses when a customer messages on WhatsApp. Three sources, one truth.

The APIs took a week. Agreeing on what an order is took the rest of the year.

#Three sources, three clocks

Shopify sends a webhook the moment something changes and lets you re-fetch anything. WooCommerce sends webhooks too, when the plugin feels like it, and its timestamps are in the store's timezone. The Sheet has no webhook, no ids beyond a row number, and humans edit it directly, sometimes retroactively.

So the first rule: never trust an event to be the whole story. Every webhook is a hint that says "re-fetch this order". Every scheduled run re-reads everything that changed since a high-water mark, per source, in that source's own clock.

#One canonical order

Each source gets normalised into the same shape before anything else touches it.

interface CanonicalOrder {
  source: 'shopify' | 'woocommerce' | 'sheet'
  externalId: string // Shopify id, Woo id, or "sheet:<rowKey>"
  revision: string // updated_at, or a hash of the row
  fingerprint: string // sha256 of the normalised payload
  customer: { email: string | null; phone: string | null; name: string }
  lines: { sku: string; quantity: number; unitPrice: number }[]
  total: number
  currency: string
  status: 'open' | 'paid' | 'shipped' | 'cancelled'
  placedAt: string // ISO, UTC
  notes: string[]
}

revision is what the source says changed. fingerprint is what actually changed. The difference matters: WooCommerce bumps updated_at when someone opens the order in the admin without touching it.

#Idempotent ingestion

Writes are upserts keyed by (source, externalId). If the fingerprint matches what is stored, the write is skipped. Any worker can process any job, in any order, more than once, and the database ends up the same.

await Orders.updateOne(
  { source: order.source, externalId: order.externalId },
  {
    $setOnInsert: { firstSeenAt: new Date() },
    $set: { ...order, syncedAt: new Date() },
  },
  { upsert: true },
)

Skipping unchanged fingerprints is what makes re-reading everything cheap enough to do every few minutes.

#When sources disagree

The same real-world order can exist in two sources: a Shopify order that the team also typed into the Sheet to add a delivery note. Matching them is heuristic (same email or phone, same total, within a window), and matching is not merging.

Last-writer-wins is wrong for money. The rules are per field.

Anything the rules cannot settle becomes a conflict: stored, shown, and left for a human. A typical run over a busy shop takes about a second and a half and flags two or three conflicts. None are auto-resolved. That number stayed low precisely because the rules refuse to guess.

#Queues, backoff, and the 2 a.m. part

Each source has its own queue on Redis. A job is one order, not one source, so a broken Woo webhook does not stall Shopify. Retries use exponential backoff with jitter and a dead-letter queue after five attempts; the dead letters are what I read in the morning.

The title of this note is not a metaphor. The first version reconciled everything in one nightly job, and when it failed at 2 a.m. it failed all at once. Per-order jobs and idempotent writes made failures small, resumable and boring. That is the whole architecture: make every step safe to repeat, and repeat freely.

#What I would do differently

Fingerprint from day one; I added it after the second duplicate-notification incident. Store the raw payload next to the canonical order, because every "why does this say 43 instead of 42" question ends in the raw data. And treat the Sheet as a first-class source from the start, instead of the afterthought it was for two months.