A parent/leaf flow model becomes much easier to query when the parent row's primary key is…

A parent/leaf flow model becomes much easier to query when the parent row's primary key is treated as the stable aggregate/load identity, every leaf stores that key as its group foreign key, and every event/submission redundantly stores both the exact leaf ID and aggregate ID. Apply this to distinguish per-actor workflow state from load-wide evidence. Limits: the external business load number may be payload data rather than a relational key, and the wrapper's own group-reference column may remain null; status and “latest submission” semantics still require product-specific rules.

Evidence/example (2026-09-15): - In Shield's current schema, flows.id is the identity of every wrapper or leaf; wrapper types are direct_driver / dispatcher_to_drivers, leaf types are driver / dispatcher, and leaf flows.flow_group_id self-references the wrapper. Wrapper rows are non-leaf and conventionally have flow_group_id = NULL. Source: https://github.com/e3-solutions/theft/blob/175f0bde25789f1e556803915e0c0163bd0235ff/web/src/db/schema.ts#L1248-L1347 - flow_submissions.id identifies one typed evidence/event row; flow_id points to the exact wrapper/leaf that owns it, while flow_group_id enables aggregate-wide reads. Source: https://github.com/e3-solutions/theft/blob/175f0bde25789f1e556803915e0c0163bd0235ff/web/src/db/schema.ts#L1349-L1431 - Creation writes load metadata as a load_context submission with both IDs equal to the wrapper ID, then creates driver leaves whose flow_group_id is the wrapper and whose own submissions use the leaf ID plus wrapper ID. Source: https://github.com/e3-solutions/theft/blob/175f0bde25789f1e556803915e0c0163bd0235ff/web/src/lib/api/v1/flow-helpers.ts#L234-L307 - Inference: load-wide query = submissions by flow_group_id; exact actor/request query = submissions by flow_id. The external/TMS load_id is inside load_context.data, so it should not be confused with either UUID. Uncertainty: duplicate/retry policy determines whether one external load number maps to one or multiple historical groups.