QuickBooks to Postgres: how to replicate your ledger into a database you can query

How do you connect QuickBooks to Postgres?

You connect QuickBooks to Postgres by reading QuickBooks Online objects through the Intuit Accounting API v3 and writing them into Postgres tables, because QuickBooks Online has no native database export. Something sits in the middle: a managed connector, or a scheduled job that authenticates with OAuth 2.0, queries each entity, and upserts every batch into your schema keyed on the QuickBooks Id.

The connection has three parts. First, an Intuit developer app with OAuth 2.0 credentials and the accounting scope, which issues a refreshable access token and identifies the company you are reading through its realmId. Access tokens are short lived and the refresh token rotates, so the job has to persist and rotate both. Second, a Postgres landing spot: a database, a schema, and a role that can create tables and run upserts. Third, the transfer loop itself, which pages each entity through the /query endpoint, lands each batch in staging, and upserts into the target. The managed path is the QuickBooks to Postgres integration, which owns the OAuth token rotation, the paging, the watermark, the staging tables, the upserts, and the backoff.

Extraction method	What it is	When it fits and what it costs you
Full query reload	A paged SELECT * FROM Entity against the /query endpoint that walks every record with STARTPOSITION and MAXRESULTS	The first backfill, and small reference tables like Account or Item. Wasteful and slow to rerun on transaction tables that only change a little
LastUpdatedTime watermark	The same query with WHERE MetaData.LastUpdatedTime > '2026-07-19T00:00:00-07:00', resuming from your stored maximum	The default for steady-state incremental reads. Misses hard deletes, so pair it with reconciliation
Change Data Capture (CDC)	The /cdc endpoint returns every entity of the types you name that changed since a single timestamp, including soft-deleted ones	One call covers many entity types at once and surfaces deletes. Bounded lookback window, so it is a companion to the watermark, not a full replacement
Webhooks	Intuit POSTs an event notification to your endpoint when named entities change on a realm	Near real time refresh hints. Best effort only: verify the HMAC signature, treat it as a nudge to re-read, never as the source of truth
Managed sync tool	A hosted connector that owns OAuth, paging, throttling, deletes, and idempotent loads	When you would rather not maintain token rotation and delete reconciliation. Less control over exact field selection

Most production setups combine three of these: a full reload for the initial backfill, the LastUpdatedTime watermark for the steady state, and CDC (plus webhooks) to catch deletes and shorten latency. If your reporting lives in a warehouse rather than an application database, the sibling path is QuickBooks to Snowflake, and the modeling discipline below carries over unchanged.

Does QuickBooks Online have an API to export data?

Yes. QuickBooks Online exposes the Intuit Accounting API v3, a REST API with a SQL-like query language, but it is not a database and it is not a bulk dump. You authenticate with OAuth 2.0, address a company by its realmId, and read one entity type at a time through the /query endpoint. There is no direct database connection, no read replica, and no credentials to point psql at.

The query language looks like SQL but is deliberately narrow. You write statements such as SELECT * FROM Invoice WHERE MetaData.LastUpdatedTime > '...' STARTPOSITION 1 MAXRESULTS 1000 against a single entity, with no joins and a capped page size of 1000 rows. The entities you will replicate for a full ledger are Invoice, Bill, Payment, BillPayment, Customer, Vendor, Item, Account (the chart of accounts), JournalEntry, CreditMemo, Deposit, and Transfer. That gap between a query API and a database is the whole reason this pipeline exists. Calling the Intuit API live from a dashboard is slow and easily throttled: results are paged 1000 at a time, requests are rate limited per realm, and a report that ties a payment to the invoices it settled turns into many round trips because the API cannot join. Worse, you cannot join API responses to your own tables. Once the ledger sits in Postgres, attributing revenue to your product plans, or reconciling QuickBooks customers against your CRM, is one join instead of a paging loop, which is exactly what a data integration platform is for.

How QuickBooks Online objects map to Postgres tables and types

