DEV Community: ilshaad

Stripe Data Pipeline Doesn't Sync to PostgreSQL: What to Use Instead

ilshaad — Wed, 02 Sep 2026 10:55:53 +0000

You went looking for the official answer. Stripe sells a product called Data Pipeline whose entire job is getting your Stripe data out of Stripe and into somewhere you can query it, which sounds exactly like what you need. Then you open the destination list.

Snowflake. Amazon Redshift. Databricks. BigQuery. Google Cloud Storage, Azure Blob Storage, Amazon S3.

No PostgreSQL. No Supabase, no Neon, no RDS. If your stack is a Postgres database, the Data Pipeline you can actually buy today does not meet you there. The two Stripe products that will are both locked behind a preview.

This post covers what Data Pipeline actually is, what Stripe is quietly building for Postgres users and why you probably cannot have it yet, what the cloud storage route really costs if you decide to bridge the gap yourself, and the options that exist today.

What Stripe Data Pipeline Actually Is

Data Pipeline is a paid Stripe product that pushes a copy of your Stripe data to a destination you own, with no code on your side. Per Stripe's documentation, it supports two shapes of destination:

Data warehouses (Snowflake, Amazon Redshift, Databricks, BigQuery). Stripe sends a data share to your warehouse, with one instructive exception covered in the next section. Either way, your core Stripe data is available within 12 hours of setup.

Cloud storage (Google Cloud Storage, Azure Blob Storage, Amazon S3). Stripe drops Parquet files into a bucket you own.

After the initial load, both routes refresh on the same cadence: a new full load of your data every 3 hours, with Stripe noting that some tables update on their own schedules as new data becomes available. Your data is split into a STRIPE schema for live mode and a STRIPE_TESTMODE schema for sandboxes, every table carries a merchant_id column so multiple Stripe accounts can share one destination, and a data_load_times table tells you when each table was last refreshed.

Two things worth knowing that rarely make it into comparison posts. A Data Pipeline subscription includes Stripe Sigma, so you are buying the in-dashboard SQL tool along with the export. And Stripe does not offer Data Pipeline to customers in India, because of data localization requirements.

Why PostgreSQL Is Not on the List

It is tempting to read the destination list as an oversight. The setup mechanics are more interesting than that, and they explain the shape of what Stripe is building next.

Three of the four warehouse destinations arrive as a native data share, a feature of the warehouse rather than anything Stripe operates. In Snowflake you accept a share from a Stripe account and mount it as a database. In Redshift you associate an incoming datashare, which only works on an RA3 cluster with encryption enabled. In Databricks it comes through Delta Sharing and you mount the Stripe share to a catalog. No bytes move through infrastructure Stripe runs. It publishes, you accept, and your warehouse does the rest.

BigQuery is the exception. There is no share: you create a service account, grant it BigQuery Job User and BigQuery Data Editor, stand up an intermediate Cloud Storage bucket for Stripe to write to, and Stripe runs load jobs into your datasets, deleting the intermediate files afterwards.

That exception is the tell. Stripe will operate a real write pipeline into a destination you own when it decides the destination is worth the engineering. Postgres was not on the list because it is the harder version of that problem: not one managed vendor with one stable way in, but millions of instances across every host and self-run box on the internet, each with its own version, extensions, connection limits, pooler, IP allow-list and maintenance window, and no share mechanism to fall back on.

Hard is not the same as never, though, which brings us to the part most comparison posts have not caught up with.

Stripe Is Building Postgres Support, in Two Places

Neither is generally available. Neither is something you can go and buy this afternoon. Both are documented, and both are aimed squarely at the reader of this post.

Real-time sync to Postgres. Stripe's documentation for it is unambiguous: it continuously streams "your Stripe data to a Postgres database", in real time rather than in a 3-hour batch, with a schema that is "a direct mapping to the public Stripe API". Coverage is everything in the public API that has a list endpoint, so charges, customers, subscriptions, payment intents and invoices are in, while single-object resources like Balance are not, and you choose which tables to sync from the Dashboard. The setup guide is public and specific: PostgreSQL 13 or later from any provider, with Stripe naming Amazon RDS and Aurora, Supabase, Neon and self-hosted deployments; a stripe schema reserved for the pipeline; a dedicated database user that owns that schema and holds SELECT, INSERT, UPDATE and DELETE on it; an SSL connection; and sixteen Stripe IP addresses added to your firewall allowlist. Stripe then backfills the tables you picked and keeps them current. The catch is the status: it is still a preview you request access to by email, with no published general availability date, and the commitment is to "a near-real-time basis" with no end-to-end latency SLA.

