Work snippet
Airtable purchase orders into Xero, with approval levels and an audit trail
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
A residential builder running about a dozen sites. Orders are raised in Airtable against the job’s cost-code budget, approved in value bands, and only then sent to Xero; each supplier bill is matched to its order and delivery, and anything outside tolerance is held with the reason.
The problemPurchase orders live in email and bills arrive with nothing to match them against.
What changesEach order is approved by value before Xero sees it, and every bill is matched to its order.
- Area
- Purchasing and bill matching
- Tools
- Airtable · Xero · Make
- Typical scope
- US$3,000-5,000
- Timeline
- About 6 weeks · fixed milestones
The problem
Materials are ordered by phone or text before anyone signs off, bills turn up with no order to check them against, and when a price goes up nobody can show who agreed to it.
- Spend is visible only when the supplier bill arrives
- A site can go over its cost-code budget with no warning
- The same bill can be entered twice
- Approvals live in personal inboxes and cannot be audited
What discovery would examine
A review of purchase orders, site budgets and supplier bills for a builder with a dozen live sites, plus the current path from a verbal order to a Xero bill.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | Orders are placed by message or phone before anyone approves them | A week of supplier invoices with no purchase order number | Spend is visible only when the bill arrives |
| F2 | There is no record of who approved a value rise | Emails in personal inboxes, some deleted | A disputed bill cannot be traced |
| F3 | Bills are entered in Xero without a match to an order or a delivery | Xero bills with a blank reference | Over-delivery and double bills are easy to miss |
| F4 | A site can exceed its cost-code budget with no warning | Two sites over budget mid-month, noticed at month-end | The overspend is already committed |
| F5 | The same supplier bill was entered twice in one quarter | Two Xero bills, one supplier invoice number | The second was paid |
Approach
- Write the bands and the match rule
Value levels, deputies, and the tolerance for a bill versus the order and the delivery.
- Build the PO and the budget check
Every line has a site and a cost code, and a remaining budget figure.
- Post only after approval
Make sends the approved order to Xero, with an idempotency key.
- Match bills or hold them
A nightly audit log is exported, and the hold queue has an owner.
Current state and future state
Current state
Future state
Future-state modules
How it is built
- Module APO form and budget
A purchase order is raised in Airtable against a site and a cost code, and checked against the remaining budget.
- Module BApproval levels
Value bands route the order to the right people, in order. A value rise sends it back.
- Module CPost to Xero
Only an approved order is sent. The Airtable record id is the idempotency key.
- Module DBill match
A supplier bill is matched to the order and the delivery within a set tolerance, or held with a reason.
- Module EAudit log
Every approval, rejection, value change and posting is appended and exported nightly.
Field mapping
| Airtable field | Xero field | Rule | Idempotency |
|---|---|---|---|
| PO number, record id | PO reference / tracking | Created only after final approval | Airtable record id |
| Supplier | Contact | Must already exist; a new supplier is parked for a person | Supplier id |
| Lines: item, qty, unit cost, tax | PO and bill lines | Quantities and tax codes must match the order | Line id |
| Site and cost code | Tracking / account | Written on every line | N/A |
| Approved total | PO total | A later rise must go through approval again | Revision number |
| Delivery qty | Bill qty tolerated | Within the written tolerance, else hold | Bill number + PO number |
Worked exhibits
| Band | Approver | Second approver | Value rise |
|---|---|---|---|
| Up to $1,000 | Site lead | None | Back to the site lead |
| $1,000 to $5,000 | Site lead, then project lead | Project lead | Back to both, in order |
| Above $5,000 | Project lead, then director | Director | Back to both, in order |
| New supplier, any value | Director only | N/A | Director again |
Exceptions and runbook
| Case | Rule | Who acts | First action |
|---|---|---|---|
| Bill above tolerance | Hold, do not post as a draft for payment | Project lead | Revise the PO, or reject the extra and request a credit |
| Bill arrives before the delivery | Hold until the delivery is recorded, or until a written exception | Site lead | Confirm whether the goods are on site |
| Split delivery | Match each delivery to the remaining quantity | Flow, then site lead if a remainder sits open | The PO stays open until the last delivery or a close |
| Approver on leave | The written deputy for that band is used; the log still names both | Operations admin | The deputy is listed before the absence |
| Bill with no order | Hold in the no-PO queue, never auto-posted | Director | Raise a retrospective PO, or reject the bill |
How to read the exhibits
- The monthly counts walk through purchase orders, bills and holds.
- The held bill shows the match rule at work: a price rise does not become payable on its own.
- The audit log is append-only, so a later change does not erase who approved the first version.
Test plan
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Under the first band | Raise a $400 PO | Site lead only, then a Xero PO | It is sent before approval |
| T2 | Value rise | Raise $800, then change to $1,400 | Returns to the chain for the new band | The original approval still stands |
| T3 | Replay | Send the approved record twice | One Xero PO | A second PO |
| T4 | Tolerance | A bill 2% over, inside the written tolerance | Matched and drafted | Held, or posted with no note |
| T5 | Above tolerance | A bill 12% over | Held with a reason, not payable | Posted for payment |
| T6 | Audit log | Approve, reject, then change a value | Each step is in the nightly export, in order | A missing or overwritten row |
Scope
| Area | Included |
|---|---|
| Discovery | Current order path, site budgets and unmatched bills, with written findings |
| Airtable PO and budgets | Form, cost codes, remaining budget and revision history |
| Approval chain | Value bands, deputies and the value-rise rule |
| Xero posting | Approved orders only, with idempotent keys |
| Bill match | Tolerance, hold queue and reasons |
| Audit log | Nightly append-only export |
| Handover and support | SOPs, walkthrough, 14 days of fixes, optional monthly hour block |
| Not included | Negotiating supplier prices, or a full inventory system |
Rollout, handover and monthly support
| Milestone | Deliverable | Timing | Share of fee |
|---|---|---|---|
| M1 Discovery and rules | Findings, bands, tolerance and the account map | Week 1 | 20% |
| M2 PO, approvals and tests | Live in a copy, scripted cases passed | Weeks 2 to 4 | 40% |
| M3 Bill match and audit log | Hold queue and nightly export live | Week 5 | 25% |
| M4 Handover | SOPs, deputies list, 14 days of fixes | Week 6 | 15% |
Handover
- Written operating procedures for the happy path and each exception in the runbook
- A recorded walkthrough of the architecture, the queue and how to replay an item
- A credentials and ownership list, with every connection in your name
- Fourteen days of fixes after go-live, for defects in the agreed scope
Monthly support
A set block of hours each month, at US$35 per hour, agreed in writing before it starts. Typical use of the block: the hold queue, deputy cover, and any change to approval bands or match tolerance
- Review of the exception queue and the weekly digest
- Small mapping or rule changes, agreed before they are made
- A short written note on what was done and what is still open
Typical scope for this kind of project: US$3,000-5,000
About 6 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing, to watch the flows, handle changes and work the exception queue.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.