Provisioning Tenants at Runtime

There is a moment in every multi-tenant platform where a button click in an admin console executes CREATE DATABASE against a production PostgreSQL server. The first time I stepped through it in a debugger it felt vaguely illegal — DDL is supposed to live in migration scripts and change tickets, not in a MediatR handler. But on-demand provisioning is the entire promise of the platform: a tenant signs up, and seconds later they have isolated storage with the full schema applied.

The trick is not avoiding runtime DDL. It is containing it. This part walks the two provisioning paths in the codebase and the fences around them.

One entry point, two paths

TenantProvisioningService.ProvisionTenantAsync branches on the tenant type that part 1 made a property of the tenant rather than of the architecture:

if (tenantType == TenantType.Isolated)
{
    // Isolated tenant: new database group with MaxTenants = 1
    var databaseGroup = await CreateIsolatedTenantDatabaseGroupAsync(tenant.Name);
    connection = await CreateIsolatedTenantConnectionAsync(tenant, databaseGroup);
}
else
{
    // Shared tenant: use an existing group or find one with capacity
    var databaseGroup = databaseGroupId.HasValue
        ? await databaseGroupRepository.GetByIdAsync(databaseGroupId.Value)
        : await FindAvailableDatabaseGroupAsync();

    var schemaName = await CreateSharedTenantSchemaAsync(tenant, databaseGroup);
    connection = await CreateSharedTenantConnectionAsync(tenant, databaseGroup, schemaName);
}

// Apply migrations to the new database/schema
await ApplyMigrationsAsync(connection.ConnectionString, connection.SchemaName);

Whichever branch runs, the output shape is identical: a TenantConnection row recording the database, the schema, the connection string and the isolation type. That record is the contract with the data plane — everything downstream asks “what is tenant X's connection?” and never cares how it was made.

The isolated path, and my favourite design decision

An isolated tenant gets a real database:

var databaseName = $"tenant_{tenantName.ToLowerInvariant().Replace(" ", "_")}_{Guid.NewGuid():N}";

await CreatePostgreSQLDatabaseAsync(databaseName);

var databaseGroup = new DatabaseGroup
{
    GroupName = $"Isolated-{tenantName}-{Guid.NewGuid():N}",
    DatabaseName = databaseName,
    ConnectionString = connectionString,
    MaxTenants = 1,          // isolated tenants get their own database
    CurrentTenantCount = 1,
    IsActive = true
};

MaxTenants = 1 is the line I would defend in a design review. Instead of modelling “dedicated database” as a separate concept, an isolated tenant is just a database group that happens to be full at one. Capacity accounting, health checks, connection storage, the purge path — all of it works identically for both strategies, because there is only one abstraction. The whole “hybrid isolation” story costs one integer.

The database creation itself shows the two-tool pattern that all runtime DDL in this codebase follows — parameters where PostgreSQL allows them, validated quoted identifiers where it does not:

// Check: identifiers CAN be parameterised in a WHERE clause
var checkDbSql = "SELECT 1 FROM pg_database WHERE datname = @databaseName";
using var checkCommand = new NpgsqlCommand(checkDbSql, connection);
checkCommand.Parameters.AddWithValue("@databaseName", databaseName);
var exists = await checkCommand.ExecuteScalarAsync();

if (exists == null)
{
    // Create: DDL identifiers CANNOT be parameters — quote instead
    var createDbSql = $"CREATE DATABASE \"{databaseName}\"";
    using var createCommand = new NpgsqlCommand(createDbSql, connection);
    await createCommand.ExecuteNonQueryAsync();
}

You cannot write CREATE DATABASE @name — the protocol does not support parameters in DDL positions. So the identifier is interpolated, but only after it was constructed by the platform (slug plus GUID, lower-cased, whitelisted characters) rather than accepted from input, and only inside double quotes so PostgreSQL treats it as a literal identifier. A freshly created database also gets its extensions installed immediately — uuid-ossp, ltree, pg_trgm — because a database without ltree will fail the very first migration in interesting ways.

The new connection string is derived, not concatenated:

var builder = new NpgsqlConnectionStringBuilder(baseConnectionString)
{
    Database = databaseName
};
return builder.ConnectionString;

