QuickBooks API investigation

Started 2026-09-26. Owner: Stephen. Findings are appended to the bottom as each stage completes.

Why

Phase 1 of Initiative B (the account/class classifier + review loop in src/) is blocked on ground truth: data/feedback.db has zero rows because nobody has produced the historical-bills export that src.ingest_seed needs. Stephen has now accepted the invites to all 73 QuickBooks companies (records/qbo_invites_2026-09-25.tsv), so that ground truth is reachable through the API instead of a manual CSV export per company.

Until now the repo has never talked to QuickBooks at all: no QBO_* values in .env, no OAuth token cache, and src/config.py refuses anything but QBO_ENV=sandbox.

Decision (2026-09-26)

Read-only production access is in scope. The sandbox is Intuit's demo company, so it can only prove plumbing; every question that matters (what the 73 charts of accounts look like, whether they share a template, pulling real bills) needs production. The "sandbox-only" rule in src/config.py is replaced by two independent guardrails:

1. an explicit env gate (QBO_ENV=production must be set deliberately), and
2. a read-only client wrapper enforced in code — Intuit has no read-only OAuth scope (com.intuit.quickbooks.accounting is read+write), so read-only has to be our discipline: .all() / .query() / .get() only, never .save() / .delete() / batch.

Writing anything to production QuickBooks stays out of scope.

Where the data lives

The discovery output (records/qbo_discovery/) is not committed — it holds vendor names and dollar totals. It lives in the project's private S3 bucket (see infra/README.md) and syncs with scripts/data_sync.py:

uv run python scripts/data_sync.py pull     # get the latest dumps (needs the cnu profile or a collaborator key)
uv run python scripts/data_sync.py push     # after re-running discovery

Anyone with access to the QuickBooks companies can also regenerate it from scratch in a few minutes:

uv sync
cp .env.example .env            # fill in QBO_PROD_CLIENT_ID / _SECRET / _REDIRECT_URI
QBO_ENV=production uv run python -m src.qbo_client authorize   # once per company
QBO_ENV=production uv run python scripts/qbo_discover.py       # ~5 s per company

Tokens land in data/qbo_tokens/ (gitignored, one file per company). records/qbo_realms.tsv (realm ID ↔ company name, no financial data) is committed so people can tell which companies have been surveyed.

Questions to answer

1. Consistency across companies. Are the chart of accounts, class list and department list identical (or template-derived) across the 73 companies? This decides whether Phase 1 is one classifier or 73.
2. Realm ↔ campus mapping. CompanyInfo (legal name, address, ZIP) for each realm, cross-checked against alpha_locations.json. Also gives a clean fix for the 75010 Sports Academy / Alpha Carrolton ZIP collision the campus classifier hits.
3. Bill volume and shape. Bills per company per month, line-item structure (AccountBasedExpenseLineDetail vs ItemBasedExpenseLineDetail), how consistently ClassRef is filled in. Purchase-order counts (is PO matching even viable later?).
4. Attachments. Do Attachable records on bills correspond to the invoice PDFs in mail_export/? If so, that is ground truth for the campus classifier as well as the account/class one.
5. Operational limits. Rate limits (500 req/min/realm, 10 concurrent), refresh-token expiry (100 days idle), and how painful 73 one-time consents are in practice.

Stages

Stage 0 — Plumbing on sandbox

• Put the dev/sandbox QBO_CLIENT_ID / QBO_CLIENT_SECRET in .env (from app-creds.txt), QBO_ENV=sandbox. (2026-09-27)
• Run uv run python -m src.qbo_client: completes the paste-back OAuth flow, writes data/qbo_token.json, prints the sandbox company's accounts and classes. Proves intuit-oauth + python-quickbooks + refresh. (2026-09-27 — see Findings / Stage 0.)
• Redirect-URI rules (checked 2026-09-27). Development accepts http://localhost:8000/callback (registered under Settings → Redirect URIs). Production rejects localhost entirely, both http and https. So Stage 2 needs a real HTTPS URI on a domain we control registered under the Production tab, e.g. https://alpha-billing-classifier.aicost.fit/qbo/callback. Nothing has to be listening there — the paste-back flow only needs the code and realmId to land in the address bar — but Cloudflare Access sits in front of that host, so verify the query string survives the Access login redirect; if it doesn't, use a tiny separate Pages project without Access.

