Tenant Offboarding: Soft Delete, Purge and Restore

Provisioning a tenant is exciting engineering. De-provisioning one is where careers go to die. Behind the innocuous “Delete tenant” button in the admin console sit two of the most destructive statements PostgreSQL offers — DROP SCHEMA ... CASCADE and DROP DATABASE — and in the shared-schema world from part 1, the schema being dropped lives in a database forty-nine other tenants are using right now.

So the platform refuses to make delete a single action. Offboarding is a lifecycle: suspend, soft-delete, and only then — as a separate, deliberate command — purge. This part walks each step and the machinery that makes the safe path the default one.

Soft delete you cannot forget to use

The trap with soft delete is that it usually depends on discipline: every query must remember WHERE IsDeleted = 0, every delete must remember to be an update. Discipline does not survive team growth. The control plane wires both halves into the DbContext itself, where nobody can forget them.

Half one — reads. Every entity gets a global query filter:

modelBuilder.Entity<Tenant>().HasQueryFilter(e => !e.IsDeleted);
modelBuilder.Entity<TenantConnection>().HasQueryFilter(e => !e.IsDeleted);
modelBuilder.Entity<TenantFeature>().HasQueryFilter(e => !e.IsDeleted);
modelBuilder.Entity<DatabaseGroup>().HasQueryFilter(e => !e.IsDeleted);
modelBuilder.Entity<TenantMigrationHistory>().HasQueryFilter(e => !e.IsDeleted);

Half two — writes. SaveChangesAsync intercepts deletions and rewrites them:

foreach (var entry in ChangeTracker.Entries<BaseEntity>())
    switch (entry.State)
    {
        case EntityState.Modified:
            entry.Entity.UpdatedAt = DateTime.UtcNow;
            break;
        case EntityState.Deleted:
            entry.State = EntityState.Modified;      // soft delete
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
            break;
    }

The pleasant consequence: application code calls Remove() like it always did, and the context quietly refuses to destroy anything. The delete handler contains no soft-delete logic at all — it cannot get it wrong, because it is not the one doing it.

The less pleasant consequence: once reading deleted rows is impossible by default, every legitimate “include the dead” path needs an explicit escape hatch. The repositories grow methods like GetByIdIncludingDeletedAsync (built on IgnoreQueryFilters()), and each one is a small hole punched in the safety net. That trade — safe by default, noisy exceptions — is the right one, but the noise is real.

Suspension: reversible by design

Before delete there is suspend, and the guarded transitions from part 6 do the work — Suspend(reason) only from Active, timestamps and reason recorded, Activate() to come back. Nothing in the data plane is touched; the tenant's schema and data sit untouched while the status flag does the enforcement.

Which surfaces the one dishonesty in the current implementation, flagged in part 2 and still true here: enforcement happens through TenantContext.RequireActiveTenant(), whose answer is cached for five minutes. A suspended tenant can keep making requests until the cache expires. For “stop the bleeding” scenarios that window matters, and fixing it (targeted cache eviction on status change — Redis is right there) is on the retrospective list.

Purge: the only door to real deletion

Hard deletion is its own command, and its first move is a guard that encodes the whole philosophy:

var tenant = await tenantRepository.GetByIdIncludingDeletedAsync(request.Id);

if (!tenant.IsDeleted)
    throw new InvalidOperationException("Cannot purge a tenant that is not soft-deleted");

You cannot purge a live tenant. Ever. The API will not let you; the two-step is mandatory. Between soft delete and purge there is a human-sized gap in which “wait, wrong tenant” costs nothing — RestoreTenantCommand flips IsDeleted back and the customer never knows. After purge, the gap is a backup-restore ticket and an apology call.

Once past the guard, the handler unwinds provisioning in reverse order — capacity first, then infrastructure, then records:

// Give the slot back to the group
databaseGroup.CurrentTenantCount = Math.Max(0, databaseGroup.CurrentTenantCount - 1);
await databaseGroupRepository.UpdateAsync(databaseGroup);

// Drop the actual storage (schema or database)
await tenantProvisioningService.DeprovisionTenantAsync(tenant);

// Isolated tenants also take their dedicated group record with them
if (tenant.TenantType == TenantType.Isolated && tenant.DatabaseGroupId.HasValue)
    await databaseGroupRepository.HardDeleteAsync(tenant.DatabaseGroupId.Value);

// Finally, the tenant row itself — a real DELETE this time
await tenantRepository.HardDeleteAsync(tenant.Id);

The MaxTenants = 1 unification from part 5 pays its final dividend here: an isolated tenant's dedicated database is just “a group that is now empty”, so purging it is the same bookkeeping as any group operation, plus one hard delete. And note HardDeleteAsync — because the context converts Remove() into soft delete, genuinely destroying a row requires a deliberately different repository method. Destruction is never the default spelling.

The SQL at the bottom

For a shared tenant, deprovisioning is one statement against the group database:

var dropSchemaSql = $"DROP SCHEMA IF EXISTS \"{tenantConnection.SchemaName}\" CASCADE";

CASCADE is doing heavy lifting: tables, indexes, the tenant's own __EFMigrationsHistory — everything inside the namespace goes, and nothing outside it. This is the moment schema-per-tenant justifies itself operationally. The equivalent cleanup in a discriminator-column design is DELETE FROM every table with a WHERE TenantId = clause, in dependency order, in batches, praying about foreign keys. Here it is one line, and the reserved-word validation from part 5 is what guarantees SchemaName can never be public.

For an isolated tenant, there is a prerequisite ritual — PostgreSQL will not drop a database that has connections:

var terminateSql = @"
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = @databaseName AND pid <> pg_backend_pid()";
// ...then:
var dropDbSql = $"DROP DATABASE IF EXISTS \"{connection.DatabaseName}\"";

pg_terminate_backend kicks every session off the doomed database — mid-request, if necessary; the tenant is purged, there are no legitimate requests — while pid <> pg_backend_pid() politely declines to terminate the connection doing the terminating. Both statements are IF EXISTS-idempotent, so a purge that crashes halfway can be re-run without drama.

What the two-step does not solve

Soft delete does not free anything. Between soft delete and purge, the tenant's schema or database sits there at full size — invisible in the control plane, fully present on disk. That is a feature (it is the restore window) with a bill attached. What is missing is policy: nothing currently expires soft-deleted tenants into automatic purges after thirty days. Today that is a human remembering; it should be a background job with an audit trail.

Restore has an edge. Restoring a soft-deleted shared tenant is trivial — its schema never went anywhere. But its database group's CurrentTenantCount was never decremented at soft-delete time (only purge touches capacity), so the books stay balanced. I had this wrong in an early draft of the handler — decrementing at soft delete and again at purge — and the double-count meant groups slowly leaked phantom capacity. Reconciliation queries found it; the lesson is that capacity changes belong to exactly one lifecycle step.

Backups are out of scope but not out of mind. DROP DATABASE on an isolated tenant destroys data the platform may be legally required to have destroyed — or legally required to retain. The purge handler is where a “final snapshot to cold storage” step belongs, and its absence in the current code is the sharpest edge in this whole part.

Offboarding closes the loop on a single tenant's life. The remaining operational question is fleet-shaped: when migration 20241220000003 ships, how does it reach every schema in every group database — and how do you know it arrived? That is part 8.