Data Import Session and Workflow Partial-Success
Full ORE data import pipeline; pending trade staging; non-live marker for reference data; structured workflow failure reporting

The current ORE import workflow writes trades directly into ores_trading_trades_tbl (live books) with no intermediate validation or enrichment stage. Real-world trade data is routinely dirty: missing counterparties, zero amounts, unrecognised instrument parameters, mismatched currencies. Accepting this data directly into live books is dangerous because:

• Bad trades appear in risk/compute runs immediately.
• There is no structured way to bulk-fix missing reference data (counterparty, book) before committing.
• Instrument creation failures leave orphaned trade rows.
• There is no clear user workflow from "imported" to "verified and live."

Market data, conventions, curves, and pricing engine configurations land in the main tables immediately and are indistinguishable from manually curated or Bloomberg-sourced data. Users have no way to know which data came from an ORE import and which is production-quality.

ORE import actions appear in both the Trades window toolbar and Portfolio Explorer. The import entry point should be a dedicated Data Import facility, not the live-book views.

The existing workflow conflates trade staging with market data, conventions, curves, and pricing configuration — all of which have different lifecycle requirements.

The workflow instance FSM only has completed and failed terminal states. A partial import shows completed — indistinguishable from a clean run.

ore_import_execute_result.item_errors is already collected but only surfaced as flat log text. There is no way to render a table of failed trade IDs with files and error messages.

Three concepts drive the new design:

1. Data Import Session — a single import event that brings in a full ORE data set (trades + market data + conventions + curves + pricing configs). Everything imported in one session shares a common session_id for lineage.
2. Pending Book — a system-defined book that holds imported trades before they are enriched and promoted to a live book. Trades in the Pending Book are visible in the normal trade views but clearly marked. They cannot be used in compute runs until booked into a real book.
3. Non-live marker — a simple is_live boolean not null default false on every reference-data table touched by ORE import (conventions, curves, pricing engine configs, market data). Only rows with is_live = true are used by compute runs and visible in normal reference-data views. The import session notebook lets users review and activate imported rows explicitly.

The import handler processes a complete ORE data directory in a single pass:

• Portfolio XML files → Pending Book trades (pending trade staging)
• Market data files → market data tables with is_live = false
• Conventions XML → convention tables with is_live = false
• Curve configs → curve config tables with is_live = false
• Pricing engine configs → pricing engine tables with is_live = false

All rows created in a session carry import_session_id for lineage. There is no deferred "Phase 2" for reference data — it is imported together with trades from the start.

The "Data Transfer" top-level menu is kept and extended:

Data Transfer
  ├── Data Import        → opens Data Import Session window
  └── Data Export        → (future)

The scattered import actions in the Trades window and Portfolio Explorer are removed; all import activity flows through Data Transfer → Data Import.

User clicks Data Transfer → Data Import
        │
        ▼
  Directory picker (selects an ORE data directory or group)
        │
        ▼
  data_import workflow triggered
        │
        ├── Portfolio XML → Pending Book trades (staged, validated)
        ├── Market data   → market data tables (is_live = false)
        ├── Conventions   → convention tables (is_live = false)
        ├── Curves        → curve config tables (is_live = false)
        └── Pricing cfg   → pricing engine tables (is_live = false)
        │
        ▼
  Data Import Session window auto-opens (or refreshes) showing new session

A persistent MDI window accessible via Data Transfer → Data Import. Shows import sessions with a two-level selector at the top. The body is a notebook with one tab per data type. Both selectors scope all tabs simultaneously.

+-------------------------------------------------------------------------------------+
|  DATA IMPORT SESSION                                       [New Import] [Refresh]    |
|  Batch: [ 2026-05-17 10:15 — ORE XML — /ore/examples           ▼ ]                 |
|  Unit:  [ All units (34)                                        ▼ ]                 |
+-------------------------------------------------------------------------------------+
| [ Trades (847) ] [ Market Data (312) ] [ Conventions (45) ] [ Curves ] [ Pricing ] |
+-------------------------------------------------------------------------------------+
| [ tab-specific grid + actions ]                                                     |
+-------------------------------------------------------------------------------------+

Dropdown listing recent import batches, most recent first. Each entry shows: the batch timestamp, source type (ORE XML, FpML, …), source directory (truncated), and a summary badge (e.g. "34 units · 847 trades · 12 invalid"). A search/filter allows finding batches by date or path.

Searchable dropdown scoped to the selected batch. First entry is always "All units (N)". Remaining entries are the display names derived from each unit's relative orchestration XML path (see naming heuristic below). Selecting a unit narrows every tab to that unit's data only.

