3 ms·

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 a

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.

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.