Trade Status FSM Implementation

The trade lifecycle is a domain concept: its states, transitions and activity types are described in trade lifecycle. This page records how that design is realised in the database, and nothing about why the design is what it is.

The lifecycle runs on the generic state machine infrastructure in ores.dq, which supplies ores_dq_fsm_machines_tbl, ores_dq_fsm_states_tbl and ores_dq_fsm_transitions_tbl.

The trade status machine is seeded in ores_dq_fsm_machines_tbl under the name trade_status. Its states and transitions are the ones trade lifecycle sets out; that note is the authority and this page does not restate them. dq_fsm_trade_status_populate.sql seeds them and cites the same note.

Two properties of the note are easy to lose in seed data, so the script states them where it sets them: draft and live are both initial, because there are two ways in; and no state is terminal, because expired and cancelled are both reversible.

Confirmation is not part of this machine. A trade's confirmation status is its own lifecycle, described in confirmations, and the activity that confirms a trade names no transition here.

The ores_trading_trades_tbl carries a status_id column (soft foreign key to ores_dq_fsm_states_tbl) representing the trade's current status.

The full set of activity types is stored in ores_trading_activity_types_tbl (replaces the narrower ores_trading_lifecycle_events_tbl). Each activity type record includes:

• code: unique identifier (e.g. novation, rate_reset).
• category: one of new_activity, lifecycle_event, misbooking, valuation_change, cancellation.
• requires_confirmation: boolean.
• fsm_transition_id: optional reference to the triggered FSM transition (null for no-status-change activities).
• description: human-readable description.

The insert trigger on ores_trading_trades_tbl resolves and guards the transition. The activity names the event; ores_trading_activity_types_tbl maps it to a transition, or to none; and the trigger requires the trade's current status to match the transition's from_state_id before setting status_id to its to_state_id. An illegal transition raises 23514.

The trigger is the enforcement point rather than a service for two reasons. Every path that writes a version passes through it, so a bulk import or a correction cannot bypass the guard. And the version select immediately above locks the superseding row, so reading the prior status under that lock is what stops two concurrent amendments from both finding their transition legal.

An activity that names no transition carries the prior status forward. That is how the note's Amend self-loops are realised, and why a fixing leaves a live trade live.

• Trade Lifecycle — the states, transitions and activity types this realises.
• ores.dq — the generic FSM infrastructure.