Square to Postgres: how to replicate payments data your app can actually query

How do you connect Square to Postgres?

You connect Square to Postgres by reading Square objects through the Square API and writing them into Postgres tables, because Square gives you no database endpoint to point psql at. Something has to sit in the middle: a managed connector, or a scheduled job that authenticates with an access token, lists each object type, pages through the cursors, and upserts every batch into your schema on the Square id.

The connection has three parts. First, credentials: a Square application with an OAuth access token (or a personal access token for a single seller) scoped to the payments, orders, and customers read permissions, plus the merchant and location ids you are reading. Square runs a sandbox and a production environment on separate base URLs and separate tokens, so build against sandbox and cut over to production once the schema is stable. 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 walks each list endpoint by cursor, lands each page in staging, and upserts into the target. The managed path is the Square to Postgres integration, which owns the token refresh, the cursor paging, the updated-at watermark, the staging tables, the upserts, and the backoff.

Method	What it is	When it fits
Full reload	Walk every object type end to end by cursor with no date filter, truncate, and reload the target	The first backfill, and tiny reference sets like locations. Wasteful to rerun on payments and orders that barely change between runs
Updated-at watermark	The same cursor walk, but filtered so each run only returns records with updated_at after your stored maximum	The default for steady-state incremental reads. Cheap and fast, but you must overlap the window and reconcile deletes and refunds separately
Webhooks	Square POSTs an event (for example payment.updated or refund.updated) to your endpoint when something changes	Near real time refresh hints. Best-effort delivery only: verify the signature, treat each event as a nudge to re-read the object, never as the source of truth
Managed sync tool	A hosted connector that owns tokens, cursor paging, throttling, and idempotent loads	When you would rather not maintain token rotation and refund reconciliation. Less control over exact field selection

Most production setups combine three of these: a full reload for the initial backfill, the updated-at watermark for the steady state, and webhooks to shorten latency and to hint at refunds and disputes that mutate a payment after it settled. If your reporting lives in a warehouse rather than an application database, the sibling path is Square to Snowflake, and the modeling discipline below carries over unchanged.

Does Square have an API to export payments data?

Yes. Square exposes a REST API with a Payments API, an Orders API, a Refunds API, a Customers API, and a Payouts API, but it is a per-object list API, not a database and not a bulk dump. You authenticate with a token, call each list endpoint, and page through results with an opaque cursor. There is no direct database connection, no read replica, and no single query that joins a payment to the order it paid for.

Each endpoint is deliberately narrow. ListPayments returns payments for a date range with a cursor; SearchOrders filters orders by location and state; the Refunds and Payouts APIs return their own object shapes. Every object carries an id, a location_id, RFC 3339 timestamps in created_at and updated_at, and money as a nested object rather than a bare number. That gap between a list API and a database is the whole reason this pipeline exists. Calling Square live from a dashboard is slow and easily throttled: results come back a page at a time, requests are rate limited, and a report that ties a payment to its order, its refunds, and the payout that settled it turns into many round trips because the API cannot join across objects. Once the data sits in Postgres, attributing revenue by location, or reconciling Square payouts against your bank feed, is one join instead of a paging loop, which is exactly what a data integration platform is for.

How Square objects map to Postgres tables and types

The one rule that matters everywhere is money. Square never sends a monetary value as a bare decimal. It sends a money object, { "amount": 1299, "currency": "USD" }, where amount is an integer in the smallest unit of that currency (cents for USD, and note that not every currency has two decimal places). Store the integer amount and the currency code together, either as integer cents plus a currency column or as NUMERIC(19,4) after dividing by the currency's minor-unit factor. Never coerce it into a floating point type: binary floats cannot hold every two-decimal cent exactly, and summing thousands of payments drifts by pennies that reconciliation will flag.

Square field	Postgres table.column	Type and notes
payment.id	square_payment.id	TEXT primary key (or BIGINT only if you map to a surrogate). The Square id is a string; keep it as TEXT and make it the upsert conflict target
order.total_money.amount	square_payment.total_cents	Integer cents (BIGINT), or NUMERIC(19,4) once divided by the minor-unit factor. Store the paired currency in its own column
buyer.email_address	square_payment.buyer_email	TEXT, nullable. Often absent on card-present sales; do not assume it is present
payment.location_id	square_payment.location_id	TEXT foreign key to a replicated square_location.id. The key to per-location reporting
payment.status	square_payment.status	TEXT. APPROVED, COMPLETED, CANCELED, or FAILED. Changes over the payment's life, so the watermark must re-read it
payment.updated_at	square_payment.updated_at	TIMESTAMPTZ. Parse the RFC 3339 string to UTC. The incremental watermark; index it per table
refund.id, refund.amount_money	square_refund (child table)	Own table keyed to payment id. A payment can have several partial refunds, so never flatten them into the payment row
payout.id	square_payout.id	TEXT. Ties settled payments to a bank deposit for reconciliation against the payout total

