Exchange Rate Management & Revaluation

How a foreign-currency rate is resolved at lookup time, how a pegged currency short-circuits that lookup, and how open foreign balances are periodically re-marked to a base currency

1. Requirements

1.1 Functional requirements

• Resolve a rate between any two currencies for a given date through a fixed precedence: a stored, dated rate record first; failing that, a pegged-currency short-circuit; failing that, a live call to a configured external rate provider; and a defined behavior when none of those produce a number.
• Support separate buy-side and sell-side quotes for the same currency pair and date, so a caller can ask for the rate applicable to a purchase or a sale rather than one blended figure.
• Let an administrator declare that a currency is pegged to another at a fixed ratio, so lookups involving it are satisfied from the peg alone — no network call — unless the peg’s own base still needs a rate against a third currency.
• Let an administrator plug in one of a small number of built-in external rate providers, or hand-template a fully custom one, by declaring an endpoint URL template, request parameters, and a path into the JSON response where the numeric rate lives — no code change to add or swap a provider.
• Run, on an opt-in schedule per legal entity (daily, weekly, or monthly), a batch that finds every foreign-currency balance-sheet ledger account still carrying an open balance, computes what it is worth today in base currency, and books the difference as an unrealized gain or loss.
• Separately settle account/party combinations already at zero in one currency view but not the other — a byproduct of prior partial payments and rounding — by crystallizing the residual rather than leaving a permanent sliver open.
• Let an accountant reverse a posted revaluation’s postings on demand, once, via a mirrored voucher.
• Book, at invoice level, the per-allocation rate difference between an advance’s own posting rate and the invoice’s own conversion rate as a realized gain or loss. The difference itself is computed upstream by the allocation logic in the general-ledger design; producing the actual posting for it is this module’s job.

1.2 Non-functional requirements

• Idempotency: a fetched rate is cached for a fixed window so the same date/pair isn’t re-fetched per call in a batch; reversing the same posting twice is rejected; recomputing a run’s balances replaces the table rather than appending.
• Auditability: a revaluation run is a versioned, submittable record linked to every voucher it produced, recording the old (blended, historic) rate beside the new one per row.
• Fail-soft on the provider path: an outage, malformed response, or unresolvable response path never raises up to the caller — it is logged, surfaced with actionable text, and resolves to a numeric zero rather than crashing the caller’s transaction.
• No deployment to add a provider or a peg: both are data — a configuration record and a ratio registry — never a code change.

1.3 Constraints

• The rate-provider configuration is a singleton — one active template for the whole system, not one per legal entity or currency pair. Every entity’s revaluation run and every ad hoc lookup shares the same provider and credential.
• Only a handful of provider “shapes” are pre-templated; anything else must be hand-built with the same endpoint/parameter/response-path fields as a custom entry.
• The peg registry is likewise global — a declared peg applies to every lookup in the system, not one entity’s revaluation.
• A revaluation run only considers non-group Balance Sheet accounts of type Asset, Liability, or Equity, excluding Stock-valued accounts — it cannot revalue profit-and-loss balances or inventory-valuation accounts.
• The provider credential — an API access key required by one built-in provider — is a single plain-text field on that singleton: one shared secret for the whole system, never scoped per entity or currency.

2. High-Level Design

2.1 Component diagram

2.2 Sequence — the scheduled fetch against an external rate provider

There is no automatic retry: a failed fetch caches nothing, so the next lookup for that date/pair tries the provider fresh.

2.3 Scheduled trigger to posted revaluation

1. A cron-style scheduler fires the daily/weekly/monthly job, querying every legal entity opted in for that frequency, and enqueues one background job per matching entity so a slow provider or a large account batch never blocks the scheduler itself.
2. The job opens a new revaluation run dated today, scoped to that entity, with the default rounding-loss allowance, pulls every candidate account/party balance from the ledger (§ 3.3), and looks up today’s rate for each through the § 2.2/3.2 precedence chain.
3. If at least one row shows a gain or loss, the run is saved and submitted — a Revaluation Posting for rows still genuinely open, a Zero-Balance Cleanup Posting for rows already at zero in one currency view; otherwise nothing posts for that entity this cycle.
4. If the entity also opted in to auto-submission, both postings submit immediately; otherwise they’re left as drafts for review.

