Stripe Sync Engine Backfill

You are an expert in backfilling historical Stripe data using stripe-sync-engine. Your goal is to help users import their existing Stripe data into PostgreSQL.

Initial Assessment

Before proceeding, verify:

• Is stripe-sync-engine set up? (see setup skill)

• Are migrations completed? (see migrations skill)

• How much historical data do you need? (all time, last year, last 30 days?)

• How large is your Stripe account? (affects backfill strategy)

Basic Backfill

Method 1: Backfill Script (Recommended)

Create scripts/backfill-stripe.ts :

import { StripeSync } from "stripe-sync-engine";

const stripeSync = new StripeSync({ poolConfig: { connectionString: process.env.DATABASE_URL!, max: 10, }, stripeSecretKey: process.env.STRIPE_SECRET_KEY!, stripeWebhookSecret: process.env.STRIPE_WEBHOOK_SECRET!, schema: "stripe", autoExpandLists: true, backfillRelatedEntities: true, });

async function main() { const startDateArg = process.argv[2];

if (!startDateArg) { console.error("Usage: npm run stripe:backfill <start-date-unix-timestamp>"); console.error("Example: npm run stripe:backfill 1704067200"); process.exit(1); }

const timestamp = parseInt(startDateArg, 10); console.log(Backfilling Stripe data from ${new Date(timestamp * 1000).toISOString()}...);

await stripeSync.syncBackfill({ object: "all", created: { gte: timestamp }, });

console.log("Backfill completed successfully"); }

main().catch((error) => { console.error("Backfill failed:", error); process.exit(1); });

Add to package.json :

{ "scripts": { "stripe:backfill": "tsx scripts/backfill-stripe.ts" } }

Run:

Backfill from January 1, 2024 (Unix timestamp)

npm run stripe:backfill 1704067200

Get Unix timestamp for a date (macOS/Linux)

date -d "2024-01-01" +%s

Method 2: API Endpoint

Create app/api/sync/backfill/route.ts :

import { NextResponse } from "next/server"; import { stripeSync } from "@/lib/stripeSync";

export async function POST(request: Request) { const { object = "all", startDate } = await request.json();

try { const result = await stripeSync.syncBackfill({ object, created: startDate ? { gte: startDate } : undefined, });

return NextResponse.json({ status: "completed", result });

} catch (error) { const message = error instanceof Error ? error.message : "Unknown error"; return NextResponse.json({ error: message }, { status: 500 }); } }

Backfill Options

Object Types

The object parameter accepts:

Value Description

all

All supported object types

customer

Customer records

product

Product catalog

price

Price objects

plan

Legacy plan objects

subscription

Subscription records

invoice

Invoice records

charge

Charge records

payment_intent

Payment intents

payment_method

Payment methods

setup_intent

Setup intents

dispute

Dispute records

Date Filters

The created parameter supports Stripe's RangeQueryParam:

// All objects created after a date created: { gte: 1704067200 }

// Objects created before a date created: { lte: 1735689599 }

// Objects in a date range created: { gte: 1704067200, lte: 1735689599 }

// Exclusive comparisons created: { gt: 1704067200 } // strictly after created: { lt: 1735689599 } // strictly before

Backfilling Large Accounts (10,000+ Objects)

For large Stripe accounts, backfill in smaller chunks to avoid timeouts:

Day-by-Day Backfill

import { StripeSync } from "stripe-sync-engine";

const stripeSync = new StripeSync({ poolConfig: { connectionString: process.env.DATABASE_URL! }, stripeSecretKey: process.env.STRIPE_SECRET_KEY!, stripeWebhookSecret: process.env.STRIPE_WEBHOOK_SECRET!, });

async function backfillByDay(startDate: Date, endDate: Date) { const currentDate = new Date(startDate);

while (currentDate <= endDate) { const dayStart = Math.floor(currentDate.getTime() / 1000); const dayEnd = dayStart + 86400 - 1; // End of day

console.log(`Syncing ${currentDate.toISOString().split('T')[0]}...`);

await stripeSync.syncBackfill({
  object: "all",
  created: { gte: dayStart, lte: dayEnd },
});

console.log(`Completed ${currentDate.toISOString().split('T')[0]}`);

currentDate.setDate(currentDate.getDate() + 1);

} }

// Backfill all of 2024 backfillByDay(new Date("2024-01-01"), new Date("2024-12-31"));

Object-by-Object Backfill

const objects = [ "product", "price", "customer", "subscription", "invoice", "payment_intent", "charge", ];

for (const object of objects) { console.log(Backfilling ${object}s...); await stripeSync.syncBackfill({ object, created: { gte: 1704067200 }, }); console.log(Completed ${object}s); }

Syncing Single Entities

To sync or refresh a single Stripe object:

// Sync by Stripe ID (type is auto-detected from prefix) await stripeSync.syncSingleEntity("cus_1234567890"); await stripeSync.syncSingleEntity("prod_1234567890"); await stripeSync.syncSingleEntity("sub_1234567890"); await stripeSync.syncSingleEntity("in_1234567890"); await stripeSync.syncSingleEntity("pi_1234567890");

API Endpoint for Single Entity Sync

Create app/api/sync/entity/[id]/route.ts :

import { NextResponse } from "next/server"; import { stripeSync } from "@/lib/stripeSync";

export async function POST( request: Request, { params }: { params: { id: string } } ) { try { await stripeSync.syncSingleEntity(params.id); return NextResponse.json({ status: "synced", id: params.id }); } catch (error) { const message = error instanceof Error ? error.message : "Unknown error"; return NextResponse.json({ error: message }, { status: 500 }); } }

Revalidation on Sync

For critical objects, always fetch fresh data from Stripe API instead of trusting webhook payloads:

const stripeSync = new StripeSync({ // ... other config revalidateObjectsViaStripeApi: [ "customer", "subscription", "invoice", "payment_intent", ], });

Verifying Backfill Results

After backfill completes, verify data in your database:

-- Count synced objects SELECT 'customers' as type, COUNT() as count FROM stripe.customers UNION ALL SELECT 'products', COUNT() FROM stripe.products UNION ALL SELECT 'subscriptions', COUNT() FROM stripe.subscriptions UNION ALL SELECT 'invoices', COUNT() FROM stripe.invoices;

-- Check date range of synced data SELECT MIN(to_timestamp(created)) as earliest, MAX(to_timestamp(created)) as latest FROM stripe.customers;

Troubleshooting

Timeout Errors

• Backfill in smaller date ranges (daily or weekly)

• Backfill specific object types separately

• Increase function timeout if using serverless

Rate Limits

• stripe-sync-engine respects Stripe rate limits automatically

• For very large backfills, run during off-peak hours

Missing Related Data

• Enable backfillRelatedEntities: true in configuration

• This ensures foreign key relationships are maintained

Related Skills

• setup: Install and configure stripe-sync-engine

• migrations: Create database schema before backfill

• query: Query the backfilled data