Manaz Afzal Book a consultation

Work snippet

Recurring maintenance contract billing from Airtable into Xero

Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.

A few hundred service contracts, some billed monthly, some quarterly, some once a year in advance. Contract terms live in an Airtable register; on the 25th Make drafts the following month’s Xero invoices, and anything out of the ordinary, such as a price review, a pause or an end date, is held for review first.

The problemXero repeating invoices drift as contracts change: missed price reviews, ended contracts still billing.

What changesOne contract register drafts every invoice due, and anything unusual waits for a person.

Area
Recurring billing
Tools
Airtable · Xero · Make · Slack
Typical scope
US$350-800
Timeline
About 5 weeks · fixed milestones

The situation and what was breaking

Xero’s repeating invoices coped until contracts started changing mid-term: price rises, paused sites and extra visits all needed hand edits that were easy to miss.

Each contract had been set up as a Xero repeating invoice. Price reviews were applied by hand, sometimes late, pauses were done by deleting the template, and contracts that ended kept billing until a customer called. Nobody could produce a list of active contracts and what they should bill next month.

The owner wanted the terms in one place and the invoices drafted from it, with a person approving anything that changed.

  • Price reviews applied months late
  • Ended contracts still billing
  • Paused contracts deleted and lost
  • No list of what should bill next month
  • Annual contracts missed when the template was edited

What good looks likeOne register of contracts, and next month's invoices drafted from it for me to approve.

Discovery findings

What a read-only review checks first: an export of the Xero repeating invoice templates, the contract PDFs on file, and 12 months of recurring invoices issued.

What a read-only review checks firstWeek 1
IDFindingEvidenceRisk if left
F1Templates with no matching contract27 templatesBilling with no agreement on file
F2Price reviews overdue41 contracts past their review dateRevenue left behind
F3Ended contracts still billing6 in 12 months, $8,920 creditedCredits and complaints
F4Paused by deleting the template11 contractsHistory and terms lost
F5Annual contracts billed on the wrong month4 casesCash timing and disputes
F6No owner for the monthly runRun by whoever noticedMissed months

Architecture: Xero recurring invoices

Terms live in Airtable. On the 25th, Make reads every contract due next month, drafts invoices in Xero, and holds the ones that need a person. Nothing is approved or sent automatically.

Scroll sideways to see the full drawing

Recurring billing architectureAn Airtable contract register feeds a monthly Make run that drafts Xero invoices, holds exceptions for review in Slack, and writes the invoice number back to the contract.020040060080due contractsdraftexceptionapprovesummaryinvoice no. backA · TERMSAirtable registerContracts, cycle,price, review dateB · 25TH 07:00Make runDue next month,rules appliedC · DRAFTSXero draftsOne per contractper periodD · HELDReview listReview, pause,end, newE · APPROVEOffice approvesThen Xero sendsF · NOTICESlack #billingRun summarythe run only drafts;a person approvesa contract with no end dateand no review date is heldBILLING RUN · 25TH MONTHLYManaz Afzal
Contract register to Xero drafts, with exceptions heldSolid: live data path · Dashed: exception path

The screens your team will use

The office maintains contracts in Airtable and approves drafts in Xero. The run summary arrives in Slack on the 25th.

Airtable · Contracts · Due next monthLive

Contract register

ContractCyclePriceNext billReviewStatus
MC-0412Monthly420.001 NovMarActive
MC-0418Quarterly1,260.001 NovJanActive
MC-0433Annual5,880.001 NovDue nowReview
MC-0447Monthly310.001 NovJunPaused
MC-0452Monthly890.001 NovFebEnds 30 Nov

Price, cycle, next bill date and review date are the only fields the run reads.

Slack · #billing25 Oct
MK
Billing run

November drafts are ready in Xero.

214 drafts · $96,714.00208 standard. 6 held: 3 price reviews due, 1 paused, 1 ending, 1 new without a start date.
Open held listOpen drafts in Xero

The run posts once. Held contracts have no draft until the office resolves them.

Make · Scenario · Monthly billing runLive
standardexceptionCLKSchedule25th 07:00ATSearch recordsnext bill in month220?RouterrulesXCreate draftXero invoice214ATUpdate recordinvoice no., next date!Holdreason set6#SummarySlack

