Dinesh/ Blog
systems

A metering ledger that survives a replay

Put event identity and usage aggregation in one transaction, then test rollback, duplicates and conflicting payloads.

12 min read
Checking device speech…
Lesson preparation & details

Level: intermediate

By the end, you should be able to

  • Implement atomic deduplication and aggregation
  • Reject a repeated ID with changed units
  • Prove rollback and replay behavior on a local SQLite fixture

Sources checked: . A source check does not change the publication or update date.

Editorial review: · What review means

In this article · 14 sections

The unsafe design is easy to draw: write an ID to Redis, then increment an analytical database. Crash between those writes and a retry may be suppressed even though no usage was counted. Reverse the writes and the same crash may double-count. Both stores can be individually reliable while their combined operation is wrong.

For a small system, start with a single transactional database and keep a raw event ledger plus a derived total. The invariant is: for each tenant, the total equals the sum of accepted unique event units. This lesson stores nonnegative integer usage units, not money or invoice policy.

One commit boundary

One commit boundary
Figure text equivalent — labels and relationships
  • Node “Authenticated tenant plus stable event ID” (square).
  • Node “Begin write transaction” (square).
  • Node “ID already exists?” (diamond).
  • Node “Commit and return duplicate” (square).
  • Node “Roll back and reject conflict” (square).
  • Node “Insert event” (square).
  • Node “Increment aggregate” (square).
  • Node “Commit both” (square).
  • “Authenticated tenant plus stable event ID” leads to “Begin write transaction”. Line: normal; ends: arrow point.
  • “Begin write transaction” leads to “ID already exists?”. Line: normal; ends: arrow point.
  • “ID already exists?” leads to “Commit and return duplicate”: same payload. Line: normal; ends: arrow point.
  • “ID already exists?” leads to “Roll back and reject conflict”: different payload. Line: normal; ends: arrow point.
  • “ID already exists?” leads to “Insert event”: new. Line: normal; ends: arrow point.
  • “Insert event” leads to “Increment aggregate”. Line: normal; ends: arrow point.
  • “Increment aggregate” leads to “Commit both”. Line: normal; ends: arrow point.

SQLite documents one simultaneous writer. BEGIN IMMEDIATE acquires the write transaction before the duplicate check, preventing a competing writer from slipping between this read and insert on this database. It may fail with SQLITE_BUSY; a real service needs a bounded retry policy. The function below owns its transaction and must not be called inside another caller-owned transaction.

Runnable failure injection

Copy this entire block. It uses only an in-memory database. The injected exception is a rollback test, not a power-loss durability test. Every SQL value is parameterized; the duplicate key includes the tenant.

import sqlite3
 
con = sqlite3.connect(":memory:", isolation_level=None)
con.executescript("""
CREATE TABLE events (
  tenant TEXT NOT NULL, event_id TEXT NOT NULL,
  units INTEGER NOT NULL CHECK (typeof(units) = 'integer' AND units >= 0),
  PRIMARY KEY (tenant, event_id)
);
CREATE TABLE totals (
  tenant TEXT PRIMARY KEY NOT NULL,
  units INTEGER NOT NULL CHECK (typeof(units) = 'integer' AND units >= 0)
);
""")
 
def apply(tenant, event_id, units, fail_after_insert=False):
    if (not isinstance(tenant, str) or not tenant
            or not isinstance(event_id, str) or not event_id
            or type(units) is not int or not 0 <= units <= 2**63 - 1):
        raise ValueError("nonempty string identities and signed-64-bit nonnegative units required")
    if con.in_transaction:
        raise RuntimeError("function owns its transaction")
    con.execute("BEGIN IMMEDIATE")
    try:
        row = con.execute(
            "SELECT units FROM events WHERE tenant=? AND event_id=?",
            (tenant, event_id)).fetchone()
        if row is not None:
            if row[0] != units:
                raise ValueError("event ID reused with different payload")
            con.execute("COMMIT")
            return "duplicate"
        con.execute("INSERT INTO events VALUES (?, ?, ?)",
                    (tenant, event_id, units))
        if fail_after_insert:
            raise RuntimeError("injected crash boundary")
        con.execute("""INSERT INTO totals VALUES (?, ?)
          ON CONFLICT(tenant) DO UPDATE SET units=totals.units+excluded.units
        """, (tenant, units))
        con.execute("COMMIT")
        return "applied"
    except BaseException:
        if con.in_transaction:
            con.execute("ROLLBACK")
        raise
 
try:
    assert apply("a", "e1", 5) == "applied"
    assert apply("a", "e1", 5) == "duplicate"
    try:
        apply("a", "e2", 7, fail_after_insert=True)
    except RuntimeError:
        pass
    assert con.execute("SELECT SUM(units) FROM events").fetchone()[0] == 5
    assert apply("a", "e2", 7) == "applied"
    try:
        apply("a", "e1", 99)
    except ValueError:
        pass
    else:
        raise AssertionError("conflicting replay accepted")
    assert apply("b", "e1", 3) == "applied"
    ledger = con.execute("SELECT tenant, SUM(units) FROM events GROUP BY tenant ORDER BY tenant").fetchall()
    totals = con.execute("SELECT tenant, units FROM totals ORDER BY tenant").fetchall()
    assert ledger == totals == [("a", 12), ("b", 3)]
    print(totals)
