How should a subscription tracker store money in multiple currencies?
Integer minor units per row with the currency attached, exponents from a table, and conversion only at read time. The three rules and why each one bites.
Answer
Store integer minor units with the currency code on the same row, keep a per-currency exponent table because JPY and KRW have zero decimal places, and convert only at read time with the rate date attached to the response. Never write a converted amount back — doing so makes last year's total change when today's rate moves.
Leutrim Miftaraj
Founder, SubTracker · Updated September 5, 2026
The short answer
Three rules, and each fails at a different point. Floats fail on summation, a global exponent of two fails the first time a Japanese user appears, and converting at write time fails when anyone looks at a historical total twice.
Rule one: integer minor units
"amount_cents bigint", not "numeric" and definitely not "float".
Floating point cannot represent 0.1 exactly. Sum a hundred monthly amounts and the total drifts by a fraction of a cent — invisible until a user compares your annual figure against their own arithmetic and finds a discrepancy they cannot explain. They will not report it as a rounding artefact. They will report it as the app being wrong, and they are right.
"numeric" is correct and slower, and it invites a second problem: somebody writes "amount * 12" and the type quietly promotes. Integers of minor units make the unit explicit at every call site — you cannot accidentally treat 1290 as dollars.
Rule two: the exponent is per currency
Not every currency has two decimal places. JPY and KRW have none: ¥1,290 is 1290 minor units, not 129000.
""`ts const EXPONENT: Record<string, number> = { JPY: 0, KRW: 0, ISK: 0 } // default 2
export function toMinor(amount: number, currency: string) { return Math.round(amount * 10 ** (EXPONENT[currency] ?? 2)) }
export function format(minor: number, currency: string, locale?: string) { const e = EXPONENT[currency] ?? 2 return new Intl.NumberFormat(locale, { style: 'currency', currency }) .format(minor / 10 ** e) } ""`
Get this wrong and every Japanese amount is a hundred times too large. It reaches production regularly, because nobody on the development team has a JPY subscription to notice it with — which is a general argument for testing with a zero-decimal currency in the fixture set rather than only USD and EUR.
"Intl.NumberFormat" handles the display side, including symbol placement, which differs by locale: "4,99 €" in German, "€4.99" in English. Do not build that by hand.
Rule three: convert at read time, never at write
The tempting design is one currency on the user profile with amounts converted on entry. It fails on first contact with a real list — someone in Switzerland pays CHF locally, USD for American SaaS and EUR for a German newspaper.
Converting at entry destroys the original figure. When the rate moves, your stored amount is wrong and there is nothing to recompute from.
So: store what the user is charged, convert for display, and attach the rate and its date to the presentation rather than to the row.
""`ts type Total = { minor: number; currency: string; rateDate: string; approximate: true } ""`
Users understand "≈ $71 at today's rate". They do not understand a stored figure that changed on its own.
Do not add an "exchange_rate" column to the subscriptions table. A rate is a property of a moment, not of a subscription, and putting it there means either updating every row on a schedule or serving stale figures.
The rates table
If you convert at all, you need rates from somewhere and a decision about staleness.
""`sql create table fx_rates ( base char(3) not null, quote char(3) not null, rate numeric(18,8) not null, fetched_at timestamptz not null default now(), primary key (base, quote, fetched_at) ); create index on fx_rates (base, quote, fetched_at desc); ""`
Keyed on the timestamp rather than overwriting, so a total can be reproduced. Daily granularity is plenty for a subscription tracker; intraday is a trading concern.
Decide what happens when the rate is stale. Serving a week-old rate silently is the wrong answer. Either label the figure with its rate date, or refuse to produce a cross-currency total and show a per-currency breakdown instead. The second is more honest and users accept it more readily than product teams expect.
What we changed after shipping
We started with a single display currency on the profile and converted on entry. It worked for the first few hundred users and then produced a support pattern we could not explain: people reporting that an amount they had entered was subtly different a month later.
It was the conversion. The stored value was a converted figure and the display converted again on read in some paths, so the number moved.
Two fixes: currency moved onto the row, and conversion moved to a single function at the presentation boundary rather than existing in three places. The second mattered more than the first — the underlying defect was that conversion happened at more than one layer, and no schema fixes that.
Die ehrliche Bau-oder-Kauf-Linie
Das Schema ist der einfache Teil. Es ist auch der Teil, der sich nach Fortschritt anfühlt — weshalb das Projekt unmittelbar danach aufhört, Spass zu machen.
Was tatsächlich Zeit kostet, in der Reihenfolge, in der es zubeisst: geplante Erinnerungen mit Idempotenz und Zeitzonen, Wiederholungs-Arithmetik über unregelmässige Intervalle, Währungsbehandlung, und ein Import, aus dem heraus überhaupt etwas entsteht.
Nichts davon ist intellektuell schwierig. Es ist schwierig im Sinne von vier Wochenenden, und das vierte ist das, an dem das Projekt stirbt.
Wenn das Ziel Lernen ist: bau es. Der Code hier ist echt und funktioniert. Wenn das Ziel ist, kein Geld mehr an vergessene Abos zu verlieren, ist der Bau ein Umweg.
Was SubTracker damit macht
Zur Einordnung, weil die Entscheidungen oben aus einem laufenden System stammen und nicht aus einer Entwurfsübung.
SubTracker ist eine Next.js- und PostgreSQL-Anwendung (Supabase), die Abos manuell verfolgt — es gibt keine Bankverbindung, jede Zeile stammt aus einem Formular oder einem CSV-Import. Diese Einschränkung prägt das Schema: kein „erkannt"-Zustand, kein Konfidenzwert, keine Händler-Zuordnungstabelle. Jede Zeile ist eine Behauptung, die jemand aufgestellt hat.
Der kostenlose Plan verfolgt unbegrenzt viele Abos inklusive CSV-Import und Live-Kalender-Feed. Erinnerungen und Preis-Alarme sind Plus für $5.90/Monat; Family kostet $9.90/Monat und fügt einen geteilten Workspace für bis zu zehn Personen hinzu — an dem Punkt hören die Besitzverhältnisse im Schema auf, theoretisch zu sein.
Frequently asked questions
Should money be stored as integer or decimal?+
Integer minor units. Floating point cannot represent common decimal amounts exactly, so summed totals drift and users find discrepancies they cannot explain. Integers also make the unit explicit at every call site.
How do you handle currencies without decimal places?+
With a per-currency exponent table — JPY, KRW and ISK have zero. A global assumption of two makes every Japanese amount a hundred times too large, and it reaches production because nobody on the team has a JPY subscription.
Should each subscription store its own currency?+
Yes. A single profile currency fails as soon as someone pays for services in more than one, which is common. Store what the user is charged and convert at display time.
Where should exchange rates be stored?+
In a separate table keyed on the fetch timestamp, never as a column on the subscription. A rate is a property of a moment, and writing converted amounts back makes last year's total change when today's rate moves.