Money Is Not a Float: Exact Decimals with Postgres and Prisma

Open a browser console and type this:

0.1 + 0.2;            // 0.30000000000000004
(2.675).toFixed(2);   // "2.67"  (you wanted "2.68")

Neither is a JavaScript bug. Numbers are binary floating point, and most decimal fractions can't be represented exactly. For a chart, nobody cares. For an invoice, it's a one-paisa difference between what you printed and what's in the ledger, and an accountant will find it.

#The half-fix: exact columns

In BuildLedger, the construction ERP I built, every money column was already exact:

model Invoice {
  subtotal        Decimal @db.Decimal(14, 2)
  tax             Decimal @db.Decimal(14, 2)
  retainageAmount Decimal @db.Decimal(14, 2)
  total           Decimal @db.Decimal(14, 2)
  paidAmount      Decimal @db.Decimal(14, 2)
  balanceDue      Decimal @db.Decimal(14, 2)
  taxRate         Decimal @db.Decimal(5, 2)
  // …
}

NUMERIC(14,2) stores exactly two decimal places. But exact storage doesn't help if the code in between does this:

const subtotal = +calculatedItems.reduce((s, i) => s + i.amount, 0).toFixed(2);
const tax = +(subtotal * (taxRate / 100)).toFixed(2);

That's what the invoice service used to do. Every value went through a float, got rounded with toFixed (which, as we saw, rounds 2.675 down), and was written back to an exact column. The database was precise; the maths wasn't.

#The fix: one helper, used everywhere money is calculated

Prisma already ships a decimal type, Prisma.Decimal (it's decimal.js under the hood). So the fix is a small module that wraps it, with the rounding rule written down once:

/**
 * Exact money arithmetic. Database columns are NUMERIC(14,2); doing the math
 * with JS numbers reintroduces float errors (0.1 + 0.2 !== 0.3), so all money
 * math goes through Prisma.Decimal and is rounded once, at the end.
 */
export const Decimal = Prisma.Decimal;
export const money = (value: MoneyInput): Money => new Decimal(value ?? 0);

/** Round to paisa (2 dp), half-up, as on printed invoices. */
export const round2 = (value: MoneyInput): Money =>
  money(value).toDecimalPlaces(2, Decimal.ROUND_HALF_UP);

/** Round a quantity (measurement mode) to 4 dp, half-up. */
export const round4 = (value: MoneyInput): Money =>
  money(value).toDecimalPlaces(4, Decimal.ROUND_HALF_UP);

export const sumMoney = (values: MoneyInput[]): Money =>
  values.reduce<Money>((total, v) => total.plus(money(v)), new Decimal(0));

A few deliberate choices:

• Half-up rounding, because that's what people expect on a printed invoice. Banker's rounding (half-to-even) is a valid choice too, but it has to be a choice, written down, not whatever toFixed happens to do.
• Round once, at the end. Rounding every intermediate step compounds the error. Line items round to their own precision; totals round once.
• Quantities get 4 places. Construction invoices measure things (length × width), so area keeps more precision than money.

The invoice totals now read almost like the business rule itself:

const subtotal = round2(sumMoney(calculatedItems.map((i) => i.amount)));
const taxRate = money(data.taxPercentage ?? 0);
const retainageRate = money(data.retainageRate ?? 0);
const tax = percentOf(subtotal, taxRate);
const retainageAmount = percentOf(subtotal, retainageRate);
const total = round2(subtotal.plus(tax).minus(retainageAmount).minus(advanceAmount));

#Don't let the driver sneak floats back in

There's a second leak: reading values back. When I lock an invoice row with raw SQL (see Two Accountants, One Invoice), the amounts are cast to text:

SELECT total::text AS total, paid_amount::text AS paid_amount, …

A string like "1234.50" goes straight into money() without ever being a float. If you let the driver hand you a JavaScript number, you've lost before you started.

#Tests that pin the behaviour

These are the cases that would have caught the old code:

expect(round2("2.675").toFixed(2)).toBe("2.68"); // (2.675).toFixed(2) gives "2.67" in JS

• "adds in exact decimals (0.10 + 0.20 = 0.30, not 0.30000000000000004)"
• "computes totals exactly: 3 × 19.99 = 59.97, 17% tax = 10.19"

They're small, fast, and they document the rounding rule better than any comment.

#What's still on the list

Being honest: the helper covers invoices and labour totals, but the inter-project settlement engine still sums with Number(...) and compares against a 0.01 threshold. It's the next thing to move onto Decimal, and the tests for it already exist, so the change will be safe to make.

#The rules I follow now

1. Store money as NUMERIC/DECIMAL, never float or double.
2. Calculate with a decimal type, never JavaScript numbers.
3. Read exact values as strings or decimals, never let the driver convert them.
4. Pick a rounding rule, put it in one function, and round once.
5. Write the 0.1 + 0.2 test. It takes a minute and it will catch someone, someday.