DEV Community: ilshaad

How to Sync Billing Data to DigitalOcean Managed PostgreSQL

ilshaad — Mon, 10 Aug 2026 13:31:08 +0000

Sync Stripe, QuickBooks, Xero or Paddle into DigitalOcean Managed PostgreSQL. Flat monthly database pricing, no ETL bill that grows with your row count.

By Ilshaad Kheerdali · 10 August 2026

Most guides about getting billing data into Postgres skip the part people actually care about, which is what the bill looks like at the end of the month. You can build a perfectly good pipeline and still be unpleasantly surprised, because the database is priced per hour, the pipeline is priced per row, and neither number is knowable in advance.

DigitalOcean Managed PostgreSQL is a good answer to half of that problem. It has a flat monthly price with a small, published list of tiers, and you know what you owe before you start. This guide covers the other half: getting Stripe, QuickBooks, Xero or Paddle data into it without adding a variable-cost pipeline on top.

Why DigitalOcean for Billing Data

Billing data has an unusual shape. It is small (most SaaS companies are talking about thousands of rows, not millions), it changes slowly, and it gets queried constantly because it sits behind every revenue question anyone asks. That profile is a poor fit for infrastructure priced on throughput and a good fit for a fixed monthly box.

Here is what DigitalOcean actually charges for Managed PostgreSQL:

RAM	vCPUs	Storage	Monthly
1 GiB	1	10-30 GiB	$15.15
2 GiB	1	30-60 GiB	$30.45
4 GiB	2	60-120 GiB	$60.90
8 GiB	4	140-280 GiB	$122.10
16 GiB	6	290-580 GiB	$244.35

Extra storage beyond the base allocation is $0.215 per GiB per month.

The detail worth knowing, and the one most comparison posts miss: traffic to and from DigitalOcean managed databases does not count against your bandwidth transfer allowance. That is DigitalOcean's own wording, not an inference. For a workload that repeatedly pulls data in on a schedule, that removes an entire category of surprise line item. It is one of the few places where a sync workload is genuinely cheaper to run than the marketing implies.

What you give up compared to AWS RDS is the deep ecosystem: no IAM database authentication, fewer regions, no Aurora-style scaling story. For a billing table that nobody is sharding, none of that matters. If you are already on RDS and want that route instead, we covered it in syncing billing data to AWS RDS.

The DigitalOcean Connection String Gotcha

This is where most first attempts fail, and it has nothing to do with your billing provider.

DigitalOcean does not use port 5432. It issues exactly two ports, and picking the wrong one produces a confusing failure rather than a clear error:

25060 is the direct connection to the database
25061 is the connection pool, if you have created one

A DigitalOcean connection string looks like this:

postgresql://doadmin:password@db-postgresql-lon1-12345-do-user-123456-0.k.db.ondigitalocean.com:25060/defaultdb?sslmode=require

Three things to get right:

The port. If you created a connection pool and paste the pool's credentials with port 25060, or the direct credentials with 25061, the connection fails in a way that reads like a password problem. Copy the whole string from the DigitalOcean control panel rather than assembling it by hand.
sslmode=require. DigitalOcean enforces SSL. The parameter is already in the string the control panel gives you, so the usual cause of losing it is retyping the string or trimming query parameters.
Trusted sources. Under your database cluster's Network Access tab, DigitalOcean lets you restrict inbound connections to specific Droplets, Kubernetes clusters, App Platform apps, IP addresses or tags. If you have added any trusted sources, an external service cannot reach the database until it is allowed too, so check this tab first when a connection test fails for no obvious reason.

If a connection string is not behaving and you would rather not guess, paste it into our free PostgreSQL connection string validator. It parses the host, flags whether you are on a direct or pooled port, and tells you what is missing. It handles DigitalOcean's ports specifically.

Connecting Your Billing Provider

Once the database is reachable, the rest is short. In Codeless Sync, create a sync configuration, pick Digital Ocean as the database platform, and paste the connection string. It is validated before you continue, so you find out immediately if the port or SSL parameter is wrong.

Then authorize the provider you want to pull from:

Stripe and Paddle use an API key. For Stripe, use a restricted key with read-only permissions rather than your secret key.
QuickBooks and Xero use OAuth, so you approve access on their consent screen and never handle a token yourself.

Choose a data type (Customers is the easiest to eyeball first), let the destination table be created automatically, and run the sync. The full walkthrough with screenshots lives in the quick start guide if you want it step by step.

The table naming is predictable: stripe_customers, quickbooks_invoices, xero_contacts, paddle_subscriptions, and so on. Each provider gets its own tables, so you can run several providers into one cluster without them colliding.

Will It Fit on the $15 Plan?

This is the question the pricing table does not answer, and it is worth two minutes before you commit.

The entry tier gives you 10 GiB of storage. Billing tables are mostly short text fields, timestamps and numerics, plus a JSONB column holding the original API object. That JSONB column is the one that actually consumes space, because it stores the complete record rather than the handful of columns you query.

After your first sync, ask Postgres directly:

SELECT
  relname AS table_name,
  to_char(n_live_tup, 'FM999,999,999') AS approx_rows,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_stat_user_tables
WHERE relname LIKE 'stripe\_%'
   OR relname LIKE 'quickbooks\_%'
   OR relname LIKE 'xero\_%'
   OR relname LIKE 'paddle\_%'
ORDER BY pg_total_relation_size(relid) DESC;

One caveat: n_live_tup is an estimate maintained by autovacuum, so straight after a first sync it can still read zero. Run ANALYZE; first if the row counts look wrong. The size column is always accurate.

To turn that into a forecast, work out the space per thousand rows and multiply by where you expect to be in a year:

SELECT
  pg_size_pretty(SUM(pg_total_relation_size(relid))) AS billing_data_total,
  pg_size_pretty(
    SUM(pg_total_relation_size(relid)) / NULLIF(SUM(n_live_tup), 0) * 1000
  ) AS per_1k_rows
FROM pg_catalog.pg_stat_user_tables
WHERE relname LIKE 'stripe\_%'
   OR relname LIKE 'quickbooks\_%'
   OR relname LIKE 'xero\_%'
   OR relname LIKE 'paddle\_%';

For most small and mid-sized SaaS businesses the answer comes back in tens or low hundreds of megabytes, which means the 10 GiB entry tier is not the constraint and will not be for years. Where the entry tier does bite is RAM: 1 GiB is fine for a billing table you query a few times a minute, and tight if this cluster is also serving your application. If billing data is sharing a database with production traffic, start at 2 GiB.

What This Actually Costs End to End

Putting the two halves together, for a single small billing sync:

	Monthly
DigitalOcean Managed PostgreSQL, entry tier	$15.15
Codeless Sync Free (manual syncs, 2 per day)	$0
Total, manual	$15.15
Codeless Sync Pro (scheduled syncs, 40 per day)	$29
Total, scheduled and hands-off	$44.15

Both numbers are the same every month regardless of how many rows move, which is the whole point. Usage-priced ETL tools bill on rows changed, so your invoice tracks your growth in a way you cannot forecast at the start of the month, and a busy billing month costs more precisely when you are least inclined to audit it.

To be fair to the alternatives: if you are syncing one small provider and stay inside a generous free tier, a usage-priced tool can be cheaper than $29. The trade is predictability, and it flips as soon as you add a second or third provider, because per-connector pricing means each one is metered separately.

Keeping It Current

A one-off sync is enough to explore. For anything you actually depend on, put it on a schedule so nobody has to remember it.

Scheduled syncs are a paid feature. When you create a schedule you also pick the sync mode: a full sync, which is the default and re-reads everything, or an incremental window that fetches only records changed in the last minute, day, 7 days or 30 days. Matching the window to the cadence is what keeps each run short and keeps load on a 1 GiB cluster negligible, so a daily schedule pairs naturally with the last-day window.

Different providers can run at different cadences. Stripe customers hourly and QuickBooks invoices daily is a reasonable default, since invoices genuinely do not change often enough to justify hourly polling. If you want help choosing, cron expressions for scheduled data syncs covers the syntax.

Frequently Asked Questions

Does Codeless Sync work with DigitalOcean's connection pool?

Yes. Both the direct port (25060) and the pool port (25061) work. Use whichever the control panel gives you, and make sure the port matches the credentials, since pool and direct connections have separate ones.

Do I need to add Codeless Sync to my trusted sources?

If you have added trusted sources to the cluster, yes. Trusted sources live under the Network Access tab and act as a firewall, so any source not on the list is refused. If you have never touched that tab, there is nothing to add. It is the first place to look when the connection test fails but the connection string is definitely correct.

Will syncing count against my DigitalOcean bandwidth?

No. Traffic to and from managed databases does not count against your account's bandwidth transfer allowance, so sync frequency does not create a bandwidth charge.

Can I sync more than one billing provider into the same cluster?

Yes. Each provider writes to its own prefixed tables (stripe_, quickbooks_, xero_, paddle_), so they coexist in one database and can be joined together in a single query.

Is the 1 GiB entry tier enough?

For billing data alone, almost certainly. Storage is rarely the limit because billing tables are small. RAM is the thing to watch, and if the cluster is also serving application traffic, start one tier up at 2 GiB.

How is this different from syncing to AWS RDS?

Mainly cost predictability and setup effort. RDS needs VPC and security group configuration before anything external can connect; DigitalOcean needs the right port and an SSL parameter. RDS gives you a deeper ecosystem you probably do not need for a billing table.

Wrapping Up

DigitalOcean Managed PostgreSQL is a sensible home for billing data specifically because it is boring: a fixed price, a short list of tiers, and no bandwidth meter running in the background. Adding a usage-priced pipeline on top would reintroduce the unpredictability you picked it to avoid.

Get the connection string right, pick your provider, and the data lands as ordinary tables you can query with the tools you already use.

Try it at codelesssync.com

Related:

Xero API Integration Guide (2026): OAuth, Tenants, and Your First Query

ilshaad — Tue, 04 Aug 2026 09:41:44 +0000

Step-by-step Xero API integration: OAuth 2.0, tenant routing, paging, rate limits, the 2026 scope and pricing changes, plus a no-code path to PostgreSQL.

By Ilshaad Kheerdali · 4 August 2026

The Xero API is well documented and pleasant to work with once it clicks, but the first integration always takes longer than people expect. There is an extra discovery step that most accounting APIs don't have, tokens expire faster than you'd guess, and 2026 brought two changes that alter how you scope and budget an integration.

This guide walks the whole flow: creating an app, running OAuth 2.0, resolving which organisation you're actually talking to, making your first call, paging through results, staying inside the rate limits, and pulling incremental updates. At the end it covers what changed in 2026 and the shortcut if the plumbing isn't the part you want to own.

Everything below targets the Xero Accounting API over OAuth 2.0. Xero retired OAuth 1.0a some years ago, so any tutorial you find that mentions consumer keys and signed requests is out of date.

What the Xero API Is

The Xero Accounting API is a REST API that returns XML by default and JSON if you ask for it. You read and write accounting entities: Invoices, Contacts, Payments, BankTransactions, Accounts, CreditNotes, Items, PurchaseOrders, ManualJournals and a few dozen more, plus a set of report endpoints.

The thing that surprises most developers coming from Stripe or QuickBooks is the tenant model. A single Xero login can have access to many organisations: an accountant might be connected to two hundred client orgs. So authorisation and targeting are two separate concerns. Your token proves the user said yes, and a separate header tells Xero which organisation the call is for.

That means every integration has a step that a Stripe integration simply doesn't: after you get a token, you have to ask Xero which tenants that token can reach.

Step 1: Create a Xero App

Sign in at the Xero Developer portal and create an app.
Pick the grant type. Auth Code is what you want for a web server app that can keep a client secret safe. Native desktop and mobile apps use PKCE instead, and single page apps are not supported at all. Custom Connections are the machine-to-machine option for a single organisation, which suits internal tooling.
Set your redirect URI. It has to be an absolute HTTPS address and match exactly what you send during OAuth. http://localhost/ is allowed for local testing, but http://127.0.0.1 is not. You can register up to 50 per app.
Copy the Client ID and generate a Client Secret.

Xero gives you a Demo Company with realistic data, which is the sane place to develop. There is no separate sandbox host: you point at the same production API and authorise against the demo org.

Step 2: Authenticate with OAuth 2.0

Xero uses the standard authorization code flow. Send the user to Xero, they pick which organisation to connect, and you exchange the returned code for tokens.

// 1. Build the consent URL
const authUrl = new URL('https://login.xero.com/identity/connect/authorize');
authUrl.searchParams.set('response_type', 'code');
authUrl.searchParams.set('client_id', process.env.XERO_CLIENT_ID!);
authUrl.searchParams.set('redirect_uri', process.env.XERO_REDIRECT_URI!);
authUrl.searchParams.set(
  'scope',
  'openid profile email accounting.contacts.read accounting.invoices.read offline_access',
);
// CSRF guard. State only protects you if you store it and compare it on return.
const state = crypto.randomUUID();
await saveOAuthState(state);
authUrl.searchParams.set('state', state);

