Xero and CRM Invoice Integration

Snapshot date: 2026-05-28. Updated: 2026-07-03 for yearly seat and prorate invoice-line behavior.

This document describes the current Xero custom-connection implementation and the MSP invoice automation path. The current production target is to generate monthly MSP invoices from CRM MSP_Invoice_Lines, create the CRM invoice record, create a Xero draft invoice, and keep the two records linked.

Scope

Implemented:

• Xero Accounting API client using a Custom Connection.
• Zoho CRM invoice creation from recurring MSP_Invoice_Lines.
• Xero draft invoice creation through the Flask/Deluge apply path.
• Flask routes for Zoho CRM buttons to generate one plan or all plans.
• Draft refresh for previously generated CRM/Xero invoices while Xero is still DRAFT.
• Middleware reconciliation routes for CRM invoice-line and plan-level workflows.
• CRM update with Xero_Invoice_ID, Xero_Invoice_Number, and Xero_Sync_Status.
• Preview reports before write operations.
• Read-only audit reports comparing generated CRM previews to referenced Xero invoice lines.
• Duplicate guard using MSP_Invoice_Key.
• Recurring annual seat renewal and one-off seat prorate lines represented in MSP_Invoice_Lines.
• Preview-only CLI for report generation. Live CRM/Xero writes use the same Flask routes that Zoho Deluge calls.

Not implemented yet:

• Scheduled monthly batch generation or an MSP Billing Runs module.
• Approval or sending of Xero invoices.
• Bi-directional state sync after the initial draft create.
• Dynamic device counts.

Code Map

• xero_sync/auth.py Loads Xero settings, defaults to Custom Connection auth, fetches client_credentials tokens, and writes .xero_token_cache.json.

• xero_sync/client.py Thin Accounting API client for invoices and connection discovery. In Custom Connection mode it omits Xero-Tenant-Id; Xero binds the token to the single organisation behind the custom connection.

• xero_sync/invoice_sync.py Reconciliation helpers for matching existing Zoho CRM invoices to Xero invoices. This is separate from the MSP invoice generator.

• xero_sync/cli.py Operational CLI for Xero checks and invoice reconciliation experiments.

• msp_imports/msp_invoices/ MSP invoice generator package. It reads CRM Managed Services Plans, MSP Seats, MSP Invoice Lines, Accounts, Products, and existing Invoices. It produces preview rows for the CLI and live CRM/Xero writes for the Flask/Deluge apply path. msp_imports/generate_msp_invoices.py remains the preview CLI shim for python -m msp_imports.generate_msp_invoices.

• msp_imports/audit_msp_xero_invoices.py Read-only reference audit command. It reads an exported MSP Plans CSV, follows the stored Xero invoice reference, classifies invoice lines, and writes CSV and Markdown audit reports.

• app/blueprints/msp_invoices.py Token-protected Flask endpoints used by Zoho CRM custom buttons and invoice-line workflows.

• msp_imports/msp_invoices/reconcile.py Idempotent reconciliation for MSP_Invoice_Lines: date state transitions, Xero account-code calculation, and plan-level account propagation.

• zoho_crm/deluge/standalone.generate_msp_invoice_for_plan.dg Reference Deluge function that calls the one-plan Flask route.

• zoho_crm/deluge/standalone.generate_monthly_msp_invoices.dg Reference Deluge function that calls the all-plans Flask route.

• zoho_crm/deluge/button.call_generate_msp_invoice_for_plan.dg Thin call function for the plan-level CRM button.

• zoho_crm/deluge/button.call_generate_monthly_msp_invoices.dg Thin call function for the list-level CRM button.

• zoho_crm/deluge/standalone.reconcile_msp_invoice_line.dg Reference Deluge function that calls the invoice-line reconcile route.

• zoho_crm/deluge/standalone.reconcile_msp_plan_invoice_lines.dg Reference Deluge function that calls the plan invoice-line reconcile route.

• zoho_crm/deluge/automation.call_reconcile_msp_invoice_line.dg Thin CRM workflow wrapper for an MSP_Invoice_Lines create/edit event.

