DEV Community

Cover image for Building a Double-Entry Wallet & Idempotent Ledger in .NET and PostgreSQL
Harut Petrosyan
Harut Petrosyan

Posted on Edited on

Building a Double-Entry Wallet & Idempotent Ledger in .NET and PostgreSQL

Update (3 Nov 2026): The kit is now at v3.1.0. Its claims are backed by 162 integration tests that run against real PostgreSQL 16. Since the first version it has added EF Core migrations, business-reference uniqueness, append-only ledger triggers and money range checks. Two suggestions from the comments below are also in v3.1: a reconciliation query that pinpoints the exact ledger row where a chain breaks, and a least-privilege runtime database role. Section 5 shows real test results.

If you've ever built an in-app wallet, a SaaS credit system, or a payment ledger in .NET, you know that standard CRUD patterns break down quickly under concurrency.

A naive read-modify-write operation can suffer from race conditions and double-spending when multiple requests update the same wallet simultaneously:

wallet.Balance -= request.Amount;
await db.SaveChangesAsync();
Enter fullscreen mode Exit fullscreen mode

Optimistic concurrency using PostgreSQL's xmin can help detect conflicting updates, but under heavy contention, repeated DbUpdateConcurrencyException failures may become a problem.

In this article, I'll explore how to build a double-entry wallet and idempotent ledger using .NET, EF Core, and PostgreSQL, focusing on database locking, transaction consistency, currency precision, and duplicate request handling.

The goal is to make the system's financial invariants explicit and verify its behavior under concurrent withdrawals.


1. Double-Entry Accounting: SUM(Amount) == 0

In a double-entry ledger, money should never appear or disappear without a corresponding entry.

Each transaction contains at least two entries whose amounts balance to zero:

SUM(Amount) = 0   -- for every transaction
Enter fullscreen mode Exit fullscreen mode

To represent deposits and withdrawals without modeling external bank accounts directly in the schema, we can introduce a System Settlement Wallet for each currency, marked with IsSystem = true.

For example, a $100 deposit creates two ledger entries:

  • System settlement wallet: -100.0000
  • User wallet: +100.0000

The transaction balances to zero.

EF Core configuration

modelBuilder.Entity<Wallet>(b =>
{
    b.Property(w => w.Balance)
        .HasPrecision(18, 4);

    // Map PostgreSQL's system column xmin
    // as an optimistic concurrency token.
    b.Property(w => w.Version)
        .HasColumnName("xmin")
        .HasColumnType("xid")
        .ValueGeneratedOnAddOrUpdate()
        .IsConcurrencyToken();

    // User wallets cannot have negative balances.
    // System settlement wallets may.
    b.ToTable(t => t.HasCheckConstraint(
        "CK_Wallets_NonNegativeBalance",
        "\"Balance\" >= 0 OR \"IsSystem\" = TRUE"));

    // Allow at most one system wallet per currency.
    b.HasIndex(w => w.Currency)
        .IsUnique()
        .HasFilter("\"IsSystem\" = TRUE");
});
Enter fullscreen mode Exit fullscreen mode

You can inspect the global ledger balance with:

SELECT COALESCE(SUM("Amount"), 0)
FROM "LedgerEntries";
Enter fullscreen mode Exit fullscreen mode

For a fully balanced ledger, the result should be zero.

Important: A zero global sum is necessary, but not sufficient, to prove that every transaction is correct. Each transaction should also be validated independently, and the ledger should be reconciled against the relevant business operations.


2. Pessimistic Locking and Deterministic Lock Ordering

Consider two simultaneous transfers:

  • User A transfers money to User B.
  • User B transfers money to User A.

If each transaction locks its source wallet first and its destination wallet second, they may acquire locks in opposite orders and create a deadlock.

One approach is to acquire wallet locks in a deterministic order based on wallet ID.

For example:

SELECT *, xmin
FROM "Wallets"
WHERE "Id" IN (@fromWalletId, @toWalletId)
ORDER BY "Id"
FOR UPDATE;
Enter fullscreen mode Exit fullscreen mode

This approach aims to make competing transfers acquire the same wallet locks in the same order.

EF Core and Npgsql considerations

When using FromSqlInterpolated, EF Core may compose additional SQL around the supplied query. PostgreSQL system columns such as xmin require special care when mapping concurrency tokens.

An explicit projection can help ensure that the required column is available to the materializer:

var lockedWallets = await db.Wallets
    .FromSqlInterpolated(
        $"""
        SELECT *, xmin
        FROM "Wallets"
        WHERE "Id" IN ({fromWalletId}, {toWalletId})
        ORDER BY "Id"
        FOR UPDATE
        """)
    .ToListAsync(ct);
