ADR-008: Database-per-Tenant Multi-Tenancy

Status

Accepted

Date

2023-Q2

---

Context

Microtec ERP is a multi-tenant SaaS platform. Every customer (tenant) runs the same application code but must see only their own data — never another tenant's. We needed to choose a multi-tenancy data isolation model.

The three standard options considered:

Model	Description	Isolation Level
Shared database, shared schema	Single DB, all tenants in same tables; filtered by TenantId column	Logical only
Shared database, schema-per-tenant	Single DB server; each tenant gets their own schema namespace	Medium
Database-per-tenant	Each tenant gets their own SQL database on the shared server	Physical

Domain Requirements

The ERP domain drove specific requirements:

1. Regulatory isolation: Saudi and Egyptian data residency requirements (ZATCA, ETA) mandate that tenant data must be fully separable on demand.
2. Performance isolation: A tenant running a large month-end accounting close must not degrade other tenants' API response times.
3. Schema flexibility: Some tenants may require custom fields or different schema versions during a phased migration.
4. Backup granularity: Tenants may request point-in-time restore of their specific data without affecting others.
5. Compliance audit: Tenant data export for GDPR-equivalent requests must be clean and complete.

---

Decision

Adopt database-per-tenant isolation using Azure SQL.

Each tenant has an independently named SQL database on the shared mic-backend-shared-sql SQL server (Azure SQL elastic pool). The ITenantProvider resolves the current tenant from the JWT token on every request, and IUnitOfWork<TContext> dynamically connects to the correct database.

Architecture

Tenant Resolution Flow

1. Request arrives at the API gateway with a Keycloak JWT token
2. TenantMiddleware extracts the tenant_id claim from the token
3. ITenantConnectionStringResolver looks up the tenant's database name in the admin database (cached in Redis for 5 minutes)
4. IUnitOfWork<TContext> opens a connection to the resolved database
5. All EF Core queries for this request execute against the tenant's database

Connection String Management

Tenant connection strings are stored in the admin database and cached:

// Conceptual — simplified
public class TenantConnectionStringResolver : ITenantConnectionStringResolver
{
    private readonly IAdminUnitOfWork _adminUow;
    private readonly IDistributedCacheService _cache;

    public async Task<string> ResolveAsync(string tenantId, CancellationToken ct)
    {
        var cacheKey = $"tenant:connstr:{tenantId}";

        return await _cache.GetOrCreateAsync(cacheKey, async () =>
        {
            var tenant = await _adminUow.Repository<Tenant>()
                .FirstOrDefaultAsync(t => t.Id == tenantId, ct)
                ?? throw new NotFoundException($"Tenant {tenantId} not found");

            return tenant.ConnectionString;   // Decrypted from KV reference
        }, TimeSpan.FromMinutes(5));
    }
}

Database Naming Convention

tenant_{sanitized_tenant_code}_{environment}

Examples:

• tenant_acme_dev
• tenant_acme_stage
• tenant_acme_production

---

Migration Strategy

Tenant databases are migrated independently using EF Core Migrations.

Approach

• Tenant databases use EF Core migrations applied per-tenant at startup (or via a migration runner service)
• Admin database is migrated by the BusinessOwners service at startup
• New tenant provisioning: When a tenant is created, the BusinessOwners service provisions a new empty database and runs all pending migrations

Migration Runner

# Migrate a specific tenant database manually
dotnet ef database update \
  --project Platforms/Src/AppsPortal/Accounting/AppsPortal.Infrastructure \
  --startup-project Platforms/Src/AppsPortal/Accounting/AppsPortal.Apis \
  -- --tenant-id acme

The migration runner service (Platforms/Src/BusinessOwners/MigrationRunner/) iterates all tenant databases and applies pending migrations during platform upgrades.

Tenant database migrations must be backward-compatible

Because migrations are applied rolling (not all tenants simultaneously), a new migration must be compatible with the previous version of the application still running on some tenants. Additive changes (new columns with defaults, new tables) are safe. Destructive changes (rename, drop) require a multi-step migration strategy.

---

Consequences

Positive

• Maximum data isolation: A SQL bug or misconfiguration cannot leak data between tenants — they are in separate databases
• Independent backup and restore: Azure SQL automatic backups are per-database; a tenant's point-in-time restore does not affect others
• Performance isolation: Azure SQL elastic pool ensures CPU/memory contention is bounded per tenant (via per-database DTU/vCore limits)
• Regulatory compliance: Tenant data can be exported, deleted, or migrated independently — clean GDPR-equivalent requests
• Schema version flexibility: A tenant on a long-term contract can remain on an older schema version while others upgrade
• Security: Connection strings are tenant-specific — a compromised connection string only exposes one tenant's data

Negative

• Connection pool pressure: 500 tenants = 500 possible connection pools per service instance. Mitigated by connection string pooling in SqlConnection and MultipleActiveResultSets=False.
• Migration complexity: Every schema change must be applied to N databases (one per tenant). The migration runner adds operational complexity.
• Admin database as single point of failure: If the admin database is unreachable (and Redis cache is cold), all tenant lookups fail. The Redis 5-minute cache significantly reduces this risk.
• Cross-tenant queries are impossible: Platform analytics that span all tenants cannot use EF Core. A separate reporting pipeline reads from all tenant databases asynchronously.
• Cost: N databases consume more storage overhead than one shared database. Mitigated by Azure SQL elastic pool's shared storage model.

Neutral

• Shared SQL server (mic-backend-shared-sql-rg) is intentionally not renamed per environment — it is a cross-environment resource with connection strings differentiated by database name

---

Alternatives Reconsidered

Schema-per-tenant was the close runner-up. It was rejected because:

• Azure SQL does not support per-schema resource limits; noisy-neighbour risk remains
• Cross-schema queries (for future platform analytics) would create coupling
• EF Core's multi-schema support requires significant custom infrastructure

Row-level security (shared schema) was rejected because:

• Even with RLS, a SQL Server bug or misconfigured filter could expose all tenants
• Regulatory requirements effectively rule out shared-schema approaches for Saudi financial data

---

Related ADRs

• ADR-001: Microservices Architecture (each service has its own tenant databases)
• ADR-002: Azure Container Apps (services run stateless; tenant context from JWT)
• ADR-006: Clean Architecture (ITenantProvider is a Domain interface; resolver is Infrastructure)