Model transactions and their lines as separate tables. Flattening an invoice's Line array into the invoice row (as a JSON blob or as line_1_amount, line_2_amount columns) destroys line-level analysis: you cannot group revenue by item or account, join lines to the replicated Item table, or answer which product drives credit memos without unnesting on every query. A child table keyed to the invoice id keeps all of that as ordinary SQL. The other rule that matters everywhere: QuickBooks returns linked objects as Ref types with a value (the id) and a name. Flatten Ref.value to a foreign-key column that joins to the replicated customers, vendors, or accounts table, and keep the name only as a cached label.

QuickBooks Online field	Postgres table.column	Type and notes
Id (every entity)	qbo_invoice.id, qbo_customer.id, ...	TEXT primary key. Unique per entity per realm. The upsert conflict target
Invoice DocNumber	qbo_invoice.doc_number	TEXT. The human-facing invoice number, not the primary key
Invoice TotalAmt	qbo_invoice.total_amt	NUMERIC(19,4). Arrives as a JSON number. Store as NUMERIC so cents never drift
Invoice Balance	qbo_invoice.balance	NUMERIC(19,4). Open amount still owed. Zero once fully paid
Invoice CustomerRef	qbo_invoice.customer_id	TEXT FK to qbo_customer.id. Flatten CustomerRef.value; keep .name as a label
Invoice TxnDate	qbo_invoice.txn_date	DATE. The accounting date of the transaction
Invoice DueDate	qbo_invoice.due_date	DATE, nullable. Drives aging and receivables reports
Invoice Line (array)	qbo_invoice_line (child table)	Own table keyed to invoice id. Do not flatten: lines carry per-item amount, ItemRef, and account detail
Payment TotalAmt	qbo_payment.total_amt	NUMERIC(19,4). The amount received on the payment
Payment LinkedTxn	qbo_txn_link (link table)	Ties a payment to the invoices it settles. Store txn_id, txn_type, and amount per link row
Account AcctType	qbo_account.acct_type	TEXT. Income, Expense, Bank, Accounts Receivable, and so on
Account AcctNum	qbo_account.acct_num	TEXT, nullable. The chart-of-accounts number when the company uses account numbers
Customer DisplayName	qbo_customer.display_name	TEXT. Unique per realm. The name shown across the ledger
Customer Balance	qbo_customer.balance	NUMERIC(19,4). Open receivable for the customer
Vendor DisplayName	qbo_vendor.display_name	TEXT. The payee name for bills and bill payments
Any MetaData.LastUpdatedTime	*.last_updated_time	TIMESTAMPTZ. The incremental watermark. Index it per table
CurrencyRef	*.currency_code, *.exchange_rate	TEXT (3-char) plus NUMERIC. Transaction currency in multi-currency companies. Keep home currency separate

The money trap is worth its own paragraph. QuickBooks returns monetary values as JSON numbers, and it is tempting to keep them as a floating point type because they already parse as numbers. Do not. Binary floating point cannot represent every two-decimal cent exactly, so summing thousands of invoice totals drifts by pennies that accounting will eventually flag. Store every amount as NUMERIC(19,4) and let Postgres hold exact decimals. Multi-currency companies add a second trap: each transaction carries a CurrencyRef for the currency the document was written in, while the company also has a home currency, and an ExchangeRate converts between them. Keep the transaction currency and the home currency in separate columns, store the exchange rate, and never blend amounts across currencies into one total. With the ledger modeled cleanly in Postgres you can join it to the rest of the business and turn the ledger into board-ready financial statements without re-reading Intuit for every refresh.

How do you sync QuickBooks incrementally instead of a full export?

Sync incrementally by filtering each query on MetaData.LastUpdatedTime, or by calling the Change Data Capture endpoint, so you only fetch records that changed since your last run. Store the maximum LastUpdatedTime you saw as a watermark, resume from it next time, and overlap the window by a few minutes so a commit that landed out of clock order is never skipped.

Every QuickBooks entity carries a MetaData block with a LastUpdatedTime. A typical run reads the stored watermark, issues SELECT * FROM Invoice WHERE MetaData.LastUpdatedTime > '2026-07-19T00:00:00-07:00' STARTPOSITION 1 MAXRESULTS 1000, advances STARTPOSITION by the page size until a page returns fewer than 1000 rows, then moves the watermark forward to the max LastUpdatedTime returned. The Change Data Capture endpoint is the denser alternative: one call to /cdc names several entity types and a single since-timestamp, and Intuit returns everything of those types that changed, including records that were soft-deleted. CDC is the better tool for catching deletes and for keeping many small tables fresh in one request, while the per-entity watermark gives you fine control over the busy transaction tables. Overlap protects correctness in both: subtract a few minutes from the watermark before you query, because re-reading a handful of rows costs nothing when the load is idempotent, which is the next section.