Enter fullscreen mode Exit fullscreen mode

Verify the generated SQL and actual behavior with your specific EF Core and Npgsql versions.

Deterministic ordering reduces a common source of deadlocks, but it does not guarantee that every possible database deadlock is eliminated. Other queries, constraints, and lock acquisition paths must also be considered.


3. Currency Precision and Rounding Protection

Even when PostgreSQL stores monetary values using numeric(18,4), the application must enforce the permitted precision for each currency.

For example:

  • USD: 2 decimal places
  • JPY: 0 decimal places
  • Other currencies: according to their defined minor-unit rules

Without validation, an API might accept an amount such as 0.00005 USD, even though that amount exceeds the currency's permitted precision.

A basic validation method could look like this:

public static void ValidateAmount(
    decimal amount,
    string currency)
{
    if (amount <= 0)
    {
        throw new DomainException(
            "Amount must be strictly positive.");
    }

    var minorUnits =
        CurrencyMinorUnits.GetValueOrDefault(currency, 2);

    if (decimal.Round(amount, minorUnits) != amount)
    {
        throw new DomainException(
            $"Amount '{amount}' exceeds allowed precision " +
            $"({minorUnits} decimals) for {currency}.");
    }
}
Enter fullscreen mode Exit fullscreen mode

In a production implementation, currency definitions should be explicit rather than relying on a default for unknown currencies.

Per-wallet ledger sequences

A wallet can also maintain a monotonically increasing NextSequence counter.

Ledger entries can enforce uniqueness using a composite index:

modelBuilder.Entity<LedgerEntry>()
    .HasIndex(e => new
    {
        e.WalletId,
        e.SequenceNumber
    })
    .IsUnique();
Enter fullscreen mode Exit fullscreen mode

This provides a deterministic per-wallet sequence for audit ordering and can help detect missing sequence numbers.

Sequence allocation must itself be concurrency-safe. Here the counter lives on the wallet row, which is already locked, and it rolls back together with the transaction. A failed operation therefore leaves no gap, unlike a PostgreSQL SEQUENCE. The test suite checks that every wallet's sequence is contiguous and that each entry's running balance matches the sum before it. v3.1 also ships this check as a read-only SQL query: it uses LAG over each wallet's entries and returns the exact row where a balance or sequence breaks, instead of a single pass or fail.


4. Atomic Idempotency

A common idempotency bug occurs when an application commits a wallet transaction and saves the corresponding idempotency record separately.

If the process crashes between those commits, the client may retry the request and execute the same business operation again.

A robust design should coordinate the idempotency record and the financial transaction within the same database transaction.

The intended workflow is:

  1. The idempotency filter begins a database transaction.
  2. The application reserves the idempotency key using a database uniqueness constraint.
  3. Concurrent requests using the same key are handled according to a defined conflict or replay policy.
  4. The wallet engine participates in the same database transaction.
  5. The response and ledger changes are committed together.
  6. If the transaction rolls back, the idempotency reservation and ledger changes roll back as well.

A unique constraint can prevent two transactions from successfully creating the same idempotency record.

For example, the logical uniqueness scope might include:

  • Idempotency key
  • Request path or operation
  • Tenant or authenticated principal, where appropriate

The exact scope depends on the API's contract.

What about retries?

The implementation should distinguish between:

  • The same key with the same request payload.
  • The same key with a different request payload.
  • Two concurrent requests using the same key.
  • A client retry after the server committed the transaction but failed to deliver the response.
  • A failed request that was rolled back.

For successful requests, the stored response should be replayed consistently. For failures, the application must define which responses are persisted and which failures allow a retry.

The critical invariant: The financial transaction and its idempotency record must not be independently committed if the design promises atomic idempotency.

When the client retries with a new key

An idempotency key protects only retries that reuse it. A client that times out and retries with a fresh key would still post the same payment twice. v3 adds a second guard: a business reference that is unique per wallet and operation type, enforced by a PostgreSQL unique index inside the same transaction. The second attempt gets a 409 and leaves no trace: no ledger entry, no balance change, and no stored key.


5. Testing Concurrent Withdrawals

A useful concurrency test starts with a wallet containing $100 and launches 50 concurrent withdrawal attempts, each requesting $10.

The expected outcome is:

  • 10 successful withdrawals.
  • 40 insufficient-funds failures.
  • A final wallet balance of zero.
  • No successful transaction that causes the user wallet to become negative.
  • A balanced ledger.

