QuickBooks — ontology map
What QuickBooks Online gives Kula Intelligence, what it cannot give, and why the books axis must never be summed against the revenue axis.
Source id: com.quickbooks · Role: general ledger / reconciliation — a satellite source, never the system of record for members or bookings · Access: QuickBooks Online v3 REST API, OAuth (minorversion 75), one company file per Kula org
QuickBooks is the studio's books — the ledger a bookkeeper keeps and the tax return is filed from. It knows what money was recognised: categorised, tax-treated, reconciled. It knows nothing about members, classes, attendance or instructors, and in most studios it doesn't even know about individual customers — revenue usually arrives in the books as daily-sales summaries or processor settlement batches, not people.
The single most important thing to understand about QuickBooks here is the two-axis model.
The two axes: books vs behaviour
Behaviour / revenue
Booking + payment systems (Mindbody, Wix, GymMaster, Stripe)
commerce.*, bookings.*
What was sold and collected — per member, per class, per charge
Books
QuickBooks (or Xero)
accounting.*
What the bookkeeper recognised — the general ledger, categorised and tax-treated
The same dollar legitimately exists on both axes: a Stripe charge in commerce.payment and the settlement posted into the books in accounting.entry. That is by design, and it is why nothing may ever sum across the axes — revenue questions go to commerce.*, GL/reconciliation questions go to accounting.*, and adding them double-counts every dollar. Comparison is the only legitimate cross-axis operation: put the two numbers side by side, never in one sum.
Within the books axis QuickBooks plays the same role as Xero: a studio connects one or the other, never both.
Coverage at a glance
accounting.entry (origin = 'derived')
one GL line — debit or credit, never both
backfill, default 24 months, extendable to company start
nightly CDC sweep
accounting.journal
one header per source document
same
nightly CDC sweep
accounting.invoice_line
one row per invoice / sales-receipt line, not per invoice
same
nightly CDC sweep
accounting.account
one per chart-of-accounts row
current snapshot
nightly CDC sweep
people.company (+ accounting.contact_link once confirmed)
one per QBO customer or vendor
current snapshot
nightly CDC sweep
Which QBO documents produce what:
Sales — journal +
invoice_line+ revenue entries (credit): Invoice (journal_type = 'invoice'), SalesReceipt ('receipt').Refunds and credits — journal + revenue-reducing entries (debit): CreditMemo and RefundReceipt (
'adjustment'). VendorCredit ('adjustment') is the expense-side mirror — it credits the expense account, reducing spend.Expenses — journal + expense entries (debit): Bill (
'bill'), Purchase — expenses, cheques, card spend ('bank_transaction'). Deposit ('bank_transaction') is money in, so its direct lines credit.Manual journals — journal + both sides: JournalEntry (
'manual'), the one document where Σdebit = Σcredit within the fan-out.Journal header only — no entry lines: Payment and BillPayment (
'payment'), Transfer ('bank_transaction'). These are cash / AR / AP movements with no P&L effect — see Counting traps.Never a guessed side. A Deposit line referencing a linked transaction or carrying no account, and a JournalEntry line with an unreadable posting type, demote the whole document to header-only.
TaxCode / TaxRate land raw only (re-pulled weekly). They are not joined into
accounting.entry— see the per-line-tax gap below.
category is populated — but asymmetrically, and you must know which way. Expense-side lines (Bill, Purchase, Deposit, VendorCredit, JournalEntry) carry category, account_code and account_name, resolved against the landed chart of accounts. Sales item lines (Invoice, SalesReceipt, CreditMemo, RefundReceipt) carry category = 'revenue' and no account_code — a QBO sales line references an Item, not an account, and the Item entity isn't pulled in Phase A. So expenses break down by account; revenue does not. Break revenue down by category, or by invoice_line.item_name on the invoice side. (The one exception: a discount line references QBO's own discount account, so it does carry a code.)
Discount and bundle lines are included. Discount lines land as entries on the opposite side to their siblings, and bundle (GroupLineDetail) lines are expanded into their nested components, so Σ(credits − debits) across one journal's entries reconciles to accounting.journal.total_amount. Discounts are entries only — they get no accounting.invoice_line row, so a sum(line_total) is gross of discount.
Amounts are normally in the home currency (multiplied through QBO's exchange rate on a foreign-currency document), so the GL sums like a GL; the original currency and rate ride in source_extras. One exception: a foreign document with no usable exchange rate keeps its transaction currency, and the currency column says so rather than mislabelling it — so group by currency before summing if a studio bills in more than one. If a row isn't in this table, we don't have it.
What we cannot get
Each item names its gap kind. Most are hard limits of QuickBooks, not gaps in our implementation.
Anything behavioural. Members, attendance, bookings, classes, visits, door access. QuickBooks is a ledger; it records money, not behaviour. bookings.* and people.member get nothing from this source. (product doesn't do it)
Per-member revenue, in most books. QBO "Customers" are whoever the bookkeeper invoices — frequently one "Daily Sales" customer or POS/Stripe settlement summaries, not people. Member-level money lives on the revenue axis (Stripe/MBO/Wix). Where a studio genuinely invoices individuals, accounting.contact_link can join them — after operator confirmation, never automatically. (product doesn't do it — depends on the studio's bookkeeping practice)
Tax per line. accounting.entry.tax_amount is never populated from QuickBooks — QBO reports tax at the document level only (TxnTaxDetail.TotalTax), which lands in accounting.journal.source_extras.total_tax. And accounting.entry.tax_code holds QBO's opaque numeric TaxCodeRef id (e.g. "5"), an internal pointer rather than a BAS/VAT label; the label sits in the raw poll.taxcode rows we land but never join. GST/BAS questions belong to the journal-level total or QuickBooks' own tax reports, never a per-line sum. (vendor has no API)
Which GL account a sale posted to. QBO sales lines reference an Item; the revenue account behind it lives on the Item entity, which Phase A deliberately doesn't pull. Revenue entries carry category = 'revenue' with a NULL account_code. (endpoint exists, not wired)
Payroll detail. Intuit has no public payroll API (its GraphQL beta is US-only, compensation-read, paid tier). Wages reach the books only as posted lump-sum GL lines — no per-staff labour cost. (vendor has no API)
The audit trail. QBO's audit log is UI-only. We cannot see who changed a transaction or when, beyond MetaData.LastUpdatedTime. (vendor has no API)
Bank-feed "For Review" lines. Unaccepted bank transactions sit in a closed Intuit system; only ledger-posted transactions are readable. The books we mirror are only as current as the studio's bookkeeping. (vendor has no API)
Card-processing detail — per-charge fees, payouts, disputes — when the studio uses QuickBooks Payments. That is a separate API we don't wire; for Stripe studios the same detail is already on the payment axis. (endpoint exists, not wired)
Time-of-day money. TxnDate is date-only; QBO transactions carry no business timestamp. Revenue by hour or class time is impossible from the books — that question belongs to the booking/payment sources. (destroyed at source)
Deletes that happened while the connector was down for more than 30 days. QBO transactions hard-delete and its change feed reports deletions for only 30 days. There is no Id-inventory backstop — a document deleted during a longer outage stays in accounting.* as a row QuickBooks no longer has, and nothing in the pipeline notices. A periodic inventory diff is Phase B work, not something running today. (live-forward only)
Custom fields beyond the legacy three on sales forms (the 12-field model is a paid GraphQL API), and Class/Department dimensions — those arrive only in source_extras for now. (endpoint exists, not wired)
QuickBooks Desktop. No cloud API exists; a QBD company file is invisible to this integration. QuickBooks Online only. (vendor has no API)
Draft and pending documents. We ingest posted-equivalent states only; drafts and estimates are not money. (not pulled, by design)
Identity and join keys
The realmId — QBO's id for one company file — is both the site_id on every row and the namespace prefix on every id the document fan-out mints, so lines from two company files can never collide on (source, source_external_id).
Company file
realmId
site_id on every row; prefix of the document/line ids below
Document (invoice, bill, payment, …)
Id
accounting.journal.source_external_id = <realmId>:<Id>
GL line
<parentId>:<lineId>
accounting.entry.source_external_id = <realmId>:<parentId>:<lineId>; the parent document in journal_source_external_id, same namespaced form
Invoice / sales-receipt line
<parentId>:<lineId>
accounting.invoice_line.source_external_id, same shape; the parent in invoice_external_id
A bundle's nested line
<groupLineId>:<lineId>
<realmId>:<docId>:<groupLineId>:<lineId> — QBO numbers a group's sub-lines from 1 within the group, so the group id has to qualify them
Account
Id
accounting.account.source_external_id — not namespaced (the SQL projector writes the bare id); code = AcctNum, falling back to FullyQualifiedName then Id where the studio doesn't number accounts
Customer / Vendor
Id
people.company.source_external_id (source = 'com.quickbooks') — also the bare id
entry.account_code carries the chart's code, not QBO's internal AccountRef.value, so it joins accounting.account.code. A ref the chart doesn't know leaves the attribution empty rather than writing an id that joins to nothing.
One company file per Kula org. accounting.* keys on (source, source_external_id) with the realm folded into the id — there is no per-realm partition in the schema — and mcp-admin stores exactly one QuickBooks connection per org. So a genuinely multi-entity operator needs one Kula org per company file, not one org holding several connections. If an org is ever reconnected to a different company file, the new realm's rows land alongside the old realm's: they no longer collide, but the old rows linger as stale history that no sweep will refresh and no delete will clear. Filter on site_id when that has happened.
A QBO customer or vendor is a counterparty, not a person. Resolution to the people side goes through accounting.contact_link, and it is opt-in and two-step: propose_contact_match (AI, status='proposed') then confirm_contact_match (operator, status='confirmed'). There is no automatic email matching — only confirmed links feed accounting.journal_resolved, and a contact with no confirmed link is the normal state for most books, not a bug.
Counting traps
Two axes, never summed together. The same dollar legitimately exists in commerce.payment (Stripe) and accounting.entry (books). Revenue questions → commerce.*; GL and reconciliation → accounting.*. Compare the two; never add them.
accounting.entry has no amount column — debit and credit only, one side per row. Net revenue is sum(credit) − sum(debit) over category = 'revenue'; net expense is the reverse over category IN ('expense','cogs'). There is nothing to SUM(amount). And because debit and credit are mutually exclusive, one of the two sum()s is NULL in any group holding a single side — coalesce() both or the subtraction silently returns NULL.
Refunds already reduce revenue — don't subtract them twice. CreditMemo and RefundReceipt fan out debit entries against category = 'revenue', and VendorCredit credits the expense side. A revenue figure computed as credits-minus-debits is therefore already net of credit notes and refunds. Subtracting the 'adjustment' journals' total_amount on top of it double-counts every refund.
Payments, bill payments and transfers are journal headers only. They are cash / AR / AP movements with no P&L effect — the revenue or expense was booked by the invoice or bill. Counting a payment journal as revenue double-counts the invoice that raised it, and joining one expecting entry lines returns nothing, correctly.
Don't group revenue by account_code. It is NULL on every sales item line (QBO sales lines point at Items, not accounts) — only discount lines carry one. A GROUP BY account_code over revenue drops nearly everything into one nameless bucket alongside a lonely "Discounts given".
No per-line tax. entry.tax_amount is always NULL here, and entry.tax_code is an opaque numeric id, not a tax label. A sum(tax_amount) over QuickBooks entries returns NULL, not zero — and a GROUP BY tax_code groups by internal ids nobody can read.
Query the guarded views, and know they lag a day. accounting.entry_guarded, journal_guarded and invoice_line_guarded resolve GL-import periods over derived ones and cut off at CURRENT_DATE — today's postings are deliberately invisible. The base tables are blocked to the read path anyway; asking for "today" and getting nothing is the view working as designed.
Voided documents remain as zero-amount rows. QBO keeps a voided transaction queryable with its amounts zeroed, and we mirror that (source_extras.voided = true). Sum amounts; never count rows as activity.
An edited-away line leaves a stale row. The fan-out upserts by line id and never retracts an individual line — delete line 3 of a five-line invoice in QuickBooks and its entry / invoice_line row stays behind, exactly as with Xero. Only deleting the whole document retracts (accounting.journal_removed clears the journal and its children). When a document's numbers look wrong, reconcile its entries against journal.total_amount.
Refunds aren't linked to their original sale. A RefundReceipt carries no hard link to the document it refunds — refund attribution within the books is heuristic at best.
The derived lane understates expenses. Until the GL-report lane (origin='gl_import') lands for QuickBooks, payroll and depreciation journals are absent — so a derived-lane P&L is decision-grade for revenue and coded operating spend, but total expense reads low. Say so when presenting a bottom line.
Questions this source can and can't answer
Can answer well
P&L by month and category — from the derived lane, with the expense caveat above
Expense breakdown by account (rent, software, cleaning, contractors)
Refunds and credit notes against revenue, by month
AR aging — who owes the studio, and how overdue
Supplier spend, by vendor and over time
Revenue-vs-books drift — what the processor collected vs what the books recognised, side by side
Cannot answer from QuickBooks alone
Any member, attendance or retention question (not in the books)
Revenue by class, instructor, or time of day (TxnDate is date-only, and the books don't know what a class is)
Revenue by GL account (sales lines reference Items, not accounts)
GST or tax per line, or by readable tax label (QBO reports tax at the document level, with an opaque code id)
Per-member lifetime value (customers are counterparties, often settlement summaries)
Payroll cost per staff member (no public payroll API)
"Who deleted this transaction?" (audit log is UI-only)
Phrase a gap as the vendor's limit, not missing data. "QuickBooks has no public payroll API, so wages only appear as posted totals" is correct and useful; "your data is incomplete" is not.
Recipes — what works well
Monthly P&L by category → accounting.entry_guarded
Debit/credit only — there is no amount column — always the guarded view, and coalesce() both sides or a single-sided month returns NULL:
net_revenue is already net of credit memos and refund receipts — don't net them again. And name the derived-lane caveat: payroll and depreciation are absent until the GL lane, so expenses read low.
Expense breakdown → entry_guarded by account_code + category
Works on the expense side because expense lines carry account attribution — there is no revenue equivalent.
account_code is AcctNum where the studio numbers accounts and the fully-qualified name where it doesn't — group by account_name too, as above, so the output reads either way.
Tax: the document total, not a per-line sum
There is no per-line tax. The document total QBO does give lands in the journal's source_extras, and it is the only tax number this axis has:
Sales-side journal_type (invoice, receipt) is tax collected; expense-side (bill, bank_transaction) is tax paid. Indicative only — not a BAS/VAT filing figure. The lodgeable number comes from QuickBooks' own tax reports, which apply adjustment/rounding/period rules we don't mirror. Say so rather than presenting it as final.
AR aging → accounting.invoice_line_guarded
invoice_status from QuickBooks is only ever sent, paid or void — there is no 'overdue' to filter on, so "outstanding" means 'sent' and the buckets come from invoice_date:
The grain is one row per line, so invoice counts must be DISTINCT on invoice_external_id. And line_total carries neither document tax nor discount lines, so it is ex-tax and gross of discount — use it for the shape of the aging, and journal_guarded.source_extras->>'balance' when the number itself has to be right.
Books vs processor → compare the axes, never join them
Two numbers side by side, each from its own axis:
Report both columns and the gap — never their sum. A gap is normal and usually explainable: timing (a December charge recognised in January), processor fees netted in the books, and cash posted as a settlement batch rather than per charge.
accounting.entry.sale_source_external_id is the per-record drill-down from a books line to the sale it recognises, and the column is honoured everywhere — but no accounting feed populates it in Phase A, QuickBooks included. A WHERE sale_source_external_id = … probe returns nothing today; use the comparison above rather than promising a per-charge trace.
Is the data current → get_system_status, then the coverage window
get_system_status first — it reports QuickBooks as current / stale / failing with a plain-language "current as of" date. Then establish the window before charting, remembering the views stop at yesterday:
The books are only as current as the bookkeeping — a transaction sitting in the bank feed's "For Review" list isn't posted and doesn't exist to us. A books_to that trails by weeks usually means the bookkeeper is behind, not the connector.
What doesn't work on QuickBooks alone
list_at_risk_members, get_class_utilisation, get_teacher_performance, get_retention_curve, get_member_payments — everything member- or booking-shaped needs sources QuickBooks isn't. On a QuickBooks-only org they return empty, and that is the correct result, not a fault.
The interesting questions are in the join
With a revenue source and QuickBooks connected the valuable analyses are cross-axis: what Stripe collected vs what the books recognised, by month; supplier and contractor spend against teacher performance (via confirmed contact_link rows); coded operating spend against the revenue that funded it. Keep each number on its own axis — present the comparison, never the sum.
Where this lives in the code
Ingestor (stage 1 pull + stage 2 restate)
services/ingestors/quickbooks/
Fan-out transforms (documents → journal + lines + entries)
services/ingestors/quickbooks/internal/transform/
1:1 SQL projections (accounts, customers, vendors)
services/intelligence/internal/ingest/project/templates_quickbooks.go
Canonical tables
services/intelligence/migrations/postgres/org/400_accounting/
derived/gl_import resolution views
services/intelligence/migrations/postgres/org/950_guarded_views/003_entry_guarded_feed_resolution.sql, 004_journal_guarded_feed_resolution.sql
OAuth connect flow
services/intelligence/internal/admin/httpapi/onboarding_quickbooks.go
Operator-facing connect guide
Last updated
Was this helpful?