Sprint Backlog 10

Updates to sprint and product backlog.

We need to scan all of our finance notebooks so we can use them with AI. Each sprint will have a story similar to this until we scan and process them all.

We keep running out of claude code tokens. One way out of it is to generate the basic infrastructure via code generation and then use claude to fix any issues.

The following is the analysis for adding support to party schemes.

List of schemes to seed in SQL:

• party identifier scheme: LEI
• Name: Legal Entity Identifier
• URI: http://www.fpml.org/coding-scheme/external/iso17442
• Description: Legal Entity Identifier (ISO 17442, 20-char alphanumeric). Global standard for legal entities.
• party scheme: BIC
• Name: Business Identifier Code
• URI: http://www.fpml.org/coding-scheme/external/iso9362
• Description: Business Identifier Code (SWIFT/BIC, ISO 9362). Used for banks and financial institutions.
• party scheme: MIC
• Name: Market Identifier Code
• URI: http://www.fpml.org/coding-scheme/external/iso10383
• Description: Market Identifier Code (ISO 10383). Identifies trading venues (e.g., XNYS, XLON). Note: Technically a venue, but often linked to party context in trade reports.
• party scheme: CEDB
• Name: CFTC Entity Directory
• URI: http://www.fpml.org/coding-scheme/external/cedb
• Description: CFTC Entity Directory (US-specific). Used in CFTC swap data reporting for non-LEI entities.
• party scheme: Natural Person
• Name: Natural Person
• URI: http://www.fpml.org/coding-scheme/external/person-id
• Description: Generic identifier for individuals (e.g., employee ID, trader ID). Not standardized; value interpreted contextually.
• party scheme: National ID
• Name: National Identifier
• URI: http://www.fpml.org/coding-scheme/extern/national-id
• Description: National identifiers (e.g., passport number, tax ID, SIREN, ORI, national ID card). Covers MiFID II “client identification” requirements.
• party scheme: Internal
• name: Internal
• URI: http://www.fpml.org/coding-scheme/external/party-id-internal
• Description: Proprietary/internal system identifiers (e.g., client ID in your OMS, CRM, or clearing system).
• party scheme: ACER Code
• name: EU Agency for Energy Regulation
• URI: http://www.fpml.org/coding-scheme/external/acer-code
• Description: ACER (EU Agency for Energy Regulation) code. Required for REMIT reporting by non-LEI energy market participants. Officially supported in FpML energy extensions.
• party scheme: DTCC Participant ID
• Name: Depository Trust & Clearing Corporation Participant ID
• URI:
• Description: DTCC Participant ID: A unique numeric identifier (typically 4–6 digits) assigned by the Depository Trust & Clearing Corporation (DTCC) to member firms authorized to participate in U.S. clearing and settlement systems, including DTC, NSCC, and FICC. Used in post-trade processing, trade reporting, and regulatory submissions in U.S. capital markets.
• party scheme: AII / MPID
• Name: Alternative Trading System (ATS) Identification Indicator / Market Participant ID
• Description: Commonly referred to as MPID – Market Participant Identifier; AII stands for ATS Identification Indicator, a term used in FINRA contexts. A four-character alphanumeric code assigned by FINRA (Financial Industry Regulatory Authority) to broker-dealers and alternative trading systems (ATSs) operating in U.S. equities markets. Used to identify the originating or executing firm in trade reports (e.g., in OATS, TRACE, or consolidated tape reporting). MPIDs are required for all SEC-registered trading venues and participants in U.S. equity and options markets.

We need to implement these before we can implement party:

• entity type
• party role
• local jurisdiction
• business center
• person role
• account types
• entity classifications
• local jurisdictions
• party relationships
• party roles
• person roles
• regulatory corporate sectors
• reporting regimes
• supervisory bodies

This pull request introduces a robust and automated system for managing FPML reference data within the platform. It significantly upgrades the codegen capabilities to parse FPML XML, generate comprehensive SQL database structures, and create scripts for populating both staging and production tables. The changes also streamline the data publication process by embedding key operational metadata directly into dataset definitions, moving towards a more configurable and less hard-coded approach for data management.

Highlights:

