QuickBooks Integration
Overview of the QuickBooks Online (QBO) integration — daily ETL extracts and the ACH Remittance workflow.
What's in this section
- QuickBooks ETL — Daily extract of 11 QB entities to PostgreSQL via Prefect
- ACH Remittance — Automated daily ACH payment emails to vendors
ETL Overview
The quickbooks-etl project extracts data from QuickBooks Online daily to PostgreSQL (wasteology_dev, qbo schema). It runs on the enevo-aci-pool work pool in the Production Azure subscription.
Deployments
| Deployment | Schedule | Description |
|---|---|---|
qbo-daily-extract | Daily 6:00 AM UTC | Full extract of all 11 QBO entities |
qbo-full-refresh | Manual only | Nuclear option — rebuild from scratch |
Both deployments use full extraction mode. Incremental extraction was abandoned due to data loss risk — QB entities don't all support reliable watermarks.
Entities Extracted
| Entity | QB Object | PostgreSQL Table |
|---|---|---|
| Accounts | Chart of accounts | qbo.stg_qbo_accounts |
| Vendors | AP vendors | qbo.stg_qbo_vendors |
| Customers | AR customers | qbo.stg_qbo_customers |
| Bills | AP bills | qbo.stg_qbo_bills |
| Bill Payments | ACH/check payments | qbo.stg_qbo_bill_payments |
| Invoices | AR invoices | qbo.stg_qbo_invoices |
| Purchases | PO/expense | qbo.stg_qbo_purchases |
| Transfers | Bank transfers | qbo.stg_qbo_transfers |
| Journal Entries | JE | qbo.stg_qbo_journal_entries |
| Deposits | Bank deposits | qbo.stg_qbo_deposits |
| Vendor Credits | AP credits | qbo.stg_qbo_vendor_credits |
Credential Strategy
QBO credentials follow a mixed strategy (see Prefect — Deploying a Flow):
| Credential | How stored | Why |
|---|---|---|
QBO_CLIENT_ID, QBO_CLIENT_SECRET, QBO_REALM_ID | KV-injected literals | Static — never change during flow run |
QBO_ACCESS_TOKEN, QBO_REFRESH_TOKEN | Prefect block refs | Flow writes refreshed tokens back on every run |
| DB passwords | KV-injected literals | Static |
Dashboard
The QuickBooks page in Palantiri provides: ACH payment validation, vendor contact management, and financial views.
Running a Full Refresh
Use qbo-full-refresh when data looks inconsistent or a QB entity was backfilled:
- Go to app.prefect.cloud → Deployments
- Find
qbo-full-refresh - Click Run → Quick Run
- Monitor in Flow Runs — typically takes 5–15 minutes
A full refresh truncates and reloads all tables. Don't run during business hours if downstream reports are in use.
Source Repo
~/projects/quickbooks-etl — ADO: dev.azure.com/wasteology/Wasteology/_git/quickbooks-etl