Keep raw events and derive every total from them

How to do it

A counter you add to cannot be checked, corrected or explained to a customer who disputes a bill. Raw events can. Sum them by customer, meter and period in a query or a scheduled job. Store the totals as a cache that can be rebuilt. Correct a mistake with a new event that has a negative or offsetting quantity, never by editing an old one. Keep the events as long as Keep payment records for the legal period requires for the invoice.

Examples

SQL example

SELECT customer_id, meter, sum(quantity) AS total
FROM usage_events
WHERE occurred_at >= $1 AND occurred_at < $2
GROUP BY customer_id, meter;

Python example

def period_totals(db, start, end) -> dict[tuple[str, str], int]:
    rows = db.execute(
        "SELECT customer_id, meter, sum(quantity) FROM usage_events "
        "WHERE occurred_at >= %s AND occurred_at < %s GROUP BY customer_id, meter",
        (start, end),
    ).fetchall()
    return {(customer, meter): total for customer, meter, total in rows}

JavaScript example

export async function periodTotals(db, start, end) {
  const { rows } = await db.query(
    `SELECT customer_id, meter, sum(quantity) AS total FROM usage_events
     WHERE occurred_at >= $1 AND occurred_at < $2 GROUP BY customer_id, meter`,
    [start, end],
  )
  return new Map(rows.map(row => [`${row.customer_id}:${row.meter}`, Number(row.total)]))
}

How to check

A job rebuilds last month's totals from raw events and compares them with the stored totals. They match for every customer and meter. The app's database role cannot run UPDATE or DELETE on the usage events table.

Related rules

• PAY-56 Keep payment records for the legal period in Payments and billing
• USG-20 Let customers download the usage behind each invoice in Usage-based billing
• USG-21 Credit back usage your own fault caused in Usage-based billing