PayPal to Postgres: how to replicate transactions your finance team can query

How do you connect PayPal to Postgres?

You connect PayPal to Postgres by reading transactions through the PayPal Transaction Search API and writing them into Postgres tables, because PayPal has no native database export and no read replica to point psql at. Something sits in the middle: a managed connector, or a scheduled job that gets an OAuth2 token, walks bounded date windows, and upserts each page keyed on the PayPal transaction id.

The connection has three parts. First, a PayPal REST app in the developer dashboard that gives you a client id and secret. You exchange those for an access token with the OAuth2 client_credentials grant, and the token is short lived, so the job requests a fresh one and caches it until it expires. The app also needs the Transaction Search feature enabled on the account. Second, a Postgres landing spot: a database, a schema, and a role that can create tables and run upserts. Third, the transfer loop itself, which steps a start and end date across the range you want, pages each window with page and page_size, lands each batch in staging, and upserts into the target. The managed path is the PayPal to Postgres integration, which owns the token refresh, the date windowing, the pagination, the staging tables, the upserts, and the backoff, so you configure the field mapping and leave the plumbing alone.

Method	What it is	When it fits
Full historical backfill in date windows	Step the Transaction Search API from your earliest available date to now, one bounded window at a time, paging each window to the last record	The first load, and any time you need history. PayPal limits how far back you can reach (about three years), so archive older data separately
Recent-window incremental	Re-read a trailing window (for example the last several days) on a schedule and upsert, so new and newly-settled transactions land	The default steady state. Cheap, catches status changes, and needs no webhook endpoint to maintain
Webhooks (IPN or webhooks, best-effort)	PayPal POSTs an event to your endpoint when a payment or refund happens, which you verify and treat as a nudge to re-read	Lower latency hints. Best effort only: verify the signature, treat it as a trigger to re-pull the window, never as the source of truth
Managed sync tool	A hosted connector that owns the token, the windowing, the pagination, and idempotent loads	When you would rather not maintain token refresh and window bookkeeping. Less control over the exact request shape

Most production setups combine two: a full historical backfill in date windows for the initial load, then a recent-window incremental on a schedule for the steady state. Webhooks are an optional latency booster on top, not a replacement, because they can be missed or delivered out of order. If your reporting lives in a warehouse rather than an application database, the sibling path is PayPal to BigQuery, and the same date windowing and reconciliation discipline carries over unchanged.

Does PayPal have an API to export transaction data?

Yes. PayPal exposes the Transaction Search API under its REST platform at v1/reporting/transactions, which returns transaction detail for a date range, but it is a reporting endpoint, not a database and not a bulk dump. You authenticate with an OAuth2 client_credentials token, pass a start_date and end_date in ISO 8601, and read results a page at a time. There is no direct database connection.

Two constraints shape everything you build on top of it. First, the endpoint returns a bounded window: historically a single call can span up to 31 days, and the searchable history reaches back around three years, so a full backfill is a loop of adjacent windows, not one request. Second, data is not instantly available. PayPal requires a transaction to be a few hours old before it appears in Transaction Search, so a sync that only ever reads "the last hour" will miss recent activity. Each record arrives as a transaction_info object with fields like transaction_id, transaction_amount (the gross), fee_amount (a negative value PayPal charged), transaction_status, and transaction_initiation_date, alongside a payer_info object with the payer email and name. That gap between a reporting API and a database is the whole reason this pipeline exists. Calling PayPal live from a dashboard is slow and easily throttled, and you cannot join PayPal responses to your own orders or accounting tables. Once the data sits in Postgres, attributing revenue to orders or reconciling fees is one join instead of a paging loop, which is exactly what a data integration platform is for.

How PayPal fields map to Postgres tables and types

Keep the money fields exact and keep net derived. PayPal hands you gross and fee as separate amount objects, each with a numeric value and a three-letter ISO currency code, and net is gross plus fee (fee is negative). Store gross and fee as they arrive, compute net once, and never fold the amounts into a floating point type, because binary floats cannot hold every two-decimal cent and the drift shows up when finance sums a month.

PayPal field	Postgres column	Type and notes
transaction.id	paypal_transaction.transaction_id	TEXT primary key. Stable per transaction. The upsert conflict target
transaction.gross	paypal_transaction.gross_amount	NUMERIC(19,4). The gross value before fees. Store as NUMERIC so cents never drift
transaction.fee	paypal_transaction.fee_amount	NUMERIC(19,4). The PayPal fee, arrives negative. Keep the sign as PayPal sends it
net (derived)	paypal_transaction.net_amount	NUMERIC(19,4). Computed as gross plus fee. Derive it once on load, do not re-key
payer.email	paypal_transaction.payer_email	TEXT. Flatten from the payer_info object. Joins to your customers table
transaction.status	paypal_transaction.status	TEXT. P, S, V, or D. Can change after creation, so re-pull recent windows
transaction.created	paypal_transaction.created_at	TIMESTAMPTZ. Parse the ISO 8601 initiation date, store in UTC, index it
currency	paypal_transaction.currency_code	CHAR(3). Three-letter ISO currency. Keep it beside every amount, never blend currencies

