Xero to Postgres: how to replicate accounting data your app can actually query

How do you connect Xero to Postgres?

You connect Xero to Postgres by authenticating to the Xero Accounting API with OAuth 2.0, reading the objects you care about for each connected organization, and writing them into Postgres tables, because Xero has no native database export. Something sits in the middle: a managed connector, or a scheduled job that refreshes tokens, pages through each endpoint, and upserts every batch.

The OAuth flow returns a 30-minute access token plus a refresh token, and a xero-tenant-id that identifies the specific organization you are reading. Every request carries that tenant id, which matters the moment you have more than one company, because you loop the tenants and pull each one separately. From there you page through each endpoint (100 records per page via the page parameter, or the offset cursor on a few endpoints), stay under the rate limits, and land the rows. A first run is a full backfill of every object; after that you switch to incremental so you are not re-reading the whole ledger every night. If you would rather not own the token refresh, paging, retry, and upsert logic, the Xero to Postgres integration runs all of it as a managed pipeline on a schedule you set.

Extraction method	What it is	When it fits and what it costs you
Full page walk	Paged reads of every record, 100 per page via the page parameter, walked until a short page comes back	The first backfill and any object with no reliable watermark. Slow and call-heavy, so it draws down the daily cap fast
Incremental watermark	The If-Modified-Since header and/or a filter on UpdatedDateUTC, so only records changed since your last run come back	The steady-state default. Small deltas keep you well under the rate limits even as history grows
Xero webhooks	Xero POSTs your endpoint on events, but only for a limited set (Invoices, Contacts, and a few others)	A refresh hint for low latency. Best effort only, so verify the signature and treat it as a trigger to re-read, never as the source of truth
Reports endpoints	Bulk export of pre-aggregated views such as trial balance, aged receivables, and profit and loss	Useful for tie-out and reconciliation totals. Not a row-level replica, so it complements the object reads rather than replacing them
Managed sync tool	A hosted connector that handles OAuth refresh, tenant loops, paging, throttling, and idempotent loads	When you would rather not own the retry and backfill logic. Less control over exact field selection

Most production setups combine two of these: a full page walk for the initial backfill, then the UpdatedDateUTC watermark on every subsequent run, with webhooks layered on when a feature needs an invoice in the database within seconds. If your reporting lives in a warehouse rather than an application database, the sibling path swaps the target for BigQuery and the tradeoffs are covered in the Xero to BigQuery guide.

Does Xero have an API to export data to a database?

Yes, Xero exposes the Accounting API, but not a database. There is no SQL endpoint, no read replica, and no direct credentials. The only programmatic access is the REST Accounting API over OAuth 2.0, plus CSV and PDF exports from the web app. To run arbitrary SQL against your ledger, you replicate the API objects into a database you control, such as Postgres.

That gap is the whole reason this pipeline exists. Calling the Xero API live from a dashboard is slow and fragile: responses are paged 100 records at a time, requests are rate limited to roughly 60 per minute per tenant, and a report that joins invoices to contacts to payments turns into hundreds of round trips. Worse, you cannot join API responses to your own systems. Once the ledger sits in Postgres, attributing revenue to your internal subscription plans, or reconciling Xero payments against your billing tables, is one join instead of an afternoon of pagination code. Anything that mixes accounting data with your product, CRM, or billing systems needs one place to live, which is what a data integration platform is for.

How Xero objects map to Postgres tables and types

Model invoices and their line items as separate tables. Flattening line items into the invoice row (as a JSON blob or as item_1_amount, item_2_amount columns) destroys line-level analysis: you cannot group spend by account code, tie to a tracking category, or answer which line drove a variance without unnesting on every query. A child table keyed to the invoice id keeps all of that as ordinary SQL. Plan on tables for invoices, invoice line items, contacts, payments, credit notes, bank transactions, accounts, manual journals, items, and tracking categories. The Type field on an invoice is how you split the two directions: ACCREC is a sales invoice (accounts receivable) and ACCPAY is a bill (accounts payable), so filter on it rather than keeping two pipelines.

