<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Harut Petrosyan</title>
    <description>The latest articles on DEV Community by Harut Petrosyan (@harut_petrosyan).</description>
    <link>https://dev.to/harut_petrosyan</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4209941%2F3f87e6d6-186d-44a6-96cc-b25fad3d9ceb.jpg</url>
      <title>DEV Community: Harut Petrosyan</title>
      <link>https://dev.to/harut_petrosyan</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/harut_petrosyan"/>
    <language>en</language>
    <item>
      <title>162 Integration Tests Found 3 Real Bugs in My .NET Wallet Kit</title>
      <dc:creator>Harut Petrosyan</dc:creator>
      <pubDate>Tue, 03 Nov 2026 10:24:15 +0000</pubDate>
      <link>https://dev.to/harut_petrosyan/162-integration-tests-found-3-real-bugs-in-my-net-wallet-kit-34j8</link>
      <guid>https://dev.to/harut_petrosyan/162-integration-tests-found-3-real-bugs-in-my-net-wallet-kit-34j8</guid>
      <description>&lt;p&gt;The first version of my wallet kit had a stress-test endpoint. You called it, 50 threads tried to withdraw $10 from a $100 wallet, and it answered &lt;code&gt;"passed": true&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;That endpoint was honest about the one scenario it checked. The problem was everything it didn't check. So I replaced it with a real integration suite and one rule: &lt;strong&gt;write the test first, watch it fail in CI, then fix the code.&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;The suite now has 162 tests. Along the way it found three bugs that the demo never could. Here they are, with the code.&lt;/p&gt;

&lt;h2&gt;
  
  
  The setup
&lt;/h2&gt;

&lt;p&gt;Every test gets its own fresh PostgreSQL 16 database. No in-memory provider, no mocks.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Requests go through the real HTTP pipeline with &lt;code&gt;WebApplicationFactory&lt;/code&gt;, including the idempotency filter.&lt;/li&gt;
&lt;li&gt;Connection pooling is off, so concurrent requests really use separate connections.&lt;/li&gt;
&lt;li&gt;Races start behind a common barrier, to push requests into the same moment.&lt;/li&gt;
&lt;li&gt;After every scenario, an &lt;code&gt;Audit&lt;/code&gt; helper checks every transaction (two entries, distinct wallets, zero sum, one currency) and every wallet: its balance equals the sum of its entries, &lt;code&gt;BalanceAfter&lt;/code&gt; is the running sum, and sequence numbers have no gaps. That includes the settlement wallets.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The audit is the important part. A global &lt;code&gt;SUM(Amount) = 0&lt;/code&gt; can hide a transaction that is wrong in a way that balances out somewhere else.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bug 1: A transfer could move money out of the settlement wallet
&lt;/h2&gt;

&lt;p&gt;The kit models deposits and withdrawals as transfers between a user wallet and a per-currency system settlement wallet. Normal wallets cannot go negative. The settlement wallet can, because it represents money outside the system:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToTable&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasCheckConstraint&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="s"&gt;"CK_Wallets_NonNegativeBalance"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s"&gt;"\"Balance\" &amp;gt;= 0 OR \"IsSystem\" = TRUE"&lt;/span&gt;&lt;span class="p"&gt;));&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Deposits and withdrawals looked up the user wallet with &lt;code&gt;!w.IsSystem&lt;/code&gt;. The transfer path did not check &lt;code&gt;IsSystem&lt;/code&gt; on either side:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;fromWallet&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="n"&gt;lockedWallets&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;FirstOrDefault&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt; &lt;span class="p"&gt;==&lt;/span&gt; &lt;span class="n"&gt;fromWalletId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;??&lt;/span&gt; &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DomainException&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;$"Source wallet &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;fromWalletId&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="s"&gt; not found."&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;404&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;So a transfer &lt;strong&gt;from&lt;/strong&gt; the settlement wallet to a user created a user balance of any size, recorded as a &lt;code&gt;Transfer&lt;/code&gt;. A transfer &lt;strong&gt;to&lt;/strong&gt; the settlement wallet was really a withdrawal, also recorded as a &lt;code&gt;Transfer&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;The public API didn't expose settlement wallet IDs, so you couldn't reach this without knowing one. That was luck, not a control. Any admin screen or report that shows a counterparty ID would have opened it.&lt;/p&gt;