Stage 1 — Client refactor (multi-company, read-only)

• One OAuth consent grants one realm, so tokens become per-realm: data/qbo_tokens/<env>/<realmId>.json (gitignored), replacing the single data/qbo_token.json.
• Realm → company mapping is logged to records/qbo_realms.tsv (environment, realm_id, company_name, authorized_at) as each consent completes — a separate file rather than a column on the invites TSV, since one company can have both a sandbox and a production realm. Matching realms back to the invites roster is a Stage 2 job (CompanyInfo name/ZIP vs. qbo_invites_*.tsv).
• src/config.py: env gate accepts sandbox or production; each has its own client ID/secret pair (QBO_SANDBOX_* / QBO_PROD_*) so the two can never be mixed up. Production has no default redirect URI (Intuit rejects localhost there), so QBO_PROD_REDIRECT_URI is required.
• src/qbo_client.py: ReadOnlyQuickBooks refuses everything but GET and POST …/query inside make_request, so every SDK write path is covered without enumerating them; authorize runs one consent with a one-shot local listener on the dev redirect (paste fallback for production) and checks the OAuth state; proactive refresh on every get_client. Verified live on the sandbox: reads work, Bill.save() and delete_object are refused before any HTTP.
• Backoff on 429 — deferred to Stage 2's discovery loop, where it matters (nothing in Stage 1 makes more than a handful of calls).
• Tests: tests/test_qbo_client.py — the read-only policy table, the guard firing inside make_request, per-env config isolation, the production key/redirect requirements, and the token store layout. (uv run pytest → 24 passed.)

Stage 2 — Discovery

scripts/qbo_discover.py dumps, per realm, to records/qbo_discovery/<env>/<realmId>/: company_info.json, accounts.csv, classes.csv, departments.csv, vendors.csv, summary.json (bill counts by month, line-detail types, ClassRef fill rate, lines/$ by class, top accounts and vendors, PO count). Then records/qbo_discovery/<env>/REPORT.md diffs the account and class sets across every company on disk. ~5 s per company; pages bills 1000 at a time and backs off on 429.

• Script written and run against the first production company (2026-09-27; see Findings).
• Authorize 2 more companies (QBO_ENV=production uv run python -m src.qbo_client authorize, once each), re-run discovery, read the cross-company diff. Suggested: Brownsville (78520) and Austin K-8, both prominent in the mail data.
• Then all 73. Each consent is one browser round-trip (~20 s), so ~30 minutes of clicking; the discovery run itself is ~6 minutes.
• Q4 experiment: scripts/qbo_match_mail.py lines up exported mail with categorized bills on four signals (attachment hash, filename, DocNumber in text, vendor+amount). Run on the Miami company 2026-09-27; see Findings.

Stage 3 — Ground-truth export

• scripts/qbo_export_bills.py: Bill.Line[].AccountBasedExpenseLineDetail .AccountRef / .ClassRef → data/seed/bills.csv in the shape src.ingest_seed expects (vendor, description, amount, entity, account, class), entity = the realm's company name.
• uv run python -m src.ingest_seed data/seed/bills.csv → uv run python -m src.retrain → uv run streamlit run src/review_app.py. Phase 1 actually runs.

Wrap-up

• README: drop the "only ever talks to the sandbox" promise; state the new rule (production read-only, write path out of scope).
• Findings section below filled in; note to Sahil pointing here.

Findings

Stage 0 — sandbox plumbing (2026-09-27)

