> 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/quickbooks.md). # QuickBooks — ontology map **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 | Axis | Systems | Canonical home | What it means | | ----------------------- | ------------------------------------------------------------ | -------------------------- | -------------------------------------------------------------------------------- | | **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 | Canonical home | Grain | History | Freshness | | ------------------------------------------------------------- | ------------------------------------------------------------- | -------------------------------------------------------- | ----------------- | | `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)`. | Thing | QBO id | Canonical | | ------------------------------------ | ------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 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` = `:` | | GL line | `:` | `accounting.entry.source_external_id` = `::`; the parent document in `journal_source_external_id`, same namespaced form | | Invoice / sales-receipt line | `:` | `accounting.invoice_line.source_external_id`, same shape; the parent in `invoice_external_id` | | A bundle's nested line | `:` | `:::` — 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: ```sql SELECT period_year, period_month, coalesce(sum(credit) FILTER (WHERE category = 'revenue'), 0) - coalesce(sum(debit) FILTER (WHERE category = 'revenue'), 0) AS net_revenue, coalesce(sum(debit) FILTER (WHERE category IN ('expense','cogs')), 0) - coalesce(sum(credit) FILTER (WHERE category IN ('expense','cogs')), 0) AS net_expense FROM accounting.entry_guarded WHERE source = 'com.quickbooks' GROUP BY 1, 2 ORDER BY 1, 2; ``` `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. ```sql SELECT category, account_code, account_name, coalesce(sum(debit), 0) - coalesce(sum(credit), 0) AS spend FROM accounting.entry_guarded WHERE source = 'com.quickbooks' AND category IN ('expense', 'cogs') AND posted_at >= now() - interval '12 months' GROUP BY 1, 2, 3 ORDER BY 4 DESC; ``` `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: ```sql SELECT date_trunc('quarter', posted_at) AS quarter, journal_type, coalesce(sum((source_extras->>'total_tax')::numeric), 0) AS document_tax FROM accounting.journal_guarded WHERE source = 'com.quickbooks' AND source_extras ? 'total_tax' GROUP BY 1, 2 ORDER BY 1, 2; ``` 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`: ```sql SELECT CASE WHEN invoice_date > now() - interval '30 days' THEN '0-30' WHEN invoice_date > now() - interval '60 days' THEN '31-60' WHEN invoice_date > now() - interval '90 days' THEN '61-90' ELSE '90+' END AS age_bucket, count(DISTINCT invoice_external_id) AS invoices, coalesce(sum(line_total), 0) AS outstanding_ex_tax FROM accounting.invoice_line_guarded WHERE source = 'com.quickbooks' AND invoice_status = 'sent' GROUP BY 1 ORDER BY 1; ``` 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: ```sql WITH books AS ( SELECT period_year AS y, period_month AS m, coalesce(sum(credit), 0) - coalesce(sum(debit), 0) AS recognised FROM accounting.entry_guarded WHERE source = 'com.quickbooks' AND category = 'revenue' GROUP BY 1, 2 ), processor AS ( SELECT extract(year FROM occurred_at)::int AS y, extract(month FROM occurred_at)::int AS m, coalesce(sum(amount), 0) AS collected FROM commerce.payment_guarded WHERE status IN ('captured', 'succeeded') GROUP BY 1, 2 ) SELECT coalesce(b.y, p.y) AS year, coalesce(b.m, p.m) AS month, b.recognised, p.collected FROM books b FULL OUTER JOIN processor p ON p.y = b.y AND p.m = b.m ORDER BY 1, 2; ``` 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: ```sql SELECT min(posted_at) AS books_from, max(posted_at) AS books_to, count(*) AS lines FROM accounting.entry_guarded WHERE source = 'com.quickbooks'; ``` 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 | Concern | Path | | ---------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- | | 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 | [QuickBooks connector](/your-data-sources/quickbooks.md) |