3 ms·

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 (so

by sgc 4y ago

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.

withinboredom 4y ago
Footguns: - ensure you encode the billing rate WITH THE EVENT. This makes summation practically simple and allows you to quickly zero out a billing error! It also allows you to (at some point in the future) offer special billing for certain customers by encoding the billing rates at the time of subscription as part of the user’s profile or in its own table. Why? You’re probably going to be using the ids of what you’re selling. Over time, you’re going to be changing these prices. If you change the SKU, you’ll lose historical pricing in your data and maybe even screw up customer’s past invoices. If you just create a new id/SKU/tier/product, then you have to change all the ids all over the code, which is a nightmare. However, if you set the base rate for the SKU, and copy that for anything that gets billed, you can easily change the base rate of the SKU without affecting the past.
- sgc 4y ago
  However, that requires doing an extra lookup for every write on the most active / real time table. It would be better to add an optional product_id to the account table, which then allows to have different rates for different products at the same time for each account. You really don't need a tier table, if you are always using multipliers to the base rate, and we could eliminate it unless we want to store tier types as well, that allow for other formulas beyond multipliers. The account, tier, and product tables already take care of changing prices using timestamps, but I can see that you might offer a fixed rate to an account that spans a price change on the base rate. There is an entire facet to this which is not really about the db, but business logic - like whether we are going to change rates used for calculation based on time of use, time of account creation, time of rate tier activation, product base rate change - change that logic if it's a promo rate - etc. I am not totally sure that we need to go beyond what we have for the dbs to work with this business logic, it is better handled and enforced by the calculation classes. I don't think calculations are ever that hard, because I presume you will have previously written and optimized lookups and calculations for all common to semi-common cases. I would also (obviously?) never calculate on the live server, but dump the db and calculate billing on an admin server. If the site is busy enough, I would have this entire db on an admin server, and just import the usage logging table from a simpler db on live. For live, you just need enough account and product info for authentication and logging.
  - withinboredom 4y ago
    > However, that requires doing an extra lookup for every write on the most active / real time table. You only have to do that lookup once and you're likely looking up other things to write that event as well. So it is almost a free cost. You can actually do all of this async and faster: Just call a url containing all the details in the url (/usage?user_id=123&product_id=123&type=start_usage) that returns a 200 response with a minimal payload (0 byte static file). From there, just stream the server's access logs to Apache Kafka after the logs rotate once per minute to be parsed. There it will do all the lookups and aggregation. You're probably only looking at a delay of 4-5 minutes before the data is available to the end user -- even with millions of active users. Then you can parse the logs async as fast/slow as you need to.

by sgc 4y ago

withinboredom 4y ago

Footguns: - ensure you encode the billing rate WITH THE EVENT. This makes summation practically simple and allows you to quickly zero out a billing error! It also allows you to (at some point in the future) offer special billing for certain customers by encoding the billing rates at the time of subscription as part of the user’s profile or in its own table. Why? You’re probably going to be using the ids of what you’re selling. Over time, you’re going to be changing these prices. If you change the SKU, you’ll lose historical pricing in your data and maybe even screw up customer’s past invoices. If you just create a new id/SKU/tier/product, then you have to change all the ids all over the code, which is a nightmare. However, if you set the base rate for the SKU, and copy that for anything that gets billed, you can easily change the base rate of the SKU without affecting the past.

sgc 4y ago
However, that requires doing an extra lookup for every write on the most active / real time table. It would be better to add an optional product_id to the account table, which then allows to have different rates for different products at the same time for each account. You really don't need a tier table, if you are always using multipliers to the base rate, and we could eliminate it unless we want to store tier types as well, that allow for other formulas beyond multipliers. The account, tier, and product tables already take care of changing prices using timestamps, but I can see that you might offer a fixed rate to an account that spans a price change on the base rate. There is an entire facet to this which is not really about the db, but business logic - like whether we are going to change rates used for calculation based on time of use, time of account creation, time of rate tier activation, product base rate change - change that logic if it's a promo rate - etc. I am not totally sure that we need to go beyond what we have for the dbs to work with this business logic, it is better handled and enforced by the calculation classes. I don't think calculations are ever that hard, because I presume you will have previously written and optimized lookups and calculations for all common to semi-common cases. I would also (obviously?) never calculate on the live server, but dump the db and calculate billing on an admin server. If the site is busy enough, I would have this entire db on an admin server, and just import the usage logging table from a simpler db on live. For live, you just need enough account and product info for authentication and logging.
- withinboredom 4y ago
  > However, that requires doing an extra lookup for every write on the most active / real time table. You only have to do that lookup once and you're likely looking up other things to write that event as well. So it is almost a free cost. You can actually do all of this async and faster: Just call a url containing all the details in the url (/usage?user_id=123&product_id=123&type=start_usage) that returns a 200 response with a minimal payload (0 byte static file). From there, just stream the server's access logs to Apache Kafka after the logs rotate once per minute to be parsed. There it will do all the lookups and aggregation. You're probably only looking at a delay of 4-5 minutes before the data is available to the end user -- even with millions of active users. Then you can parse the logs async as fast/slow as you need to.