Works end to end with the dev keys: consent → code exchange → token cache → proactive refresh on the next call → Account.all / Class.all / CompanyInfo.all / Bill.all / Department.count.

• Sandbox realm 9341457980115544, "Sandbox Company US 8466". 81 active accounts, 0 classes, 0 departments, 15 bills. Class tracking isn't even on in the sandbox company — one more reason it can't answer the real questions.
• Bill lines come in two shapes. Of 16 sandbox bill lines, 14 are AccountBasedExpenseLineDetail (has AccountRef + ClassRef) and 2 are ItemBasedExpenseLineDetail (has ItemRef; the expense account is on the Item, not the line). The Stage 3 exporter must handle both, and python-quickbooks exposes each as a different attribute on the line (l.<DetailType>), so read via getattr(l, l.DetailType).
• 0 of 16 lines carry a ClassRef in the sandbox — so how consistently Alpha's bookkeepers fill in class is a production-only question (Q3).
• python-quickbooks .count() and max_results paging work as expected; no rate limiting hit at this volume.
• Consent UX note: pasting the callback URL into chat instead of the script's prompt works fine (code exchanged by hand), but the Stage 1 consent helper should just run a one-shot local HTTP listener on :8000 for the dev flow so nothing needs pasting.

Stage 2 — first production company (2026-09-27)

Production consent works via https://alpha-billing-classifier.aicost.fit/qbo/callback: Cloudflare Access preserves the query string through its login, so the code/state/realmId land in the address bar as needed. First realm: 9341452781248173 = Alpha School 33155, LLC (Miami). Read-only survey:

• Ground truth is excellent. 991 bills (2024-07 → 2026-10, all fetched in one page), 1,175 lines, 100% AccountBasedExpenseLineDetail, 1,172/1,175 (99.7%) carry a ClassRef. No manual CSV export needed for Phase 1 — Stage 3 can go straight from the API (Q3 answered for this company; still need to see whether the others look the same).
• Class is the campus dimension, but per-company it's nearly constant. 11 classes exist (Alpha Miami, Alpha Atlanta, Alpha Boca Raton, Alpha East Bay, Alpha Palm Beach, Alpha Palo Alto, Alpha School LLC, 2hour Learning, Alpha Summer Camps, Austin K-8, and a stray lowercase alpha-miami), which looks like a shared/templated list — but 1,169 of 1,175 lines are Alpha Miami. So within a company, account is the real classification target; class is ~deterministic given the company. Whether the same 11 classes appear in every company is the key Q1 check for the next realms.
• Chart of accounts is numbered and hierarchical (90 active; 43 Expense), e.g. 62300 Facilities/Support:Repairs and maintenance, 11510 Tangible Assets, 63220 Program:Workshops. Template-shaped; cross-company diff pending.
• CompanyInfo address is HQ, not the campus (2028 E Ben White Blvd, Austin 78741, for the Miami company). Realm → campus mapping must come from the ZIP in the company name (Alpha School 33155, LLC), not the address. That's what alpha_locations.json already keys on. (Q2)
• 133 vendors; top by bill count: Nicolas Management Group (168), Bryant Security (64), FPL (63), Natural Touch Lawn (62), Worksmith (59). Rent is a single big line per month (GulliverPrep, 62200 Rent, ~$138k).
• 6 purchase orders in 26 months → PO matching is not a thing here (Q3).
• 1,554 attachables, 980 of them PDFs, linked to Bill IDs via AttachableRef (some also to Vendor, some unlinked). File names look like the emailed invoices (Miami Ice Arena_Alpha School (1) (1).pdf), so matching against mail_export/ attachments by name/size/hash is the Q4 experiment.
• 1 department (Alpha DA), unused for our purposes.

Mail ↔ bill matching, Miami company (2026-09-27)