• zoho_crm/deluge/automation.call_reconcile_msp_plan_invoice_lines.dg Legacy thin CRM workflow wrapper for the plan invoice-line reconcile route. The route is retained for compatibility and does not apply billing rules from Plan_Type.

Authentication

The Xero integration uses a Custom Connection. XERO_AUTH_MODE is optional and defaults to custom_connection; do not prefix commands with env XERO_AUTH_MODE=custom_connection.

Required environment variables:

XERO_CLIENT_ID=...
XERO_CLIENT_SECRET=...

Optional environment variables:

XERO_AUTH_MODE=custom_connection
XERO_SCOPES=accounting.invoices

Defaults:

• Auth mode: custom_connection
• Scope: accounting.invoices
• Xero account code: from MSP_Invoice_Lines.Xero_Account_Code_Override when filled, otherwise from Product Xero_Account_Code
• Xero tax type: OUTPUT
• Xero item code: 003 / Consulting

The token cache is local runtime state and must not be committed:

.xero_token_cache.json

CRM Setup

Account Fields

Accounts must have:

• Xero_Contact_ID

The generator uses this ID as the Xero invoice contact:

{"Contact": {"ContactID": "<Xero_Contact_ID>"}}

Invoice Fields

Invoices must have:

• MSP_Plan lookup to Managed_Services_Plans
• MSP_Billing_Period_Start date
• MSP_Billing_Period_End date
• MSP_Invoice_Key single line
• Xero_Invoice_ID single line
• Xero_Invoice_Number single line
• Xero_Sync_Status picklist or single line
• update_source single line

The current source marker is:

msp_invoice_automation

The invoice key format is:

<managed_services_plan_id>|YYYY-MM

Example:

42659000014776835|2026-05

Product Setup

The generator expects these Zoho CRM Products:

Product Code	Purpose	Default_Invoice_Label example
MSP-SEAT-SB / MSP-SEAT-L1 / MSP-SEAT-L2 / MSP-SEAT-L3	Package-specific monthly seat fee	Solopreneur Blend
MSP-SEAT-ANNUAL-SB / MSP-SEAT-ANNUAL-L1 / MSP-SEAT-ANNUAL-L2 / MSP-SEAT-ANNUAL-L3	Package-specific yearly seat fee; quantity comes from MSP Seats and the line recurs yearly from its Start_Date renewal anchor	Annual Premium Security
MSP-PRORATE-SEAT	One-off prorated annual seat addition; quantity stays on the invoice line	Prorated annual seat charge
MSP-BASE	Base monthly infrastructure fee	Base Infrastructure
MSP-DEVICE	Extra device fee	Extra Device
MSP-DISCOUNT	Regular MSP discount	Discount
MSP-NFP-DISCOUNT	NFP discount	NFP Discount
MSP-ADJUSTMENT	Recurring MSP adjustment foundation	MSP Adjustment

These products are used to satisfy Zoho CRM inventory-module requirements and to make invoice lines readable in CRM. They do not need to be synced as Xero Items. The Xero invoice lines are sent with descriptions, quantities, unit amounts, tax type, account code, and the default ItemCode 003 / Consulting.

Invoice description labels come from CRM invoice-line records:

• MSP_Invoice_Lines.Description_Override, when filled.
• Products.Default_Invoice_Label, when the line override is blank.
• Products.Product_Name, as the final fallback.

Unit prices come from MSP_Invoice_Lines.Unit_Price_Override when filled, otherwise from Product Unit_Price. Xero revenue account codes come from MSP_Invoice_Lines.Xero_Account_Code_Override when filled, otherwise from Product Xero_Account_Code. Each generated line must have a linked Product. Missing quantity or resolved account code values are reported as warnings and passed through as blank values so the draft can still be generated for finance review. A missing resolved unit price is reported as a warning and sent as 0.00 so Xero cannot fill an item default price.

Managed Services Plan Plan_Type is metadata only. Operators may add or edit plan-type labels without changing invoice pricing, Product selection, seat quantity, or Xero account-code rules.

Data Mapping

CRM Preview Inputs

Managed Services Plan fields:

• id
• Name
• Account
• Deal
• Plan_Status
• Plan_Type metadata
• Billing_Start
• Billing_End
• Include_Users_Invoice
• May_2026_Invoice_Ref