3. Deep Dive

3.1 Data model

Exchange Rate Record — a stored, dated quote: date, from_currency, to_currency, exchange_rate (nine-decimal precision), and two independent flags, for_buying/for_selling (both default on), so the same pair/date can carry a buying-only and a selling-only record side by side without colliding. Validation requires a strictly positive rate and distinct currencies, with at least one purpose flag set.

Currency Peg Table — a global registry of entries, each naming a source_currency, the currency it’s pegged_against, and a fixed ratio (stored as free text, parsed at use time — never validated against a live source). A separate system-wide switch gates whether lookups consult it at all.

Rate Provider Configuration — a singleton describing one live adapter: a provider selector (a short pre-templated list plus Custom), an endpoint template, a request-parameter table, and a response-path table describing how to walk the JSON reply to the numeric rate. One pre-templated provider additionally requires an access-key credential — entered once here and reused for every request the whole system makes. A disabled flag turns off the live-fetch path entirely. Choosing anything but Custom locks the template fields to that provider’s fixed shape. Saving the configuration (outside test/setup contexts) immediately fires one validation request with placeholder values, catching a broken template or bad credential at configuration time.

Revaluation Run — a submittable, per-legal-entity, per-posting-date record holding a rounding_loss_allowance (0–1, default 0.05, below which a residual in either currency view is clamped to zero before computing gain/loss), a table of Revaluation Lines, and three totals: gain/loss already crystallized on zero-balance rows, gain/loss still open on live rows, and their sum. Rows with no gain/loss at all are dropped before submission, and an empty result blocks submission.

Revaluation Line — one row per account/party combination carrying an open foreign balance: account, its currency, party (for Receivable/Payable accounts), balance in account and base currency, the current (blended, historic) rate, the freshly looked-up new rate, the recomputed base-currency balance, the resulting gain/loss, and a zero_balance flag for rows already at zero in one currency view.

Realized vs. unrealized gain/loss accounts — the legal entity designates two distinct Ledger Accounts: one absorbs the realized, per-allocation difference booked immediately at invoice level (the Realized Adjustment Posting); the other absorbs the periodic, unrealized mark-to-market swing from a revaluation run. The run hard-stops if its own unrealized-side account isn’t configured — there is no fallback to the realized-side one.

3.2 Algorithm — rate resolution precedence

Given (from_currency, to_currency, transaction_date, purpose?):

1. If from == to: return 1.
2. Default transaction_date to today if not supplied.
3. Find the most recent Exchange Rate Record with date <= transaction_date,
   matching currencies, and (if purpose given) the matching buy/sell flag.
   If "allow stale rates" is off, also require date > transaction_date - stale_days.
   Match found -> return its rate. STOP. (Always wins if present.)
4. Rate Provider Configuration disabled -> return 0.00. STOP.
5. If pegged-currency lookups are enabled, consult the Currency Peg Table:
     a. both pegged to the same base   -> combine both ratios, no fetch.
     b. both pegged to different bases -> resolve a rate between the two
        bases (may itself reach step 6), then combine with both ratios.
     c. from is pegged directly to to  -> return that ratio, no fetch.
     d. to is pegged directly to from  -> return its inverse, no fetch.
     No match -> fall through to step 6.
6. Rate Cache hit for (date, from, to) -> use it. Miss -> build the templated
   request (substituting each currency's pegged-against base where
   applicable), call the provider, parse the rate along the configured
   response path, cache it for 6 hours. Either way, adjust the value by any
   applicable peg ratio before returning it.
7. Any failure in step 6 -> log it, prompt for a manual rate record, return 0.0.

The precedence is strict: a stored record always wins over a peg, a peg always wins over a live fetch, and nothing downstream double-checks something already resolved upstream.

3.3 Algorithm — what a revaluation run computes

Selection. One grouped query over the ledger: non-group leaf accounts, Balance Sheet report type, root type Asset/Liability/Equity, account type not Stock, scoped to the entity, account currency different from base currency, as of the posting date. Grouped by account and party, so a Receivable/Payable account gets one row per open party. A row survives only if its base- and account-currency views actually differ (or either is nonzero).