QBO_ENV=production uv run python scripts/qbo_match_mail.py 9341452781248173 → records/qbo_discovery/production/9341452781248173/mail_match.{csv,md} (in S3, not git). 100 messages (Sep 23–25) vs 95 bills since 2026-07-01.

• 9 messages matched 11 bills, all on strong signals (14 pairs). The workhorse is the vendor's invoice number: DocNumber in QuickBooks is the invoice number, and it appears in the subject ("Automated Payment Reminder – Invoice #1035"), the body (a forwarded past-due list named six Nicolas Management invoices, all six found as bills), or the attachment name (Invoice_1036_from_Mason_...pdf). Attachment hashes matched only 2 pairs — the PDF in QuickBooks is usually not byte-identical to the one that was emailed (re-saved, "(1) (1)" copies), so hashing is a confirmation signal, not the primary key.
• Every match gives both labels at once: the bill's account and class (= campus). E.g. mail 71444 → bill #8110: 63220 Program:Workshops, Alpha Miami. That is a training row for the account/class classifier and a ground-truth row for the campus classifier, from one join.
• The campus classifier under-attributes. Of the 9 matched messages, the latest run labelled 4 as alpha_school_33155_llc and 5 as not_campus_specific — yet all 5 are provably Miami bills (past-due forwards, reply threads about Miami invoices). The forwarded/reply messages carry the campus only via invoice numbers, which the text classifier can't resolve but a QuickBooks lookup can. So the join is not just evaluation data; it should be a feature (or a pre-pass) of the campus classifier: extract invoice numbers → look up DocNumber across companies → campus.
• Timing explains the low hit rate on bills, not the method. Only 5 of 95 bills were entered during the mail window, and 4 of those on Sep 26 — after the export's last message. Bookkeepers enter a bill a few days after the invoice email arrives. The 84 unmatched bills were entered before Sep 23, so their originating emails are simply older than the export. We need more mail: the export should reach back to at least July 2026 to cover the 95-bill window, and to 2024-07 to cover all 991 bills. export_alpha_mail.py has --limit N (newest N) and --incremental but no date-range backfill — adding --since DATE (IMAP SINCE) is a small change. The inbox runs ~50 messages/day, so three months ≈ 4,500 messages.
• The exporter is safe for the shared inbox: the only fetch is (FLAGS BODY.PEEK[]) (export_alpha_mail.py:112) and there is no STORE, so read/unread flags are never touched. Keep it that way when adding --since.
• One suspected false positive: mail 71415 (an Xtium thread) matched Mason bill doc 1025 on docnum alone — a bare 4-digit number in a long thread. Rule for the next version: a docnum-only match needs ≥5 characters or a vendor token too.

The mailbox is the AP workflow (2026-09-27)

While checking whether the emails behind the Sep-26 bills had arrived, a read-only folder scan (scripts/mailbox_census.py) showed that INBOX is a small slice of billing@alpha.school. The mailbox has ~1,466 Gmail labels and tens of thousands of label-messages, and the labels are the AP team's workflow:

