If you've been following my cryptic, dare I say unhinged posts about finance all of a sudden, this is the explanation.
I want to tell you about the most quietly dangerous line of code running in production software right now. Not dangerous in the "this will get you hacked" sense. Dangerous in the "this has already cost companies millions of dollars and nobody talks about it" sense.
Here it is:
UPDATE accounts SET balance = balance - amount WHERE id = $1You've written it. I've written it. Every fintech tutorial on the internet teaches it. It's in the official docs of at least three major frameworks. It's in the starter templates that junior engineers copy-paste into production at Series A startups.
And it is a landmine with the pin already pulled.
I want to walk you through exactly why. Not at a hand-wavy "here's the concept" level, but at the level of actual database behavior, actual race conditions, actual production failure modes. By the time you finish reading this, you will never look at a balance column the same way again.
The Naive Implementation
Let's say you're building a payments feature. Users have wallets. They can send money to each other. Classic problem.
You create a table:
CREATE TABLE accounts (
id UUID PRIMARY KEY,
balance NUMERIC NOT NULL DEFAULT 0
);And when User A sends $50 to User B, you write something like:
await db.query(`UPDATE accounts SET balance = balance - $1 WHERE id = $2`, [50, userA]);
await db.query(`UPDATE accounts SET balance = balance + $1 WHERE id = $2`, [50, userB]);This works perfectly in development. It works perfectly in staging. It works perfectly in your first week of production with 50 users.
Then you hit your first flash sale. Or your first viral moment. Or just a Tuesday when a lot of people happen to be online at the same time.
And then something quietly, invisibly, catastrophically breaks.
Race Condition 101: The Double-Spend
Here's what actually happens when two requests hit your server at the same millisecond:
T=0ms Request A reads User X's balance: $100
T=0ms Request B reads User X's balance: $100
T=1ms Request A checks: does $100 >= $80? Yes. Proceeds.
T=1ms Request B checks: does $100 >= $60? Yes. Proceeds.
T=2ms Request A executes: UPDATE balance = 100 - 80 → $20
T=2ms Request B executes: UPDATE balance = 100 - 60 → $40
T=3ms User X's balance: $40
T=3ms User X actually sent: $140
T=3ms Money created from thin air: $40Both requests read the same balance. Both passed the validation check. Both executed. User X just spent $140 from a $100 account and your system happily recorded a $40 balance.
This is a double-spend. It's one of the oldest and most well-documented bugs in financial software. And the naive implementation ships it to production by default.
Your first instinct is to add a transaction:
await db.transaction(async (tx) => {
const account = await tx.query(`SELECT balance FROM accounts WHERE id = $1`, [userA]);
if (account.balance < amount) throw new Error("Insufficient funds");
await tx.query(`UPDATE accounts SET balance = balance - $1 WHERE id = $2`, [amount, userA]);
await tx.query(`UPDATE accounts SET balance = balance + $1 WHERE id = $2`, [amount, userB]);
});Better. But not fixed.
The transaction gives you atomicity, either both updates happen or neither does. But it doesn't solve the read problem. Two transactions can still both read the same balance before either has committed. The window is smaller, but it's still open.
SELECT FOR UPDATE: The Lock You Think Saves You
You read the PostgreSQL docs. You discover SELECT FOR UPDATE. This is a pessimistic lock, it tells the database "I am about to modify this row, hold it for me exclusively until my transaction commits."
await db.transaction(async (tx) => {
const account = await tx.query(
`SELECT balance FROM accounts WHERE id = $1 FOR UPDATE`,
[userA]
);
if (account.balance < amount) throw new Error("Insufficient funds");
await tx.query(`UPDATE accounts SET balance = balance - $1 WHERE id = $2`, [amount, userA]);
await tx.query(`UPDATE accounts SET balance = balance + $1 WHERE id = $2`, [amount, userB]);
});Now Request B cannot read the balance until Request A's transaction commits. The double-spend is closed.
You feel good. You push to production.
Three days later, under moderate load, your server starts throwing errors you've never seen before:
ERROR: deadlock detected
DETAIL: Process 47 waits for ShareLock on transaction 1890; blocked by process 52.
Process 52 waits for ShareLock on transaction 1891; blocked by process 47.Deadlocks: The Problem You Created By Solving The Last Problem
Here's what's happening:
Request A: Transfer from Account X → Account Y
Step 1: Lock Account X ✓
Step 2: Try to lock Account Y... waiting
Request B: Transfer from Account Y → Account X
Step 1: Lock Account Y ✓
Step 2: Try to lock Account X... waitingBoth requests are waiting for each other. Forever.
PostgreSQL detects the cycle after ~1 second and kills one. Your application throws an unhandled exception. The user sees a 500 error.
This is a textbook deadlock. It's not a bug in your code per se, it's an emergent property of lock ordering under concurrency. Two requests, locking two shared resources, in opposite order.
PostgreSQL will detect it eventually and kill one of the transactions. But now you need to handle that. You add retry logic. With exponential backoff. With jitter. With a maximum retry count. With logging so you know when it's happening.
Your "simple balance update" now has retry infrastructure.
But you're a good engineer. You actually read about this. You learn the solution: always acquire locks in the same order. If every transaction locks accounts in lexicographic order of their IDs, no two transactions can ever form a deadlock cycle.
const sortedIds = [userA, userB].sort();
const accounts = await tx.query(
`SELECT id, balance FROM accounts WHERE id = ANY($1) ORDER BY id FOR UPDATE`,
[sortedIds]
);This works. Deadlocks become mathematically impossible. You're genuinely proud of this one, you read a database paper and implemented it correctly.
But now you have a new problem you don't know about yet.
The ORM Trap: When Your Lock Doesn't Lock What You Think
Most production TypeScript code isn't using raw SQL. It's using Prisma, TypeORM, Drizzle, Sequelize, something with an abstraction layer. And that abstraction layer has a cache.
Here's what happens when you do this in Prisma:
const account = await prisma.account.findUnique({ where: { id: userA } });
// ^ This read happened OUTSIDE your transaction. It's already cached.
await prisma.$transaction(async (tx) => {
// You think you're reading a fresh, locked balance here.
// You are not. You might be reading a cached value from before the transaction started.
const lockedAccount = await tx.account.findUnique({
where: { id: userA }
// No way to express FOR UPDATE in Prisma without dropping to raw SQL
});
// Your validation is running against potentially stale data.
if (lockedAccount.balance < amount) throw new Error("Insufficient funds");
// ...
});The TOCTOU (Time-of-Check Time-of-Use) window is back open. Your lock exists at the database level, but your application layer read a different value and is making decisions based on it.
This is subtle. This is the kind of bug that passes code review, passes QA, and then surfaces as a support ticket six months later when a specific timing condition aligns just right.
To fix this, you have to drop to raw SQL inside your ORM transaction, which partially defeats the purpose of using an ORM, and requires the people maintaining your code to understand both the ORM's transaction semantics and the underlying SQL behavior.
Your "simple balance update" now has raw SQL in the middle of ORM code with a comment explaining why.
Idempotency: The Network Is Lying To You
You ship the locking logic. Things are stable. Then you start getting reports of duplicate charges.
User clicks "Pay". Gets a network timeout. Clicks "Pay" again. Gets charged twice.
This is not a UI bug. This is a distributed systems problem. The first request may or may not have completed, the client has no way of knowing. The only safe behavior is to retry. But retrying without idempotency creates duplicate transactions.
The solution is idempotency keys, a unique identifier the client sends with each request, and your server uses to deduplicate:
await db.transaction(async (tx) => {
// Check if we've seen this request before
const existing = await tx.query(
`SELECT id FROM transactions WHERE idempotency_key = $1`,
[idempotencyKey]
);
if (existing.rows.length > 0) return existing.rows[0]; // Already processed
// ... do the transfer ...
// Record that we've processed this key
await tx.query(
`INSERT INTO transactions (idempotency_key, ...) VALUES ($1, ...)`,
[idempotencyKey, ...]
);
});Now you're maintaining a separate transactions table. With its own indexes. With its own cleanup job to prevent it from growing forever. With its own unique constraint that you need to handle the violation of gracefully.
Your "simple balance update" now has a shadow table.
Serverless: The Connection Pool Apocalypse
Your startup takes off. You move to serverless, Vercel, AWS Lambda, Cloudflare Workers. Infinite scale, pay per request, the dream.
Except PostgreSQL has a hard limit on concurrent connections. A typical db.t3.medium RDS instance allows around 80. A connection pool like PgBouncer helps, but under genuinely high load, your serverless functions are each trying to open their own TCP connection to Postgres.
At 1000 concurrent requests, you have 1000 functions each waiting for a connection. Your connection pool is exhausted. Requests are queuing. Timeouts cascade. Your perfectly-coded locking logic never even gets to execute because the database refuses the connection.
Error: remaining connection slots are reserved for non-replication superuser connectionsYou need connection pooling middleware. PgBouncer, Supavisor, or a managed equivalent. You need to understand transaction-mode vs session-mode pooling and why SELECT FOR UPDATE behaves differently in each. You need to tune pool sizes against your expected concurrency profile.
Your "simple balance update" now requires infrastructure-level configuration that affects how your locks behave.
The Audit Trail: The Bill Always Comes Due
Everything above is about correctness under load. But there's a quieter, slower problem building in parallel.
Every time you run UPDATE accounts SET balance = balance - 50, you destroy information. The previous balance is gone. Overwritten. The fact that a specific transfer happened at a specific time for a specific amount, that context lives only in your application logs, if you were disciplined enough to write them.
When a user disputes a charge, you're diffing raw balance numbers trying to reconstruct what happened. When your accountant asks for a reconciliation report, you're writing ad-hoc SQL against your logs table. When a regulator asks for a complete transaction history, you're in trouble.
This isn't a hypothetical concern. It's a compliance requirement the moment you handle real money. PCI DSS, SOC 2, and virtually every financial regulation requires immutable audit trails. "The balance column changed from 100 to 50" is not an audit trail. It's an observation. The event that caused it, who authorized it, when, for what reason, against what prior state, needs to exist as a permanent, tamper-evident record.
You could add logging. A transaction_log table. Events on every update. But now you have two sources of truth, the balance column and the log table, that can diverge. A bug writes the balance but not the log. Or the log but not the balance. Your system is now split-brained.
The "simple balance update" has no answer for this. It's architecturally incapable of providing a real audit trail because it's built on mutation, and mutation destroys history.
The Bill of All This
Let me show you what the "simple balance update" looks like by the time you've handled everything above in production:
async function transfer(fromId: string, toId: string, amount: number, idempotencyKey: string) {
// Input validation layer
if (amount <= 0) throw new InvalidAmountError();
if (fromId === toId) throw new SelfTransferError();
return await db.transaction(async (tx) => {
// Idempotency check
const existing = await tx.query(
`SELECT id, status FROM transactions WHERE idempotency_key = $1 FOR UPDATE`,
[idempotencyKey]
);
if (existing.rows[0]?.status === 'completed') return existing.rows[0];
if (existing.rows[0]?.status === 'processing') throw new ConcurrentRequestError();
// Insert idempotency record
await tx.query(
`INSERT INTO transactions (idempotency_key, status) VALUES ($1, 'processing')`,
[idempotencyKey]
);
// Lexicographic lock ordering to prevent deadlocks
const sortedIds = [fromId, toId].sort();
const accounts = await tx.query(
`SELECT id, balance, currency, status FROM accounts
WHERE id = ANY($1::uuid[])
ORDER BY id
FOR UPDATE`,
[sortedIds]
);
if (accounts.rows.length !== 2) throw new AccountNotFoundError();
const fromAccount = accounts.rows.find(a => a.id === fromId);
const toAccount = accounts.rows.find(a => a.id === toId);
// Status validation
if (fromAccount.status !== 'active') throw new AccountFrozenError();
if (toAccount.status !== 'active') throw new AccountFrozenError();
// Currency validation
if (fromAccount.currency !== toAccount.currency) throw new CurrencyMismatchError();
// Balance check — must happen AFTER lock acquisition, never before
if (fromAccount.balance < amount) throw new InsufficientFundsError();
// Floating point check — you remembered this one, right?
if (!Number.isInteger(amount)) throw new FloatAmountError();
// Execute updates
await tx.query(
`UPDATE accounts SET balance = balance - $1, updated_at = NOW() WHERE id = $2`,
[amount, fromId]
);
await tx.query(
`UPDATE accounts SET balance = balance + $1, updated_at = NOW() WHERE id = $2`,
[amount, toId]
);
// Audit log — separate write, separate failure mode
await tx.query(
`INSERT INTO transaction_log (from_id, to_id, amount, idempotency_key, created_at)
VALUES ($1, $2, $3, $4, NOW())`,
[fromId, toId, amount, idempotencyKey]
);
// Mark idempotency record as completed
await tx.query(
`UPDATE transactions SET status = 'completed' WHERE idempotency_key = $1`,
[idempotencyKey]
);
return { success: true };
});
}And this still doesn't handle:
- Connection pool exhaustion under serverless load
- The float precision problem above
Number.MAX_SAFE_INTEGER - Escrow / two-phase holds for marketplace patterns
- Multi-currency conversions
- Rollback semantics when downstream services fail
- Concurrent balance reads for display purposes that need to account for pending holds
This is the true cost of the naive implementation. Not the one-liner. The 200+ lines you end up with after production teaches you everything it forgot to warn you about.
Every fintech team that has ever rolled their own payments has gone through exactly this journey. Some version of this code exists in a payments/utils.ts file somewhere at most startups processing real money right now. Maintained by one senior engineer who understands all the decisions that led here. Quietly accumulating new edge cases with every product requirement.
The Mental Model Was Wrong From The Start
Here is the thing nobody told you at the beginning of this journey.
The problem was never your implementation. The problem is the mental model that balance is a number you store and modify.
Money is not a number in a column. Money is a history of movements.
Think about it from first principles. Where does a balance come from? It comes from every deposit, every withdrawal, every transfer that ever touched that account. The balance is just the sum of those movements. It's derived. It's not a fact, it's a calculation.
The banking industry figured this out five hundred years ago. Double-entry accounting. Every financial event is recorded as an immutable journal entry with two legs: a debit on one account and a credit on another. The sum of all debits always equals the sum of all credits. Money is never created or destroyed, only moved. The balance of any account at any point in time is simply SUM(all entries for that account).
If you build your system this way, something remarkable happens:
- Race conditions disappear. You're not modifying a shared value anymore. You're appending to an immutable log. Two concurrent requests don't conflict they both append their entries and the balance is derived correctly from both.
- The audit trail is free. The journal is the audit trail. Every movement that ever happened is a permanent, queryable record. You don't need a shadow log table. The log is the source of truth.
- Reconciliation becomes trivial.
SUM(all debits) === SUM(all credits)is a mathematical invariant you can check at any time. If it fails, something is wrong. If it holds, your books are clean. - Time travel is built in. "What was the balance at exactly 3:47pm on November 3rd?" is a query, not an investigation. You filter entries by
created_atand sum them.
This is not a new idea. This is the foundation of every serious financial system on the planet. It's just never existed as a clean, developer-friendly, TypeScript-native primitive for the Postgres + Node.js stack.
Until now.
What I'm Building
I've spent the last 2-3 days building the accounting primitive that the TypeScript ecosystem never had.
It's called Konto.
Konto is a mathematically strict, embeddable double-entry accounting engine for TypeScript and PostgreSQL. Not a SaaS. Not an API you call. A library that lives in your codebase, runs in your database, and gives you financial-grade correctness by default.
It handles the locking. The deadlock prevention. The idempotency. The BigInt precision. The audit trail. The escrow system. The balance derivation. The migration safety.
The transfer call looks like this:
await transfer(db, {
entries: [
{ accountId: userWallet, amount: -5000n }, // $50.00 debit
{ accountId: merchantAccount, amount: 4500n }, // $45.00 credit
{ accountId: platformFeePool, amount: 500n }, // $5.00 fee
],
idempotencyKey: "order_xyz_payment",
metadata: { orderId: "xyz", customerId: "abc" }
});Three accounts. One atomic journal entry. Zero race conditions. Full audit trail. Mathematically verified zero-sum. Type-safe metadata with a generated TypeScript client.
The 200 lines of locking hell becomes one function call.
The era of hand-rolled ledgers in TypeScript is over.
If you're building anything that touches money on a TypeScript + Postgres stack, a marketplace, a fintech product, a platform with wallet features, an internal expense system, you want this primitive under you before you ship the naive implementation and spend the next year paying off the technical debt.
I'll be sharing more about how it works, the deadlock math, the escrow system, the performance architecture, in the weeks ahead.
For now: the repo is almost ready. Watch out.