Given the orchestration XML path relative to the batch root:

1. Split into directory components; drop the filename.
2. Remove uninformative folder names: Input, ExpectedOutput, Notebooks, Calc, bak, xlwings, Helpers, Dependencies.
3. Join remaining components with · (e.g. Legacy · Example_1).
4. If the orchestration filename stem is not plain ore, strip the ore_ prefix (or _ore suffix) and append the variant name (e.g. MarketRisk · sensi, CurveBuilding · BootstrapConsistency · EUR_xois).
5. If two units in the same batch produce the same display name, append a numeric suffix to make them unique.

Examples:

This is the actionable tab — trades in the Pending Book need user enrichment before they can be booked into a live book.

Layout: filter bar → action bar → virtual-scroll grid → inspector panel (bottom 30% when a row is selected).

Filter bar:

• Status: All / Invalid / Ready / Booked / Rejected
• Error Type: All / Missing Book / Missing Counterparty / Zero Amount / …
• Trade Type: All / FxForward / IRS / FxOption / …
• Free-text search (External ID, raw counterparty)

Action bar (bulk actions activate on selection):

Grid columns:

Cell-level error highlighting: cells with invalid data render with an amber background and a hover tooltip from validation_errors explaining the specific error and suggested fix. Grid uses virtual/lazy rendering (QAbstractItemModel + proxy) to stay responsive with large portfolios.

Inspector panel (bottom ~30%, shown on single-row selection):

Left column — Trade Parameters:

• Parsed fields from raw_data (read-only)
• Source provenance: session, directory, file, element index
• NPV if valued

Right column — Validation Errors + Audit Trail:

• Errors table: Severity | Field | Message | Fix hint (blocking first in red, warnings in amber)
• Audit trail: timestamped log of system and user actions

Post-import behaviour:

• Window auto-opens (or refreshes) filtered to new session
• Summary banner: "N trades staged — M ready, K invalid"
• If all valid: "Book All Ready" prompt shown
• If some invalid: filter pre-set to Invalid subset

Read-only grid showing market data rows imported in the session, with their is_live status. Columns: Name | Type | Date | Is Live | Source.

Bulk actions: "Mark as Live" (sets is_live = true on selection) and "Discard" (deletes non-live rows). Warning shown at activation time if an existing live row with the same key would be shadowed.

Same pattern as Market Data. Lists convention rows from the session. Bulk "Mark as Live" / "Discard." Dedup warning if an existing live convention has the same code.

Curve configuration rows from the session. Same is_live model.

Pricing engine configuration rows from the session. Same is_live model.

The Pending Book is a system-defined book created at database initialisation. It is not selectable by users when creating trades manually. Its sole purpose is to hold imported trades before enrichment.

Pending Book properties:

• Fixed UUID, seeded in populate scripts
• Name: "Pending" (or "Import Staging")
• is_system = true on the book record — filtered out of book picker dropdowns
• Visible in the trade list view as a special book with an amber indicator

1. Import handler inserts trade into ores_trading_trades_tbl with book_id = pending_book_id, status = pending (new FSM state), and validation_errors populated.
2. User sees trade in Data Import Session → Trades tab (and in the main Trades window filtered to Pending Book).
3. User assigns book and counterparty; validation re-runs; status moves to ready.
4. User clicks "Book Selected" → booking service reassigns book_id to the target book, clears validation_errors, transitions status from pending to new.

Trades in pending status must tolerate nullable counterparty_id and nullable book_id (the pending book is assigned but the real book is not yet known). This means the NOT NULL constraints audit defers to the booking service: the service enforces that a real book and counterparty are assigned before transition from pending to new.

Add is_live boolean not null default false and import_session_id uuid null to every reference-data table touched by ORE import:

At import time all rows land with is_live = false. Compute runs and normal UI data-loading queries filter on is_live = true (or current live rows only).

User reviews rows in the relevant tab of the Data Import Session notebook. Bulk "Mark as Live" sets is_live = true on selected rows.

Dedup strategy: before activation, the service checks for an existing live row with the same natural key. If found, it warns the user: "Activating this row will supersede existing live [Convention / Curve / …] with code X." User can proceed or cancel.

Non-live rows are hidden from normal reference-data grids by default. A "Show Non-Live" toggle (or filter) reveals them with an amber "Pending" badge. This keeps normal data management views clean while preserving the data for review in the Data Import Session.