MSP Invoice Line fields:

• id
• Name
• Plan
• Start_Date
• End_Date
• Sort_Order
• Product
• Quantity
• Unit_Price_Override
• Description_Override
• Xero_Account_Code_Override

MSP Seat fields:

• id
• Plan
• Billing_Status
• Billing_Start
• Billing_End

Account fields:

• id
• Account_Name
• Xero_Contact_ID

Product fields:

• id
• Product_Name
• Product_Code
• Default_Invoice_Label
• Unit_Price
• Xero_Account_Code

Existing invoice fields:

• id
• Invoice_Number
• Subject
• Status
• MSP_Invoice_Key
• Xero_Invoice_ID
• Xero_Invoice_Number
• Xero_Sync_Status

Invoice Lines

Current invoices generate one line for each applicable MSP_Invoice_Lines record. Non-annual lines are applicable when their Start_Date / End_Date range overlaps the invoice month. Annual seat lines are applicable only in the first renewal invoice month and each 12-month anniversary after that. Internal line behavior is derived from the linked Product code; MSP_Invoice_Lines no longer needs a Line_Type field. Plan type is not used to classify invoice lines. The MSP invoice path is for recurring managed-service items. One-off onboarding, project, or deal invoices should be generated outside this path through Deal-led invoicing.

Managed Services Plan Plan_Status does not gate recurring invoice generation. The plan's Billing_Start / Billing_End range gates whether the plan is in scope for a month. Plans missing Billing_Start need review before generation.

Seat count is based on MSP_Seats records whose Billing_Start and Billing_End range overlaps the target month. Billing_Status does not drive invoicing at this stage. Seats missing Billing_Start are excluded and reported as a review issue.

Seat-line quantity is calculated from MSP Seats when the linked Product code starts with MSP-SEAT-. The current monthly seat Products are MSP-SEAT-SB, MSP-SEAT-L1, MSP-SEAT-L2, and MSP-SEAT-L3.

Annual seat Products are the exact recurring annual set MSP-SEAT-ANNUAL-SB, MSP-SEAT-ANNUAL-L1, MSP-SEAT-ANNUAL-L2, and MSP-SEAT-ANNUAL-L3. For those Products, Start_Date is the renewal-month anchor and must be the first day of that invoice month. With a blank End_Date, the line appears in the first renewal invoice month and then every 12 months. If the line should stop, set End_Date before the next unwanted renewal month. The generated quantity comes from billing-eligible MSP Seats in the renewal invoice month, and the generator appends the covered annual period to the line description.

One-off annual seat prorates use MSP-PRORATE-SEAT. That Product code does not start with MSP-SEAT-, so the generated quantity comes from MSP_Invoice_Lines.Quantity, normally 1, and the prorate amount comes from Unit_Price_Override.

One-off annual seat prorate lines should be bounded to one invoice month by setting both Start_Date and End_Date inside that month. The generated monthly invoice is still keyed by plan and month; the prorate line description should state the covered annual period.

Line descriptions are not month-suffixed automatically. The invoice subject and billing period fields carry the month:

Base Infrastructure Charge
Premium Monthly Support
Additional Device:
1. Training laptop

Billing-eligible seat names are listed on the seat line description only when Managed_Services_Plans.Include_Users_Invoice is true. Blank or false means the invoice shows only the quantity, not the user list. When user names are enabled, the generator uses the MSP_Seats.Contact display name first. If a legacy seat does not have a Contact lookup value in the fetched data, it falls back to the MSP Seat Name with the plan suffix stripped.

If an imported seat invoice-line description already contains a trailing numbered staff list, the generator removes that historical list before appending the current billing-eligible seat list. The preview report records this as a non-blocking warning so staff can check the CRM seat records.

The preview report also compares each seat line's imported invoice-line Quantity with the current billing-eligible seat count, even when user names are not listed on the invoice. Differences are recorded in seat_quantity_gap for audit visibility, but they do not appear as generation warnings.

Device details should be stored directly in the device invoice line's Description_Override when they need to appear on the invoice.

Zoho CRM Invoice Payload