finally:
    con.close()

Why each detail matters

Catching every integrity error and calling it a duplicate would hide invalid data. SQLite's ON CONFLICT uniqueness handling is narrower than CHECK and NOT NULL validation. Here the duplicate branch compares the payload explicitly, and other exceptions roll back.

A retry after commit returns “duplicate” without adding usage again. A failure after inserting the event but before updating the total rolls back both changes. Tenant b can legitimately reuse the textual ID e1; the compound key keeps the identities distinct. A production event would also include operation, unit type and any other fields defining equality, not only the unit count.

If the aggregate is sent to another database later, publish it through a recoverable export/outbox protocol and treat that destination as a derived view. Do not move the two writes apart and retain the same correctness claim. Deleting deduplication records also changes the contract: after the ID expires, an old retry can look new. Retention and reconciliation require an explicit business policy.

Exercises

  1. What total should tenant a have after replaying both successful events ten times?
  2. Can a client prove usage by supplying its own unit count and tenant string?
  3. Does this program prove that an invoice is financially or legally correct?
Answers and reasoning
  1. Twelve units. The twenty attempts are retries of two identities, not twenty new operations.
  2. No. Production identity comes from authentication, and usage must be measured or validated at a trusted boundary. The strings are synthetic fixtures, not an authentication mechanism.
  3. No. Pricing versions, rounding, credits, tax, late events, invoice finalization, storage durability and audit policy are out of scope.

Limits

The input guard bounds each quantity to SQLite's signed 64-bit integer range. SQLite can promote overflowing integer addition to REAL even in an INTEGER-affinity column; the typeof(units) = 'integer' checks therefore reject an overflowing aggregate, rolling back the new event and leaving the previous exact total intact. A caller must handle that capacity error rather than endlessly retry the same oversized total. Integer units do not define currency or invoice policy. SQLite's single-writer design is appropriate for this teaching transaction, not a demonstrated high-throughput multi-region billing system. No filesystem crash, concurrent-process stress test or external queue was run.

Sources and verification boundary

Primary sources checked on 7 October 2026:

The numerical fixtures and Python programs are teaching examples, not measurements of a deployed service. Python blocks were executed locally; no cloud deployment or model inference was performed.

Previous: Replay is a feature; duplicate effects are a design choice. Track map.

Architecture: ingestion, ledger and derived aggregates

A usage event needs a stable identity, authenticated tenant, operation, measured quantity, unit, event time, ingestion time and schema version. Create the business identity at a trusted boundary before retries of that operation. A UUID lowers accidental collision risk; it does not prove that two differently named events are different billable actions. Conversely, clients must not reuse an ID with changed usage or invent their own trusted totals. The program rejects conflicting units for an existing tenant/ID pair for this reason.

Architecture: ingestion, ledger and derived aggregates
Figure text equivalent — labels and relationships
  • Node “Trusted usage measurement” (square).
  • Node “Durable source event or outbox” (square).
  • Node “Replayable transport” (square).
  • Node “Atomic event ledger and accepted effect” (square).
  • Node “Versioned analytical projection” (square).
  • Node “Reconciliation and audit” (square).
  • Node “Invoice calculation” (square).
  • “Trusted usage measurement” leads to “Durable source event or outbox”. Line: normal; ends: arrow point.
  • “Durable source event or outbox” leads to “Replayable transport”. Line: normal; ends: arrow point.
  • “Replayable transport” leads to “Atomic event ledger and accepted effect”. Line: normal; ends: arrow point.
  • “Atomic event ledger and accepted effect” leads to “Versioned analytical projection”. Line: normal; ends: arrow point.
  • “Atomic event ledger and accepted effect” leads to “Reconciliation and audit”. Line: normal; ends: arrow point.
  • “Versioned analytical projection” leads to “Invoice calculation”. Line: normal; ends: arrow point.
  • “Reconciliation and audit” leads to “Invoice calculation”. Line: normal; ends: arrow point.

1. The Ingestion Queue

Kafka is a transport here, not an unconditional no-loss guarantee. Producer acknowledgments, replication, in-sync-replica policy, retention and the failure model determine what an acknowledgment establishes. Acknowledging ingestion before a durable source write can lose usage in a crash. Where the usage operation already commits to a database, an outbox row in that same transaction can make publication recoverable. For operations outside that transaction, explain which authoritative log or reconciliation process recovers omissions.