// 2. Exchange the code in your redirect handler
app.get('/callback', async (req, res) => {
  const expected = await takeOAuthState(); // read it once, then delete it
  if (!expected || req.query.state !== expected) {
    return res.status(400).send('Invalid state');
  }

  const basic = Buffer.from(
    `${process.env.XERO_CLIENT_ID}:${process.env.XERO_CLIENT_SECRET}`,
  ).toString('base64');

  const tokenRes = await fetch('https://identity.xero.com/connect/token', {
    method: 'POST',
    headers: {
      Authorization: `Basic ${basic}`,
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: new URLSearchParams({
      grant_type: 'authorization_code',
      code: req.query.code as string,
      redirect_uri: process.env.XERO_REDIRECT_URI!,
    }),
  });

  const token = await tokenRes.json();
  // token.access_token  -> 30 minutes
  // token.refresh_token -> rotates on every use
});

Four details worth internalising before you build anything on top of this:

offline_access is not optional. Leave it out of your scope string and Xero won't issue a refresh token at all. You'll get thirty minutes of access and then a dead integration, which is a confusing thing to debug at 5pm.
The authorization code is single use and expires 5 minutes after issuance. If your callback handler is slow or you retry the exchange, you'll be looking at an invalid_grant error rather than an obvious timeout.
Access tokens last 30 minutes. Short, so refresh logic isn't something you can defer to "later". The id_token expires even faster, at 5 minutes.
Refresh tokens rotate. Every refresh returns a new refresh token and the old one stops working. Persist the new value in the same transaction you use it, or a crash mid-refresh will lock that connection out. Unused refresh tokens expire after 60 days. If a refresh call fails without a response, Xero lets you retry with the existing refresh token for a grace period of 30 minutes before you have to send the user back through consent.

async function refresh(storedRefreshToken: string) {
  const basic = Buffer.from(
    `${process.env.XERO_CLIENT_ID}:${process.env.XERO_CLIENT_SECRET}`,
  ).toString('base64');

  const res = await fetch('https://identity.xero.com/connect/token', {
    method: 'POST',
    headers: {
      Authorization: `Basic ${basic}`,
      'Content-Type': 'application/x-www-form-urlencoded',
    },
    body: new URLSearchParams({
      grant_type: 'refresh_token',
      refresh_token: storedRefreshToken,
    }),
  });

  const token = await res.json();
  await saveTokens(token); // token.refresh_token has changed. Save it.
  return token.access_token;
}

Step 3: Resolve the Tenant

This is the step people miss. Your access token does not identify an organisation, so before you can read anything you ask Xero what the token can reach:

const res = await fetch('https://api.xero.com/connections', {
  headers: {
    Authorization: `Bearer ${accessToken}`,
    'Content-Type': 'application/json',
  },
});

const connections = await res.json();
// [{
//   id: 'e1eede29-...',        // connection id, used to DELETE the connection
//   authEventId: 'd99ecdfe-...',
//   tenantId: '70784a63-...',  // this is what goes in the header
//   tenantType: 'ORGANISATION',
//   tenantName: 'Maple Florist',
//   createdDateUtc: '2019-07-09T23:40:30.1833130',
//   updatedDateUtc: '2020-05-15T01:35:13.8491980',
// }]
const tenantId = connections[0].tenantId;

Store tenantId next to the tokens. Every subsequent API call carries it in the Xero-tenant-id header. If it is missing, malformed, or not authorised for that token, Xero returns 403 Forbidden with "detail": "AuthenticationUnsuccessful", which is easy to misread as an expired token when the token is fine. The genuinely silent failure is the other one: a tenant the token is authorised for but that belongs to a different organisation returns 200 with that org's data, so a mixed-up tenant looks like missing records rather than an error.

Two things make this step subtler than it looks. First, the endpoint returns every tenant the user has ever connected, not just the ones authorised in this flow. If you want only the new ones, decode the authentication_event_id claim from the access token JWT and filter with ?authEventId=.... Second, tenantType is not always ORGANISATION: a user can connect a Practice Manager account too, and those entries come back with a null tenantName.

If your user connects several organisations, you'll need a way for them to pick. That is a genuine product decision, not just plumbing: agencies and bookkeepers routinely connect dozens of orgs, and as Step 6 shows, each one carries its own separate rate-limit budget.

Step 4: Make Your First API Call

The Accounting API lives under https://api.xero.com/api.xro/2.0/. Three headers on every request:

const BASE = 'https://api.xero.com/api.xro/2.0';

const res = await fetch(`${BASE}/Invoices`, {
  headers: {
    Authorization: `Bearer ${accessToken}`,
    'Xero-tenant-id': tenantId,
    Accept: 'application/json', // omit this and you get XML
  },
});

const { Invoices } = await res.json();
console.log(Invoices.length, Invoices[0]?.InvoiceNumber);

Two quirks to expect. Endpoints and response fields are PascalCase (Invoices, InvoiceNumber, AmountDue), which trips up anyone auto-mapping into snake_case Postgres columns. And dates come back in Microsoft's legacy JSON format, /Date(1712345678000+0000)/, so plan on parsing them rather than passing them straight into a timestamp column.

For filtering, Xero uses a where query parameter with its own expression syntax. It has to be percent encoded before you append it, and so does the space in an order clause:

const where = encodeURIComponent('Status=="AUTHORISED"&&Type=="ACCREC"');
const order = encodeURIComponent('Date DESC');
const url = `${BASE}/Invoices?where=${where}&order=${order}`;

Keep where clauses simple. Xero's own advice is to stick to == comparisons where you can, because long or complex expressions time out against larger organisations. The busiest endpoints also have dedicated parameters that are faster than a where: Invoices?Statuses=AUTHORISED, Invoices?ContactIDs=..., and IDs=... all take comma-separated lists. There's also summaryOnly=true on Invoices and Contacts, which strips the computation-heavy fields and is worth using when you're just building an index.

Step 5: Page Through Results

Xero pages with a simple page parameter, and pageSize controls how many records come back. The default is 100 and the maximum is 1,000, so always set it explicitly or you'll make ten times the calls you need to. Values outside the range are clamped rather than rejected, so pageSize=5000 quietly gives you 1,000.

Paging is available on Invoices, Contacts, CreditNotes, BankTransactions, ManualJournals, Payments, PurchaseOrders, Prepayments and Overpayments. It's worth using even when you don't need the volume: on several endpoints the unpaged response is a summarised version, so paged results come back with extra detail such as line items, and you avoid a follow-up request per record.

async function fetchAllInvoices(accessToken: string, tenantId: string) {
  const all = [];
  let page = 1;

  while (true) {
    const res = await fetch(
      `${BASE}/Invoices?page=${page}&pageSize=1000`,
      {
        headers: {
          Authorization: `Bearer ${accessToken}`,
          'Xero-tenant-id': tenantId,
          Accept: 'application/json',
        },
      },
    );

    const body = await res.json();
    all.push(...body.Invoices);

    // Paged responses carry a pagination object: page, pageSize, pageCount, itemCount
    if (page >= body.pagination.pageCount) break;
    page++;
  }

  return all;
}

The pagination object supersedes the older approach of fetching pages until one comes back short. If you're maintaining code that still does that, it works, but the object tells you itemCount and pageCount up front, which is what you want for progress reporting and for deciding whether a backfill will fit inside the daily call budget.

Step 6: Handle Rate Limits and Errors

Xero's limits are considerably tighter than most billing APIs, and they stack. As of 2026:

Limit	Value	Scope
Concurrent	5 calls in progress	Per organisation, per app
Per minute	60 calls	Per organisation, per app
Per day	1,000 calls on the Starter tier, 5,000 on Core and above	Per organisation, per app
App-wide	10,000 calls per minute	Across all your connections

The daily cap is the one that bites, and note that it is tiered, which is easy to miss because most write-ups quote a flat 5,000. On the free Starter tier you get 1,000 calls per day per organisation. A full backfill of a busy org, paged at 100 records because you forgot pageSize, can exhaust that before it finishes, and you are then locked out of that tenant until the window resets. The limits are per tenant rather than per app, so ten connected organisations means ten separate daily budgets.

You don't have to guess where you stand. Every response carries X-DayLimit-Remaining, X-MinLimit-Remaining and X-AppMinLimit-Remaining, so a scheduler can back off before it trips anything.

Exceeding a limit returns HTTP 429 with an X-Rate-Limit-Problem header naming which of the four you hit. For the minute and daily limits you also get a Retry-After header with the seconds to wait. The windows are fixed and reset at different times per tenant, so Retry-After is the only reliable signal:

if (res.status === 429) {
  const problem = res.headers.get('X-Rate-Limit-Problem'); // which limit tripped
  const retryAfter = res.headers.get('Retry-After');

  // Concurrent-limit 429s have no Retry-After. Back off briefly and retry.
  const waitSeconds = retryAfter ? Number(retryAfter) : 5;
  console.warn(`Hit ${problem} limit, waiting ${waitSeconds}s`);
  await new Promise((r) => setTimeout(r, waitSeconds * 1000));
}

Check Xero's current limits documentation before you size a backfill, since these numbers do get revised.

Step 7: Sync Incrementally with If-Modified-Since

Re-pulling everything on a schedule is exactly how you exhaust the daily cap. For updates, send the If-Modified-Since header with a UTC timestamp and Xero returns only records modified after it:

const res = await fetch(`${BASE}/Invoices?pageSize=1000`, {
  headers: {
    Authorization: `Bearer ${accessToken}`,
    'Xero-tenant-id': tenantId,
    Accept: 'application/json',
    'If-Modified-Since': '2026-07-01T00:00:00',
  },
});

Note the format: UTC, accurate to the second. The pattern is a full backfill once, then If-Modified-Since on a schedule with the watermark stored per tenant per endpoint. Xero explicitly recommends it for any endpoint with a large result set.

One caveat matters a lot if you're building a mirror of the data. The filter keys off UpdatedDateUTC, and Xero documents specific changes that don't bump that field: edits to partially paid transactions that don't generate a journal, such as DueDate or SentToContact, and Contact fields derived from elsewhere such as Balances, IsSupplier and IsCustomer. Records changed only in those ways will never appear in an incremental pull. If completeness matters, schedule a periodic full reconciliation pass rather than trusting the incremental stream indefinitely.

What Changed in 2026: Scopes and Pricing

Two changes landed in March 2026 that affect how you build and what it costs.

Granular scopes. Xero replaced three broad scopes with sixteen narrower ones. accounting.transactions splits into accounting.invoices, accounting.payments, accounting.banktransactions and accounting.manualjournals (plus the matching .read variants), and accounting.reports.read splits into eight report-specific scopes covering aged reports, balance sheet, bank summary, budget summary, executive summary, profit and loss, trial balance and tax reports. Scopes for settings, contacts, attachments and budgets were not affected.

Web and PKCE apps created from 2 March 2026 use granular scopes from the start, apps created before that date were assigned them by the end of April 2026, and custom connections followed on 29 April 2026. Broad scopes keep working until September 2027. The catch is that migration isn't a config change: because you're requesting a different permission set, every connected user has to consent again. Scopes are additive, so the usual approach is to update your authorisation link now, let re-authorisations and new organisations migrate people organically, then chase the remainder before the deadline.

Worth wiring up defensively: calling an endpoint whose granular scope you haven't requested returns a 401 with a WWW-Authenticate: insufficient_scope header, so catch that case specifically and prompt the user to update permissions rather than showing a generic auth error.

The API is no longer free. This is the bigger one, and it's easy to miss because most Xero tutorials predate it. Xero retired its revenue-share model on 2 March 2026 and replaced it with five tiers priced on two axes: how many organisations you're connected to, and how much data you pull.

Tier	Max connections	Monthly fee (AUD)	Included egress	Overage
Starter	5	Free	n/a	n/a
Core	50	$35	10 GB	$2.40/GB
Plus	1,000	$245	50 GB	$2.40/GB
Advanced	10,000	$1,445	250 GB	$2.40/GB
Enterprise	No limit	On application	On application	On application

Fees are tax exclusive, and allowances reset on the first of the calendar month (UTC). Billing is on data egress, so only data you download counts. Uploads into Xero are unlimited at every tier, and the Organisation endpoint is excluded from the calculation. Plus and above require app certification, and Advanced adds an annual security assessment, which also gates the premium endpoints: Journals, the Practice Manager API and bulk connections are Advanced-tier features.

Two things follow from this. First, an inefficient integration now has a line item. Paging at 100 instead of 1,000, re-pulling full history nightly, or syncing entities nobody queries used to be merely untidy, and now they show up on an invoice. Second, the free tier's 5-connection cap and 1,000 calls per day make it a testing tier rather than a production one, so budget for at least Core if you're shipping.

One more change landed alongside the pricing: the updated developer terms prohibit using data obtained from Xero's APIs to train, fine tune, adapt or enhance AI or ML models. If that was on your roadmap, read clause 7 of the developer platform terms and conditions before you build. Current tiers and allowances are on Xero's developer pricing page.

The Hard Parts

A first integration is very achievable. Keeping it healthy across many organisations is where the effort actually lands:

Token lifecycle at scale. Thirty-minute access tokens and rotating refresh tokens, per tenant, with safe persistence and a recovery path when a refresh fails.
Per-tenant rate budgets. Limits are per organisation, so a hundred connected orgs means a hundred separate budgets to schedule against without tripping the app-wide ceiling.
Schema mapping. PascalCase fields, /Date(...)/ timestamps, nested LineItems, and per-endpoint quirks all need flattening before the data is queryable.
Scope migration. If your app predates March 2026, the September 2027 deadline is a re-authorisation campaign across your entire user base.
Egress awareness. Sync design is now a cost decision, not just an engineering one.

If you're still weighing whether to build this at all, Xero API vs Database Sync works through the build-versus-buy maths in detail.

A Simpler Path: Sync Xero to PostgreSQL with No Code

If the goal is simply having Xero data in your own database to query and report on, none of the above is differentiating work. Codeless Sync connects Xero to your PostgreSQL database (Supabase, Neon, Railway, AWS RDS, or any Postgres host) in about five minutes. You authorise Xero once, pick your organisation, and CLS handles token refresh, tenant routing, paging, rate-limit backoff, incremental pulls and table creation. Data lands as clean relational tables:

-- Outstanding receivables by contact, oldest first
SELECT c.name,
       SUM(i.amount_due) AS outstanding,
       MIN(i.due_date)   AS oldest_due
FROM xero_invoices i
JOIN xero_contacts c ON c.id = i.contact_id
                    AND c.tenant_id = i.tenant_id
WHERE i.status = 'AUTHORISED'
  AND i.type = 'ACCREC'
  AND i.amount_due > 0
GROUP BY c.name
ORDER BY oldest_due ASC;

CLS also offers incremental pulls (last day, last 7 days, last 30 days) alongside a full sync, so once the backfill is done a scheduled run fetches only what changed rather than spending your daily call budget and egress allowance on history you already have. Pricing is a flat monthly fee rather than usage-metered, so your bill doesn't move when your invoice volume does. There's a free tier and no credit card required.

For the step-by-step version, see How to Sync Xero to PostgreSQL Automatically in 5 Minutes, or the Xero to PostgreSQL overview.

Frequently Asked Questions

Is the Xero API free to use?

Not really, since March 2026. Xero replaced its revenue-share model with five tiers priced on connection count and data egress, effective 2 March 2026. There is a free Starter tier, but it caps you at 5 connections and 1,000 API calls per day per organisation, which suits testing rather than production. The next step up is Core at $35 AUD per month for 50 connections and 10 GB of monthly egress. Only data you download counts toward the allowance; writing data into Xero is unlimited at every tier.

How long do Xero access tokens last?

Access tokens expire after 30 minutes. Refresh tokens rotate on every use and expire after 60 days if unused, so you must persist the new refresh token each time you refresh or you'll lose the connection.

What is the Xero-tenant-id header and why do I need it?

A Xero access token can have access to multiple organisations, so the token alone doesn't say which one you mean. You call GET https://api.xero.com/connections to list the tenants the token can reach, then pass the chosen tenantId in the Xero-tenant-id header on every Accounting API request.

What are the Xero API rate limits?

As of 2026: 5 concurrent calls, 60 calls per minute, and a daily cap that depends on your tier (1,000 calls per day on Starter, 5,000 on Core and above). Those are measured per organisation per app, so each connected tenant has its own budget. There is also an app-wide ceiling of 10,000 calls per minute across all connections. Exceeding any of them returns HTTP 429 with an X-Rate-Limit-Problem header naming the limit, plus a Retry-After header for the minute and daily limits.

Can I sync Xero data to PostgreSQL without writing code?

Yes. Codeless Sync handles the OAuth flow, tenant selection, token refresh, paging and schema mapping, writing Xero data straight into PostgreSQL tables. You authorise once and it stays in sync on a schedule.

Related:

How to Export Paddle Data to a Database

ilshaad — Mon, 27 Jul 2026 14:12:24 +0000

How to export Paddle data to a database: dashboard CSV reports, the Reports API, the Paddle API, Zapier, and no-code sync. Pros, cons, and real costs.

By Ilshaad Kheerdali · 27 July 2026

Paddle reports build asynchronously, carry up to 24 hours of lag, and delete themselves after 14 days. The API is fresher, but it hands you transactions 30 at a time behind a cursor. Neither one is an export, and if you run your billing on Paddle you have probably already hit that wall.

The awkward part is that "export Paddle data to a database" sounds like it should be a single button. It isn't. Five different methods exist, each solving a different slice of the problem, and most of them either go stale before you download them, expire before you use them, or leave you maintaining a pipeline that quietly breaks at 3am.

This guide walks through all five practical ways to get Paddle Billing data into a real, queryable database: dashboard CSV reports, the Reports API, the Paddle API directly, Zapier-style automation, and no-code sync. What each one costs, and where each one breaks.

Why Exporting Paddle Data Is Harder Than It Should Be

As a merchant of record, Paddle is the system of record for your revenue, not just another payment processor. It holds your customers, subscriptions, transactions, and adjustments, and it handles the sales tax filing on top. That makes a queryable local copy more valuable than it would be elsewhere, and it also makes the data harder to get at than most teams expect.

Built-in reports are asynchronous snapshots. Paddle Billing generates CSV reports for transactions, transaction line items, adjustments, adjustment line items, products and prices, discounts, checkouts, and payout reconciliation, but each report has to be built, then downloaded before it expires (files are kept for 14 days). And the data inside a report can be delayed by up to 24 hours, so even a fresh export isn't fully current.

There's no bulk "export everything" endpoint. Paddle's API is designed to run a billing system, so every entity (customers, subscriptions, transactions, products, prices) is its own set of cursor-paginated list calls. Page sizes vary by endpoint, and transactions cap at 30 records per page. A busy account means hundreds of sequential requests per entity, stitched back together in order.

Webhooks notify about new events only. Paddle notifications are push-based and start capturing from the moment you create the destination. Your historical transactions and existing subscribers never flow through them, so webhooks can't backfill a database. (For the full webhook trade-off, see Paddle Webhooks vs Database Sync.)

You configure everything twice. Sandbox and production are isolated Paddle environments with their own API keys, so a report you build or a pipeline you script against sandbox has to be recreated, and re-verified, against live data before you trust the numbers.

So the job falls to one of five approaches. Here is how each one holds up.

5 Ways to Export Paddle Data to a Database

Method 1: Manual Paddle CSV Export from the Dashboard

The simplest option. In the Paddle dashboard, go to Reports, find the report type you need under the Build reports tab (transactions, adjustments, transaction line items, products and prices, or discounts), click Build report, filter the date range, then click Generate report. Paddle emails you once the file is ready. The output is a UTF-8, comma-delimited CSV.

Once you have the file, you import it from your own machine with psql's \copy command (the server-side COPY FROM variant needs filesystem access on the database server, which managed hosts like Supabase, Neon, and RDS don't give you):

\copy paddle_transactions_export (transaction_id, customer_id, status, currency, total, billed_at) FROM 'paddle-transactions.csv' CSV HEADER

Create the destination table before you run that, and check the column list against the file: CSV HEADER skips the header row, it does not match columns by name, so the order has to line up. Paddle's transaction report ships a lot more columns than the six above, so trim the file or list every column.

Pros:

Free and built into Paddle
No code, no API setup, no developer required
Useful for one-off analysis or a finance handoff

Cons:

Report data can lag up to 24 hours, so the export is stale before you even download it
Asynchronous: you request the report, wait for it to build, then download
Files expire 14 days after creation; miss the window and you rebuild the report
Manual every time. If you need fresh data weekly, you're clicking through this every week
Each report type is separate, so a full dataset means several builds and several imports
No automation, no incremental updates, no joins with your application data

Dashboard reports are fine when someone in finance needs a spreadsheet once a quarter. As a way to keep a database current, they fall over immediately.

Method 2: Automate the Reports API

Paddle exposes the same reporting engine over the API: POST /reports creates a report, you poll until its status is ready (or listen for the report.updated notification), then call the download-url endpoint to get a link to the CSV. That link expires after 72 hours, and the report itself stays available for 14 days, so you can request a fresh link at any point inside that window.

Pros:

Scriptable version of Method 1; can run on a schedule
Same broad report types as the dashboard
Good fit if you already have a data pipeline that ingests CSV files

Cons:

You're building a pipeline anyway: create, poll, download via an expiring link, parse CSV, load, dedupe
Inherits Method 1's data lag, so your database is always up to a day behind
CSV column layouts follow the report, so you still design the table schema and upserts yourself
Sandbox/live duplication, credential storage, and failure alerting are all on you

This is a legitimate middle path for data teams, but by the time it runs reliably on a schedule, you've built and now own a small ETL system.

Method 3: Direct Paddle API Integration

If you need current data and you're comfortable writing code, you can pull directly from the Paddle API's list endpoints and write the results into PostgreSQL yourself.

A minimal version looks like this:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function exportTransactions() {
  let url: string | null = 'https://api.paddle.com/transactions?per_page=30';

  while (url) {
    const response = await fetch(url, {
      headers: { Authorization: `Bearer ${process.env.PADDLE_API_KEY}` },
    });
    const json = await response.json();

    for (const tx of json.data ?? []) {
      await pool.query(
        `INSERT INTO paddle_transactions_raw (id, customer_id, status, currency_code, grand_total, billed_at, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6, $7)
         ON CONFLICT (id) DO UPDATE
         SET status = $3, grand_total = $5, updated_at = $7`,
        [
          tx.id,
          tx.customer_id,
          tx.status,
          tx.currency_code,
          tx.details?.totals?.grand_total ?? 0,
          tx.billed_at,
          tx.updated_at,
        ],
      );
    }

    url = json.meta?.pagination?.has_more ? json.meta.pagination.next : null;
  }
}

That hand-rolled schema is not the same as the table Codeless Sync creates, which stores details.totals.total in a column called total. Keep the two apart, or the SQL further down this page won't run against your table.

Pros:

Real, current data on demand, no report lag
Full control over which entities you export and how they map to your schema
Simple Bearer-key auth for your own account, so there are no OAuth tokens to refresh the way QuickBooks and Xero require

Cons:

Cursor pagination with small pages: transactions cap at 30 records per page, so large accounts mean many requests per run
Rate limiting, retries on 429s, and error recovery are all on you
Amounts arrive as strings of integer minor units inside nested objects (details.totals.grand_total), so schema mapping and type conversion are manual work
Each new entity (customers, subscriptions, products, prices, adjustments, discounts) is another loop, another schema, another set of edge cases
Sandbox and live need separate configuration and testing
Maintenance is forever. The build is the easy part

For most teams the arithmetic is simple: a weekend to build it, then an open-ended commitment to keep it alive as Paddle's API and your own schema both move.

Method 4: Zapier, Make, or Generic Automation Platforms