NpgsqlConnectionStringBuilder inherits host, credentials and pooling settings from the master connection string and swaps only the database. Every hand-rolled $"Host=...;Database={db}" I have ever written eventually broke on an option someone added to the base string; the builder never has.

The shared path

A shared tenant needs a schema inside a group database with spare capacity. The create-tenant handler does the capacity math before any DDL runs:

databaseGroup = await databaseGroupRepository.GetFirstWithAvailableCapacityAsync();

if (databaseGroup == null)
    throw new InsufficientCapacityException(
        "No available database groups with capacity found for shared tenant.");

if (!databaseGroup.HasCapacity())
    throw new InsufficientCapacityException(
        $"Database group '{databaseGroup.GroupName}' is at maximum capacity.");

Then the schema is created — idempotently — and the group's tenant count moves:

var schemaName = $"tenant_{tenant.TenantIdentifier.ToLowerInvariant()}";

var createSchemaSql = $"CREATE SCHEMA IF NOT EXISTS \"{schemaName}\";";
using var command = new NpgsqlCommand(createSchemaSql, connection);
await command.ExecuteNonQueryAsync();

databaseGroup.CurrentTenantCount++;
await databaseGroupRepository.UpdateAsync(databaseGroup);

The schema name embeds the tenant's slug, which is user-influenced — and this is where the fence from part 2 pays off again. Before any schema-creating SQL runs in the migration service, the name passes a validator that is fussier than the resolver's:

private static bool IsValidSchemaName(string schemaName)
{
    var reservedWords = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
        { "public", "information_schema", "pg_catalog", "pg_toast", "pg_temp" };

    if (reservedWords.Contains(schemaName)) return false;
    if (schemaName.Length > 63) return false;
    if (!char.IsLetter(schemaName[0]) && schemaName[0] != '_') return false;

    return schemaName.All(c => char.IsLetterOrDigit(c) || c == '_' || c == '-' || c == '$');
}

The reserved-word list is the one I care about most. Without it, a tenant named public would have its “own” schema aliased onto the database's default namespace, and its purge — DROP SCHEMA ... CASCADE — would delete every other tenant's assumption that the world is sane.

Migrate-on-provision

The last provisioning step hands off to the machinery from parts 3 and 4, using the overload that accepts an explicit connection string — because the control plane's own database is not where the tenant lives:

await dynamicSchemaMigrationService.ApplyMigrationsForSchemaAsync(
    connectionString, targetSchema);

Inside, that creates the schema if needed, builds a SchemaAwareDbContext for exactly this (connection, schema) pair, and calls MigrateAsync(). Because migrations are idempotent against the per-schema history table, re-running provisioning after a partial failure is safe — the steps that completed are skipped, the steps that did not are applied.

That matters because provisioning is not atomic and cannot be. CREATE DATABASE refuses to run inside a transaction, so there is no rollback that unwinds “database created but migrations failed”. The platform's answer is a status machine instead of a transaction: tenants are born in Provisioning, flip to Active only after every step succeeds, and drop to Failed (with a reason) otherwise. The end-to-end test script polls for exactly this transition rather than trusting the API's 200.

The trade-offs I signed for

The application credential is powerful. An account that can CREATE DATABASE and DROP SCHEMA is a bigger blast radius than a CRUD login. The mitigation is architectural: only the control plane holds that credential; the data-plane APIs connect with ordinary rights. I would still sleep better with a separate provisioning-only role, and that is on the list.

Capacity checks race. Two simultaneous signups can both see one free slot. HasCapacity() re-checks and IncrementTenantCount() throws past the maximum, and the row carries a concurrency token — but a serialized “allocate slot” operation would remove the race instead of merely losing one of the racers politely.

Orphans are possible. A crash between CREATE DATABASE and the TenantConnection insert leaves a database nobody remembers. Idempotent re-provisioning covers the retry case; a reconciliation sweep (“databases matching tenant_* with no connection row”) is the missing janitor.

Provisioning writes a lot of rows into the control plane — connections, groups, counts, statuses. Next part: the domain model that keeps all of that honest, and why the database about tenants is the most carefully guarded single-tenant database in the system.