The generator creates CRM invoices with:

• Subject: <Account Name> - MSP - <Month YYYY>
• Account_Name: CRM Account lookup
• Invoice_Date: first day of billing month
• Due_Date: last day of billing month
• Status: Created
• MSP_Plan: Managed Services Plan lookup
• MSP_Billing_Period_Start: first day of billing month
• MSP_Billing_Period_End: last day of billing month
• MSP_Invoice_Key: duplicate guard key
• update_source: msp_invoice_automation
• Invoiced_Items: CRM product line items

Zoho CRM inventory taxes require both the invoice-level $line_tax declaration and each line's Line_Tax. The generator sends Sales Tax at 10% on both levels so CRM totals match Xero GST totals.

Xero Invoice Payload

The generator creates Xero invoices with:

• Type: ACCREC
• Status: DRAFT
• Contact.ContactID: Account Xero_Contact_ID
• Date: first day of billing month
• DueDate: last day of billing month
• LineAmountTypes: Exclusive
• Reference: Peppermint iT
• LineItems: generated MSP base, seat, prorate, device, license, subscription, discount, and adjustment lines

Each Xero line gets:

• Description
• Quantity
• UnitAmount
• AccountCode: from MSP_Invoice_Lines.Xero_Account_Code_Override when filled, otherwise from Product Xero_Account_Code
• TaxType: OUTPUT by default
• ItemCode: 003 / Consulting by default

The Xero idempotency key is:

msp-invoice-<managed_services_plan_id>-YYYY-MM

Operational Commands

Run commands from the repo root.

Default Billing Month

HTTP/Deluge-backed generation defaults the billing month in Australia/Sydney time:

• days 1-20: current calendar month
• days 21+: next calendar month

Examples:

• 2026-06-20 -> 2026-06
• 2026-06-21 -> 2026-07
• 2026-12-21 -> 2027-01

The preview CLI defaults to the current calendar month if --month is omitted. Live HTTP calls can omit billing_month to use the billing-cycle rule above.

Preview One Plan

python -m msp_imports.generate_msp_invoices \
  --plan-id 42659000014776835 \
  --month 2026-05

This writes a CSV report under msp_imports/reports/ and does not mutate CRM or Xero.

Audit Existing Xero References

Use this when reviewing whether current Managed Services Plan fields can regenerate existing invoices closely. The command is read-only against CRM and Xero. It writes CSV and Markdown reports and does not create or update invoices.

python -m msp_imports.audit_msp_xero_invoices \
  --plans-csv "/Users/felix/Downloads/MSP Plans for Invoice (1).csv" \
  --month 2026-05

To inspect only the Xero references without fetching CRM-generated previews:

python -m msp_imports.audit_msp_xero_invoices \
  --plans-csv "/Users/felix/Downloads/MSP Plans for Invoice (1).csv" \
  --month 2026-05 \
  --skip-crm-preview

The audit classifies referenced Xero lines as base, seats, devices, discounts, adjustments, or license. It reports:

• generated MSP subtotal from CRM previews when available
• Xero MSP subtotal from classified recurring MSP, license, subscription, discount, and adjustment lines
• preview issues from the invoice-line generator
• recommendations for recurring discount or adjustment invoice-line records

--create-drafts exists as a future-safe flag, but currently fails closed with a clear error. Draft license invoice creation must wait until license products, account-code rules, and invoice grouping rules are configured.

Live Local Test

Live CRM/Xero writes use the same HTTP routes as Zoho Deluge. The local server defaults to port 5050.

For a local one-plan test with an explicit billing month:

curl -sS -X POST "http://localhost:5050/zoho/msp-invoices/generate-plan" \
  -H "Authorization: Bearer $INBOUND_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"plan_id":"42659000014776835","billing_month":"2026-06"}' | jq

For a local one-plan test using the default billing month:

curl -sS -X POST "http://localhost:5050/zoho/msp-invoices/generate-plan" \
  -H "Authorization: Bearer $INBOUND_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"plan_id":"42659000014776835"}' | jq

For a local all-plans test with an explicit billing month:

