Run one query tonight: every billing row in your database, joined to Stripe’s subscription list on the Stripe customer id, showing the rows where the two disagree. That query is how you keep Stripe subscriptions in sync with database records you can trust; a weekly job that reruns it, and a fix for each of the 6 ways they drift, do the rest.
What these controls are, together: how to keep Stripe subscriptions in sync with database records
Stripe subscriptions stay in sync with a database through two layers: webhooks that update each row as events arrive, and billing reconciliation, a check that compares the app’s billing records with Stripe’s and corrects every difference. Webhooks alone can lag, because Stripe can retry failed live webhook deliveries for up to three days and does not guarantee event order.
Billing reconciliation, as this page uses the term, is about who pays for what: the app’s answer on one side, the payment provider’s on the other, and a correction wherever they differ. Accounts payable, bank, invoice and budget reconciliation share the name and are not covered here. The check belongs with dunning, plan limits and webhook handling among the SaaS billing process best practices for an AI-built app.
To compare app records with Stripe you need two columns that a Stripe integration keeps on its side: the Stripe customer id, and either the subscription id or the plan and status the app stores for that customer. Those two are all the query below needs to find subscription mismatches between the two systems. The status values it compares are Stripe’s own: trialing, active, incomplete, incomplete_expired, past_due, canceled, unpaid and paused, as Stripe’s subscriptions overview lists them.
| Control | What it is | What it produces |
|---|---|---|
| One-time reconciliation | A query that joins the app’s billing rows to Stripe’s subscriptions, then a fix for each row where they disagree | The mismatch list, each row labeled with its drift class and then corrected |
| Scheduled reconciliation | The same query rerun on a schedule, with an alert attached | An output table of run date, mismatch count and rows, plus an alert when the count is above zero or a run is missing |
In the Production Hardening Sprint these are two deliverables: 5.6 compares the application’s billing records with the payment provider and corrects mismatches, and 5.9 runs recurring reconciliation and alerts the team to billing discrepancies.
What goes wrong without them
When the database plan does not match Stripe, sort the row into one of six drift classes; each leaves a different trace and has a different fix. The cause column below is my reading of how each class arises, except for classes 3 and 5, where the paragraph after the table quotes Stripe’s docs.
| Drift class | What the customer sees | What the founder sees | The cause |
|---|---|---|---|
| 1. Missed or failed webhook | Paid, and the app still says free | A payment in Stripe, a free row in the database | The event never reached the handler, or the handler failed on it |
| 2. Out-of-order event | A plan that jumps back to an older one | The database holds a state Stripe has since replaced | An older event processed after a newer one |
| 3. Change made in the Stripe Dashboard | Access after a cancellation, or the old plan after a change | The Dashboard shows the change; the database does not | Stripe sends an event for the change, and the handler does not listen for that event type |
| 4. Failed renewal never handled | Paid access after the card fails | Stripe shows past_due or unpaid; the database shows active | The handler ignores the payment failure and the status change that follows |
| 5. Customer deleted in Stripe | Nothing at first, then errors at the next billing action | The app holds a customer id whose subscriptions Stripe has canceled | Deleting a customer in Stripe also immediately cancels its active subscriptions; the app row still points at it |
| 6. One-way plan flag | Pro access long after leaving | A plan column written at checkout and never touched again | The handler writes the plan once and has no path that changes it |
Each class has an owner elsewhere. Class 1 has its own checks, run in order, in the guide to a Stripe webhook not updating the database. The second is the ordering gap named at the top of this page, and the states an event can carry are mapped in the subscription lifecycle in Stripe. Class 3 exists because Stripe triggers events every time a subscription is created or changed, Dashboard changes included, while a handler only hears the event types it subscribes to. For class 4, the dunning problem, start with how to handle failed subscription payments. Class 5 comes from Stripe’s own rule that deleting a customer “also immediately cancels any active subscriptions on the customer.” And class 6 is the case of a canceled Stripe subscription that still has access.
A Dashboard cancellation shows how class 3 happens. A customer asks support to cancel, and someone cancels the subscription in the Stripe Dashboard instead of in the app. The app’s webhook handler listens only for the checkout event, so nothing writes the cancellation back; the app keeps the customer on the paid plan and counts them as paying. Webhooks cover the events a handler listens for; a reconciliation query sees the result of every change, whoever made it. The lesson I take from it: compare the outcome, not the event log.
Undetected drift creates access and revenue-reporting inconsistencies, and the harm runs both ways. A paying customer marked as free files a support ticket and may leave. A free customer marked paid gets through the limits you set up for enforcing plan limits on the backend, and your revenue figure counts money that never arrived. A subscription status out of sync raises no error in either direction, which is how billing drift gets discovered months later: a customer writes in, or a revenue total stops matching Stripe’s.
How to set them up
Reconciliation is set up in three pieces: a query that joins the users table to Stripe’s subscriptions on the customer id and lists every disagreement, a scheduled job that reruns it, and an alert that posts the mismatch count and the rows when the count is above zero.
The query below is my own, built on Stripe’s documentation, and the schedules come from Supabase’s and GitHub’s; they are a method to follow, not a report of a job I ran.
The reconciliation query
The reconciliation query is a full outer join between the app’s billing rows and Stripe’s subscriptions on the Stripe customer id, one current subscription per customer. It yields four sets: paid in the app and not in Stripe, in Stripe and not paid in the app, both present with a different plan or status, and customers in one system only.
The first run takes five steps.
- 01 Pull Stripe's subscriptions with the list-subscriptions endpoint and the status parameter set to all, or read the stripe schema if a sync engine is installed.
- 02 Load them into a table next to the app's billing rows: customer id, subscription id, status, price id and created time.
- 03 Run the full outer join on the Stripe customer id, keeping one Stripe row per customer: the subscription the app stores, or else the customer's newest one.
- 04 Label each row that comes back with its drift class from the table above.
- 05 Fix each row: Stripe wins for status and plan, the app wins for the user's identity. Retrieve the current Stripe object and run it through the same sync function the webhook handler uses; never edit the row directly.
The status parameter matters in step 1. Passing all returns subscriptions of all statuses, while a call with no status returns only the subscriptions that have not been canceled, so a cancellation your app missed would never appear on Stripe’s side of the join. The endpoint pages its results: limit runs from 1 to 100 with a default of 10, and starting_after is the cursor for the next page, as Stripe’s list subscriptions reference describes.
with s as ( -- Stripe's side, loaded with status=all
select distinct on (customer) customer, id, status, price_id
from stripe_subs
order by customer, created desc -- newest subscription per customer
), a as ( -- the app's side: anyone billed or marked paid
select user_id, stripe_customer_id, status, price_id from app_billing
where stripe_customer_id is not null or status in ('active', 'trialing')
)
select a.user_id, coalesce(a.stripe_customer_id, s.customer) as customer,
a.status as app_status, s.status as stripe_status, a.price_id, s.price_id
from a full outer join s on s.customer = a.stripe_customer_id
where case when a.user_id is null then s.status <> 'canceled' -- Stripe only
when s.customer is null then a.status is not null -- app only
else a.status is distinct from s.status or a.price_id is distinct from s.price_id end;The skeleton assumes the app stores Stripe’s own status string; an app that keeps only a plan flag maps it to a status first. Keeping one Stripe row per customer matters because a canceled subscription is a terminal state, so a customer who leaves and later comes back gets a new subscription beside the old canceled one. Join on the subscription id where the app stores it, and on the newest subscription otherwise, so old history is not reported as drift and a correct system can return zero rows. A first run that returns rows is the check doing its job.
Step 5 is my rule, and the cancellation article linked above handles stale state the same way: retrieve the current state and run the same sync function. Stripe’s own webhook guidance says you can retrieve the API resource “to access the latest and up-to-date object definition” instead of using the copy inside the event, which is the object as it was at the time of the event. Editing the row directly fixes one customer and leaves the handler that caused it untouched.
The Supabase/Stripe sync engine and other mirrors: what they do and do not do
A sync engine such as the Stripe Sync Engine syncs Stripe data into a stripe schema in Postgres through webhooks and scheduled backfills, which turns the reconciliation query into a plain join. The copy is Stripe’s side only, so the comparison against the app’s own users table is still the step you add.
The Stripe Sync Engine is installed from the Integrations section of the Supabase dashboard, and syncing starts once the install completes. Supabase announced on April 14, 2026, in its blog post on the transfer, that the repository moves from supabase/stripe-sync-engine to stripe/sync-engine; the stripe/sync-engine README now points to the og branch for the original Supabase version. Anyone self-hosting it should read the README’s warning that the service “lacks tight access controls and should only be deployed internally.”
The Stripe wrapper is the other Supabase route. It is a foreign data wrapper: “The Stripe Wrapper allows you to read data from Stripe within your Postgres database,” and queries against its tables make calls to Stripe’s API, which is why Supabase’s Stripe wrapper docs advise to “always limit records to reduce API calls to Stripe.” Its subscriptions table exposes id, customer and the billing period, with other subscription details in an attrs jsonb column.
Either one changes how you load Stripe’s side: step 2 becomes a join against stripe.subscriptions instead of a loaded table. Neither changes the comparison, because both give you Stripe’s side only. A mirror that has fallen behind also makes both sides agree on stale data, so my rule is that the scheduled run checks a small sample of customers against the live API as well.
The weekly job and the alert
The scheduled reconciliation reruns the same query on a fixed schedule, writes the mismatch count and the rows to a table with the run date, and sends two alerts: one when the count is above zero, and one when the job did not run at all.
Three schedulers fit a small app. Your host’s cron works if it has one. GitHub Actions’ schedule trigger takes POSIX cron syntax, in UTC unless you set a timezone, and runs on the latest commit on the default branch; GitHub warns that it “can be delayed during periods of high loads” and that “some queued jobs may be dropped,” and in a public repository scheduled workflows are disabled after 60 days with no repository activity. Supabase Cron runs SQL snippets or database functions, or makes an HTTP request such as invoking an Edge Function, and Supabase says each job “should run no more than 10 minutes,” so the Stripe pull runs through a mirror or the wrapper, or inside the Edge Function the job calls. On frequency, my working rule is weekly for an app with up to a few thousand customers and daily above that.
Write each run’s date, mismatch count and rows to an output table, so the trend is visible and a sudden jump stands out. The job should alert the team on billing mismatches in the channel the founder already reads whenever the count is above zero, with the rows attached. When Stripe cannot be reached, the job reports that instead of fixing anything, and it grants no access solely on that basis; the exact failure policy depends on how critical the product is and what your customer agreement promises.
The missed-run alert lives outside the job, since a job that did not run cannot report itself. My rule is that a second schedule, or an outside monitor the job pings on each run, checks the output table’s newest run date and alerts when it is older than one cycle.
The billing monitoring checklist, then, has six items.
- 01 The reconciliation query, saved in the repo and run against both sides.
- 02 A schedule: host cron, GitHub Actions or Supabase Cron, weekly or daily.
- 03 An output table with run date, mismatch count and the mismatched rows.
- 04 A count alert, posted when the count is above zero, with the rows attached.
- 05 A missed-run alert, raised from outside the job when the newest run is older than one cycle.
- 06 A named owner who reads both alerts and fixes the cause, not only the row.
Add one line to the same checklist for revenue reporting accuracy in the sense used here: the app’s paid count against Stripe’s, compared on every run. Accounting standards and revenue recognition are a different subject. Metering drift on usage-based plans is its own check, covered under the meter, the price and the invoice are never reconciled.
How to verify each one
Reconciliation is verified by seeding drift in test mode: flip one user’s plan in the database, cancel one subscription immediately in Stripe with its webhook off, then confirm the query lists both, the fix clears both, the scheduled run alerts with the rows, and a skipped run raises its own alert.
Run the checks in a Stripe sandbox, which Stripe describes as an isolated test environment whose transactions “don’t move funds,” against a copy of the app connected to it. Stripe’s testing docs list the test cards for creating the subscriptions.
- 01 Change one test user's plan or status in the database without touching Stripe. The query lists that user. Evidence: the output row with its run date.
- 02 Disable the sandbox webhook endpoint, then cancel a second test user's subscription in the Stripe Dashboard and choose to end it immediately. The query lists it the other way round: active in the app, canceled in Stripe. Evidence: the row and the subscription id. Leave the endpoint off until check 3 has run.
- 03 Run the fix. Both rows are gone, and each app row matches the Stripe object the fix retrieved. Evidence: the before and after rows.
- 04 Seed one mismatch again as in check 1, then trigger the scheduled job by hand. The alert arrives with the row and the output table holds the run. Evidence: the alert and the table row.
- 05 Disable the job for one cycle. The missed-run alert, which runs outside the job, fires. Evidence: that alert.
Check 2 needs the immediate option. The Dashboard also offers to end the subscription at the end of the period or on a custom day, and a period-end cancellation lets the subscription run to the end of the period already paid for, so the query would rightly list nothing yet. Stripe’s docs say that if the endpoint is disabled when a retry comes due, Stripe prevents future retries of that event, but an endpoint disabled and re-enabled before the retry still sees future retry attempts; that is why the endpoint stays off until the fix has run, and you re-enable it after check 3. Check 5 takes one full cycle, a week on a weekly schedule; it is the one check that cannot be hurried, because it tests a run that never happens. For check 4, a GitHub Actions workflow needs a workflow_dispatch trigger beside schedule to run on demand; on Supabase Cron, run the job’s SQL or function directly.
In the Production Hardening Sprint, these checks are how the two deliverables close. Deliverable 5.6 is verified this way: seed mismatched records and verify they are identified and reconciled correctly. Deliverable 5.9 is verified this way: execute the scheduled job against test mismatches and verify its alert and output.
Seeding the drift on purpose matters because a check that has never caught anything proves nothing. The apps’ own tests would rarely have caught it either: in my June and July 2026 audits, only 1 of the 21 third-party apps, a healthcare FHIR hub, was credited with a real test suite, and even it skipped sign-up, login and the payment webhook. Those 21 were a selected set, not a random sample, so the count is no rate for AI-built apps in general. Testing the handler that let the drift in is its own job, how to test webhooks locally and in staging, and so is making it safe to receive the same event twice: how to make a webhook handler idempotent.
Where the sprint does this
In the Production Hardening Sprint, the one-time check and the scheduled run are deliverables 5.6 and 5.9, closed by the seeded checks in the section above. The result goes into the production readiness report, deliverable 13.1, which is verified this way: account for all 123 IDs; keep failures visible until resolved and explain genuine non-applicable items. Hosting, paid tools, and API usage remain in your accounts. Every deliverable is listed in the sprint scope, all 123 items.
Common questions about billing reconciliation
How do you handle data mismatch?
For billing data, decide which system owns each field and correct the row through code: Stripe owns subscription status and plan, and the app owns the user’s identity. Retrieve the current subscription from Stripe and write it through the function your webhook handler already uses, so a correction leaves the same trace an event would; that is my rule, and Stripe’s docs say the API returns the latest object definition.
How to resolve billing discrepancy?
Name the drift class first, fix its cause, then correct the row and rerun the query to confirm it no longer appears. Fixing only the row brings the same discrepancy back on the next renewal or Dashboard change, because the handler that missed it has not changed.
How to verify subscription status?
Retrieve the subscription from Stripe by its id and compare its status with the value in your database; they should be the same string. Stripe uses eight statuses, from trialing and active to past_due, unpaid, canceled and paused, and it says to revoke access when a subscription changes to canceled or unpaid.
How can I see all my Stripe subscriptions?
Call the list-subscriptions endpoint with status=all; leave the status out and canceled subscriptions are missing from the result. Page through the results with the starting_after cursor, up to 100 per page. In the Dashboard, the Subscriptions page is where Stripe’s docs point you to find a subscription and act on it.
If you have a working app built with these tools and need it ready for real customers, this is what we do.
Built it with AI. Now it has to hold up for real customers.
The Production Hardening Sprint takes the app you already have and builds the production foundation underneath it. Authentication and access rules, payments that stay consistent, error handling, monitoring, backups, automated tests and a documented handover. Our engineers work inside your existing codebase for ten working days. All 123 deliverables are included, and you get the evidence for each one.
See the Production Hardening Sprint → 
$2,500 fixed price · 10 working days · One codebase