Nightly reconciliation: proving two systems agree

Every integration between two systems drifts, and usually not through any fault in the code. One side was unavailable for four minutes, or a request timed out after the write had already landed, or someone edited a record by hand.

The integration itself does not notice. It processed the event, got an error, retried a few times and gave up. From its point of view that is a handled failure. From the client's point of view, their stock numbers are wrong.

A reconciliation job is the thing that finds those records. It runs on a schedule, compares the two sides, and re-queues whatever does not match.

Why this matters more than it sounds

Drift does not announce itself, which is why it tends to be discovered late and in bulk.

Take an integration handling 50,000 orders a day. If 0.1% fail to sync and never recover, that's 50 stuck records a day. Nobody notices 50. After a month it's 1,500, and fixing them means reconstructing which of the two systems was right at the time, across records that no longer carry that information.

The job below is maybe thirty lines. Writing it in week one is much cheaper than reconstructing six months of divergence later.

What should you compare?

You have two options, and most integrations end up using both.

Compare state you already track. If your side records whether each order synced, reconciliation is a query for records where synced is false. This is cheap and catches the common case, which is a sync that failed and was never picked up again.

Compare against the other system. Fetch what the partner has and diff it against your own records. This catches things the first approach cannot: a record that your side thinks synced but the other side never stored, or a value that was changed directly in the other system.

The first is a query against your own database. The second requires an API call per batch and is slower, so it usually runs less often. Start with the first.

Keep a cursor for the incremental pass

A reconciliation job that scans everything works fine for a while, then gets slower every week until it stops finishing inside its window.

The fix is to store the timestamp of the last successful run and only look at records created or modified since then. Be careful about which query you apply that to. The cursor belongs on the sweep for recently changed records, not on the query for records that never synced. Something that failed to sync last week still has last week's timestamp, so a time window would quietly drop it after the first night, which is exactly the record you wrote this job to find.

Two details matter once you have one. Write the cursor only after the run succeeds, so a failed run reprocesses that window rather than skipping it. And overlap the window slightly (an hour is usually plenty), because two systems rarely agree exactly on clocks or on the order writes landed in.

The job

This is a complete nightly reconciliation on Codehooks. The cursor lives in the key-value store, the records live in the database, and the job runs in the same deploy as both.

import { app, Datastore } from 'codehooks-js';

// Every night at 03:00
app.job('0 3 * * *', async (req, res) => {
  const db = await Datastore.open();

  // Where the last successful run finished, minus an hour of overlap.
  // On the very first run there is no cursor, so start from the epoch.
  const last = await db.get('reconcile-cursor');
  const since = last
    ? new Date(Date.parse(last) - 3600 * 1000).toISOString()
    : new Date(0).toISOString();

  // Everything still outstanding, however old. No time window here:
  // a record that failed last week still carries last week's timestamp.
  const stuck = await db.enqueueFromQuery('orders', {
    synced: false
  }, 'sync-erp');

  // And anything that changed since the last run, in case a change
  // landed without the normal sync path firing. The worker's own
  // check makes re-queueing an already-synced record a no-op.
  const changed = await db.enqueueFromQuery('orders', {
    updated: { $gte: since }
  }, 'sync-erp');

  console.log(
    `queued ${stuck.ticket.count} stuck, ${changed.ticket.count} changed`
  );

  // Only move the cursor once the run has succeeded
  await db.set('reconcile-cursor', new Date().toISOString());
  res.end();
});

// The same worker your normal sync path uses
app.worker('sync-erp', async (req, res) => {
  const db = await Datastore.open();
  const { id } = req.body.payload;

  // findOneOrNull rather than findOne, which throws when nothing matches
  const order = await db.findOneOrNull('orders', { id });
  if (!order || order.synced) return res.end();

  await pushToErp(order);
  await db.updateOne('orders', { id }, { synced: true });
  res.end();
});

export default app.init();

Three things worth pointing out.

enqueueFromQuery enqueues one job per matching record, so the job stays fast no matter how many records it finds. It queues the work and finishes, rather than processing everything inline and risking a timeout. It resolves to a ticket, so the number of records queued is on ticket.count.

Two queries rather than one, because they answer different questions. The first asks what is still outstanding, at any age. The second asks what has moved recently. Only the second needs the cursor.

The worker is the same one your normal sync path uses. Reconciliation should not have its own copy of the sync logic, because a second implementation drifts from the first and you end up debugging two. It also opens with an idempotency check, which is what makes the overlap between the two queries harmless.

What happens when the two sides disagree?

This is the part no framework solves for you.

When your record says one thing and the partner system says another, something has to decide which is correct. Sometimes there is an obvious rule (the payment provider is always right about payments). Often there isn't, and the honest answer is that a human has to look.

What you can do is make that cheap. Write the conflicts to their own collection rather than resolving them silently, with both values and the timestamps. Then a person reviews a short list instead of searching two systems for a discrepancy someone reported by email.

Try not to resolve conflicts silently. If your job quietly overwrites one side with the other, you won't find out it chose wrong until a client tells you.

How often should it run?

Nightly is the usual answer, and it is usually right. It's frequent enough that drift stays small, and it runs when both systems are quiet.

Run it more often if the data drives something time-sensitive, like stock levels during a sale. Run it less often if the partner API charges per call and the volume is low.

One thing to avoid: running it so often that a slow run overlaps the next one. There is no automatic locking, so either keep the interval comfortably longer than the job takes, or write a lock key with a TTL at the start of the run and check for it before doing any work.

If the job is large enough that a failed run matters, making a scheduled job resumable covers the cursor and idempotency side in more detail.

Summary and conclusion

A reconciliation job is how you know an integration is actually working, rather than assuming it is because nobody has complained. The job itself is small: a cursor, a query for the records that never synced, and a re-queue of the same worker your normal path already uses.

It's worth writing early, before the first incident rather than after it. Store conflicts instead of resolving them silently, write the cursor only after a run has succeeded, and use the same sync logic in both paths.

If you want to see where this fits in a full integration, with the inbound webhook, the queue and the per-client setup around it, that is covered in keeping two systems in sync.