Where Did the Money Go? Building OTA Reconciler

If you run a hotel or short-term rental business that takes reservations through Booking.com, here's a question worth sitting with for a minute: do you actually know whether you're being paid correctly?

Most operators don't. They can't. There is no single screen, anywhere, that shows you Booking.com's view of a reservation next to your PMS's view of the same reservation. The two systems live in different worlds. When they disagree — wrong commission, missing booking, status drift, mysterious refunds — nobody finds out until it's too late to dispute.

That's the problem I built OTA Reconciler to solve.

The Pain

Every month, the controller would download a Booking.com reservation statement and start cross-referencing it against PMS exports in a spreadsheet. Find the discrepancies, write notes, file disputes, hope.

That workflow is fine when you have ten reservations a month. It is unsustainable when you have hundreds across multiple properties on multiple PMSes (Hostaway for the cabins, Cloudbeds for the hotel). The reality: most discrepancies were never caught at all. The ones that were got disputed too late.

If you can't see the leak, you can't plug it. Most OTA leaks live in the gap between the channel and the PMS.

What OTA Reconciler Does

Upload your monthly Booking.com export. The app pulls the same period from your PMS (Hostaway or Cloudbeds, via API). It matches reservations three ways:

1. ID-first — Booking reservation number vs. Hostaway channel reservation ID.
2. Exact fallback — when IDs disagree, match on dates + guest name.
3. Fuzzy match — for the inevitable cases where the guest name is spelled differently in each system.

Then it flags every discrepancy across six categories:

• MISSING_IN_PMS — Booking has it, PMS doesn't. Lost reservation.
• MISSING_IN_BOOKING — PMS has it, Booking doesn't. Probably a manual booking that wasn't logged.
• STATUS_MISMATCH — one says cancelled, the other says active.
• DATE_MISMATCH — different check-in/out windows.
• AMOUNT_MISMATCH — different totals.
• COMMISSION_MISMATCH — Booking is charging a different commission than the contract says.

Each discrepancy gets a row with both views side-by-side, a resolve/ignore action, and an audit trail of who decided what and when.

The AI Dispute Workflow

Finding the discrepancy is half the work. The other half is writing the dispute response — and that was the part that kept slipping.

So I added a dispute queue. Add a Booking-linked discrepancy to the queue, and the app generates a dispute response using OpenAI based on the discrepancy type, the property, the reservation context, and the contract terms. The controller reviews and submits. Lifecycle is tracked: PENDING → GENERATED → SUBMITTED → CLOSED.

What used to be a half-day of writing per dispute is now five minutes of editing per dispute. The disputes actually get filed.

The Stack

Next.js 14 + TypeScript. PostgreSQL via Prisma. Tailwind. Zod for input validation. NextAuth for credentials. OAuth integrations with Hostaway and Cloudbeds, with token caching and proper retry/backoff on 429/5xx. Recharts for the summary view.

The pluggable PMS adapter interface was an upfront design call that paid off — adding Cloudbeds after Hostaway took days, not weeks.

What I Learned

The technical work was straightforward. The hard part was modeling the messiness. Real-world data doesn't fit neat schemas. Names get misspelled. IDs get reused. Booking changes statuses without telling anyone. The matching engine spent more iterations on edge cases than on the happy path.

One specific thing that bit me: idempotency. The first version re-ran reconciliation cleanly the first time and then created duplicate discrepancies the second time. The fix — upserting on a composite key — sounds obvious in hindsight, but it took two reruns to catch.