How to reconcile Stripe subscriptions with your database

September 18, 2026

Every other post on this blog describes a way your billing and your app can disagree. This one is the practical counterpart: a script that goes and checks. It lists every subscription in Stripe, compares it to who your application currently lets in, and prints the mismatches. It takes about ten minutes to adapt, and the first run usually finds something.

What it checks

Three questions, in the order they tend to cost you money:

• Access without payment. Your database says the user has access, but Stripe has no active or trialing subscription for that customer. This is the ghost customer bucket.
• Payment without access. Stripe shows an active subscription, but your app has the user locked out. This is the bucket that produces angry support tickets, as in the paywall after paying case.
• Paying customers your database has never heard of. A Stripe customer ID with a live subscription that doesn’t appear in your users table at all, usually a duplicate customer object.

Before you run it

• Create a restricted key in the Stripe dashboard with read-only access to Subscriptions. Never run an audit script with your full secret key.
• Adjust the table and column names in the query (users, stripe_customer_id, has_access) to match your schema. If access is derived from a plan or role column instead of a boolean, compute the boolean in the query.
• Install the dependencies: npm i stripe pg. The script uses top-level await, so save it as an .mjs file or set "type": "module" in your package.json.

The script

import Stripe from 'stripe'
import pg from 'pg'

// Use a restricted key with read-only access to Subscriptions.
const stripe = new Stripe(process.env.STRIPE_READONLY_KEY)
const db = new pg.Client({ connectionString: process.env.DATABASE_URL })

const PAYING = new Set(['active', 'trialing'])
const GRACE = new Set(['past_due'])

// 1. Stripe side: which customers should have access right now?
const paying = new Set()
const grace = new Set()
for await (const sub of stripe.subscriptions.list({ status: 'all', limit: 100 })) {
  if (PAYING.has(sub.status)) paying.add(sub.customer)
  else if (GRACE.has(sub.status)) grace.add(sub.customer)
}

// 2. App side: who does your app actually let in?
await db.connect()
const { rows } = await db.query(
  'select id, email, stripe_customer_id, has_access from users where stripe_customer_id is not null'
)
await db.end()

const knownIds = new Set(rows.map((r) => r.stripe_customer_id))

// 3. Diff.
const accessWithoutPayment = rows.filter(
  (r) => r.has_access && !paying.has(r.stripe_customer_id) && !grace.has(r.stripe_customer_id)
)
const pastDueWithAccess = rows.filter((r) => r.has_access && grace.has(r.stripe_customer_id))
const paymentWithoutAccess = rows.filter((r) => !r.has_access && paying.has(r.stripe_customer_id))
const unknownPayers = [...paying].filter((id) => !knownIds.has(id))

console.log('Access without payment:', accessWithoutPayment.map((r) => r.email))
console.log('Past due, still has access:', pastDueWithAccess.map((r) => r.email))
console.log('Paying but locked out:', paymentWithoutAccess.map((r) => r.email))
console.log('Paying customers your DB has never heard of:', unknownPayers)

Run it with STRIPE_READONLY_KEY=rk_live_... DATABASE_URL=... node audit.mjs. Start with your live-mode key: test-mode data won’t tell you anything about real customers.

How to read the output

Access without payment

Check each one by hand before revoking anything. Some will be deliberate (a comped account, a partner, a friend), and some will be the bug. Anyone whose subscription ended weeks ago and still has access is exactly what you were looking for.

Past due, still has access

Not necessarily wrong: a grace period during dunning is a legitimate product decision. The point of the separate list is that you now know how many people are in that state and for how long, which is the number your MRR dashboard is silently counting as revenue.

Paying but locked out

Fix these first. Each row is a customer who paid you and can’t use the product. Then work out why the grant never happened, because the cause is usually a webhook problem that will hit the next customer too.

Making it a habit

A one-off audit finds the backlog. The more valuable version runs on a schedule and tells you when something new appears: a cron job, a diff against yesterday’s output, and an alert to wherever your team actually looks. That’s the part that tends to never get built, because the script works once and then the next task arrives.

It’s the reason we built Venwai: it does this comparison every day with a read-only Stripe key and one endpoint on your side, and alerts you in Slack, Discord, Telegram, or email when the two disagree, so you don’t have to remember to run anything. The script above is a perfectly good place to start, and Venwai is what you use once you’ve run it twice and decided you don’t want to keep doing it manually.

Free while it’s in beta, no card required: venwai.com.