Exchange Rate — multi-currency conversion table

The whole exchange module reduces to one idea: a tiny lookup table of (fromCurrency, toCurrency) → rate rows. It stores nothing more. Everything that uses a rate (orders, finance legs) copies a single exchangeRate number onto its own document at entry time and never re-reads this table. So the table is a convenience lookup for the data-entry UI, not a live FX engine.

Source: BE src/modules/exchange · Admin src/modules/exchange · consumed in src/modules/inventory/order, src/modules/finance/transaction

1. Purpose & scope

The Exchange module owns a single collection, exchange_rates, holding manually-entered conversion rates between two currencies. It exposes CRUD plus one lookup query (exchangeRate) that the admin order screen calls to pre-fill an order's exchangeRate.

It does NOT:

• Fetch live/market rates from any external provider — every rate is hand-keyed by an operator (src/modules/exchange/exchange.dto.ts CreateExchangeInput).
• Store rate history or effective-date ranges — there is a date field but no version chain; exchangeRate(from, to) returns the single matching row (exchange.repository.ts findOne).
• Apply the rate to any amount itself. Conversion math lives in the consuming modules (order, transaction). See §4.
• Define currencies. Currencies are Master records under the currency master key; this module only references them by id. See config.md for the config/lookup system and master-reference-data.md for the Master hierarchy.

2. Data model

Collection: exchange_rates (exchange.schema.ts)

Represents one directional conversion rate from currency fromId to currency toId.

Plus the standard BaseSchema envelope (_id, ref, companyId, branchId, createdAt, updatedAt, createdBy, updatedBy, soft-delete deletedAt/deletedBy).

// exchange.schema.ts
@ApSchema({ collection: "exchange_rates" })
export class Exchange extends BaseSchema {
  @Prop({ set: BaseSchema.toObjectId })
  fromId: Types.ObjectId;
  @Prop({ set: BaseSchema.toObjectId })
  toId: Types.ObjectId;
  @Prop()
  rate: number;
  @Prop()
  date?: number;

  from?: string;   // non-persisted, populated by lookup/resolver
  to?: string;     // non-persisted
}
ExchangeSchema.plugin(SoftDelete, { deletedAt: true, deletedBy: true });

Enums: none. There is no status/type enum on this collection.

Relationships & scoping:

• fromId / toId → masters collection (currencies). The repository joins them via $combineLookup ($lookupFrom + $lookupTo, both $lookup against masters).
• Soft-deleted via mongoose-delete; deleted rows are excluded from all reads.
• Tenant/branch scoped through BaseSchema + the tenant DB connection (see ../../platform/multi-tenancy.md). Each tenant maintains its own rate table.

Derived fields: from / to are not stored — resolved per-request. The repository's findOne aggregates the lookups first, then matches; the resolver's from/to resolve-fields fall back to masterSvc.findById(fromId|toId) when the parent didn't already carry the joined object.

3. API surface

All operations are GraphQL, guarded by @ApGqlAuthorize() at the resolver class level (exchange.resolver.ts). Mutations are wrapped with @AuditMeta({ module: 'exchange', collection: 'exchange_rates', ... }).

GraphQL types (src/schema.gql):

type Exchange {
  _id: String  ref: String  companyId: String  branchId: String
  fromId: String!  toId: String!  rate: Float!  date: Float
  from: Master   to: Master
}
input CreateExchangeInput { fromId: String!  toId: String!  rate: Float!  date: Float }
input UpdateExchangeInput { fromId: String   toId: String   rate: Float   date: Float }
input ExchangeRateInput  { from: String!  to: String! }   # NOTE: by currency NAME, not id

exchangeRate(rate: ExchangeRateInput!): Exchange

The exchangeRate lookup keys on currency name, not id. ExchangeService.rate({from, to}) → exchangeRepo.findOne({ from, to }), and buildQuery translates those into { 'from.name': from, 'to.name': to } after the $lookup join (exchange.repository.ts). The admin therefore passes customer.currency.name and the company's local currency name, not ObjectIds.

4. Business rules & calculations

4.1 Rate entry & validation

• A rate is a plain multiplier (1 fromUnit = rate toUnit). No inverse is auto-created — if you need USD→NGN and NGN→USD, you enter two rows.
• Admin-side validation (exchange/components/create.tsx, Yup): fromId and toId required currency objects, rate required, numeric, positive (> 0). BE has no class-validator decorators on the inputs beyond GraphQL non-null — the positivity guard is UI-only.
• No uniqueness constraint on (fromId, toId) — duplicates are possible; findOne returns the first match after the lookup.

