Rolling Out Migrations Across a Tenant Fleet

Everything in parts 3 and 4 made a single question answerable: can one compiled migration set correctly migrate any schema? Yes. This part is about the question that actually wakes operations up: when migration 20241220000003 ships, how does it reach tenant 1 through tenant 500 — across shared group databases and isolated ones — and how do you know it arrived everywhere?

Single-database migration is a deploy step. Fleet migration is an orchestration problem, and orchestration problems are about topology, audit and failure — in that order.

The topology: who knows what

The division of labour falls out of the architecture from part 6:

• The control plane knows where everyone lives — every TenantConnection with its connection string and schema — but contains none of the migration machinery.
• The data plane owns the machinery — SchemaAwareDbContext, the replaced EF internals, the compiled migrations — but has no idea how many tenants exist.

So a rollout is the control plane iterating its records and, for each one, invoking the data plane's migration service with an explicit target. The pivotal method takes the connection string as a parameter rather than assuming ambient configuration:

public async Task ApplyMigrationsForSchemaAsync(string connectionString, string schemaName)
{
    await CreateSchemaIfNotExistsAsync(connectionString, schemaName);

    using var context = contextFactory
        .CreateForSchemaWithConnectionString(connectionString, schemaName);

    // ltree is database-scoped: install once per database, not per schema
    if (schemaName == SchemaAwareDbContext.DefaultSchema)
        await context.Database.ExecuteSqlRawAsync("CREATE EXTENSION IF NOT EXISTS ltree;");

    await context.Database.MigrateAsync();
}

Three lines carry most of the operational weight. CreateSchemaIfNotExists makes the call safe for brand-new tenants and no-op for existing ones. The extension check respects PostgreSQL's scoping rules — CREATE EXTENSION acts per database, so running it per schema would be wasted round-trips at best and privilege errors at worst. And MigrateAsync() consults this schema's own __EFMigrationsHistory (part 3's per-schema history), so each target independently applies exactly what it is missing.

That last property is the quiet hero of the design: the rollout is resumable by construction. If a fleet run dies at tenant 217, you re-run it from the top. Tenants 1–216 no-op in milliseconds; tenant 217 picks up where it failed. There is no rollout state to persist, because the state is the history tables.

The HTTP surface

The data plane exposes the machinery through a dedicated MigrationController, and the endpoint the control plane actually uses during provisioning and rollouts is the cross-database one:

[HttpPost("apply-dynamic-migrations-for-schema-with-connection")]
public async Task<IActionResult> ApplyMigrationsForDynamicSchemaWithConnection(
    [FromBody] ApplyMigrationsRequest request)
{
    await dynamicMigrationService.ApplyMigrationsForSchemaAsync(
        request.ConnectionString, request.SchemaName);
    return Ok(new { message = $"Migrations applied successfully for schema: {request.SchemaName}" });
}

Alongside it sit the query endpoints — pending-dynamic-migrations/{schemaName} returns whether a schema is behind, which turns “are we done rolling out?” into a loop of GET requests instead of a database archaeology session.

Let me say the uncomfortable part plainly: this endpoint accepts a connection string over HTTP and executes DDL with it. Inside the docker-compose network, where the only caller is the control-plane API, that is a pragmatic internal RPC. Exposed any further, it is a remote-code-execution kit with Swagger documentation. This surface must live on an internal network with service-to-service auth, and nothing in the C# can enforce that for you — it is a deployment contract, the same class of assumption as part 2's tenant-schema header, and it belongs in writing next to your network diagram.

The audit ledger

EF's per-schema history says what has been applied in that schema. Fleet operations need the transposed view — per migration, who has it — and that is what the control plane's TenantMigrationHistory entity exists for:

public class TenantMigrationHistory : BaseEntity
{
    public Guid TenantId { get; set; }
    public string MigrationName { get; set; } = string.Empty;
    public string MigrationVersion { get; set; } = string.Empty;
    public string DatabaseName { get; set; } = string.Empty;
    public string? SchemaName { get; set; }
    public DateTime AppliedAt { get; set; } = DateTime.UtcNow;
    public long? ExecutionTimeMs { get; set; }
    public bool IsSuccessful { get; set; } = true;
    public string? ErrorMessage { get; set; }
}

ExecutionTimeMs and ErrorMessage are the fields that turn a ledger into a diagnostic tool. “The rollout is slow” becomes “tenant 88's CreateCrumbTable took 40 seconds because their group database was IO-starved”. “Three tenants failed” comes with three error messages instead of three support tickets. The write happens on the control-plane side of the call, wrapping the HTTP invocation — which means it records the orchestrator's view, including failures where the data plane never even answered.

The honest gap: the loop that isn't there

Time to show the least finished code in the whole platform. The data plane has a hosted service intended to catch up all schemas at startup, and its heart currently reads:

public async Task ApplyMigrationsForAllTenantSchemasAsync()
{
    // First apply to public schema
    await ApplyMigrationsForSchemaAsync(SchemaAwareDbContext.DefaultSchema);

    // Then apply to tenant-specific schemas
    // This would typically involve getting a list of tenant schemas from a service
    logger.LogInformation("Tenant-specific schema migrations would be applied here");
}

"Would be applied here." The fan-out — enumerate active groups from the control plane, for each group enumerate its tenant schemas, apply per schema, record history — exists as provisioning-time calls and HTTP endpoints, but not yet as the one self-driving loop. The hosted service that would run it at startup is registered in Program.cs behind a comment, for a defensible reason: migrating N tenants inside application startup couples deploy time to fleet size and turns one bad tenant into a failed deploy. The loop belongs in a job you trigger — after deploy, per group, with concurrency limits — not in StartAsync. Writing that job is the largest single piece of remaining work in the codebase, and pretending otherwise would make this a worse blog series.

Proving it works: the end-to-end script

The migration system's test story is deliberately not a unit test. Mocking EF's internals proves nothing about the only questions that matter (does the right schema get the right tables under the real PostgreSQL?), so verification is a PowerShell script against a running docker-compose stack:

# create two shared groups, two shared tenants and one isolated tenant via the API
$createdGroup1 = Invoke-ApiCall -Method "POST" -Uri "$BaseUrl/api/DatabaseGroups" -Body $databaseGroup1
$createdSharedTenant1 = Invoke-ApiCall -Method "POST" -Uri "$BaseUrl/api/Tenants" -Body $sharedTenant1
$createdIsolatedTenant = Invoke-ApiCall -Method "POST" -Uri "$BaseUrl/api/Tenants" -Body $isolatedTenant

# then poll until each tenant reaches Active (status 2) — provisioning + migration succeeded
$waitResults += Wait-ForTenantCreation -TenantId $createdSharedTenant1.id

The script's assertion is the status machine from part 5: a tenant only reaches Active if database/schema creation and migrations succeeded. Polling for Active therefore exercises the entire pipeline — resolution, provisioning, schema creation, InStoreAssembly, the model cache key, the works — in one pass, covering both isolation strategies. It found the model-cache bug from part 4. A unit test never would have.

Where the design lands

The primitives are genuinely good: per-schema history makes rollouts idempotent and resumable, the parameterised connection string makes one service migrate any database, and the audit ledger makes progress observable. What is missing is the conductor — the batch job that walks the fleet on demand — and guard rails around a very sharp HTTP surface. I would rather ship honest primitives than a fragile conductor, but the conductor is not optional forever.

All this effort exists to deliver tables into schemas. Next part is finally about what is in them — the ltree hierarchies and jsonb payloads that made PostgreSQL worth all this trouble in the first place.