If you want fresh data without writing code, automation platforms can catch Paddle webhooks. Neither Zapier nor Make has a native Paddle trigger (Make's Paddle app is actions-only, and Zapier has no Paddle app at all), but both have generic webhook modules. You point Paddle's transaction.completed webhook at a catch-hook URL, then map the payload fields to a PostgreSQL insert action.

Pros:

No code required, though you do set up a webhook destination and map fields by hand
Both platforms have solid PostgreSQL insert actions
Reasonable for low-volume, single-trigger use cases

Cons:

No historical backfill; only future events trigger the automation. Your existing customers, subscriptions, and transaction history stay outside the database unless you export them separately
Per-task pricing scales with your sales volume; a growing subscription business generates a lot of events
Limited transformation logic; anything beyond direct field mapping needs custom code steps, which take you back toward Method 3
Failures retry, but silently; debugging a stuck automation is painful
Your "export pipeline" lives inside a third-party automation account, not your codebase

Automation platforms earn their place on single-trigger flows. Asking one to hold a complete, current copy of your Paddle data is asking it to act as a replication tool, which it was never built to be.

Method 5: A Purpose-Built No-Code Sync (Codeless Sync)

Codeless Sync was built for exactly this problem: getting API data into a PostgreSQL database without writing code and without a report-lag delay, then keeping it there without a pipeline to maintain.

How it works:

Connect your PostgreSQL database via connection string (Supabase, Neon, AWS RDS, Railway, Heroku, or self-hosted)
Add your Paddle API key (read access is enough)
Pick which data to export (customers, subscriptions, transactions, products, prices, adjustments, or discounts)
The destination table is auto-created with the right schema and indexes
Run the first export: a full sync pulls your complete history. Schedule recurring syncs, or trigger them manually

Pros:

No code to write, and no report polling or pagination loops left running
Historical backfill plus ongoing incremental updates in one workflow (no 24-hour report lag)
Works with any PostgreSQL host
Free tier for small projects, flat predictable pricing as you scale
Setup takes about 5 minutes

Cons:

Runs in batches on a schedule (webhooks remain the right tool for instant reactions like access provisioning)
Currently focused on Stripe, QuickBooks, Xero, and Paddle; not a general-purpose ETL tool

If what you want is a current, queryable copy of your Paddle data sitting in your own database, this is the shortest route to it.

Comparison: Which Paddle Export Method Fits Your Use Case?

Method	Setup time	Keeps data current?	Best for	Cost
Dashboard CSV report	Minutes	No; async snapshot, up to 24h data lag	A finance handoff you do once a quarter	Free
Reports API pipeline	Days	Partial; scheduled but inherits report lag	Teams already loading CSV feeds on a schedule	Server time plus the build
Direct Paddle API	Days to weeks	Yes, if you maintain the polling	A custom entity mix worth the engineering	Server time plus ongoing upkeep
Zapier / Make	Hours	Partial; future events only, no backfill	Reacting to one Paddle event at low volume	Per-task, rises with sales volume
Codeless Sync	~5 minutes	Yes; backfill plus scheduled incremental	Anyone who wants the data there and current	Free tier, then flat plans

Read down the table and the trade is clear enough. The free routes hand you a snapshot that is already stale and will delete itself. The API hands you freshness in exchange for permanent upkeep. The automation platforms only ever see what happens next. A purpose-built sync is the one option that gives you history and freshness without putting a pipeline in your name.

What You Can Do Once Paddle Data Is in PostgreSQL

Paddle's own reporting can tell you what happened inside Paddle. Once the same data is in Postgres, you can ask questions Paddle has no way to answer, because you can join your billing records against your application's tables and against each other.

The queries below run against the tables Codeless Sync creates. Paddle returns amounts as strings in the lowest denomination for the currency (cents for USD, pence for GBP), so totals are cast to numeric and divided by 100. Most currencies use two decimal places, but a few (JPY, for example) use none, so adjust the divisor if you sell in those. If you sell in more than one currency, add AND currency_code = 'USD' or group by currency_code, since every row is stored in its original currency. Add AND livemode = true to keep sandbox rows out of the numbers.

Monthly revenue with month-over-month growth:

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', billed_at) AS month,
    SUM(total::NUMERIC) / 100.0 AS revenue,
    COUNT(*) AS transaction_count
  FROM paddle_transactions
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  transaction_count,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month DESC
LIMIT 12;

Refund and chargeback rate from adjustments:

WITH adj AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) FILTER (WHERE action = 'refund' AND status = 'approved') AS refunds,
    COUNT(*) FILTER (WHERE action = 'chargeback' AND status = 'approved') AS chargebacks
  FROM paddle_adjustments
  GROUP BY 1
),
tx AS (
  SELECT
    DATE_TRUNC('month', billed_at) AS month,
    COUNT(*) AS completed_transactions
  FROM paddle_transactions
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT
  a.month,
  a.refunds,
  a.chargebacks,
  ROUND(
    100.0 * (a.refunds + a.chargebacks) / NULLIF(t.completed_transactions, 0),
    2
  ) AS refund_chargeback_rate_pct
FROM adj a
LEFT JOIN tx t ON t.month = a.month
ORDER BY a.month DESC;

Trial-to-paid conversion by cohort (only possible with full history):

SELECT
  DATE_TRUNC('month', started_at) AS cohort_month,
  COUNT(*) AS subscriptions_started,
  COUNT(*) FILTER (WHERE first_billed_at IS NOT NULL) AS converted_to_paid,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE first_billed_at IS NOT NULL)
    / NULLIF(COUNT(*), 0),
    1
  ) AS conversion_pct
FROM paddle_subscriptions
WHERE started_at IS NOT NULL
  AND livemode = true
GROUP BY 1
ORDER BY cohort_month DESC
LIMIT 12;

That last one is the query a webhook-fed table can never answer, because the cohorts you most want to measure started before you set the webhook up. That is what a real export gets you: a queryable dataset sitting next to your application data, ready for dashboards or alerts, instead of a CSV that deletes itself in 14 days. For ready-made metric queries, see How to Calculate MRR, Churn, and LTV in PostgreSQL.

Step-by-Step: Set Up an Automated Paddle Export in 5 Minutes

