The invoice and the trip it pays for were separate records, so nobody could say which trips were unpaid
A booking and invoicing system for a travel agency where every invoice line points at the trip component it bills, and every payment points at the bank account it landed in. "Which trips are unpaid" becomes a query instead of an afternoon.
The brief, in specifics
- Industry
- Travel & Tourism
- Duration
- 10 weeks
- Cooperation model
- Fixed price
Client name withheld under NDA. Engagement details are shown to the extent our agreement permits.
The hard problem
- 01
Trips lived in one place and invoices in another, joined by a reference typed by hand. Part-payments across two bank accounts were normal, and a customer who had paid two of three components looked identical to one who had paid nothing. Chasing ran on memory, and the wrong people got chased.
A hand-typed reference is a join that fails silently: abbreviate it once, and the trip and its invoice are no longer connected, with nothing to show it. Part-payments across two accounts were routine, so a customer who had paid two of three components looked identical to one who had paid nothing. Chasing ran on memory, and memory chased the wrong people.
Make the invoice line the join. Each line points at the trip component it bills, so a trip knows what it has been invoiced for and what has been paid. Payments carry the bank account, which is what makes the month-end match against a statement possible at all.
How the pieces fit
- 01
Made every invoice line reference the trip component it bills, replacing free-text descriptions
Migrating the existing book to real references surfaced forty trips whose hand-typed codes matched nothing, most of them abbreviated by one person.
- 02
Recorded the receiving bank account on each payment, since part-payments arrive in different places
Each payment records the bank account that received it, the field that turns a month-end match against two statements from a search into a mechanical check.
- 03
Derived trip payment status from its lines instead of storing a status somebody sets
Deriving status meant auditing every trip the old system had marked paid, and thirty-one of them turned out not to be.
- 04
Generated the customer-facing document from the same records, so the PDF can't disagree with the ledger
The customer-facing PDF generates from the same records the ledger reads, so regenerating an invoice reflects corrections instead of reprinting a stored file.
- 05
Made the unpaid view the default landing screen, because that's the question being asked
The unpaid and overdue view is the landing screen, because that's the question the office asks every morning.
The system we were asked to build
The travel agency books trips with several components each (flights, transfers, accommodation) and invoices them in pieces. We built the system that keeps the trip and its invoicing joined up, with payments recorded against the bank account that received them.
A travel agency booking trips of several components each (flights, transfers, accommodation) and invoicing them in pieces as they're confirmed. Trips lived in one system and invoices in another, joined by a reference typed by hand. The engagement was commissioned after a month-end where reconciliation across two bank accounts took four days and still didn't balance.
Full-Stack Engineering
Lines point at components
The join between a trip and its invoice is a real reference, where it used to be a hand-typed code that drifted.
Each invoice line holds a reference to the trip component it bills (this flight, this transfer, this night), where the old system relied on a hand-typed code that drifted the moment someone abbreviated it. Because the join is a reference, a trip knows exactly what has and hasn't been invoiced, and a component can't be billed twice without the second attempt showing.
- Lines reference the component they bill, not a typed code
- A trip knows what has and hasn't been invoiced
- Double-billing a component shows up instead of slipping through
Every line on INV-4127 carries a pill naming the component it bills, and the customer PDF beside it is generated from those same six records, down to the $2,123.00 balance due.
September's receipts grouped by the account the money actually landed in, operating and client deposits, with two bank credits still waiting for an invoice to attach to.
Payments carry a bank
Each payment records where it landed, which is what makes a month-end match against two statements possible.
Every payment records the bank account it landed in, the field that makes a month-end match against two statements possible at all. Without it, reconciliation is guesswork across accounts; with it, the match is mechanical and the exceptions are a short list instead of the whole month.
- Bank account recorded on every payment
- Month-end match becomes mechanical, no guessing
- Exceptions are a short list, never the whole month
Status is derived
A $3,410.00 credit that arrived with nothing but a reference number, split across two invoices until none of it is left unallocated. Both invoices, and the trip, change status because the lines now say so.
A trip's payment state comes from its lines, so it can't be set to something the money doesn't support.
A trip's payment status is computed from its lines and their payments; there's no field for anyone to set. So it can't say paid when the money doesn't support it. That was the previous system's most expensive failure, because a trip marked paid stops being chased and the discrepancy only appears at year end.
- Status computed from lines and payments, never set
- Can't claim paid without the money behind it
- Removes the marked-paid-but-unpaid failure entirely
The morning report and landing screen: $59,691.50 invoiced, $30,255.00 paid, and the $29,436.50 between them, worked out from the lines rather than read off a stored status, with the chase list ordered by age and amount.
Unpaid is the landing screen
The default view answers the question the agency actually asks every morning.
The application opens on unpaid and overdue trips, because that's the question the agency asks every morning, and the previous landing screen answered a different one. Ordering is by age and amount, with the bank the payment is expected in shown alongside, so the screen itself is the morning's chasing list.
- Opens on unpaid and overdue, the actual morning question
- Ordered by age and amount, with expected bank shown
- The chasing list is the screen itself
One source for the PDF
The document a traveler receives is generated from the same records the ledger reads.
The PDF a traveler receives is generated from the same records the ledger reads, so the document and the accounts can't describe different amounts. Regenerating an invoice reflects any correction (there's no stored file to reprint), and each generated document records the version of the data it came from.
- Documents generated from the ledger's own records
- Regeneration reflects corrections; nothing is reprinted from a stored file
- Each PDF records the data version it came from
One customer's whole history as a running balance, ending at $2,123.00 after $3,500.00 received against the Marenca trip, and the statement the agency sends out of it.
Phase by phase
Phase 1: Trips And Components
A Trip Is Several Things
Modeled a trip as its components (flights, transfers, accommodation), which is what makes partial invoicing and partial payment describable.
- Trip Model
- Components
- Traveller Records
Phase 2: Invoicing
Lines That Point Somewhere
Built invoices whose lines reference components. A hand-typed reference was the old join and it broke constantly; a foreign key doesn't.
- Invoices
- Invoice Lines
- Component Linking
Phase 3: Payments And Banks
Where The Money Landed
Built payments against invoice lines with the receiving bank account recorded, because reconciling a month against two statements is impossible without knowing which one to look at.
- Payments
- Bank Accounts
- Allocation
Phase 4: The Unpaid View
The Question, As A Screen
Made outstanding balance the landing view, derived from lines instead of a stored status, so the list of who to chase is always current and never someone's recollection.
- Outstanding View
- Ageing
- Chase List
Phase 5: Documents
One Source, One PDF
Generated the customer-facing invoice from the same records the ledger reads, so a document sent to a traveler can't say something the system disagrees with.
- PDF Invoices
- Receipts
- Statements
The migration check: 40 trips whose hand-typed codes matched nothing, relinked to real components, and the 31 trips the old system had marked paid that the money did not support.
What it carries now
0
Hand-typed joins
All
Bank accounts reconciled
None
Stored payment statuses
Immediate
Time to list unpaid trips
Hand-typed joins is zero: lines reference components. Bank accounts reconciled is all of them, because every payment records where it landed. Stored payment statuses is none: status is derived. Time to list unpaid trips is immediate, because it's the default view and not a report to run.
Client name withheld under NDA. Figures are approximate, drawn from the engagement’s own reporting.
What the architecture settled
A hand-typed reference is a join that fails quietly and gets discovered at month end.
A typed reference is a join that fails quietly, and the failure only surfaces at month end, when it's expensive and cold.
Partial payment is the normal case in travel, so the schema has to describe it exactly.
Partial payment is the normal case in travel, not an edge, so a schema that approximates it will misrepresent most of the book most of the time.
Record which account received the money, or reconciliation becomes a manual search across statements.
Without the receiving account on the payment, reconciliation is a manual search across statements: exactly the four days the agency was spending.
Derive status from the records. A stored status is a second source of truth waiting to disagree.
A stored status is a second source of truth, and the direction it fails in (marked paid, actually unpaid) is the one that stops the chasing.
How the work was run
- 01
The agency's bookkeeper spent a morning walking us through a month-end close, and that's where the bank-account-per-payment requirement came from. Nobody had asked for it. It was simply impossible to do her job without it, and she'd been doing it by hand.
Ten weeks, fixed, with the invoice-line-to-component reference settled first because every other improvement depends on it. The unpaid view became the landing screen at the agency's request in week two: they pointed out that the previous system opened on a dashboard answering a question nobody in the office asked.
Nobody marks an invoice paid. The lines work it out.
A bank credit arrives with nothing but a reference number. Follow it as it's split across two invoices, spent to the last cent, and taken off the chase list, without a status being written anywhere. Switch tabs, or use the arrow keys once one is focused.
The deposits account shows a credit with a reference number and nothing else: no name, no invoice number. It's recorded against the account it landed in and sits, visible, in the unallocated list. Nobody guesses it onto a customer.
- CMP-9112Return flights · 2 adults1,000.00 / 1,980.00
- CMP-9113Hotel Alfama Pátio · 3 nights0.00 / 880.00
- CMP-9117Quinta das Rosas · 3 nights0.00 / 1,140.00
- CMP-9118Sintra palaces day tour · 20.00 / 410.00
- Adaeze Okonjo2,480.00
- Cal & Ruth Brennan3,915.50
- The Salgado family2,360.00
- Harriet Whitcombe1,860.00
- Dana & Miles Ferrante2,123.00
- Harriet Whitcombe1,550.00
- Mara Pryce-Hollis5,140.00
- Eszter Varga6,620.00
- Thandi Mbeki-Ross3,388.00
Stored payment statuses: none. Every status above is computed from invoice lines and allocations each time it is shown. Step timing in the split tab is illustrative.
From a booked component to a trip that knows whether it's paid
A Laravel application over MySQL where the invoice line is the join. Everything after it, from reconciliation to the chase list to the document a traveler receives, reads the same records instead of keeping a copy.
- 01 · SourceTrip componentsA trip is modeled as its flights, transfers and accommodation, so partial invoicing and partial payment can be described at all.
- 02 · InvoicingLines that reference componentsEach invoice line holds a foreign key to the component it bills, never a typed code, so billing a component twice is visible.
- 03 · PaymentsPayments with their bank accountEvery payment records the account that received it and is recorded against invoice lines, so the month-end match is mechanical.
- 04 · StateMySQL, no status columnPayment status is computed from lines and their payments. There's no field to set, so it can't say paid without the money.
- 05 · DeliveryUnpaid view and PDFsBlade renders the unpaid view as the landing screen; PDFs generate from the ledger's own records and note the data version they used.
Where the money could slip
Reconciliation that holds at month end
Money received can't go quietly unmatched
Every payment records the bank account it landed in, so each month is matched against the right statement mechanically, and whatever doesn't match is a short list of exceptions.
A paid customer drops off the chase list by itself
Status is computed from invoice lines and their payments, never stored. It can't say paid before the money supports it, so the list of who to chase is always current, never someone's recollection.
Margins stay tied to the components they came from
Each invoice line references the trip component it bills, so a trip knows what has been invoiced, a component billed twice is visible, and the PDF is generated from those same records.
Still working out which trips are unpaid from memory? Scope your build in three minutes.
Scope your buildNearby engagements
Data & AnalyticsDynamic pricing that reads demand in real time
A revenue-management engine that turns booking pace, seasonality, and local events into nightly rate recommendations across a multi-property portfolio.
Hotels & Accommodation · 12 weeks
Web PlatformsA booking platform tuned from search to checkout
A reservation platform where the search-to-checkout path is designed around real traveler behavior, then engineered to load fast and hold up under seasonal spikes.
Travel & Tourism · 16 weeks
Mobile AppsA trip assistant that still answers with roaming switched off
A companion app for a tour operator's guided trips, whose assistant answers questions about a traveler's own itinerary on-device. The moment people land abroad they turn data roaming off, and most apps go dark exactly when they're needed.
Tour Operators · 12 weeks
Let's talk
Running a large platform, shaping a first MVP, or getting a product ready for a funding round? Tell us where you are. We'll shape the process around it, and stay with you after launch.