create table ores_data_import_sessions_tbl (
    id                uuid        not null default gen_random_uuid(),
    source_type       text        not null,  -- ore_xml | fpml | csv | api | manual
    source_reference  text        not null,  -- directory path, URL, system name
    source_checksum   text        null,      -- hash of directory manifest for dedup
    source_metadata   jsonb       null,      -- file counts, schema versions, etc.
    workflow_run_id   uuid        null,
    imported_by       text        not null,
    imported_at       timestamptz not null default now(),
    constraint ores_data_import_sessions_tbl_pk primary key (id)
);

Note: this replaces the earlier ores_trading_import_sessions_tbl design. The session table lives in a shared schema (not trading-specific) since it spans all data types.

Trades in the Pending Book remain in ores_trading_trades_tbl. Additional columns needed:

alter table ores_trading_trades_tbl
    add column if not exists import_session_id  uuid null,
    add column if not exists validation_errors  jsonb null,
    add column if not exists source_position    text null,   -- "filename:index"
    add column if not exists raw_data           text null;   -- original XML fragment

The status_id FK already exists (pointing to the trade status FSM). A new pending state is added to the FSM (see below).

After booking (pending → new), the lineage columns persist: import_session_id and source_position remain set so that auditors can trace any live trade back to its source file and import session.

alter table ores_trading_trades_tbl
    add column if not exists npv             numeric null,
    add column if not exists npv_currency    text null,
    add column if not exists npv_computed_at timestamptz null;

Included now to avoid a future migration.

-- Example pattern applied to each affected table:
alter table ores_refdata_conventions_tbl
    add column if not exists is_live           boolean not null default false,
    add column if not exists import_session_id uuid null;

Full list of tables to audit and patch is part of Phase 1 work.

19 top-level subdirectories with mixed PascalCase / hyphenated naming: Academy, AmericanMonteCarlo, CreditRisk, CurveBuilding, Exposure, ExposureWithCollateral, InitialMargin, Input, Legacy, MarketRisk, MinimalSetup, ORE-API, ORE-Python, Performance, Products, ScriptedTrade, TradeGenerator, XvaRisk.

Two are meta-containers:

• Legacy/ — 75 numbered child examples (Example_1 … Example_78, with gaps).
• Academy/ — 3 prefix-coded children (FC003_…, TA001_…, TA002_…).

Two are non-runnable and must be skipped:

• Input/ — shared fixture files, no ore.xml, no portfolio.xml.
• TradeGenerator/ — only a Readme.md.

The universal layout is <Group>/Input/<files> with <Group>/ExpectedOutput/ alongside. All simulation files live inside Input/, never at the group root (with one anomaly: Legacy/Example_48 has an ore.xml at the group root as well as under Input/).

Max nesting depth: 5 levels (ORE-Python/Notebooks/Example_8/Input/Example_57/ore.xml).

Key file distribution:

• ore.xml / ore_*.xml / *_ore.xml — always inside Input/ (or a nested Input/<Variant>/ sub-directory).
• portfolio*.xml — inside Input/; naming is highly variable (see below).
• market*.txt, fixings*.txt, conventions*.xml, curveconfig*.xml, pricingengine*.xml, todaysmarket*.xml — inside Input/; for multi-variant groups, common copies sit at Input/ and per-variant copies sit at Input/<Variant>/.

The plain-name ore.xml is rare in modern groups. Most multi-variant groups use only ore_*.xml variants (no plain ore.xml). Selected counts:

Naming is highly variable — portfolio.xml is only the most common variant:

• portfolio.xml (~60 occurrences)
• portfolio_<thing>.xml (very common — swap, swaption, capfloor, fxtarf, etc.)
• portfolio<N>.xml / portfolio_{1..5}.xml (CreditRisk, Legacy/Example_43, Legacy/Example_6)
• Instrument-named files: irswap.xml, eqoption.xml, swap_eur.xml (Academy, InitialMargin/Dim)
• portfolio_cpr_{0,5,…}.xml (parametric suffix — Legacy/Example_65)
• Never Portfolio.xml (capitalised) or trades.xml

Portfolio XLM is not reliably discoverable by name alone. The orchestration XML explicitly names the portfolio file in its <Portfolio> element.

Typical range: 1–15 for didactic examples; 30–200 for benchmark portfolios.

Neither ore.xml alone nor the top-level group folder is a reliable anchor:

• Literal ore.xml misses the majority of runs in modern groups (which use ore_*.xml or *_ore.xml).
• Top-level group folder is too coarse: Legacy/ wraps 75 unrelated examples.

The reliable anchor is any orchestration XML — a file matching the glob ore*.xml OR *_ore.xml in any non-output, non-backup directory. The orchestration XML then serves as the manifest: it explicitly names every input file (portfolio, market, fixings, conventions, curveconfig, pricingengine, todaysmarket, simulation). The import handler must parse the orchestration XML to discover the actual file set rather than relying on directory globs.

