Why We Sync Stripe to a Local Database

The Naive Approach

The simplest way to build a Stripe dashboard is to call the Stripe API whenever a user loads a page. Need to show a list of customers? stripe.customers.list(). Need subscription details? stripe.subscriptions.retrieve(). Need invoice history? stripe.invoices.list().

This works fine for a prototype. It falls apart in production.

Why Direct API Calls Don't Scale

Latency

Every Stripe API call takes 200-800ms. A dashboard page that needs customers, subscriptions, and recent charges makes 3+ API calls — that's 600ms to 2.4 seconds before the page can render. Users notice.

Rate Limits

Stripe's API has a rate limit of 100 requests per second in live mode. Sounds generous until you have 10 team members loading dashboard pages simultaneously, each triggering multiple API calls. You'll hit rate limits faster than you expect.

Pagination Pain

Stripe's list endpoints return a maximum of 100 objects per request. If you have 5,000 customers, loading the full list requires 50 sequential API calls. That's 10-40 seconds of waiting.

Cost

While Stripe doesn't charge per API call, excessive API usage can trigger reviews and, in extreme cases, throttling. It's also wasteful — you're re-fetching the same data that hasn't changed since the last page load.

The Sync Engine Approach

Agent Billy uses a sync engine (built on the excellent @supabase/stripe-sync-engine) that mirrors your Stripe data to a local PostgreSQL database.

How It Works

1. Initial Sync. When you connect your Stripe account, we pull all your customers, subscriptions, invoices, charges, products, and prices into PostgreSQL. This is a one-time operation.

1. Webhook Sync. After the initial pull, we listen for Stripe webhooks. When a charge succeeds, a subscription updates, or a customer is created, the webhook fires and we update the local database in real-time.

1. Periodic Reconciliation. Every 6 hours, we run a lightweight reconciliation pass to catch any events that webhooks might have missed (network blips, webhook endpoint downtime, etc.).

The Result

Dashboard queries hit PostgreSQL instead of Stripe:

-- Loads in < 50ms vs 800ms+ from Stripe API
SELECT * FROM stripe_customers
WHERE email ILIKE '%acme%'
ORDER BY created DESC
LIMIT 20;

-- MRR calculation in < 100ms
SELECT SUM(
  CASE
    WHEN interval = 'month' THEN amount
    WHEN interval = 'year' THEN amount / 12
  END
) AS mrr
FROM stripe_subscriptions
JOIN stripe_prices ON stripe_subscriptions.price_id = stripe_prices.id
WHERE status = 'active';

Data Freshness

"But is the data stale?" Almost never. Stripe webhooks fire within seconds of an event. Our sync engine processes them in under 500ms. So the local database is typically less than 2 seconds behind Stripe.

For the rare case where you need absolute real-time data (like checking a payment status during a support call), the dashboard has a "Refresh from Stripe" button that bypasses the local cache and queries the API directly.

What We Sync

The Performance Difference

In our benchmarks with a test account containing 10,000 customers, 15,000 subscriptions, and 50,000 invoices:

That's a 15-40x improvement in load times. The dashboard feels instant because it is instant.