What I Learned Syncing PayPal Transaction Data

The 31-Day Window Limit

PayPal's Transaction Search API (/v1/reporting/transactions) won't accept a date range wider than 31 days. For a user with years of transaction history, that means splitting the request into chunks:

const WINDOW_DAYS = 31;

export function getDateWindows(from: Date, to: Date): Array<[Date, Date]> {
  const windows: Array<[Date, Date]> = [];
  let windowStart = from;

  while (isBefore(windowStart, to)) {
    const windowEnd = addDays(windowStart, WINDOW_DAYS);
    windows.push([windowStart, isBefore(windowEnd, to) ? windowEnd : to]);
    windowStart = windowEnd;
  }

  return windows;
}

Initial sync goes back 3 years — that's about 36 windows. Fine. But within each window, one API call isn't enough.

Two Passes Per Window

The Transaction Search API has a parameter called balance_affecting_records_only. It accepts Y or N, and you need both.

Pass N (balance_affecting_records_only=N) returns everything — all transaction types including non-balance-affecting records. It gives you the full transaction graph: amounts, counterparty details, item descriptions, status codes. But it does not include ending_balance or available_balance.

Pass Y (balance_affecting_records_only=Y) returns only transactions that actually moved money. It's a subset of N — but it's the only pass that includes the balance fields.

So neither pass alone is complete. You need N for the full transaction details, and Y for the balance data. The fix is to run both in parallel per window and merge the results:

const [nResult, yResult] = await Promise.all([
  fetchAllPages(baseUrl, token, windowStart, windowEnd, 'N'),
  fetchAllPages(baseUrl, token, windowStart, windowEnd, 'Y'),
]);

// Build a balance lookup from Y — it's the only reliable source of these fields
const balanceMap = new Map<string, BalanceFields>();
for (const txn of yResult.transactions) {
  const ti = txn.transaction_info;
  if (ti.ending_balance && ti.available_balance) {
    balanceMap.set(ti.transaction_id, {
      endingBalance: ti.ending_balance.value,
      endingBalanceCurrency: ti.ending_balance.currency_code,
      availableBalance: ti.available_balance.value,
      availableBalanceCurrency: ti.available_balance.currency_code,
    });
  }
}

Then for each transaction from N, look up any balance fields from the map and upsert both together.

The Duplicate Transaction ID Problem

The N pass can return the same transactionId twice. This happens with authorizations: PayPal first records the transaction with status P (pending), then again with status S (settled) once the payment clears. Both records have the same ID and identical financial data — just different status codes.

If you try to upsert both, the second write overwrites the first. That's actually fine here since the financial data is the same. But if you're processing them in order before the upsert, you want to be deliberate about it. The simplest approach: deduplicate by keeping the last occurrence, which has the final settled status:

const deduped = new Map<string, PaypalTransactionDetail>();
for (const txn of nResult.transactions) {
  deduped.set(txn.transaction_info.transaction_id, txn);
}

Iterating over deduped.values() then gives you one record per transaction, and the balance lookup works cleanly against unique IDs.

Child Transactions Are Not Real Transactions

Once the data is stored, the next surprise arrives at query time.

PayPal records internal accounting entries alongside real transactions. A single payment from a customer might generate two rows: the actual payment (with referenceIdType of something like ODR) and a child entry (with referenceIdType = 'TXN') that represents the same money moving through PayPal's internal ledger. Both show up in the API response. Both land in your database.

If you forget to filter out the children, every metric is wrong — transaction counts are doubled, revenue totals are inflated, customer counts include phantom entries. The filter needs to go on every analytical query:

const PARENT_ONLY = {
  OR: [
    { referenceIdType: { not: 'TXN' } },
    { referenceIdType: null },
  ],
};

There's one exception: balance history. ending_balance is a running total across all rows including children. If you filter children out of a balance chart, you lose data points and the curve breaks. For balance queries, filter instead on endingBalance: { not: null } — this keeps only the rows that actually carry a balance value, which is a safer indicator than the referenceIdType alone.

The Full Picture

Here's what a single sync window actually involves:

1. Fire two API requests in parallel: one for N (all transactions), one for Y (balance-affecting only)
2. Build a balance lookup map from Y results
3. Deduplicate the N results by transaction ID
4. For each unique transaction, merge in balance fields from the map and upsert to the database
5. At query time, always spread PARENT_ONLY into your where clause — except for balance history queries

None of this is complicated once you know it. The PayPal docs document each piece; they just don't explain why you need both passes, or that child transactions exist and will silently inflate your numbers. Hopefully this saves you the debugging session.