From Usage to Invoice: Building Real-Time Metered Billing Engines on ClickHouse Event Logs

For CTOs and engineering leaders at B2B SaaS companies moving from flat subscriptions to usage-based pricing, the billing system quietly becomes one of the most consequential pieces of infrastructure you own. Per-seat plans tolerate a lot of sloppiness. A monthly cron job, a spreadsheet and a Stripe subscription are enough. Metered pricing removes that tolerance, and three problems tend to appear together:

1. Correctness under retries. Usage events travel through SDKs, queues and collectors that all retry. A single duplicated event is a billing error, and a dropped one is lost revenue. Neither is acceptable at invoice time.
2. Real-time visibility expectations. Customers now expect a live "usage this period" view and spend alerts. Finance wants to know before month-end whether a large account is about to blow through a commit. A nightly batch cannot answer either.
3. Explainability and disputes. When an enterprise customer questions a line item, "the system says so" is not an answer. You need to trace every invoiced unit back to the raw events that produced it.

This article describes an architecture we consider a sound default for the middle band of the market: an append-only event log in ClickHouse, incremental rollups for real-time aggregation, and a separate, transactional rating and invoicing layer that treats ClickHouse as the source of usage truth but not of financial truth.

All numbers below are illustrative worked examples with stated assumptions, not measurements from a specific deployment.

1. The Design Principle: Separate Metering, Rating and Invoicing#

The most common architectural mistake is to let one system do everything. Metering, rating and invoicing have different consistency needs and should be separable.

The corollary is that every aggregate in ClickHouse must be reproducible from the raw log. If a rollup is wrong, you drop it and rebuild it. That property is what makes the whole system safe to operate.

2. The Event Contract#

Billing quality starts at the event schema. A minimal contract looks like this:

{
  400 font-semibold">class="text-emerald-300">"event_id": 400 font-semibold">class="text-emerald-300">"01J9ZK3X7Q5M2R8VWT4N6B1CDE",
  400 font-semibold">class="text-emerald-300">"tenant_id": 400 font-semibold">class="text-emerald-300">"ten_8f2a",
  400 font-semibold">class="text-emerald-300">"meter": 400 font-semibold">class="text-emerald-300">"api_requests",
  400 font-semibold">class="text-emerald-300">"quantity": 1,
  400 font-semibold">class="text-emerald-300">"occurred_at": 400 font-semibold">class="text-emerald-300">"2026-10-09T10:15:03.412Z",
  400 font-semibold">class="text-emerald-300">"dimensions": { 400 font-semibold">class="text-emerald-300">"region": 400 font-semibold">class="text-emerald-300">"eu-west-1", 400 font-semibold">class="text-emerald-300">"tier": 400 font-semibold">class="text-emerald-300">"premium" },
  400 font-semibold">class="text-emerald-300">"source": 400 font-semibold">class="text-emerald-300">"gateway-v3"
}

Rules we enforce at the edge:

• event_id is generated by the producer, not the collector. A ULID or UUIDv7 gives retries the same identity and keeps IDs roughly time-ordered. Without a producer-assigned ID there is no principled way to deduplicate.
• occurred_at is event time, distinct from ingest time. Billing periods are defined by when usage happened, so you must also record when it arrived. The difference is what lets you reason about late data.
• Dimensions are a bounded, allow-listed set. Unbounded dimension values create unbounded rollup cardinality, which becomes a cost and performance problem later.

Events enter through a durable queue (Kafka, Redpanda, or a managed equivalent) so the collector can acknowledge quickly and ClickHouse can ingest in large batches.

3. The Raw Event Table#

The raw table is the system of record for usage. It is append-only and partitioned by month so that old periods can be detached or moved to cheaper storage.