curl -sS -X POST "http://localhost:5050/zoho/msp-invoices/generate-all" \
  -H "Authorization: Bearer $INBOUND_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"billing_month":"2026-06"}' | jq

For a local all-plans test using the default billing month:

curl -sS -X POST "http://localhost:5050/zoho/msp-invoices/generate-all" \
  -H "Authorization: Bearer $INBOUND_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{}' | jq

The HTTP route is the only live apply path. It creates missing CRM/Xero draft invoices, refreshes existing generated invoices while the linked Xero invoice is still DRAFT, and returns locked once Xero is no longer editable.

Flask Routes for CRM Buttons

Both routes use the normal inbound bearer token protection:

Authorization: Bearer <INBOUND_API_TOKEN>

Zoho Deluge should not calculate the billing month. If the request omits billing_month, Flask applies the default cycle rule above.

Generate or Refresh One Plan

POST /zoho/msp-invoices/generate-plan

Payload:

{
  "plan_id": "42659000014776835",
  "billing_month": "2026-06"
}

billing_month is optional.

Reconcile One Invoice Line

POST /zoho/msp-invoices/reconcile-line

Payload:

{
  "invoice_line_id": "42659000015269037"
}

This endpoint is called by the MSP_Invoice_Lines create/edit workflow. It is currently a compatibility no-op for date, price, and Xero account-code fields so operator-entered values stay unchanged and blank overrides can continue to use Product defaults.

Reconcile Plan Invoice Lines

POST /zoho/msp-invoices/reconcile-plan-lines

Payload:

{
  "plan_id": "42659000014776835"
}

This compatibility endpoint no longer updates invoice-line account-code overrides and does not read plan type values. Blank override values use Product defaults during invoice generation.

Generate or Refresh All Plans

POST /zoho/msp-invoices/generate-all

Payload:

{
  "billing_month": "2026-06"
}

billing_month is optional.

Response shape:

{
  "billing_month": "2026-06",
  "mode": "apply",
  "summary": {
    "created": 1,
    "updated": 0,
    "locked": 0,
    "skipped": 0,
    "needs_review": 0,
    "failed": 0
  },
  "results": [
    {
      "plan_id": "42659000014776835",
      "invoice_key": "42659000014776835|2026-06",
      "action": "created",
      "crm_invoice_id": "42659000014966008",
      "xero_invoice_id": "3f48f675-b4d7-4086-8d98-4e1bac588909",
      "xero_status": "DRAFT",
      "message": "Created CRM invoice and Xero draft."
    }
  ]
}

Actions:

• created: no existing CRM invoice existed; CRM invoice and Xero draft were created and linked.
• updated: existing generated invoice was refreshed because the linked Xero invoice is still DRAFT.
• locked: existing linked Xero invoice is not DRAFT, so neither CRM nor Xero was changed.
• skipped: reserved response bucket; current generation rules do not skip plans through a plan-level manual-generation flag.
• needs_review: structural setup needs manual review before any live write.
• failed: unexpected fetch or write failure.

Zoho CRM Buttons

Create two CRM custom buttons that call the button.call_* Deluge functions:

Button	Module	Placement	Deluge function
Generate / Refresh MSP Invoice	Managed_Services_Plans	record detail page	button.call_generate_msp_invoice_for_plan
Generate Monthly MSP Invoices	Managed_Services_Plans	list utility menu	button.call_generate_monthly_msp_invoices

The standalone.* functions read these CRM Variables by API name:

• MIDDLEWARE_BASE_URL
• MIDDLEWARE_API_TOKEN

The button.call_* functions are the CRM button entrypoints and only delegate to the standalone functions. The functions intentionally send only the plan ID or an empty payload plus a message-response flag. Flask owns the billing cycle default. The CRM popup displays the plain-text message response; structured JSON remains available when the routes are called without that flag.

Create two CRM workflow function wrappers:

Workflow	Module	Deluge function
Reconcile MSP invoice line	MSP_Invoice_Lines	automation.call_reconcile_msp_invoice_line
Reconcile MSP plan invoice lines	Managed_Services_Plans	automation.call_reconcile_msp_plan_invoice_lines

These wrappers delegate to standalone.reconcile_msp_invoice_line and standalone.reconcile_msp_plan_invoice_lines, which call the middleware.