In v3 this is an automated integration test, not a manual endpoint. It runs through the real HTTP pipeline against a fresh PostgreSQL 16 database, with separate connections and a common start barrier, in three isolated rounds. Each round ends with exactly 10 successes, 40 insufficient-funds responses and a zero balance. An audit then checks every transaction and every wallet, including the settlement wallet.

The same suite covers the other scenarios this article describes:

  • 20 transfers in each direction between two wallets at once: all succeed, and both balances end where they started.
  • 20 identical requests racing with one idempotency key: one posting, and identical responses.
  • Two different payloads racing with one key: one succeeds, the other gets 422, and only the winner's amount moves.
  • Duplicate business references with different keys: one 200, the rest 409.
  • Concurrent first use of a currency's settlement wallet: exactly one settlement wallet is created.
  • Precision and range limits for USD and JPY, including amounts and balances outside numeric(18,4).
  • Direct UPDATE, DELETE and TRUNCATE on the ledger tables: rejected by triggers.

All 162 tests pass locally and in CI:

Test summary: total: 162, failed: 0, succeeded: 162, skipped: 0, duration: 122.7s
Enter fullscreen mode Exit fullscreen mode

Writing these tests was worth it beyond the green checkmarks. They exposed two real defects in the earlier version: a transfer path that could move money out of the settlement wallet, and oversized amounts that reached PostgreSQL and came back as a 500. Both are fixed, and the tests keep them fixed.

What the tests still don't cover

  • Process crashes or cancellations around the commit.
  • Wallet ownership and authorization.
  • Precision edge cases for currencies other than USD and JPY.
  • Every possible interleaving. Synchronized races increase contention, but they don't prove that no deadlock can ever happen.

6. Architecture and Production Considerations

A wallet ledger requires more than correct balance arithmetic.

Before using such a system for real financial operations, consider:

  • Authentication and authorization: Verify that callers are allowed to operate on the requested wallets. The kit doesn't do this yet; it is the most important gap to close before real use.
  • Database migrations: Manage schema evolution through EF Core migrations rather than relying on initial database creation. v3 does this, and serializes startup migrations across instances with an advisory lock.
  • Auditability: Preserve the history of financial operations and define correction and reversal procedures. v3 makes the ledger tables append-only with PostgreSQL triggers. Since a table owner or a superuser can disable triggers, v3.1 adds a second layer: migrations run as an owner role in a separate step, and the API connects as a runtime role that has no UPDATE, DELETE or TRUNCATE on the ledger tables. A test disables the triggers and confirms that the runtime role is still refused.
  • Observability: Add structured logs, correlation IDs, metrics, and distributed tracing where appropriate.
  • Operational reliability: Test transaction rollbacks, connection failures, timeouts, and retry behavior.
  • Reconciliation: Define how internal ledger records are compared with external payment or settlement records.
  • Security and compliance: Evaluate the requirements applicable to the actual deployment and business model.

A reference implementation can demonstrate important financial backend patterns, but it should not be treated as a complete production payment platform without additional verification and operational controls.


Get the Complete Source Code

If you want to explore the complete .NET and PostgreSQL wallet reference implementation, including its wallet engine, idempotency filter, EF Core configuration, Docker setup, and concurrency-testing workflow, you can find the project here:

👉 Bulletproof .NET Wallet & Idempotent Ledger Micro-Kit on Gumroad

Version 3.1.0 includes:

  • The double-entry ledger, with running balances and per-wallet sequences
  • Ordered PostgreSQL row locking for transfers
  • Atomic HTTP idempotency, plus business-reference uniqueness
  • Append-only ledger triggers, and currency precision and range checks
  • EF Core migrations, Docker Compose and a CI workflow
  • A reconciliation query that reports the exact row where a wallet's history breaks
  • Separate owner and least-privilege runtime database roles
  • 162 integration tests against real PostgreSQL
  • Decision records, a changelog and an upgrade guide for earlier buyers

Use it as a learning resource and reference implementation, and verify its guarantees against your own requirements before adapting it for real financial workloads.


Final Thoughts

Building a wallet is not just about updating a balance. It is about preserving invariants when requests arrive concurrently, processes fail, and clients retry operations.

The most important lesson is to make those invariants explicit, enforce them at the appropriate layers, and prove the expected behavior with reproducible tests.

That is what turns a simple wallet API into a useful exercise in financial backend engineering.

Top comments (8)

Collapse
 
lycore profile image
Lycore Development •

Sorting the wallet IDs before SELECT ... FOR UPDATE is the cleanest deadlock fix I know, and the xmin note about SELECT * in subqueries is a useful gotcha. For idempotency, a unique constraint on the idempotency key, inserted in the same transaction as the ledger entry, gives you a hard guarantee even if two retries race. Have you considered an append-only ledger table with the balance derived or cached, so you can audit every change?

