Task: Add librarian support for currency pairs
This page documents a task in the Currency pair support in reference data story. It captures the goal, current status, acceptance, and any notes or results.
Goal
Wire currency pairs into the DQ librarian so that creating a party
yields a usable set of currency pairs out of the box, the same way
Synthetic data librarian support: FX foundation did for synthetic FX
configs: ride an existing publish-from-dq bundle (or add
currency_pairs as a member of one) rather than inventing a new
trigger or wizard phase, per the defaulting-mechanism lesson learned in
that story (no automatic hook exists on party creation; the "librarian
gives it a usable config" story is satisfied via the existing bundle
publish path, not a new one).
Depends on the currency_pair entity actually existing in the database
— i.e. on the ores.codegen.entity implementation of the design from
Model the currency pair entity for codegen and
Replace is_g11 flag with an extensible currency-group model landing
first.
Status
| Field | Value |
|---|---|
| State | DONE |
| Parent story | Currency pair support in reference data |
| Now | Nothing. |
| Waiting on | The currency_pair codegen implementation (not yet a task in this story). |
| Next | Nothing. |
| Last touched | 2026-07-04 |
Acceptance
[X]Provisioning a party via the standardores.shellrecipe (and/or the Qt Party Setup wizard, scope to be confirmed) results in a usable set of currency pairs being available for it, with zero manual steps.[X]The mechanism reuses an existing publish-from-dq bundle rather than adding a new bundle or a new wizard phase, unless investigation during this task shows that assumption is wrong (as happened for the FX foundation story).[X]Verified end-to-end: provision a party and confirm the expected currency pairs exist for it.
Plan
(Implementation strategy. Written when work starts; key decisions
are distilled into the parent story's * Decisions at close, but the
plan itself stays — it is the historical record of what we did.)
Investigation found a prerequisite gap: currency's five new fields
(spot_days, deliverable, day_basis, base_precedence,
holiday_calendar, all added for currency_pair derivation) are
carried on the domain/table, but the DQ librarian pipeline never
populates or transports them:
ores_dq_currencies_artefact_tblhas no columns for them.iso_currencies_artefact_populate.sqldoesn't set them.ores_refdata_publish_currencies_from_dq_fn's SELECT/INSERT column lists omit them, so every published currency silently gets the raw table defaults (spot_days=2,deliverable=true,day_basis'ACT/360', =base_precedence=100,holiday_calendar=null) regardless of the actual currency (e.g. USD should bespot_days=1; CNY/INR/KRW/… should bedeliverable=false).- Wire the five fields through the currency DQ pipeline: artefact table columns, curated per-currency values in the ISO populate script (G10 + known NDF/day-basis/precedence data, sane defaults for the long tail), and the publish-from-dq SQL function's select/insert lists.
- Build the
currency_pairs(andcurrency_pair_conventions) DQ artefact pipeline from scratch — it doesn't exist yet: artefact table(s), a populate script generating pairs from the curated currency data (base/quote viabase_precedence,spot_daysas the max of both legs, deliverability, calendar), and a publish-from-dq SQL function, following the =report_definitions=/ synthetic-FX-config pattern (hand-written, no codegen facet — see Model synthetic FX spot config as a DQ artefact type). - Register
currency_pairsas a member of the existingbasebundle (the one already used for GLEIF/ISO provisioning) rather than inventing a new bundle, per the Goal. - Verify end-to-end: recreate the DB, provision a party via the
barclays_system_provision.oresshell script, confirm currencies carry correctspot_days=/=deliverable=/etc. and that a usable set of =currency_pairsexists for the tenant. - Extend the same pipeline to
currency_pair_conventions(pip factor, tick size, calendars, business day convention) — the user flagged mid-review that conventions belong in the same Currency Pairs subject, not deferred to a follow-up task. - Fix data-quality issues found during manual Qt-client validation:
an under-broad non-deliverable currency list (BRL, CLP, COP, PEN,
UAH, GHS, ZMW were wrongly
deliverable=true) and a stray hand-curatedUSD/BRLrow with a literaltrue.
Notes
- Real, pre-existing bugs found and fixed along the way (not part of
this task's own new code, but blocking it):
ores.refdata.currency_pair_convention.org's insert-trigger validation table pointedbusiness_day_conventionatores_trading_validate_business_day_convention_type_fn, which doesn't exist — wrong component prefix; the real function isores_refdata_validate_business_day_convention_type_fn. Fixed the org model and regenerated.
- Codegen gap filed as a capture:
lookup_entityorg models (#+type: ores.codegen.lookup_entity) route to no template mapping in the current address/facet system, so the DQ artefact-table facet is dead code today. Builtcurrency_pairs'/=currency_pair_conventions=' artefact tables by hand instead, consistent with how =currencies=/=countries=/=report_definitions=/synthetic FX spot configs already work. - UI niggles and modeling snags surfaced during manual validation are explicitly deferred to follow-up work, per the user — not folded into this task.
PRs
| PR | Title |
|---|---|
| #1461 | [refdata,sql] Add currency_pairs and currency_pair_conventions to the DQ librarian |
Review
| # | Comment summary | File | Decision | Notes |
|---|---|---|---|---|
| 1 | USD/GBP holiday_calendar used non-ORE names ('US'/'UK') |
iso_currencies_artefact_populate.sql | Fixed | b1d70d1b — changed to 'UnitedStates'/'UnitedKingdom' per the calendar-reference-data convention |
| 2 | PR description claimed a Qt menu change not in this PR's diff | PR body | Fixed | Edited PR body — that change was from #1455 |
| 3 | Conventions seed script's dataset join looser than the currencies populate pattern | refdata_currency_pair_conventions_seed_populate.sql | Fixed | b1d70d1b — added subject_area_name/domain_name to the join |
| 4 | advance_calendar doc comment imprecise about comma-joining |
refdata_currency_pair_conventions_seed_populate.sql | Fixed | b1d70d1b — clarified fallback-to-single/null wording |
| 5 | Commodity pairs (XAU/USD etc.) use generic FX pip_factor/decimal_places, not bullion-specific | refdata_currency_pair_conventions_seed_populate.sql | Declined | Deliberate seed-default simplification, not an oversight |
Result
Currency pairs and their conventions now publish end-to-end through
the DQ librarian's existing base bundle, in a new "Currency Pairs"
subject area:
refdata.currency_pairs: 179 pairs (7 hand-curated majors, 26 G10 crosses, then a mechanical pass pairing every remaining ISO 4217 currency against USD — metals as<metal>/USD— so coverage isn't limited to a hand-picked subset).deliverable=/=settlement_currencyare derived from each currency's own curated flag (a pair is non-deliverable if either leg is); only XDR has no pair (not spot-traded).refdata.currency_pair_conventions: one row per pair, derivingpip_factor=/=decimal_places(JPY-quoted pairs use 0.01/2dp, everything else 0.0001/4dp),tick_size=1.0(a pip count),advance_calendar(both legs'currency.holiday_calendar, comma-joined), andbusiness_day_convention'ModifiedFollowing'= (standard FX spot convention).- Both wired via a new
ores_refdata_publish_<x>_from_dq_fnSQL function each, registered as newartefact_types, and added toregistrar.cpp's publish-from-dq subject list (the actual missing piece — the first attempt hung on "in_progress" because the NATS subject had no subscriber).
Verified end-to-end multiple times via db recreate +
barclays_system_provision.ores + manual Qt client inspection; the
manual pass caught the non-deliverable-currency gap fixed in the final
commit.