Xero field	Postgres table.column	Type and notes
Invoice InvoiceID	invoices.invoice_id	UUID primary key (GUID, not an integer). The upsert conflict target
Invoice InvoiceNumber	invoices.invoice_number	TEXT. Human-facing label such as INV-1042, no arithmetic
Invoice Type	invoices.type	TEXT. ACCREC (sales / AR) or ACCPAY (bill / AP). Filter on it to split directions
Invoice Total	invoices.total	NUMERIC(19,4). Arrives as a JSON number. Never cast to float
Invoice AmountDue	invoices.amount_due	NUMERIC(19,4). Outstanding balance, drives aged receivables
Invoice AmountPaid	invoices.amount_paid	NUMERIC(19,4). Settled so far against the invoice
Invoice Contact.ContactID	invoices.contact_id	UUID FK to contacts.contact_id. Flatten the nested contact to the id
Invoice Date	invoices.date	DATE. Issue date, used for period grouping
Invoice DueDate	invoices.due_date	DATE. Drives aging buckets and overdue logic
Invoice Status	invoices.status	TEXT. DRAFT, SUBMITTED, AUTHORISED, PAID, VOIDED
Invoice LineItems	invoice_line_items (child table)	Own table keyed to invoice_id. Do not flatten, or line-level analysis breaks
Payment Amount	payments.amount	NUMERIC(19,4). Cash applied on this payment
Payment Invoice.InvoiceID	payments.invoice_id	UUID FK to invoices.invoice_id. Links the payment to what it settled
Account Type	accounts.type	TEXT. REVENUE, EXPENSE, ASSET, and so on, for GL grouping
Account Code	accounts.code	TEXT. Chart-of-accounts code, the join key for reports
Contact Name	contacts.name	TEXT. Customer or supplier display name
UpdatedDateUTC	*.updated_utc	TIMESTAMPTZ in UTC. The incremental watermark. Index it on every table
CurrencyCode	invoices.currency_code	TEXT (ISO 4217). Carry with CurrencyRate for multi-currency

The date trap deserves its own note, because it is the most common source of timestamps that are silently wrong. Xero returns UpdatedDateUTC and related fields as Microsoft JSON date strings such as /Date(1499472000000+0000)/, which is milliseconds since the Unix epoch with an offset. Parse the number to a real TIMESTAMPTZ in UTC on ingest; if you store the raw string, every date comparison and every watermark filter breaks. Keep it in UTC as Xero sends it and convert to a local reporting zone in the query layer, not on load.

Money has its own discipline. Xero returns amounts as JSON numbers, and if your loader parses them as floats you inherit binary floating point rounding, so summing tens of thousands of invoice totals drifts by cents that finance will eventually notice. Store every amount as NUMERIC(19,4) and let Postgres hold exact decimals. For multi-currency organizations, keep CurrencyCode and CurrencyRate next to the amount so you can hold the transaction currency and the base currency separately, and make every revenue query state which basis it uses instead of blending euros and dollars into one plausible-looking total.

How do you sync Xero incrementally with UpdatedDateUTC?

You sync incrementally by storing the maximum UpdatedDateUTC you have already seen per object, then asking Xero for only the records changed since that timestamp. Xero supports this two ways: the If-Modified-Since request header, and a filter on the UpdatedDateUTC field. Either pulls a small delta each run instead of the entire ledger.

This is not a nice-to-have. A Xero invoice does not sit still: it moves from DRAFT to AUTHORISED to PAID, it can be VOIDED, and it can be reduced by a credit note weeks after it was raised. A naive nightly dump that only inserts new ids will miss every one of those after-the-fact edits, and your receivables numbers slowly drift from what Xero shows. The watermark catches them because any change bumps UpdatedDateUTC, so the record comes back in the next delta. Because AmountDue on ACCREC invoices moves as payments land, a fresh replica is also what lets an accounts-team tool automatically chase the invoices that are still unpaid without anyone re-keying balances by hand.

