Money, time, and a database that lied to me

I like problems with a right answer. Broken calculations are my favourite kind of bug precisely because there is a correct number and I either have it or I do not. The trouble is that the worst data bugs do not look broken. They give you a number that is plausible, slightly wrong, and easy to ship. In this system the numbers are money and limits, so slightly wrong quietly means someone is charged or capped incorrectly. Here are three I keep meeting.

Money is not a float#

If you store money as a floating point number, it will drift. Not dramatically, not on any single value, but across a lot of accumulated additions the binary representation of decimals bites you, and a running total ends up a cent or two off what a human with a calculator gets. In money code, a cent or two off is not a rounding footnote. It is a bug report.

So every monetary value in the system is a fixed-precision decimal end to end, stored in a Numeric(18, 8) column, never a float, from the API boundary down to the database. And the aggregations wrap the sum:

COALESCE(SUM(cost), 0)

so an empty window returns zero instead of NULL and quietly poisoning the maths downstream. Two small decisions, and a whole class of “the totals are slightly wrong and nobody knows why” tickets never gets written.

”This month” is a timezone question#

There is a per-account monthly limit in the system, and the naive version of “how much this month” is to slice the data on UTC month boundaries. That is wrong for anyone who does not live in UTC. Their month starts and ends at the wrong moment, so spend lands in the wrong bucket near the edges, and the limit trips a few hours early or late.

The honest version is more steps than you would like:

• take now,
• convert it to the real local timezone,
• pull out the year and month there,
• rebuild the start of the local month,
• convert that back to UTC for the query.

It reads like a lot of ceremony to answer “what month is it.” But “what month is it” genuinely has a different answer depending on where you are standing, and the database only speaks UTC. The translation has to happen somewhere, and doing it explicitly beats being quietly wrong at the boundaries.

The database that lied in the tests#

Most of the unit tests run against an in-memory SQLite database instead of the real Postgres. It is fast, it needs no infrastructure, and for the bulk of the logic it is fine. It is also not telling the whole truth, and you have to know that going in.

To make it work at all, two hacks live in the test setup: Postgres-only column types get patched down to generic equivalents, and cross-schema foreign keys get collapsed into one namespace, because SQLite has no concept of schemas. It works. But it means a test can fail for a reason that has nothing to do with your logic: it fails because SQLite and Postgres disagree, not because the code is wrong. That is a genuinely confusing half hour the first time it happens.

The compromise I settled on: a fake database is a fine default if you do it consciously, and route anything genuinely dialect-specific to a smaller suite that runs on the real engine. The fast fake tests cover the shared behaviour. The slow honest tests cover the parts where the fake would lie. What you must not do is forget that “the test lied” is a failure mode at all.

The common thread#

Floats for money, UTC for someone else’s calendar, a stand-in database in your tests: none of these throw. Nothing goes red. They just hand you a number that is a little bit wrong, in a way that survives code review because it looks completely reasonable.

Being data-driven is not only about reading the numbers. It is about not trusting the number until you know it came out of a pipeline that respects decimals, real timezones, and the difference between the database you test against and the one you ship on.