Issue #3605491: Add a first-class correlation key (resolves correlation lookups)

Resolves correlation lookups with a first-class correlation key on the process instance, the design proposed in the issue discussion, rather than indexing and scanning JSON-encoded variable values.

Why this over a (name, value) index

The original idea was a composite index on orchestra_variable(name, value). But value is a TEXT column, so that index puts a 191-char prefix on every variable row (wide and heavy), and matching a business key against it still risks the MySQL/PostgreSQL coercion the issue set out to avoid. A dedicated scalar column on the instance is smaller, portable by construction, and matches how Camunda (businessKey) and Zeebe (correlation key) model this.

What this does

  • Adds an indexed correlation_key string column to orchestra_instance, with a (tenant, correlation_key) index, set via a new optional start() argument. Pre-1.0, so it rides a fresh install (no update hook).
  • Adds WorkflowEngine::findInstancesByCorrelationKey($key), scoped to the acting tenant: an index-served equality on a real scalar column, portable across databases, never coercing a TEXT column against an integer. The key is not required to be unique.
  • No index on the variable name/value column (the correlation path no longer needs it), so the heavy TEXT-prefix index is avoided entirely.

Resolve via the key, join via the id

The correlation key is a string (so arbitrary keys, UUIDs and refs all fit). Resolve an instance from an external event with it, then for a persistent link or a Views relationship store the instance id (the integer primary key) on your entity and join on that: the key resolves, the id is the foreign key. Joining an integer column directly against the string key is not portable (PostgreSQL rejects integer = varchar), so the docs steer to the instance id. This is covered in the Concepts guide.

A kernel test covers a match, a non-match, a key shared by several instances, and tenant scoping. phpcs (Drupal, DrupalPractice) and cspell are clean.

Edited by Frank Mably

Merge request reports

Loading