Rounding-loss allowance. Any balance whose absolute value is at or below the run’s allowance is clamped to zero first, so past-rounding pennies aren’t treated as live exposure.

Two paths per row. Still open (zero_balance = false): the current rate is derived — existing base-currency balance ÷ existing account-currency balance, i.e. the blended rate already in the ledger; the new rate is a fresh § 3.2 lookup for (account_currency, base_currency, posting_date); gain/loss is the recomputed base-currency balance minus the existing one. Already zero in one view (zero_balance = true): there is no live exposure to re-rate, so the “current rate” is read off the last ledger posting that touched that account/party, and the whole residual is booked so both views land on zero.

Posting. A Revaluation Posting carries the still-open rows — a leg at the new rate and an offsetting leg at the old rate per row, net difference to the unrealized account. A Zero-Balance Cleanup Posting carries the already-zero rows, offsetting the same way. Neither posts if its bucket is empty after the rounding-loss clamp; the run throws if both are.

Reversal. Once posted, the form offers a “create reversal” action — not an automatic follow-up. Triggering it mirrors every leg (debit/credit swapped, same rate, same references), dated the day it’s clicked, and submits immediately. It’s one-shot: reversing the same posting twice is rejected. Nothing in the scheduler reverses a prior period’s revaluation automatically at the start of the next one; that timing is entirely manual.

3.4 Error handling

• Provider disabled: every live lookup reaching that point silently returns 0.00 — a caller that doesn’t guard against a zero rate can post at a rate of zero unnoticed.
• Provider failure (network error, non-2xx, unparseable response, missing response-path key): caught, logged, surfaced with manual-entry guidance, resolved to 0.0.
• Stale-rate rejection: with “allow stale rates” off, a record older than the tolerance window is treated as absent, falling through to peg/provider instead of returning an outdated figure.
• Empty run: submission is blocked once every zero-gain/loss row is dropped and nothing remains.
• Missing unrealized-side account: posting fails hard with a prompt to configure it; no fallback account.
• Double reversal: rejected outright rather than creating a second offsetting entry.
• Cancellation: cancelling a submitted run does not force-cascade into the ledger postings it produced — decoupled from theirs.

4. Scale and Reliability

• Cache absorbs repeat calls. A run over many foreign-currency accounts routinely asks the same (currency, base, today) triple repeatedly; the 6-hour window means the provider is typically hit once per distinct pair per run, not once per row.
• Background execution. The scheduler enqueues one job per matching entity rather than computing inline, so a slow or unreachable provider degrades one entity’s run, not the scheduler itself.
• A single shared external dependency. Every ad hoc lookup not satisfied by a stored record or peg depends on the same singleton provider and credential; an outage can make an entire cycle’s unrelated entities resolve to 0.00 at once — system-wide, not isolated.
• No visible locking on the balance scan. The selection query reads a live ledger snapshot; nothing stops an ordinary posting landing on the same account between that scan and the run’s submission — a narrow window for the computed balance to go stale.
• What to monitor: the error log for failed fetches (outage and misconfigured template log identically); runs producing zero candidate rows; and the age of any unreversed revaluation posting from a prior period.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Disambiguate “no rate” from “rate is zero.” A disabled provider and an unrecoverable failure both return the same 0.0 a genuinely zero rate would; a distinguishable sentinel or a caller-visible exception would remove a class of silent bad postings.
• Per-entity or per-pair provider configuration, once currency diversity across legal entities makes one shared provider and one shared credential a bottleneck and a single blast radius.
• Concurrency guard on the balance scan, as run frequency or account volume grows and the window between snapshot and submission starts to matter more.
• Surface unreversed prior-period postings proactively — an aging report or reminder, since reversal is entirely manual today.
• Validate peg ratios periodically against the very provider they’re meant to bypass, rather than trusting a manually-entered figure indefinitely.
• Per-currency rounding-loss tolerance, since one global allowance fraction doesn’t fit currencies of very different typical magnitude equally well.