If you pull payment data from Square's API and sum it, the number you get will be higher than the money that reached the bank. Not by a rounding error. On one merchant account we work with, the gap was $52,571.
Two separate mistakes produce it, and they compound. Both are easy to make, because in each case the API is behaving correctly and the mistake is in the assumption you bring to the payload.
Mistake one: summing every transaction, not the completed ones
Square's Payments API returns a status field on every payment object. The
values that matter are COMPLETED, FAILED, and CANCELED.
A failed payment is a real record. The customer tapped, the terminal read the
card, a row was written. It has an amount_money on it. If your query selects
payments for a date range and sums amount_money, that failed swipe is now
revenue.
So is the canceled one. So is the retry that failed twice before succeeding, which means a single $85 service can enter your totals three times: $85 failed, $85 canceled, $85 completed, $255 counted.
This does not show up in Square's own dashboard, which filters correctly. It shows up in anything built on the raw payload. In the account above, 246 rows carried a non-completed status.
The fix is a WHERE clause, and the reason it gets missed is that nothing
about the response makes it feel necessary. The rows look like sales.
WHERE status = 'COMPLETED'
Mistake two: bucketing by UTC instead of merchant time
Square returns timestamps in UTC. Your merchant does not live in UTC.
For a business on Pacific time, the offset is seven or eight hours depending on daylight saving. Every sale rung up after 4:00 PM local lands on the following calendar day once the timestamp is read as UTC. For an evening-heavy business, that can be a third of the day's transactions moving to tomorrow.
Daily totals are wrong in both directions. Weekly totals are wrong at the boundaries. Month-end is wrong in a way that matters, because the last evening of the month falls into the next one, and the comparison you show the merchant against last month is built on two differently broken windows.
The correction is to convert the timestamp into the merchant's own timezone before extracting a date, which means the timezone has to be stored per merchant and joined in.
(t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date AS truth_date
What the corrected view looks like
Both fixes belong in the same place: a single view that every downstream query reads from, rather than a filter each report remembers to apply.
CREATE VIEW public.v_register_truth
WITH (security_invoker = true)
AS
SELECT
t.user_id,
t.integration_id,
(t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date AS truth_date,
COUNT(*) AS transaction_count,
COALESCE(SUM(t.gross_amount), 0::numeric) AS gross_sales,
COALESCE(SUM(t.tip_amount), 0::numeric) AS tips_collected,
COALESCE(SUM(t.tax_amount), 0::numeric) AS tax_collected,
COALESCE(SUM(t.refund_amount), 0::numeric) AS refunds,
COALESCE(SUM(t.processing_fee), 0::numeric) AS processing_fees,
COALESCE(SUM(t.total_collected), 0::numeric) AS total_collected,
COALESCE(SUM(t.gross_amount - t.refund_amount), 0::numeric) AS net_sales
FROM public.transactions t
LEFT JOIN public.integrations i ON i.id = t.integration_id
WHERE t.platform = 'square'
AND t.status = 'COMPLETED'
GROUP BY
t.user_id,
t.integration_id,
(t.transaction_date AT TIME ZONE COALESCE(i.timezone, 'UTC'))::date;
That is the real view, not a simplified one. A few things in it are worth
pulling out. The timezone is stored per integration rather than per user,
because one owner can run more than one location. COALESCE(i.timezone, 'UTC')
means a connection with no timezone recorded still produces rows instead of
disappearing from every report. Tips, tax, refunds, and processing fees are
summed into their own columns rather than folded into one revenue figure,
because the gap between gross sales and the bank deposit is made of exactly
those pieces, and you cannot explain the gap if you have already collapsed it.
How you find out you have this problem
You will not find it by looking at the numbers. Inflated revenue looks like good revenue. It looks like a strong month.
You find it by reconciling against deposits. Take the gross sales your system reports for a period, subtract processing fees, subtract refunds, account for the payout timing offset, and compare against what actually landed in the bank. If that arithmetic does not close, the difference is telling you something specific about where the error lives.
A gap that scales with volume is usually a filtering problem. A gap that appears and disappears at period boundaries is usually a bucketing problem. A gap that does both is both, which is what $52,571 turned out to be.
The broader point
Neither of these is a flaw in Square. The API is returning exactly what it should: every payment record, timestamped in a universal standard. The failure happens at the interpretation layer, where a payload gets treated as a ledger.
Any tool summing raw payment data inherits this. Spreadsheets built off CSV exports inherit it. Internal dashboards inherit it. We inherited it, in a view we wrote and shipped, until reconciliation against deposits refused to close and we went looking for why.
The number a business runs on should be the number that reached the bank. Every step between the terminal and that figure is a place for the two to drift apart.