How to design database schema for usage based billing?

I've written billing/usage systems before, something your missing is the audit trail, you will get a query on a bill by a customer (for example, why did my bill increase this month so much, or spike on Wednesday?) and you need to be able to dig into the raw data (eg, your raw usage logs that contain high detail of start/stop events or usage information for each service call) to provide comfort to yourself and your customer about the billing query and validate there was no billing error.

Since you will want to plan for that, one thing you can do is treat the end of month billing calculation (or ongoing usage per day) as a calculation based off your raw usage logs.

So your usage of the product (service usage start/stop times) are log events (eg, pushed to S3 if its a huge system or start with just another database table like billing_usage_event) that describes each service start/stop and calculated rate etc.

Then your bill is the aggregation of the raw events for the day/month, allowing you to both do an audit if there is a billing query (find out why the bill spike occurred by looking at billing_usage_event) and also provide peace of mind to you and your customer the billing is accurate.

("Accounting for Developers" was posted recently on HN, it was a great read - https://news.ycombinator.com/item?id=32495724)

I know it feels like overkill, but I’d recommend either outsourcing (even Stripe has usage-based billing if you can keep your updates under 100/s) or using something off-the-shelf like https://killbill.io.

I know it seems like a simple problem, but billing systems quickly turn into Eldridge horrors, even if you have the best intentions, and it’s always nicer to have someone else to maintain that for you.

We built an open source usage based billing with Lago (YC S21). You deploy a Postgres database with all useful objects to run your billing (events, subscriptions, plans, prepaid credits, customers, invoices …).

Check it out here: - GitHub https://github.com/getlago/lago/issues - website https://www.getlago.com/ - documentation doc.getlago.com

Have you checked https://github.com/getlago/lago ? Open source billing API for SaaS.

Disclaimer: I am one of the cofounders, but looks like this could really fit your needs, especially as you add more complex logics afterwards: prepaid/offered credits, probably discounts, etc.

Also, if it’s the first time you’re building a billing system, you might want to check this thread about billing nightmares. It seems very simple at first but it becomes messy very fast. https://news.ycombinator.com/item?id=31424450

Happy to help more (whether you use Lago or not!)!

If a customer ever changes their billing rate, you will need to keep track of when it was changed and what their previous rates were, otherwise you'll have no audit trail and no way to bill customers correctly.

One approach would be to have a billing_period (account_id, billing_rate_id, start_date, end_date) table. Another would be to store the billing_rate_id in your account_credit table so each usage can be tied to the billing rate the customer should be charged for it.

I also wouldn't keep a running_balance column. I forget which rule of normalisation it is because it's been so long, but you shouldn't normally store a value that you can compute from other data.

Unless your product is a billing system, stop right now and go look at using a third party. Zuora, Stripe, etc all have billing platforms you can use. Trust me from hard earned experience that billing system seem "easy" but they really aren't. They are very, very hard to get right and if you fuck up you are gonna overcharge, undercharge or god knows what else and piss your customers off.

Not only that but you'll be stuck in a loop writing features that work out of the box with third party software. Important ones like the ability to change the price, customize the price for a single account, refund money, export to quickbooks, add new products, offer coupons, offer sales, provide quotes to prospects, etc. You don't want to do this! A third party does all this and more.

So yeah. Don't build it! You'll regret doing so! Trust me!

I am no sql expert, but have some recent experience designing similar systems and went through the db structures of several big oss projects for inspiration (some lost HN post, if anybody remembers).

I think your account_debit and account_credit tables have their names swapped.

You should have a products table, and a billing tier table with timestamps for start and end, so that accounts billing tier * products billing base rate at the time of use = their cost. You are not going to do only one thing or have only one price forever.

So at least:

accounts (acct_id, timestamp_start, timestamp_end, tier_id) - this gets more than one row per acct based on whether they change rate tiers. Same thing true for products and tiers below.

products (prod_id, timestamp_start, timestamp_end, base_rate)

tiers (tier_id, rate_multiplier, product_id, timestamp_start, timestamp_end) - include product_id if you want use different multipliers for the same account on different products in the future. timestamp_start and _end so you can use the right multiplier when prices change for active accounts.

you can include an `active` boolean on any of those table to speed up queries if necessary.

Then, in account_debit (labelled account_credit above), I would just log usage (debit_id, timestamp_start, timestamp_end, product_id, account_id), and calculate out only when actually billed. The way you have it now you could never deal with a usage dispute. You need better logging of raw data, never store calculation results when you can trivially store numbers.

account_credit looks fine, maybe store source_transaction_id for ease of lookup. I don't have the experience in footguns as indicated with foreign key constraints by others, that seems worth listening to ;). I think using timestamp_start and _end so that there are never any changes to tier rate rows, product base price rows, etc, should go a long way towards mitigating that here.

Barring performance/memory/misc concerns, this seems pretty much the same as I'd design it.

If I understand your problem correctly, you have two facts: user makes a payment (account gets credited) and user consumes credits (account gets debited). Everything else is a dimension (accounts, rates).

The only choice you have to make is how you deal with changing rates. You can write the premultiplied amount on the debit table, or you can treat rates as a slowly changing dimension and decompose "amount" into "time" and the foreign key to the rate table.

Which one is better depends on what the application is doing, there's nothing wrong in principle with either.

Those "id" names are really confusing; I assume they are all shorthand for "acct_id", or... maybe not.

I think account_credit & account_debit need foreign keys back to billing_account, and they probably are gonna need their own primary columns eventually (so you can add on addl metadata like 3rd party transaction tracking).

I'm not sure what billing_rate's relationship is to billing_account, if any...

Just based on what I am reading about building such systems, it seems like the common advice is to not have a single balance column such as `running_balance`, but instead calculate this value on the go every time it is needed.

That's fine. What else?

I would get rid of the billing_rate table or at least copy its contents into billing_account - nothing good can come of changing that reference!

Also consider how you will build invoicing on top of this - i.e. A monthly PDF that covers (for a period) prepayments made, services rendered, balance remaining.

It seems like your billing model makes that quite easy but bear in mind you have to be able to sum that up on a particular date, and for that summary to be immutable.

Avoid building it if you can. Stripe is great. The last billing system I built had a similar schema to yours. As the business grew, we got bigger customers who hated that we just provided a balance due, which was a sum of all debits and credits and not actual invoices they could pay or submit to their finance team for payment. Wrangling recurring charges yourself will unnecessarily take years off of your life.

if the billing rate for running something is a delta (like you posted, 0.01/second) of time, then having a timer (or cron, or batch, or kafka, whatever) that records the usage is also atomically deducting it from the account_balance. The account_balance is then topped up with whatever payment schema you want to create. The main issue is that you track the usage as a variable of a constant value to deduct from the account_balance variable of a constant value.

Your design is sound. Multiple rates against a running balance, that running balance's constant value is, well, USD. So the rate of usage is USD. Credits/Debits table is really there for reporting and has no bearing on the running_balance.

Good luck!

consider not storing running_balance (or at least expect it to be written to a LOT). Based on the above description that's going to be a hot table with a write/update of 1 per second per row (of active customers).

If you are thinking sql, that running balance could be calculated from a VERY tall and skinny table of time_spent per session (which would be calculated in the application) or an append only table of (billing_account_id, timestamp) where you write a new row every second.

Should account_credit record their payments and account_debit their spending?

How about open sourcing and putting this on Github and letting anyone submit pull requests with suggestions/ideas?

Stripe allows you to bill based on reported usage which seems considerably easier than crafting an equivalent feature from scratch. This is how we handle billing at skusavvy

https://stripe.com/docs/products-prices/pricing-models#usage...