Investigation: the trade shape

Read on main at 1786aa23cd (2026-10-02).

• trade maps one-to-one to ores_trading_trades_tbl, 19 columns plus the temporal and audit columns. It holds no instrument, envelope, identifier or party role; those are separate tables keyed by the trade. At the top level the model already follows "there is no entity".
• The five sub-structs are not tables, are never loaded separately, and are used by no other model. The trade model states their reason: to keep each reflected struct under MSVC's C1202 limit in reflect-cpp's reflection (a reason section 16's correction shows does not hold for plain structs). They also fix the wire as nested JSON. trade_audit is a field-for-field copy of ores.dq.audit_record, which every instrument uses. The split is driven by a serialiser limit, not by any access pattern.
• The real aggregates are the export carriers: trade_instrument (a variant over eight families), the *_data carriers, trade_export_item and instrument_payload, built for the ORE import and export journey.

1. The family routing lives in three places: trade_types.product_type, a string switch in the hand-written export handler that maps trade-type codes to 30 instrument tables, and a check constraint on each instrument table's trade_type_code. Nothing checks that they agree.
2. trades.product_type is a nullable copy of trade_types.product_type, kept in step by nothing, and the dispatch reads the copy.
3. All 30 instrument tables copy trade_type_code with no check against the trade, and their trigger takes party_id from the session (app.current_party_id) while the trade derives its own from the book. The two can disagree, and row-level security filters on party_id.
4. Nothing stops one trade having rows in two instrument tables.
5. The counterparty is stated three times: trades.counterparty_id, trade_party_role and trade_envelope.counter_party (document text). The netting set twice.
6. Identifiers have two homes: external_id is the trade's key, and trade_identifier holds UTI, USI and the rest.
7. trade_envelope, its two children and trade_identifier never check that their trade exists; trade_party_role does.
8. Smaller: trade_party_role writes to ores_trading_party_roles_tbl; trade_types keeps two columns that only drove Qt forms; the instrument rebuild is a third hand-written operation besides the two exports P04 names.

Decided: the trade becomes an immutable anchor row plus component tables keyed by the trade, one per access pattern, each versioning on its own timeline.

• The anchor holds only facts fixed for the trade's life. Because it never changes, other tables reference it with real foreign keys instead of trigger-checked soft keys against temporal rows.
• A copy is allowed only of an immutable fact, and is pinned by a composite foreign key, so it cannot drift. Instruments may carry trade_type and party_id (row-level security needs the party on every row) only through a key to the anchor. Each instrument table checks that trade_type is one of its own types, and the lists are disjoint, so a trade's instrument can sit in exactly one table. That closes findings 1 to 4.
• The five field groups go: each table is narrow (see section 16's correction on what the C1202 limit counts). trade_audit becomes ores.dq.audit_record; instrument_identity loses its unpinned copies and becomes the one shared key group.

Set aside: keeping trade as one wide temporal row, which versions booking, lifecycle and classification together on every change.

• external_id leaves the anchor. Every identifier scheme — UTI, USI, a legacy system's id, ORE's Trade/@id — is a row of trade_identifier (scheme, value, issuer), with a real foreign key to the anchor. That closes finding 6.
• The firm's own id is the anchor's UUID, called the trade id throughout; "deal id" is not used.
• Set aside: a human-readable deal number on the anchor, and the firm's number as one more scheme.

• The firm's legal entity (party) and the counterparty are fixed for a trade's life and sit on the anchor. A change of either is a cancel and rebook: the old trade is cancelled, a new trade is booked, and a typed link joins them (novation for the counterparty).
• successor_trade_id goes; the link carries the relation. The knowledge notes described the field two contradictory ways, and that is now resolved in Trade Versioning.

The trade-links documentation merged on main during the design. Its catalogue filed structures, merges, compressions, allocations and process runs as links. The owner's principle: where a collection of trades is named and an operation attaches to it, it is an entity with an identity, not a by-product of links.

• The vocabulary of the analysis notes was adopted: a set is trades sharing an attribute (book, counterparty, netting set), with the identity on the attribute's entity; a group is trades tied to an operation, with an identity of its own (a structure, a compression); a link joins particular trades for a reason and holds nothing.
• With the groups taken out, a link joins two trades, so the pairwise trade_link (from trade, to trade, type) is enough. Each link type names its two roles and states whether it has an economic effect.
• Exercise joined the catalogue: exercised option to resulting trade.
• Restructure is an amendment of the same trade; a roll that books a replacement is a link.
• Set aside: one link record with several ends, and links plus a separate run table.

Recorded in Trade sets and trade groups, Netting sets, Trade compression, Trade merge and Trade allocation, with the link catalogue, Trade Links, Trade Versioning and Trade Activity brought into line.

Researched before deciding. No rule forbids moving a trade between two books that share a legal entity, a branch, a regulatory book and an accounting classification; the restrictions apply when a move crosses one of them (RBC25.14-16 for the trading and banking book, IFRS 9 4.4.1 for classification). The accounting constraint people remember governs the ledger postings, not the trade.

Decided: a move within those four lines amends the trade's booking, cuts an internal version with book move as the reason, and carries its fair value and whether historic cash flows move. Any other change of book is a close-out and rebooking, linked, or internal risk transfer trades. The temporal booking keeps the book history, which removes the P&L break the analysis notes describe.

Recorded in Book moves.

Decided: the dates are economic terms and live on the instrument; each family states them in its own words. The blotter maps them to common names (start, end, expiry, settlement) through one generated view.

Set aside: dates copied onto the booking, and a separate dates table.

Authorisation is stored as decisions, not as a status column.

• authorisation_decision: the unit, the internal version decided on, the decision (authorise, deny, hold over, return to unseen, un-authorise), hold reason, a required comment, amendment reason, department, operational-error flag, escalation, who or which rule decided, and when. auto_authorisation_rule holds Operations' rules.
• The status is derived: a unit is authorised when its latest decision is authorise and covers the current version, so an amendment withdraws the release without anything resetting it.
• Denied is final, enforced in the schema. Decisions are append-only metadata and cut no version.
• Decided: the unit Operations authorises is the unit the customer confirms — a structure that confirms as a whole, or each trade.

Decided: trade_activity records each business event — type, actor, time, comment, operational-error flag — and every component version carries a foreign key to the activity that caused it. The activity type is reference data stating the economic, confirmable and real axes and the priority order. The external version moves only for a confirmable activity; a null amend is recorded and versions nothing.

Set aside: a reason code per row grouped by transaction time, and an activity table for confirmable events only.

• The anchor gains counterparty_id, immutable like party_id.
• trade_booking keeps netting_set_id, versioned, because re-papering can move a trade to another set.
• A composite key from the booking to netting_set (set, counterparty, party), pinned to the anchor, stops a trade being filed in another counterparty's or another entity's set.
• trade_party_role keeps only the other roles: broker, sales person, agent, calculation agent.

Decided: import resolves the envelope's counterparty name and portfolio ids to entities and rejects a document that does not resolve; export regenerates them. AdditionalFields becomes trade_additional_field (name, value, order). That closes finding 5.

Set aside: a provenance copy of the document's text, and keeping today's envelope tables.

• structure is an immutable anchor: tenant, party, counterparty, kind, template.
• structure_kind states whether the kind confirms as a whole (strategy, typed, dynamic) or trade by trade (package).
• structure_template states the roles and leg counts a strategy or typed product allows, checked when a leg is linked.
• structure_member, keyed by trade_id, is temporal: a trade sits in at most one structure at a time and can be linked and unlinked.
• A structure's legs share its party and counterparty.
• Decided: one level of nesting, two levels in total, through a nullable parent_structure_id and a check that a structure with a parent has no children.

Decided: an act is an activity. trade_activity_participant (activity, trade, role) replaces the activity's single unit, and each kind of act adds a detail table keyed by the activity: compression (provider, cycle reference), allocation (block, source, template), merge (policy, expiry-netting style), runs (process, run date), bulk book move (books, cash flows moved). Links keep the activity that created them.

Set aside: groups as separate entities pointing at an activity, and a table pair per kind.

The design first kept a copy of a trade in another workspace under the same trade id, with the workspace in every key. It paused there, because the layered workspace design overrides a parent's rows copy-on-write and was never designed for a trade split into components.

The owner then reframed the question from the use cases rather than the data model. Users need two things without disturbing official data: to import trades and market data, and to experiment with trades and market data and run reports against the changes. Prior art — Calypso's pricing environment, Murex's what-if simulation and hypothetical trades, ORE's run-time stress scenarios, and the analysis notes on personal portfolios, test books and perturbed market data — isolates by explicit inputs and by classification, not by a copy of everything. A layered workspace is an application-level database branch, and it pushes a workspace into every key, query and foreign key.

Decided: workspaces are removed from the trade model. The Sandbox replaces them:

• A sandbox is an entity anchored to a node of the official portfolio tree (a business, a desk), opened only by users holding that right at the node, private or shared through the anchor's permissions so desks keep their Chinese walls. A single tenant-wide sandbox portfolio was considered and rejected for exactly that reason.
• Its books are virtual books: created by the trading system, never mapped to the ledger, holding no position of the firm.
• A trade in a virtual book is a draft recorded before any agreement, a test or a hypothetical, and is never live. A draft becomes real by Execute into a real book; a hypothetical by a new actual trade linked to it. Both go through the ordinary lifecycle and authorisation.
• Real concluded deals are never parked in the sandbox: they are booked into real books at once and held as unauthorised until Operations releases them.
• Imported and perturbed market data sit in the universe under their own source; the official configuration does not resolve to them. A report definition selects its inputs explicitly.
• The regulatory boundary was checked: Basel RBC25.1-2 (every instrument held is in the trading or banking book), MAR12, EMIR Article 9 (reporting of concluded contracts), MiFIR Article 25 and MiFID II Article 16(6) (record keeping). Each is met by the rule that a sandbox trade is never live.

The anchor's key returns to (tenant, id), and the real foreign keys need no workspace.

The instrument refactor began with the bonds, and the clean-trading story's decisions record its outcome (D10, D18, D19, D21, D28, D29). It is not one table per trade type, nor one wide table per family, but a factoring of the shape:

Decided: every instrument family is reworked to this rule, as the bonds were. How far a family can be cleaned is a function of how clean its data model becomes under the data-oriented review; the rule is the target, and the review decides each family's tables. The rates, FX, equity, credit and commodity families are still to do: ten of their tables share one list of all 121 codes, five equity tables hold two products each (defect 9), and the risk participation table has no constraint.

The routing from trade type to instrument table becomes data: each trade type names the table that holds it, and the generator produces the dispatch the export handler hand-codes today.

Set aside: one table per distinct full shape, which repeats the header's columns in every product table, and one table per ORE trade type.

Decided: trading owns what describes trades; reference data owns what exists independently of any trade.

Every foreign key between the two runs from trading into reference data: a trade names a book, a counterparty, a netting set; reference data never names a trade. A netting set exists before its first trade and outlives its last.

Set aside: a credit component created now for agreements, netting sets and CSAs, before anything in it computes; and placing them in trading.

Superseded on 2026-10-03: the nine-field limit below does not hold. 220 of the 287 reflected structs the models generate have more than nine members and compile on MSVC. The failure the CI log shows comes from a tagged variant write over a large carrier; see the decomposition section of Data-Oriented Design and MSVC fails C1202 on the bond export carrier. The counts below still show that each table is narrow.

Every reflected struct keeps nine effective fields or fewer, a nested group counting as one.

• Real foreign keys to the immutable anchors, from every component, link end, participant, member, identifier, decision and agreement.
• Composite keys pin every copied fact: an instrument's trade type and party, a booking's netting set to the trade's counterparty and party, a structure's legs to its party and counterparty.
• Disjoint trade-type checks put each trade's instrument in exactly one table.
• A book move within the four-attribute boundary is the only change of book; anything else is rejected as a move.
• Denied authorisation is final; the authorised status is derived.
• Structures nest one level at most.
• A trade's booking names a virtual book only while the trade is a pre-agreement draft, a test or a hypothetical; going live requires a real book in the same write.

Trade sets, groups and links; Netting sets; Trade compression; Trade merge; Trade allocation; Book moves; Sandbox; and consistency passes over the link catalogue, Trade Links, Trade Versioning, Trade Activity, Trade Structures, the Trade hub, the book cluster and the funding and sweep notes. On branch feature/document-trade-groups.

The sandbox replaces workspaces in the trade model; the five questions the layered design raised no longer apply. Removing workspaces from the other entities that carry them, and from report definitions, is a separate piece of work this design depends on only in that trades no longer carry one.

The implementation is scaffolded as stories, listed on the trade shape story: the code generator support first, then netting agreements and sandboxes in reference data, then the split of the trade, activities and links, authorisation and the agreement, and structures. Each instrument family is a story of its own in the backlog, reworked to the rule of section 14. The knowledge notes still say "deal id" in places, and a task on the story renames it to trade id.