A revenue dashboard summing the `amount` column. The table has rows in INR, USD, EUR. SUM produces a number that has no meaning — adding paise and cents is dimensional nonsense.

The vulnerable diff

tsreports/total-revenue.ts

// reports/total-revenue.ts
async function totalRevenue(since: Date) {
  // BUG: amount column holds INR (paise), USD (cents), EUR (cents).
  // SUM mixes them — result has no meaning.
  const { _sum } = await db.orders.aggregate({
    _sum: { amount: true },
    where: { createdAt: { gte: since } },
  });
  return _sum.amount;  // 4,238,991 (paise? cents? rupees? meaningless)
}

What is wrong

Currency is a dimension, like physical units. Adding 100 INR + 100 USD produces neither 200 INR nor 200 USD — the result has no meaningful unit. SUM across currencies is dimensional nonsense unless you first convert everything to a single currency. The fix is either to GROUP BY currency and report per-currency totals, or to maintain a separate column with a converted-to-base-currency amount captured at the time of the transaction (so exchange-rate drift is locked in).

The attack

Symptom:

textexploit

Dashboard: "Revenue: 4,238,991"
Real: 12,000 paise (INR) + 8,500 cents (USD) + 6,200 cents (EUR) → some
       indecipherable mixed-unit number.

CFO asks: "What's our revenue in rupees?" → no good answer from this
query.

Multi-currency products hit this constantly when reports are added late.

Mesrai's review comment

textreview-comment.txt

mesraipilot · Bot · reviewed 1 min ago

[mesrai] [code-review] [Logic] [Multi-Currency] [high]

SUM across a currency column with mixed currencies. Result is not
in any currency.

Two correct shapes:

  1. Group by currency, report each separately:
     SELECT currency, SUM(amount_minor) AS total_minor
       FROM orders WHERE created_at > $1
       GROUP BY currency;

  2. Maintain a `amount_inr` column (or your base currency) populated
     at transaction time using the rate at that moment:

     INSERT INTO orders (amount_minor, currency, amount_inr_minor)
     VALUES ($1, $2, $1 * fx_rate_at_now($2, 'INR'));

     SELECT SUM(amount_inr_minor) AS total_inr FROM orders WHERE ...

Option 2 locks in the rate at transaction time, which matches
accounting reality (the revenue was X INR equivalent at the moment
of the sale). Option 1 is honest about the dimension.

The fix

tsreports/total-revenue.ts (fixed)

// reports/total-revenue.ts — fixed (option 1)
async function totalRevenueByCurrency(since: Date) {
  return db.$queryRaw<Array<{ currency: string; total_minor: bigint }>>`
    SELECT currency, SUM(amount_minor)::bigint AS total_minor
    FROM orders WHERE created_at >= ${since}
    GROUP BY currency
  `;
}

// reports/total-revenue.ts — fixed (option 2, requires schema migration)
// Add amount_inr_minor column populated at write time. Then:
async function totalRevenueInr(since: Date) {
  const { _sum } = await db.orders.aggregate({
    _sum: { amountInrMinor: true },
    where: { createdAt: { gte: since } },
  });
  return _sum.amountInrMinor;
}

Group by currency for honest reporting, or maintain a converted-to-base-currency column for single-number reports. Either way the math is sound.

Why human review missed it

Multi-currency support is rarely scoped well at v1 — single-currency aggregations are the default. As the product expands, the SUM becomes wrong. Mesrai catches every aggregate over a currency column without a currency partition or converted column.

Related rules + further reading

Mesrai rule pack: logic/multi-currency-aggregate — flags SUM/AVG on amount columns without currency grouping.

Martin Fowler: Money pattern § Multi-Currency.

Common in fintech/SaaS as they expand beyond a home market.

Takeaway

Currency is a dimension. SUM needs partition by currency, or a base-currency column. Mesrai catches mixed aggregates.

Mixing INR + USD in the Same Aggregation: A Multi-Currency Catch