Description of problem:

When deploying a new version of an Orchestrator workflow in Red Hat Developer Hub, the workflow appears multiple times in the workflows list - once for each version ever deployed. All duplicate entries redirect to the same workflow.

The root cause seems to be that sonataflow-platform-data-index-service.definitions table uses a composite primary key of (id, version). This means each workflow version creates a separate database row, and the UI displays all of them as distinct workflows.

Example: Same workflow appears 3 times with different versions

SELECT id, version FROM "sonataflow-platform-data-index-service".definitions
WHERE id ILIKE '%paas%';

id | version
-----------------------------------+---------
paasrepositorymanagementonboarding | 1.0.1
paasrepositorymanagementonboarding | 1.0
paasrepositorymanagementonboarding | 1.0.3

Additional Issues

• The "Workflow definition" card on the "Workflow details" page displays the specification for an older version instead of the current one
• No indication in the UI of which workflow run used which version
• Requires manual database intervention to clean up duplicate entries

Prerequisites (if any, like setup, operators/versions):

RHDH deployed in OpenShift with Orchestrator plugins enabled (plugin versions 1.8.2).

Steps to Reproduce

1. Deploy a Sonataflow workflow in Openshift and let the Orchestrator plugin pick it up.
2. Release a new version of the workflow and update the Sonataflow workflow in Openshift.
3. Orchestrator plugin now displays multiple entries of the same workflow, one for each version.

Actual results:

• Workflow appears multiple times in the UI list (once per deployed version)
• All entries redirect to the same workflow
• "Workflow definition" card may show outdated specification

Expected results:

• Each workflow should appear only once in the workflows list (showing the latest version)
• The "Workflow definition" card should display the current version's specification
• Historical version information should be accessible but not clutter the main UI.{}

Reproducibility (Always/Intermittent/Only Once):

Always.

Build Details:

RHDH Version: 1.8.0
Backstage Version: 1.42.5
Upstream: https://github.com/redhat-developer/rhdh/tree/release-1.8 @ d58642e0
Midstream: https://gitlab.cee.redhat.com/rhidp/rhdh/-/commits/rhdh-1.8-rhel-9 @ 011f64c3
Build Time: 2025-11-06T18:47:36Z

Additional info (Such as Logs, Screenshots, etc):