&lt;p&gt;It was found by reading the code during a review. The tests came first: a transfer in each direction with the settlement wallet's ID read from the database. In CI both failed with &lt;code&gt;Expected 404; got 200&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;The first fix rejected system wallets in transfers. A second change turned it into an allow-list, because a deny-list breaks the day someone adds a &lt;code&gt;Fee&lt;/code&gt; or &lt;code&gt;Adjustment&lt;/code&gt; transaction type:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="c1"&gt;// New transaction types must explicitly opt in to settlement access.&lt;/span&gt;
&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;allowsSystemWallet&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="n"&gt;type&lt;/span&gt; &lt;span class="k"&gt;is&lt;/span&gt; &lt;span class="n"&gt;TransactionType&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Deposit&lt;/span&gt; &lt;span class="n"&gt;or&lt;/span&gt; &lt;span class="n"&gt;TransactionType&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Withdrawal&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;fromWallet&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="n"&gt;lockedWallets&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;FirstOrDefault&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Id&lt;/span&gt; &lt;span class="p"&gt;==&lt;/span&gt; &lt;span class="n"&gt;fromWalletId&lt;/span&gt; &lt;span class="p"&gt;&amp;amp;&amp;amp;&lt;/span&gt; &lt;span class="p"&gt;(!&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;IsSystem&lt;/span&gt; &lt;span class="p"&gt;||&lt;/span&gt; &lt;span class="n"&gt;allowsSystemWallet&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
    &lt;span class="p"&gt;??&lt;/span&gt; &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DomainException&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;$"Source wallet &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;fromWalletId&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="s"&gt; not found."&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;404&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The response is the same 404 as a missing wallet, so the API doesn't confirm that an ID belongs to a settlement wallet.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bug 2: Big numbers came back as a 500
&lt;/h2&gt;

&lt;p&gt;Amounts are stored as &lt;code&gt;numeric(18,4)&lt;/code&gt;, so the largest value is 14 integer digits. The code validated currency precision (two decimals for USD, none for JPY) but never checked the range.&lt;/p&gt;

&lt;p&gt;An amount, or a resulting balance, beyond &lt;code&gt;numeric(18,4)&lt;/code&gt; went all the way to PostgreSQL. It failed there with error &lt;code&gt;22003&lt;/code&gt; (numeric field overflow), and the client got a 500. The settlement wallet has no lower bound, so its balance can leave the range too. A related case: an oversized withdrawal came back as "insufficient funds", which was the wrong reason.&lt;/p&gt;

&lt;p&gt;The edge-case tests covered precision, trailing zeros, zero and negative amounts, unsupported and lower-case currencies, the largest valid amount, and values past the limit. On the first CI run, 17 of them failed.&lt;/p&gt;

&lt;p&gt;The fix validates the amount, then checks the resulting balance before adding. The check is written as a subtraction, so even an extreme &lt;code&gt;decimal&lt;/code&gt; can't overflow during the check itself:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="k"&gt;public&lt;/span&gt; &lt;span class="k"&gt;const&lt;/span&gt; &lt;span class="kt"&gt;decimal&lt;/span&gt; &lt;span class="n"&gt;MaximumBalanceMagnitude&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="m"&gt;99999999999999.9999m&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;signedAmount&lt;/span&gt; &lt;span class="p"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;MaximumBalanceMagnitude&lt;/span&gt; &lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="n"&gt;Balance&lt;/span&gt; &lt;span class="p"&gt;||&lt;/span&gt;
    &lt;span class="n"&gt;signedAmount&lt;/span&gt; &lt;span class="p"&gt;&amp;lt;&lt;/span&gt; &lt;span class="p"&gt;-&lt;/span&gt;&lt;span class="n"&gt;MaximumBalanceMagnitude&lt;/span&gt; &lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="n"&gt;Balance&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DomainException&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;"Resulting wallet balance exceeds the supported range."&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Now these are 400s. Balance and sequence don't change, nothing is written, and the idempotency key can be reused after a correction.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bug 3: Two instances migrating at once
&lt;/h2&gt;

&lt;p&gt;This one surprised me most, because it came from code I thought was already covered.&lt;/p&gt;

&lt;p&gt;When the kit moved from &lt;code&gt;EnsureCreated&lt;/code&gt; to EF Core migrations, I followed the documentation: since EF Core 9, &lt;code&gt;Migrate&lt;/code&gt; takes a database-wide lock, so concurrent instances are protected. I added a test that raced two migrators on an empty database. It passed.&lt;/p&gt;

&lt;p&gt;Then I added a &lt;strong&gt;second&lt;/strong&gt; migration, and the same test failed. One migrator tried to add a column that the other had already added.&lt;/p&gt;

&lt;p&gt;The diagnosis from the failure: with EF Core 10.0.4 and Npgsql 10.0.3, migrations commit one by one, and the provider's lock on the history table lasts only for a migration's transaction. Between two migrations another migrator can read stale history and start applying work that's already done. With one migration there is no "between", so the test passed.&lt;/p&gt;

&lt;p&gt;The fix holds a session-level advisory lock across the whole migration call:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Database&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;OpenConnectionAsync&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;ct&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;span class="k"&gt;try&lt;/span&gt;
&lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Database&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ExecuteSqlRawAsync&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;"SELECT pg_advisory_lock(71853240619243801)"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ct&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Database&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;MigrateAsync&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;ct&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="k"&gt;finally&lt;/span&gt;
&lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="c1"&gt;// Unlock, then close the connection (a crash releases the lock with the session).&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The lesson I took: &lt;strong&gt;a concurrency test that passes with one migration says nothing about two.&lt;/strong&gt; It's worth re-running such tests whenever their inputs grow, not only when the code changes.&lt;/p&gt;

&lt;p&gt;In v3.1 migrations also moved out of application startup into a separate &lt;code&gt;--migrate&lt;/code&gt; step that runs as an owner database role. The API now connects as a least-privilege runtime role and refuses to start while migrations are pending. The advisory lock still guards the migrate step.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Update, 9 Nov 2026.&lt;/strong&gt; A reader asked whether the suite covers one migrator failing while another waits for the advisory lock. It didn't, so I added two integration tests. They are in the repository now (164 tests) and will ship with the next kit release:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;A failing migrator releases the lock to the one waiting behind it.&lt;/strong&gt; A gate connection holds the advisory lock while two migrators queue behind it on a database with incompatible history. The test first confirms in &lt;code&gt;pg_locks&lt;/code&gt; that both are waiting, then opens the gate. Both migrators must fail with the same clear error, schema and data must stay unchanged, and the lock must be free afterwards.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A waiting migrator takes over when the lock holder dies.&lt;/strong&gt; The test kills the backend that holds the lock with &lt;code&gt;pg_terminate_backend&lt;/code&gt;. PostgreSQL releases the session lock, and the waiting migrator applies every migration exactly once.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Neither test found a new bug, which is the answer I was hoping for. Thanks to Bob's Media for the question.&lt;/p&gt;

&lt;h2&gt;
  
  
  What changed in how I work
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;A failing test before the fix.&lt;/strong&gt; A bug fix starts with a test that I've watched fail, in CI where possible, and only then does the fix go in. A test I never saw fail doesn't prove much.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Claims point to tests.&lt;/strong&gt; The README no longer says "deadlock-free" or "zero double-spending". Each guarantee names its mechanism, the tests behind it, and what those tests don't show.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Limits are written down.&lt;/strong&gt; The tests don't cover crash recovery (beyond a migrator dying while it holds the lock), authorization (the kit has none yet), or every possible interleaving. Synchronized races increase contention, but they don't prove no deadlock can ever happen.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Thanks to the comments
&lt;/h2&gt;

&lt;p&gt;Two hardening steps in v3.1 came from the discussion under &lt;a href="https://dev.to/harut_petrosyan/how-i-handle-concurrency-safe-wallets-deadlock-free-transfers-and-idempotency-in-net-postgresql-500g"&gt;my previous article&lt;/a&gt;:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;a read-only reconciliation query that uses &lt;code&gt;LAG&lt;/code&gt; to report the exact ledger row where a wallet's chain breaks;&lt;/li&gt;
&lt;li&gt;a runtime database role without UPDATE, DELETE or TRUNCATE on the ledger, so history stays protected even if the append-only triggers are disabled.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;A test now disables the triggers as the owner and confirms that the runtime role is still refused.&lt;/p&gt;

&lt;h2&gt;
  
  
  The kit
&lt;/h2&gt;

&lt;p&gt;&lt;a href="https://harutpetrosyan.gumroad.com/l/dotnet-wallet-kit" rel="noopener noreferrer"&gt;BulletproofWalletKit&lt;/a&gt; is the full source: the .NET 10 and PostgreSQL wallet engine, the 162-test suite, migrations, Docker Compose, decision records and an upgrade guide.&lt;/p&gt;

&lt;p&gt;What's the bug your own tests found that you'd never have caught by hand? I'd like to hear it in the comments.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was drafted with AI assistance. The code, the bugs and the test results are from the kit's repository, and I reviewed every claim.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>dotnet</category>
      <category>csharp</category>
      <category>postgres</category>
      <category>testing</category>
    </item>
    <item>
      <title>Building a Double-Entry Wallet &amp; Idempotent Ledger in .NET and PostgreSQL</title>
      <dc:creator>Harut Petrosyan</dc:creator>
      <pubDate>Tue, 27 Oct 2026 05:10:57 +0000</pubDate>
      <link>https://dev.to/harut_petrosyan/how-i-handle-concurrency-safe-wallets-deadlock-free-transfers-and-idempotency-in-net-postgresql-500g</link>
      <guid>https://dev.to/harut_petrosyan/how-i-handle-concurrency-safe-wallets-deadlock-free-transfers-and-idempotency-in-net-postgresql-500g</guid>
      <description>&lt;blockquote&gt;
&lt;p&gt;&lt;strong&gt;Update (3 Nov 2026):&lt;/strong&gt; 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.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;A naive read-modify-write operation can suffer from &lt;strong&gt;race conditions and double-spending&lt;/strong&gt; when multiple requests update the same wallet simultaneously:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="n"&gt;wallet&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Balance&lt;/span&gt; &lt;span class="p"&gt;-=&lt;/span&gt; &lt;span class="n"&gt;request&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Amount&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;SaveChangesAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Optimistic concurrency using PostgreSQL's &lt;code&gt;xmin&lt;/code&gt; can help detect conflicting updates, but under heavy contention, repeated &lt;code&gt;DbUpdateConcurrencyException&lt;/code&gt; failures may become a problem.&lt;/p&gt;

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

&lt;p&gt;The goal is to make the system's financial invariants explicit and verify its behavior under concurrent withdrawals.&lt;/p&gt;




&lt;h2&gt;
  
  
  1. Double-Entry Accounting: &lt;code&gt;SUM(Amount) == 0&lt;/code&gt;
&lt;/h2&gt;

&lt;p&gt;In a double-entry ledger, money should never appear or disappear without a corresponding entry.&lt;/p&gt;

&lt;p&gt;Each transaction contains at least two entries whose amounts balance to zero:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;SUM(Amount) = 0   -- for every transaction
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;To represent deposits and withdrawals without modeling external bank accounts directly in the schema, we can introduce a &lt;strong&gt;System Settlement Wallet&lt;/strong&gt; for each currency, marked with &lt;code&gt;IsSystem = true&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;For example, a $100 deposit creates two ledger entries:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;System settlement wallet: &lt;code&gt;-100.0000&lt;/code&gt;
&lt;/li&gt;
&lt;li&gt;User wallet: &lt;code&gt;+100.0000&lt;/code&gt;
&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The transaction balances to zero.&lt;/p&gt;

&lt;h3&gt;
  
  
  EF Core configuration
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="n"&gt;modelBuilder&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Entity&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;Wallet&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;(&lt;/span&gt;&lt;span class="n"&gt;b&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt;
&lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Property&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Balance&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasPrecision&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="m"&gt;18&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;4&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

    &lt;span class="c1"&gt;// Map PostgreSQL's system column xmin&lt;/span&gt;
    &lt;span class="c1"&gt;// as an optimistic concurrency token.&lt;/span&gt;
    &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Property&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Version&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasColumnName&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;"xmin"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasColumnType&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;"xid"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ValueGeneratedOnAddOrUpdate&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;IsConcurrencyToken&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;

    &lt;span class="c1"&gt;// User wallets cannot have negative balances.&lt;/span&gt;
    &lt;span class="c1"&gt;// System settlement wallets may.&lt;/span&gt;
    &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToTable&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasCheckConstraint&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="s"&gt;"CK_Wallets_NonNegativeBalance"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="s"&gt;"\"Balance\" &amp;gt;= 0 OR \"IsSystem\" = TRUE"&lt;/span&gt;&lt;span class="p"&gt;));&lt;/span&gt;

    &lt;span class="c1"&gt;// Allow at most one system wallet per currency.&lt;/span&gt;
    &lt;span class="n"&gt;b&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasIndex&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Currency&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;IsUnique&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
        &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasFilter&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s"&gt;"\"IsSystem\" = TRUE"&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;span class="p"&gt;});&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You can inspect the global ledger balance with:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;COALESCE&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nv"&gt;"Amount"&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nv"&gt;"LedgerEntries"&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For a fully balanced ledger, the result should be zero.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Important:&lt;/strong&gt; 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.&lt;/p&gt;