4.2 How a rate reaches an order — the lookup

The lookup is a read-time convenience used only when creating/editing an order in the admin (inventory/order/new.tsx):

// new.tsx — getExchangeRate(customer)
const getExchangeRate = async (customer): Promise<number> => {
  if (!customer?.currency?.name || customer.currency.name === localCurrency) return 1;
  const result = await getRate({ from: customer.currency.name, to: localCurrency });
  return result?.rate > 0 ? result.rate : 1;   // ← fallback to 1 when no row / non-positive
};

If no rate row matches, the order silently uses 1. getRate resolves to { rate: 0 } on a miss (exchange/context.tsx getRate), and every consumer coerces a non-positive rate to 1. This is the client-side mirror of the safeRate rule below.

The resolved number is written onto the Order.exchangeRate field (default 1) and then copied down onto every order item and every finance leg the order produces. The exchange table is never read again for that order — the order owns its rate snapshot.

4.3 Applying the rate — conversion math lives in consumers, not here

The stored exchangeRate converts a line amount from the document/foreign currency into the company base currency:

• Order item entry (admin): when editing an existing line, the foreign-currency rate/amount is recovered by dividing by the order rate (add-or-update-item.tsx):

  rate:   (item.rate   || 0) / (order?.exchangeRate || 1),
  amount: (item.amount || 0) / (order?.exchangeRate || 1),

  and on submit the entered value is multiplied back by er = +values.exchangeRate > 0 ? … : 1 (new.tsx).
• Finance legs: the order copies exchangeRate onto every AccountTransaction it writes — bill, tax, COGS, inventory, payment legs all carry order.exchangeRate (inventory/order/order.transaction.ts; sales/purchase item.service.ts repair order.exchangeRate onto their COGS/inventory/revenue legs when the order rate ≠ 1).
• Payment legs: never persist a falsy rate — payment.service.ts coerces model.exchangeRate = model.exchangeRate || 1 on both create() and addSalesPayment() (regression-tested in payment.service.spec.ts).

4.4 The safeRate rule — the critical invariant

When finance aggregates base-currency totals (trial balance, ledger, P&L), it multiplies each leg's amount by its exchangeRate. A leg accidentally stored with exchangeRate: 0 would multiply to zero and silently drop that leg, throwing the trial balance out of balance. The transaction repository defends against this:

// finance/transaction/transaction.repository.ts → totalDrAnCr()
// A 0 (or null) exchangeRate must NEVER zero out a posted amount — it is an
// invalid rate, not a real "multiply by zero". Treat 0/null as 1 here.
// ($ifNull alone does not catch 0.)
const safeRate = {
  $cond: [{ $or: [{ $eq: ["$exchangeRate", 0] }, { $eq: ["$exchangeRate", null] }] }, 1, "$exchangeRate"]
};
const $sum = (type) => ({ $sum: { $cond: {
  if:   { $eq: ["$type", type] },
  then: witExchangeRate ? { $multiply: ["$amount", safeRate] } : "$amount",
  else: 0,
}}});

The same (exchangeRate || 1) coercion appears wherever finance reads rates: journal.service.ts (amount / (trans.exchangeRate || 1)), journal.controller.ts, report.service.ts, report.controller.ts. Rule of thumb for any port: never trust a stored exchangeRate to be truthy; coerce 0/null → 1 at read time. Full detail of how legs use the rate is in ../finance/transaction.md.

State machine: none — exchange_rates rows have no status lifecycle.

Side effects of CRUD: create/update/delete only write the audit trail (@AuditMeta). No GL legs, no stock. Deleting a rate does not retro-change any order — orders carry their own snapshot.

Transactionality: the service extends AbstractBaseService with a no-op setSession; CRUD here is single-document and not part of a larger Mongo transaction.

5. Permissions

• Resolver-level @ApGqlAuthorize() gates every operation (JWT + permission check — see ../../platform/permissions-access.md).
• Admin UI gates the "New Rate" button on USER_ACCESS.EXCHANGES.MODULE / .ACTIONS.CREATE (exchange/page.tsx).
• Mutations stamp the audit trail via @AuditMeta({ module: 'exchange', collection: 'exchange_rates' }). No CASL abilities specific to this module.

