Canonical data model and decision engine for automated three‑way invoice reconciliation in travel agencies

If you've already decided to automate reconciliation, the hard part isn't the "why." It's the stuff nobody writes about: what a Booking record should actually contain, how the match engine decides between an exact match and a tolerance pass, and what happens to invoices that don't match at all. The parent piece on how to automate supplier invoice reconciliation for travel bookings covered the case for doing this. This one hands your team the artifacts to build it.

What follows is opinionated on purpose. Travel reconciliation breaks in predictable ways — currency rounding on net rates, hotel invoices that bundle two bookings into one line, GDS confirmation numbers that don't match the supplier's own reference. A generic three‑way match built for office supplies will choke on all three. The schemas and rules below are shaped around what actually goes wrong in a travel back office.

Why three‑way match is harder in travel than in standard AP

In a normal accounts‑payable world, three‑way match means: purchase order, goods receipt, supplier invoice. All three usually share a PO number and quantities are integers.

Travel doesn't work like that. Your "PO" is a booking confirmation. Your "goods receipt" might be a check‑out, a flown segment, or a tour completion — events that happen on different dates and sometimes never get logged. And the amounts are fractional, FX‑adjusted, and frequently commissionable, which means the number on the invoice and the number you expected are supposed to differ.

So the three records we reconcile are Booking, SupplierInvoice, and Payment. The match isn't "do these three numbers equal each other." It's "does the invoiced net align with the booked net within an agreed tolerance, after accounting for commission, FX, and known supplier quirks — and if not, who owns the discrepancy and how fast must they clear it."

Get the data model wrong and you spend your automation budget building exception queues nobody can clear. Get it right and roughly 70–85% of invoices clear untouched, which is the only ratio that makes this worth doing.

Canonical schema: Booking

The Booking record is the anchor. Everything matches to it. The most common mistake is under‑modeling the booking — storing a single total with no line breakdown — which makes it impossible to match a supplier invoice that itemizes.

Keep bookings normalized to the line level. One booking, many booking lines (room, tax, resort fee, transfer).

