Usage-based billing with Stripe: a practical data model

What you are really building (and why it breaks)

Usage-based billing sounds simple: measure what a customer used, multiply by a price, and charge them at the end of the period. In practice, you’re building a small accounting system. It has to stay correct even when data arrives late, arrives twice, or never arrives at all.

Most failures don’t happen in checkout or the dashboard. They happen in the metering data model. If you can’t answer, confidently, “Which usage events were counted for this invoice, and why?”, you’ll eventually overcharge, undercharge, or lose trust.

Usage billing usually breaks in a few predictable ways: events go missing after an outage, retries create duplicates, late arrivals show up after totals were computed, or different systems disagree and you can’t reconcile the difference.

Stripe is excellent at pricing, invoices, taxes, and collecting money. But Stripe doesn’t know your product’s raw usage unless you send it. That forces a source-of-truth decision: is Stripe the ledger, or is your database the ledger that Stripe reflects?

For most teams, the safest split is:

• Your database is the source of truth for raw usage events and their lifecycle.
• Stripe is the source of truth for what was actually invoiced and paid.

Example: you track “API calls.” Each call generates a usage event with a stable unique key. At invoice time, you total only eligible events that haven’t been billed yet, then create or update the Stripe invoice item. If ingestion retries or a webhook arrives twice, idempotency rules make the duplicate harmless.

Decisions to make before you design tables

Before you create tables, lock down the definitions that decide whether billing stays explainable later. Most “mysterious invoice bugs” come from unclear rules, not bad SQL.

Start with the unit you charge for. Choose something easy to measure and hard to argue about. “API calls” gets tricky with retries, batch requests, and failures. “Minutes” gets tricky with overlaps. “GB” needs a clear base (GB vs GiB) and a clear measurement method (average vs peak).

Next, define boundaries. Your system needs to know exactly which window an event belongs to. Is usage counted per hour, per day, per billing period, or per customer action? If a customer upgrades mid-month, do you split the window or apply one price to the whole month? These choices drive how you group events and how you explain totals.

Also decide what owns which facts. A common pattern with Stripe is: your app owns raw events and derived totals, while Stripe owns invoices and payment status. That approach works best when you don’t silently edit history. You record corrections as new entries and keep the original record.

A short set of non-negotiables helps keep your schema honest:

• Traceability: every billed unit can be tied back to stored events.
• Auditability: you can answer “why was this charged?” months later.
• Reversibility: mistakes are fixed with explicit adjustments.
• Idempotency: the same input can’t be counted twice.
• Clear ownership: one system owns each fact (usage, pricing, invoicing).

Example: if you bill for “messages sent,” decide whether retries count, whether failed deliveries count, and which timestamp wins (client time vs server time). Write it down, then encode it into event fields and validation, not someone’s memory.

A simple data model for usage events

Usage-based billing is easiest when you treat usage like accounting: raw facts are append-only, and totals are derived. That single choice prevents most disputes because you can always explain where a number came from.

A practical starting point uses five core tables (names can vary):

• customer: internal customer id, Stripe customer id, status, basic metadata.
• subscription: internal subscription id, Stripe subscription id, expected plan/prices, start/end timestamps.
• meter: what you measure (API calls, seats, storage GB-hours). Include a stable meter key, unit, and how it aggregates (sum, max, unique).
• usage_event: one row per measured action. Store customer_id, subscription_id (if known), meter_id, quantity, occurred_at (when it happened), received_at (when you ingested it), source (app, batch import, partner), and a stable external key for dedupe.
• usage_aggregate: derived totals, usually by customer + meter + time bucket (day or hour) and billing period. Store summed quantity plus a version or last_event_received_at to support recalculation.

Keep usage_event immutable. If you later discover an error, write a compensating event (for example, -3 seats for a cancellation) instead of editing history.

Store raw events for audits and disputes. If you can’t store them forever, keep them at least as long as your billing lookback window plus your refund/dispute window.

Keep derived totals separate. Aggregates are fast for invoices and dashboards, but they’re disposable. You should be able to rebuild usage_aggregate from usage_event at any time, including after a backfill.

Idempotency and event lifecycle states

Usage data is noisy. Clients retry requests, queues deliver duplicates, and Stripe webhooks can arrive out of order. If your database can’t prove “this usage event was already counted,” you will eventually bill twice.

Give every usage event a stable, deterministic event_id and enforce uniqueness on it. Don’t rely on an auto-increment id as the only identifier. A good event_id is derived from the business action, such as customer_id + meter + source_record_id (or customer_id + meter + timestamp_bucket + sequence). If the same action is sent again, it produces the same event_id, and the insert becomes a safe no-op.

Idempotency must cover every ingest path, not just your public API. SDK calls, batch imports, worker jobs, and webhook processors all get retried. Use one rule: if the input could be retried, it needs an idempotency key stored in your database and checked before totals change.

A simple lifecycle state model makes retries safe and support easier. Keep it explicit, and store a reason when something fails:

• received: stored, not yet checked
• validated: passes schema, customer, meter, and time-window rules
• posted: counted into billing period totals
• rejected: permanently ignored (with a reason code)

