Ninety-Day Reconciliation

Figures exclude the ten deactivated duplicate clients, which the plan says to exclude from reporting. Across the full 8,733 client-days including them the shape is the same: 1,087 → 108 days out of balance, $63,764.92 → $1,995.36.

The earlier thirty-day report derived its baseline arithmetically. This one does not. The job was run three separate times over the same ninety days, against the same data, writing real summaries each time:

• Run A — baseline. The service-charge flag cleared on all 210 clients and get-tip restored to its tendered-only form. This is exactly what production calculates today.
• Run B — plus R1. Only get-tip changed.
• Run C — plus R2. The service-charge flag switched on as well.

The underlying data — Phase 0, the re-key, the repaired charge references — is identical across all three, so what separates them is the calculation and nothing else. 18,900 summaries were written per run, 56,700 in total.

The most recent week (08-08 → 08-14) is excluded deliberately. The backup was cut mid-evening on the 14th, and the days either side of that are partial, which distorted the earlier window.

Applied in sequence, each measured against the run before it, over 8,350 reportable client-days.

Both fixes add a single credit line. Nothing else in a summary moves — no sales figure, no tender, no tax. That is why they can only ever help a day that was already short, and it is visible in the line items.

Read the two credit lines together and the story is complete: the day already carried a $60.00 card refund — the guest was given their money back, tip included — but the tip was still credited in full at $482.94, because the reversal lives on an order with no tender and get-tip only reached tips through tenders. The books claimed $60 of tip income that had been handed back. The corrected figure, $422.94, matches the refund exactly.

The customer paid $427.10 of service charge as part of a $4,975.89 card tender, so the money arrived on the debit side. No line credited it, so the day showed $427.10 more collected than earned. Adding the credit closes it exactly, and the single order responsible is square/order/NTPT-PT-KrMZzcon1cpQEJUyetErkBIpcdEZY, carrying a :sales-order/service-charge of 427.10 on a 3,198.78 order.

Every one overstated tip income, and in every case the correction equals the imbalance to the cent. NGFL was crediting $238.46 of tips on a day where $168.04 had been given back.

NGPA 2026-06-04 is the single largest repair in the ninety days: $1,344.86 of service charges collected from customers and credited to no revenue account at all.

The two corrections pull in opposite directions and still land on zero, which is a useful check that they are independent and neither is compensating for the other.

Fifteen client-days above the ten-cent materiality threshold, out of 8,350. Everything else — 90 client-days — totals $2.26.

Widening from thirty days to ninety surfaced two clusters the shorter window could not see: NGMV across five days in late May, and NGEB across four days spanning May to July. NGEB is the known ezCater fee-semantics question. NGMV and N-30012 are new and worth a look before this ships — they are the kind of thing only a longer baseline exposes.

The ten deactivated twins, excluded above, contribute a further $1,142.98 across three days. They should not be reported on at all once one client per location is settled.

Running the job 56,700 times surfaced two problems that a normal nightly pass would hide, both now fixed or documented.

The second is a deployment setting rather than a code change, but it is dramatic: with a 2 GB object cache against a 27 GB database, the final 7,958 client-days of a pass were crawling at about 35 a minute. After resizing they completed in 90 seconds. Worth checking what production's transactor is sized at.

The unit tests covering both fixes and the re-key:

Balancing was only half the problem. The other half is whether an entity can change hands between two clients at all. Rather than reason about it, every entity type the importers create with a unique external id was audited, and ownership was read out of the database's own history.

Both payout and cash-shift endpoints are filtered by location, so two clients configured on one Square location import the same records and collide on a single entity — the identical defect refunds and charges had, in two types nobody had looked at.

The re-import is the important negative result. Every payout and shift the importer fetched resolved to the client's own entity and wrote nothing — the re-key is transparent to the importer, which is exactly what the legacy-key fallback is for. And recomputing all 18,900 client-days afterwards reproduced the previous figures exactly — largest difference 0.000000, so re-keying 213,943 entities moved no money at all.

One caveat worth stating plainly: re-keying stops future contention, but it does not retrospectively re-attribute records that were claimed by the wrong client while the configuration was shared. Those stay where they were last written. Correcting them is a separate exercise, and one to do only once the business decides which client owns each location.

The schema says a charge belongs to its order:

:db/isComponent means Datomic treats charges as parts of the order rather than independent records. So [:db/retractEntity <order>] deletes the order and its charges. That is the documented behaviour, and normally it is what you want.

remove-voided-orders asks Square for the last ten days of orders, keeps the ones Square reports as voided, and retracts them. Correct on its own terms.

The problem is that where two clients shared a location, both clients' orders resolved to the same charge entity, because charge keys carried no client:

Retract either order and Datomic deletes that shared charge. The other client's order survives with its sales intact but its payment gone: the day silently goes out of balance and the tender disappears from the current database value. Measured on the restore, 35,870 of 56,829 charges (63%) in the contended clients' recent window have two parent orders.

The re-key stops new sharing but does not un-share those. Two remedies, either sufficient: split them by re-importing the affected window now that keys are client-scoped, or guard the retraction so it detaches a shared charge instead of deleting it. The guard is small and makes the operation safe whatever shape the data is in.