There’s a $1.25 line on the April invoice, and it says “March usage”. The customer has written in to ask what it is.
I judge a usage billing design by how that ticket gets answered. In a good one, support runs a query and replies the same day: these 312 calls were made in March and didn’t reach us until April 3, so they’re billed now, at the March rate. In a bad one an engineer spends the afternoon in job logs, because a counter was incremented in place or an invoice total was recalculated, and the evidence went with it.
The arithmetic of usage billing is one multiplication. What breaks is the handoff between stages: a retry delivers the same event twice, or an event shows up after its month has closed. So I’d build it as the line in the diagram above. Every stop writes rows for the next stop to read, and no stop changes a row once it’s written.
To keep the numbers exact, I’ll use an invented geocoding vendor, Gullwing Maps. Each month includes 50,000 calls, and every call beyond that costs $0.004.
Two clocks and a key on every event
CREATE TABLE usage_event (
event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
meter text NOT NULL, -- 'geocode'
quantity numeric(18,6) NOT NULL,
idempotency_key text NOT NULL,
event_time timestamptz NOT NULL, -- when the usage happened
received_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (customer_id, idempotency_key)
);
CREATE INDEX usage_event_time_idx ON usage_event (event_time);
-- Ingest. A retry of an event that is already stored inserts zero rows.
INSERT INTO usage_event (customer_id, meter, quantity, idempotency_key, event_time)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (customer_id, idempotency_key) DO NOTHING;
That table is the usage ledger. Deduplication is the unique constraint plus ON CONFLICT DO NOTHING, which Postgres has had since 9.5. The hard part is upstream. The key has to be created where the usage happens, before the first attempt to send it. The geocoder’s own request ID is a good one. A UUID generated inside the retry loop is useless, because every retry gets a fresh one and they all look like new usage.
Ingest reports success whether the insert wrote one row or none. To the sender both mean the event is recorded, and an error on a duplicate would only earn another retry.
The two timestamps do different jobs. event_time comes from the product and decides which month the usage belongs to. received_at is filled in by the database, and the insert never supplies it. The gap between them is what answers the $1.25 ticket.
Rate the month, then write the difference
Prices get the same treatment as events. A price change is a new row with a later effective_from, and every rated line names the row it was priced with. (A real price book is keyed by plan or contract. One price per meter keeps this short.)
CREATE TABLE price_version (
price_version_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
meter text NOT NULL,
effective_from date NOT NULL, -- first day of a month
included_units numeric(18,6) NOT NULL,
unit_price numeric(12,6) NOT NULL
);
CREATE TABLE rated_line (
rated_line_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
meter text NOT NULL,
usage_period date NOT NULL, -- month the usage happened in
billing_period date NOT NULL, -- month whose invoice carries this line
price_version_id bigint NOT NULL REFERENCES price_version,
quantity numeric(18,6) NOT NULL, -- billable units this line adds
amount numeric(12,2) NOT NULL, -- below zero when a correction favors the customer
created_at timestamptz NOT NULL DEFAULT now()
);
The tempting way to handle a late event is to price it alone, 312 calls at $0.004. That overcharges any customer who was still inside their included calls. So the rating job works on a whole month at a time. It totals the month from the ledger, prices the total, subtracts what it has already written, and inserts a line for whatever is left.
-- $1 is the first day of the billing period that is still open,
-- for example '2024-04-01'.
WITH used AS (
SELECT customer_id,
meter,
date_trunc('month', event_time AT TIME ZONE 'UTC')::date AS usage_period,
sum(quantity) AS units
FROM usage_event
WHERE event_time >= ($1::date - interval '3 months') AT TIME ZONE 'UTC'
AND event_time < ($1::date + interval '1 month') AT TIME ZONE 'UTC'
GROUP BY customer_id, meter, usage_period
),
priced AS (
-- A meter with no price version drops out of this join and is never
-- billed, so check for unpriced meters before invoicing.
SELECT u.customer_id,
u.meter,
u.usage_period,
p.price_version_id,
greatest(u.units - p.included_units, 0) AS billable,
round(greatest(u.units - p.included_units, 0) * p.unit_price, 2) AS amount
FROM used u
CROSS JOIN LATERAL (
SELECT v.price_version_id, v.included_units, v.unit_price
FROM price_version v
WHERE v.meter = u.meter
AND v.effective_from <= u.usage_period
ORDER BY v.effective_from DESC, v.price_version_id DESC
LIMIT 1
) p
),
already AS (
SELECT customer_id,
meter,
usage_period,
sum(quantity) AS billable,
sum(amount) AS amount
FROM rated_line
WHERE usage_period >= $1::date - interval '3 months'
GROUP BY customer_id, meter, usage_period
)
INSERT INTO rated_line
(customer_id, meter, usage_period, billing_period, price_version_id, quantity, amount)
SELECT p.customer_id,
p.meter,
p.usage_period,
$1::date,
p.price_version_id,
p.billable - coalesce(a.billable, 0),
p.amount - coalesce(a.amount, 0)
FROM priced p
LEFT JOIN already a USING (customer_id, meter, usage_period)
WHERE p.billable <> coalesce(a.billable, 0)
OR p.amount <> coalesce(a.amount, 0);
Run it twice and the second run writes nothing, because the difference is zero. Run it after late events land and it writes one small line. On a nightly schedule the open month collects a line per night, and the invoice builder adds them up. Rounding happens on the month’s total, so a month’s lines always sum to it and no pennies drift.
A price change needs no special handling. March lines keep pointing at the March version, so they can still be explained after April’s price goes live. If a version turns out to be wrong, I’d insert a corrected one with the same date and let the next run write the difference, negative if the customer was overcharged.
The query assumes one run at a time. Two at once would see the same gap and both fill it, so the job takes an advisory lock first. It also refuses a billing period that already has an invoice.
The invoice builder is dull on purpose. It sums amount over one customer’s lines for one billing_period and writes the invoice, or a credit note when the sum is negative. It has no idea what a geocode costs, so rerunning it can’t change a price.
March at Gullwing Maps
In this invented March, one customer’s integration sends 1,264,580 events. Of those, 1,180 are retries that hit the unique constraint, which leaves 1,263,400 rows in the ledger. On April 1 the rating job runs with March as the open period. Take away the 50,000 included calls and 1,213,400 are billable, which at $0.004 comes to $4,853.60. The March invoice goes out for that amount.
On April 3, 312 more events arrive carrying March timestamps. Say a queue in one region had been stuck. The next run, with April now the open period, totals March again at 1,263,712 calls and prices it at $4,854.85. It finds $4,853.60 already written and inserts the second row below.
| usage_period | billing_period | price_version_id | quantity | amount |
|---|---|---|---|---|
| 2024-03-01 | 2024-03-01 | 3 | 1,213,400 | 4,853.60 |
| 2024-03-01 | 2024-04-01 | 3 | 312 | 1.25 |
Nobody reopens the March invoice. April’s invoice carries April’s usage plus one line with March written on it.
The rule for usage that arrives after its invoice
The query encodes a policy, and the policy should also exist in words that finance and support have both agreed to. The version I’d propose:
Usage belongs to the month of its event time in UTC, however late it reaches us. Once a month has been invoiced, that invoice is final. Late usage for that month is rated at the price version that applied to it and billed as a separate adjustment line on the next invoice, labeled with the month it belongs to. A negative adjustment reduces the next invoice, and becomes a credit note if it would take that invoice below zero. Usage that arrives more than three billing periods late is stored but not billed automatically, and finance reviews it.
The last sentence is the interval '3 months' in the query. I’d defend a cutoff against anyone who wants adjustments open forever, because without one no month is ever closed for revenue reporting.
Adjustment lines have a cost. Customers notice them, and a $1.25 line can eat more support time than it’s worth. I’d still take that over reissuing an invoice, which sends the customer’s accounting team back to redo a month they’d already closed.
As for the ticket, the answer is one query against the ledger:
-- $1 is the customer, $2 is when the March invoice was issued.
SELECT (received_at AT TIME ZONE 'UTC')::date AS received_on,
count(*) AS events,
sum(quantity) AS calls
FROM usage_event
WHERE customer_id = $1
AND event_time >= timestamptz '2024-03-01 00:00:00+00'
AND event_time < timestamptz '2024-04-01 00:00:00+00'
AND received_at > $2
GROUP BY received_on
ORDER BY received_on;
For Gullwing’s customer it comes back with one row: April 3, 312 events.