RESEARCH / ENHANCEMENT

Companion to 0005622. Where 0005622 covers the transitions (the EDGES of a state machine), this covers the STATES themselves (the NODES): generate a canonical state/status reference table that enumerates the valid states an entity may hold.

Rationale:

• The states are the foundation the transition enforcement builds on. The entity's status column should reference this table, and the allowed_transition table's from_state/to_state should reference it too, so no transition can name a non-existent state.
• The states correspond to the model's role/subtype set (e.g. Candidate, Tenderer, Winner). They can be generated from the population of the state OFType / subtype labels, the same fact-oriented way other lookup tables are produced.

Generation target:

• CREATE TABLE org_state (state PRIMARY KEY [, label, sort_order, is_initial, is_final ...]).
• Populate from the model's enumerated states.
• Foreign keys: entity.status -> org_state(state); allowed_transition.from_state/to_state -> org_state(state).
• Optionally flag initial/terminal states so the generator can also emit "may only start in an initial state" and "may not transition out of a final state" checks.

Together with 0005622 this gives a complete generated state machine in SQL: states table (nodes) + transition table (edges) + transition-enforcement trigger.

Related: 0005622