Order Reconciliation
Exact match first, AI only when needed
- 70%Of 50 records matched exactly, with no AI
- 86%Resolved without a single AI call
- 7 of 50Reached the AI, and 4 of those were resolved automatically
Portfolio project, realistic test data. See all results
For a direct-to-consumer (DTC) brand, one order leaves a trail in three different systems: the online store, the payment processor and the shipping partner. There is no single reference number that ties the three together. Matching them up by hand means someone cross-checking spreadsheets every week, and the discrepancies that matter most (a payout for an order that never shipped, an amount that doesn’t match) are the easiest to miss.
Order Reconciliation
What I Built
Every order goes from raw data to a logged outcome with a stated reason, and no person is involved until a genuinely unclear case needs one:
Here’s a walkthrough of the system in action:
- Pulls and tidies the data
In one run it collects every Order, Payout and Fulfillment record from Airtable and puts them in the same format, with cleaned-up reference numbers and consistent field names.
- Tier 1 · exact match, no AI
If the reference and the amount both line up, the order is logged as Matched. If the reference lines up but the amount doesn’t, it’s logged as an Amount Mismatch. Anything with no matching reference moves on to the next pass.
- Tier 2 · near matches, still fixed rules, no AI
Compares the leftover records’ reference numbers for near matches (using a standard text-similarity score called Jaro-Winkler) and resolves automatically any match strong enough (0.85 or higher on a 0 to 1 scale) that a person would reach the same conclusion instantly.
- Tier 3 · only the truly unresolved go to the AI
The AI gets a short, auditable instruction: the order, up to three scored candidate matches and a request for a confidence score with its reasoning.
- Routes by confidence
High confidence: resolved automatically, with the AI’s reasoning logged. Medium confidence: sent to Slack for a person to decide, with all the candidate matches. Low confidence: logged straight away as an orphan (an order with no confident match), with no escalation needed. Orphans stay in the Reconciliation table for a person to review.
Order Reconciliation
Engineering Highlights
Select a heading to read the detail. Select it again to close it.
Not just an exact-match pass: a second pass scores leftover records for near matches and resolves anything a person would recognize as an obvious typo, without calling the AI at all. The AI only sees records that survive both passes.
A single scored answer drives three outcomes: resolve automatically and log the reasoning, send to Slack with full context or log straight to an orphan status, since a low-confidence “maybe” isn’t worth a person’s attention.
Every Airtable read or write, the Claude call and the Slack post retries automatically (3 attempts, waiting a little longer each time) before it counts as a real failure and triggers a separate error-handling workflow.
n8n’s built-in Python environment doesn’t allow even Python’s standard tools for text matching, dates or reading structured data, so text cleaning, date math and reading the AI’s reply all had to be written from scratch.
Order Reconciliation
Results
This is a portfolio project on a fictional business: the results below are illustrative of how the system behaves on realistic test data, not measurements from live usage.
What each claim rests on
Each row matches a claim from What I Built or Engineering Highlights with the test behind it. Tested on 50 orders. Rows run from the strongest evidence to the weakest.
Two rule-based passes before the AI is ever used
35 of 50 orders matched exactly (Tier 1) and 8 more were resolved as near matches (Tier 2), so 43 of 50 (86%) never reached the AI.
Tested on 50 ordersOne AI answer leads to one of three outcomes
Of the 7 orders that reached the AI, 4 were resolved automatically at high confidence, 2 were sent to Slack for review and 1 was logged as an orphan.
Tested on 7 ordersEvery outcome is logged with a plain-language reason
All 50 orders in the test run were logged with a plain-language reason.
Tested on 50 ordersEvery outside connection retries before it fails
Pointing the AI scoring step at a deliberately broken connection produced three retries, then the separate error-handling workflow alerted Slack.
Shown in one test
Limits: The numbers show how the 50 orders were routed. They don’t include a check of the resolved matches against known-correct answers.
The numbers
resolved instantly, zero AI (Tier 1)
resolved as near matches (Tier 2)
reached the AI (Tier 3)
Of the 7 records that reached the AI: 4 were auto-resolved at high confidence, 2 were sent to Slack for a person to review and 1 was logged as an orphan. 47 of 50 records were resolved automatically; 2 went to Slack for review and 1 was logged as an orphan. Every outcome carries a logged, plain-language reason and an audit trail back to the evidence that decided it.
Failure test: I forced a real failure by pointing the AI scoring step at a broken connection. The retry logic worked as set up (three attempts, each after a longer wait) before the step failed for real, and the separate error-handling workflow caught it and alerted Slack.
How this helps your business
What you get from a system like this.
Zero AI cost for most cases
86% of records (Tier 1 + Tier 2) resolve without a single AI call, keeping routine reconciliation runs free of AI cost and delay.
Full audit trail
Every outcome (matched, mismatched, resolved automatically, sent to a person or orphaned) is logged with a plain-language reason and the exact evidence that decided it.
Retries before failing
Every outside connection retries three times, waiting longer each time, before it counts as a real failure, so one slow response doesn’t sink a whole run.
Rules first, AI last
Two rule-based passes run before any AI is used, so the AI only ever sees the genuinely unclear remainder.
Confidence decides what happens next
A single AI score drives three outcomes (resolve automatically, send to a person or log as orphan), so low-confidence guesses never reach the review channel.
Failures are caught and reported
A separate error-handler workflow catches failures that survive the retries and alerts the team in Slack immediately.
Reviewable AI reasoning
The AI’s reasoning is logged alongside every record it resolved automatically or sent to a person, so a person can check its judgment without re-running anything.
Easy to extend
A single cleanup step puts three different source systems into one common format, so the same process can take in a new source with minimal changes.
Let’s find what your process is letting slip.
Book a free 30-minute call. We’ll look at one process that’s costing you time, leads or money and where it breaks down today.