Part of the Sanchayam series.

Why FX Matters

Sanchayam tracks assets in their native currency. An Indian equity is priced in INR. A US stock is priced in USD. Crypto may be in USD or USDT. A bank account might be in AED. The portfolio view needs to aggregate everything into one base currency (the user’s choice - INR by default).

Every portfolio snapshot, every holdings page, every P&L calculation requires converting across currencies. FX rates are fetched from the same Twelve Data API used for price feeds. A naive approach - one API call per currency per request - would be both slow and expensive.

USD as the Pivot

All exchange rates are stored relative to USD. To convert from currency A to currency B, the service computes:

rate(A -> B) = rate(A vs USD) / rate(B vs USD)

This is getRate in the FX service:

export async function getRate(from: string, to: string): Promise<string> {
  if (from === to) return '1'
  const [rateFrom, rateTo] = await Promise.all([resolveRate(from), resolveRate(to)])
  return new Big(rateFrom).div(new Big(rateTo)).toFixed(18)
}

resolveRate returns the rate of a currency against USD. USD itself returns '1' immediately. All arithmetic uses big.js to avoid floating point drift in financial calculations.

The pivot approach means storing one rate per currency instead of a rate for every possible currency pair. Adding a new currency requires one new database row, not N-1 new rows.

Two-Layer Cache

Rate resolution goes through two cache layers before hitting the database or the API.

Layer 1: in-process Map

const PROCESS_CACHE_TTL_MS = 5 * 60 * 1000  // 5 minutes

const rateProcessCache = new Map<string, { rate: string; cachedAt: number }>()

The in-process cache holds rates for 5 minutes. A hit here costs nothing - no database round-trip, no network call. Every resolveRate call checks this first.

Layer 2: PostgreSQL fx_rates table

CREATE TABLE fx_rates (
  currency_code  VARCHAR(10)    PRIMARY KEY REFERENCES currencies(code),
  rate_vs_pivot  NUMERIC(38,18) NOT NULL,
  collector_name VARCHAR        NOT NULL,
  fetched_at     TIMESTAMP      NOT NULL,
  last_consumed_at TIMESTAMP
);

A cache hit in the DB skips the API. The rate is considered fresh for 24 hours. On a DB hit, the rate is loaded into the in-process Map for 5 minutes.

last_consumed_at is updated asynchronously on every DB read via a fire-and-forget query:

function touchConsumed(currency: string): void {
  sql`UPDATE fx_rates SET last_consumed_at = NOW() WHERE currency_code = ${currency}`.catch(() => {})
}

This tracks which currencies are actively being used. The background refresh cron only refreshes rates that have been consumed within the last 48 hours - rates nobody is looking at are left to expire.

Stale-While-Revalidate

When a DB-cached rate is found but is older than 24 hours, the service does not block the request waiting for a fresh value. It serves the stale rate immediately and triggers a background refresh:

if (cached) {
  touchConsumed(currency)
  const isFresh = (Date.now() - new Date(cached.fetched_at).getTime()) < CACHE_TTL_MS
  if (!isFresh) {
    lookupCollector(currency)
      .then(name => fetchAndCacheRate(currency, name))
      .catch(() => {})
  }
  const rate = cached.rate_vs_pivot as string
  rateProcessCache.set(currency, { rate, cachedAt: Date.now() })
  return rate
}

The user sees a response immediately. The background fetch updates the DB and the in-process cache for the next caller. In practice, FX rates do not move dramatically within a day, so a slightly stale rate is nearly always correct enough.

Background Refresh Cron

Every minute, refreshStaleRates finds currencies that need a refresh and fetches them:

const stale = await sql`
  SELECT ccm.currency_code, ccm.collector_name, dc.rate_limit_per_min
  FROM currency_collector_map ccm
  LEFT JOIN fx_rates fr ON fr.currency_code = ccm.currency_code
  JOIN data_collectors dc ON dc.name = ccm.collector_name
  WHERE fr.currency_code IS NULL
     OR (
       fr.fetched_at < NOW() - INTERVAL '24 hours'
       AND fr.last_consumed_at IS NOT NULL
       AND fr.last_consumed_at > NOW() - INTERVAL '48 hours'
     )
`

A currency qualifies for refresh if it has no cached rate at all, or if it is stale (24h+ old) and has been consumed in the last 48 hours. Currencies nobody has looked at in two days are skipped. Between each fetch, the cron sleeps for 60000 / rate_limit_per_min milliseconds to respect the provider’s rate limit.

Soft Failure with getRateSafe

Some callers - notably the snapshot engine - cannot let an FX failure abort the entire computation. They use getRateSafe:

export async function getRateSafe(from: string, to: string): Promise<string | null> {
  try {
    return await getRate(from, to)
  } catch {
    return null
  }
}

At snapshot time, an asset whose currency rate is unavailable contributes null to FX conversion. The snapshot records the value in the asset’s native currency and skips the base-currency conversion for that entry. The portfolio total may be slightly incomplete but the snapshot does not fail.

Next: Price Feed Queue: Providers, Routing, and Rate Limits