elsa-workflows / elsa-workflows/elsa-core

Include TenantId in IX_WorkflowDefinition_DefinitionId_Version unique index to support multi-tenancy

Open
#7,539 1 comment 0 reactions 0 assignees View on GitHub
triaged
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.