Example: your worker crashes after validating but before posting. On retry, it finds the same event_id in state validated, then continues to posted without creating a second event.

For Stripe webhooks, use the same pattern: store the Stripe event.id and mark it processed only once, so duplicate deliveries are harmless.

Step-by-step: ingest metering events end to end

Treat every metering event like money: validate it, store the original, then derive totals from the source of truth. That keeps billing predictable when systems retry or send data late.

A reliable ingestion flow

Validate each incoming event before touching any totals. At minimum require: a stable customer identifier, a meter name, a numeric quantity, a timestamp, and a unique event key for idempotency.

Write the raw event first, even if you plan to aggregate later. That raw record is what you’ll reprocess, audit, and use to fix mistakes without guessing.

A dependable flow looks like this:

• Accept the event, validate required fields, normalize units (for example, seconds vs minutes).
• Insert a raw usage event row using the event key as a unique constraint.
• Aggregate into a bucket (daily or per billing period) by applying the event quantity.
• If you report usage to Stripe, record what you sent (meter, quantity, period, and Stripe response identifiers).
• Log anomalies (rejected events, unit conversions, late arrivals) for audits.

Keep aggregation repeatable. A common approach is: insert the raw event in one transaction, then enqueue a job to update buckets. If the job runs twice, it should detect that the raw event is already applied.

When a customer asks why they were billed for 12,430 API calls, you should be able to show the exact set of raw events included in that billing window.

Reconciling Stripe webhooks with your database

Webhooks are the receipt for what Stripe actually did. Your app may create drafts and push usage, but invoice state only becomes real when Stripe says so.

Most teams focus on a small set of webhook types that affect billing outcomes:

• invoice.created, invoice.finalized, invoice.paid, invoice.payment_failed
• customer.subscription.created, customer.subscription.updated, customer.subscription.deleted
• checkout.session.completed (if you start subscriptions through Checkout)

Store every webhook you receive. Keep the raw payload plus what you observed when it arrived: Stripe event.id, event.created, your signature verification result, and your server received timestamp. That history matters when you’re debugging a mismatch or responding to “why was I charged?”

A solid, idempotent reconciliation pattern looks like this:

1. Insert the webhook into a stripe_webhook_events table with a unique constraint on event_id.
2. If the insert fails, it’s a retry. Stop.
3. Verify the signature and record pass/fail.
4. Process the event by looking up your internal records by Stripe IDs (customer, subscription, invoice).
5. Apply the state change only if it moves forward.

Out-of-order delivery is normal. Use a “max state wins” rule plus timestamps: never move a record backward.

Example: you receive invoice.paid for invoice in_123, but your internal invoice row doesn’t exist yet. Create a row marked as “seen from Stripe,” then attach it to the right account later using the Stripe customer ID. That keeps your ledger consistent without double processing.

From usage totals to invoice line items

Turning raw usage into invoice lines is mostly about timing and boundaries. Decide whether you need totals in real time (dashboards, spend alerts) or only at billing time (invoices). Many teams do both: write events continuously, compute invoice-ready totals in a scheduled job.

Align your usage window with Stripe’s billing period. Don’t guess calendar months. Use the subscription item’s current billing period start and end, then sum only events whose timestamps fall inside that window. Store timestamps in UTC and make the billing window UTC too.

Keep the history immutable. If you find an error later, don’t edit old events or rewrite prior totals. Create an adjustment record that points to the original window and adds or subtracts quantity. It’s easier to audit, and easier to explain.

Plan changes and proration are where traceability often gets lost. If a customer switches plans mid-cycle, split usage into sub-windows that match each price’s active range. Your invoice can include two usage line items (or one line plus an adjustment), each tied to a specific price and time range.

A practical flow:

• Pull the invoice window from Stripe period start and end.
• Aggregate eligible usage events into a usage total for that window and price.
• Generate invoice line items from the usage total plus any adjustments.
• Store a calculation run id so you can reproduce the numbers later.

Backfills and late data without breaking trust

Late usage data is normal. Devices go offline, batch jobs slip, partners resend files, and logs get replayed after an outage. The key is to treat backfills as correction work, not as a way to “make the numbers fit.”

Be explicit about where backfills can come from (application logs, warehouse exports, partner systems). Record the source on every event so you can explain why it arrived late.

When you backfill, keep two timestamps: when usage happened (the time you want to bill for) and when you ingested it. Tag the event as backfilled, but don’t overwrite history.

Prefer rebuilding totals from raw events over applying deltas to today’s aggregate table. Replays are how you recover from bugs without guessing. If your pipeline is idempotent, you can re-run a day, a week, or a full billing period and get the same totals.

Once an invoice exists, corrections should follow a clear policy:

• If the invoice isn’t finalized, recalculate and update totals before finalization.
• If it’s finalized and underbilled, issue an add-on invoice (or add a new invoice item) with a clear description.
• If it’s finalized and overbilled, issue a credit note and reference the original invoice.
• Don’t move usage into a different period to avoid a correction.
• Store a short reason for the correction (partner resend, delayed log delivery, bug fix).