{ "bookingid": "BK-2026-00184412", // required, PK "agencyref": "TRV-ITN-55123", // required, your itinerary ref "supplierid": "SUP-ACCOR-EU", // required, FK to supplier "supplierbookingref": "AC7741920", // required if supplied, used for fuzzy match "gdspnr": "QX4T9P", // optional "clientid": "CL-99812", // required "status": "confirmed", // required: confirmed|amended|cancelled|completed "currency": "EUR", // required, ISO 4217 "bookedgross": 1840.00, // required "bookednet": 1564.00, // required, what you owe supplier "commissionrate": 0.15, // optional, decimal "commissionamount": 276.00, // optional "servicedatestart": "2026-07-14", // required "servicedateend": "2026-07-18", // required "fulfillmentevent": null, // optional: checkout|flown|completed ts "lines": [ // required, >=1 { "lineid": "BK-...-L1", "type": "room", // required: room|tax|fee|transfer|air|other "description": "Deluxe King, 4 nights", "qty": 4, "unitnet": 365.00, "linenet": 1460.00, "taxable": true }, { "lineid": "BK-...-L2", "type": "tax", "description": "City tax", "qty": 4, "unitnet": 26.00, "linenet": 104.00, "taxable": false } ], "fxrateatbooking": 1.000000, // optional, base->booking currency "createdat": "2026-05-02T10:21:00Z", // required "updated_at": "2026-06-01T08:44:00Z" // required }

Required vs optional, quickly: bookingid, supplierid, currency, bookednet, status, service dates and at least one line are non‑negotiable. supplierbookingref is "required if your supplier returns one" — and if they do, store it, because it's your single best fuzzy‑match key. commissionamount is optional only because some net‑rate products don't carry it; if you work commissionable, make it required in your own validation.

One operational note: store booked_net as the number you genuinely expect to pay, after contracted discounts, not the rack figure. Teams that store gross and try to derive net at match time introduce rounding noise that trips the tolerance band for no reason.

Canonical schema: SupplierInvoice

Invoices arrive messy. If you're running OCR/IDP on PDFs, this is the record the extraction lands in — so model it to hold both the raw extracted values and the parsed/normalized ones. Keeping a raw block pays off every time you need to debug why a match failed.

{ "invoiceid": "INV-INT-7781", // required, your internal PK "supplierinvoiceno": "AC-INV-556201", // required, supplier's number "supplierid": "SUP-ACCOR-EU", // required "supplierbookingref": "AC7741920", // required if present "invoicedate": "2026-07-19", // required "duedate": "2026-08-18", // optional "currency": "EUR", // required "invoicedtotal": 1566.50, // required "invoicednet": 1566.50, // required if separable "taxtotal": 104.00, // optional "lines": [ // required, >=1 { "invlineid": "L1", "description": "Room 4 nts Deluxe", "qty": 4, "unitamount": 365.00, "lineamount": 1460.00 }, { "invlineid": "L2", "description": "Taxe de sejour", "qty": 4, "unitamount": 25.625, "lineamount": 102.50 } ], "source": "ocridp", // required: ocridp|edi|api|manual "confidence": 0.93, // optional, 0-1 from IDP "raw": { // optional but recommended "ocrtextref": "s3://inv-stage/7781.json", "extractedfields": { "...": "..." } }, "matchstatus": "unmatched", // required: unmatched|matched|exception|partial "receivedat": "2026-07-20T06:10:00Z" // required }

The field that saves the most grief is confidence. When an invoice comes from ocr_idp with a confidence below your threshold (0.90 is a reasonable starting point), route it to a human review lane before it ever hits the match engine. Letting low‑confidence extractions into matching is how you generate phantom exceptions — the engine flags a mismatch that's really just a misread "8" as a "6."

Canonical schema: Payment

Payment closes the loop. In travel this is often many‑to‑many: one wire pays several invoices, or one invoice gets split across a deposit and a balance. Model the allocation explicitly rather than assuming one payment per invoice.

{ "paymentid": "PAY-2026-4471", // required, PK "method": "banktransfer", // required: card|banktransfer|bsp|offset "currency": "EUR", // required "amount": 5820.00, // required, total paid "valuedate": "2026-08-15", // required "supplierid": "SUP-ACCOR-EU", // required "fxrateatpayment": 1.000000, // optional "allocations": [ // required, >=1 { "invoiceid": "INV-INT-7781", "allocatedamount": 1566.50 }, { "invoiceid": "INV-INT-7782", "allocatedamount": 4253.50 } ], "reconciled": false, // required "created_at": "2026-08-15T12:00:00Z" }

If you settle through BSP or a card wall, the allocations array is where your remittance file gets parsed into line‑level links. Agencies that skip allocation modeling end up "reconciling" at the statement level, which hides every per‑booking discrepancy inside a lump sum — the exact leakage this whole exercise is meant to catch.

CSV equivalents

bookings.csv — bookingid,supplierid,supplierbookingref,currency,bookednet,commissionamount,servicedatestart,servicedateend,status

supplierinvoices.csv — invoiceid,supplierinvoiceno,supplierid,supplierbookingref,invoicedate,currency,invoicednet,taxtotal,source,confidence,match_status

payments.csv — paymentid,supplierid,currency,amount,valuedate,invoiceid,allocated_amount,reconciled

For CSV, flatten allocations into one row per allocation — the composite key becomes paymentid + invoiceid.

The decision engine: exact → fuzzy → tolerance → exception

The order matters more than any single rule. Run the cheap, certain checks first and only fall through to fuzzy and tolerant logic when the clean match fails. Running tolerance before exact is a classic mistake — it lets a "close enough" match consume an invoice that actually belonged to a different booking.

function reconcile(invoice): # --- STAGE 0: gatekeeping --- if invoice.source == "ocridp" and invoice.confidence < 0.90: return exception(invoice, "E-OCR-LOWCONF") candidates = findbookings( supplierid = invoice.supplierid, currency = invoice.currency, datewindow = invoice.invoicedate +/- 45 days ) if candidates.empty: return exception(invoice, "E-NOBOOK") # --- STAGE 1: EXACT --- exact = candidates.where( supplierbookingref == invoice.supplierbookingref AND abs(bookednet - invoice.invoicednet) == 0 ) if exact.count == 1: return match(invoice, exact[0], "EXACT") if exact.count > 1: return exception(invoice, "E-DUPMATCH") # --- STAGE 2: FUZZY (identity, not amount) --- fuzzy = candidates.where( refsimilarity(supplierbookingref, invoice.supplierbookingref) >= 0.90 OR (clientid match AND dateoverlap AND linecount match) ) if fuzzy.count != 1: return exception(invoice, "E-NOMATCH" if fuzzy.empty else "E-AMBIG") booking = fuzzy[0] # --- STAGE 3: TOLERANCE (amount) --- tol = gettolerance(booking.supplierid, linetype, invoice.currency) delta = invoice.invoicednet - booking.bookednet if abs(delta) <= tol.absolute OR abs(delta / booking.bookednet) <= tol.percent: return match(invoice, booking, "TOLERANCE") # --- STAGE 4: CLASSIFY THE MISS --- if delta > 0: return exception(invoice, "E-OVERBILL") else: return exception(invoice, "E-UNDERBILL")

Here is a simple visual of the reconcile cascade stages.

A few design decisions worth calling out.

Fuzzy matches identity, not money. The fuzzy stage answers "is this the right booking?" It never relaxes the amount. Amount tolerance is a separate, later stage. Mixing the two is how agencies end up paying the wrong supplier the right amount.

Date windows are supplier‑dependent. A hotel invoices after checkout; a tour operator might invoice 60 days before departure. The 45‑day window is a starting default — widen it per supplier once you see their actual billing cadence.

Duplicate matches are an exception, not a tiebreaker. If two bookings match exactly, do not auto‑pick one. E-DUPMATCH almost always means a genuine double‑booking or a data‑governance issue upstream. It needs human eyes.

Recommended tolerances

Tolerance is where judgment lives. Too tight and your exception queue floods with one‑cent FX rounding. Too loose and real overbilling sails through. These are sane defaults to start from, then tune using your actual exception data after a month.

Take the more generous of absolute or percentage on small invoices and the more restrictive on large ones. A flat 1.5% on a €40,000 group booking is €600 of unchecked drift — way too much. A practical rule: cap the effective tolerance at a hard ceiling (say €250) regardless of percentage.

“

A practical rule: cap the effective tolerance at a hard ceiling (say €250) regardless of percentage.

One pattern worth watching: if a specific supplier consistently invoices 0.4% high and it keeps clearing inside tolerance, that's not "within tolerance," that's a slow leak. Pull a monthly report of signed deltas by supplier even for matched invoices. Tolerance should absorb noise, not mask a trend.

Exception taxonomy

The exception queue is where this system lives or dies. Every exception needs a code, an owner, an SLA, the evidence required to resolve it, and a clear resolution path. Without owners and SLAs, exceptions pile up and people quietly start approving them just to clear the backlog — which defeats the entire purpose.

A couple of things that make this taxonomy work in practice:

1. 1
   Owners are roles, not names. The moment you write "Sarah," the SLA breaks when Sarah's on leave. Assign to the role; let your routing map roles to current staff.
2. 2
   SLAs are tiered by money‑at‑risk, not by code difficulty. E-DUPINV and E-DUPMATCH get a 1‑day SLA because they represent either imminent double‑payment or clear data corruption. E-COMM gets 5 days because commission recovery is a negotiation, not a race.
3. 3
   Every exception carries its evidence from the start. The engine should attach the booking, invoice, and contract reference when it creates the exception record. Nothing kills reconciliation velocity faster than a clerk opening an exception and spending twenty minutes hunting for source documents.

Owners are roles, not names. The moment you write "Sarah," the SLA breaks when Sarah's on leave. Assign to the role; let your routing map roles to current staff.

SLAs are tiered by money‑at‑risk, not by code difficulty. E-DUPINV and E-DUPMATCH get a 1‑day SLA because they represent either imminent double‑payment or clear data corruption. E-COMM gets 5 days because commission recovery is a negotiation, not a race.

Every exception carries its evidence from the start. The engine should attach the booking, invoice, and contract reference when it creates the exception record. Nothing kills reconciliation velocity faster than a clerk opening an exception and spending twenty minutes hunting for source documents.

Integration points, briefly

You don't need a diagram to get the flow right, but you do need clean handoffs.

1. 1
   OCR/IDP staging. PDFs and EDI land in a staging store. Extraction runs, writes a SupplierInvoice with source and confidence, and keeps the raw block. Nothing moves forward until the record passes schema validation — reject malformed invoices here, not downstream.
2. 2
   Matching engine. Reads validated invoices and candidate bookings, runs the exact→fuzzy→tolerance→exception cascade, and writes a match_status. Matched invoices get a booking link; everything else becomes a coded exception with evidence attached.
3. 3
   ERP / accounting posting. Only matched and reconciled records post. Exceptions never auto‑post — they wait for human clearance, then re‑enter at the matching stage. This one‑way gate is what keeps a bad invoice out of your ledger.

The staging‑then‑match‑then‑post separation gives you a clean audit trail and a safe place to replay. When a supplier sends a corrected invoice, you re‑run it through the same pipeline rather than hand‑editing a posted entry.

A realistic before/after

A mid‑sized leisure agency running roughly 900–1,100 supplier invoices a month was reconciling in spreadsheets, matching on supplier name and total. Their two‑person AP team cleared maybe 60% cleanly; the rest got eyeballed, and FX drift plus rounding meant a lot of "close enough" approvals.

After modeling bookings to the line level, adding supplier‑specific tolerances, and routing low‑confidence OCR to a review lane before matching, clean auto‑match landed around 78%. The exception queue shrank to something two people could actually clear within SLA. The signed‑delta report surfaced one consolidator quietly overbilling around 0.6% on net rates — a leak of roughly €900–€1,200 a month that had been hiding inside tolerance‑approved matches. That one finding paid for the build.

Where to be careful

This model assumes you can get a reliable supplierbookingref from most suppliers. If a chunk of your volume comes from suppliers who invoice with no usable reference and no line detail — some small ground operators still do — the fuzzy stage carries too much load and your E-AMBIG rate climbs. For those suppliers, tighten the date window, require client‑level matching, and accept that a higher share will route to manual. Don't loosen tolerance to compensate; that just converts visible exceptions into invisible leakage.

Don't over‑engineer the fuzzy matcher before you have data either. Start with exact‑plus‑simple‑similarity, watch which exceptions actually dominate your queue, and add rules only where the volume justifies them.

The schemas, the cascade order, and a disciplined exception taxonomy with real owners will get you most of the way there — far more than a clever matching algorithm sitting on top of a sloppy data model ever will.