1. Legacy/ and Academy/ are meta-containers — one group session per child, not per top-level folder.
2. ORE-Python/ has its own Input/ore.xml plus 9 independent notebook sub-examples and 2 deeply-nested sub-runs.
3. MarketRisk/ — 27 distinct orchestrations in one folder; must be treated as 27 units, not one.
4. Exposure/ — 34 ore_*.xml orchestrations, flat layout.
5. ExposureWithCollateral/ — 13 top-level orchestrations plus a self-contained FirstMpor/ nested run (its own ore.xml, portfolio, market data).
6. CurveBuilding/BootstrapConsistency/ — uses *_ore.xml suffix (EUR_xois_ore.xml, ois_ore.xml, USD_xois_ore.xml); glob ore*.xml alone misses these. Discovery glob must also match *_ore.xml.
7. Legacy/Example_48 — ore.xml at group root AND Input/ore.xml; if identical (packaging artefact) keep one; if different, keep both as two units.
8. Input/ (top-level) — shared fixtures only; must be skipped.
9. TradeGenerator/ — no simulation files; skip.
10. Products/Example_Trades/ and Products/SupportedTrades/ — trade catalogues, not runnable simulations; skip.
11. Editor backup files (#ore_sensi.xml#, etc.) — filter with regex ^#.*#$.
12. ExpectedOutput/ trees — never recurse into directories named ExpectedOutput, bak, Calc, xlwings, Helpers.

Option D — Batch import with parent/child sessions, anchored on orchestration XML, with file set discovered by parsing the orchestration XML.

Analysis completed 2026-05-17. See "ORE Examples Import Unit Analysis" section above for full findings. Decision: Option D (batch import with parent/child sessions) anchored on orchestration XML, file set discovered by parsing the orchestration XML. parent_session_id is required on the session table. Phase 2 may proceed.

1. Create ores_data_import_sessions_tbl with nullable parent_session_id (batch → unit session hierarchy; see analysis section).
2. Add pending columns to ores_trading_trades_tbl.
3. Seed Pending Book record.
4. Add pending state + transitions to trade status FSM.
5. Add completed_with_warnings to workflow instance FSM.
6. Add is_live + import_session_id to reference data tables.
7. Rename workflow definition to data_import.
8. Add grants; run recreate_database.sh; run schema validation.

1. Add new columns to trade domain type and entity.
2. Implement booking_service: validate → create instrument → update book/status.
3. Wire NATS handlers: list_pending, book_pending, reject_pending.
4. Unit tests.

1. Rename to data_import_execute_handler.
2. Implement full directory scan: classify files by type.
3. Stage trades to Pending Book; stage ref data with is_live = false.
4. Return completed_with_warnings for invalid entries.
5. Update planner/scanner.

1. Amber badge for completed_with_warnings.
2. failed_items table in step details.
3. "Show in Data Import Session" link.
4. Update step-completion handler.

1. Remove import actions from Trades window and Portfolio Explorer.
2. Delete ImportTradeDialog.
3. Add Data Import item under Data Transfer menu.
4. Wire DataImportSessionController.

1. DataImportSessionWindow: session selector + notebook.
2. PendingTradesTab: filter bar, action bar, virtual grid, inspector panel.
3. ImportedDataTab: reusable for market data / conventions / curves / pricing.
4. Bulk actions: Assign Book, Assign Counterparty, Validate, Book Selected, Reject, Mark as Live, Discard.
5. Post-import auto-open and summary banner.
6. Reserve NPV column and "Value Selected" (disabled placeholder).

Bad trades stay in the Pending Book rather than reaching the live table in a broken state. The import_failed state, import_error column, and mark_trade_import_failed endpoint from earlier design iterations are not required.

Only trades with status = new (or later) in a real book are visible to compute runs. Pending Book trades are invisible to the risk engine by design.

The compute service must be audited to ensure all reference-data queries filter on is_live = true. This is a correctness requirement, not just a UI concern.

When the user activates a non-live row that has the same natural key as an existing live row, the service must warn before overwriting. The previous live row should be archived (is_live = false, superseded_at timestamp) rather than deleted, to preserve history.

The Data Transfer menu is designed to accommodate export alongside import. Export is out of scope for this plan but the menu structure should reflect it.

The old workflow type name must be updated to data_import. Historical run records carry the old name — no backfill required.

step_outcome::completed_with_warnings is a step property. The workflow instance completed_with_warnings state is distinct: one partially-failing import step causes the entire run to be marked accordingly.

Provenance is stored as FK/column data, enabling relational queries. Human- readable notes can be derived but are not the source of truth.