Once the delta lands in a staging table, you upsert it into the target with INSERT ... ON CONFLICT (invoice_id) DO UPDATE keyed on the Xero GUID. The upsert is what makes the pipeline safe to retry: if a run fails halfway or two runs overlap, the same InvoiceID updates the existing row rather than inserting a second copy. Guard the update with WHERE excluded.updated_utc > invoices.updated_utc so an out-of-order or replayed page never overwrites a newer state with an older one, and store the new high-water mark only after the transaction commits, so a crash mid-load re-reads the same window rather than skipping it.

How do you handle the Xero API rate limit?

Handle it by pacing requests under the caps rather than sending as fast as you can and reacting to failures. Xero allows roughly 60 calls per minute and 5,000 calls per day per tenant, plus an app-wide concurrent-call limit across all tenants. A well-behaved job spaces its calls, keeps concurrency low, and backs off on an HTTP 429 instead of hammering the API.

The model has three limits working together. The per-minute cap smooths bursts, the daily cap bounds total volume per organization, and the concurrent limit stops one app from opening too many parallel connections at once. When you loop tenants for a multi-org sync, remember the concurrent limit is shared across the whole app, so ten organizations pulling in parallel can trip it even when each one is under its own per-minute cap. The incremental design helps on every axis: smaller deltas mean fewer calls, so you stay comfortably under all three even as history grows.

Two practical consequences. First, pagination is page based at 100 records per page for most endpoints, with an offset cursor on a few, so a large backfill is many sequential calls and you should budget it against the daily cap rather than trying to burst it. Second, when a 429 does come back, honor the retry hint Xero returns instead of retrying immediately, and resume from the same page so no record is skipped or read twice. Building the backoff, the tenant loop, and the concurrency ceiling correctly is most of the work in a hand-rolled Xero sync.

How do you consolidate multiple Xero organizations in one database?

You consolidate multiple Xero organizations by loading each one through its own xero-tenant-id, stamping every row with a tenant_id (or org) column, and writing them all into shared tables. Because Xero is strictly per organization, there is no cross-entity report inside Xero itself. The database is where group reporting actually happens.

The tenant column is not optional, it is a correctness requirement. Two entities can both have an invoice numbered INV-1001, and both can have an account called "Sales" at different codes. If you load them into shared tables without a tenant_id on every row, a consolidated query silently blends two companies and a group total is wrong in ways that are hard to spot. Put tenant_id in the primary key or a unique constraint alongside the Xero GUID, and every rollup can either group by entity or sum across the group on purpose.

The harder part is rarely the load, it is the chart of accounts. The fix is a mapping layer: a crosswalk from each org's local account code to a shared group account, applied so every downstream model reads one consistent hierarchy. Handle currency at the same layer. If entities report in different currencies, store the original amount, CurrencyCode, and CurrencyRate as loaded, then apply a rate table in the query to produce a group-currency column, keeping the transaction and base amounts separate throughout.

How often should you sync Xero to your database?

For most finance teams, every few hours on the UpdatedDateUTC watermark is the right steady state, with a nightly wider reconciliation pass. Match the interval to the decision the data drives: cash and collections dashboards want a few hours, while month-end close and margin reporting are fine nightly. Syncing more often than anyone reads just spends daily-cap budget for nothing.

Deletes and voids are the case a watermark handles well but a delete-blind loader gets wrong. When Xero voids or deletes an invoice, it does not vanish: it still returns from the API with a status of VOIDED or DELETED, and its UpdatedDateUTC bumps, so it flows through the next delta. Keep the row and rely on the status column rather than deleting locally; a VOIDED invoice with a nonzero total is still meaningful history. Records that are hard-gone (a rare permanent removal) will not come back at all, so run a periodic reconciliation that pulls the current id set for a trailing window and flags local ids Xero no longer returns.

Backfilling is a different job from the steady state and should be built as one: page-walk each object in full, load into staging first, then upsert, and only after the backfill lands do you set the watermark to the backfill start time so anything that changed mid-load gets caught on the first incremental pass. Metered per-row pipeline pricing interacts badly with sync frequency, so our flat pricing is by plan rather than per row, and month-end volume does not change the bill. If you want to see the pair running against your own ledger before you wire anything up, book a walkthrough or map it yourself with the Xero to Postgres connector.