Automating Stripe Reconciliation: Building a Daily Revenue Sync Pipeline

Automating Stripe Reconciliation

A note from the founder. Spending time each week reconciling Stripe data against your internal records? I'm looking for a small group of early users to try Runhooks and share honest feedback. Early adopters get upgraded plans for free.

Stripe processes your payments in real time, but your accounting doesn't run in real time. Your finance team needs structured revenue data in your internal database. Your BI dashboard needs MRR, churn, and cohort metrics that Stripe's dashboard doesn't break down the way you need. And your bookkeeping tool needs clean records that tie back to Stripe's numbers — without someone manually exporting CSVs and pasting them into a spreadsheet.

The solution is a scheduled sync: a job that pulls yesterday's charges, invoices, and payouts from Stripe's API and writes them into your database. A task that should run every morning and never require human intervention.

Why Manual Stripe Exports Don't Scale

Every SaaS founder starts the same way. You log into Stripe's dashboard, filter by date range, export a CSV, and drop it into a spreadsheet or accounting tool. At 30 customers with a single pricing tier, this is manageable.

At 500 customers across multiple plans, currencies, and billing intervals, it breaks:

Multi-currency complexity — if you accept payments in EUR, GBP, and USD, each charge is recorded in the customer's currency. Stripe converts to your settlement currency at payout time, but the dashboard export shows the original amounts. Reconciling those against your bank requires tracking exchange rates per charge.
Proration and credits — Stripe handles subscription upgrades, downgrades, and mid-cycle changes with prorated invoice line items. A manual export gives you the net total, but your revenue recognition system may need the individual line items to attribute revenue correctly.
Refunds and disputes — a charge exported last Tuesday might be partially refunded on Thursday. Your export doesn't know that. You find out when the monthly totals don't match.
Human error — someone exports the wrong date range, forgets to run the export during vacation, or imports a duplicate batch. The mismatch surfaces weeks later during reconciliation.

Stripe gives you excellent payment infrastructure. It doesn't give you an automated pipeline into your own systems.

What Stripe's API Gives You

Stripe's API is built around several core objects that together give you a complete picture of your revenue:

Charges — individual payment attempts, including amount, currency, status, and the associated customer. This is your raw transaction log.
Invoices — subscription billing records with line items, proration details, discounts, and tax. This is what you need for revenue recognition.
Balance Transactions — the ledger that ties everything together: charges, refunds, fees, and payouts, all with amounts converted to your settlement currency. This is what reconciles against your bank account.
Payouts — the actual transfers from Stripe to your bank, linking back to the balance transactions they contain.

For daily reconciliation, the Balance Transactions API is typically the most useful starting point — it gives you a single, unified view of money movement with Stripe's fees already deducted and currency conversion already applied. See Stripe's API reference for the full list of fields and query parameters on each endpoint.

Building the Sync Endpoint

The pattern is the same as any scheduled HTTP task: expose an endpoint in your backend that performs the sync, and call it on a schedule.

// POST /api/billing/sync-stripe
async function handleStripeSync(req, res) {
  // 1. Calculate yesterday's date range as Unix timestamps

  // 2. Fetch balance transactions from Stripe's List Balance Transactions API
  //    Filter by created date range, auto-paginate through all results
  //    See: https://docs.stripe.com/api/balance-transactions/list

  // 3. Upsert each transaction into your database
  //    Store: transaction ID, type (charge/refund/payout/fee), amount,
  //    currency, net amount, fee, source ID, and created timestamp
  //    Use upsert (INSERT ... ON CONFLICT UPDATE) so re-runs are safe

  // 4. Return a summary: { synced: count, date: "YYYY-MM-DD" }
}

The important design decisions:

Auto-pagination — Stripe's list endpoints return up to 100 objects per page. Use the starting_after parameter to paginate through all results. Stripe's official libraries provide auto-pagination helpers that handle this for you.
Upsert, not insert — use an upsert so that re-running the sync for the same day is safe. If a balance transaction was updated between runs, the record gets refreshed rather than duplicated.
Use net amounts — Balance Transactions include the net field: the amount after Stripe's fees. This is the number that matches your bank deposit and the one your accounting system cares about.

Handling Refunds and Disputes

A daily sync that only pulls successful charges misses refunds, disputes, and dispute reversals — events that happen days or weeks after the original payment.

Balance Transactions handle this naturally. Refunds and disputes appear as separate balance transaction entries with their own types (refund, adjustment, stripe_fee). If you sync all balance transactions created yesterday, you automatically capture:

Full and partial refunds issued yesterday, regardless of when the original charge was made
Dispute (chargeback) debits and any associated fees
Dispute reversals when you win a chargeback

