Перейти до основного вмісту

Multi-currency model

UCRM stores every monetary amount DUAL: original (player wallet currency) + base-converted (tenant reporting currency), with the FX rate locked at event time. Reports always render in the tenant's base; player profile shows native wallet.

Why DUAL storage​

Naive single-currency CRMs convert at query time using current FX rates. That makes lifetime metrics drift — a player's "lifetime deposit" jumps around as FX rates change. UCRM's DUAL pattern stores the converted amount at event time, so historical metrics stay stable.

Storage convention​

  • Subunit BIGINT — every amount stored as integer in smallest unit. EUR €123.45 = 12345; BTC 0.00012345 = 12345.
  • Currency code — paired with every amount. "EUR", "USD", "BTC", "USDT", "ETH".
  • Scale — decimal places for the currency. EUR/USD = 2, BTC = 8, USDT = 6, JPY = 0.
  • Crypto cap — 8 decimals max (industry standard). ETH wei (18 decimals) overflows BIGINT and is impractical for CRM display.

Currency catalogue​

Every tenant has a currencies catalogue. The migration seeds EUR + USD for every tenant; operators add cryptos as needed.

SELECT code, kind, scale, symbol, display_name, is_base
FROM currencies WHERE tenant_id = $1;

 code | kind   | scale | symbol | display_name | is_base
------+--------+-------+--------+--------------+---------
 EUR  | fiat   |     2 | €      | Euro         | t
 USD  | fiat   |     2 | $      | US Dollar    | f
 BTC  | crypto |     8 | ₿      | Bitcoin      | f
 USDT | crypto |     6 | USDT   | Tether       | f
 ETH  | crypto |     8 | Ξ      | Ethereum     | f

Exactly one currency per tenant has is_base = true; that's the reporting currency.

FX rates​

fx_rates is a per-tenant timeline. The FX fetcher (pnpm --filter @ucrm/api fx:fetch) runs hourly and:

  • Pulls FIAT pairs from Frankfurter (ECB-backed, no auth)
  • Pulls crypto pairs from CoinGecko (no-auth tier)
  • Upserts rows with (tenant_id, base, quote, ts) PK + source field

The lookup is WHERE tenant_id = ? AND base = ? AND quote = ? AND ts <= ? ORDER BY ts DESC LIMIT 1 — newest rate at or before event time.

DUAL columns on transactional tables​

bonus_grants (issued at event time):

bonus_amount        BIGINT NOT NULL,        -- original (e.g. 1000 USD cents)
bonus_currency      TEXT NOT NULL,          -- "USD"
bonus_amount_base   BIGINT NOT NULL,        -- e.g. 910 EUR cents (locked)
base_currency_code  TEXT NOT NULL,          -- "EUR"
fx_rate_locked      NUMERIC(28, 12) NOT NULL,
fx_locked_at        TIMESTAMPTZ NOT NULL

Same DUAL pattern on wagering_events. ClickHouse raw_events properties get enriched with amount_base + base_currency + fx_rate_locked at insert time so dashboard queries SUM(amount_base) work coherently across heterogeneous wallet currencies.

Per-player wallet currency​

players.default_currency_code is the player's primary wallet currency, captured on first deposit_confirmed. Until then it's NULL → admin UI shows tenant base. Once set, the player profile renders amounts natively (₿0.0023 BTC) plus the base equivalent in tooltip.

Querying​

  • Reports + segments — query amount_base_subunit columns or JSONExtractInt(properties, 'amount_base') in CH. Always coherent.
  • Player profile — show native + base (already provided by the schema).
  • Bonus issuance — pass currency_override if the bonus should be denominated in something other than the player's wallet; engine locks FX at issue time.