The status codes are worth memorizing because reconciliation depends on them: P is pending, S is successfully completed, V is reversed, and D is denied. A sale can land as P, settle to S hours later, and move to V if it is reversed, which is precisely why a one-time export is misleading. Refunds and chargebacks do not overwrite the original row; PayPal records them as separate transaction events with their own ids and their own (negative) amounts, so your net revenue for a period is the sum across those events, not a single field. Model that as ordinary rows and the arithmetic stays honest. With the settlement data modeled cleanly in Postgres you can join it to orders and, once net revenue is reconciled, feed the reconciled net figures straight into a tool that turns the ledger into board-ready financial statements without re-reading PayPal for every refresh.

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

Sync incrementally by re-reading a trailing date window on a schedule rather than replaying all of history. Store the end of your last successful window as a watermark, start the next run a little before it, page to the end, and upsert. Overlap is deliberate: because transactions appear a few hours late and change status after creation, re-reading recent days catches both new and updated rows.

The mechanics are a two-level loop. The outer loop steps the date range in windows the API will accept (historically up to 31 days each), and the inner loop pages a single window with page and page_size until it returns the last page. For steady-state incremental runs you do not need the full three years; you need a trailing window wide enough to cover PayPal's ingestion delay and any late status changes, commonly the last few days to a week. Set the window start to a few hours or a day before your stored watermark, not exactly at it, so a transaction that only became visible after your previous run is still inside the window this time. Then advance the watermark to the end of the window you just read. Because the load is idempotent (the next section), overlapping windows cost nothing: a row you have already seen updates itself in place. The one rule you cannot break is never assuming a transaction is final on first sight, since a P today can be S or V tomorrow.

How do you reconcile PayPal gross, fees, and net to your payout?

Reconcile by summing gross and fee across every transaction event in the settlement period and checking that gross plus fees plus refunds equals the net PayPal actually paid out. Because gross, fee, and net live in separate amount objects and refunds and chargebacks are their own rows, keep each as its own column, sum with SQL, and compare the total to the payout figure from PayPal.

Reconciliation is where the modeling pays off. A payout is a batch of transactions PayPal moves to your bank, and its amount is the net of everything in the batch: sales gross, minus PayPal fees, minus refunds and reversals in the window. If you stored only a single "amount" per row you cannot rebuild that number, which is why gross and fee are separate NUMERIC(19,4) columns and net is derived rather than trusted from a lone field. Group your paypal_transaction rows by settlement period, SUM(gross_amount), SUM(fee_amount), and SUM(net_amount), and the net sum should match the payout. When it does not, the usual causes are a currency you blended by accident (always group by currency_code too), a status you counted that should have been excluded (a denied D transaction never settled), or a window boundary that split a payout in half. Keeping currency beside every amount and treating refunds as first-class rows makes the query a one-liner, and a PayPal to BigQuery path gives the same numbers to a warehouse if analytics lives there instead.

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

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

This is the single most important property of the pipeline, because everything else about PayPal pushes you toward overlap: windows have to overlap to cover the ingestion delay, status changes force you to re-read recent days, and retries replay pages you already loaded. Without an upsert, each of those would double a row. With one, they are free. The DO UPDATE clause is what lets a status change actually land: when a P becomes S, the second read carries the same transaction_id with a new status, and the upsert overwrites the status, the net, and the loaded_at timestamp in place. Set DO UPDATE SET status = EXCLUDED.status, net_amount = EXCLUDED.net_amount, loaded_at = now() and let the primary key do the deduplication. Refunds and chargebacks never collide with the original because they carry different ids, so they insert as new rows exactly as they should. Keep a loaded_at column on the table so freshness checks and drift debugging are a one-line query, and the managed PayPal to Postgres sync wires all of this up without you writing the conflict clause by hand.

How often should you sync PayPal to your database?

For most finance and ops teams, every hour on a trailing window is the right steady state, with a wider nightly pass that re-reads the last week to catch late status changes. Match the interval to the decision the data drives: cash and revenue dashboards are happy hourly, while month-end reconciliation only needs a daily settled view. Syncing every few minutes buys little, because PayPal only surfaces transactions a few hours after they happen.

Backfilling is a different job from the steady state and should be built as one. Run the full historical load as a loop of adjacent date windows from your earliest reachable date (roughly three years back) to now, page each window to its end, record which windows completed so a failure at 80 percent resumes rather than restarts, and load into staging before you upsert. Only after the backfill lands should you set the incremental watermark, and set it to the start of the backfill rather than its end so anything that changed while the backfill ran gets picked up on the first incremental pass. Metered per-row pipeline pricing interacts badly with a busy sales month, since row counts climb exactly when you least want a surprise invoice. Our flat pricing is by plan rather than per row, so a heavy month does not change the bill. If you want to see the pair running against your own account before you wire anything up, book a walkthrough or map it yourself with the PayPal to Postgres integration.