elsa-workflows / elsa-workflows/elsa-core
Include TenantId in IX_WorkflowDefinition_DefinitionId_Version unique index to support multi-tenancy
- Dominant language
- C#
- Stars
- 7.9k
- Forks
- 1.5k
- Avg merge
- 15h 22m
- Merged PRs (30d)
- 114
Description
## Enhancement Request
### Enhancement Overview
The unique index `IX_WorkflowDefinition_DefinitionId_Version` on `Elsa.WorkflowDefinitions` is defined on `(DefinitionId, Version)` only. In a multi-tenant setup, this prevents two tenants from having a workflow definition with the same `DefinitionId` and `Version`, even though they are entirely isolated tenants. Attempting to do so produces:
```
Msg 2601, Level 14, State 1
Cannot insert duplicate key row in object 'Elsa.WorkflowDefinitions'
with unique index 'IX_WorkflowDefinition_DefinitionId_Version'.
The duplicate key value is (, ).
```
---
### Proposed Enhancement
Recreate the unique index to include `TenantId`, using two filtered indexes to correctly handle both tenanted and non-tenanted workflows (since `NULL` is not considered equal to `NULL` in a SQL unique index):
**Current index:**
| Column | Key Ordinal |
|---|---|
| `DefinitionId` | 1 |
| `Version` | 2 |
**Proposed replacement:**
| Index Name | Columns | Filter |
|---|---|---|
| `IX_WorkflowDefinition_DefinitionId_Version` | `DefinitionId`, `Version`, `TenantId` | `WHERE TenantId IS NOT NULL` |
| `IX_WorkflowDefinition_DefinitionId_Version_NoTenant` | `DefinitionId`, `Version` | `WHERE TenantId IS NULL` |
```sql
-- Tenanted workflows
CREATE UNIQUE INDEX [IX_WorkflowDefinition_DefinitionId_Version]
ON [Elsa].[WorkflowDefinitions] ([DefinitionId], [Version], [TenantId])
WHERE [TenantId] IS NOT NULL;
-- Non-tenanted workflows
CREATE UNIQUE INDEX [IX_WorkflowDefinition_DefinitionId_Version_NoTenant]
ON [Elsa].[WorkflowDefinitions] ([DefinitionId], [Version])
WHERE [TenantId] IS NULL;
```
---
### Alternative Solutions
| Alternative | Why It Falls Short |
|---|---|
| Prefix `TenantId` into `DefinitionId` | Couples infrastructure concerns into a business identifier; may break Elsa's internal workflow resolution logic |
| Modify the index locally as a custom migration | Creates a maintenance burden on every Elsa upgrade and diverges from the official schema |
---
### Use Cases
- A SaaS platform using Elsa where multiple tenants need equivalent workflow definitions (e.g. an onboarding workflow deployed per tenant).
- Copying a "template" workflow definition from a system tenant to individual tenants.
- Running isolated tenant environments that share a single Elsa database.
---
### Impact of Enhancement
Without this fix, multi-tenant deployments of Elsa on SQL Server **cannot** share `DefinitionId` values across tenants, which severely limits the viability of multi-tenancy as a first-class feature. The `TenantId` column already exists on the table, indicating multi-tenancy was a deliberate design goal — the index simply needs to be aligned with that intent.
---
### Visuals and Mockups
N/A
---
### Additional Context
- Tested against: **SQL Server** — other database providers should be reviewed for the same issue.
Contributor guide
Assessment
This issue has not been assessed yet.