Stripe Database. Announced at Sessions 2026 as a managed, hosted, read-only Postgres database holding your Stripe data, created in one click from the Dashboard or a single CLI command. Its documentation page returns a 404 unless your account is enrolled in the early access programme, so the substantive public document is the preview terms, which are worth reading before you build anything on it. It is a Preview Service "not subject to Stripe's standard compliance certifications, access controls requirements, or related auditing commitments". Stripe "may, at its discretion, temporarily throttle, rate-limit, or suspend" your access based on query volume. It "reserves the right to apply or change Fees". And on termination, "Stripe will delete all associated data".

So the honest status is that Stripe will probably solve part of this, on its own timeline. Two things stay true when it lands.

It is Stripe only. The real-time Postgres sync syncs Stripe data and Stripe Database holds Stripe data. If your reporting also needs QuickBooks, Xero or Paddle in the same database, that is a second tool or a warehouse regardless.

With Stripe Database, the data lives in Stripe's infrastructure, not yours. That is genuinely convenient, and it is also the opposite of why most people want the copy in Postgres in the first place: to join billing data against their own application tables, own the schema, and keep it somewhere they control.

Until either ships, the practical answer is unchanged. If you want Stripe data in your Postgres today, something other than Data Pipeline has to put it there.

What Data Pipeline Costs, and What the Meter Is

The pricing matters here because it shapes the decision even for people who could adopt a warehouse.

Data Pipeline is priced in tiers based on your monthly charge count, with a per-charge rate on anything above your tier. Read off Stripe's pricing page in September 2026, the entry tier covers up to 1,000 charges a month at $65 a month, or $50 a month on an annual plan, with 7 cents and 6 cents respectively per additional charge. Above that it is annual only: $75 a month up to 2,500 charges, $280 up to 10,000, $550 up to 25,000, and custom pricing beyond. The UK page shows the same ladder at £48 or £37, then £56, £209 and £410. New accounts get a 30-day free trial.

Two things sit in the small print. Monthly billing exists only on the entry tier, so passing 1,000 charges a month means taking on an annual commitment. And the meter counts "successful charges both on Stripe and through third-party payments processors in connection with any Stripe service", so payments you did not run through Stripe at all can still push you up a tier.

Note what the meter is. You are not billed for tables, rows, syncs or queries. You are billed for payment volume, which is to say your business getting busier, for a job whose difficulty does not change when it does. That is the same structural complaint as usage-priced ETL, arrived at from a different direction: Fivetran's monthly active rows model meters row churn instead, with the same result that the bill moves for reasons unrelated to what you asked for.

And this is before the destination. Snowflake, Redshift, Databricks and BigQuery all bill you separately for storage and compute. Adopting a warehouse to receive this data is not a one-line decision.

The Cloud Storage Escape Hatch, and What It Really Costs

Here is the part that a flat "Data Pipeline can't do Postgres" leaves out, and it is the honest centre of this post.

There is a technical path. Point Data Pipeline at S3, GCS or Azure Blob, and Stripe delivers Parquet files into a bucket you control. Nothing stops you writing a job that reads those files and loads them into Postgres. Stripe even helps a little: it writes a SUCCESS file per run date and mode once all files for a set of tables are transferred and validated, and it updates a /data_load_times.json file with the time and location of the most recent successful load for each table.

Now count what you own the moment you take that path.

A loader that reads Parquet. Postgres does not ingest Parquet natively. You are writing a job, in a language of your choice, on a runtime you have to host, or standing up something like DuckDB or a Spark step in between.
Type mapping. Parquet's types are not Postgres types. Timestamps, nested structures, numeric precision and the JSON-shaped fields in Stripe's schema each need a decision, and the wrong one is usually discovered months later inside a report. Our post on designing a Postgres schema for Stripe data covers the choices that bite.
Deduplication, every three hours, forever. This is the big one. Each run is a full load, not a changelog. You are handed the whole dataset again eight times a day, and it is entirely on you to turn that into idempotent upserts against your existing rows rather than duplicates. Get the conflict target wrong once and you are cleaning up a table by hand.
Orchestration and failure handling. Polling for the SUCCESS file, not starting on a half-written batch, retrying, and alerting when a load silently stops.
Schema drift. Stripe adds fields. Your loader has to notice, and your table has to change.
Two bills. You are paying Stripe for Data Pipeline, and paying for the bucket, the egress and whatever compute runs the loader.

None of this is hard in the sense of being intellectually difficult. It is hard in the sense of never being finished. You wanted Stripe data in a table; you have acquired a data pipeline, and pipelines are things people end up on call for. If the appeal of Data Pipeline was "without writing code", the cloud storage route quietly hands the code back.

Today's Version Is Not Real Time

One more expectation worth resetting before you build anything around this.

A 3-hour cadence is not a 3-hour freshness guarantee. Stripe's data freshness documentation works the example: with a 3-hour cadence, a record created at 00:01 UTC is picked up in the 03:00 UTC batch, and with a typical delivery time of about three hours after the batch closes, it lands by 06:00 UTC. That is a P50 freshness of six hours from event creation. Half the time it is slower than that, delivery varies with your data volume, and derived datasets run on their own schedules.