&lt;h2&gt;
  
  
  2. Pessimistic Locking and Deterministic Lock Ordering
&lt;/h2&gt;

&lt;p&gt;Consider two simultaneous transfers:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;User A transfers money to User B.&lt;/li&gt;
&lt;li&gt;User B transfers money to User A.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If each transaction locks its source wallet first and its destination wallet second, they may acquire locks in opposite orders and create a deadlock.&lt;/p&gt;

&lt;p&gt;One approach is to acquire wallet locks in a deterministic order based on wallet ID.&lt;/p&gt;

&lt;p&gt;For example:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;xmin&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="nv"&gt;"Wallets"&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="nv"&gt;"Id"&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;fromWalletId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;toWalletId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="nv"&gt;"Id"&lt;/span&gt;
&lt;span class="k"&gt;FOR&lt;/span&gt; &lt;span class="k"&gt;UPDATE&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This approach aims to make competing transfers acquire the same wallet locks in the same order.&lt;/p&gt;

&lt;h3&gt;
  
  
  EF Core and Npgsql considerations
&lt;/h3&gt;

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

&lt;p&gt;An explicit projection can help ensure that the required column is available to the materializer:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;lockedWallets&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Wallets&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;FromSqlInterpolated&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="s"&gt;$"""
&lt;/span&gt;        &lt;span class="n"&gt;SELECT&lt;/span&gt; &lt;span class="p"&gt;*,&lt;/span&gt; &lt;span class="n"&gt;xmin&lt;/span&gt;
        &lt;span class="n"&gt;FROM&lt;/span&gt; &lt;span class="s"&gt;"Wallets"&lt;/span&gt;
        &lt;span class="n"&gt;WHERE&lt;/span&gt; &lt;span class="s"&gt;"Id"&lt;/span&gt; &lt;span class="nf"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;({&lt;/span&gt;&lt;span class="n"&gt;fromWalletId&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;toWalletId&lt;/span&gt;&lt;span class="p"&gt;})&lt;/span&gt;
        &lt;span class="n"&gt;ORDER&lt;/span&gt; &lt;span class="n"&gt;BY&lt;/span&gt; &lt;span class="s"&gt;"Id"&lt;/span&gt;
        &lt;span class="n"&gt;FOR&lt;/span&gt; &lt;span class="n"&gt;UPDATE&lt;/span&gt;
        &lt;span class="s"&gt;""")
&lt;/span&gt;    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;ct&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Verify the generated SQL and actual behavior with your specific EF Core and Npgsql versions.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;




&lt;h2&gt;
  
  
  3. Currency Precision and Rounding Protection
&lt;/h2&gt;

&lt;p&gt;Even when PostgreSQL stores monetary values using &lt;code&gt;numeric(18,4)&lt;/code&gt;, the application must enforce the permitted precision for each currency.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;USD: 2 decimal places&lt;/li&gt;
&lt;li&gt;JPY: 0 decimal places&lt;/li&gt;
&lt;li&gt;Other currencies: according to their defined minor-unit rules&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Without validation, an API might accept an amount such as &lt;code&gt;0.00005 USD&lt;/code&gt;, even though that amount exceeds the currency's permitted precision.&lt;/p&gt;

&lt;p&gt;A basic validation method could look like this:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="k"&gt;public&lt;/span&gt; &lt;span class="k"&gt;static&lt;/span&gt; &lt;span class="k"&gt;void&lt;/span&gt; &lt;span class="nf"&gt;ValidateAmount&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="kt"&gt;decimal&lt;/span&gt; &lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="kt"&gt;string&lt;/span&gt; &lt;span class="n"&gt;currency&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount&lt;/span&gt; &lt;span class="p"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="m"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;
        &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DomainException&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="s"&gt;"Amount must be strictly positive."&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;

    &lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;minorUnits&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt;
        &lt;span class="n"&gt;CurrencyMinorUnits&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;GetValueOrDefault&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;currency&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;2&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kt"&gt;decimal&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;minorUnits&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;!=&lt;/span&gt; &lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;
        &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DomainException&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
            &lt;span class="s"&gt;$"Amount '&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="s"&gt;' exceeds allowed precision "&lt;/span&gt; &lt;span class="p"&gt;+&lt;/span&gt;
            &lt;span class="s"&gt;$"(&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;minorUnits&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="s"&gt; decimals) for &lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;currency&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="s"&gt;."&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In a production implementation, currency definitions should be explicit rather than relying on a default for unknown currencies.&lt;/p&gt;

&lt;h3&gt;
  
  
  Per-wallet ledger sequences
&lt;/h3&gt;

&lt;p&gt;A wallet can also maintain a monotonically increasing &lt;code&gt;NextSequence&lt;/code&gt; counter.&lt;/p&gt;

&lt;p&gt;Ledger entries can enforce uniqueness using a composite index:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="n"&gt;modelBuilder&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Entity&lt;/span&gt;&lt;span class="p"&gt;&amp;lt;&lt;/span&gt;&lt;span class="n"&gt;LedgerEntry&lt;/span&gt;&lt;span class="p"&gt;&amp;gt;()&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;HasIndex&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt;
    &lt;span class="p"&gt;{&lt;/span&gt;
        &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;WalletId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;SequenceNumber&lt;/span&gt;
    &lt;span class="p"&gt;})&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;IsUnique&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This provides a deterministic per-wallet sequence for audit ordering and can help detect missing sequence numbers.&lt;/p&gt;

&lt;p&gt;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 &lt;code&gt;SEQUENCE&lt;/code&gt;. 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 &lt;code&gt;LAG&lt;/code&gt; over each wallet's entries and returns the exact row where a balance or sequence breaks, instead of a single pass or fail.&lt;/p&gt;




&lt;h2&gt;
  
  
  4. Atomic Idempotency
&lt;/h2&gt;

&lt;p&gt;A common idempotency bug occurs when an application commits a wallet transaction and saves the corresponding idempotency record separately.&lt;/p&gt;

&lt;p&gt;If the process crashes between those commits, the client may retry the request and execute the same business operation again.&lt;/p&gt;

&lt;p&gt;A robust design should coordinate the idempotency record and the financial transaction within the same database transaction.&lt;/p&gt;

&lt;p&gt;The intended workflow is:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;The idempotency filter begins a database transaction.&lt;/li&gt;
&lt;li&gt;The application reserves the idempotency key using a database uniqueness constraint.&lt;/li&gt;
&lt;li&gt;Concurrent requests using the same key are handled according to a defined conflict or replay policy.&lt;/li&gt;
&lt;li&gt;The wallet engine participates in the same database transaction.&lt;/li&gt;
&lt;li&gt;The response and ledger changes are committed together.&lt;/li&gt;
&lt;li&gt;If the transaction rolls back, the idempotency reservation and ledger changes roll back as well.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;A unique constraint can prevent two transactions from successfully creating the same idempotency record.&lt;/p&gt;

&lt;p&gt;For example, the logical uniqueness scope might include:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Idempotency key&lt;/li&gt;
&lt;li&gt;Request path or operation&lt;/li&gt;
&lt;li&gt;Tenant or authenticated principal, where appropriate&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The exact scope depends on the API's contract.&lt;/p&gt;

&lt;h3&gt;
  
  
  What about retries?
&lt;/h3&gt;

&lt;p&gt;The implementation should distinguish between:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;The same key with the same request payload.&lt;/li&gt;
&lt;li&gt;The same key with a different request payload.&lt;/li&gt;
&lt;li&gt;Two concurrent requests using the same key.&lt;/li&gt;
&lt;li&gt;A client retry after the server committed the transaction but failed to deliver the response.&lt;/li&gt;
&lt;li&gt;A failed request that was rolled back.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The critical invariant:&lt;/strong&gt; The financial transaction and its idempotency record must not be independently committed if the design promises atomic idempotency.&lt;/p&gt;

&lt;h3&gt;
  
  
  When the client retries with a new key
&lt;/h3&gt;

&lt;p&gt;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.&lt;/p&gt;




&lt;h2&gt;
  
  
  5. Testing Concurrent Withdrawals
&lt;/h2&gt;

&lt;p&gt;A useful concurrency test starts with a wallet containing $100 and launches 50 concurrent withdrawal attempts, each requesting $10.&lt;/p&gt;

&lt;p&gt;The expected outcome is:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;10 successful withdrawals.&lt;/li&gt;
&lt;li&gt;40 insufficient-funds failures.&lt;/li&gt;
&lt;li&gt;A final wallet balance of zero.&lt;/li&gt;
&lt;li&gt;No successful transaction that causes the user wallet to become negative.&lt;/li&gt;
&lt;li&gt;A balanced ledger.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;The same suite covers the other scenarios this article describes:&lt;/p&gt;

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

&lt;p&gt;All 162 tests pass locally and in CI:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;Test summary: total: 162, failed: 0, succeeded: 162, skipped: 0, duration: 122.7s
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;h3&gt;
  
  
  What the tests still don't cover
&lt;/h3&gt;

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




&lt;h2&gt;
  
  
  6. Architecture and Production Considerations
&lt;/h2&gt;

&lt;p&gt;A wallet ledger requires more than correct balance arithmetic.&lt;/p&gt;

&lt;p&gt;Before using such a system for real financial operations, consider:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Authentication and authorization:&lt;/strong&gt; 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.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Database migrations:&lt;/strong&gt; 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.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Auditability:&lt;/strong&gt; 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.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Observability:&lt;/strong&gt; Add structured logs, correlation IDs, metrics, and distributed tracing where appropriate.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Operational reliability:&lt;/strong&gt; Test transaction rollbacks, connection failures, timeouts, and retry behavior.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Reconciliation:&lt;/strong&gt; Define how internal ledger records are compared with external payment or settlement records.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Security and compliance:&lt;/strong&gt; Evaluate the requirements applicable to the actual deployment and business model.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;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.&lt;/p&gt;




&lt;h2&gt;
  
  
  Get the Complete Source Code
&lt;/h2&gt;

&lt;p&gt;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:&lt;/p&gt;

&lt;p&gt;👉 &lt;strong&gt;&lt;a href="https://harutpetrosyan.gumroad.com/l/dotnet-wallet-kit" rel="noopener noreferrer"&gt;Bulletproof .NET Wallet &amp;amp; Idempotent Ledger Micro-Kit on Gumroad&lt;/a&gt;&lt;/strong&gt;&lt;/p&gt;

&lt;p&gt;Version 3.1.0 includes:&lt;/p&gt;

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

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




&lt;h2&gt;
  
  
  Final Thoughts
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

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

&lt;p&gt;That is what turns a simple wallet API into a useful exercise in financial backend engineering.&lt;/p&gt;

</description>
      <category>dotnet</category>
      <category>csharp</category>
      <category>postgres</category>
      <category>architecture</category>
    </item>
  </channel>
</rss>
