Xero — ontology map

What Xero gives Kula Intelligence, what it cannot give, and why the derived (API) and gl_import (CSV) feeds must never be summed together.

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

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

-- 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

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)

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