That is completely fine for finance, reporting and analytics, which is what the product is for. It is not fine if you were planning to drive product behaviour off the copy. Real-time streaming is exactly what the Postgres sync in preview promises, which is Stripe's own way of conceding that the shipping product does not do it. Until that is generally available, if you need a customer's subscription state correct within seconds, that is a webhook job rather than a sync job. We covered where the line sits in how often you should sync billing data to Postgres.

Your Real Options for Getting Stripe Data into Postgres Today

Assuming Postgres is not negotiable, these are the paths, with the trade-off named honestly.

Option	What it costs you	Best when
Wait for Stripe's real-time Postgres sync	No money, but no date either, and access is Stripe's decision	Stripe is your only source and the timing genuinely does not matter
Adopt a warehouse	Warehouse bill plus a Data Pipeline tier, and a second system to run	You already run Snowflake, BigQuery, Redshift or Databricks
Cloud storage plus your own loader	Data Pipeline tier, storage, compute, and permanent ownership of the job	You have a data engineer and Parquet is already in your stack
Stripe API plus your own script	Build time, pagination, rate limits, retries, schema upkeep	Your needs are narrow enough that nothing off the shelf fits
Managed sync straight into Postgres	A flat subscription, and a narrower feature set than a warehouse platform	Postgres is the destination and you want the job to be finished

If you want the full field rather than these four, 5 ways to get Stripe data into PostgreSQL walks the methods, and the best tools to sync Stripe data to a database reviews seven products with their weak points included.

When Data Pipeline Is Still the Right Call

It would be a bad post that only argued one way, so here is the case for buying it.

If a warehouse is already in your stack, Data Pipeline is close to unbeatable for Stripe specifically. It is first-party, and Stripe's pitch is that you get "structured and ready-to-use datasets not available through third-party APIs or ETLs", which nothing built on the public API can match. On the three share-based destinations there is no pipeline to run at all. Multi-account businesses can fold every Stripe account into one destination and filter on merchant_id, and Organizations gives administrators a single view of every pipeline. Sigma is bundled rather than sold separately, so the reporting layer arrives with it, which we compared in the best Stripe Sigma alternative for Postgres users.

Two limits to know before committing: you can connect only one warehouse account to a Stripe account at a time, and the pre-built financial report templates are Snowflake only, not available on Redshift, Databricks or BigQuery.

The question is not whether it is a good product. It is whether you are willing to run a warehouse in order to use it.

The Postgres-Native Version

If your destination is Postgres and you want the job to stay finished, that is the narrow problem Codeless Sync exists for.

You connect Stripe with a restricted, read-only API key, choose a data type, and the destination table is created for you in your own database. It works with Supabase, Neon, AWS RDS, Railway, DigitalOcean and any other Postgres you can reach, and the same setup covers QuickBooks, Xero and Paddle if your billing data is not all in one place. The first sync takes about five minutes.

The difference from the cloud storage route is not the setup, it is the ownership. There is no Parquet reader, no type mapping, no dedupe logic, no SUCCESS file to poll, no schema drift to chase. Syncs upsert on the record's Stripe ID, so a re-run corrects rather than duplicates, and rate limits, retries and pagination are ours to worry about.

The pricing is a flat monthly fee rather than a meter on your payment volume. Free is $0 for 2 manual syncs a day, 2 configurations and one database project. Starter is $19 a month, Pro is $29 and Business is $99, each adding configurations and a larger monthly row allowance, with more database projects from Pro upwards. Scheduled syncs are the paid feature: Starter and Pro run daily, weekly or monthly, and Business adds a 12-hourly option.

Put the two next to each other on the axes that actually differ:

	Stripe Data Pipeline	Codeless Sync
PostgreSQL	Not a destination	The only destination
Priced on	Your monthly charge count	Flat monthly fee
Second system needed	A warehouse, or a bucket plus a loader you build	No
Cadence	Full load every 3 hours, P50 about 6 hours old	Manual, or scheduled daily to monthly, 12-hourly on Business
Sources	Stripe	Stripe, QuickBooks, Xero, Paddle
Dataset coverage	First-party, includes data the API does not expose	Common billing tables
Bundled reporting	Includes Sigma	No, you query your own Postgres

Data Pipeline wins coverage and completeness. It cannot win a single row of that table that involves the word Postgres, and that is the row you came here for.

Frequently Asked Questions

Does Stripe Data Pipeline support PostgreSQL?

No. Data Pipeline delivers to Snowflake, Amazon Redshift, Databricks and BigQuery, or to Google Cloud Storage, Azure Blob Storage and Amazon S3. PostgreSQL, including Supabase, Neon and AWS RDS, is not a supported destination.

