Currency Conversion in Analytics

All BillHive analytics queries that aggregate monetary amounts apply currency conversion to normalize values into the tenant’s base currency. This ensures dashboards and reports present consistent, comparable figures regardless of the currencies used across individual subscriptions and invoices.

Conversion Pattern

Every analytics SQL query that sums monetary amounts uses a Common Table Expression (CTE) to load the tenant’s currency rates, then joins and divides:

WITH currency_rates AS (
  SELECT currency_code, conversion_rate
  FROM subscription.currency
  WHERE tenant_id = $1
)
SELECT
  SUM(amount / COALESCE(cr.conversion_rate, 1)) AS amount_in_base
FROM some_table t
LEFT JOIN currency_rates cr
  ON cr.currency_code = t.currency::text
WHERE t.tenant_id = $1

Key Details

• conversion_rate represents target currency units per 1 base currency unit (e.g., if base is EUR and target is USD with rate 1.08, then $108 / 1.08 = 100 EUR)
• COALESCE(..., 1) ensures amounts in the base currency (or unmapped currencies) pass through unchanged
• ::text cast is required because currency columns use a PostgreSQL enum type (subscription."Currency"), while the currency_code column in the rates table is varchar

Affected Analytics Queries

The following queries in the reporting service apply currency conversion:

Revenue & Recurring Metrics

Subscription Health

Payment Performance

Dunning & Collections

Frontend Integration

The analytics frontend dynamically resolves the tenant’s base currency for display:

1. On page load, the component fetches both the analytics data and the tenant’s currency list in parallel using forkJoin
2. The base currency is identified from the currency list (the entry where isBaseCurrency is true)
3. The currency code is mapped to a display symbol using a currency_mapper (e.g., USD → $, EUR → €, INR → ₹)
4. All chart labels, tooltips, and formatted values use the resolved currency symbol instead of a hardcoded $

forkJoin({
  data: httpService.getAnalyticsData(),
  currencies: httpService.getCurrencies(),
}) → resolve base currency → render charts with correct symbol

Fallback Behavior