SaaS Multi-Currency Support in PostgreSQL
Money looks like just another number until you store it wrong. Multi-currency support in a SaaS starts as "add a currency column" and quietly becomes an accounting problem the first time your ledger drifts away from your bank balance — one fractional cent per transaction, compounding across thousands of invoices, until a quarter-end reconciliation doesn't reconcile. The cause is almost always the same: someone stored money in a FLOAT.
Here are the rules that keep the books straight: store money as integer minor units or NUMERIC, never a float; keep exchange rates in their own table and stamp the rate onto each transaction at payment time; convert in SQL; and let Stripe's balance_transaction be the source of truth for real rates and fees. This is PostgreSQL doing what it's good at — exact arithmetic — as long as you give it the right column types.
Rule 1: Never Store Money in a Float
The first law of financial engineering: monetary values never go in binary floating point. FLOAT and REAL store base-2 approximations, not exact base-10 decimals, which is why every language does this:
1console.log(0.1 + 0.2); // 0.30000000000000004That rounding error is harmless once and catastrophic at scale — run it through millions of multi-tenant conversions and your recorded revenue diverges from your actual settlements. The fix is to use a type that stores money exactly, and to keep the arithmetic out of floats entirely.
Rule 2: Model the Ledger with BIGINT or NUMERIC
Two safe representations, depending on whether you bill in fractional cents:
BIGINTminor units — store$10.50as1050,¥500as500(JPY has no minor unit). This is exactly how Stripe represents money, which makes the integration boring in the best way.NUMERIC(p, s)— arbitrary-precision decimals, for usage-based metering that bills at, say,$0.0001per API call.
1CREATE TABLE invoices (
2 id BIGSERIAL PRIMARY KEY,
3 tenant_id UUID NOT NULL,
4 invoice_uuid UUID DEFAULT gen_random_uuid() UNIQUE,
5 gross_amount NUMERIC(18, 4) NOT NULL, -- exact, up to 4 minor digits
6 currency_code CHAR(3) NOT NULL CHECK (length(currency_code) = 3), -- ISO 4217
7 created_at TIMESTAMPTZ DEFAULT NOW()
8);
9
10CREATE INDEX idx_invoices_currency ON invoices (tenant_id, currency_code);And skip PostgreSQL's built-in MONEY type — it's tied to the server's lc_monetary locale, so the same row can render with the wrong symbol after a host migration. Crunchy Data's guide to money in Postgres walks through why integers and NUMERIC win. Scope every money table by tenant_id the same way you would the rest of your multi-tenant schema.
Rule 3: Freeze the Exchange Rate at Transaction Time
Exchange rates move all day. If an invoice is paid on June 1st, its value in your base reporting currency must be fixed forever — recompute it with today's live rate and last quarter's revenue changes every time your rate worker runs. So keep rates in their own table with validity windows, and stamp the rate onto the transaction at payment:
1CREATE TABLE fx_rates (
2 id BIGSERIAL PRIMARY KEY,
3 base_currency CHAR(3) NOT NULL,
4 target_currency CHAR(3) NOT NULL,
5 rate NUMERIC(18, 8) NOT NULL,
6 valid_from TIMESTAMPTZ NOT NULL,
7 valid_to TIMESTAMPTZ NOT NULL,
8 CONSTRAINT unique_fx_window UNIQUE (base_currency, target_currency, valid_from)
9);A daily cron pulling mid-market rates is plenty for most billing; only real-time wallets need minute-by-minute polling.
Rule 4: Convert in SQL Against the Historical Rate
Consolidated reporting is a join from transactions to the rate that was valid when each one happened:
1SELECT
2 i.currency_code AS native_currency,
3 SUM(i.gross_amount) AS native_total,
4 SUM(i.gross_amount * COALESCE(fx.rate, 1.0)) AS usd_total
5FROM invoices i
6LEFT JOIN fx_rates fx
7 ON fx.base_currency = i.currency_code
8 AND fx.target_currency = 'USD'
9 AND i.created_at >= fx.valid_from
10 AND i.created_at < fx.valid_to
11WHERE i.tenant_id = $1
12GROUP BY i.currency_code;The COALESCE(fx.rate, 1.0) handles the case where the native currency is the base currency (no row needed). Reports stay stable because they read the stamped historical rate, not whatever the market is doing right now.
Rule 5: Format on the Edge, Sync With Stripe
Do the math in integers or NUMERIC; only convert to a display string at the very edge, with Intl.NumberFormat — never hand-rolled string concatenation, because $1,000.50 in the US is 1.000,50 € in much of Europe:
1export function formatCurrency(amount: number, currency: string, locale = 'en-US'): string {
2 return new Intl.NumberFormat(locale, { style: 'currency', currency }).format(amount);
3}
4// formatCurrency(1250.5, 'EUR', 'de-DE') → "1.250,50 €"
5// formatCurrency(1250.5, 'USD', 'en-US') → "$1,250.50"Intl.NumberFormat is built into the runtime and handles every locale's rules for you — pair it with your i18n setup so the currency follows the user's locale.
Finally, reconcile against Stripe rather than your own estimate. Stripe spans three currencies — presentment (shown at checkout), settlement (your payout), and cardholder (their card) — and its balance_transaction carries the exact rate and fees applied. Read it from the charge webhook and record those numbers, so your ledger matches the money that actually landed.
Multi-Currency Support: Money Storage Types at a Glance
| Type | Precision | Rounding risk | Use for |
|---|---|---|---|
FLOAT / REAL | Approximate | Extreme | Never money |
BIGINT | Exact minor units | None | Fixed-precision billing, Stripe |
NUMERIC | Arbitrary exact | None | Fractional / usage-based billing |
MONEY | Locale-dependent | High (locale drift) | Avoid for multi-currency |
Get the column type right and multi-currency stops being scary — PostgreSQL does exact arithmetic all day, and your reconciliation just works. Get it wrong and you'll spend a quarter-end hunting a few hundred dollars that floating point quietly ate, one rounding error at a time. Store money like it's money, not like it's a physics measurement.
Frequently Asked Questions
Two safe options: BIGINT minor units (store $10.50 as 1050 cents), which matches how Stripe represents money and is the simplest for fixed-precision billing; or NUMERIC(precision, scale) when you need fractional cents, like usage-based metering at $0.0001 per call. Never use FLOAT/REAL — binary floating point can't represent base-10 money exactly, so it drifts. Avoid PostgreSQL's MONEY type too, because it depends on the server locale.
FLOAT stores base-2 approximations, so 0.1 + 0.2 is 0.30000000000000004 — those tiny errors compound across millions of transactions until your ledger no longer matches your payment gateway. PostgreSQL's MONEY type is exact but tied to the server's lc_monetary locale, so the same value can render with the wrong symbol or decimal placement if the database moves hosts. Both are wrong for multi-currency SaaS.
Store FX rates in their own table with validity windows, and stamp the exact rate used onto each transaction at the moment of payment. Then historical reports join to the rate that was true then, not the live rate. If you recompute old revenue with today's rate, last quarter's numbers change every time your rate worker runs — which fails audits and confuses everyone.
Stripe deals with three currencies: presentment (shown to the customer), settlement (what your bank receives), and cardholder (their card's currency). Listen for the charge/balance events and read the balance_transaction, which carries the exact exchange rate and fees Stripe applied. Record those, so your internal ledger reflects real payouts rather than your own estimate of the rate.
Calculate tax in the presentment currency — the amount the customer actually saw and paid — then store it, then convert to your base reporting currency with the stamped historical rate. Computing VAT after conversion introduces rounding that can leave you a cent off the legally required amount on the invoice, which is exactly the kind of discrepancy a tax audit flags.