async function handleStripeSync(req, res) {
  // Fetch ALL balance transaction types created yesterday
  // Refunds, disputes, and adjustments appear as their own entries
  // No second pass needed — one query captures everything
  // Upsert all records into your database
}

Because every money movement in Stripe generates a balance transaction, a single daily query captures the full picture. For more details on how Stripe models these events, see their balance transaction types documentation.

Why This Needs a Reliable Scheduler

A billing sync that silently fails is worse than no sync at all. Your finance team starts trusting the internal data, makes decisions based on it, and doesn't realize it's three days stale until the numbers don't add up.

A cron job on your server can trigger the endpoint, but it brings familiar problems:

No failure notification — if Stripe's API returns a rate limit error or your database connection drops, cron exits and moves on. You won't know until someone checks.
No retry logic — a transient 429 resolves itself in seconds, but cron waits 24 hours to try again. That's a full day of missing data.
No execution history — there's no record of how many transactions were synced, how long the job took, or whether sync duration is trending upward — a signal that transaction volume is growing and your sync might need tuning.
Infrastructure coupling — the cron schedule is tied to a specific server. A deployment, migration, or auto-scaling event can silently delete it.

For a job that feeds your accounting and BI systems, you need visibility into every run and automatic recovery from transient failures.

Scheduling With Runhooks

Runhooks calls your sync endpoint on a schedule with built-in retries, logging, and alerting. Setup:

Create a job — "Daily Stripe Reconciliation Sync"
Set the URL — https://api.yourapp.com/api/billing/sync-stripe
Set the schedule — 0 7 * * * (daily at 7:00 AM)
Set the timezone — your business's primary timezone (e.g. America/New_York, Europe/London)
Add an auth header — Authorization: Bearer <your-sync-secret>
Enable retries — 3 attempts with exponential backoff

Why 7 AM? Stripe processes payouts overnight. Running the sync in the morning ensures yesterday's balance transactions — including end-of-day payouts — are fully available before your finance team starts their day.

What this gives you:

Execution logs — every sync run is recorded with HTTP status, response body (including synced count), and duration. You can see that Monday synced 312 balance transactions in 4.1 seconds and Tuesday synced 287 in 3.6 seconds.
Automatic retries — if Stripe returns a 429 or your database is temporarily unreachable, Runhooks retries within seconds instead of waiting until tomorrow.
Failure alerts — if the sync fails after all retries, you get an email or webhook notification. Your finance team doesn't discover stale data during the monthly close.
Timezone-aware scheduling — set the job to run at 7 AM in America/New_York and it stays at 7 AM through DST transitions. Yesterday's transactions in your timezone are always captured.
No infrastructure coupling — the schedule lives in Runhooks, not on your application server. Deployments, container restarts, and scaling events don't affect it.

Webhooks vs. Scheduled Sync: Why Not Both?

Stripe's webhooks push events to your server in near real-time. If you already handle charge.succeeded, invoice.paid, or charge.refunded events, you might wonder why a scheduled sync is necessary.

Webhooks are great for triggering immediate actions — provisioning access, sending receipts, updating subscription status. But they're unreliable as your sole data pipeline:

Missed events — if your server is down during a webhook delivery and the retries exhaust, that event is lost. Stripe retries for up to 3 days, but extended outages or deployment gaps can cause misses.
Ordering issues — webhooks can arrive out of order. A refund event might be processed before the original charge event if your webhook handler was slow or restarting.
Incomplete picture — webhooks fire for individual events, not for aggregate views. Reconstructing a daily revenue summary from individual webhook payloads requires maintaining your own running totals.

The scheduled sync acts as a daily reconciliation pass that catches anything webhooks missed and provides a consistent, complete snapshot. Run both: webhooks for real-time reactions, scheduled syncs for data integrity.

Extending the Pattern

Once the daily balance transaction sync is running, the same approach works for other Stripe data your internal systems need:

Invoice sync — pull the Invoices API to get line-item detail for revenue recognition and subscription analytics
Subscription sync — pull the Subscriptions API to track active, past-due, and cancelled subscriptions for churn analysis
Customer sync — pull the Customers API to keep your CRM or user database aligned with Stripe's customer records
Payout reconciliation — pull the Payouts API and cross-reference with balance transactions to verify bank deposits

Each is a separate endpoint in your backend and a separate job in Runhooks — with its own schedule, retry policy, and alerting.

Get Started

Stripe handles real-time payment processing. Your internal systems need that data structured, reconciled, and available on a predictable schedule:

Build a sync endpoint that pulls from Stripe's Balance Transactions API and upserts into your database
Try Runhooks and schedule it to run daily in your business timezone
Get execution logs, retries, and alerts — so you know when a sync fails before your finance team does

Preview your schedule with the cron expression visualizer, and compare plans when you need more jobs or longer log retention.