The next bill date only moves forward after the Xero draft exists, so a failed run can be rerun safely.

How it is built

Register first, then migrate from the templates, then run in parallel for one month before the templates are switched off.

Module-level build6 modules
  1. Module ARegister design

    Contract, customer, cycle, price, start, end, review date, pause, Xero contact and item.

  2. Module BMigration

    412 templates matched to contract PDFs; 27 with no contract listed for a decision.

  3. Module CRun rules

    Standard, review due, paused, ending, new, and annual in the wrong month each get a rule.

  4. Module DDraft creation

    One draft per contract per period, with the contract id in Reference.

  5. Module EIdempotent rerun

    The next bill date moves only after the draft exists, so reruns cannot double-bill.

  6. Module FParallel month

    Drafts compared to the old templates for one month, then templates switched off.

Field mapping and rules

The register is small on purpose. Every field it has is used by a rule.

Register to Xero
AirtableXeroRule
Contract idReferenceRequired, used for reruns
CustomerContactLinked by Xero contact id
PriceLine amountReviewed price once approved
CyclePeriod on line descriptionMonthly, quarterly, annual
Next bill dateInvoice dateMoves forward after draft
Item codeItemFixed list of service items
Run rules
CaseResult
StandardDraft created
Review date passedHeld until the new price is approved
PausedNo draft; pause end date checked
End date in the periodFinal draft prorated, then contract closed
New, no start dateHeld
Annual, month differs from startHeld

Worked figures

Walkthrough arithmetic: the first run from the register, for one month.

November runWalkthrough figures
Active contracts380after migration
Due in November220all cycles
Drafts created214$96,714.00
Held6with reasons
Templates switched off412after parallel month
Reviews applied41approved prices

November drafts by cycle

Monthly168
Quarterly39
Annual7
Held6

QA and testing checklist

Every check runs in a copy or on test records first, and each one is ticked with evidence before go-live.

  • T1StandardA monthly contract creates one draft at the register price.
  • T2ReviewA passed review date holds the contract.
  • T3PauseA paused contract creates no draft.
  • T4EndAn ending contract bills prorated, then closes.
  • T5Annual monthAn annual contract drafts only in its start month.
  • T6RerunRunning twice in a month creates no second draft.
  • T7ParallelNovember drafts equal the old templates, apart from listed changes.
  • T8Nothing sentNo draft is approved or emailed by the run.

Scope

AreaIncluded
ReviewTemplates, contract PDFs and 12 months of recurring invoices, with findings
RegisterAirtable base with views for due, held and reviews
MigrationTemplates matched to contracts, gaps listed
RunMake scenario on the 25th, drafts only, rules and holds
Parallel monthCompared with templates, then templates off
Not includedContract wording, pricing decisions, or Airtable plan costs

Milestones, week by week

  1. Week 1

    Review and design

    • Template export
    • Register design signed
    20% of the fee
  2. Week 2

    Migration

    • 380 contracts in Airtable
    • Gaps listed
    25% of the fee
  3. Week 3

    Run built

    • Rules and holds on a copy
    • Rerun tested
    25% of the fee
  4. Week 4

    Parallel month

    • Drafts against templates
    • Differences explained
    20% of the fee
  5. Week 5

    Handover

    • Templates off
    • SOP
    • 14 days of fixes
    10% of the fee

Handover and keeping it running

Handover pack

  • Billing SOP the 25th to the 1st, step by step
  • Register guide adding, pausing, ending and reviewing a contract
  • Run rules what holds a contract and why
  • Recorded walkthrough register, scenario and Xero drafts
  • Ownership list Airtable, Make and Xero in the business's name
  • 14 days of fixes for defects in the agreed scope

Keep it running

The run is only as good as the register. A monthly review view lists contracts with a review date in the next 60 days, so price rises are decided before the run, not after.

  • 25th: run summary checked, held list cleared by the 28th
  • Monthly: review view for the next 60 days
  • Quarterly: contracts with no review date listed
  • Optional support hours at US$35 per hour, agreed in writing

Typical scope for this kind of project: US$350-800

About 5 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.

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.

Related work snippets

Next step

Bring me the work that repeats.

I scope it in writing, test it against your current process on your own data and hand over everything I build.

Book a consultation