Comment by janci | Hacker News Reader

Comment by janci | Hacker News Reader

I was working on a billing system for utility company and it was a nightmare but not for the reasons from the article.

Dates are no problem, everything is billed at the end of month, quarter or year.

Upgrades are downgrades are simplified, customer can legally change tariff only on predefined dates/events.

Usage - yes, but exampled in the article do not even scratch the complexity of this

Idempotency is not really needed, the billing process is not continuous, it is a batch job.

Cash collection is out of scope of billing software.

Taxing was clear. Yes, there were multiple billable items with different tax rates, but not too complex. All customers were local.

The nightmare part was:

- customers can have multiple consumption locations, location can have multiple meters and customer can request to split the bill on multiple invoices or combine multiple bills to single invoice as they please

- meters can be replaced at any time

- meter readings are available on dates totally independent from billing cycle. Most of consumption data are mere forecasts.

- when the actual consumption data is available, everything must be recalculated AND compensated (on a correction invoice showing the difference)

- actual consumption data can be wrong and may be corrected at a later date, even multiple times

- consumption points can be added or removed or moved to different customer at any time, but this information is only available after the fact

- the prices can change mid-billing cycle for some customers but the consumption data is not available with that granularity

- customer legal information (company name, address) can change mid-cycle

Thanks janci for sharing your experience!

Indeed, we only scratched the surface of the complexity of metered billing, we'll do a deep dive soon on this topic (it does deserve its own post).

I think for the'nightmares' you mention: - some might be specific to utility (not applicable to an online business), such as '- meter readings are available on dates totally independent from billing cycle'. - Some topics might be simpler for a utility co: tax for instance (you know where your users are, and they are limited to a geographic region)

- But some nightmares such as '- the prices can change mid-billing cycle for some customers but the consumption data is not available with that granularity' do really resonate for online businesses too, indeed

Thanks for sharing, great insights for our future post!

For online businesses as well, selling above a certain amount of revenue or product can trigger what's known as a nexus event in taxation wherein the business becomes liable for collecting sales or use tax. Now online businesses are on the hook for collecting sales tax in every jurisdiction above wildly varying threshold values. Should the business fail to collect the sales tax for selling from Montana to California for instance, then the business will be liable for sales tax not collected for products or services sold to California residents online even though the business might physically reside in Montana.

Is there an easy way to deal with this e.g. compliance as a service?

Sounds like a nightmare to deal with, of course this is only assuming it is enforced. If it is not well who cares, companies like Lyft/Uber view laws as a starting point in negotiations when expanding into new areas not as a brick wall of dogma.

> Is there an easy way to deal with this e.g. compliance as a service?

I think we call ourselves CPAs :). In all seriousness the 'solution' depends on what industry we're talking about. Short term rentals? Avalara. Software as a service? Various accounting firms and startups. Etc.

Montana does not have a sales tax.

Did the company use a relational database to deal with the billing and subsequent adjustments? I've been there: it can be more straightforward to use an event streaming approach that recalculates billing as new events arrive.

Yes, almost everything ended as temporal or bitemporal model. Pain to work with. Even for simple questions like "What is the customer #1234 company name?" the system needs to know when are you asking.

By "temporal model" do you mean relational tables of "customer X used Y amount of this resource at price Z from timestamp A to timestamp B" or ... ?

In field metering situations you can have the timeline of each meter, when it records data points, and then you have the different timeline of the backend service, when it actually _receives_ data points.

Bonus points if the meters can send data out of order.

Ah, thanks. OMFG and I thought our billing was hard. You're in my thoughts and prayers!

I did a tender for aggregating events and emitting notifications from a fleet of transport vehicles that reported metrics over LTE.

When connected they would emit events as they happened, but when there was poor service or disconnection they would buffer them and send them as a burst once they reconnected... in reverse order.

I forget the finer details, but the service I was writing was supposed to notify of trucks entering and leaving geofences, inactivity etc. anything you can think of where the order of events is important.

You just perfectly described what I’m currently working on. And it’s even more complex than you’ve explained (which I’m sure you’d well know haha)

I assume they are using something like DB2, which has very specific meaning when it comes to temporal or bitemporal tables.

https://www.ibm.com/docs/en/db2/10.5?topic=tables-bitemporal

Not DB2, but the meaning is the same.

In a time-series (or event) table you have one date - when the data point is valid (event occurred).

In a temporal table you have two dates - interval when the data is valid.

In a bitemporal table you have two intervals. First describes when the data is valid in real world, the second when does the system know about the real-world state.

I.e. if company changed address on 2022-05-01 but the system will not get that information until three day later (2022-05-03) you have two validity intervals.

Then you may ask - what was the company address on 2022-05-02? And what did the system think the address was on 2022-05-02? You will get two different answers. This is important if you need e.g. to make a correction to previously issued invoice. You need to know what data was used for original invoice and what data should have been used.

Designed and build the system meter readings and calculations for the Dutch market, all mysql, worked like a dream.

I have heard some horror stories about the dutch smart meters.

Apparently the billing system provided for 7 'counters' and hence the smart-meter had to have exactly 7 counters.

In my opinion, the best way to do it is ingesting events in a relational db with an idempotency key to prevent duplicated events. Some companies don't need real time billing data, so you could just use it to aggregate these events with the aggregation function that fits your need for a certain feature. Thus, you have the flexibility of (i) calculating pretty much everything you want, (ii) give granularity of usage to your customers and (iii) be closer to real time (you play the query anytime you need it).

I was also consulting for a utility company. Billing was easy, we used SAP (lol). But CRM (where we also used SAP) was a nightmare. The different processes where a nightmare. I mean utilities are amazingly complex logistically. New metering for a single house build, when it's ordered, installed, etc.

I work in a closely related space: parent's comments are accurate. Some of these items can be solved with better forecasting models, but the issue is that almost no-one actually cares about 'better' forecasting at inidividual meter levels. Sysytem-wide (or at least substation level) forecasts are well studied for supply/demand considerations (at a minimum). Also, the variability in consumption patterns at an individual level are large. IOW, one might be able to generate forecasts accurately for 'typical' residential consumption (at a per-household level), but commercials can be very different. Ideally, IF (and this is a BIG if), forecasts for "most" premises was accurate enough, then one could make the claim that the missing data shouldnt really cause a recalculation of bill once the actual consumption data comes thru, except in cases where the error is large. Guess that means that the error check/correct process needs to continue to exist.

The meter being replaced shouldnt be a major issue, this is relational (at least we treat this all as relational) data that should be captured (by the customer information 'CIS' system), and should be available with a 1-2 day delay. Similar argument applies to other relational aspects of the premise under consideration. Not saying that those are easy (more ways for this process to have gaps).

Reply on news.ycombinator.com