Is Stripe adding PostgreSQL support to Data Pipeline?

Yes, through a real-time sync to Postgres that is still in preview. Stripe's documentation describes it as continuously streaming Stripe data to a Postgres database, with a schema mapped directly to the public API, and the setup guide is already public: PostgreSQL 13 or later from any provider including RDS, Supabase and Neon, a reserved stripe schema, a dedicated database user, SSL, and sixteen Stripe IP addresses allowlisted on your firewall. You request access by email, and there is no published general availability date.

What is Stripe Database?

A managed, hosted, read-only Postgres database of your Stripe data, announced at Sessions 2026 and still in preview, with documentation gated behind early access enrolment. Note that the data sits in Stripe's infrastructure rather than your own, so you cannot join it against your application tables, and the public preview terms let Stripe throttle access, change fees, and delete all associated data on termination.

Can I get Stripe Data Pipeline into Supabase or Neon?

Not directly. The nearest path is exporting Parquet files to cloud storage and writing your own job to load them into your database, which means owning a loader, type mapping, three-hourly deduplication and schema drift. A managed Stripe to Postgres sync does the same job without the pipeline.

How much does Stripe Data Pipeline cost?

It is tiered on your monthly charge count, with a per-charge rate above each tier. Checked in September 2026, the entry tier covers up to 1,000 charges a month at $65 a month, or $50 a month on an annual plan, and the tiers above it are annual only at $75, $280 and $550 a month for 2,500, 10,000 and 25,000 charges. Warehouse storage and compute are billed separately by your warehouse provider.

How fresh is Stripe Data Pipeline data?

Stripe delivers a full load every 3 hours. Because a record waits for the next batch and then for delivery, Stripe's own worked example comes to a P50 freshness of about six hours from event creation, with delivery time varying by data volume.

Is Stripe Data Pipeline the same as Stripe Sigma?

No, though a Data Pipeline subscription includes Sigma. Sigma runs SQL inside the Stripe Dashboard and returns results there. Data Pipeline copies your data to an external destination you own.

What is the fastest way to get Stripe data into a Postgres database?

A managed sync. Connecting a read-only Stripe key and letting the tool create the destination table takes about five minutes, against the days it takes to build a Parquet loader or a paginated API script, and it keeps working afterwards.

Wrapping Up

Stripe Data Pipeline is a good product aimed at a reader who is not you. It assumes a warehouse, it prices on payment volume, and its Postgres story today is a bucket of Parquet files plus a job you write yourself. The version with a real Postgres destination exists, and it is in preview with no date, which is not something you can plan a quarter around.

If Postgres is where your data lives, skip the detour. Either adopt a warehouse deliberately, for reasons that go beyond this one export, or use something built to land Stripe data in Postgres and be done with it.

Start a free sync and see your Stripe tables in your own database in about five minutes.

How Often Should You Sync Billing Data to Postgres?

ilshaad — Mon, 24 Aug 2026 07:28:28 +0000

Daily, twice daily, weekly or monthly. How to pick a sync schedule for Stripe, QuickBooks, Xero or Paddle data, and why real-time is usually the wrong goal.

By Ilshaad Kheerdali · 24 August 2026

You have your billing data landing in Postgres. The connection works, the table is there, the first sync completed. Now there is a dropdown asking how often you want to repeat it, and no obvious way to answer.

The instinct is to pick the fastest option available, on the reasoning that fresher is better and the sync is automated anyway. That instinct is worth resisting. For billing data specifically, the right cadence is usually slower than people expect, and picking a faster one costs you in places that are not obvious on day one.

This post covers how to choose, how to match the sync mode to the schedule you picked, and the one case where scheduling is genuinely the wrong tool.

Start With the Decision, Not the Data

The useful question is not "how fresh could this be". It is "what decision am I making with it, and how often do I make that decision".

Billing data is consumed in a small number of recognisable ways, and almost all of them are periodic:

Board reporting and investor updates. Monthly, occasionally quarterly. The numbers are cut at a month boundary and nothing that happens on a Tuesday afternoon changes them.
MRR, churn and cohort dashboards. Reviewed weekly by most teams, daily by some. The underlying figures move slowly enough that hourly refreshes show noise, not signal.
Month-end reconciliation. Intense for a few days, irrelevant for the rest of the month. Daily is comfortably enough, because the work happens against a closed period.
Customer support lookups. Feels like it needs to be live, usually is not. Support agents overwhelmingly need "what plan is this person on and did their last invoice clear", which a daily copy answers correctly for the vast majority of tickets.
Dunning and failed payment follow-up. The most time-sensitive of the common cases, and the one worth syncing twice a day for if you act on it manually.

If your use case is on that list, daily is the default answer and you should need a reason to move away from it. Weekly is right more often than people admit, particularly for accounting data from QuickBooks or Xero where the source system is itself updated in batches by a human.

