---
title: "Single-currency database, dual-currency checkout: adding CAD without a schema change"
canonical: https://dxdev.com/blog/single-currency-db-dual-currency-checkout/
datePublished: 2026-04-23
---
A customer needed Canadian-dollar checkout. The clean, textbook answer is a multi-currency accounting model: a currency column on every money row, a rate table, conversion at write time, reconciliation per currency. The shippable answer, the one I actually merged, keeps the entire database single-currency in USD and translates at exactly two places: the page the buyer looks at, and the handoff to PayPal. Everything in between stays USD and stays reconcilable.

That distinction, "display currency" versus "ledger currency", is the whole post. If you only need to *show* a second currency and take payment in it, you do not need to *store* a second currency. Storing it is where the cost and the bugs live. Here is how CAD mode actually works in an aging ASP/IIS/MSSQL sports-registration platform, and the one guard that keeps it honest.

## The setting that drives the whole thing

There is no schema migration. CAD mode is a per-account option setting stored in the account's preferences table. When it's set to CAD, the checkout path behaves differently. When it's absent, nothing changes and every other account on the platform is untouched. That's the first property that matters: the feature is *opt-in per tenant*, because the absence of the setting is the existing behavior. No backfill, no default-value migration, no risk to the other accounts sharing the same tables.

A symmetric "pay in USD / pay in CAD" toggle link writes that same setting and reloads the page, so the buyer can flip it themselves. The toggle and the mode are the same single piece of state read from one place.

## Translate at the view, translate at the gateway, nowhere else

The checkout page reads the currency-mode setting. If it's CAD it does three things at the presentation boundary:

1. Renders prices in CAD with a red label so the buyer knows the number on screen is converted, not the canonical USD total.
2. Swaps the PayPal SDK over to CAD so the PayPal button itself transacts in Canadian dollars.
3. Hides the Authorize.net credit-card form and the Pay Later option, leaving a single PayPal button.

That third one is a real constraint leaking into the UI, not a design choice. Authorize.net wasn't set up for CAD settlement on this account, so offering the card form in CAD mode would be offering a path that can't actually settle. The honest move is to remove it in CAD mode rather than let a buyer fill it out and fail. Match the surface to what can actually clear money.

The point is that the conversion happens at the render and at the SDK handoff. It does not happen on the way into the database. The order, the line items, the stored total: all USD, same as every non-CAD account.

## The rate lives in exactly one place

The CAD multiplier lives as a single named constant in a new shared config file. Not inline in the checkout page, not duplicated in the PayPal files, not pulled from a config table. One constant, one file, every conversion reads it.

Worth being precise about what that number is. It isn't a live exchange rate, it's a pricing decision. The code comment says so directly: "Value is a pricing decision, not an exchange rate." It shipped at 1.4 and got dialed to 1.31 the next day to keep Canadian buyers from being penalized by the rate rounding.

This is the part people skip and regret. The moment a conversion rate appears in two places, they drift, and you get a checkout that shows one number and charges another. With the rate as a lone constant, "change the rate" is a one-line edit and there is no second copy to forget. The 1.4-to-1.31 change a day after launch was a single line touched in one file, and every call site followed. If this account ever needed a live feed instead of a pegged number, the seam is already there.

## The guard that keeps the ledger honest

Here is the load-bearing detail, the one that separates "I added a display currency" from "I corrupted my own accounting."

PayPal settles in CAD. It rounds. The amount that comes back through the return handlers is a *rounded CAD number*, derived from the USD total times the CAD rate and then rounded to whole units. Before this change, the USD checkout path had a legacy fallback: if the paid amount didn't match the order, it logged a mismatch and overwrote the stored order total with whatever was actually paid. Leave that fallback in place under CAD and you'd replace your canonical USD figure with a rounded conversion of itself, and now the row no longer reconciles against the rest of your USD ledger. Do it across enough orders and your books are a pile of slightly-wrong numbers nobody can trace back.

So the CAD path in the PayPal return handlers computes the *expected* paid amount by converting the stored USD total to CAD and compares with a tolerance band: a difference of two units or less counts as a match. Anything inside that band is treated as expected rounding, not a tampering signal, and the stored total is left exactly as it was. Even on a real mismatch outside the band, the code now overwrites the stored total only when the account is *not* in CAD mode, so the USD-mode legacy fallback survives while CAD mode never writes a converted number back. The gateway confirms the payment; in CAD mode it does not get to redefine the order's value.

That's the rule: in CAD mode the gateway's rounded amount is allowed to be off by up to a couple of units, and is never allowed to write back. Tolerate it on the way in, ignore it as a source of truth, keep your USD total untouched.

The whole guard is six lines:

```
expected = round(storedUsdTotal * CAD_RATE)
paid     = gateway.amountPaid
if (abs(expected - paid) <= 2) {
    // expected rounding, not tampering: do nothing
} else if (!account.cadMode) {
    storedUsdTotal = paid  // legacy USD-mode fallback only
}
```

## Why not just do multi-currency?

Because the database is shared by every account on the platform, and going multi-currency means touching every money path for one tenant's requirement. A currency column on the order tables means every report, every export, every reconciliation query, every refund path now has to ask "which currency" and handle the answer. You'd be paying that tax forever, on every account, to serve one Canadian customer who needs CAD at the till.

The display-only approach pays nothing on the other accounts. The blast radius is a handful of files in the checkout-and-PayPal path, gated behind one setting that's absent everywhere else. The trade-off is real: you cannot run financial reports *in* CAD, because you don't store CAD. Your books are USD, full stop.

The moment you store the second currency, you own multi-currency forever, on every account, for one customer's checkout button. Display is cheap and reversible, and storage is a cost you keep paying on every order until you delete the column.

## Related

The tolerance band is the whole feature in miniature: two units of slack on the way in, zero units of trust handed back out. CAD mode never got a currency column, a rate table, or a reconciliation job; it got a single constant that moved from 1.4 to 1.31 in one line, and a six-line guard that lets PayPal's rounding be off by up to two units without ever letting that rounding rewrite the order. The ledger stayed USD through the whole exercise, which is the only reason the fix was six lines instead of a schema migration.