• FPML Reference Data Workflow: Implemented a complete Data Quality (DQ) artefact workflow for 15 FPML reference data entity types, encompassing 18 distinct datasets.
• SQL Schema Generation: Added automated SQL schema generation for FPML entities, including bi-temporal tables, notification triggers, and dedicated artefact staging tables.
• Image Linking: Introduced functionality to link business centres to country flags, enhancing visual representation within the data.
• Architecture Refinement: Refactored SQL generation to produce individual files per entity/coding-scheme instead of concatenated scripts, improving modularity and maintainability.
• Dataset Metadata Enhancement: Extended dataset records to directly store target_table and populate_function details, enabling more dynamic and flexible data publication.

When we publish images, the image cache does not reload.

We need some data to drive the new Party tables. We can get GLEIF data for this. The dataset is too large, so we need to obtain a representative subset.

Links:

• LEI Data: GLEIF Golden Copy

The ImageCache currently clears all cached images and re-fetches everything on reload. This is inefficient when only a few images have changed. We should track the last load time and only fetch images that have been modified since then.

The image domain already has a recorded_at timestamp field that tracks when each image was last modified.

Required changes:

1. Protocol (assets_protocol.hpp):
   • Add std::optional<std::chrono::system_clock::time_point> modified_since parameter to list_images_request.
   • Add recorded_at field to image_info struct in the response.
2. Server handler:
   • Update the list_images handler to filter images where recorded_at > modified_since= when the parameter is provided.
   • Return all images when modified_since is not set (current behaviour).
3. Client ImageCache (ImageCache.hpp/cpp):
   • Add last_load_time_ member to track when images were last loaded.
   • On reload(), don't clear caches; instead send modified_since with last load time.
   • Only fetch and update images that were returned (changed since last load).
   • Update last_load_time_ after successful load.

Acceptance criteria:

• When publishing an image dataset, only changed images are fetched.
• UI refresh is minimized (no flashing for unchanged images).
• Full reload still possible (e.g., on first load or explicit cache clear).
• Protocol is backward compatible (server handles missing modified_since).

FPML and LEI data is breaking misspell action. Need to find a way to ignore folders.

This was complicated in the end because we relied on GLM 4.7, but it got very confused and kept suggesting approaches that were not very useful. In the end, fixed it manually.

At present we have external data all over the place, making it hard to understand what's what. We need to consolidate it all under https://github.com/OreStudio/OreStudio/blob/main/external.

This pull request introduces a significant refactoring of how reference data is managed and generated within the project. By reorganizing data into domain-specific directories, implementing manifest files for better source tracking, and developing code generators, the changes aim to streamline the process of maintaining and updating reference data. The relocation of external data and the introduction of master include files further enhance the system's structure and clarity, ultimately improving the robustness and maintainability of the data population process.

Highlights:

• Reference Data Reorganization: The populate directory has been restructured into domain-specific subdirectories, including fpml/, crypto/, flags/, and solvaris/, to improve organization and modularity.
• Manifest Files Introduction: New manifest.json files have been added for external data sources (flags, crypto) and internal solvaris data, providing a centralized way to manage data sources.
• Code Generator Creation: Dedicated code generators have been developed for flags, crypto, and solvaris metadata SQL, automating the generation of SQL files from source data.
• External Data Relocation: External data has been moved to a new 'external/' directory, accompanied by proper methodology documentation to clarify data sourcing and usage.
• Master Include Files: Master include files (e.g., fpml.sql, crypto.sql, flags.sql, solvaris.sql) have been added for each domain, simplifying the inclusion of related SQL scripts.
• Populate Ordering Fixes: The ordering of data population has been adjusted to resolve existing dataset dependency issues, ensuring correct data loading.

We need to add datasets for coding schemes. At present they are going directly into production tables.

This pull request significantly refactors the management of ISO and FpML coding schemes by integrating them into a robust Data Quality (DQ) artefact pipeline. This change standardizes the process for handling external reference data, ensuring greater consistency and control over data publication. It introduces new database structures and functions to support a more governed lifecycle for these critical datasets, allowing for previewing changes and flexible update strategies, while maintaining direct insertion for specific internal party schemes.

Highlights:

• Standardized Coding Scheme Management: ISO and FpML coding schemes are now processed through a dedicated Data Quality (DQ) artefact pipeline, moving away from direct database insertions. This ensures a more consistent and governed approach to managing external reference data.
• Enhanced Data Governance: The new pipeline supports preview, upsert, insert-only, and replace-all modes for publishing coding schemes, offering greater control and consistency over data updates and lifecycle management.
• New Schema Components: A new staging table, dq_coding_schemes_artefact_tbl, has been introduced for temporary storage of coding schemes. Additionally, new functions dq_preview_coding_scheme_population() and dq_populate_coding_schemes() are provided to manage the publication process to production.
• Automated Generation and Dependencies: Code generators for ISO and FpML have been updated to produce artefact inserts for the new pipeline. Dependencies for ISO countries and currencies now correctly link to the new iso.coding_schemes dataset, ensuring proper ordering during data loading.
• Exclusion of Party Schemes: Certain party-related coding schemes (e.g., LEI, BIC, MIC) will continue to be handled via direct inserts, acknowledging their distinct, manually-defined nature and keeping them separate from the new pipeline.

At present we have all files in one directory. As we add more tables it's becoming quite hard to follow.

Notes:

• user ores must be a variable.

The diagrams are getting too out of line with the code, see if we can do a quick hack to code-generate them from source.

This pull request introduces a significant enhancement to the ores.codegen project by implementing an automated system for generating PlantUML Entity-Relationship diagrams directly from the SQL schema. This system not only creates visual representations of the database structure but also incorporates a comprehensive validation engine to enforce schema conventions, ensuring consistency and adherence to best practices. The new tooling streamlines documentation, reduces manual effort, and provides a single source of truth for the database design, making it easier for developers and stakeholders to understand and maintain the data model.

Highlights:

• Automated ER Diagram Generation: Introduced a new codegen system to automatically generate PlantUML Entity-Relationship (ER) diagrams directly from SQL CREATE/DROP statements.
• Comprehensive SQL Parsing: Developed a Python-based SQL parser (plantuml_er_parse_sql.py) capable of extracting detailed schema information including tables, columns, primary keys, unique indexes, and foreign key relationships.
• Intelligent Table Classification: The parser automatically classifies tables into categories like 'temporal', 'junction', and 'artefact' based on their column structure and naming conventions, applying appropriate stereotypes in the diagram.
• Convention Validation: Integrated a robust validation system that checks SQL schema against predefined naming, temporal table, column, drop script completeness, and file organization conventions, issuing warnings or failing builds in strict mode.
• Dynamic Relationship Inference: Relationships between tables are inferred based on foreign key column naming conventions and explicit references, supporting one-to-many and one-to-one cardinalities.
• Mustache Templating for Output: Utilizes a Mustache template (plantuml_er.mustache) to render the parsed JSON model into a human-readable and machine-processable PlantUML diagram format, including descriptions from SQL comments.
• Orchestration and Integration: A shell script (plantuml_er_generate.sh) orchestrates the parsing, model generation, and PlantUML rendering, with an optional step to generate PNG images. A separate validate_schema.sh script provides a dedicated validation entry point.
• Enhanced Documentation: A new doc/plans/er_diagram_codegen.md document outlines the objective, design principles, architecture, components, and implementation steps of the ER diagram generation system.
• SQL Schema Descriptions: Added descriptive comments to 22 existing SQL table definitions (projects/ores.sql/create/**/*.sql) to enrich the generated ER diagram with meaningful context.

To make the toolbar more readable, it would be nice if we could add some words to icons such as the icons for export and import:

• CSV
• ORE
• FPML

Then we could use 2 well-defined icons for import and export across the code-base.

At present this is hard-coded.

As per FPML analysis, it is better to have all of the export/import code in one place rather than scattered across other components. Add a component specific to ORE.

This pull request provides a crucial fix for Windows MSVC builds by addressing a specific compilation error. It ensures the project's compatibility and successful compilation on the Windows platform, particularly when dealing with code that generates large object files due to complex template structures.

Highlights:

• MSVC Build Fix: Resolves a C1128 build failure on Windows when compiling domain_xsd.hpp with MSVC.
• Compiler Option Addition: Introduces the /bigobj compiler option to the ores.ore target specifically for MSVC builds.
• Large Object File Support: This option increases the section limit, accommodating large object files often generated due to extensive template usage.

At present the population is a bit messy. We need to structure it around layers.

Values:

• Up, Down, Closest, Floor, Ceiling. Maybe none?

Notes:

• Currency should check against these.
• Supply the mapping table to mappers?

Grok:

Your current list covers several of the most commonly used rounding behaviors in applications:

• Up → typically means always round away from zero (positive direction) — also called Round Up / Towards +∞ / Ceiling-like but always positive direction
• Down → always round toward zero (truncation) — also called Round Down / Truncation / Towards 0
• Closest → round to nearest (with some tie-breaking rule — usually unspecified/default in casual UIs)
• Floor → round down toward −∞ (always toward more negative)
• Ceiling → round up toward +∞ (always toward more positive)

These are already very solid for most general-purpose apps. However, depending on your audience (especially finance, accounting, statistics, or scientific users), you are likely missing one or two very frequently requested/expected options.

### Commonly missing but important ones to consider adding

1. Bankers (or Banker's Rounding / Round Half Even / Round Half to Even) → Round to nearest, but ties (exactly .5) round to the nearest even digit. This is the statistically unbiased method, required or strongly preferred in:

   • Financial applications (interest calculations, totals)
   • Many accounting systems
   • IEEE 754 default for binary floating-point
   • Java BigDecimal.HALF_EVEN, .NET MidpointRounding.ToEven, Python round(), SQL ROUND() in many databases

   Very commonly expected when users see "Closest" is not enough.

2. Half Up (or Round Half Away from Zero / Commercial Rounding) → Round to nearest, ties (.5) always round away from zero (2.5 → 3, -2.5 → -3) This is what most people intuitively think "normal rounding" means in school and is the most common tie-breaking rule in non-financial UIs.

   Many users expect this when they choose "Closest".

### Less common but occasionally requested

• Half Down — ties round toward zero (2.5 → 2, -2.5 → -2) — rare in business apps
• Half Toward Zero — same as Half Down
• Half Away from Zero — same as Half Up
• Half to Odd — ties round to odd digit (very rare)

### Suggested final list (modern, user-friendly set)

Most applications end up offering roughly these 5–7 options in a dropdown:

• Up (always away from zero / toward +∞ for positive numbers)
• Down (always toward zero / truncation)
• Floor (toward −∞)
• Ceiling (toward +∞)
• Closest / Nearest (usually implies Half Up in simple UIs)
• Half Up (explicitly — very popular)
• Bankers / Round Half Even / Banker's Rounding (critically important for finance/accounting)

A very clean and common 6-item list seen in many tools:

1. Up
2. Down
3. Floor
4. Ceiling
5. Nearest, Half Up
6. Nearest, Half Even (Bankers)

If you want to keep the list very short, adding just Bankers to your current five is already a big improvement for serious users.

FPML via Gemini:

FpML does not use a "Coding Scheme" (like a URL-referenced list) for rounding in the same way it does for Currencies or Business Centers. Instead, it uses a Complex Type called Rounding and a fixed Enumeration for the direction.

Because rounding logic is typically a structural part of a calculation (e.g., how to round a floating rate or a payout amount), it is "baked into" the schema itself rather than being an external lookup table.

1. The Rounding Complex Type

To model rounding in your database or XML, you need two pieces of data:

• precision: An integer representing the number of decimal places.
• roundingDirection: A fixed value from the RoundingDirectionEnum.

Allowed Values for roundingDirection:

• Up: Round away from zero (or to the next higher number).
• Down: Round toward zero (truncate).
• Nearest: Standard mathematical rounding (0.5 rounds up).

Now that we got the basic codegen infrastructure working, we need to start code generating the SQL for domain types.

Refactor the database architecture to separate catalog/metadata tables from production data tables. This resolves a bootstrapping paradox where catalog metadata must exist before provisioning, but provisioning populates the database.

This pull request implements a significant architectural change by dividing the monolithic database schema into logical metadata and production schemas. This separation is designed to streamline data management, improve data governance, and provide a clearer distinction between data used for defining and staging information versus data actively used by applications. The change impacts nearly all SQL files, requiring updates to schema references, function calls, and data population logic to align with the new structure.

Highlights:

• Schema Restructuring: The single ores PostgreSQL schema has been split into two distinct schemas: metadata and production, along with a public schema for shared utilities. This enhances separation of concerns and clarifies data ownership.
• Schema Content Definition: The metadata schema now houses data governance, classification, and staging tables (e.g., dq_*, change_control). The production schema contains operational data consumed by applications (e.g., refdata_*, iam_*, assets_*, geo_*, variability_*, telemetry_*).
• Cross-Schema References and Validation: All SQL scripts, including table creations, triggers, functions, and population logic, have been updated to use schema-qualified names. Foreign key validations and data population functions now correctly reference tables across the new metadata and production schemas.
• ER Diagram Generation Update: The ER diagram generator has been updated to correctly parse and represent the new multi-schema architecture, ensuring visual documentation remains accurate.
• Documentation Updates: Extensive documentation has been added and updated to explain the new schema mental model, key principles of data flow (unidirectional from metadata to production), and clear boundaries of responsibility for each schema.

It seems a bit painful to have to manually craft entities for parsing ORE and FPML code. Explore tooling in this area.

This pull request significantly upgrades the system's data handling capabilities by integrating xsdcpp for automated C++ type generation from XML schemas. This change not only modernizes the way ORE XML types are managed but also enhances the data quality publication process by introducing a dedicated artefact_type mechanism for managing target tables and population functions. Additionally, it standardizes SQL function naming and corrects currency rounding logic, leading to a more robust and maintainable codebase.

Highlights:

• XSDCPP Code Generation Integration: Introduced infrastructure to automatically generate C++ types from XSD schemas using the xsdcpp tool, streamlining the creation and maintenance of domain types for ORE XML configurations.
• Refactored ORE XML Types: Replaced manually crafted CurrencyConfig and CurrencyElement C++ types with automatically generated currencyConfig and currencyDefinition types, improving consistency and reducing manual effort.
• Enhanced Data Quality (DQ) Publication Infrastructure: Added a new artefact_type domain, entity, mapper, and repository to centralize the lookup of target_table and populate_function for dataset publication, removing these fields directly from the dataset entity.
• SQL Naming Convention Enforcement: Renamed all dq_populate_* SQL functions to follow a consistent dq_populate_*_fn suffix convention across the database schema.
• Currency Rounding Type Correction: Fixed invalid currency rounding types in the CURRENCY_DEFAULTS_POOL within generator.py and in SQL populate scripts, changing 'standard' and 'swedish' to 'Closest', and 'none' to 'Down' for commodity currencies.

Links:

• codesynthesis xsd: good but requires their own library at runtime and it's not on vcpg.
• codesynthesis xsde: good but requires their own library at runtime and it's not on vcpg.
• #11: Issues parsing XSD for ORE (Open Source Risk Engine): raised issue against xsdcpp project for ORE schema.

Group together datasets so that you can just install a bundle and have a system ready to use.

This pull request significantly enhances the data management capabilities by introducing a robust 'Dataset Bundle' feature. This allows for the logical grouping and coordinated publication of multiple datasets, ensuring data consistency and simplifying the deployment of related reference data. The changes encompass new database schema elements, comprehensive PL/pgSQL functions for lifecycle management, and initial seed data to demonstrate its utility, ultimately streamlining the process of setting up and maintaining coherent data environments.

Highlights:

• New Feature: Dataset Bundles: Introduced the concept of 'Dataset Bundles', which are named collections of datasets designed to work together, allowing the system to be brought into a ready state with a coherent set of reference data.
• New Database Objects: Added three new tables: dq_dataset_bundles_tbl (main bundle definition), dq_dataset_bundle_members_tbl (junction table linking bundles to datasets), and dq_bundle_publications_tbl (audit table for bundle publication history).
• Bundle Management Functions: Implemented a suite of PostgreSQL functions for managing dataset bundles, including dq_list_bundles_fn() (list available bundles), dq_list_bundle_datasets_fn() (list datasets within a bundle), dq_preview_bundle_publication_fn() (simulate publication), dq_populate_bundle_fn() (publish all datasets in a bundle), and dq_get_bundle_publication_history_fn() (retrieve publication audit records).
• Automated Bundle Publication: The dq_populate_bundle_fn() function orchestrates the publication of all member datasets within a bundle to production, processing them in a defined display order to respect dependencies and logging individual dataset publication results.
• Seed Data and Upsert Functions: Added upsert_dq_dataset_bundle() and upsert_dq_dataset_bundle_member() functions to facilitate idempotent seeding of bundle definitions and their members, along with initial seed data for 'solvaris', 'base', and 'crypto' bundles.

At present we need to go to the shell to bootstrap. It would be easier if we just had a button in Qt for this. Actually we probably need a little wizard which takes you through choosing an initial population etc.

This pull request introduces a crucial user experience improvement by providing a guided, interactive wizard for first-time system initialization. It resolves the 'chicken-and-egg' problem where a new system couldn't be easily set up without an existing administrator account or initial data. The wizard streamlines the process of creating the first admin and populating the system with essential reference data, making the application much more user-friendly upon initial deployment.

Highlights:

• New System Provisioner Wizard: Introduces a multi-page wizard UI (SystemProvisionerWizard) to guide users through the initial setup of a fresh system, including creating an administrator account and selecting an initial data bundle.
• Bootstrap Mode Detection and Handling: The system now detects if it's in 'bootstrap mode' (no administrator account exists). If detected, the LoginDialog emits a signal, and the MainWindow automatically displays the new SystemProvisionerWizard instead of the standard login form.
• Admin Account Creation and Data Provisioning: The wizard facilitates the creation of the initial administrator account with validation for username, email, and password. It also allows users to select a dataset bundle (e.g., Solvaris, Base System, Crypto) for initial data provisioning, with progress tracking and logging.
• Product Backlog Documentation: Two new stories have been added to the product backlog: one detailing the 'System Provisioner Wizard' feature and its acceptance criteria, and another addressing the architectural issue of 'Separating catalog metadata schema from production data' which is a prerequisite for full provisioning functionality.

We ran out of tokens to address code review comments from:

• https://github.com/OreStudio/OreStudio/pull/348

Notes:

• cannot view domains, edit etc. Done.
• consider adding nodes to the tree with like dimensions so that users effectively can filter by those. Done.
• check to see if the diagram is using the new message to retrieve dependencies. Done.
• most countries don't seem to have flags. How and when is the mapping done? actually after a restart I can now see the flags. It seems we need to force a load. Done.
• headers in data set are the same colour as rows. Make them different.
• crypto currencies sometimes overlap with countries and iso currencies. we should "namespace" them somehow to avoid having a country flag being overwritten by a crypto icon (happens with AE).
• when publish fails we can't see the errors. Raised story.

Where we can, we should start to add export and import functionality for FPML. This validates our modeling and catches aspects we may be missing.

Currency export:

<?xml version="1.0" encoding="utf-8"?>
<referenceDataNotification
    xmlns="http://www.fpml.org/FpML-5/reporting"
    fpmlVersion="5-11">
  <header>
    <messageId messageIdScheme="http://sys-a.gigabank.com/msg-id">SYNC-CURR-2026-001</messageId>
    <sentBy>GIGABANK_LEI_A</sentBy>
    <creationTimestamp>2026-01-25T10:00:00Z</creationTimestamp>
  </header>

  <currency>
    <currency currencyScheme="http://www.fpml.org/coding-scheme/external/iso4217">USD</currency>
  </currency>

  <currency>
    <currency currencyScheme="http://www.fpml.org/coding-scheme/external/iso4217">EUR</currency>
  </currency>
</referenceDataNotification>

and:

<header>
  <messageId messageIdScheme="http://instance-a.internal/scheme">MSG_99821</messageId>
  <sentBy>INSTANCE_A</sentBy>
  <creationTimestamp>2026-01-25T14:30:00Z</creationTimestamp>
</header>

Copy the BOINC data model.