> For the complete documentation index, see [llms.txt](https://docs.kula.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kula.digital/whats-in-your-data/xero.md). # Xero — ontology map **Source id:** `com.xero` (one id for both feeds — see below) · **Role:** general ledger / accounting — a **satellite** source, not a system of record for the studio floor · **Access:** Xero Custom Connection (OAuth2 client-credentials, studio's own Xero subscription pays for it), AU/NZ/UK/US only, **plus** an optional GL CSV upload for studios without one Xero knows the money side of the business as Xero itself understands it — accounts, invoices, bills, payments, bank transactions — and, for studios that upload a GL export, the *actual* posted ledger including payroll and depreciation. It knows nothing about classes, attendance, instructors, or members as people; a Xero-only studio has a GL and no operations, and its contacts are counterparties (customers, suppliers, contractor companies), not `people.member` rows. The single most important thing to understand about Xero here is the **two-feed model**. ## The two feeds: derived vs gl\_import | Feed | Where it comes from | `origin` | What it is | | -------------- | ----------------------------------------------------------------------------------------------------------------------------- | ----------- | -------------------------------------------------------------------------------------------------------------- | | **derived** | The live Custom Connection, synthesised from source documents (invoices, bills, payments, bank transactions, manual journals) | `derived` | P\&L lines only — a live, incremental, API-shaped view of revenue and expense | | **gl\_import** | An operator-uploaded Xero Journal Report / Account Transactions / Trial Balance CSV | `gl_import` | The full posted GL for the uploaded period — including payroll and depreciation journals the API can never see | Both feeds write to the **same** `source = 'com.xero'` — there is no `com.xero.gl` split. The discriminator is the `origin` column on `accounting.entry` (and, for `accounting.journal`, whether `source_external_id` carries the `glimport:` prefix — see Counting traps). **Never read the base tables directly**; `accounting.entry_guarded` and `accounting.journal_guarded` resolve the two feeds to exactly one per `(period_year, period_month)` — gl\_import wins for any period it covers, derived fills the rest. Summing both for a period double-counts. A studio can run either feed alone, or both (CSV to backfill history and capture payroll/depreciation; API for near-real-time coverage of what it *can* see). ## Coverage at a glance | Xero entity | Canonical home | Grain | Window / freshness | | ------------------ | --------------------------------------------------------------------- | ------------------------------------------------------------ | ------------------------------------------------------ | | Organisation | (read at connect time only — org name, currency, country) | one per tenant | full pull, not stored canonically | | Contacts | `people.company` (+ `accounting.contact_link` once resolved) | one per contact | incremental (`UpdatedDateUTC` watermark) | | Accounts | `accounting.account` | one per chart-of-accounts code | incremental | | TrackingCategories | `accounting.tracking_category` | one per category+options | full pull (unpaged, no watermark) | | Items | raw only (`ingest.raw_record`, `poll.items`) — not mapped canonically | — | incremental | | TaxRates | raw only — not mapped canonically | — | full pull (unpaged, no `UpdatedDateUTC`) | | Invoices | `accounting.journal` (header) + `accounting.entry` (lines) | one journal + N entry lines | **date-windowed** on `Date`, `journal_type='invoice'` | | CreditNotes | `accounting.journal` + `accounting.entry` | one journal + N lines | date-windowed, folds into invoice-shaped entries | | Payments | `accounting.journal` **header only** | one journal, no entry lines | date-windowed, `journal_type='payment'` | | Overpayments | `accounting.journal` **header only** | one journal, no entry lines | date-windowed, `journal_type='overpayment'` | | Prepayments | `accounting.journal` **header only** | one journal, no entry lines | date-windowed, `journal_type='prepayment'` | | BankTransactions | `accounting.journal` + `accounting.entry` | one journal + N lines | date-windowed, `journal_type='bank_transaction'` | | BankTransfers | `accounting.journal` **header only** | one journal, no entry lines | date-windowed, `journal_type='bank_transfer'` | | ManualJournals | `accounting.journal` + `accounting.entry` | one journal + N lines | date-windowed, `journal_type='manual'` | | GL CSV import | `accounting.entry` + `accounting.journal`, `origin='gl_import'` | whatever the export contains, including payroll/depreciation | as of the last upload — operator-driven, not scheduled | If a row isn't in this table, we don't have it — see What we cannot get. Every line carries `tracking` (JSONB) when the source document has Xero Tracking Categories applied (e.g. `[{"name":"Location","option":"Downtown"}]`) — join to `accounting.tracking_category` for the category's full option set. ## What we cannot get **Tier/vendor-gated:** * **Posted GL (`/Journals`) is Xero Advanced-tier only** (roughly $895–1,445/month plus a security assessment), and post-April-2026 Custom Connection scopes exclude journals entirely. We cannot read Xero's own journal feed — instead we **synthesise** P\&L entries from source documents (`origin='derived'`). * **Payroll journals are invisible** to accounting API scopes — Xero Payroll posts its own system journals that the Accounting API never exposes. Manual-journal payroll (an operator posting payroll by hand) IS captured; the Xero Payroll API itself is deliberately not wired. GL CSV upload covers the rest. * **Depreciation and other fixed-asset system journals** — same class of gap as payroll. `/Journals` or a GL CSV import are the only paths. * **Unreconciled bank statement lines** — there is no public API for raw bank feed data (Bank Feeds is partner-restricted). `BankTransactions` are coded/reconciled entries only, so cash here is "as coded in Xero," never "as sitting at the bank." * **Custom Connections are AU/NZ/UK/US only**, and the studio pays for it directly (roughly $10/month on their own Xero subscription). Studios outside those regions are GL-CSV-only. * **5,000 API calls/day and 60/minute per tenant** — a large org's first backfill can legitimately take several days; see Counting traps. * **No webhooks** — this is a poll-based ingestor. Coverage is as fresh as the last sweep or on-demand sync, never real-time. **Product doesn't do it:** * **Zero `bookings.*` coverage.** No members, classes, attendance, or appointments. Xero doesn't know what a class is. * **No member-level revenue.** Xero contacts are settlement-level — a Stripe payout landing as one lump deposit is one Xero contact, not the members behind it. Member money lives on the Stripe/MBO/Wix axes; `accounting.contact_link` is an opt-in join (propose → confirm), and analytics only ever read `status='confirmed'` links. **Architectural consequences (deliberate design, not gaps to fix):** * **The derived feed is P\&L only.** AR/AP and bank control accounts are intentionally not synthesised, so there is **no balance-sheet math on the API path** — Trial Balance needs the GL CSV import. * **Payments, credit notes, overpayments, prepayments and bank transfers land as journal header only** — cash-timing signal, no P\&L lines. This prevents double-counting the invoice/bill lines that already carry the P\&L side. * Only `AUTHORISED`/`PAID` invoices, `AUTHORISED` bank transactions, and `POSTED` manual journals produce derived entries. `DRAFT`/`SUBMITTED` documents are invisible; a voided document flips its journal's `status` rather than disappearing (join on `status`, don't assume absence means never-existed). * Items and TaxRates land raw only (`ingest.raw_record`) — join keys (`accounting.invoice_line.item_code`, `accounting.entry.tax_code`) already exist on the canonical lines; the reference tables themselves aren't projected. ## Identity and join keys | Thing | Xero id | Canonical | | -------------------------------------------- | ------------------------------------ | --------------------------------------------------------------------------------------------------------------------------- | | Contact | `ContactID` (GUID) | `accounting.contact_link.contact_external_id`, resolved via `entity_lookup(type=company)` or a confirmed link's `target_id` | | Account | `Code` | `accounting.account.source_external_id`, `accounting.entry.account_code` | | Tracking Category | `TrackingCategoryID` | `accounting.tracking_category.source_external_id`; each entry's `tracking` JSONB carries `{name, option}` pairs, not ids | | Item | `Code` | `accounting.invoice_line.item_code` (raw item detail lives in `ingest.raw_record`, not projected) | | Tax Rate | `TaxType` | `accounting.entry.tax_code` (no GUID — `TaxType` itself is the natural key) | | Invoice/Bill/Bank Transaction/Manual Journal | vendor GUID or natural-key hash | `accounting.journal.source_external_id` | | GL CSV row | deterministic hash of the export row | `accounting.journal.source_external_id`, prefixed `glimport:` | A Xero contact is **settlement-level**, not a person. `accounting. contact_link` resolves one to a `people.staff` / `people.company` / `people.identity_link`-canonical member / `people.location` row — `target_type` says which. Resolution is **opt-in and two-step**: `propose_contact_match` (AI, `status='proposed'`) then `confirm_contact_match` (operator, `status='confirmed'`). Only confirmed links feed `accounting.journal_resolved` — an unmatched or still-proposed contact leaves `resolved_target_id` NULL, which is the normal state for most contacts, not a bug. Cost splitting works the same way: `accounting.allocation` splits a journal or a recurring account-code rule across `location` / `class_category` / `staff`, proposed via `propose_allocation` and made authoritative via `confirm_allocation`. `accounting.entry_allocated` and `accounting.teacher_cost_month` include only confirmed splits — an entry with no confirmed allocation is simply absent from those views, not zeroed out. ## Counting traps **derived vs gl\_import — the one that matters most.** Query `accounting.entry_guarded` / `accounting.journal_guarded`, never the base tables. A raw `SUM(accounting.entry.debit)` over a period that has both an API-derived line and a GL-imported line for the same month double-counts every time. **Payments/credit notes/overpayments/prepayments/bank transfers have NO entry lines** — only a journal header. `SUM(accounting.entry.debit) FROM ... JOIN accounting.journal WHERE journal_type IN ('payment', 'overpayment', ...)` returns nothing (correctly) because these entries don't exist; that is not a missing-data bug. **Multi-currency is real here and not converted.** `CurrencyRate` is read from Xero but not applied — every amount is in the document's own currency. A multi-currency studio's rows will have mixed `currency` values across `accounting.entry`/`journal`; **group by currency, never blind-`SUM`** (exactly like `accounting.entry_allocated` and `accounting. teacher_cost_month` already enforce by design). **DRAFT and SUBMITTED documents are invisible, and voids don't delete.** A draft invoice never produces a derived entry — don't read its absence as "no sales this period," check `get_system_status` and the coverage window first. A voided invoice's journal flips to `status='voided'`; the row is still there, so filter on `status`, don't assume every open-ended `SELECT` is already excluding it. **Edited invoices can leave a stale line.** Xero line items are GUID-keyed but not tombstoned on our side when a line is removed after initial sync — a subsequent full re-pull (datafix) or the journal's `total_amount` versus the summed entry lines is the drift check. **Day-cap-partial first backfills are normal, not stuck.** A large org's initial pull can legitimately span several days under Xero's 5,000 calls/tenant/day ceiling — `get_system_status` and the coverage card show progress; a "day 2 of 5" backfill is working as designed, not broken. **Axis separation.** A Xero sales invoice and a Stripe charge for the same revenue event are different axes recording different things — never add `accounting.entry` into `commerce.payment` or `commerce.sale` totals. `commerce.payment.settlement_batch_id` is a reserved hook for a future payout-reconciliation join; it is not populated yet. ## Questions this source can and can't answer **Can answer well** * P\&L by account/category/period, from whichever feed (or blend) covers that period — via `accounting.entry_guarded` * Cash timing on payments, credit notes, bank transactions, overpayments, prepayments and transfers (journal-header level) * Cost of a specific instructor/location/class-category, once contacts and allocations are confirmed — `accounting.teacher_cost_month`, `accounting.entry_allocated` * Who a settlement-level contact resolves to on the people side, once confirmed — `accounting.journal_resolved` * Whether this studio's Xero connection (or CSV import) is current — `get_system_status` **Cannot answer from Xero alone** * Anything operational — classes, attendance, instructors, members as people *(not in Xero)* * Balance sheet position from the API feed *(derived is P\&L only — needs a GL CSV Trial Balance import)* * Payroll or depreciation detail *(invisible to API scopes — GL CSV only)* * Per-member revenue *(Xero contacts are settlement-level — join through `accounting.contact_link`, and expect an unmatched remainder)* * Anything before the earliest backfilled window, or before a GL CSV import's covered period **When both a live Xero connection and a GL CSV import exist**, the CSV wins per period it covers and the API fills the rest — `accounting.entry_guarded` already resolves this; don't attempt to pick a feed yourself. ## Recipes — what works well ### Is the data current → `get_system_status` Xero is polled (backfill + incremental sweep), not webhook-driven, and a first backfill on a large org can span several days by design. Check here before assuming staleness is a bug. ### P\&L by account, correctly de-duplicated ```sql SELECT account_code, account_name, sum(debit) - sum(credit) AS net FROM accounting.entry_guarded WHERE source = 'com.xero' AND posted_at >= now() - interval '90 days' GROUP BY 1, 2 ORDER BY 3 DESC; ``` Reading `entry_guarded` (not `accounting.entry`) is what makes this safe against the derived/gl\_import overlap. ### Resolving a contact to a person → `entity_lookup(type=company)`, then `propose_contact_match` / `confirm_contact_match` ```sql -- Which Xero contacts have no confirmed people-side identity yet? SELECT DISTINCT contact_external_id, contact_name FROM accounting.journal_resolved WHERE source = 'com.xero' AND resolved_target_id IS NULL ORDER BY contact_name; ``` Propose with `auto=true` first (deterministic email/exact-name match against `people.company`/`people.staff`) — it reports `no_match`/`ambiguous` rather than guessing, so a human only needs to look at the genuinely unclear ones. ### What did this instructor actually cost us → `accounting.teacher_cost_month` ```sql SELECT period_year, period_month, currency, sum(cost_debit) FROM accounting.teacher_cost_month WHERE staff_id = $1 GROUP BY 1, 2, 3 ORDER BY 1, 2; ``` Zero rows means no confirmed staff-dimension allocation exists yet for this person — propose one with `propose_allocation`, not a sign that the instructor cost nothing. ### Cash timing on payments/bank activity (no P\&L double-count) ```sql SELECT journal_type, date_trunc('month', posted_at) AS month, sum(total_amount) FROM accounting.journal_guarded WHERE source = 'com.xero' AND journal_type IN ('payment', 'bank_transaction', 'bank_transfer') GROUP BY 1, 2 ORDER BY 2; ``` ### Update-to-now vs a full re-pull The connector page's "Update to now" runs an incremental `If-Modified-Since` sync from the watermark — safe to run anytime, cheap, and it is **not** the same operation as a windowed re-pull or a per-entity datafix (those force-reload a date range or a whole entity and are heavier). Coverage-gap re-pulls in the operator console use the windowed path automatically; the sync button never does. ### What doesn't work on Xero alone `list_at_risk_members`, `get_class_utilisation`, `get_teacher_performance` and every other bookings-shaped tool return empty on a Xero-only org — there is no booking data for them to read, and empty is the correct answer, not a fault to chase. ## Where this lives in the code | Concern | Path | | ---------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | | Entity catalogue (specs, `Dated`/`ModifiedSince`, the `where=Date` window builder) | `services/ingestors/xero/internal/xeroclient/specs.go` | | Stage-1 pull (watermark vs windowed, day-cap, per-page checkpoints) | `services/ingestors/xero/internal/orchestrator/stage1.go` | | Transforms (journal-header-only entities, tracking passthrough) | `services/ingestors/xero/internal/transform/` | | Two-feed resolution views | `services/intelligence/migrations/postgres/org/950_guarded_views/003_entry_guarded_feed_resolution.sql`, `004_journal_guarded_feed_resolution.sql` | | Contact/allocation tables + analytics views | `services/intelligence/migrations/postgres/org/400_accounting/006_contact_link.sql`, `007_allocation.sql`, `950_guarded_views/006_accounting_analytics.sql` | | Matching/allocation MCP tools | `services/intelligence/internal/tools/accounting_links.go` | | GL CSV import + staleness reminder | `services/intelligence/internal/admin/httpapi/ingestor_xero_gl.go`, `nightly_xero_gl_reminder.go` | | Operator-facing connect guide | [Xero connector](/your-data-sources/xero.md) |