Collapse
 
harut_petrosyan profile image
Harut Petrosyan •

Thanks! I agree — an append-only ledger is a strong approach for auditability and financial consistency.

My preferred design is to treat the ledger entries as the source of truth, while keeping wallet balances as cached or materialized values for fast reads. Every balance-changing operation should update the ledger and the cached balance atomically within the same database transaction.

The key challenge is ensuring that the cached balance can always be reconciled against the ledger, especially under concurrent transfers and retries. I’d also add periodic reconciliation checks to detect any inconsistencies.

For a production system, I’d rather make the ledger immutable and auditable than rely on the balance field alone. Thanks for bringing this up!

Collapse
 
lycore profile image
Lycore Development •

That split makes sense: the ledger as the immutable source of truth with the balance as a derived cache is much easier to audit. One concrete detail that helps is storing a running balance_after on each ledger row, so reconciliation can compare a single row against the cached value instead of re-summing everything. Thanks for the detailed answer!

Thread Thread
 
harut_petrosyan profile image
Harut Petrosyan •

Agreed, and the kit already stores it: every ledger entry carries BalanceAfter and a per-wallet SequenceNumber (unique per wallet), both written while the wallet row is locked. The cheap check is the wallet's cached balance against the BalanceAfter of its latest entry.

One caveat I ran into: that check trusts the chain. A wrong entry in the middle still passes if the last row matches. So I'd run both: the last-row check often, and a periodic full pass that confirms the running sum of amounts equals BalanceAfter on every row and that the sequence numbers have no gaps. The sequence is what makes a missing or reordered entry visible.

In the version I'm working on now (not in the published package yet), the ledger tables are also append-only at the database level: PostgreSQL triggers reject UPDATE, DELETE and TRUNCATE on them.

Thread Thread
 
lycore profile image
Lycore Development •

Thanks Harut, the periodic full pass is the right complement to the last-row check. Two cheap additions: a window function comparing each row's BalanceAfter to the previous BalanceAfter plus amount flags the exact row where the chain breaks, and a unique (wallet_id, sequence_number) index plus a gap query makes missing entries visible. Append-only triggers are a nice touch, and REVOKE UPDATE, DELETE on the app role makes it a second layer even if a trigger is dropped.

Thread Thread
 
harut_petrosyan profile image
Harut Petrosyan •

That’s a great point — especially the role-level permissions as a second line of defense.

The (WalletId, SequenceNumber) unique index is already in place, and the reconciliation pass verifies both sequence continuity and that each entry’s BalanceAfter matches the running sum.

I like the window-function idea because it makes the reconciliation diagnostic, not just boolean — instead of only knowing that the chain is inconsistent, we can identify the exact row where it diverged.

And agreed on REVOKE UPDATE, DELETE for the application role. The current version uses append-only PostgreSQL triggers, but combining that with least-privilege DB permissions would make the boundary much stronger.

I’ll likely add both as the next hardening step. Thanks for the suggestion!

Collapse
 
williamsj04 profile image
Jessica Williams •

The distinction between a rolled-back request and a request that committed but never delivered its response is the part that tends to cause duplicate postings in practice. If the wallet call sits inside a larger flow that also notifies an external system, it may be worth storing that downstream reference alongside the idempotency record, so a replay can return the original outcome instead of triggering a second call.

Collapse
 
harut_petrosyan profile image
Harut Petrosyan •

Thanks, that's exactly the gap that matters in practice.

In the kit, the two cases are handled differently. If the request rolls back, the idempotency record rolls back with it, so the same key can be retried safely. If it commits but the response is lost, the stored status and body were committed in the same transaction as the ledger entries, so a retry with the same key gets the original response back (with an X-Idempotent-Replayed header) and no second posting.

What it doesn't cover is your case: a side effect outside the database. I agree the downstream reference belongs with the record, with one addition. Saving it after the external call still leaves a window where the call succeeded and the process died before the reference was stored. The pattern I'd use is an outbox: write the intent in the same transaction as the posting, send it from a dispatcher that passes a key derived from the operation as the downstream system's own idempotency key, and store the downstream reference once it's acknowledged. A replay then returns the stored outcome, and a resend after a crash is deduplicated on the other side. That only works if the downstream API accepts an idempotency key, so it's worth checking that first.

In the version I'm working on now (not in the published package yet), I've also added a uniqueness check on the business reference per wallet and operation type. It covers the other common case: a client that retries with a new idempotency key.