6. Flows

6.1 Create / edit a rate (admin)

1. Admin opens Exchanges page (/exchanges) → fetchExchangePage(filter) → exchangePage query.
2. Clicks New Rate → CreateExchange modal (Formik + Yup).
3. Selects From Currency and To Currency via ApMasterSelectInput masterKey="currency" (currencies come from the Master tree), enters a positive rate.
4. Submit → payload flattens fromId/toId to their _id and parseFloat(rate) → saveExchange(_id, payload).
   • No _id → createExchange mutation → ExchangeService.create → repo insert → audit CREATE.
   • Existing _id → updateExchange → repo update → audit UPDATE, then fetchExchangePage reloads.
5. Unhappy path: missing fromId._id/toId._id → submit aborts client-side; non-positive rate → Yup blocks submit; GraphQL errors → toastSvc.graphQlError.

6.2 Consume a rate during order entry (the real use)

1. Admin creates an order and selects a customer/supplier (inventory/order/new.tsx).
2. If the counterparty's currency name ≠ company localCurrency, the screen calls getRate({ from: customer.currency.name, to: localCurrency }) → exchangeRate query → exchangeRepo.findOne({ 'from.name', 'to.name' }).
3. Returned rate (or 1 on miss / non-positive) is set as Order.exchangeRate and used to reprice lines (repricedItems, getOrderLineRate).
4. On checkout the order copies that one exchangeRate onto every item and every finance leg (../finance/transaction.md). The exchange table is not consulted again.

6.3 Read base-currency totals (finance)

• Trial balance / ledger aggregations multiply amount × safeRate(exchangeRate) (§4.4). A 0/null rate is treated as 1, so a leg is never silently zeroed.

7. Admin UI

• Route: src/pages/exchanges/ (index renders the module page). Module screen: exchange/page.tsx.
• Components:
  • components/exchangeTable.tsx — the rate list (exchanges, actions).
  • components/create.tsx — create/update modal; FormSchema (Yup) requires fromId/toId currency objects and a positive rate; uses ApMasterSelectInput masterKey="currency" and ApTextInput type="number".
  • detail.tsx — single-rate view.
• Context (context.tsx) — the only gql consumer. Methods: fetchExchangePage, saveExchange (routes to createExchange/updateExchange), deleteExchange, findOneExchange, and getRate (exposed to other modules, notably order entry). State: exchanges, exchange, filter, totalRecords, modal, loading.
• GraphQL (gql/query.ts): EXCHANGE_PAGE, FIND_ONE_EXCHANGE, GET_RATE, CREATE_EXCHANGE, UPDATE_EXCHANGE, DELETE_EXCHANGE; page and rate use fetchPolicy: 'no-cache'. Also exports SSR helpers findOneExchangeAsync / findExchangePageAsync (the latter forces skip:0, take:1000).
• UX: inline currency create flows through the master select; no import/print here.

8. Dependencies & integrations

• MasterModule — from/to resolve-fields call masterSvc.findById; currencies are Master records (master module, currency key).
• AuthModule — @ApGqlAuthorize + audit.
• Consumed by inventory/order — getRate pre-fills Order.exchangeRate (admin), which then flows into stock-less order math and finance legs.
• Consumed by finance/transaction, finance/journal, finance/report — read the copied exchangeRate (never this table) and apply the safeRate coercion. See ../finance/transaction.md.
• No cron, no external FX provider, no events emitted/consumed.

9. Gotchas & project-specific rules

• Lookup is by currency name, not id. exchangeRate(from, to) matches from.name / to.name post-join. Renaming a currency Master breaks existing lookups even though the rate row is intact.
• No effective-dating / history. date is stored but ignored by selection. One row per direction is assumed; duplicates resolve to the first match.
• Rates are a snapshot, not a live link. Orders and finance legs copy exchangeRate at entry time. Editing the rate table never retro-updates posted documents — by design.
• 0/null rate is always coerced to 1 (both client getExchangeRate and server safeRate / payment.service / journal / reports). $ifNull is deliberately not used because it does not catch a literal 0.
• No inverse rate auto-creation — both directions must be entered if both are needed.
• ExchangeRepository.findOne logs to console (console.log("ExchangeRepository.findOne", …)) — noise/TODO in current code.
• Tenant-scoped — each tenant DB has its own exchange_rates; there is no platform-wide/master-DB rate table.