What Actually Changes, and How Fast

The other half of the answer is how much your data really moves, which is usually less than it feels.

Customer records are close to static. A row changes when someone updates a card, an email or a billing address. For most SaaS businesses that is a low single-digit percentage of the table per month.

Subscriptions move more than they look like they do, because every status transition is a write: trialing to active, active to past_due, past_due to canceled, plus plan changes, quantity changes and renewals. Still, an individual subscription generates a handful of changes a year, not a day.

Invoices and charges are the genuinely busy tables, and they are lifecycle-heavy. An invoice moves through draft, open and paid, with a possible detour through a failed payment and a retry. But they are also the most predictable: they are created on billing anniversaries and settle within days.

Accounting data behaves differently again. QuickBooks and Xero records change when a person enters them, which means they arrive in bursts around invoicing runs and month end, and are quiet in between. Syncing a Xero ledger every hour mostly re-reads a ledger nobody has touched since yesterday.

The pattern across all four providers is the same: billing data changes in bursts tied to billing cycles and human work rhythms, not continuously. A schedule that matches those rhythms captures nearly everything a faster schedule would, at a fraction of the cost.

The Costs of Syncing Too Often

Faster schedules are not free, and the bill arrives in four places.

Your provider's rate limits. Stripe's published limits are 100 requests per second in live mode and 25 per second for an individual endpoint, which sounds like plenty. The constraint that actually bites is quieter: Stripe allocates read requests relative to your transaction count, at "an average of 500 per transaction" over a rolling 30 days, with a floor of 10,000 read requests per month. A business processing 100 transactions a month has a 50,000 read allocation, and a paginated full sync of several tables can consume a meaningful slice of that in a single run. Multiply by 24 runs a day and the arithmetic stops working. Xero is stricter still, with a per-tenant daily cap that starts at 1,000 calls a day on its entry tier. QuickBooks and Paddle both enforce their own per-minute limits.

Sync duration and overlap. Every run has fixed overhead: authenticating, paginating, comparing rows, writing. Schedule runs closer together than a run takes to finish and you get overlapping jobs competing for the same table, which is a good way to turn a working pipeline into an intermittent one.

Your own quota. Every managed sync tool meters something. Runs that produce no changes still consume your daily sync allowance, so an aggressive schedule spends your plan on re-reading unchanged rows.

Database write amplification. Frequent full syncs rewrite rows that did not change, which inflates your Postgres write volume and, on serverless hosts that bill on compute time, your database bill too.

None of these are catastrophic individually. Together they mean an hourly schedule on data that changes daily is pure waste.

Matching the Sync Mode to the Schedule

This is the part that most often goes wrong, and it matters more than the frequency itself.

A scheduled sync has two settings, not one: how often it runs, and how much data each run fetches. In Codeless Sync, a full sync is the default and re-reads everything in the table. The alternatives are fixed lookback windows: the last day, the last 7 days, or the last 30 days. These are windows, not a bookmark. A "last 7 days" run always asks for the last 7 days of changes, regardless of when the previous run happened or whether it succeeded.

That single detail drives the rule:

Your lookback window must be wider than the gap between runs. If they are equal, one failed or delayed run leaves a permanent hole in your data.

A daily schedule paired with a last-day window has zero margin. The run that fails at 2am on a Sunday silently loses Saturday's changes, and nothing later goes back for them. Pair a daily schedule with a last-7-days window instead and you get six days of self-healing overlap for almost no extra cost, because re-fetching a mostly-unchanged week is cheap.

Sensible pairings:

Schedule	Lookback window	Why
Twice daily	Last 7 days	Wide safety margin, still a small fetch
Daily	Last 7 days	Six days of overlap absorbs failed or skipped runs
Weekly	Last 30 days	Covers a missed week and late-arriving edits
Monthly	Full sync	Volume is low enough that correctness beats efficiency
Any, small table	Full sync	Under a few thousand rows, just re-read it and stop thinking

Full sync deserves more credit than it usually gets. It is the only mode that reliably reflects deletions and corrections made to historical records, which accounting systems produce constantly when someone amends last quarter's invoice. If your tables are small, full sync on a daily schedule is the most robust configuration available and the reason it is the default.

When Scheduling Is the Wrong Tool

There is a case where no schedule is correct, and it is worth naming plainly so you do not try to solve it with frequency.

If you need to react to an individual billing event within seconds, provisioning access the moment a payment clears, or emailing a customer the instant a card fails, that is not a reporting problem and a sync will not solve it at any cadence. Even a one-minute schedule gives you an average latency of thirty seconds and a worst case of sixty, with no ordering guarantees. Use the provider's webhooks for that specific workflow: they exist precisely for event-driven reactions, and they push in real time.