Example: a partner sends missing events for Jan 28-29 on Feb 3. You insert them with occurred_at in January, ingested_at in February, and a backfill source of “partner.” The January invoice was already paid, so you create a small add-on invoice for the missing units, with the reason stored alongside the reconciliation record.

Common mistakes that cause double-counting

Double-counting happens when a system treats “a message arrived” as “the action happened.” With retries, delayed webhooks, and backfills, you need to separate the customer action from your processing.

The usual culprits:

• Retries treated as new usage. If every event doesn’t carry a stable action id (request_id, message_id) and your database doesn’t enforce uniqueness, you’ll count twice.
• Event time mixed with processing time. Reporting by ingested time instead of occurred time makes late events land in the wrong period, then get counted again during replays.
• Raw events deleted or overwritten. If you keep only a running total, you can’t prove what happened, and reprocessing can inflate totals.
• Webhook order assumed. Webhooks can be duplicated, out of order, or represent partial states. Reconcile by Stripe object IDs and keep an “already processed” guard.
• Cancellations, refunds, and credits not modeled explicitly. If you only add usage and never record negative adjustments, you end up “fixing” totals with imports and counting again.

Example: you log “10 API calls” and later issue a credit for 2 calls due to an outage. If you backfill by re-sending the whole day’s usage and also apply the credit, the customer can see 18 calls (10 + 10 - 2) instead of 8.

Quick checklist before you go live

Before turning on usage-based billing for real customers, do a last pass on the basics that prevent expensive billing bugs. Most failures aren’t “Stripe issues.” They’re data issues: duplicates, missing days, and silent retries.

Keep the checklist short and enforceable:

• Enforce uniqueness on usage events (for example, a unique constraint on event_id) and commit to one id strategy.
• Store every webhook, verify its signature, and process it idempotently.
• Treat raw usage as immutable. Correct with adjustments (positive or negative), not edits.
• Run a daily reconciliation job that compares internal totals (per customer, per meter, per day) against Stripe billing state.
• Add alerts for gaps and anomalies: missing days, negative totals, sudden spikes, or a large difference between “events ingested” and “events billed.”

A simple test: pick one customer, re-run ingestion for the last 7 days, and confirm totals don’t change. If they do, you still have an idempotency or backfill problem.

Example scenario: a realistic month of usage and invoices

A small support team uses a customer portal that charges $0.10 per conversation handled. They sell it as usage-based billing with Stripe, but trust comes from what happens when data is messy.

On March 1, the customer starts a new billing period. Each time an agent closes a conversation, your app emits a usage event:

• event_id: a stable UUID from your app
• customer_id and subscription_item_id
• quantity: 1 conversation
• occurred_at: the close time
• ingested_at: when you first saw it

On March 3, a background worker retries after a timeout and sends the same conversation again. Because event_id is unique, the second insert becomes a no-op, and totals don’t change.

Mid-month, Stripe sends webhooks for invoice preview and later the finalized invoice. Your webhook handler stores stripe_event_id, type, and received_at, and marks it processed only after your database transaction commits. If the webhook is delivered twice, the second delivery is ignored because stripe_event_id already exists.

On March 18, you import a late batch from a mobile client that was offline. It contains 35 conversations from March 17. Those events have older occurred_at values, but they’re still valid. Your system inserts them, recalculates daily totals for March 17, and the extra usage is picked up on the next invoice because it’s still inside the open billing period.

On March 22, you discover one conversation was recorded twice due to a bug that generated two different event_id values. Instead of deleting history, you write an adjustment event with quantity = -1 and a reason like “duplicate detected.” That keeps the audit trail intact and makes the invoice change explainable.

Next steps: implement, monitor, and iterate safely

Start small: one meter, one plan, one customer segment you understand well. The goal is simple consistency - your numbers match Stripe month after month, without surprises.

Build it small, then harden

A practical first rollout:

• Define one event shape (what is counted, in what unit, at what time).
• Store every event with a unique idempotency key and a clear status.
• Aggregate into daily (or hourly) totals so invoices can be explained.
• Reconcile against Stripe webhooks on a schedule, not only in real time.
• After invoicing, treat the period as closed and route late events through an adjustment path.

Even with no-code, you can keep strong data integrity if you make invalid states impossible: enforce unique constraints for idempotency keys, require foreign keys to customer and subscription, and avoid updating accepted raw events.

Monitoring that saves you later

Add simple audit screens early. They pay for themselves the first time someone asks, “Why is my bill higher this month?” Useful views include: searching events by customer and period, seeing per-period totals by day, tracking webhook processing status, and reviewing backfills and adjustments with who/when/why.

If you’re implementing this with AppMaster (appmaster.io), the model fits naturally: define raw events, aggregates, and adjustments in the Data Designer, then use Business Processes for idempotent ingestion, scheduled aggregation, and webhook reconciliation. You still get a real ledger and an audit trail, without hand-writing all the plumbing.

When your first meter is stable, add the next one. Keep the same lifecycle rules, the same audit tools, and the same habit: change one thing at a time, then verify it end to end.