Model refunds and payment line detail as their own tables, not columns. A payment can be refunded in parts and disputed after it settled, so a refund is a separate object with its own id, amount, and status that points back at the payment. Keeping refunds in a child table lets you compute net revenue per location as a plain join and aggregate, and it means a new refund on an old payment is just an insert into the refund table plus an update to the payment row. This modeling is where a solo operator and a finance team diverge: if you are a solo seller who just needs to track what each payout actually nets you after fees and refunds, a light copy of payments and payouts is plenty; a multi-location business modeling margin wants the full object graph.

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

Sync incrementally by filtering each list call on the updated-at range, so you only fetch records that changed since your last run. Store the maximum updated_at you saw as a watermark, resume from it next time, and overlap the window by a few minutes so a record whose timestamp landed out of clock order is never skipped. Refunds and status changes flow through the same way, because they bump the payment's updated_at.

A typical run reads the stored watermark, calls the list endpoint with a begin time a few minutes before it, and walks the cursor to the end of the range. When a page comes back it upserts the batch and tracks the largest updated_at seen, then advances the watermark once the walk finishes cleanly. Updated-at works because Square touches updated_at whenever a payment's status moves from APPROVED to COMPLETED, when a refund is created, or when a dispute changes state, so those late mutations come back on the next incremental pass without a full reload. Webhooks are the optional accelerator: a payment.updated or refund.updated event tells you to re-read one object now rather than waiting for the next scheduled run, but because delivery is best-effort you still lean on the watermark as the source of truth. The overlap is what keeps this correct, and it costs nothing because the load is idempotent, which is the next section.

How do you handle the Square API rate limit and pagination?

Handle it by paging with the cursor Square hands you and pacing requests so you stay under the rate limit instead of firing as fast as you can. Square list endpoints return an opaque cursor with each page; you pass it back on the next call and stop when the response omits it. When a throttle response comes back, back off and retry the same cursor rather than hammering the endpoint.

Cursor pagination is different from offset pagination in a way that matters. There is no page number and no way to jump to page 40; you must walk pages in order, and the cursor is opaque, so you cannot construct or cache one. Persist the cursor of the page you are on so a crash resumes mid-walk instead of restarting the whole range. Keep pages moving in a single ordered loop per object type rather than trying to parallelize one endpoint's pages, because you cannot fan out over cursors you do not have yet. On rate limits, Square returns an error you should treat as expected under load: wrap each request in retry logic with exponential backoff that recognizes the throttle, waits, and resumes from the same cursor so no page is skipped or double read. Because the steady-state watermark run only touches records that changed, you rarely approach the ceiling at all; it is the initial backfill that needs pacing, so chunk that by date range and by object type. If you later add a warehouse target, the same discipline applies to Square to Snowflake.

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

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

The load has a consistent shape. Land each page in staging, then upsert from staging into the target in one statement so inserts and updates happen together. Child objects need the same discipline one level down: when a payment gains a new partial refund, upsert on the refund.id so re-reading the same payment does not duplicate refunds it already has. The conflict target is always the provider id, never a pairing of columns you assembled yourself, because Square guarantees the id is unique and stable while your composite keys will collide the first time a seller reuses a reference note.

Refunds and disputes are the case a naive one-time export can never keep up with. A payment settles, then days later it is partially refunded or a cardholder opens a dispute, and both change the payment's status and updated_at. The updated-at watermark catches those because the timestamp moves, but a static CSV pulled last week does not. Cancellations are the trickier tail: if a record can disappear rather than change status, a watermark will never surface it, so run a periodic reconciliation that pulls the current id set over a trailing window and flags local ids Square no longer returns. Keep a loaded_at column on every table so freshness checks and drift debugging are a one-line query, and lean on the managed Square to Postgres sync when you would rather not hand-build the refund and dispute reconciliation.

How often should you sync Square to your database?

For most retail and ops teams, every fifteen to sixty minutes on the updated-at watermark is the right steady state, with a nightly wider reconciliation pass to catch cancellations and to true up payout totals. Match the interval to the decision the data drives: a live sales dashboard wants fifteen minutes, while margin and cohort reporting are fine hourly or daily. Syncing more often than anyone reads just spends request budget for nothing.

Backfilling a seller is a different job from the steady state and should be built as one. Run a full cursor walk per object type, chunk payments and orders by created_at range so each chunk is a bounded walk you can retry independently, and record which chunks completed so a failure at 80 percent resumes rather than restarts. Load into staging first, then upsert. Only after the backfill lands should you set the watermark, and set it to the start time of the backfill rather than its end so anything that changed while the backfill was running gets picked up on the first incremental pass. Metered per-row pipeline pricing interacts badly with a busy sales day, since row counts spike exactly when you least want a surprise invoice. The Adapters plan is a flat $49 a month rather than per row, so a heavy weekend does not change the bill. If you want to see the pair running against your own Square account before you wire anything up, book a walkthrough or map it yourself with the Square to Postgres connector.