Our Stripe configuration is pretty typical for software startups. We have Customers in Stripe which correspond one-to-one with the organizations in our app. Each of those Customers has an associated Subscription, which manages their billing policy.

Initially, Sequin used fixed prices for Subscriptions. That simple approach has its advantages: you set the price when you reach a service agreement with your customer, then forget it until someone wants to renegotiate or the customer wants to cancel. Nothing dynamic, no unpleasant surprises. As Sequin scaled, though, we realized these static prices didn't capture our relationship with a given customer, either in terms of value delivered or cost of support. The solution is metered billing – in Stripe terms, a recurring Price with the metered usage type – where a customer's bill is automatically calculated from their use of the product. For now, we're happy using monthly active rows (MAR).

You don't just set and forget metered billing; you repeatedly report per-customer usage to Stripe.

The Subscription itself isn't the billable entity. Instead, you report usage to a specific subscription item, which represents a Subscription in a certain billing period, e.g. a month. Those Subscription Items are automatically generated by Stripe.

Stripe has a handy single endpoint for updating the usage for a subscription item, either by incrementing the existing value or overriding it:

curl https://api.stripe.com/v1/subscription_items/{{SUBSCRIPTION_ITEM_ID}}/usage_records \
  -X POST \
  -d quantity=100 \
  -d action=set # Alternatively, action=increment.

But watch out! Woe unto they who post usage to non-metered (e.g. licensed) subscription items. If the Price for a Subscription Item isn't configured appropriate, Stripe will reject your requests.

We had our MAR calculations ready to go, a stored mapping between Sequin organizations and Stripe Customers, and a way to post usage. That leaves one question: for a given customer, at a given time, what's the corresponding subscription item ID? Which Subscription Items need usage reports?

Without syncing Stripe

Starting with our stripe customer ID, we have to resolve their active Subscription with a GET request to v1/subscriptions:

curl -G https://api.stripe.com/v1/subscriptions \
  -d limit=1 \
  -d customer={{CUSTOMER_ID}} \
  -d status=active

The resulting Subscriptions include their current Subscription Items and their corresponding prices. From there, for each Subscription Item,

1. Check if it requires a usage report.
2. If so, make the POST request described above.

The Subscription-getting round-trip request might seem insignificant, but for large numbers of subscription items the additional API round-trip can get onerous. Sequin's hunch is that it should also be unnecessary; we're using our own product in production to simplify metered billing usage reports.

Sync-simplified

Sequin's Stripe sync replicates your Stripe API data in your database, then keeps it consistent as things change in Stripe. The multiple-association resolved by the Stripe API's v1/subscriptions endpoint (Customer to Subscription, Subscription to Price, and Subscription to Subscription Item) is all right there in Postgres, out of the box. The kicker? You'll spend longer debugging that GET request (did you forget to filter by status?) than it takes to connect Stripe to Sequin.

With all that data in Postgres, we replaced the precursor Subscription API call with a single Postgres query joining organizations (with usage data) to their active Subscription Items. In Elixir we use Ecto.Query.from/2:

from(
  sub in Subscription,
  # Join organizations by Customer ID.
  join: org in Org,
  on: sub.customer_id == org.stripe_customer_id,
  # Join Subscription Item by Subscription ID.
  join: sub_item in SubscriptionItem,
  on: sub.id == sub_item.subscription_id,
  # Join prices by price ID on the Subscription Item.
  join: price in Price,
  on: sub_item.plan_id == price.id,

  # Don't report usage if the Subscription Item has been deleted.
  where: is_nil(sub_item.deleted) or sub_item.deleted != true,
  # Only report usage for metered subscriptions.
  where: price.recurring_usage_type == "metered",
  # Only report usage for active subscriptions.
  where: sub.status == "active",

  # Select fields we need to report usage: Subscription Item ID, MAR.
  select: {sub_item.id, org.mar, 0)}
)

At regular intervals, a cron-like Quantum job runs a version of this query. For each row of the result, we build and POST a usage report. This task is robust. The query performance is predictable. It's short lived – it makes half as many HTTP requests as an implementation fetching Subscriptions from the API. Syncing our Stripe data simplified our application.

Sounds compelling? Explore your own Stripe data in a free demo database, and let us know what you build.