Validation and Duplicate Guards

The generator refuses live writes when structural preview issues remain. Warnings are reported in the response message and preview report but do not block create or refresh. CRM button popups show only the warning text when warnings exist.

Common needs_review causes:

• Account is missing Xero_Contact_ID
• Plan is missing Billing_Start
• No applicable MSP_Invoice_Lines records exist for the month
• An applicable invoice line is missing Product or references a Product that was not found

Common warning causes:

• MSP seats missing Billing_Start were excluded
• MSP invoice lines missing Start_Date were excluded
• An applicable invoice line is missing Quantity, a resolved Unit Price, or a resolved Xero account code

Invoice generation does not use a plan-level manual-generation flag. A plan can still generate a monthly invoice when it has applicable, in-range invoice lines, even if other one-off or pre-billed lines are out of range.

Preview report warnings are non-blocking. They call out cleanup gaps such as blank pricing/account-code setup and imported seat descriptions that contained a historical numbered staff list which was replaced with the current billing-eligible MSP seat list.

The Flask flow treats a skip_existing preview as a refresh request:

• If the CRM invoice has a linked Xero invoice and Xero is DRAFT, it rebuilds the generated invoice lines from current CRM data and updates both records.
• If Xero is no longer DRAFT, it returns locked and does not mutate CRM or Xero.
• If the existing CRM invoice line product-id order still matches the generated invoice-line preview, CRM line item IDs are preserved and updated in place.
• If the existing CRM invoice line product-id order has changed, the refresh deletes the old generated CRM line items and adds the regenerated line items. This supports the normal draft workflow: generate, review, edit MSP_Invoice_Lines, and generate again until the linked Xero invoice moves past DRAFT.
• If the existing CRM invoice has no Xero ID, it creates and links a Xero draft only when the CRM invoice is still in the generated editable state.

Verification Commands

Run focused tests:

python -m pytest \
  tests/test_generate_msp_invoices.py \
  tests/test_msp_invoice_preview.py \
  tests/test_xero_reference_audit.py \
  tests/test_xero_client.py \
  -q

Run all tests:

python -m pytest -q

Check the effective Xero auth mode and scopes:

python -c "from xero_sync.auth import load_config; c=load_config(); print(c.auth_mode); print(' '.join(c.scopes))"

Expected output:

custom_connection
accounting.invoices

Historical Live Smoke Test

The first live Sharps test used Managed Services Plan 42659000014776835 for May 2026.

Created records:

• Xero draft: INV-0136
• Xero Invoice ID: 3f48f675-b4d7-4086-8d98-4e1bac588909
• CRM Invoice ID: 42659000014966008
• CRM Invoice Number: 42659000014966011

Verified totals:

• Base fee: 1 x 500
• Seats: 27 x 219
• Subtotal: 6413
• GST: 641.30
• Total: 7054.30

Design Notes

• CRM remains the operational source of truth for MSP plans, seat counts, and generated invoice records.
• Xero remains the accounting system of record. The automation creates Xero invoices in DRAFT so finance can review before approval or sending.
• Zoho Billing is intentionally not in the path.
• Products are standard CRM invoice products, not per-client products.
• Contract pricing, device lines, discounts, adjustments, licenses, and subscriptions come from MSP_Invoice_Lines.
• Seat quantity remains dynamic from MSP_Seats whose Billing_Start and Billing_End range applies to the invoice month. Billing_Status does not drive invoicing at this stage; the invoice-line Quantity value on seat lines is import/reference data only.
• Licenses, voice, and other subscriptions remain on the main MSP invoice.

External References

• Xero OAuth 2.0 overview: https://developer.xero.com/documentation/guides/oauth2/overview
• Xero Custom Connections: https://developer.xero.com/documentation/guides/oauth2/custom-connections/
• Xero Accounting API Invoices: https://developer.xero.com/documentation/api/accounting/invoices
• Xero invoice creation best practices: https://developer.xero.com/documentation/best-practices/data-integrity/creating-invoices
• Zoho CRM API v8 Insert Records: https://www.zoho.com/crm/developer/docs/api/v8/insert-records.html