Ninety-Day Reconciliation

In one sentence: a day's sales summary should show the money taken and the money earned agreeing to the penny, and on roughly one day in seven it did not — because two clients were fighting over the same records, tips that had been refunded were still counted as income, and service charges customers paid were credited to nothing.

Five changes. The first three stop two clients from sharing a record; the last two record money that was being collected but not booked. Each is small — the difficulty was knowing which line to change, not writing it.

Every imported record has an identifier the importer uses to decide "have I seen this before?". Sales orders already included the client; refunds, card payments, payouts and cash-drawer shifts did not, which is precisely why two clients could land on one record.

Applied at five places in the Square importer: order payments, refunds, payouts (twice — the record itself and the lookup that finds it) and cash-drawer shifts. ezCater orders already did this and needed no change.

This is the one that makes the change safe to deploy. The identifiers are unique keys, so the importer relies on "same id, same record". Rename them and the next import matches nothing — and would quietly create a second copy of every refund and payment in the system, leaving the originals orphaned. So the importer looks up the record explicitly, new name first, old name second, and writes to whichever it finds.

The result is pinned as the record's id on the way in, so the write lands on the existing row regardless of which name it currently carries. The proof this worked: after re-naming 213,943 records, the totals for refunds, payouts and shifts were identical before and after. Had the fallback been missing they would have doubled.

Renaming stops new collisions but does not undo old ones: a payment already shared by two orders is still one row with two owners. The migration walks each order's payments and, where another order has already claimed one, makes that order its own copy with the same amounts and points the order at the copy.

On the restored data that was 189,167 renamed and 77,599 copied, and payments owned by two orders went from 11,469 to zero. The record count rose by exactly 77,599 — the number of copies it reported making, which is the check that it created what it meant to and nothing else.

One subtlety worth recording, because it bit us: the Square id has to be recovered from the record's current owner rather than by trimming a fixed prefix. Client codes contain dashes — N-30003 — so a pattern cannot tell where the client name ends and the Square id begins. Getting this wrong scoped some records twice and doubled their tender.

Tips were summed by walking from the order to its payments. A refund-only order has no payment attached, so its negative tip was invisible. The fix adds those tips rather than replacing the calculation.

Adding rather than replacing is deliberate. Where an order does have a payment, the payment is the correct source: real orders exist whose payment carries a tip the order does not — an auto-gratuity recorded as a service charge, or a wallet tip missing from the order totals. Reading the order instead would have dropped those. Three tests hold this in place: the refund case must change, and the tendered and ordinary cases must not.

Nothing read the service-charge field at all. A new line credits it, for Square orders only and for negative amounts as well as positive.

Why the vendor test has two branches. ezCater service charges are commission the platform deducts from the restaurant, not money the diner hands over, so crediting them would make a day worse rather than better — hence the Square-only condition. But whole eras of Square orders carry no vendor field at all, and a test on vendor alone would silently credit nothing. The second branch falls back to the order's own identifier.

Why negatives matter. A returned catering fee arrives as a negative service charge and is already deducted from the day's returns; dropping negatives would lose the reversal. The line sits behind a per-client switch, off by default, so it can be turned on a few restaurants at a time.

That last one is worth a sentence for engineers: transact-schema installed schema.edn then cloud-migration-schema.edn, but a composite tuple in the first file is built from an attribute in the second. Datomic will not create a tuple before its members exist, so every test fixture died in setup. It is very likely why sales summaries had no tests before this work.

The job was run over the same ninety days at each stage, writing real summaries every time, so these are measured outcomes rather than estimates.

Neither arithmetic fix touched a day that was already correct. Across 8,733 client-days, no balanced day was knocked out of balance and no balanced day had a single figure altered — every day they changed was already wrong. Service charges are by far the larger of the two, moving $58,923.85 against the tip fix's $3,777.67.

Deduplication is the one step that puts six days out of balance. That is expected: those days were previously balanced only because a summary was empty, and filling it in exposes the same arithmetic faults every other day had. Both later fixes then close them, which is why the end-to-end figure is zero.

Excluding the ten now-deactivated duplicate records, which should not be reported on at all, the population is 8,350 client-days and the movement is 1,217 days and $69,995.61 down to 105 days and $852.38.

Both arithmetic fixes add exactly one credit line. Nothing else in a summary moves — no sales figure, no payment, no tax.

The day already carried a $60.00 card refund — the guest was given their money back, tip included — while the tip line still credited the full $482.94. The corrected figure matches the refund to the penny. The order behind it is square/order/NGLK-SM-OxSX9gpXJV394qqT8mnBGypUwKNZY: a tip of −60.00 on an order with no payment attached at all.

In every case the correction equals the imbalance exactly, which is what you would expect if the fix is recording something real that was recorded nowhere. On NGNP 2026-06-25 both fixes land on one day and pull opposite ways — $301.40 credited, $1.80 removed, $299.60 closed — a useful check that they are independent.

Every step below was performed against a restored copy of the production database. Production itself was never touched.

The count checks are the ones that matter. If re-keying had gone wrong it would have created a second copy of every record rather than updating the existing one, and the totals would have doubled. They did not move. The payment-copy step is the exception and is meant to add records — it added exactly 77,599, matching the number of copies it reported making.

Fifteen client-days across ninety days and 8,350 client-days, totalling $850.12. Everything else — 90 further days — comes to $2.26, the largest being 9.00¢, which is till rounding.

The ten deactivated duplicate records contribute a further $1,142.98 across three days. They are excluded above and should be excluded from reporting generally, since they are now dormant copies.

Two clusters — NGMV and N-30012, both in late May — are new and unexplained. They only became visible because the window was widened to ninety days; a thirty-day view did not reach them. They are worth a look before this ships.

The comparison tool is committed as auto-ap.jobs.compare-sales-summaries, which diffs summaries between two points in the same database using as-of — so "before" is production's own stored figures rather than a re-simulation. Unit tests: lein test auto-ap.jobs.sales-summaries-test auto-ap.square.core3-test auto-ap.jobs.rekey-square-external-ids-test.