How do you handle the QuickBooks API rate limit?

Handle it by pacing requests under the per-realm limit rather than firing as fast as you can and reacting to failures. Intuit caps a company at roughly 500 requests per minute per realm and about 10 concurrent requests, so budget your paging against those numbers, keep a small concurrency pool, and when a throttle response comes back, back off and retry from the same page instead of hammering the endpoint.

The model is per realm, which has two consequences. First, denser calls win: pulling 1000 rows a page with MAXRESULTS and filtering on the watermark means a steady-state run touches only the rows that changed, which is a tiny fraction of the ledger, so you rarely approach the ceiling at all. Second, concurrency is shared across everything hitting that realm, including any other integration the finance team runs, so keep your pool well under 10 and treat throttling as expected rather than exceptional. Wrap each request in retry logic with exponential backoff that recognizes rate errors, waits, and resumes from the same STARTPOSITION so no page is skipped or double read. Reserve full-table reloads for the initial backfill, and run that backfill in chunks by entity type so no single window saturates the minute budget. If you later add a warehouse target the same pacing discipline applies to QuickBooks to Snowflake.

How do you keep a QuickBooks Postgres sync from creating duplicates?

Make the load idempotent. Give every table a primary key on the QuickBooks Id, land each batch in a staging table, and upsert with INSERT ... ON CONFLICT (id) DO UPDATE SET .... Because the id is stable within a realm, replaying an overlapping LastUpdatedTime window or retrying a failed batch updates the existing row in place rather than inserting a second copy, so the target converges to the same state a clean run would produce.

The load has a consistent shape. Land each page in staging, then upsert from staging into the target in one statement so inserts and updates happen together. Lines need the same discipline one level down: when an invoice is edited, its Line set can change, so a plain upsert on line id leaves orphaned rows for lines that were removed. The clean pattern is to delete all qbo_invoice_line rows for the invoice ids in the batch and reinsert them inside the same transaction as the invoice upsert, so an invoice and its children are never briefly inconsistent. The same applies to LinkedTxn rows in the link table.

Deletes are the case a watermark can never catch. QuickBooks soft-deletes: when a record is voided or removed its status becomes Deleted, and the CDC endpoint returns it so you can mark the local row deleted. But a record that is hard-absent, gone with no tombstone, will never appear in a LastUpdatedTime query, so your replica quietly keeps rows the company no longer has. Two mitigations, and you want both: read CDC so soft-deletes flow through as status changes, and run a periodic reconciliation that pulls the current id set for each entity over a trailing window and flags local ids that Intuit no longer returns. Keep a loaded_at column on every table so freshness checks and drift debugging are a one-line query.

How often should you sync QuickBooks to your database?

For most finance and ops teams, every fifteen to sixty minutes on the LastUpdatedTime watermark is the right steady state, with a nightly wider CDC and reconciliation pass to catch deletes. Match the interval to the decision the data drives: cash and receivables dashboards want fifteen minutes, while month-end close and cohort reporting are fine hourly or daily. Syncing more often than anyone reads just spends per-realm request budget for nothing.

Backfilling a company is a different job from the steady state and should be built as one. Run a full query reload per entity, chunk the large transaction tables by TxnDate range so each chunk is a bounded paging walk you can retry independently, and record which chunks completed so a failure at 80 percent resumes rather than restarts. Load into staging first, then upsert. Only after the backfill lands should you set the watermark, and set it to the start time of the backfill rather than its end so anything that changed while the backfill was running gets picked up on the first incremental pass. Metered per-row pipeline pricing interacts badly with sync frequency and with a busy month-end, since both push row counts up exactly when you least want a surprise invoice. Our flat pricing is by plan rather than per row, so a heavy close does not change the bill. If you want to see the pair running against your own company before you wire anything up, book a walkthrough or map it yourself with the QuickBooks to Postgres connector.