Commit a consumer offset only after the ledger transaction succeeds. If the offset is committed first, a crash can skip an unrecorded effect. If the ledger commits first and the consumer crashes before the offset commit, replay is expected and the durable identity suppresses a second effect. Kafka 4.1 explicitly says exactly-once effects in external systems require cooperation with those systems; broker transactions do not include an arbitrary billing database by themselves.

2. The Metering Workers and Deduplication

The local invariant is stronger than “the ID is somewhere in Redis”: accepted identity and accepted usage share one commit boundary. The opening counterexample shows why independent deduplication and aggregation stores leave a crash gap. The retained SQLite fixture is a fully runnable single-database model, not a claim about a particular distributed database's isolation. A production equivalent must establish its uniqueness and transaction guarantees, bounded busy/conflict retries, and durability settings under the expected failures.

A worker seeing an existing identity with identical content returns the prior outcome. A different payload is an error to investigate, not a duplicate to silently accept. Include all equality-defining fields in this comparison, including unit type and operation. Keep raw accepted events so totals can be checked and rebuilt; an aggregate alone cannot explain which source operation created a charge.

3. The Aggregation Database

For the same tenant and minute, two accepted events of 7 and 5 units have total 12. Exporting a bare increment by 12 twice produces 24. Instead publish a projection identified by its grain, version and source boundary, and let the destination replace that version idempotently or consume uniquely identified component events. A later correction from 12 to 11 must not be confused with another increment of 11. Decide which versions may replace older ones.

Pre-aggregation reduces output rows but does not justify discarding the raw audit trail. Choose grain explicitly: tenant, product, unit, pricing version and accounting interval may all matter. Never combine bytes and requests in one total. Reconcile derived totals against the ledger by the same grain before finalization; a fast analytical engine is not by itself a uniqueness or accounting guarantee.

Precision vs. Scale in Deduplication

At 10,000 accepted events/s and a 24-hour retry horizon there are 864 million identities. Even an assumed 32 bytes per entry is 27.648 GB in decimal units before indexes, allocator and replication overhead. These are scenario inputs, not measured Redis memory costs. A TTL cache can accelerate recent duplicate lookup, but an expired entry means “not in this cache,” not “never billed.” Route older events through durable identity lookup or an explicit closed-period reconciliation policy. A Bloom-filter positive cannot authorize dropping an event because false positives are possible; check the authoritative ledger.

Bound both accepted event lateness and operational replay. A queue restored from an old backup can replay far older data than normal network delay. Retain identity evidence for the actual replay/correction horizon and applicable obligations. There is no universal 24-hour financial deduplication rule. Privacy and retention requirements need their own policy; an educational example does not prescribe indefinite personal-data retention.

Money, timestamps and finalized invoices

The fixture counts integer units, not currency. PostgreSQL 16 distinguishes exact numeric from inexact floating-point types. Decimal arithmetic still needs scale, precision, overflow limits and a rounding rule. Suppose two items cost 0.025 currency units each: rounding each to cents using half-up gives 0.03 + 0.03 = 0.06, whereas summing first gives 0.050, rounded to 0.05. Neither result is selected merely by choosing DECIMAL; the pricing contract must define the rounding stage. Avoid constructing a decimal from an already approximated float.

Event time can be untrusted or skewed too. Preserve both occurrence and receipt timestamps, validate plausible bounds, and define the billing timezone and period boundary. A worker's clock is not automatically the correct attribution clock, but neither is a client's claim. An event dated September 30 and received October 2 needs an explicit late-usage decision. Keep an auditable adjustment/credit policy for finalized invoices under the applicable contract and accounting rules; “always put it on the next invoice” is not a universal law.

Reconciliation exercise

A worker commits event e7 for 12 units, loses its acknowledgment and retries. An export consumer separately receives aggregate version 4 (12 units) twice, then version 5 (11 units). What state should each boundary retain? How should a changed replay of e7 be handled?

Ledger, projection and accounting answer

The ledger counts the identical e7 replay once. A reused e7 with different units is a conflict, not an update hidden as a retry; represent an authorized correction explicitly. The projection applies version 4 once and ends at 11 for version 5, not 12 + 12 + 11. Its version rule must also reject a delayed version 4 after version 5. A finalized invoice needs a traceable adjustment linked to the original usage, not silent historical mutation. This separates delivery attempts, accepted events, projection updates and financial decisions.

What to carry forward

Replay is survivable when identity and effect share a durable boundary. Correctness also requires detecting missing source events, rebuilding derived state and reconciling periods; deduplication alone only addresses repeated identities. The SQLite test proves rollback and replay behavior in memory, not power-loss survival, a Kafka deployment or legal compliance.

Additional source: PostgreSQL 16 numeric types for exact/inexact representation and rounding; SQLite datatypes and expression arithmetic for affinity and overflow promotion. Transport and transaction limits follow the Kafka 4.1 and SQLite sources cited above; SQLite pages are access-dated, not version-pinned.

Pause / Recall / Apply

Can you explain it without the page?

Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.

Stored in this browser only. No account, no sync. Clearing browser data removes your record.