The mistake is assuming that because you need webhooks for one workflow, you need them for everything. Webhooks are excellent at "tell me when this one thing happens" and poor at "give me a complete, queryable table I can run SQL against", because they deliver events rather than state, they can be missed, and they never cover history. A scheduled sync is the opposite on all three counts.

Most teams end up wanting both, and that is a reasonable architecture: webhooks for the two or three events that trigger immediate action, a scheduled sync for the tables you query. We covered the trade-off in detail for Stripe and for Paddle.

Setting It Up

In Codeless Sync, scheduling lives on the configuration you already created. You pick a frequency, pick a sync mode, and the schedule runs without further involvement. Daily, weekly and monthly are available on every paid plan, and twice daily is available on Business. Free accounts sync manually.

Two practical notes. First, schedules are evaluated against a fixed time, so choose an hour that suits your reporting rather than leaving everything at midnight UTC, especially if your finance team works to a local month end. Second, a schedule only runs when the project, the configuration and the schedule itself are all active, so if runs stop appearing, check all three before assuming a failure.

The schedules documentation covers the mechanics, and how to use cron expressions for scheduled data syncs explains the syntax behind the presets. If you have not connected a database yet, the quick start takes about five minutes.

A Reasonable Default

If you want to stop reading and just pick something:

Daily, with a last-7-days window, at an hour that suits your reporting. Move to twice daily only if you act on dunning manually. Move to weekly if your data is accounting-led and entered by hand. Switch to full sync if your tables are small or your source system amends historical records.

You can change it later. That is rather the point of not agonising over it now.

Frequently Asked Questions

How often should I sync Stripe data to Postgres?

Daily suits almost every reporting, dashboard and reconciliation use case. Twice daily is worth it if you manually work failed payments. Faster than that rarely changes a decision, and it consumes your Stripe read allocation, which is capped relative to your transaction volume rather than being unlimited.

Is real-time syncing better?

Not for billing data. Real-time is better for reacting to individual events, which is a webhook's job. For a queryable table you run SQL against, a scheduled sync gives you completeness and history, which matter more than latency for reporting.

What happens if a scheduled sync fails?

The next scheduled run goes ahead as normal. Because lookback windows are fixed rather than resuming from a bookmark, a failed run is only self-correcting if your window is wider than your schedule interval. A daily schedule with a last-7-days window recovers automatically. A daily schedule with a last-day window does not.

Should I use full sync or an incremental window?

Full sync if your tables are small, or if your source system amends historical records, since it is the mode that reflects deletions and corrections. An incremental window once the table is large enough that re-reading it every run is wasteful. Full sync is the default for that reason.

Does syncing more often cost more?

The subscription price does not change, but each run counts against your daily sync allowance, and every provider enforces API rate limits. Frequent full syncs also increase Postgres write volume, which can matter on hosts that bill by compute time.

Can I sync different tables on different schedules?

Yes. Scheduling is set per configuration, so you can run invoices daily and customers weekly by creating separate configurations. This is usually a better use of your sync allowance than putting everything on the fastest schedule.

Wrapping Up

The honest answer to "how often should I sync" is "less often than you first assumed, with a wider lookback window than you first assumed".

Billing data moves in bursts tied to billing cycles, and the decisions you make with it are periodic. Matching your schedule to those rhythms, and leaving enough overlap that a failed run heals itself, gets you a more reliable pipeline than chasing freshness ever will.

See the pricing page for which schedules come with which plan, or start on the free tier and sync manually until you know what cadence you actually need.

Related:

Fivetran Pricing Explained: Why MAR Billing Is Overkill for One Billing Sync

ilshaad — Mon, 17 Aug 2026 08:14:09 +0000

Fivetran prices on monthly active rows. Here is how MAR actually works, what changed in January 2026, and why the bill is hard to forecast for one sync.

By Ilshaad Kheerdali · 17 August 2026

You have one billing source, Stripe or QuickBooks or Xero or Paddle, and one Postgres database. You want the first to land in the second on a schedule. Somewhere in the research you end up on Fivetran, and the pricing page quotes you in a unit you have never used before: monthly active rows.

MAR is not a bad idea. It is a genuinely fairer meter than per-connector seat pricing for a company moving hundreds of tables. The problem for a reader in your position is narrower and worth naming precisely: MAR is priced on how much your data changes, and you cannot know that number before you start.

This post explains how MAR works, what changed on 1 January 2026, and where the model stops making sense for a single billing source.

What a Monthly Active Row Actually Is

A monthly active row is a distinct row that was inserted, updated or deleted in a calendar month, identified by its primary key.

The part people get wrong is the counting. From Fivetran's own documentation: "We only count a row once per month, even if it syncs multiple times." A subscription record that changes thirty times in March is one MAR, not thirty. That is the model working in your favour, and it is why running syncs more often does not directly cost more.

Two details matter more than the headline:

MAR is measured per connection, not per account. Since March 2025, Fivetran calculates usage separately for each account, destination, connection and table. Syncing the same Stripe data to two destinations counts twice.
Rows that did not change are free. Only churn is billable, which is why a table with a million static rows can cost almost nothing while a small, busy table costs more.

What Changed on 1 January 2026

Three things moved, and older comparison posts have not caught up.

Deletes now count toward paid MAR. Fivetran's 2026 pricing update states it plainly: "As of January 1, 2026, inserts, updates, and deletes will count toward paid MAR. Previously, deletes did not contribute to paid MAR." The reasoning is defensible, since a deletion is a real change with analytical value, but it is a straightforward increase for any source that removes records.

There is now a minimum charge per connection. A $5 base charge applies to every standard connection doing between 1 and 1 million MAR per month. If you have five small connections that barely move, you now have a $25 floor before any usage is counted. The Free plan is exempt.

The Starter and Private Deployment plans are gone. The current lineup is Free, Standard, Enterprise and Business Critical, the last of which adds customer-managed encryption keys, PCI DSS Level 1 and private networking.

Yes, the Free Plan Is Real

Most posts arguing against Fivetran skip this, and skipping it makes the argument worse rather than better.

Fivetran's Free plan includes 500,000 MAR per month, along with 3,500 activation MAR and 5,000 transformation model runs. It is a plan, not a trial. There is also a 14-day account trial, and separately each new connection gets 14 days of free use starting when incremental syncs are first detected.

For a single billing source at small scale, 500,000 changed rows in a month is a lot of headroom. A SaaS business with a few thousand customers and a few thousand invoices a month will not come close. If that is you, Fivetran may well cost you nothing, and any post telling you it is too expensive is selling you something.

So the honest question is not "is Fivetran expensive". It is "what happens after that, and can you see it coming".

Why the Bill Is Hard to Forecast

Here is the part that should decide it for a one-source setup.

There is no published per-MAR rate. Fivetran's pricing page describes a consumption curve where the per-row cost falls as volume rises, and points you to a Service Consumption Table and a pricing estimator. Neither renders a plain rate card you can read, quote or put in a spreadsheet. I went looking specifically to include real numbers here and could not verify a single per-MAR figure from Fivetran directly.

Third-party sites fill the gap badly. In the course of writing this I found published claims of $500 per million MAR and $2.50 per million MAR for the same Standard plan. Those differ by a factor of two hundred. At least one is wrong, possibly both, and there is no authoritative document to settle it. If you see a confident per-row number in a comparison post, treat it as a guess.

The meter tracks your customers' behaviour, not your requirements. This is the structural issue. Your infrastructure needs are flat: one Stripe account, a handful of tables, a nightly refresh. Your MAR is driven by how many subscriptions changed state, how many invoices moved from open to paid, how many records were removed. A billing month with a promotion, a price migration, a dunning wave or a bulk cleanup produces a bigger invoice than a quiet month, for the same pipeline doing the same job.

Cost arrives as a step, not a slope. Below 500,000 MAR you pay nothing. Above it you are on a paid plan with a $5 per-connection floor and a rate you had to run an estimator to discover. Nothing warns you as you approach the line, and the crossing is caused by your customers, not by a decision you made.

For a data team running fifteen sources into Snowflake, forecasting that is a normal part of the job and the volume discounts are worth having. For one billing sync into Postgres, you have taken on a variable cost and a modelling exercise to solve a fixed problem.

What Actually Drives MAR on a Billing Sync

It helps to see where the churn comes from, because it is rarely the table you expect.

Customers are the calmest table. A customer record changes when someone updates a card, an address or an email, so a few percent of the table per month is typical.

Subscriptions are busier than they look. Every status transition is an update: trialing to active, active to past_due, past_due to canceled, plus renewals, plan changes and quantity changes. A subscription can produce several distinct active months per year even when the customer does nothing unusual.

Invoices and charges are the volume driver, and they are lifecycle-heavy. A single invoice moves through draft, open, paid, and possibly through a failed payment and a retry. Because MAR counts a row once per month regardless of how many of those transitions happen, this is much better than it sounds, but the row count still scales directly with your transaction volume.

The rough shape: your monthly MAR is closer to "customers who did something" plus "invoices raised this month" than to the total size of your Stripe account. That is the number to estimate before signing anything, and it is also the number that moves without warning.

One thing that does not cost you: historical syncs are free. Fivetran's documentation confirms that initial syncs do not incur a charge for the historical data they load, and that tracked incremental updates are what may count outside a connection's free trial period. Backfilling five years of Stripe history is not what puts you over the line. Ordinary month-to-month operation is.

When Fivetran Is the Right Call

It would be dishonest to write this without the other side, so here it is.