• State labels — Awaiting Onboarding → Onboarded / Vendor Onboarded, Awaiting Approval (160) → Approved (1,695) → *For Payment (249) → Posted (592), plus *Pending NS Posting (162), Pending from BU (202), Pending from AP Team (69), *Bill verification.
• Entity labels — Alpha 33155, Sports Academy 75010, LLC, Sports Academy 78734, LLC, 2hour Learning Inc/*, Unbound Academy AZ, Esports Academy LLC, Future of Education, …
• Vendor labels — Vendor/<name>, hundreds of them (one per vendor, plus Vendor/*1-One Time Vendor, Vendor/*2-Cancelled Vendor invoice).
• Non-AP traffic in its own labels: Tuition and other Fees Queries (2,904), Trilogy Query (1,305), Alpha Team (1,769), **Syft (646), Newsletter/*, Customer/*.

Consequences:

1. The vendor emails behind the Miami bills aren't in INBOX because they were processed and filed. Zero INBOX hits for Mason, Nicolas Management, Designlab, Miami Ice; Mason shows up under Approved (6), Pending from BU (1), Posted (1). So "pull more mail" means pulling the labelled archive, not a longer INBOX window. export_alpha_mail.py takes --mailbox, so it can already do this one label at a time; a --since and a multi-label mode would make it one command.
2. The labels are human ground truth at scale — vendor, entity, and whether a message became a bill — independent of QuickBooks. Joined with QuickBooks (account + class via DocNumber), that's the training set. scripts/dataset_stats.py totals it up and the site's Datasets page shows it.
3. The campus classifier's taxonomy should probably be the entity labels the AP team actually uses, not the 73 QuickBooks companies — or at least be evaluated against them, since that's what the humans assign.
4. Gmail labels are many-to-many (a message can carry Vendor/X, Approved and Alpha 33155 at once), so the census counts label-messages, not distinct messages; a real export should key on Message-ID and record the label set per message.

Census output: records/mailbox/census.json (S3, not git).

Origin detection, classifier v3, and the comparison page (2026-09-27)

• src/mail_origin.py answers "did the vendor send this to billing@ or did an employee forward it?" from headers + body, no model: sender domain (internal / invoicing platform / vendor / system), forwarded block present, original sender inside the forward (first external From: — forward chains pass through several employees), and whether billing@ was in To or only Cc. On the 100-message export: vendor direct 19, vendor via platform 17, vendor reply 19, employee forward 18, employee thread 18, system 9; billing@ was only Cc'd on 25. It's on the Classifications table (column + filter), each detail page, and the Datasets page. 34 tests.
• Rules vs Jev for origin: rules. The facts are structural and exact; Jev would guess at the same headers at a per-message price across an 80k-message archive. Jev stays for the judgment calls (vendor organisation, campus), fed with the origin facts.
• v3 = v2 + origin block in the input: no improvement (see classification_runs/CHANGELOG.md). The failures the join exposed are not framing problems — the campus isn't in the text at all, only the invoice number is. Next: deterministic pre-passes, (1) invoice number → DocNumber across authorized companies → company, (2) vendor → company from records/qbo_discovery/*/vendors.csv (133 vendors for Miami alone; across 73 companies this is a near-complete vendor→campus map), and (3) the AP team's own entity labels once the labelled archive is exported.
• Site: compare.html shows every run side by side (summary, consecutive-run agreement, a scorecard against QuickBooks-linked messages, per-message table with origin and a "disagreements only" filter). Ground truth comes from mail_match.csv via S3; with it absent the page degrades to runs-only.

Cross-company: four companies (2026-09-27)

Brownsville (78521), Orange County (92610) and Alpha High School (78701) authorized and surveyed alongside Miami. Answers Q1:

• Chart of accounts is one template. 72 active accounts identical by exact name across all four; 76 after normalizing case/spaces; 61 of ~66 numbered accounts identical by number. The rest are per-company bank/clearing accounts and typos (Misc Feess, Repairs and Maintenance vs maintenance). So: one account classifier for all 73 companies, with the account number as the label, names normalized.
• Classes are per company. Only Alpha School LLC and Austin K-8 appear in every list; each company carries its own campus classes (Brownsville K-8, Nova High School - Brownsville, Alpha High School (W 6th), Alpha Orange County, …). Class = sub-campus within a company, not a global campus taxonomy. Miami's 11 classes were Miami's own list.
• All bills in all four are 100% AccountBasedExpenseLineDetail; class fill is 100% in the three newer companies (99.7% Miami).
• Volumes differ a lot: Miami 991 bills since Jul 2024; Orange County 58 (since Dec 2025), Alpha High 144 and Brownsville 230 (since Jan 2026). The newer companies' bill history starts when the company was created, so the backtest window (Jul 2026 →) covers all of them.