Verifying Tenant Deletion with Orphan Scans

A deletion job that reports success is evidence about the job, not about the data, and this page builds the independent check that closes that gap. It is a focused part of Tenant Offboarding & Data Retention Workflows: a scanner that shares no code with the deleter and answers one question — does anything belonging to this tenant still exist?

Problem Framing

Every deletion pipeline reports what it attempted. It cannot report what it missed, because the tables it forgot are exactly the tables it never queried. That is not a hypothetical: the usual causes are a table added after the deleter was written, a foreign key without a cascade, a denormalised copy in a reporting schema, a soft-delete flag that leaves the row in place, and an object-storage prefix that does not match the naming convention anyone documented.

An orphan scan inverts the question. Instead of enumerating what should be deleted, it enumerates what exists — discovering tables from the database catalogue rather than from a hand-written list — and asserts that none of it references the destroyed tenant. Tables the deleter never knew about are precisely the tables this finds.

The scanner must be independent in three ways: different code path, different credentials (read-only), and different discovery mechanism. Sharing a table registry with the deleter would reproduce the deleter's blind spot, which is the whole failure being guarded against.

Step-by-Step Guide

1. Discover tenant-scoped tables from the catalogue

-- Every table in any schema with a tenant_id column, however it got there.
SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS tenant_column
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
JOIN   pg_attribute a ON a.attrelid = c.oid
WHERE  c.relkind IN ('r', 'p')                       -- ordinary and partitioned tables
  AND  NOT a.attisdropped
  AND  a.attname IN ('tenant_id', 'tenantid', 'org_id', 'workspace_id', 'account_id')
  AND  n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER  BY 1, 2;

Accepting several column names is deliberate. Older tables and acquired codebases rarely agree on the convention, and a scanner that only knows tenant_id will confidently miss org_id.

2. Count remaining rows per table, cheaply

# orphan_scan.py — read-only role, no shared code with the deleter
import psycopg

def scan_relational(dsn: str, tenant_id: str) -> list[dict]:
    findings = []
    with psycopg.connect(dsn, autocommit=True) as conn:
        conn.execute("SET statement_timeout = '30s'")
        for schema, table, col in discover(conn):
            q = f'SELECT 1 FROM "{schema}"."{table}" WHERE "{col}" = %s LIMIT 1'
            try:
                hit = conn.execute(q, (tenant_id,)).fetchone()
            except psycopg.errors.QueryCanceled:
                findings.append({"store": f"{schema}.{table}", "status": "timeout"})
                continue
            if hit:
                n = conn.execute(
                    f'SELECT count(*) FROM "{schema}"."{table}" WHERE "{col}" = %s',
                    (tenant_id,)).fetchone()[0]
                findings.append({"store": f"{schema}.{table}", "rows": n, "status": "orphans"})
    return findings

LIMIT 1 first, then a count only if something is found, keeps the scan fast across hundreds of tables — the common case is zero rows, and an index seek answers that immediately.

3. Scan the non-relational stores by their own idioms

# non_relational.py
def scan_object_storage(client, buckets: list[str], tenant_id: str) -> list[dict]:
    out = []
    for bucket in buckets:                       # every bucket, not a curated subset
        for prefix in (f"{tenant_id}/", f"tenants/{tenant_id}/", f"exports/{tenant_id}/"):
            page = client.list_objects_v2(Bucket=bucket, Prefix=prefix, MaxKeys=1)
            if page.get("KeyCount", 0):
                total = sum(1 for _ in paginate(client, bucket, prefix))
                out.append({"store": f"s3://{bucket}/{prefix}", "objects": total, "status": "orphans"})
    # Versioned buckets: a deleted object still exists as a non-current version.
    for bucket in buckets:
        vers = client.list_object_versions(Bucket=bucket, Prefix=f"{tenant_id}/", MaxKeys=1)
        if vers.get("Versions"):
            out.append({"store": f"s3://{bucket} (versions)", "status": "orphans"})
    return out

The version check is the one most scanners omit. A bucket with versioning enabled retains every "deleted" object as a non-current version, so the data is fully recoverable and the deletion claim is false.

4. Treat unscannable stores as failures, not as passes