Fivetran is a serious product and MAR is a serious pricing model. Choose it when you have many sources rather than one, when the destination is a warehouse like Snowflake, BigQuery, Redshift or Databricks, when you need 700+ connectors covering systems nobody else supports, or when you need the compliance posture in the Business Critical tier. At real volume the consumption curve works for you, since the per-row cost falls as usage rises, and an annual commitment takes up to 22% off.

If you are building a central analytics platform, the flexibility is worth the forecasting work.

When It Is Overkill

One billing provider. One Postgres database. A handful of tables. A once- or twice-daily refresh.

That workload has no need for 700 connectors, no warehouse, no transformation layer, and no reason to be metered on row churn. You are buying a platform to solve a problem that is closer to a scheduled copy, and paying for it in a unit that varies with your customers' behaviour.

What you want instead is a flat number you can put in a budget and forget.

The Flat-Fee Version

Codeless Sync does the narrow version of this job: Stripe, QuickBooks, Xero and Paddle into PostgreSQL, including Supabase, Neon, AWS RDS, Railway and DigitalOcean. You connect the provider, pick a data type, and the destination table is created for you.

The pricing is a short list of fixed numbers you can read before you sign up. Free is $0 for 2 manual syncs a day, 2 configurations and 1 database project. Paid tiers start at $19 a month for Starter, $29 for Pro and $99 for Business, and each adds scheduled syncs, more configurations and a larger monthly row allowance.

Every tier does include a monthly row allowance, so volume is not irrelevant. The difference is what happens at the boundary: an allowance is a ceiling you can read on the pricing page before you commit, not a meter that turns a busy billing month into a larger invoice. You can outgrow a plan. You cannot be surprised by one.

Scheduled syncs are the paid feature, and when you create a schedule you choose the sync mode. A full sync is the default and re-reads everything, or you can pick a fixed window that fetches records changed in the last day, 7 days or 30 days. Keep the window wider than the gap between runs, so a daily schedule pairs best with the last-7-days window and a failed run heals itself on the next one. Cron expressions for scheduled data syncs covers the syntax if you want a specific cadence.

Side by side, on the axes that actually differ:

	Fivetran Standard	Codeless Sync
Pricing unit	Monthly active rows, per connection	Flat monthly fee
Published rate you can quote	No, estimator only	Yes, $19 to $99 a month
Same bill every month	No, tracks row churn	Yes
Sources	700+	Stripe, QuickBooks, Xero, Paddle
Destinations	Warehouses, databases, 200+ targets	PostgreSQL
Free tier	500,000 MAR per month	2 syncs per day, no card
Best for	Many sources into a warehouse	One or two billing sources into Postgres

Fivetran wins the top half of that table and it is not close. The question is whether you need any of it.

If you want the wider field rather than a straight comparison, the best tools to sync Stripe data to a database covers seven options with honest trade-offs, and the best Stripe Sigma alternative for Postgres users covers the reporting angle specifically.

Frequently Asked Questions

What is a monthly active row in Fivetran?

A distinct row, identified by primary key, that was inserted, updated or deleted during a calendar month. It is counted once per month no matter how many times it syncs, and it is measured separately for each connection, destination and table.

Is Fivetran free for a small Stripe sync?

It can be. The Free plan includes 500,000 MAR per month, which is more changed rows than most small SaaS businesses generate. The catch is that nothing tells you how close you are getting, and crossing the line puts you on a paid plan with a rate you have to run an estimator to find.

Did Fivetran's pricing change in 2026?

Yes, on 1 January 2026. Deletes now count toward paid MAR where previously they did not, and a $5 minimum charge now applies to standard connections doing between 1 and 1 million MAR per month. The Starter and Private Deployment plans were also discontinued.

Why can't I find Fivetran's price per row?

Because it is not published as a flat rate. The per-MAR cost follows a consumption curve that falls as volume rises, and Fivetran directs you to a pricing estimator rather than a rate card. Per-row figures quoted on comparison sites are unreliable, and I found published claims differing by a factor of two hundred for the same plan.

Does a historical backfill count toward my MAR?

No. Fivetran's documentation states that initial syncs do not incur a cost for the historical data they load. It is the ongoing incremental updates that count, so loading years of Stripe history is not what pushes you over a threshold.

What is a cheaper alternative for one billing source?

If your requirement is one or two billing providers into PostgreSQL, a flat-fee tool is both cheaper and easier to budget. Codeless Sync is $0 for manual syncs, and scheduled syncs start at $19 a month, with the same bill regardless of how many rows changed.

Wrapping Up

MAR is a reasonable answer to a question you probably are not asking. It exists so that a company moving thousands of tables pays in proportion to the work done, and it does that job well.

For one billing source going into one Postgres database, it converts a fixed requirement into a variable cost, prices it in a unit you cannot look up, and hands you a threshold your customers control. Fivetran's free tier may cover you today. The thing worth weighing is what happens the month it does not, and whether you would rather just know the number.

Try the flat-fee version at codelesssync.com

Related:

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: