Most data bugs are loud. A missing column throws, a bad join returns obviously wrong rows, a null blows up a template. Money bugs are quiet. A balance ends up a few cents off, or a transfer gets applied twice under load, and nothing complains until someone reconciles the numbers at the end of the month.

My university graduation project, PolakPay, was a multi-branch money transfer system — branches, transfers between them, and the records that have to agree with each other afterwards. These are the rules I came out of it with. They apply anywhere a table holds a balance, not just in fintech.

1. Never store money as a float

This one is well known and still gets broken, usually by someone reaching for Float because it's the first numeric type in the autocomplete list.

0.1 + 0.2 // 0.30000000000000004

Floating point can't represent most decimal fractions exactly, so rounding errors creep in and pile up across thousands of operations. There are two correct options: store integer minor units (cents, pence, puls) in a BigInt/Int, or use a fixed-precision decimal type. In Prisma with PostgreSQL I default to the decimal:

model Account {
  id       String  @id @default(cuid())
  currency String  @db.Char(3)
  balance  Decimal @db.Decimal(14, 2)
}

Integer minor units are just as valid — sometimes better if you deal with many currencies that have different minor-unit sizes. What matters is picking one and never mixing them. And keep the currency next to the amount. An amount without a currency isn't really a value, just a number.

2. The ledger is the truth, the balance is a cache

The tempting design is one balance column that you add to and subtract from. The problem shows up when it's wrong: there's no record of how it got wrong.

The design I default to is an append-only ledger of entries, where every movement of money is a row that never gets updated or deleted:

model LedgerEntry {
  id         String   @id @default(cuid())
  accountId  String
  transferId String
  amount     Decimal  @db.Decimal(14, 2) // negative for debits
  createdAt  DateTime @default(now())
 
  account  Account  @relation(fields: [accountId], references: [id])
  transfer Transfer @relation(fields: [transferId], references: [id])
 
  @@index([accountId, createdAt])
}

A transfer writes two entries that add up to zero. The balance column on the account still exists for fast reads, but it's a cache of SUM(amount). If the two ever disagree, the ledger wins and you can rebuild the balance from it. Corrections are new entries that reverse the old ones, never edits. That's how accountants have done it for a few hundred years, and it holds up for code too.

3. Check and write in the same statement

The classic race: two requests read a balance of 100, both decide a withdrawal of 80 is fine, and both write. Now the balance is −60.

Reading, checking in application code, then writing is the bug. Make the database do the check as part of the write:

await prisma.$transaction(async tx => {
  const debited = await tx.account.updateMany({
    where: { id: fromId, balance: { gte: amount } },
    data: { balance: { decrement: amount } },
  })
 
  if (debited.count === 0) {
    throw new InsufficientFundsError(fromId)
  }
 
  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  })
 
  // ...plus the two ledger entries
})

The updateMany with a balance >= amount condition runs as a single UPDATE ... WHERE statement. PostgreSQL locks the row while it runs, so the second concurrent request sees the updated balance and matches zero rows. No separate SELECT, no window between check and write.

When the logic doesn't fit in one conditional update, the fallback is an explicit row lock (SELECT ... FOR UPDATE through $queryRaw) inside the transaction, or a Serializable isolation level with a retry on serialization failure. Both work, but they're more moving parts, so I only reach for them when the simple version can't express the rule.

4. Make every write idempotent

Networks retry. Users double-click. Mobile clients resend on reconnect. A transfer endpoint that isn't idempotent will eventually apply the same transfer twice.

The fix is cheap: the client sends an idempotency key with each request, and the key has a unique constraint.

model Transfer {
  id             String   @id @default(cuid())
  idempotencyKey String   @unique
  // ...
}

A retried request hits the unique constraint, and the handler returns the original transfer instead of creating a new one. The database enforces "exactly once", so the application doesn't have to get it right on every code path.

The trade-off

All of this is more schema and more ceremony than a single balance column with +=. For a side project tracking expenses for one person, it's overkill. But the cost is paid once, at design time. The alternative's cost is paid every time someone asks "why doesn't this number match?", and by then the history you'd need to answer it is gone.