# report.py
def completeness(findings: list[dict], expected_stores: set[str]) -> dict:
    scanned  = {f["store"] for f in findings if f["status"] in ("clean", "orphans")}
    orphans  = [f for f in findings if f["status"] == "orphans"]
    unknown  = expected_stores - scanned
    timeouts = [f for f in findings if f["status"] == "timeout"]
    return {
        "clean": not orphans and not unknown and not timeouts,
        "orphan_stores": orphans,
        "unscanned_stores": sorted(unknown),      # a store we could not check is NOT a pass
        "timed_out": timeouts,
    }

A store that timed out, errored, or was unreachable must never round to "clean". This single rule is what separates a verification from a formality.

5. Sign the report and attach it to the certificate

# sign.py
import json, hashlib

def finalise(tenant_id: str, result: dict, signer) -> dict:
    body = {
        "tenant_id": tenant_id,
        "scanner_version": SCANNER_VERSION,
        "stores_scanned": len(result.get("orphan_stores", [])) + result.get("clean_count", 0),
        "clean": result["clean"],
        "findings": result,
    }
    raw = json.dumps(body, sort_keys=True).encode()
    return {
        **body,
        "sha256": hashlib.sha256(raw).hexdigest(),
        "signature": signer.sign(raw).hex(),
    }

Verification

The scanner itself needs testing, because a scanner that never finds anything looks identical to a clean estate.

# test_orphan_scan.py
def test_detects_a_row_the_deleter_missed(db, scanner):
    seed_tenant(db, "acme", tables=["invoice", "reporting.tenant_rollup"])
    delete_tenant(db, "acme", registry=["invoice"])          # deliberately incomplete
    result = scanner.scan("acme")
    assert not result["clean"]
    assert any("reporting.tenant_rollup" in f["store"] for f in result["orphan_stores"])

def test_detects_a_non_current_object_version(storage, scanner):
    put_versioned(storage, "acme/report.pdf")
    storage.delete_object(Bucket="files", Key="acme/report.pdf")   # leaves a version
    result = scanner.scan("acme")
    assert not result["clean"]

def test_unscannable_store_is_not_clean(scanner, broken_store):
    result = scanner.scan("acme")
    assert not result["clean"]
    assert result["unscanned_stores"]

Run the scanner against a live tenant in staging periodically, expecting it to find plenty — a scanner that returns clean for an active tenant is broken, and that inverted test is the cheapest way to notice.

Failure Modes & Gotchas

FAQ

Should the scan run before or after the certificate is issued? Before, and issuance should be mechanically gated on it. A certificate is a statement to a customer that their data is gone; issuing it and verifying afterwards means discovering you made a false statement, which is materially worse than a late certificate. Run the scan as the final step of the purge workflow and let a clean result be the only path to signing.

How do you scan a store too large to enumerate? Use the store's own index rather than a full walk: a targeted query on the tenant field, a prefix listing, or a search by the tenant term. If none of those exist, that itself is the finding — a store where you cannot ask "does this tenant have anything here?" is a store where you cannot honour a deletion request, and the fix is to add the index before the next offboarding rather than to sample and hope.

Does a clean scan prove deletion from backups? No, and the report should say so explicitly. Backups are immutable point-in-time images and are handled by expiry and tombstones instead. State the backup residency window alongside the scan result so the customer sees one honest picture rather than a clean scan implying more than it covers.

Should the scan run for every offboarding, or on a sample? Every one. The scan is cheap — an index seek per table — and its value comes precisely from being unconditional, because a sampled verification cannot support the statement a certificate makes about a specific tenant. Sampling is appropriate for a different question: periodically scanning a random selection of long-destroyed tenants to confirm that nothing has since reappeared through a restore, a replay, or a new store nobody added to the registry. Those two checks answer different questions and both are worth running.

What should the scan do about third-party processors? Query them where an interface exists, and record the gap where it does not. A payment provider, an email service and an analytics platform all hold tenant-derived data, and several of them expose an interface that can answer whether a given identifier still exists. Where none does, the deletion depends on a contractual commitment rather than on verification — which is a materially weaker position, and one the certificate should state plainly instead of implying a completeness the scan cannot support.

How long should the scan's output be retained? As long as the certificate it supports, which in practice means indefinitely. The report is small — a few kilobytes of counts and store names — and it is the artefact that substantiates the deletion claim years later, when the tenant record itself no longer exists to provide context.