400 font-semibold">CREATE 400 font-semibold">TABLE usage_events
(
    tenant_id    LowCardinality(String),
    meter        LowCardinality(String),
    event_id     String,
    occurred_at  DateTime64(3, 400 font-semibold">class="text-emerald-300">'UTC'),
    ingested_at  DateTime64(3, 400 font-semibold">class="text-emerald-300">'UTC') DEFAULT now64(3),
    quantity     Decimal(18, 6),
    dimensions   400">Map(LowCardinality(String), String),
    source       LowCardinality(String)
)
ENGINE = ReplacingMergeTree(ingested_at)
PARTITION BY toYYYYMM(occurred_at)
400 font-semibold">ORDER BY (tenant_id, meter, toStartOfHour(occurred_at), event_id)
TTL toDateTime(occurred_at) + INTERVAL 24 MONTH
SETTINGS index_granularity = 8192;

Several choices here deserve explanation.

ORDER BY serves the dominant query. Almost every billing query filters by tenant and meter over a time range. Putting event_id last, after an hourly bucket, keeps tenant data physically clustered while still making (tenant_id, meter, hour, event_id) unique enough for ReplacingMergeTree to collapse duplicates with the same key.

ReplacingMergeTree is not a guarantee. This is the point teams most often misunderstand. Deduplication happens during background merges, which run at unpredictable times. A query issued before a merge can see duplicates. There are two defensible approaches:

• Insert-level protection: for replicated tables, ClickHouse deduplicates identical insert blocks by block hash within a configurable window, and you can supply an explicit insert_deduplication_token so a retried batch is recognized as the same insert. This protects against collector retries but not against two different batches containing the same event.
• Query-level protection when closing a period: read with FINAL, or aggregate with argMax/GROUP BY event_id, accepting the extra cost on a bounded, one-time query.

We use both. The first keeps the hot path clean, and the second makes the invoice-closing query authoritative regardless of merge timing.

4. Real-Time Aggregation with Materialized Views#

Scanning raw events for every dashboard refresh is wasteful. ClickHouse materialized views run on insert and write into a target table, which gives incremental rollups without a separate stream processor.

400 font-semibold">CREATE 400 font-semibold">TABLE usage_hourly
(
    tenant_id  LowCardinality(String),
    meter      LowCardinality(String),
    hour       DateTime(400 font-semibold">class="text-emerald-300">'UTC'),
    qty_state  AggregateFunction(sum, Decimal(18, 6)),
    ev_state   AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
400 font-semibold">ORDER BY (tenant_id, meter, hour);

400 font-semibold">CREATE MATERIALIZED VIEW usage_hourly_mv TO usage_hourly AS
400 font-semibold">SELECT
    tenant_id,
    meter,
    toStartOfHour(occurred_at) AS hour,
    sumState(quantity)         AS qty_state,
    countState()               AS ev_state
400 font-semibold">FROM usage_events
400 font-semibold">GROUP BY tenant_id, meter, hour;

Reading the rollup requires merging the partial states:

400 font-semibold">SELECT
    meter,
    sumMerge(qty_state) AS quantity,
    countMerge(ev_state) AS events
400 font-semibold">FROM usage_hourly
400 font-semibold">WHERE tenant_id = 400 font-semibold">class="text-emerald-300">'ten_8f2a'
  AND hour >= toStartOfMonth(now(400 font-semibold">class="text-emerald-300">'UTC'))
400 font-semibold">GROUP BY meter;

This query touches one row per tenant, meter and hour rather than every event. For a month it is at most 744 rows per meter per tenant, which returns in milliseconds and is cheap enough to power a live usage widget.

The caveat that matters for money#

A materialized view only sees data at insert time. It does not know about duplicates that a later merge removes from the raw table, because the duplicate was already counted in the rollup. That makes the hourly rollup a monitoring and estimation surface, not the invoicing source of truth. For the live customer dashboard a small overcount from duplicates is tolerable if you also reconcile. For invoices, the closing job recomputes from deduplicated raw events. Treating the rollup as advisory and the log as authoritative is what keeps the design honest.

5. Late and Out-of-Order Events#

Event time and ingest time diverge. Mobile clients buffer, regions fail over, and queues replay. Your billing period needs an explicit policy rather than hopeful defaults.

A pragmatic scheme:

1. Grace window. A period (say, a calendar month) is "soft closed" at its end but accepts events whose occurred_at falls in it for a defined window, for example 48 hours.
2. Hard close. After the window, the billing service snapshots the period: it runs the authoritative query, writes the result to PostgreSQL, and records a checksum of the inputs (event count and summed quantity per meter).
3. Post-close arrivals. Events that arrive after hard close are not silently discarded and not silently added to a closed invoice. They are written to a late usage report and billed as an adjustment on the next invoice, or credited, according to your contract terms.

The query that detects them is simple, because you stored both timestamps:

400 font-semibold">SELECT tenant_id, meter, sum(quantity) AS late_qty, count() AS late_events
400 font-semibold">FROM usage_events
400 font-semibold">WHERE occurred_at >= {period_start:DateTime64} AND occurred_at < {period_end:DateTime64}
  AND ingested_at  >= {hard_close_at:DateTime64}
400 font-semibold">GROUP BY tenant_id, meter;

6. The Rating Engine#

Rating turns quantities into currency. Keep it deterministic and versioned: given the same usage and the same plan version, it must always produce the same lines.

Common pricing models you will need to support:

Worked example#

Assumptions: a graduated plan with a 1,500 monthly platform fee, then API requests priced at 0.0020 for the first 1,000,000, 0.0015 for the next 9,000,000, and 0.0010 beyond 10,000,000. One tenant records 14,200,000 requests in the period.

Tier 1: 1,000,000 x $0.0020 = $2,000.00
Tier 2: 9,000,000 x $0.0015 = $13,500.00
Tier 3: 4,200,000 x $0.0010 = $4,200.00
Usage subtotal              = $19,700.00
Platform fee                = $1,500.00
Invoice subtotal            = $21,200.00

The rating function itself is a pure function of (usage, plan_version):

400 font-semibold">from decimal 400 font-semibold">import Decimal, ROUND_HALF_UP

400 font-semibold">def rate_graduated(quantity: Decimal, tiers: list[tuple[Decimal | None, Decimal]]) -> list[dict]:
    400 font-semibold">class="text-emerald-300">""400 font-semibold">class="text-emerald-300">"tiers: [(upper_bound_or_None, unit_price), ...] in ascending order."400 font-semibold">class="text-emerald-300">""
    lines, lower = [], Decimal(0)
    400 font-semibold">for upper, price in tiers:
        400 font-semibold">if quantity <= lower:
            break
        top = quantity 400 font-semibold">if upper is None 400 font-semibold">else min(quantity, upper)
        units = top - lower
        amount = (units * price).quantize(Decimal(400 font-semibold">class="text-emerald-300">"0.01"), ROUND_HALF_UP)
        lines.append({400 font-semibold">class="text-emerald-300">"400 font-semibold">from": lower, 400 font-semibold">class="text-emerald-300">"to": top, 400 font-semibold">class="text-emerald-300">"units": units, 400 font-semibold">class="text-emerald-300">"unit_price": price, 400 font-semibold">class="text-emerald-300">"amount": amount})
        lower = top
    400 font-semibold">return lines

Two operational rules matter more than the math. First, use decimal arithmetic end to end; binary floats will eventually produce a one-cent discrepancy that costs more to investigate than it was worth. Second, pin the plan version on the invoice line. When pricing changes, historical invoices must still be reproducible from the version that applied.

7. From Rated Usage to an Immutable Ledger#

When the billing service closes a period, it executes a short, transactional sequence in PostgreSQL:

BEGIN;
-- 1. 400">Record the input snapshot so the invoice is reproducible
400 font-semibold">INSERT INTO usage_snapshots (tenant_id, period, meter, quantity, event_count, input_checksum)
VALUES ($1, $2, $3, $4, $5, $6);

-- 2. Create the invoice and its lines, referencing plan_version
400 font-semibold">INSERT INTO invoices (id, tenant_id, period, status, plan_version) VALUES (...);
400 font-semibold">INSERT INTO invoice_lines (invoice_id, meter, from_qty, to_qty, unit_price, amount) VALUES (...);

-- 3. Append ledger entries; never 400 font-semibold">UPDATE or 400 font-semibold">DELETE them
400 font-semibold">INSERT INTO ledger_entries (tenant_id, invoice_id, kind, amount_minor, currency) VALUES (...);
COMMIT;

A unique constraint on (tenant_id, period) makes the close job idempotent: if it crashes and reruns, the second attempt fails cleanly instead of issuing a second invoice. Corrections are new ledger entries (credits, debit adjustments) that reference the original, so the audit trail always shows what was billed and what changed.

If you hand off collection to a payment provider, push the finished invoice lines (or the aggregate quantity) to it at this point. Stripe's Billing Meters, for example, accept meter events and are an option if you prefer to keep rating in Stripe, though you then lose some control over rating logic and event-level reconciliation. Purpose-built metering products such as Lago, OpenMeter and Metronome cover parts of this stack as well. Our general view is that buying makes sense when pricing is simple, and building the rating layer makes sense when pricing is a competitive lever you will iterate on frequently.

8. Reconciliation, Cost and Operations#

Reconcile continuously, not at month-end. A scheduled job should compare, per tenant and meter, the live rollup against a fresh deduplicated query over raw events, and alert when the delta exceeds a threshold you choose (a fraction of a percent is a reasonable starting point to tune). Also compare ingested event counts against producer-side counters if your SDKs emit them. Divergence between those numbers is how you discover a silent collector bug before a customer does.

Storage sizing, illustrative. Assume 200 million events per day and a compressed on-disk footprint of roughly 15 to 30 bytes per event after columnar compression and LowCardinality encoding (the real figure depends heavily on dimensions and event_id entropy, so measure on your own data). That is roughly 3 to 6 GB per day, or about 90 to 180 GB per month before replication. At that scale a modest replicated cluster is sufficient, and the TTL and partitioning shown above keep retention predictable.

Protect the write path. Batch inserts (thousands of rows per insert, not one at a time) and use async inserts or a Kafka-fed ingest path so ClickHouse sees fewer, larger parts. Too many small parts is the classic way to turn a healthy cluster into a struggling one.

Conclusion: A Decision Framework#

Use this checklist to decide where you stand.

• If you have under a few million billable events per month and simple pricing, a relational counter table plus a payment provider's metering is probably enough. Do not adopt ClickHouse for its own sake.
• If you are heading toward tens of millions of events per day, need real-time usage visibility, or sell contracts with commitments and tiered pricing, an event-log architecture pays for itself. Start with the raw table, the hourly rollup and the reconciliation job, in that order.
• If pricing changes often, invest in versioned plans and a pure rating function before optimizing storage.

The recurring theme is that real-time billing is less about speed than about provable correctness: every dollar on an invoice should be traceable to events, and every correction should leave a trail.

If you are designing or hardening a metered billing platform and want an independent review of your event schema, deduplication strategy or rating model, the knetwork engineering team can help you assess the architecture and plan a migration path. A short consultation is usually enough to identify the highest-risk gaps.