If Method 5 looks like the right fit, the whole setup is four things: paste your PostgreSQL connection string, add a Paddle API key with Permissions set to Read for All (it's encrypted at rest), let the wizard auto-create the destination table, then run the first export. Transactions is the data type to start with, since it's your revenue record, and the Paddle transactions SQL template shows the exact schema you'll get.

That first run is a full backfill, so your complete history lands in the table rather than only what happens from now on. After that you either trigger exports manually or put them on a schedule (every 12 hours, daily, weekly, or monthly, depending on your plan). Repeat the wizard for customers, subscriptions, or any other data type, and each one becomes its own table on the same cadence.

The free tier covers small projects without a credit card, and the full walkthrough, including which PostgreSQL hosts are supported, lives on the Paddle to PostgreSQL page.

Frequently Asked Questions

Can I export Paddle data without using the API?

Yes, via a Paddle CSV export from the dashboard: go to Reports, build the report type you need (transactions, adjustments, line items, products and prices, or discounts), and download the CSV once Paddle emails you that it's generated. This works for one-off analysis, but reports build asynchronously, the data inside can lag up to 24 hours, and files expire after 14 days. For an ongoing, current copy of your data, you'll want one of the automated methods, either built on the API yourself or handled by a sync tool like Codeless Sync.

Does Paddle have a full data export or backup option?

Not as a single button. Exports are per report type in the dashboard, or per entity through the API's paginated list endpoints. A complete picture of your billing data means several separate exports, repeated whenever you need fresh data. This is the main reason teams reach for a sync tool: a complete, continuously updated copy in one database is exactly what those tools produce.

What's the best way to export Paddle data to PostgreSQL?

It depends on freshness needs and engineering time. For a one-time snapshot, a dashboard report plus a psql \copy import is fastest. For a current, queryable copy with minimal maintenance, a no-code sync is the lowest-effort path and avoids the report lag entirely. Building directly against the Paddle API gives the most control, but you own cursor pagination (transactions cap at 30 records per page), rate-limit handling, and schema mapping forever.

How long are Paddle report exports available?

Generated report files are available to download for 14 days after creation, and each download URL the API returns expires after 72 hours, so you can request a fresh link at any point inside that 14-day window. Reports are best treated as one-off snapshots rather than an archive. If you need a permanent, queryable history, load the data into your own database, then the retention question disappears.

How fresh will my exported Paddle data be?

That depends entirely on the method. A dashboard report or a Reports API pull can be up to 24 hours behind before you even download it, because Paddle builds those asynchronously. Anything that reads the API directly, including a sync tool, is current as of the moment the run starts. So if the number you are looking at needs to match what Paddle's dashboard says right now, rule out the report-based methods. For monthly finance reviews a daily run is plenty; for internal dashboards and MRR tracking, twice a day keeps the numbers feeling live. Codeless Sync runs manual syncs on the free tier, and paid tiers add scheduled runs from every 12 hours down to monthly, depending on plan.

Need a current, queryable copy of your Paddle data without babysitting reports or pagination loops? Codeless Sync has a free tier, no credit card required. For the webhook side of the story, see Paddle Webhooks vs Database Sync.

Related:

How to Export Xero Data to a Database

ilshaad — Mon, 20 Jul 2026 11:19:58 +0000

Compare 5 ways to export Xero data to a database: CSV report exports, the Xero API, Zapier, ETL platforms, and no-code sync. Honest pros, cons, and costs.

By Ilshaad Kheerdali · 20 July 2026

If you run your accounting on Xero, you've probably hit a wall trying to get the data out. There is no one-click "export everything" button. Contacts export from one screen, invoices from another, reports from a third, and every file is a static snapshot that's stale the moment you download it. The API works, but only after you register an app, wire up OAuth 2.0, and babysit tokens forever.

The frustrating part is that "export Xero data to a database" sounds like it should be simple. It isn't. Different methods exist for different needs, and most of them either go stale immediately, cost more than they should, or leave you maintaining a pipeline that quietly breaks at 3am.

This guide walks through the five practical ways to export Xero data into a real, queryable database: CSV exports, the Xero API directly, Zapier-style automation, enterprise ETL platforms, and no-code sync. Honest pros, honest cons, and what each one actually costs to run.

Why Exporting Xero Data Is Harder Than It Should Be

Xero holds the data you care about: contacts, invoices, payments, bank transactions, the whole accounting picture. But getting it out in a form you can actually use takes more work than most teams expect.

Built-in exports are per-screen snapshots. Xero lets you export contacts, invoices, and bills as CSV files, and reports as Excel, PDF, or Google Sheets, but each data type exports separately from its own screen. A complete picture of your accounts means running several exports, and every one is frozen in time the second you download it.

There's no bulk "export everything" endpoint. The Xero API is built for transactional access, not data extraction. Invoices page through 100 records at a time (up to 1,000 with the pageSize parameter), contacts are a separate set of calls, payments another. For a complete dataset you're making dozens of calls and stitching the responses together.

List calls hide the detail you actually need. Ask the API for a list of invoices and it returns trimmed summaries with no line items, to keep responses fast. Getting the full records means fetching them individually or paging with larger page sizes, so a single "get my invoices" is rarely one call.

Webhooks notify but don't deliver data. Xero webhooks only fire for four things (contacts, invoices, credit notes, and App Store subscriptions), and the payload carries just a reference to the record, not the record itself. You still call the API to fetch what changed, which means you're maintaining the polling layer regardless.

OAuth 2.0 is non-negotiable. Every Xero integration needs a registered app, a consent flow, and token management. Access tokens last 30 minutes, and the refresh token is single-use with a rolling 60-day life: every refresh returns a new refresh token and invalidates the old one. Store the wrong one and your export job silently stops working. (For the full breakdown of the integration burden, see Xero API vs Database Sync.)

The result is that "export Xero data to a database" gets solved one of five ways. Here they are.

5 Ways to Export Xero Data to a Database

Method 1: Manual CSV / Excel Exports from Xero

The simplest option. From inside Xero, export each data type from its own screen: Contacts → Export for your customer and supplier list, Business → Invoices → Export for invoices and bills, and the Reports section for the General Ledger, Trial Balance, and other reports (which export to Excel, PDF, or Google Sheets; notably, Xero's newer reports don't offer CSV).

Once you have a file, you import it into your database with a COPY statement or a one-off script:

COPY xero_invoices_export (invoice_number, contact_name, invoice_date, total, amount_due)
FROM '/path/to/xero-invoices.csv'
DELIMITER ','
CSV HEADER;

Pros:

Free and built into Xero
No code, no API setup, no developer required
Useful for one-off analysis or sending to an accountant

Cons:

Stale the moment you click export; the file represents a single point in time
Manual every time. If you need fresh data weekly, you're running this every week
Each data type exports separately, so a full dataset means many separate exports from different screens
Column layouts differ between screens and reports, so your import scripts need per-file handling
No automation, no incremental updates, no joins with your application data

CSV exports are fine for a quarterly accountant handoff. They are not a database export strategy.

Method 2: Direct Xero API Integration

If you need fresh data and you're comfortable writing code, you can pull directly from the Xero Accounting API and write the results into PostgreSQL yourself.

Here's a stripped-down example in TypeScript:

import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function exportInvoices(accessToken: string, tenantId: string) {
  let page = 1;

  while (true) {
    const response = await fetch(
      `https://api.xero.com/api.xro/2.0/Invoices?page=${page}&pageSize=1000`,
      {
        headers: {
          Authorization: `Bearer ${accessToken}`,
          'Xero-tenant-id': tenantId,
          Accept: 'application/json',
        },
      },
    );

    const json = await response.json();
    const invoices = json.Invoices ?? [];

    for (const inv of invoices) {
      await pool.query(
        `INSERT INTO xero_invoices (xero_id, invoice_number, contact_name, type, status, total, amount_due, updated_at)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
         ON CONFLICT (xero_id) DO UPDATE
         SET status = $5, total = $6, amount_due = $7, updated_at = $8`,
        [
          inv.InvoiceID,
          inv.InvoiceNumber,
          inv.Contact?.Name ?? null,
          inv.Type,
          inv.Status,
          inv.Total ?? 0,
          inv.AmountDue ?? 0,
          inv.UpdatedDateUTC,
        ],
      );
    }

    if (invoices.length < 1000) break;
    page += 1;
  }
}

Pros:

Real, current data on demand
Full control over which data types you export and how they map to your schema
Free in tooling cost; you only pay for the infrastructure that runs it

Cons:

OAuth 2.0 setup: app registration, consent flow, redirect URI handling, and token storage
Access tokens expire every 30 minutes, and the single-use refresh token must be re-persisted on every refresh
One Xero login can cover several organisations, so you also enumerate tenants and send the right Xero-tenant-id header on every call
Rate limiting is on you: 60 calls per minute per organisation, 5 concurrent requests, plus a daily cap
Incremental sync via If-Modified-Since has blind spots (some changes never bump UpdatedDateUTC), so you still need periodic full reconciliation
Each new data type (contacts, payments, bank transactions, credit notes) is another query, another schema, another set of edge cases
Maintenance is forever. The build is the easy part

This is the right path if your needs are unusual or you have engineering time to spare. For most teams, the upkeep cost outweighs the benefit.

Method 3: Zapier, Make, or Generic Automation Platforms

If you want fresh data without writing code, automation platforms like Zapier and Make have pre-built Xero triggers. You can wire up "when an invoice is created in Xero, insert a row into Postgres" and it just works.

Pros:

No code required
Decent library of triggers: new invoice, new contact, new payment, and so on
Quick to set up for simple flows

Cons:

Per-task pricing scales fast. A growing business with thousands of monthly invoices can hit the higher tiers within a couple of months
No historical backfill; only future events trigger zaps. Your existing contacts and invoices stay outside the database unless you export them separately
Limited transformation logic. Anything more complex than a direct field mapping needs custom code steps, which take you back toward the territory of Method 2
Failures retry, but silently; debugging a stuck zap is painful
Vendor lock-in. Your "data export pipeline" lives inside a Zapier account, not in your codebase

Zapier-style platforms work for single-trigger flows. They're a poor fit for "I want a complete, current copy of my Xero data in Postgres."

Method 4: Enterprise ETL Platforms (Fivetran, Airbyte)

General-purpose ETL platforms have Xero connectors and will land your data in a warehouse or database on a schedule, alongside hundreds of other sources.

Pros:

Mature scheduling, monitoring, and retry infrastructure
Handles many sources beyond accounting, useful if you're already consolidating a large data stack
Managed OAuth and rate-limit handling

Cons:

Usage-based pricing (Fivetran bills by monthly active rows) is hard to predict and usually overkill for one accounting sync
Self-hosting Airbyte trades the subscription for your own infrastructure and upgrade maintenance
Built for data teams feeding warehouses, so setup assumes more data-engineering context than most small teams have
A heavyweight platform for what is, for most readers, a single-source job

If you already run a Fivetran or Airbyte stack, adding Xero to it is reasonable. Adopting one just to export your accounting data is like buying a truck to deliver one parcel.

Method 5: A Purpose-Built No-Code Sync (Codeless Sync)

Codeless Sync was built for exactly this problem: getting API data into a PostgreSQL database without code, without ETL infrastructure, and without per-row pricing.

How it works:

Connect your PostgreSQL database via connection string (Supabase, Neon, AWS RDS, Railway, Heroku, or self-hosted)
Authorize Xero with one click; the OAuth consent, token storage, and refresh are handled for you
Pick which data to export (contacts, invoices, payments, accounts, bank transactions, credit notes, items, purchase orders, journals, or organisation details)
The destination table is auto-created with the right schema and indexes
The first export runs immediately. Schedule recurring syncs, or trigger them manually

Pros:

No code, no OAuth plumbing, no token refresh maintenance
Historical backfill plus ongoing incremental updates in one workflow
Works with any PostgreSQL host
Free tier for small projects, flat predictable pricing as you scale
Setup takes about 5 minutes

Cons:

Batch-based, not real-time (though incremental syncs run as often as every minute on paid plans)
Currently focused on Stripe, QuickBooks, Xero, and Paddle; not a general-purpose ETL tool

This is the recommended path if your goal is a current, queryable copy of your Xero data in your own database, with the lowest possible maintenance burden.

Comparison: Which Export Method Fits Your Use Case?

Method	Setup time	Keeps data current?	Best for	Cost
CSV / Excel exports	Minutes	No; single snapshot per screen	One-off accountant handoffs	Free
Direct Xero API	Days to weeks	Yes, if you maintain the polling	Bespoke integrations with engineering capacity	Infrastructure only, plus dev time
Zapier / Make	Hours	Partial; future events only, no backfill	Single-trigger flows for small volumes	Tiered, scales with task volume
Fivetran / Airbyte	Hours to days	Yes; scheduled connector runs	Data teams already running a multi-source stack	Usage-priced or self-hosted infra
Codeless Sync	~5 minutes	Yes; backfill plus scheduled incremental	Developers and small teams who want it to just work	Free tier, then flat plans

The split is roughly: free options give you stale per-screen files, the API gives you fresh data at the cost of forever-maintenance, automation platforms work until your volume grows, enterprise ETL works if you already own the stack, and a purpose-built sync sits in the middle: fresh data, low maintenance, predictable cost.

What You Can Do Once Xero Data Is in PostgreSQL

The whole point of exporting Xero data into a database is what becomes possible afterwards. With the data in Postgres, you have full SQL access to everything, and you can join it with your application's own tables.

Monthly sales revenue with month-over-month growth:

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', invoice_date) AS month,
    SUM(total) AS revenue,
    COUNT(*) AS invoice_count
  FROM xero_invoices
  WHERE type = 'ACCREC'          -- sales invoices, not supplier bills
    AND status = 'PAID'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  invoice_count,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month DESC
LIMIT 12;

Outstanding receivables by contact:

SELECT
  contact_name,
  SUM(amount_due) AS outstanding_balance,
  COUNT(*) AS unpaid_invoices,
  MIN(due_date) AS oldest_due_date
FROM xero_invoices
WHERE type = 'ACCREC'
  AND amount_due > 0
  AND status IN ('AUTHORISED', 'SUBMITTED')
GROUP BY contact_name
ORDER BY outstanding_balance DESC;

Net position: what you're owed vs what you owe:

SELECT
  type,                                     -- ACCREC = sales invoices, ACCPAY = bills
  COUNT(*) FILTER (WHERE amount_due > 0) AS open_invoices,
  SUM(amount_due) AS outstanding
FROM xero_invoices
WHERE status IN ('AUTHORISED', 'SUBMITTED')
GROUP BY type;

This is what a real export gets you. Not a folder of per-screen CSV files, but a queryable dataset that lives next to your application data, ready for dashboards, alerts, or any analysis you want to run.

Step-by-Step: Export Xero to Postgres with Codeless Sync

If Method 5 looks like the right fit, the setup itself takes about five minutes:

Create a Codeless Sync account. The free tier covers small projects without a credit card.
Add your PostgreSQL database. Paste your connection string. Codeless Sync tests the connection before saving.
Open the configuration wizard and choose Xero as the source. Pick the data type you want first; contacts is a good starting point because it's easy to verify.
Click Connect to Xero and authorize through Xero's standard consent screen, then pick which organisation to sync. The OAuth flow, token storage, and refresh loop are handled automatically.
Auto-create the destination table. Codeless Sync builds the schema for you, with the right column types and indexes. If you'd rather review the SQL first, copy the template and run it manually.
Run the first export. The full backfill pulls every matching record. For most organisations this takes seconds to a couple of minutes.
Schedule recurring exports (every minute, hourly, or daily depending on your plan), or trigger them manually from the dashboard.

When the run finishes, your Xero data is in your Postgres database. Repeat the wizard for invoices, payments, or any other data type you need. Each one becomes its own table, each one stays current.

For a deeper walkthrough including the full Xero setup flow, see the step-by-step Xero-to-PostgreSQL sync guide.

Frequently Asked Questions

Can I export Xero data without using the API?

Yes. The no-API option is to export each data type from its own screen inside Xero (contacts, invoices, bills) or export reports as Excel, PDF, or Google Sheets. This works for one-off analysis but produces static files that are stale the moment they're downloaded, and a complete dataset means several separate exports. For ongoing access, every other method eventually involves the Xero API in some form; the question is whether you build that integration yourself or use a tool that handles it for you. A no-code sync like Codeless Sync uses the API behind the scenes so you don't have to.

Does Xero have a full backup or bulk export option?

Not in the way developers usually mean it. There's no single button or API endpoint that returns all of your data at once. Xero's own guidance is to export each data type separately: contacts from the Contacts screen, invoices and bills from the Business menu, and the General Ledger and other reports from the Reports section. This is the main reason most teams reach for a sync tool; a complete, current copy of your data in one place is exactly what those tools do.

What's the best way to export Xero data to PostgreSQL?

It depends on how often the data needs to refresh and how much engineering time you can spare. For a one-time export, per-screen CSV files plus a COPY statement is fastest. For a current, queryable copy of your Xero data with minimal maintenance, a no-code sync tool is the lowest-effort path. Building directly against the Xero API gives you the most control but the highest ongoing maintenance cost: 30-minute access tokens, single-use refresh tokens, tenant routing, and rate limits are all yours to manage.

How often should I export Xero data?

It depends on what you're using the data for. For monthly accounting reviews, daily syncs are plenty. For internal dashboards or cash-flow monitoring, hourly updates feel close to live. For event-driven workflows (like flagging overdue invoices to your ops team), you'll want incremental syncs running at least every 5-15 minutes. Codeless Sync supports schedules from every minute up to daily, depending on plan tier.

Will exporting Xero data affect my API rate limits?

Xero enforces 60 calls per minute per connected organisation, with 5 concurrent requests and a daily cap on top. A well-designed export tool stays well under that; incremental syncs typically only fetch records that changed since the last run, so the request count is small. If you're building a custom integration, you'll need to implement your own backoff and retry logic to stay under the limits. Sync tools handle this for you.

Need a current, queryable copy of your Xero data without writing a pipeline? Codeless Sync has a free tier, no credit card required. For the build-vs-buy decision in more depth, see Xero API vs Database Sync.

Related:

How to Design a PostgreSQL Schema for Stripe Data

ilshaad — Tue, 14 Jul 2026 16:27:09 +0000

Design a PostgreSQL schema for Stripe data: convert unix timestamps, store amounts in integer minor units, use a JSONB catch-all, and survive API drift.

By Ilshaad Kheerdali · 14 July 2026

Once you have decided to get your Stripe data into PostgreSQL, the first practical question is what the tables should look like. The Stripe API returns tidy, self-describing JSON, so the obvious move is to create one column per field and call it done.

That obvious schema is a trap. The types in a Stripe payload lie to you (timestamps are integers, money is integers, half the interesting fields are nested objects), the payload shape changes as Stripe evolves its API, and a table built from one example payload gives you no plan for what happens on the second sync when the same customer comes back with new values.

This post walks through the three type traps, why the flat one-column-per-field dump ages badly, and the schema pattern that holds up in production. If you want to see finished schemas first, the free Stripe to Postgres Schema tool shows the exact production table for 9 core Stripe objects, side by side with the naive dump so the differences are obvious.

The three type traps in Stripe JSON

Timestamps are unix epoch integers

Every timestamp Stripe sends is epoch seconds: "created": 1782950400. Dump that into a BIGINT column and every date query you ever write needs a conversion wrapped around it. Convert once at insert time instead, with PostgreSQL's to_timestamp(), and store a TIMESTAMPTZ:

-- Once, at insert time:
to_timestamp(1782950400)  -- 2026-07-02 00:00:00+00

-- Forever after, plain SQL just works:
SELECT count(*) FROM stripe_customers
WHERE created >= date_trunc('month', now());

Amounts are integers in minor units

Stripe represents money in the smallest currency unit to avoid floating-point rounding errors, so £29.99 arrives as 2999. Store it as an integer and divide by 100 for display. One exception is documented in Stripe's currency docs: zero-decimal currencies like JPY are already whole units. Stripe also supports a handful of three-decimal currencies like KWD, where the minor unit is 1/1000, so the divisor there is 1000.

The trap is not the storage, it is the reading. An amount column holding 2999 looks like two thousand pounds to anyone querying it cold. Keep amounts as integers (never REAL or FLOAT), and make the minor-units convention loud in your column comments and dashboards.

Nested objects do not flatten

address, invoice_settings, recurring, metadata: a good chunk of every Stripe payload is nested. Flattening them into columns explodes the column count, and dumping each one into its own JSONB column just gives you a pile of fragments. Neither gets you queryable data.

Why one column per JSON field ages badly

Beyond the type traps, the flat dump has three structural problems.

No upsert key. A schema generated from a payload gives you columns, not decisions. The decision that matters most is the primary key, because syncing is repetitive by nature: the same customer comes back on every sync with updated values. Without a primary key there is nothing to ON CONFLICT against, so re-syncs either duplicate rows or fail.

No indexes. The fields you will actually filter on (customer, status, created) get no indexes, so queries crawl as the table grows.

Schema drift. The Stripe API changes shape. In API version 2025-03-31 (basil), Stripe removed current_period_start and current_period_end from the Subscription object (they moved to subscription items), removed the paid boolean from invoices, and restructured invoice line items. A table generated from one payload is only correct for that payload, on that API version, on that day. When Stripe adds or moves a field, the flat dump breaks silently: new fields vanish, and inserts written against the old shape start failing.

The PostgreSQL schema pattern that works: curated columns plus a JSONB catch-all

Here is the actual production table for Stripe customers (this is the template Codeless Sync auto-creates, shown verbatim in the schema tool):

CREATE TABLE IF NOT EXISTS stripe_customers (
  -- Core queryable fields
  id TEXT PRIMARY KEY,
  email TEXT,
  name TEXT,
  description TEXT,
  currency TEXT,
  balance INTEGER DEFAULT 0,
  delinquent BOOLEAN DEFAULT false,

  -- Full payload
  data JSONB,

  -- Sync metadata
  livemode BOOLEAN NOT NULL,
  created TIMESTAMPTZ NOT NULL,
  synced_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_stripe_customers_email ON stripe_customers(email);
CREATE INDEX IF NOT EXISTS idx_stripe_customers_created ON stripe_customers(created);
CREATE INDEX IF NOT EXISTS idx_stripe_customers_livemode ON stripe_customers(livemode);
CREATE INDEX IF NOT EXISTS idx_stripe_customers_delinquent ON stripe_customers(delinquent);

Four deliberate choices are doing the work:

A short set of typed, queryable columns. Only the fields you filter, join, and report on. Timestamps are already TIMESTAMPTZ, amounts are integers, and everything has a real type instead of a guess.
A data JSONB catch-all holding the full payload. Nothing is ever lost, and this is the drift insurance: when Stripe adds a field next quarter, it lands inside data instead of breaking your inserts. You can query it any time with data->>'phone'.
Sync metadata. livemode keeps test and live records separable, and synced_at tells you how fresh every row is, which is the first thing you check when a number looks off.
Indexes on the fields you actually query. Email lookups, date ranges, and delinquency filters stay fast at any table size.

Pick the upsert key before the first insert

Stripe IDs (cus_..., in_..., sub_...) are stable, unique text values, which makes them the natural primary key. With id TEXT PRIMARY KEY in place, re-syncing is one idempotent statement:

INSERT INTO stripe_customers (id, email, name, currency, balance, delinquent, data, livemode, created)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, to_timestamp($9))
ON CONFLICT (id) DO UPDATE SET
  email      = EXCLUDED.email,
  name       = EXCLUDED.name,
  balance    = EXCLUDED.balance,
  delinquent = EXCLUDED.delinquent,
  data       = EXCLUDED.data,
  synced_at  = NOW();

Run it once or a thousand times, the table stays correct. This is the single decision the flat dump never makes for you, and it is why re-syncs against naive schemas end in duplicate rows.

For joins, the reference fields do the same job across tables: stripe_invoices.customer points at stripe_customers.id, stripe_subscriptions.customer likewise. One table per Stripe object, joined on IDs, and metrics queries like the ones in how to calculate MRR, churn, and LTV in PostgreSQL fall out naturally.

Schema drift is the part nobody budgets for

Designing the schema is a one-off. Keeping it correct is not, and this is the cost that surprises teams months later.

Stripe versions its API and moves fields between objects, as the basil changes above show. Your own usage changes too: the day you need subscription analytics, you need the subscriptions and subscription items tables you did not create on day one. And every change lands on you as migration work: alter the table, backfill the column, update the insert code, re-test the sync. It is exactly the kind of recurring, unglamorous maintenance that quietly breaks Stripe-to-Postgres pipelines.

You can absolutely own that chore yourself, and if you are hand-building, this post's pattern (curated columns, JSONB catch-all, upsert key, sync metadata) minimises it, because unknown fields flow into data instead of breaking the pipeline.

The alternative is to not own it at all. Codeless Sync creates these exact tables in one click and keeps them filled on a schedule (hourly, daily, or custom cron on paid tiers), with full and incremental sync modes, timestamps converted, amounts kept in minor units, and the complete payload preserved in data. There are no webhooks or cron jobs to babysit and no drift handling on your plate. The 5-minute walkthrough is in how to sync Stripe data to PostgreSQL, and if you are still weighing approaches, 5 ways to get Stripe data into PostgreSQL compares the options honestly.

Either way, start from the right schema. All nine production templates (customers, invoices, subscriptions, payment intents, products, prices, refunds, invoice line items, subscription items) are free to inspect and copy in the Stripe to Postgres Schema tool.

Frequently Asked Questions

Should I store the whole Stripe payload in one JSONB column?

Not on its own. A single JSONB column preserves everything but makes every query a JSON-path expression, indexes get awkward, and type safety disappears. The hybrid works better: a handful of typed columns for the fields you query, plus one data JSONB column holding the full payload for everything else. You get fast, readable SQL and lose nothing.

Should Stripe amounts be INTEGER, BIGINT, or NUMERIC in PostgreSQL?

Use an integer type and keep Stripe's minor units. INTEGER covers single-object amounts comfortably (its ceiling is over 21 million pounds in pence), and BIGINT is the safe pick for aggregates or if you would rather never think about the ceiling. Avoid REAL and FLOAT entirely for money, and only convert to NUMERIC at display or reporting time when you divide by 100 (or 1000 for three-decimal currencies).

How do I store Stripe metadata in PostgreSQL?

As JSONB. Stripe's metadata object is arbitrary user-defined keys, so it has no fixed shape to model as columns. Query it with data->'metadata'->>'your_key', and if you filter on metadata often, add a GIN index on the JSONB column or an expression index on the specific key you query.

Do I need a separate table for every Stripe object?

Yes, one table per object type: customers, invoices, subscriptions, payment intents, and so on. Stripe objects reference each other by ID, so separate tables joined on those IDs (for example stripe_invoices.customer to stripe_customers.id) mirror the API's own structure and keep queries simple. The schema tool shows the table for each of the 9 core objects.

How do I keep my Stripe tables up to date when the API changes?

Three habits if you are hand-rolling: pin your Stripe API version so changes only arrive when you choose, watch the Stripe API changelog for moved or removed fields, and keep a full-payload JSONB column so new fields are captured even before you model them. Or hand the chore off entirely: a managed sync like Codeless Sync owns the schema, the conversions, and the drift handling, so your tables stay current without migration work.

Related:

Xero API vs Database Sync: Which is Better?

ilshaad — Mon, 06 Jul 2026 14:11:03 +0000

Xero API vs database sync for getting accounting data into PostgreSQL: a build-vs-buy guide covering OAuth tokens, rate limits, and the real maintenance cost.

By Ilshaad Kheerdali · 6 July 2026

If you're building on top of Xero, you'll eventually need that accounting data in your own database, to power dashboards, reconcile revenue, or join it against your product tables. There are two ways to get it there: build your own integration against the Xero API, or hand the job to a managed database sync. Both work, but they cost you very different things.

It helps to be honest about the framing first. This isn't really "API versus no API", because a managed sync uses the Xero API under the hood too. The real question is build versus buy: do you own the OAuth tokens, the tenant routing, the pagination, the rate-limit backoff, and the schema mapping yourself, or do you let a service run all of that for you? This post compares the two at decision altitude. (If you just want the step-by-step setup instead, here is how to sync Xero to PostgreSQL in five minutes.)

How a Xero API Integration Works

Building your own integration means going directly to Xero's developer platform. The shape of the work is always the same:

Register an app in the Xero Developer portal and get your client credentials.
Send each customer through the OAuth 2.0 consent flow with the offline_access scope, then store the access and refresh tokens. Because one Xero login can cover several companies, you also call the Connections endpoint and keep track of each tenantId.
Poll the Accounting API for the objects you care about (invoices, contacts, payments, and so on), paging through the results and sending the right Xero-tenant-id header on every call.
Map each object shape into your own tables, and keep refreshing tokens so the connection never goes stale.

In code, the loop you end up owning looks something like this:

// The moving parts you operate yourself, on every connected organisation, forever
const accessToken = await refreshIfExpired(org.tenantId); // 30-minute access tokens
const invoices = await pageThrough('/Invoices', accessToken, org.tenantId); // 100 per page

for (const invoice of invoices) {
  await upsertIntoPostgres(invoice); // your mapping, your schema
}

// plus: refresh-token rotation, 429 backoff, per-tenant scheduling, and schema drift

That snippet hides a lot. The OAuth handshake, the tenant enumeration, the pagination, and the incremental fetching are each a small project of their own. The point here is narrower: what does that integration cost you after it ships?

The Hidden Costs of Rolling Your Own

The first version is the fun part. The cost shows up later, in the things you have to keep running:

OAuth tokens you babysit forever. Access tokens last 30 minutes, so you are refreshing constantly. The refresh token has a rolling 60-day life, but it is single-use: every refresh returns a brand new refresh token and invalidates the old one, so you must persist the newest value every single time. Store the wrong one and you get a dead connection that only a manual reconnect can fix. Let a connection sit idle past 60 days and it expires too.

Rate limits you engineer around. Xero throttles each connected organisation to 60 calls per minute, with only 5 requests allowed in flight at once and a further daily cap, on top of a 10,000-per-minute ceiling across your whole app. Cross a line and you get an HTTP 429 with a Retry-After header, then you back off and retry. With one client that is trivial. Across dozens of connected organisations, throttling becomes its own scheduling problem.

List calls hide the detail you actually need. Ask Xero for a list of invoices and it returns a trimmed summary with no line items, to keep the response fast. To get the full record you either fetch each invoice individually or page through with the pageSize parameter (100 per page by default, up to 1000). So a single "get my invoices" is rarely one call, it's many.

Incremental sync that quietly misses changes. The efficient way to pull only what changed is the If-Modified-Since header, which filters on each record's UpdatedDateUTC. The catch is that some changes never bump that timestamp, including a contact's outstanding balance and its customer or supplier flags. A naive incremental sync slowly drifts out of date, so you still need a periodic full reconciliation to stay honest.

Webhooks cover almost nothing. If you were hoping to skip polling, Xero webhooks only fire for four things: contacts, invoices, credit notes, and Xero App Store subscriptions. There is nothing for accounts, payments, bank transactions, items, or journals. They emit create and update events only (a deletion or void arrives, if at all, as an update), and the payload carries just a reference, the resource id and URL, not the data itself, so you still call the API to fetch the actual record. Undelivered events are retried and stored for around 31 days, which means idempotency and replay handling are yours to build.

Schema mapping across many objects, and it moves. Invoices, contacts, accounts, bank transactions, credit notes, items, purchase orders, and journals each carry their own shape and their own relationships. You map every one of them into tables, and when Xero changes a payload, you update the mapping.

How Database Sync Works

Database sync takes the opposite approach. Instead of you operating a pipeline against the Xero API, a managed service does the pulling and writes the results straight into your PostgreSQL database.

The flow looks like this:

Connect your PostgreSQL database (Supabase, Neon, Railway, AWS RDS, or any PostgreSQL host)
Authorize Xero once through the standard consent flow, and pick which organisation to sync
The sync service calls the Xero API for you, pages through the data, and writes structured tables
On every later run it pulls what changed and upserts, so there are no duplicates and no gaps

There is no Xero app to register, no refresh-token rotation to schedule, no 429 backoff to code, and no tenant headers to juggle. The accounting data simply shows up in your database, ready to query.

Side-by-Side Comparison

Factor	Build It Yourself (Xero API)	Managed Database Sync
Time to first data	Days to weeks: app, OAuth, tenants, paging, schema	About 5 minutes: connect database, authorize
OAuth token lifecycle	Yours to run: 30-min access, 60-day rolling single-use refresh	Handled for you
Multi-organisation	You enumerate tenants and route every call	Pick an organisation and sync it
Rate-limit handling	You build it: 60/min per org, 5 concurrent, 429 backoff	Managed by the service
Historical backfill	You write paginated backfill	Full backfill on first sync
Incremental updates	You track `UpdatedDateUTC` and its blind spots	Pulls changes and upserts every run
Schema mapping	You map every object and track payload changes	Tables auto-created and maintained
Data freshness	On-demand; near real-time for the 4 webhook objects	Batch (scheduled, e.g. hourly or daily)
Control & customization	Full: fields, transforms, write-back	Standard tables, less custom
Total cost of ownership	Build plus perpetual maintenance	Predictable flat subscription

When to Build It Yourself

Rolling your own is the right call in a few real cases:

You need write-back. Sync tools are read-only by design. If you have to create or update records inside Xero, like raising invoices or adding contacts, you need the API directly.
You need real-time reaction to one of the few events Xero actually pushes, such as a new invoice or contact, rather than a scheduled batch.
You have unusual object or field requirements that a standard sync does not cover.
You already run pipeline infrastructure, so the marginal cost of one more integration is genuinely low.
You want full control over every transform and mapping, and you are willing to maintain it.

If that sounds like you, build it directly against the API and budget for the upkeep above. The control is real, and so is the maintenance.

When to Use Managed Sync

Managed sync is the right call when you mainly want to query the data, not operate a pipeline:

Build dashboards and reporting on revenue, cash flow, or accounts payable and receivable
Run ad-hoc SQL against your accounting data, like "which suppliers are we due to pay this week?"
Join Xero data with your own tables, matching invoices or contacts to your app's users
Keep maintenance near zero, with no token rotation, rate-limit code, or reconciliation job to own
Pay a predictable flat cost instead of perpetual engineer time

Once your data is in Postgres, a question like "what are we owed, and what do we owe?" is just SQL:

-- Net position from xero_invoices: sales owed to you vs bills you owe
SELECT
  type,                                     -- ACCREC = sales invoices, ACCPAY = bills
  COUNT(*) FILTER (WHERE amount_due > 0) AS open_invoices,
  SUM(amount_due) AS outstanding
FROM xero_invoices
WHERE status IN ('AUTHORISED', 'SUBMITTED')
GROUP BY type;

Try doing that against the Xero API directly. You would need paginated calls per organisation, client-side filtering, and careful rate-limit handling. With synced data, it's one query. If you would rather not build any of the pipeline, here is how to sync Xero data to PostgreSQL automatically.

When to Use Both

These two approaches are not mutually exclusive, and the strongest setups use both:

The Xero API (or a webhook) for the few moments you need to react instantly, like flagging a large invoice the second it's approved
Managed sync for the queryable, reconciled copy your team runs reports against

Your app reacts to the events that genuinely need real-time handling, while your team runs any query it likes against the synced database. The common mistake is building and maintaining a whole custom integration just to power dashboards that never needed sub-minute freshness, then paying for that decision in token refreshes, 429 retries, and reconciliation jobs for years.

Getting Started with Database Sync

If you'd rather not build and babysit all of that, Codeless Sync connects your PostgreSQL database and syncs Xero data, contacts, invoices, payments, accounts, bank transactions, credit notes, items, purchase orders, journals, and organisation details, in about 5 minutes. It handles the OAuth consent and token refresh for you, lets you pick which organisation to sync, auto-creates the destination tables, and upserts on every run so there are no duplicates. There's a free tier, no credit card required.

The same model works for Stripe, QuickBooks, and Paddle too, so if your finances span more than one provider, all of it lands in the same Postgres database. You can point it at Supabase, Neon, Railway, or AWS RDS, whichever host you already use.

Frequently Asked Questions

Should I build my own Xero integration or use a sync tool?

Build it if you need to write data back into Xero, want real-time reaction to specific events, or already run pipeline infrastructure. Use a sync tool if you mainly need a queryable copy of your accounting data for dashboards, reporting, or reconciliation, and you would rather not own OAuth token rotation, tenant routing, and rate-limit handling. Plenty of teams do both: the API for the few real-time events, managed sync for everything else.

What does it actually cost to maintain a Xero API integration?

The code is a one-time build. The cost is everything after: rotating the single-use refresh token on every refresh before the 60-day window lapses, backing off when you hit the 60-calls-per-minute or 5-concurrent limit on each organisation, running a periodic reconciliation for changes that don't bump UpdatedDateUTC, and updating your mapping when Xero changes a payload. That is recurring engineer time, which is what a flat-rate managed sync is really replacing.

Does a managed Xero sync still use the Xero API?

Yes. A sync tool like Codeless Sync talks to the same OAuth 2.0 and Accounting API endpoints under the hood. The difference is that it operates the consent flow, the tenant selection, the pagination, and the schema mapping for you, so what you end up with is a set of PostgreSQL tables to query rather than a pipeline to maintain.

Can Xero webhooks replace a scheduled sync?

Not for analytics. Xero webhooks only fire for contacts, invoices, credit notes, and App Store subscriptions, emit create and update events only (no deletes), and carry just a reference rather than the record itself, so you still call the API to fetch the data. For a complete, queryable copy of your accounting data, a scheduled sync is more reliable and needs no public endpoint. Use webhooks only for the specific events you must react to the instant they happen.

Which Xero data can I sync to my database?

Codeless Sync supports ten Xero data types: contacts, invoices, payments, accounts, bank transactions, credit notes, items, purchase orders, journals, and organisation details, each written to its own PostgreSQL table. After the first full pull, later runs are incremental, so each sync only brings over what changed.

Related:

QuickBooks API vs Database Sync: Which is Better?

ilshaad — Tue, 30 Jun 2026 11:30:30 +0000

QuickBooks API vs database sync for getting accounting data into PostgreSQL: a build-vs-buy guide covering OAuth, rate limits, and the real maintenance cost.

By Ilshaad Kheerdali · 30 June 2026

If you're building on top of QuickBooks Online, you'll eventually need that accounting data in your own database, to power dashboards, reconcile revenue, or join it against your product tables. There are two ways to get it there: build your own integration against the QuickBooks API, or hand the job to a managed database sync. Both work, but they cost you very different things.

It helps to be honest about the framing first. This isn't really "API versus no API", because a managed sync uses the QuickBooks API under the hood too. The real question is build versus buy: do you own the OAuth tokens, the polling, the rate-limit handling, and the schema mapping yourself, or do you let a service run all of that for you? This post compares the two at decision altitude. (If you also want CSV, IIF, and Zapier in the mix, this other post compares five ways to export QuickBooks data.)

How a QuickBooks API Integration Works

Building your own integration means going directly to Intuit's developer platform. The shape of the work is always the same:

Register an app in the Intuit Developer portal and get your client credentials.
Send each customer through the OAuth 2.0 consent flow, then store the access and refresh tokens against their company (Intuit calls the company a realmId).
Poll the API for the entities you care about (customers, invoices, payments, and so on), or subscribe to webhooks for change events.
Map roughly 30 different entity shapes into your own tables, and keep refreshing tokens so the connection never goes stale.

In code, the loop you end up owning looks something like this:

// The moving parts you operate yourself, on every connected company, forever
const accessToken = await refreshIfExpired(company.realmId); // 1-hour access tokens
const changes = await pollQuickBooks(accessToken, ['Invoice', 'Customer', 'Payment']);

for (const entity of changes) {
  await upsertIntoPostgres(entity); // your mapping, your schema
}

// plus: token rotation, 429 backoff, missed-event recovery, and schema drift

That snippet hides a lot. The full version, the OAuth handshake, pagination, Change Data Capture, and webhook verification, is a project in itself. We wrote the whole thing up in the QuickBooks API integration guide if you want the line-by-line version. The point here is narrower: what does that integration cost you after it ships?

The Hidden Costs of Rolling Your Own

The first version is the fun part. The cost shows up later, in the things you have to keep running:

OAuth tokens you babysit forever. Access tokens last one hour, so you are refreshing them constantly. The refresh token lasts 100 days, but it rotates roughly every day, and if you ever store the wrong one you get an invalid_grant and a dead connection. Intuit also added a hard five-year cap on refresh tokens in late 2025, so even a flawlessly maintained connection eventually forces the customer to reconnect by hand.

Rate limits you engineer around. QuickBooks throttles each company to 500 requests per minute and 10 requests per second. Cross either line and you get an HTTP 429 with a ThrottleExceeded error, then you back off and retry. With one client that is trivial. Across dozens of connected companies, throttling becomes its own scheduling problem.

Webhooks that aren't guaranteed. QuickBooks can push change events, but Intuit is clear that delivery is best-effort. Their own recommended fix for missed events is to run a scheduled Change Data Capture (CDC) reconciliation job on the side. So the "real-time" path still needs a polling backstop before you can trust it.

A reconciliation window with hard edges. That CDC backstop only looks back 30 days, and returns at most 1,000 objects per response. If your sync is down longer than a month, CDC cannot fill the gap, and you are back to a full re-pull. You own that recovery logic.

Schema mapping across ~30 entities, and it moves. Every entity has its own payload shape to map, and the target shifts under you. Intuit is migrating the webhook payload to a new CloudEvents format with a July 31, 2026 cutover deadline, so the mapping you wrote last year is not necessarily the mapping you ship next year.

Ordering and duplicates are your problem. A single notification can carry events for several companies at once, and those events can arrive out of order or more than once. The payload timestamp is the only source of truth, so the idempotency and per-company ordering logic is yours to build and test.

How Database Sync Works

Database sync takes the opposite approach. Instead of you operating a pipeline against the QuickBooks API, a managed service does the pulling and writes the results straight into your PostgreSQL database.

The flow looks like this:

Connect your PostgreSQL database (Supabase, Neon, Railway, AWS RDS, or any PostgreSQL host)
Authorize QuickBooks once through the standard Connect flow
The sync service calls the QuickBooks API and CDC for you, and writes structured tables
On every later run it pulls what changed and upserts, so there are no duplicates and no gaps

There is no Intuit app to register, no token rotation to schedule, no 429 backoff to code, and no CDC window to manage. The accounting data simply shows up in your database, ready to query.

Side-by-Side Comparison

Factor	Build It Yourself (QuickBooks API)	Managed Database Sync
Time to first data	Days to weeks: app, OAuth, polling, schema	About 5 minutes: connect database, authorize
OAuth token lifecycle	Yours to run: 1h access, 100-day rolling refresh, 5-year cap	Handled for you
Rate-limit handling	You build it: 500/min, 10/sec, 429 backoff	Managed by the service
Historical backfill	You write paginated backfill	Full backfill on first sync
Missed-event recovery	You build a CDC job: 30-day window, gaps beyond	Re-pulls and upserts every run
Schema mapping	You map ~30 entities and track payload changes	Tables auto-created and maintained
Data freshness	On-demand, near real-time possible	Batch (scheduled, e.g. hourly or daily)
Control & customization	Full: fields, transforms, write-back	Standard tables, less custom
Total cost of ownership	Build plus perpetual maintenance	Predictable flat subscription

When to Build It Yourself

Rolling your own is the right call in a few real cases:

You need write-back. Sync tools are read-only by design. If you have to create or update records inside QuickBooks, you need the API directly.
You need on-demand or real-time freshness for specific events, not a scheduled batch.
You have unusual entity or field requirements that a standard sync does not cover.
You already run pipeline infrastructure, so the marginal cost of one more integration is genuinely low.
You want full control over every transform and mapping, and you are willing to maintain it.

If that sounds like you, build it directly against the API and budget for the upkeep above. The control is real, and so is the maintenance.

When to Use Managed Sync

Managed sync is the right call when you mainly want to query the data, not operate a pipeline:

Build dashboards and reporting on revenue, accounts receivable, or cash flow
Run ad-hoc SQL against your accounting data, like "which customers owe us more than £5,000 right now?"
Join QuickBooks data with your own tables, matching invoices to your app's users or orders
Keep maintenance near zero, with no token rotation, rate-limit code, or CDC job to own
Pay a predictable flat cost instead of perpetual engineer time

Once your data is in Postgres, a question like "how much am I owed, and how overdue is it?" is just SQL:

-- Outstanding invoices from quickbooks_invoices, aged into buckets
SELECT
  CASE
    WHEN CURRENT_DATE <= due_date THEN 'Not yet due'
    WHEN CURRENT_DATE - due_date <= 30 THEN '1-30 days overdue'
    WHEN CURRENT_DATE - due_date <= 60 THEN '31-60 days overdue'
    WHEN CURRENT_DATE - due_date <= 90 THEN '61-90 days overdue'
    ELSE '90+ days overdue'
  END AS age_bucket,
  COUNT(*) AS invoice_count,
  SUM(balance) AS outstanding
FROM quickbooks_invoices
WHERE balance > 0
GROUP BY age_bucket
ORDER BY outstanding DESC;

Try doing that against the QuickBooks API directly. You would need paginated calls, client-side filtering, and careful rate-limit handling. With synced data, it's one query. If you would rather not build any of the pipeline, here is how to sync QuickBooks data to PostgreSQL automatically.

When to Use Both

These two approaches are not mutually exclusive, and the strongest setups use both:

The QuickBooks API (or webhooks) for the few moments you need to react instantly, like flagging a payment the second it lands
Managed sync for the queryable, reconciled copy your team runs reports against

Getting Started with Database Sync

If you'd rather not build and babysit all of that, Codeless Sync connects your PostgreSQL database and syncs QuickBooks data, customers, invoices, payments, items, accounts, vendors, and bills, in about 5 minutes. It handles the OAuth and token refresh for you, auto-creates the destination tables, upserts on every run so there are no duplicates, and recovers from gaps on the next scheduled sync. There's a free tier, no credit card required.

The same model works for Stripe, Paddle, and Xero too, so if your finances span more than one provider, all of it lands in the same Postgres database.

Frequently Asked Questions

Should I build my own QuickBooks integration or use a sync tool?

Build it if you need to write data back into QuickBooks, want real-time reaction to specific events, or already run pipeline infrastructure. Use a sync tool if you mainly need a queryable copy of your accounting data for dashboards, reporting, or accounting, and you would rather not own OAuth token rotation, rate-limit handling, and missed-event recovery. Plenty of teams do both: the API for the few real-time events, managed sync for everything else.

What does it actually cost to maintain a QuickBooks API integration?

The code is a one-time build. The cost is everything after: refreshing tokens before they rotate or hit the new five-year cap, backing off when you hit the 500-requests-per-minute limit, running a Change Data Capture job to catch missed webhook events, and updating your payload mapping when Intuit changes the format, as with the CloudEvents migration. That is recurring engineer time, which is what a flat-rate managed sync is really replacing.

Does a managed QuickBooks sync still use the QuickBooks API?

Yes. A sync tool like Codeless Sync talks to the same Intuit API and CDC endpoints under the hood. The difference is that it operates the OAuth flow, the polling, and the schema mapping for you, so what you end up with is a set of PostgreSQL tables to query rather than a pipeline to maintain.

Can I move from a custom integration to managed sync without losing data?

Yes. A sync upserts into a PostgreSQL database that you own and control, so there is no lock-in. You can run your existing integration and a managed sync side by side during a transition, compare the tables, and cut over only once you are happy the synced data matches.

Is a managed sync real-time, or will my QuickBooks data be stale?

Managed sync is batch based, on a schedule such as hourly or daily, which is plenty for analytics, reporting, and the accounting close. If you genuinely need to react the instant something happens in QuickBooks, pair the sync with a webhook for just those specific events, and let the scheduled sync keep the full queryable copy current.

Related:

How to Validate and Secure Your Stripe API Keys

ilshaad — Sat, 27 Jun 2026 10:41:50 +0000

Validate and secure your Stripe API keys: key types and prefixes, restricted least-privilege keys, safe storage, key rotation, and what to do after a leak.

By Ilshaad Kheerdali · 27 June 2026

A leaked Stripe secret key is not a small mistake. Anyone holding your sk_live_ key can create charges, issue refunds, read your entire customer list, and trigger payouts to themselves. That is full control of the money side of your business, handed over in a single string.

The frustrating part is that almost none of these leaks come from clever attacks. They come from sloppy handling: a key pasted into a frontend bundle, committed to a public repo, dropped into a Slack thread, or baked into a screenshot in a bug report. Bad actors run automated scanners against public repositories around the clock, so a key that hits GitHub can be abused within minutes.

This post walks through how to recognise each Stripe credential type, validate a key before you trust it, store it safely, and rotate it without downtime. If you want to sanity-check a key right now, paste it into the free Stripe API Key Validator. It runs entirely in your browser and never sends the key anywhere.

Understanding Stripe API key types and prefixes

Stripe uses the key prefix to encode both what a credential is and which mode it belongs to. Get fluent in reading prefixes and most key mistakes disappear.

Prefix	Type	Safe client-side?	What it does
`pk_test_` / `pk_live_`	Publishable	Yes	Identifies your account in browser/mobile code (Stripe.js, Elements)
`sk_test_` / `sk_live_`	Secret	No, server only	Full access to your account via the API
`rk_test_` / `rk_live_`	Restricted	No, server only	Scoped access limited to the permissions you grant
`whsec_`	Webhook signing secret	No	Verifies webhook events came from Stripe, not an API key
`sk_org_`	Organization key	No, server only	Organization-level access across multiple Stripe accounts

The middle substring is the mode switch. _test_ keys operate on sandbox data and _live_ keys operate on real data, and objects never cross between modes. A test key pointed at live data will silently return empty results rather than throw a loud error, which is why a "my sync returns nothing" bug is so often just a test/live mismatch.

Stripe is blunt about exposure in its official key documentation: "Only publishable keys are safe to expose outside your application's backend. You're responsible for protecting other Stripe API keys, including restricted API keys." Publishable keys are designed to be visible. Everything else belongs server-side.

How to validate and secure your Stripe API keys

Before you wire a key into config or paste it into a third-party tool, confirm it is actually the key you think it is. There are three quick checks:

Prefix. Does it start with the type you intended? If you meant to grant read-only access but the string starts with sk_live_, you are about to hand over full account control.
Mode. Is it _test_ or _live_? Make sure it matches the environment you are configuring.
Character set and length. Stripe keys are a fixed alphabet with an expected length. A truncated copy-paste or a stray whitespace character is a common reason a "correct" key fails auth.

Doing this by eye is error-prone, especially when keys are masked in dashboards. The Stripe API Key Validator does all three at once: it detects the key type and mode from the prefix, validates the character set and length, masks the value for safe display with a visibility toggle, and prints security guidance specific to that key type. Crucially it is 100% client-side with zero network requests, so even a live secret key never leaves your browser. That makes it safe to use on a real key, unlike a random "paste your key here" web form you should never trust.

To secure your Stripe API keys properly, validation is step one. The rest of this post covers the handling rules that keep a valid key from becoming a liability.

Why the webhook signing secret (whsec_) is not an API key

This trips up a lot of developers. The whsec_ value looks like a key, so people try to authenticate API requests with it and get nothing but auth errors.

The webhook signing secret is not a credential for calling Stripe. Stripe generates a unique secret for each webhook endpoint, and you use it to verify that incoming events genuinely came from Stripe rather than a spoofed request. Your handler reads the Stripe-Signature header and runs it through constructEvent (or Webhook.construct_event) along with the secret. Signature scheme v1 is used, and Stripe's libraries enforce a default timestamp tolerance of 5 minutes to block replay attacks.

Two things ruin webhook verification in practice. First, you must verify against the raw request body, not a parsed and re-serialized JSON object, or the signature will never match. Second, the secret is per-endpoint: the secret printed by the Stripe CLI is different from the one shown for a Dashboard endpoint, so using the wrong one fails every time. See the Stripe webhooks documentation for the exact verification flow in your language.

Restricted keys and the principle of least privilege

If your code only needs to read data, never give it a key that can move money. A restricted API key (RAK) starts with rk_live_ or rk_test_ and, in Stripe's words, "can do only what you give it permission to do." When you create one in the Dashboard you set a permission of Read, Write, or None per Stripe resource. Note that write implies read: any key that can write a resource can also read it.

This is the principle of least privilege in action, where a key should have the minimum permissions necessary to do its job and no more. Stripe's own example is sharp: a restricted key scoped to read dispute data only lets a bad actor read dispute data. They cannot create charges, touch customer payment methods, or trigger payouts. The blast radius of a leak shrinks to almost nothing.

Stripe recommends giving each service its own restricted key (billing, reporting, and your webhook handler each get a separate scoped key) and recommends always preferring restricted keys over unrestricted secret keys, especially when handing a key to an AI agent or any third-party integration. The full guidance lives in the restricted API keys docs. The takeaway: a single all-powerful sk_live_ shared across every integration is the worst possible pattern, and the easiest one to fix.

Storing keys safely: environment variables, secrets managers, and never committing to git

Once you have the right key, where it lives matters as much as what it can do. Stripe's best practices guide lays out a clear hierarchy.

Never put secret keys in source code. Bad actors continuously scan public repositories for Stripe keys. And remember git history retains a key even after you delete it from the latest commit, so a "quick fix" that removes the line does nothing unless you rewrite history and rotate the key.
Never embed keys in client applications. Use publishable keys client-side. A secret key in a frontend bundle or mobile app is a full-account compromise waiting to be discovered.
Store secrets in a vault. AWS Secrets Manager, Google Cloud Secret Manager, Azure Key Vault, or HashiCorp Vault are the recommended homes. Environment variables are an acceptable fallback when a vault is not available.
Add a guardrail at commit time. Periodically audit your codebase for sk_live_ and rk_live_ patterns, and add a pre-commit hook that rejects any commit containing them. This catches the mistake before it ever reaches a remote.

A minimal example of reading a key from the environment rather than hard-coding it:

// Good: the key lives in the environment, never in the repo
const stripe = require('stripe')(process.env.STRIPE_RESTRICTED_KEY);

// Bad: this string is now in your git history forever
// const stripe = require('stripe')('sk_live_51Hxxxx...');

You can also restrict keys to stable IP addresses and monitor your API request logs to spot misuse early. Limiting where a key works and watching how it is used are both cheap insurance.

Rotating and expiring Stripe keys without downtime

Keys are not set-and-forget. Rotate them periodically, and rotate immediately whenever a team member with access leaves or a key has been pasted somewhere it should not be.

Rotating a key in the Dashboard revokes it and generates a replacement that is ready to use immediately. The detail that saves you from an outage: both the old and new keys keep working for up to 7 days. That window lets you deploy the new key everywhere before the old one dies, so older deployments still holding the previous key do not suddenly break. If you need longer than 7 days, create a new key manually, migrate, then expire the old one. When you do cut over, you can choose Now to delete the old key instantly or schedule a future expiration.

One quirk worth knowing: you can expire a secret or restricted key (after which you create a new one and update your code), but you cannot expire a publishable key. Publishable keys can only be rotated and replaced. Also, a key left unused for 180 or more days may have its access limited, which you can restore from the Dashboard.

The mistake to avoid is a hard cutover that revokes the old key the instant you create the new one. Use the dual-key window instead and a rotation becomes a non-event.

What to do if your Stripe key is exposed

Treat any exposure as a compromise, full stop. Stripe's guidance is unambiguous: if a restricted or secret key is exposed or compromised, rotate it immediately even if you are not sure anyone saw it. If you find a sensitive key somewhere it should not be, assume it has been seen.

Concretely:

Rotate the key now. Do not wait to confirm misuse. Generate a replacement using the dual-key window and migrate.
Audit your request logs. Check Stripe's API logs for unexpected charges, refunds, or reads around the time of exposure.
Find the source. A key in git history needs the history rewritten, not just a new commit. A key in a screenshot or chat needs that artefact removed too.

Stripe does proactively monitor for exposed keys and may deactivate one and notify you, but it explicitly does not guarantee it will catch every leak. Your own rotation and monitoring process is the real safety net.

Stop hand-rolling key management: let CLS handle it

Here is where most of this gets easier in practice. If your reason for touching the Stripe API at all is to get your payments data into Postgres, you do not need a powerful secret key sitting in your own pipeline.

CLS syncs Stripe data into your PostgreSQL database (Supabase, Neon, AWS RDS, Railway, and more) and only ever needs read access. The best-practice setup is exactly what this post recommends: create a restricted, read-only key scoped to the resources you want to sync, then connect that. CLS stores the credential encrypted at rest with AES-256, runs managed scheduled syncs so you are not writing and babysitting your own cron jobs, and auto-creates the tables for you. The walkthrough is in the Stripe setup guide.

So validate your key in the Stripe API Key Validator, scope it down to read-only, and hand the minimal version to whatever consumes it. If that consumer is your analytics warehouse, see how to sync Stripe data to PostgreSQL and a comparison of the best tools to sync Stripe data to a database.

Frequently Asked Questions

What does a Stripe secret key look like?

A Stripe secret key starts with sk_test_ in test mode or sk_live_ in live mode, followed by a long string of letters and numbers. It grants full access to your account, so it must stay server-side and never appear in client code or source control. You can confirm a key's type and mode by checking its prefix, or by pasting it into a client-side validator.

Can I expose my Stripe publishable key?

Yes. Publishable keys (pk_test_ and pk_live_) are the only Stripe credentials designed to be safe in browser and mobile code. They identify your account to Stripe.js and Elements but cannot read sensitive data or move money. Every other key type, including restricted keys, must be kept private.

How do I rotate a Stripe API key?

Rotate it from the Stripe Dashboard, which revokes the old key and issues a replacement immediately. Both the old and new keys keep working for up to 7 days, so deploy the new key everywhere within that window before the old one expires. If you need more time, create a new key manually, migrate, then expire the old one.

What is the whsec_ webhook secret used for?

The whsec_ value verifies that incoming webhook events genuinely came from Stripe. It is not an API key and cannot authenticate API requests. Your handler passes the raw request body, the Stripe-Signature header, and this secret into a verification function to confirm the event is legitimate and not replayed.

Are restricted keys safer than secret keys?

Yes, when scoped correctly. A restricted key (rk_) only has the permissions you grant it, so a leaked read-only key cannot create charges or trigger payouts. Stripe recommends giving each service its own restricted key and preferring restricted keys over unrestricted secret keys, especially for third-party integrations and AI agents.

Related:

How to Use Cron Expressions for Scheduled Data Syncs

ilshaad — Fri, 26 Jun 2026 10:51:39 +0000

Learn cron expressions for scheduled data syncs: the 5 fields, special characters, common sync schedules, the day-of-week OR trap, and timezone gotchas.

By Ilshaad Kheerdali · 26 June 2026

You wanted one simple thing: keep a Postgres copy of your Stripe, QuickBooks, or Xero data fresh on a schedule. Someone on your team said "just use cron." Now you are staring at five asterisks in a terminal, unsure whether 0 9 * * 1-5 means 9am your time or 9am somewhere else, and whether you just told a server to run a job every minute by accident.

Cron is a genuinely good tool, and once the syntax clicks it stops being scary. This guide walks through the standard 5-field format, the special characters, real sync schedules you will actually use, and the handful of gotchas that quietly break jobs in production. If you want to skip ahead and just see an expression broken down with its next run times, paste it into the free Cron Expression Generator while you read.

How cron expressions work for scheduled data syncs

A standard cron expression is five space-separated fields that together describe when a job should run. Read left to right, they are: minute, hour, day of month, month, and day of week. There is no seconds field in standard cron, which is the single most common source of confusion (more on that later).

Building cron expressions for scheduled data syncs really comes down to answering one question per field: at which minutes, hours, days, months, and weekdays should this sync fire? The cron daemon checks the clock once a minute and runs any entry whose five fields all match the current time. The authoritative reference is the Linux crontab(5) man page, which is worth a bookmark.

Reading the five fields: minute, hour, day-of-month, month, day-of-week

Here is the layout, with the valid range for each field:

* * * * *
| | | | |
| | | | +-- day of week  (0-7, both 0 and 7 are Sunday; names allowed)
| | | +---- month         (1-12, names allowed)
| | +------ day of month  (1-31)
| +-------- hour          (0-23)
+---------- minute        (0-59)

A couple of details matter. The day-of-week field treats both 0 and 7 as Sunday under the Vixie/Linux convention used by most Linux servers. Strict POSIX only defines 0-6 with 0 as Sunday and does not include 7, so do not assume 7=Sunday works everywhere. Month and day-of-week also accept three-letter names like JAN or MON, but numbers are more portable across schedulers.

Special characters: asterisk, comma, hyphen, and the step operator

Four characters do most of the work:

Asterisk (*) means "every value" for that field, literally first through last. * * * * * runs every minute.
Hyphen (-) defines an inclusive range. 1-5 in the day-of-week field is Monday through Friday.
Comma (,) defines a list. 1,3,5 is Monday, Wednesday, Friday.
Slash (/) is the step operator. */15 in the minute field means every 15 units, so minutes 0, 15, 30, and 45.

You can combine them. 0,30 9-17 * * 1-5 means at minute 0 and 30, during hours 9 through 17, Monday through Friday. Keep steps in the portable */n form; some implementations treat 0/15 and */15 differently, so stick with */n to avoid surprises.

Cron expression examples for common sync schedules

Most data-sync jobs fall into a few recurring shapes. Here are the ones you will reach for, each tied to a real use case:

*/5 * * * *      Every 5 minutes        Near-real-time mirror of fast-moving data
0 * * * *        Every hour at :00       Hourly refresh of invoices or subscriptions
0 9 * * 1-5      09:00 Mon-Fri           Business-hours-only sync for a reporting DB
0 2 * * *        02:00 every day          Nightly off-peak full sync
0 0 1 * *        Midnight on the 1st     Monthly reconciliation snapshot
0 0 * * 0        Midnight every Sunday    Weekly rollup before Monday standup

A few notes on intent. */5 * * * * is the workhorse for keeping a Postgres copy of Stripe or QuickBooks reasonably fresh without hammering the API. 0 2 * * * runs at 2am, which on a UTC server is genuinely off-peak for most US and EU traffic, making it ideal for a heavier full sync. If you want to confirm any of these or build your own, drop it into the Cron Expression Generator: it parses the five fields, gives you a plain-English description, and lists the next five run times in UTC.

The day-of-month vs day-of-week OR rule that breaks schedules

This is the gotcha that catches even experienced developers. Suppose you want "Friday the 13th" and write 0 0 13 * 5, expecting day-of-month 13 and day-of-week Friday. It does not do that. The crontab(5) man page is explicit:

If both fields are restricted (i.e., do not contain the * character), the command will be run when either field matches the current time.

So 0 0 13 * 5 actually runs at midnight on the 13th of every month and every Friday. POSIX confirms the same OR behaviour. The two day fields are combined with OR, not AND, whenever both are restricted. The practical fix: keep one of the two fields as * and test the other condition inside your command, or use a scheduler that does AND matching. If your sync only needs to skip weekends, 0 2 * * 1-5 is safe because the day-of-month field stays *.

5 fields vs 6: why your cron expression has the wrong number of fields

The other classic failure is field count. Standard cron has exactly 5 fields. Quartz Scheduler, used by a lot of Java and Spring apps, uses 6 or 7 fields: it adds a leading Seconds field (0-59) and an optional trailing Year (1970-2099). The Quartz CronTrigger tutorial documents this format.

The trap is copy-paste. If you lift a 6-field Quartz expression like 0 0 9 * * ? into a 5-field crontab, every field shifts one position to the left and you silently schedule the wrong time. Going the other direction is just as bad. Before pasting any expression, confirm whether the target system expects 5 fields or 6. The Cron Expression Generator validates standard 5-field cron only, so a 6-field expression will flag immediately rather than fail at runtime.

@hourly, @daily, @weekly: cron macros that save typing

Vixie cron, the implementation on most Linux boxes, supports nickname macros so you do not have to memorise the field layout for common cases:

@hourly   ->  0 * * * *
@daily    ->  0 0 * * *
@weekly   ->  0 0 * * 0
@monthly  ->  0 0 1 * *
@yearly   ->  0 0 1 1 *
@reboot   ->  runs once at daemon startup

These are a cron extension, not POSIX, so they will not exist on every scheduler. But on a standard Linux server @daily and 0 0 * * * are identical, and the macro is harder to typo.

Cron, UTC, and daylight saving time pitfalls

Cron evaluates schedules in the daemon's timezone, which is the system local time and is commonly UTC on servers. That means 0 9 * * * is 9am in the server's timezone, not your wall clock. If your laptop is in London and your server is on UTC, those happen to line up in winter and drift by an hour in summer. Vixie/cronie supports a CRON_TZ variable to pin a timezone for crontab entries if you really need local time.

Daylight saving is where this gets nasty, and the exact behaviour depends on your scheduler. The cronie/Vixie cron on most Linux servers actually tries to compensate for shifts under three hours: at spring-forward, a fixed-time job whose hour is skipped is run immediately instead of being lost, and at fall-back, cron avoids running the same fixed-time job twice. Clock changes larger than three hours are treated as a correction and the new time is just adopted. The catch is that this only covers fixed-time entries on cronie. Other schedulers, container cron, and managed platforms each handle the transition differently, so you cannot assume the missed or doubled run is handled for you. The clean fix is to keep your servers on UTC and make your syncs idempotent, so a double-run or a skipped-run never corrupts your data. This is also why the generator tool reports its next run times in UTC: it removes the ambiguity rather than guessing your local offset.

From cron jobs to managed scheduled syncs with Codeless Sync

Here is the honest part. Cron syntax is the easy bit. Running a reliable sync on cron means owning everything around it: a server to host the job, monitoring so you know it actually ran, retries when the provider API times out, overlap handling when a 5-minute schedule meets an 8-minute run, and alerting for the day it silently stops. That operational tail is exactly why a self-rolled Stripe to Postgres sync keeps breaking.

Codeless Sync takes that whole layer off your plate. Instead of writing cron and babysitting a worker, you pick a frequency (Hourly, Daily, Weekly, or Monthly) plus a sync mode (full or incremental), and CLS runs and monitors the sync for Stripe, QuickBooks, Xero, and Paddle into your own Postgres database. There is no cron server to keep alive and no DST math to get wrong. See how schedules work in the docs, and if you still want to understand or hand-tune a raw expression for your own jobs, the Cron Expression Generator is free and entirely client-side. When you are ready to stop maintaining pipelines, the pricing page lays out the managed option.

Frequently Asked Questions

What are the 5 fields in a cron expression?

Standard cron uses five fields in this order: minute (0-59), hour (0-23), day of month (1-31), month (1-12), and day of week (0-7, where both 0 and 7 are Sunday). The daemon runs your command when all five fields match the current time. Month and day-of-week also accept names like JAN or MON.

Does cron have a seconds field?

No. Standard, POSIX, and Vixie cron all use exactly five fields with no seconds, so the smallest interval you can schedule is one minute. Schedulers like Quartz add a leading Seconds field and an optional Year, giving 6 or 7 fields. That difference is the most common reason a copied expression schedules the wrong time.

Why does my Friday the 13th cron job run on the wrong days?

Because cron ORs the day-of-month and day-of-week fields when both are restricted. An expression like 0 0 13 * 5 runs on the 13th of every month AND every Friday, not only on Friday the 13th. To require both conditions, keep one field as * and test the other inside your command, or use a scheduler that does AND matching.

What timezone do cron jobs run in?

Cron runs in the daemon's timezone, which is the system local time and is usually UTC on servers. So 0 9 * * * is 9am in that timezone, not necessarily your local 9am. Vixie/cronie supports CRON_TZ to override per crontab, but keeping servers on UTC is the simplest way to avoid daylight saving bugs.

What does /5 * * * mean?

It means "every 5 minutes." The */5 in the minute field is the step operator, firing at minutes 0, 5, 10, and so on through 55, while the four * fields match every hour, day, month, and weekday. It is the most common schedule for keeping a near-real-time copy of fast-moving data.

Related:

How to Fix a PostgreSQL Connection String That Won't Connect

ilshaad — Thu, 25 Jun 2026 12:40:13 +0000

You copied the connection string straight out of your provider's dashboard (Supabase, Neon, Railway, AWS RDS, DigitalOcean) and pasted it into your app or psql, and it still won't connect. The string looks right. It came from the source of truth. And yet the terminal throws password authentication failed, or could not connect to server: Connection refused, or some cryptic Tenant or user not found you've never seen before.

Here's the thing almost every tutorial gets wrong: it assumes you control the Postgres server and can edit pg_hba.conf, restart the daemon, or sudo -i -u postgres. If you're on a managed host, you can't touch any of that. The fix lives in the connection string itself: the host, the port, the username format, the sslmode, and how special characters in your password are encoded.

This post is an error-message-indexed troubleshooter. Find the exact text your terminal printed, read the cause, apply the connection-string-level fix first. It pairs with our free PostgreSQL Connection String Validator, which parses your string in the browser and flags these issues before you deploy.

Anatomy of a PostgreSQL connection string

Almost every connection failure is a problem with one specific part of the URI. So before the error catalogue, here's the map. A standard PostgreSQL connection string looks like this:

postgresql://user:password@host:port/database?sslmode=require

Per the official libpq documentation, the scheme can be either postgresql:// or postgres://, and both are accepted and identical. Breaking it down:

Component	Example	Notes
Scheme	`postgresql://`	`postgres://` works too
User	`postgres`	On Supabase poolers it must be `postgres.<project-ref>` (more below)
Password	`p%40ssw0rd`	Special characters must be percent-encoded
Host	`db.abc.supabase.co`	DNS name or IP; managed hosts often have pooler vs direct hostnames
Port	`5432`	Default is 5432, but DigitalOcean uses 25060, Supabase pooler 6543
Database	`postgres`	The database name, not the project name
Query params	`?sslmode=require`	TLS mode, plus `pgbouncer=true`, `channel_binding=require`, etc.

libpq is explicit that "the connection URI needs to be encoded with percent-encoding if it includes symbols with special meaning in any of its parts." That single rule causes a large share of "wrong password" errors that aren't actually wrong passwords. Keep this table handy, since every fix below maps back to one of these fields.

The most common reasons your connection string won't connect

A quick triage trick before the details: the error message tells you which layer failed. could not translate host name is DNS, before any TCP. Connection refused means TCP reached the host but nothing is listening. no pg_hba.conf entry and password authentication failed mean you reached Postgres and it's talking to you, so it's auth/config, not network. server does not support SSL is a TLS-layer mismatch. Matching the message to the layer saves hours. The six sections below cover the most frequent failures; the quick-reference table at the end adds one more (node-postgres self-signed certificates).

1. password authentication failed

FATAL: password authentication failed for user "andym"

Cause. You reached the server and it's willing to talk, but it rejected your credentials. The obvious culprit is a wrong username or password. The non-obvious one, and the reason this error shows up even when you know the password is right, is special characters in the password that aren't percent-encoded inside the URI. The parser misreads or truncates the password at the first reserved character.

A # is especially nasty: in URI syntax it begins a fragment. A strict parser rejects the whole string (node-postgres throws Invalid URL); a lax one drops everything after the # from the password. Either way the password that reaches the server is wrong, with little hint why, so encode it as %23.

Fix. Percent-encode the reserved characters in the password. The ones that actually break parsing are @, /, ?, #, and % (plus :, which otherwise splits the username from the password); the others below are optional but harmless to encode:

Char	Encoded	Char	Encoded
`@`	`%40`	`?`	`%3F`
`:`	`%3A`	`&`	`%26`
`/`	`%2F`	space	`%20`
`#`	`%23`	`$`	`%24`
`%`	`%25`	`=`	`%3D`
`[`	`%5B`	`]`	`%5D`

A password of p@$$w0rd needs the @ encoded at minimum (so p%40$$w0rd already connects, since $ is legal unencoded), and the fully encoded p%40%24%24w0rd works just as well:

# Broken: the second @ is read as the user/host separator, splitting the password
postgresql://postgres:p@$$w0rd@db.abc.supabase.co:5432/postgres

# Fixed: password percent-encoded
postgresql://postgres:p%40%24%24w0rd@db.abc.supabase.co:5432/postgres

When in doubt, encode the whole password before pasting it. If this is the error you keep hitting, drop the string into the connection string validator, which flags unencoded characters instantly.

2. could not translate host name

could not translate host name "db.abc.supabase.co" to address: Name or service not known

Cause. DNS resolution failed: your OS couldn't turn the hostname into any usable IP address. Usually a typo in the host. But there's a managed-host trap: Supabase's direct connection host (db.<ref>.supabase.co) resolves only to IPv6 by default. On an IPv4-only network or runtime (most serverless platforms like Vercel, Lambda, and Cloudflare, plus plenty of corporate and home networks), no usable address comes back. Depending on your resolver, a pure IPv6 resolution may instead surface later as Network is unreachable. Either way, the fix is the same. (The Name or service not known suffix is the Linux/glibc wording; macOS prints nodename nor servname provided, or not known, and Docker containers often print Temporary failure in name resolution for the same DNS failure.)

Fix. If it's a typo, fix the host. For Supabase on IPv4-only networks, switch to a pooler hostname, which is IPv4 across all tiers, or buy the IPv4 add-on for the direct connection:

# Direct (IPv6-only by default): fails on IPv4-only runtimes
postgresql://postgres:pw@db.abc.supabase.co:5432/postgres

# Session pooler (IPv4): note the postgres.<ref> username
postgresql://postgres.abc:pw@aws-0-eu-west-1.pooler.supabase.com:5432/postgres

Be careful with nslookup here: a plain nslookup db.abc.supabase.co (or ping) will often return an IPv6 (AAAA) record and look perfectly healthy even though an IPv4-only stack can't use it. Check specifically for an A record (nslookup -type=A db.abc.supabase.co), and treat "resolves to IPv6 only, no IPv4 answer" as the real signal. For an end-to-end test, psql -h <host> from the same environment your app runs in. See the pooler section below for the full breakdown.

3. no pg_hba.conf entry ... no encryption

FATAL: no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb", no encryption

Cause. You reached the server, but no matching rule in pg_hba.conf allows your connection. The trailing no encryption describes your side: your client connected in plaintext (that wording is PostgreSQL 12+; pre-12 said SSL off). The usual reason no rule matches a plaintext attempt is that the rule that would match is hostssl (TLS-only), so the practical fix is almost always the same: turn on TLS. The exact trailing wording is server- and version-dependent, and the canonical doc example shows just the bare no pg_hba.conf entry for host …, user …, database … line.

Fix. Add ?sslmode=require to the connection string:

# Broken: no TLS, rejected by an SSL-enforcing host
postgresql://user:pw@host:5432/mydb

# Fixed: request TLS
postgresql://user:pw@host:5432/mydb?sslmode=require

Managed hosts enforce SSL by default: Neon rejects all non-TLS connections, DigitalOcean always enforces SSL, and AWS RDS enforces it via the rds.force_ssl parameter, whose default is version-dependent: 1 (on) for RDS for PostgreSQL 15 and later, 0 (off) for 14 and older. So a PostgreSQL 15+ instance on a default parameter group rejects plaintext out of the box, while 14-and-older accepts it until you set rds.force_ssl=1 yourself.

4. server does not support SSL, but SSL was required

error: server does not support SSL, but SSL was required

Cause. The mirror image of #3. Your client demanded SSL (sslmode=require or higher), but the server doesn't have it enabled. This is classic with a default local PostgreSQL build or a bare docker postgres image that ships without ssl=on.

Fix. For a trusted local server, just connect without TLS by dropping sslmode=require or setting sslmode=disable:

# Broken against a local server with no SSL configured
postgresql://postgres:pw@localhost:5432/mydb?sslmode=require

# Fixed: local, trusted network, no TLS
postgresql://postgres:pw@localhost:5432/mydb?sslmode=disable

If it's not a local box, the real fix is to enable SSL on the server (ssl=on plus a cert and key) rather than disabling it. Never use sslmode=disable against a database reachable over the public internet.

5. could not connect to server: Connection refused

could not connect to server: Connection refused
    Is the server running on host "host" and accepting
    TCP/IP connections on port 5432?

Cause. Nothing is listening at that host and port; the TCP connection was actively rejected. The server is down, listening on a different port, not configured for TCP/IP, or a firewall / security group is blocking the port. On managed hosts, the single most common cause is the wrong port. (This is the PostgreSQL 11-and-earlier wording, still emitted by many third-party drivers; modern libpq prints connection to server at "host" (IP), port 5432 failed: Connection refused followed by Is the server running on that host and accepting TCP/IP connections?.)

Fix. Verify the port first, because managed providers don't all use 5432:

# DigitalOcean managed Postgres: port 25060, NOT 5432
postgresql://doadmin:pw@db.ondigitalocean.com:25060/defaultdb?sslmode=require

# Supabase transaction pooler: port 6543
postgresql://postgres.abc:pw@aws-0-eu-west-1.pooler.supabase.com:6543/postgres

Then confirm the server is reachable with pg_isready -h <host> -p <port>, and on AWS RDS or a self-hosted box, check the firewall / security group allows inbound on that port. If you're on a managed host, also check the project isn't paused: Supabase free-tier projects pause after inactivity and refuse connections until you resume them.

6. Tenant or user not found (Supabase pooler)

FATAL: Tenant or user not found

Cause. You're connecting to the Supabase pooler (aws-<region>.pooler.supabase.com) with the plain username postgres. Supabase's pooler (Supavisor) parses your project reference out of the username to route to the right tenant. Without it, there's no tenant to route to.

Fix. Use the tenant-qualified username postgres.<project-ref>:

# Broken: plain "postgres" against the pooler
postgresql://postgres:pw@aws-0-eu-west-1.pooler.supabase.com:6543/postgres

# Fixed: username is postgres.<project-ref>
postgresql://postgres.your-project-ref:pw@aws-0-eu-west-1.pooler.supabase.com:6543/postgres

The Supabase docs put it plainly: "If the username is postgres the username you use for Supavisor is postgres.[PROJECT_REF]." The plain postgres username only works on the direct connection (db.<ref>.supabase.co), never the pooler.

Quick reference: error → cause → fix

Error message	Layer	Root cause	Connection-string fix
`password authentication failed for user`	Auth	Wrong creds, or unencoded special chars in password	Percent-encode the password (`@`→`%40`, `#`→`%23`, `/`→`%2F`)
`could not translate host name`	DNS	Typo, or Supabase IPv6 direct host on IPv4 network	Fix host, or use the IPv4 pooler hostname
`no pg_hba.conf entry ... no encryption`	Auth/TLS	Server requires SSL, client connected plaintext	Add `?sslmode=require`
`server does not support SSL, but SSL required`	TLS	Client demands SSL, server has none (local/Docker)	`?sslmode=disable` for trusted local servers
`Connection refused`	TCP	Wrong port, server down, firewall, or paused project	Fix the port (DO `25060`, Supabase pooler `6543`); check firewall
`Tenant or user not found`	Auth/routing	Plain `postgres` user against Supabase pooler	Use `postgres.<project-ref>` as the username
`self signed certificate in certificate chain`	TLS	node-postgres verifies CA strictly by default	`ssl: { rejectUnauthorized: false }` or supply the CA

Pooler vs direct connection (the Supabase gotcha)

Supabase trips up more developers than any other host here, because it exposes three different endpoints for the same database, and they have different hostnames, ports, IP stacks, and username rules. Choosing the wrong one produces exactly the could not translate host name, Connection refused, and Tenant or user not found errors above.

Endpoint	Hostname & port	IP stack	Username	Best for
Direct	`db.<ref>.supabase.co:5432`	IPv6 (IPv4 add-on)	`postgres`	Persistent VMs/containers, migrations, `pg_dump`
Session pooler	`aws-<region>.pooler.supabase.com:5432`	IPv4 (all tiers)	`postgres.<ref>`	Persistent backends on IPv4-only networks
Transaction pooler	`aws-<region>.pooler.supabase.com:6543`	IPv4 (all tiers)	`postgres.<ref>`	Serverless / edge functions, many short-lived connections

Three rules that save you:

The pooler username is always postgres.<project-ref>, never plain postgres. The direct connection is the opposite.
Ports 5432 (session) and 6543 (transaction) share the same pooler host. Connections are pooled across both. If you're serverless, use 6543.
The transaction pooler does not support prepared statements. Per the Supabase docs, Prisma needs ?pgbouncer=true added to the string (which disables prepared statements), and for serverless you'll usually also set connection_limit=1:

# Prisma against the Supabase transaction pooler
postgresql://postgres.abc:pw@aws-0-eu-west-1.pooler.supabase.com:6543/postgres?pgbouncer=true&connection_limit=1

One more node-specific gotcha worth knowing: with node-postgres, sslmode=require is treated as an alias for verify-full, so it does full certificate verification (unlike libpq, which only encrypts without verifying the CA). That's still the default in the current release line (pg v8 / pg-connection-string v2), not a thing of the past; it only relaxes to libpq's encrypt-only behavior in the unreleased pg v9. Against managed hosts whose chain Node doesn't trust, it throws self signed certificate in certificate chain (hyphenated as self-signed … on Node 17+, which bundles OpenSSL 3). The pragmatic fix is ssl: { rejectUnauthorized: false } when you're not validating the CA, or supply the provider's CA via ssl: { ca }; you can also opt into libpq behavior now with ?sslmode=require&uselibpqcompat=true. Also: if you put sslmode (or sslcert/sslkey/sslrootcert) in the connection string, pg replaces your entire ssl config object, so don't mix the two.

Test your connection string before you deploy

Most of these errors are findable before you ship, by reading the string carefully, which is exactly the kind of tedious parsing a tool should do for you. Our free PostgreSQL Connection String Validator parses your string in the browser, breaks it into its components, and flags the common problems: unencoded special characters in the password, a missing sslmode on a host that needs it, a Supabase pooler host paired with a plain postgres username, a non-standard port, and more.

It runs entirely client-side. Your credentials never leave your machine; nothing is sent to a server, logged, or stored. Validate the string, fix whatever it flags using the sections above, and you'll catch most failures before the first deploy instead of after.

Skip connection strings entirely

Step back for a second. Every error in this post (encoding, host stack, port, SSL mode, pooler username) exists because you're hand-assembling and pasting a connection string in the first place. Remove that step and the whole class of bugs disappears.

That's the case for connecting via OAuth instead. With Supabase OAuth, Codeless Sync authorizes against your Supabase project directly, so there's no string to URL-encode, no pooler-vs-direct decision to get wrong, no sslmode to remember, and no long-lived password living in an env var. CLS validates and tests the connection at the moment you connect a database, so a misconfiguration surfaces immediately instead of three deploys later. Connect once, and your Stripe, QuickBooks, Xero, or Paddle data keeps syncing to Postgres without you ever re-pasting a string.

If you're still choosing where to host your Postgres, Supabase vs Neon vs Railway for SaaS compares the connection-string ergonomics alongside pricing and scaling. The full setup walkthrough lives in the database setup guide.

Frequently Asked Questions

How do I fix "password authentication failed for user postgres"?

First confirm the username and password are correct. If they are, the cause is almost always special characters in the password that aren't percent-encoded inside the connection string. Characters like @, :, /, ?, and # have special meaning in a URI, so the parser misreads or splits the password at them; a # in particular makes a strict parser reject the string and a lax one drop everything after it. Encode them: @→%40, /→%2F, ?→%3F, #→%23. So p@$$w0rd becomes p%40%24%24w0rd (encoding the @ is what matters; encoding the $ too is just harmless). The Codeless Sync connection string validator flags unencoded characters automatically.

Why can't I connect to my Supabase database?

The three usual causes are all in the connection string. Tenant or user not found means you used plain postgres against the pooler, so switch the username to postgres.<project-ref>. could not translate host name on a serverless or IPv4-only network means you're using the IPv6-only direct host (db.<ref>.supabase.co), so switch to the IPv4 pooler (aws-<region>.pooler.supabase.com). And Connection refused often means your free-tier project is paused (resume it in the dashboard) or you used the wrong port.

What does "no pg_hba.conf entry for host" mean and how do I fix it?

It means you reached the Postgres server, but no rule in its host-based authentication config (pg_hba.conf) allows your connection. When the message ends in no encryption or SSL off, that part describes your connection (it was plaintext), and the rule that would have matched is almost always hostssl (TLS-only). On a managed host you can't edit pg_hba.conf, but you don't need to: just add ?sslmode=require to your connection string. Neon, DigitalOcean, and SSL-enforcing AWS RDS instances all require this.

How do I fix "could not connect to server: connection refused" in PostgreSQL?

Connection refused means the TCP connection reached the host but nothing was listening on that port. On managed hosts the most common cause is the wrong port: DigitalOcean uses 25060, the Supabase transaction pooler uses 6543, not the default 5432. Check the exact port in your provider's dashboard. Other causes are a paused project, a firewall or security group blocking the port, or the server being down. Verify reachability with pg_isready -h <host> -p <port>.

What causes the Supabase "Tenant or user not found" error?

It's caused by connecting to the Supabase pooler with the plain username postgres. The pooler (Supavisor) reads your project reference from the username to route to the correct tenant, so it needs the form postgres.<project-ref>, for example postgres.your-project-ref. Plain postgres only works on the direct connection (db.<ref>.supabase.co), not the pooler host (aws-<region>.pooler.supabase.com).

Do I need sslmode=require in my PostgreSQL connection string?

For most managed hosts, yes: Neon, DigitalOcean, and SSL-enforcing AWS RDS reject plaintext connections, so omitting it gives no pg_hba.conf entry ... no encryption. (Supabase is the exception: it accepts non-SSL connections by default for client compatibility and makes SSL enforcement opt-in, though sslmode=require is still good practice there.) For a trusted local server with no SSL configured, do the opposite and use sslmode=disable, or you'll get server does not support SSL, but SSL was required. Be aware that in libpq, require encrypts but does not verify the server's certificate; for genuine protection against man-in-the-middle attacks, use verify-full with the provider's CA certificate. (Some drivers differ: node-postgres treats require as full verification, as noted above.)

How do I put a special-character password in a PostgreSQL connection string?

Percent-encode every reserved character in the password before placing it in the URI. The key ones: @→%40, :→%3A, /→%2F, ?→%3F, #→%23, &→%26, =→%3D, space→%20, $→%24, %→%25, [→%5B, ]→%5D. When unsure, encode the entire password. The alternative is to avoid the URI form entirely and pass host, port, user, and password as separate parameters, which skips URI parsing rules altogether.

Related:

Best Tools to Sync Stripe Data to a Database (2026)

ilshaad — Mon, 22 Jun 2026 11:05:22 +0000

The best tools to sync Stripe data to a database in 2026, compared. Honest pros, cons and pricing for Codeless Sync, Airbyte, Fivetran, Stitch, Hevo and more.

By Ilshaad Kheerdali · 22 Jun 2026

You've already decided you want your Stripe data in a real database, joinable, queryable, and sitting next to your application data. The only question left is which tool to use to get it there.

This is a 2026 roundup of the best tools to sync Stripe data to a database, with honest pros, cons, and pricing for each. It's not a "how to think about it" piece, if you're still weighing the broad approaches (custom scripts, webhooks, ETL, no-code), start with 5 Ways to Get Stripe Data into PostgreSQL first. This post assumes you want a tool you can sign up for today and have syncing by this afternoon.

We'll focus on getting Stripe data into PostgreSQL specifically (Supabase, Neon, AWS RDS, Railway, or any Postgres host), since that's where most SaaS teams keep their source of truth.

What to Look For in a Stripe Sync Tool

Before the list, here's the criteria that actually matters when you're picking a tool to sync Stripe into a database:

Destination support. Does it write to PostgreSQL, and specifically to your Postgres host (Supabase, Neon, RDS, Railway)? Some tools only target warehouses like Snowflake or BigQuery.
No-code vs. configuration. Some tools are genuinely click-and-go; others ("no-code" on the box) still need you to model sources, destinations, streams, and sync schedules.
Incremental sync + historical backfill. You want both: a full backfill of existing Stripe customers, invoices, and subscriptions, plus efficient incremental updates afterwards so you're not re-pulling everything each run.
Pricing model. Flat tiers are predictable. Per-row or "monthly active rows" (MAR) pricing can be cheap at first and expensive once your Stripe volume grows.
Maintenance and schema drift. Stripe changes its API and adds fields. A good tool handles schema changes and broken syncs for you instead of failing silently.
Security. Read-only Stripe keys, encrypted credentials, and ideally no need to paste a full database superuser connection string.

Keep those six in mind as you read, they're what separate "works for a weekend project" from "set it and forget it."

The Best Tools to Sync Stripe Data to a Database

1. Codeless Sync

Codeless Sync is purpose-built for exactly this problem: getting Stripe (and QuickBooks, Xero, or Paddle) data into PostgreSQL with no code. You connect a database, Supabase via one-click OAuth, or any Postgres connection string for Neon, Railway, AWS RDS, and others, add a read-only Stripe key, pick what to sync, and it auto-creates the tables and keeps them up to date.

Best for: Solo developers, startup founders, freelancers, and agencies who want Stripe data in their own Postgres without building or babysitting a pipeline.

Pros:

Built specifically for the Stripe-to-PostgreSQL use case, not a generic warehouse tool
Auto-creates tables with the right schema and handles incremental syncs and schema drift
Works with any PostgreSQL host (Supabase, Neon, Railway, AWS RDS, Heroku Postgres)
One-click Supabase OAuth, no need to paste a full connection string
Free tier, no credit card required; ~5-minute setup

Cons:

Focused on its supported providers (Stripe, QuickBooks, Xero, Paddle), not a fit if you also need GitHub, HubSpot, or Salesforce data
Scheduled batch sync rather than millisecond-level real-time

If you want to see the full flow end to end, the Stripe to PostgreSQL guide walks through setup step by step.

2. Airbyte

Airbyte is an open-source ETL platform with a huge connector library, including a mature Stripe source and a first-class PostgreSQL destination. It's the right call when Stripe is just one of many sources you need to consolidate.

Best for: Teams already running data infrastructure who want dozens of sources in one place.

Pros:

Hundreds of connectors beyond Stripe (GitHub, HubSpot, Salesforce, and more)
PostgreSQL is a first-class destination; incremental sync supported
Open-source and self-hostable, so no per-row pricing if you run it yourself

Cons:

Self-hosting is non-trivial, Airbyte's docs recommend a meaningful VM (4+ CPUs, 8GB RAM) plus monitoring and updates
Airbyte Cloud removes the hosting burden but moves you to usage-based pricing that climbs with volume
The source/destination/connection model is heavier than a single "sync Stripe here" flow, overkill if Stripe is all you need

3. Fivetran

Fivetran is the enterprise-grade, fully managed option. Polished connectors, strong schema-drift handling, and good monitoring, at an enterprise price.

Best for: Larger teams with a data function and a budget, syncing many sources into a warehouse or Postgres.

Pros:

Fully managed, no infrastructure to run
Reliable Stripe connector with automatic schema handling
Direct PostgreSQL destination plus solid alerting out of the box

Cons:

Pricing is based on Monthly Active Rows (MAR) on a sliding scale, the free plan covers up to 500K MAR, then costs scale with volume
Fivetran doesn't publish a flat per-row rate; you'll want their estimator for a real quote
A lot of platform for a single "get Stripe into Postgres" job

4. Stitch Data

Stitch is a simpler, older managed ETL service with a Stripe integration and PostgreSQL destination. It's lighter than Fivetran but now sits inside a much larger enterprise suite.

Best for: Small-to-mid teams who want managed ETL without Fivetran's complexity.

Pros:

Managed, no infrastructure
PostgreSQL supported as a destination
More approachable than Fivetran for straightforward pipelines

Cons:

Standard plan starts around $100/month, so there's no meaningful free path for ongoing use
Now a Qlik product (via Talend), so it evolves inside a big platform rather than as a focused standalone tool
Still warehouse-oriented, more than you need for one or two SaaS sources

5. Hevo Data

Hevo is a no-code data pipeline platform with a Stripe source and PostgreSQL destination. It sits between the simplicity of a focused sync tool and the breadth of Fivetran.

Best for: Teams that want a managed, no-code pipeline across several sources and don't mind a learning curve.

Pros:

No-code pipeline builder with a wide connector range
PostgreSQL destination with incremental loads
Free tier for low volume, with paid plans for scale

Cons:

Paid plans start around $239/month once you outgrow the free tier
Aimed at warehouse-style workloads, heavier than needed for just Stripe
Configuration and monitoring closer to Fivetran than to a click-and-go tool

6. Supabase Stripe Wrapper (Stripe FDW)

If your database is Supabase, the Stripe Foreign Data Wrapper is a genuinely useful, Postgres-native option. Using the wrappers extension, it exposes Stripe objects as foreign tables you can query with plain SQL, no separate pipeline at all.

Best for: Supabase users who want to query live Stripe data from SQL without running any sync job.

Pros:

Native to Postgres/Supabase, query Stripe customers, invoices, and subscriptions as if they were tables
No extra tool or cost beyond your Supabase plan
Always reflects current Stripe data (it reads live on query)

Cons:

Supabase only, not a general solution for Neon, RDS, or Railway
Foreign tables read from the Stripe API on each query, so they're subject to Stripe rate limits and aren't a persisted local copy by default, and Supabase's own docs warn that materialized views over these tables can fail during logical backups, so a real sync is the more dependable way to keep a cached copy
Mostly read-only, a few objects (customers, products, subscriptions) support writes, and limited to the Stripe objects the wrapper exposes

7. Stripe Sigma

Stripe Sigma is Stripe's own SQL analytics product. It's worth addressing because it's the first thing many people find, but it's important to be clear: Sigma does not sync data into your database. It lets you run SQL against your Stripe data inside the Stripe Dashboard, and the data never leaves Stripe.

Best for: Occasional SQL queries and scheduled reports on Stripe data, when you don't need to join it with your own tables.

Pros:

Zero setup, built into Stripe, always current
Familiar SQL interface with scheduled reports

Cons:

Data stays in Stripe, you can't join it with your users table or use it in your own app/dashboards
Paid add-on with tiered pricing (a monthly fee plus a per-charge fee that grows with volume)
Not standard PostgreSQL, and export is manual (CSV)

Stripe also offers Data Pipeline, a separate paid product that syncs Stripe data to data warehouses (Snowflake, Amazon Redshift, Databricks) and cloud storage (S3, Google Cloud Storage, Azure), but not to PostgreSQL or other transactional databases. So if Postgres is your destination, neither Sigma nor Data Pipeline gets you there.

Best Tools to Sync Stripe Data Compared (2026)

Tool	Type	PostgreSQL destination	Code required	Pricing (2026)	Best for
Codeless Sync	No-code sync	Yes (any host)	None	Free tier, scales by syncs	Stripe → Postgres, fast
Airbyte	Open-source ETL	Yes	None (config-heavy)	Free self-host / usage cloud	Many sources, self-host
Fivetran	Managed ELT	Yes	None	Free ≤500K MAR / tiered MAR	Enterprise pipelines
Stitch Data	Managed ETL	Yes	None	From ~$100/month	Mid-market managed ETL
Hevo Data	No-code ETL	Yes	None	Free tier / from ~$239/month	Multi-source pipelines
Supabase Stripe Wrapper	Postgres FDW	Supabase only (live)	SQL setup	Free (Postgres extension)	Live Stripe queries on Supabase
Stripe Sigma	In-Stripe analytics	No (stays in Stripe)	SQL only	Paid add-on (tiered)	Quick queries inside Stripe

How to Choose the Right Stripe Sync Tool

The honest answer is that most people reading this don't need a warehouse-grade ETL platform, they need Stripe data in Postgres without a maintenance burden.

Just want Stripe in your own Postgres, fast? A purpose-built no-code sync like Codeless Sync is the shortest path — connect, sync, done, with a free tier to start.
Consolidating ten-plus sources and already run infrastructure? Airbyte (self-hosted) earns its keep.
Enterprise team with a data function and budget? Fivetran is the polished managed option.
Want managed ETL but Fivetran feels heavy? Stitch or Hevo sit in the middle.
On Supabase and just want to query Stripe from SQL? The Stripe FDW is the lightest possible option, though for a persisted, queryable copy at scale you'll still want a real sync.
Only need occasional reports and don't care about owning the data? Stripe Sigma will do, but it isn't a database sync.

What You Can Do Once Stripe Data Is in Your Database

The reason to sync at all is what becomes possible afterwards: real SQL across your billing data joined with everything else you store. For example, monthly revenue straight from a synced stripe_invoices table:

SELECT
  DATE_TRUNC('month', created) AS month,
  COUNT(DISTINCT customer)     AS paying_customers,
  SUM(amount_paid) / 100.0     AS revenue
FROM stripe_invoices
WHERE status = 'paid'
GROUP BY month
ORDER BY month DESC;

From here you can build revenue dashboards, join Stripe customers to your own users table, or calculate SaaS metrics, see How to Calculate MRR, Churn, and LTV in PostgreSQL for ready-made queries. None of that is possible while your data is locked inside Stripe.

Frequently Asked Questions

What is the best tool to sync Stripe data to a database in 2026?

For most developers and small teams, a purpose-built no-code tool like Codeless Sync is the best fit, it syncs Stripe straight into your own PostgreSQL (Supabase, Neon, Railway, AWS RDS) in about five minutes with a free tier. If you're consolidating many sources and already run data infrastructure, Airbyte or Fivetran make more sense, but they're more tool than a single Stripe sync needs.

What's the best free tool to sync Stripe to PostgreSQL?

Codeless Sync has a free tier with no credit card required, which is the simplest free path for ongoing Stripe-to-Postgres sync. Self-hosted Airbyte is also free in licensing terms, but you pay in the VM and maintenance it requires. On Supabase specifically, the Stripe Foreign Data Wrapper is free as part of your existing plan, though it queries Stripe live rather than keeping a local copy.

Do I need an ETL tool like Fivetran or Airbyte just for Stripe?

Usually not. Fivetran and Airbyte are excellent when you're loading many sources into a warehouse, but for a single "get Stripe into PostgreSQL" job they add cost, configuration, and overhead you don't need. A focused sync tool is faster to set up and cheaper to run for one or two providers.

Is Stripe Sigma a Stripe sync tool?

No. Stripe Sigma runs SQL against your Stripe data inside the Stripe Dashboard, but the data never leaves Stripe, you can't join it with your own tables or use it in your app. If the goal is getting Stripe data into your own database, you need a sync tool, not Sigma. See Best Stripe Sigma Alternative for PostgreSQL Users.

Which tool is best for syncing Stripe to Supabase or Neon specifically?

For Supabase, Codeless Sync supports one-click OAuth so you don't paste a connection string, and the native Stripe FDW is an option for live queries. For Neon and other hosts, any tool with a PostgreSQL destination works, Codeless Sync, Airbyte, Fivetran, Stitch, or Hevo, but a no-code sync is the quickest to get running.

Want the fastest path? Codeless Sync syncs Stripe to your PostgreSQL database with no code and a free tier, no credit card required. For the full walkthrough, see How to Sync Stripe Data to PostgreSQL in 5 Minutes.

Related:

QuickBooks API Integration Guide for Developers

ilshaad — Mon, 15 Jun 2026 12:14:12 +0000

A developer guide to the QuickBooks Online API: OAuth 2.0 setup, querying data, pagination, rate limits, and syncing to PostgreSQL without the plumbing.

By Ilshaad Kheerdali · 15 Jun 2026

If you're integrating with QuickBooks Online, the Intuit API is powerful but it has a learning curve: OAuth 2.0 with rotating refresh tokens, a SQL-like query language, per-company rate limits, and sandbox/production environments that behave differently. This guide walks through the whole flow end to end, so you can go from zero to reading live company data, then shows the shortcut if you'd rather skip the plumbing entirely.

Everything below targets the QuickBooks Online Accounting API (the cloud product), not the older QuickBooks Desktop SDK.

What the QuickBooks API Is

The QuickBooks Online API is a REST API hosted by Intuit. You authenticate against a specific company (Intuit calls it a realm, identified by a realmId) and read or write accounting entities: Customer, Invoice, Payment, Bill, Vendor, Item, Account, and around 30 others.

Two things make it different from a typical REST API:

Every request is scoped to a realm. The company ID is part of the URL, so a single access token can only touch the company that authorised it.
Reads use a query language, not REST filters. Instead of GET /customers?active=true, you send a SQL-like string to a single /query endpoint.

Step 1: Create an Intuit Developer App

Before any code, you need credentials:

Sign up at the Intuit Developer portal and create an app under the QuickBooks Online and Payments platform.
Grab your Client ID and Client Secret from the app's Keys & credentials section. There's a separate pair for Development (sandbox) and Production.
Add a Redirect URI (e.g. https://yourapp.com/callback). It must match exactly what you send during OAuth.
Note the scope you need: com.intuit.quickbooks.accounting for accounting data.

Intuit also provisions a free sandbox company so you can develop against realistic data without touching a real business.

Step 2: Authenticate with OAuth 2.0

QuickBooks uses the OAuth 2.0 authorization code flow. The user is redirected to Intuit, approves access, and you exchange the returned code for tokens.

import OAuthClient from 'intuit-oauth';

const oauthClient = new OAuthClient({
  clientId: process.env.QB_CLIENT_ID,
  clientSecret: process.env.QB_CLIENT_SECRET,
  environment: 'sandbox', // or 'production'
  redirectUri: process.env.QB_REDIRECT_URI,
});

// 1. Send the user here to authorise
const authUri = oauthClient.authorizeUri({
  scope: [OAuthClient.scopes.Accounting],
  state: 'a-random-csrf-token',
});

// 2. In your redirect handler, exchange the code for tokens
app.get('/callback', async (req, res) => {
  const authResponse = await oauthClient.createToken(req.url);
  const token = authResponse.getJson();

  // token.access_token   -> use for API calls (valid ~1 hour)
  // token.refresh_token  -> use to get new access tokens (valid ~100 days)
  // realmId comes in as a query param on the redirect
  const realmId = req.query.realmId;

  // Persist refresh_token + realmId securely (you'll need both later)
});

Three things trip people up here:

The realmId is not in the token. It arrives as a separate query parameter on the redirect. Store it alongside the tokens — you need it in every API URL.
Access tokens expire in ~1 hour. Short-lived by design. You refresh them with the refresh token.
Refresh tokens rotate. Each refresh can return a new refresh token and the old one eventually stops working. Always persist the latest one you receive, or you'll get locked out after ~100 days of inactivity.

// Refresh an expired access token
const refreshResponse = await oauthClient.refreshUsingToken(
  storedRefreshToken,
);
const newToken = refreshResponse.getJson();
// Save newToken.refresh_token — it may have changed

Step 3: Make Your First API Call

API URLs follow the pattern /v3/company/{realmId}/{resource}, against different base hosts for sandbox and production:

Sandbox: https://sandbox-quickbooks.api.intuit.com
Production: https://quickbooks.api.intuit.com

Here's reading a single customer by ID:

const baseUrl = 'https://sandbox-quickbooks.api.intuit.com';

const res = await fetch(
  `${baseUrl}/v3/company/${realmId}/customer/1?minorversion=75`,
  {
    headers: {
      Authorization: `Bearer ${accessToken}`,
      Accept: 'application/json',
    },
  },
);

const data = await res.json();
console.log(data.Customer.DisplayName);

Always pin a minorversion — Intuit uses it to version response payloads. As of 2026 the minimum supported (and default) is 75; Intuit deprecated versions 1–74 in 2025, so pin an explicit current version rather than relying on "latest".

Step 4: Query Data with the QuickBooks Query Language

For anything beyond fetching by ID, you use the /query endpoint with a SQL-like statement. This is how you list, filter, and page through records.

const query = "SELECT * FROM Invoice WHERE TxnDate > '2026-01-01'";

const res = await fetch(
  `${baseUrl}/v3/company/${realmId}/query?query=${encodeURIComponent(query)}&minorversion=75`,
  {
    headers: {
      Authorization: `Bearer ${accessToken}`,
      Accept: 'application/json',
    },
  },
);

const data = await res.json();
const invoices = data.QueryResponse.Invoice ?? [];

Pagination is manual. The API returns up to 1,000 rows per call, and you walk the result set with STARTPOSITION and MAXRESULTS:

let startPosition = 1;
const pageSize = 1000;
const allInvoices = [];

while (true) {
  const q = `SELECT * FROM Invoice STARTPOSITION ${startPosition} MAXRESULTS ${pageSize}`;
  const res = await fetch(
    `${baseUrl}/v3/company/${realmId}/query?query=${encodeURIComponent(q)}&minorversion=75`,
    { headers: { Authorization: `Bearer ${accessToken}`, Accept: 'application/json' } },
  );
  const page = (await res.json()).QueryResponse.Invoice ?? [];
  allInvoices.push(...page);
  if (page.length < pageSize) break; // last page
  startPosition += pageSize;
}

Note the query language is a subset of SQL — no JOINs, limited functions, and each entity is queried separately.

Step 5: Handle Rate Limits and Errors

QuickBooks throttles per company (realm). As of 2026, Intuit documents 500 requests per minute per realm and a maximum of 10 concurrent requests in production, with the batch endpoint throttled separately at 40 requests per minute per realm. These limits change over time, so check the current limits in Intuit's docs. Exceed them and you get HTTP 429.

Errors come back in a Fault object, not as plain HTTP status text:

if (!res.ok) {
  const body = await res.json();
  const fault = body.Fault?.Error?.[0];
  // e.g. { Message: 'message', Detail: '...', code: '3200' }
  throw new Error(`QuickBooks error ${fault?.code}: ${fault?.Message}`);
}

Build in exponential backoff on 429 and 5xx, and use the batch endpoint (/batch, up to 30 operations per call) to cut request volume when you're reading or writing many records — though note the batch endpoint has its own, tighter per-minute throttle, so it reduces total calls rather than letting you burst past the realm limit.

Step 6: Keep Data in Sync with Change Data Capture

Polling everything on a schedule wastes calls. For incremental updates, QuickBooks offers a Change Data Capture (CDC) endpoint that returns only entities changed since a timestamp:

const since = '2026-06-01T00:00:00-00:00';
const entities = 'Customer,Invoice,Payment';

const res = await fetch(
  `${baseUrl}/v3/company/${realmId}/cdc?entities=${entities}&changedSince=${since}&minorversion=75`,
  { headers: { Authorization: `Bearer ${accessToken}`, Accept: 'application/json' } },
);

CDC is the backbone of any efficient sync: pull a full snapshot once, then poll CDC for deltas.

The Hard Parts (and Why Many Teams Don't Build This Themselves)

A working integration is achievable, but keeping it running is the real cost:

Token lifecycle. Refreshing every hour, persisting rotating refresh tokens, and recovering when a refresh fails.
Sandbox vs production. Separate credentials, separate base URLs, separate company data — easy to misconfigure on go-live.
Pagination and rate limits. Every entity paginated separately, backoff on throttling, batching to stay under caps.
Schema mapping. QuickBooks objects are deeply nested; flattening Invoice.Line[], LinkedTxn, and custom fields into clean relational tables is real work.
Ongoing maintenance. Minor-version bumps, new fields, and deprecations mean the integration is never truly "done."

If your end goal is simply getting QuickBooks data into your own database to query and report on, all of the above is plumbing that doesn't differentiate your product.

A Simpler Path: Sync QuickBooks to PostgreSQL with No Code

If you'd rather skip the OAuth dance, pagination, and schema mapping, Codeless Sync connects QuickBooks to your PostgreSQL database (Supabase, Neon, Railway, AWS RDS, or any Postgres host) in about 5 minutes. You authorise QuickBooks via OAuth once, and CLS handles token refresh, CDC-based incremental sync, pagination, rate limits, and table creation for you. Your data lands as clean relational tables, ready for SQL:

-- Top 10 customers by invoiced revenue this year
SELECT c.display_name, SUM(i.total_amt) AS invoiced
FROM quickbooks_invoices i
JOIN quickbooks_customers c ON c.id = i.customer_id
WHERE i.txn_date >= '2026-01-01'
GROUP BY c.display_name
ORDER BY invoiced DESC
LIMIT 10;

The same model works for Stripe, Xero, and Paddle too, so multi-provider billing all lands in one database. There's a free tier, no credit card required.

For a step-by-step walkthrough, see How to Sync QuickBooks Data to PostgreSQL Automatically. If you're weighing export options more broadly, How to Export QuickBooks Data to a Database compares five methods side by side.

Frequently Asked Questions

Is the QuickBooks API free to use?

Yes. Access to the QuickBooks Online Accounting API is free for developers — there's no per-call charge from Intuit. You do need a QuickBooks Online subscription (or the free sandbox company) for the data, and your own infrastructure to run the integration.

How long do QuickBooks access tokens last?

Access tokens are valid for about one hour. Refresh tokens last around 100 days but rotate — each refresh can return a new refresh token, and you must persist the latest one or you'll lose access after the window expires.

Does QuickBooks have webhooks?

Yes. QuickBooks Online offers Event Notifications (webhooks) covering most major entities — including Customer, Invoice, Payment, Bill, Item, Account, and around two dozen others — for create, update, delete, void, and merge events. They're genuinely useful for reacting to changes in real time. What they don't give you is historical backfill or a guaranteed gap-free feed, so for keeping a database fully in sync, teams typically combine a one-time snapshot with the Change Data Capture (CDC) endpoint and scheduled polling. Note Intuit is migrating webhook payloads to the CloudEvents format, with all apps required to move by July 31, 2026; both the old and new formats are supported during the transition, so check your payload parsing against the current spec.

What's the QuickBooks API rate limit?

As of 2026, Intuit throttles per company (realm) at 500 requests per minute, with a maximum of 10 concurrent requests in production. The batch endpoint is throttled separately at 40 requests per minute per realm. These limits change over time, so check Intuit's official limits documentation and implement exponential backoff on HTTP 429 responses.

Can I sync QuickBooks data to PostgreSQL without writing code?

Yes. Tools like Codeless Sync handle the OAuth flow, token refresh, pagination, and schema mapping for you, writing QuickBooks data straight into PostgreSQL tables. You authorise once and the data stays in sync on a schedule.

Related: