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

Command icon 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

mably created an issue. See original summary.

mably’s picture

Status: Active » Postponed (maintainer needs more info)
mably’s picture

The (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 at start(), holding the identity the process is about. Most engines model this explicitly (Camunda business key, Zeebe correlation key). Advantages for integrators:

  • It is a real column on the instance, so a downstream entity can hold a native foreign key to the instance and follow it as a plain Views relationship: no value-scan, no JSON-string matching, no TEXT prefix index.
  • It keeps "the identity this process is about" separate from process data (variables), which is what variables are for.

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.

mably’s picture

Title: Index orchestra_variable for correlation-variable lookups (name/value) » Add a first-class correlation key to the process instance
Issue summary: View changes
mably’s picture

Status: Postponed (maintainer needs more info) » Needs review

  • mably committed 53cbfc6d on 1.x
    task: #3605491 Index orchestra_variable for correlation-variable lookups...
mably’s picture

Status: Needs review » Fixed

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.