orchestra_variable was indexed only on instance, so resolving a process by a variable value scanned the table; and matching a TEXT/JSON value against an integer key is not portable across MySQL and PostgreSQL.
Resolution: rather than a heavy (name, value) prefix index, add a first-class correlation key (business key) on the process instance: an indexed scalar column set at start, with a tenant-scoped lookup. A string column matched as an index-served equality, portable by construction, modelled on Camunda's businessKey and Zeebe's correlation key. Resolve a process by the key; for a persistent relationship, join on the instance id (the integer primary key).
Issue fork orchestra-3605491
Show commands
Start within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
Comments
Comment #3
mably commentedComment #4
mably commentedThe (name, value) index plus
findInstancesByVariable()solves the immediate problem and gives a portable lookup. One alternative worth weighing for the longer term, likely cleaner than prefix-indexing a TEXT value column: a first-class correlation key (business key) on the ProcessInstance itself.It would be an indexed scalar column on
orchestra_instance, set atstart(), holding the identity the process is about. Most engines model this explicitly (Camunda business key, Zeebe correlation key). Advantages for integrators:The variable lookup stays useful for ad-hoc correlation on arbitrary variables, so the two are complementary; the correlation key is the dedicated joinable identity. It could land as a follow-up rather than block this issue.
For context, on the integrator side (yoyaku) we are taking the equivalent step now: storing the instance id as an indexed reference on the transaction entity, so the reverse lookup and the dashboard relationship are a native FK rather than a variable match.
Comment #5
mably commentedComment #6
mably commentedComment #8
mably commented