Usage billing where a late event is just another row

A usage billing design in Postgres where every stage only appends rows, so usage that shows up after its invoice becomes an adjustment line on the next one.

Price book Product Ingest Usage ledger Rating job Rated lines Invoice builder Invoice versions, never edited usage events dedupe by key append only with price version only sums lines or credit note
Seven stops carry usage from the product to an invoice or credit note, and the price book joins at the rating job.
On this page
  1. Two clocks and a key on every event
  2. Rate the month, then write the difference
  3. March at Gullwing Maps
  4. The rule for usage that arrives after its invoice

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.