Draining the Archive Without Losing a Row

Part 7 made archiving instant by deferring all data movement; Part 8 made deferred data readable. This part is where the deferral gets paid: EfCoreBackfillService, the worker that moves rows from Orders_v2 into Orders in the background, batch by batch, until the archive is empty and FullyDrained flips true.

Moving a row between tables sounds trivial until you remember who else is at the party. FindAsync may be reading the row right now. A write-through update (Part 8) may be moving the same row at the same moment. The worker itself might crash between statements. The drain choreography exists so that in every interleaving, a row ends up in exactly one place with its data intact — or, in the worst degenerate case, in two places briefly, never zero.

The outer loop: history-driven work discovery

The service is the same host shape as Part 6's payload backfill — BackgroundService, 15-second startup grace, interval and batch size from options, catch-all error isolation. What differs is how it finds work: not by scanning data, but by reading the ledger Part 7's migration helper maintains:

var archiveEntries = await db.Set<SchemaVersionEntry>()
    .Where(e => !e.FullyDrained)
    .ToListAsync(ct);

Every archive table that has ever been created and not yet emptied is a row here. For each, the worker resolves the CLR type back from the stored table name:

var entityType = MigrationRegistry.AllVersionedTypes()
    .FirstOrDefault(t => t.Name + "s" == archive.EntityTypeName
        || t.Name == archive.EntityTypeName);
if (entityType is null) continue;

Name-based resolution, using the same naive pluraliser as the schema helpers. It works because the same convention wrote both sides; it is also the kind of stringly-typed joint I flag in my own code reviews, and it makes the closing part's trade-off list. An archive row whose name matches no registered type is silently skipped — safe, but worth a dashboard query if you rename entity classes.

Batch reads come from BuildBatchSelect, which selects the archive's columns plus the concurrency timestamp:

SELECT "Id", "OrderNumber", "Total", "ClientId", "ShippingCity", ...,
       "_backfill_modified_at"
FROM "Orders_v1" LIMIT 50

As the reader materialises each row, archive column names are mapped through GetColumnsAtVersion to current property names (ClientId → CustomerId), values stay CLR-typed, and _backfill_modified_at is peeled off and carried alongside the row rather than into it. Each row leaves this stage as (payload, modifiedAt) — the payload for migrating, the timestamp for the dance ahead.

The choreography: insert, then conditionally delete

Here is the per-row core, trimmed to its skeleton:

// 1. migrate through the one pipeline
row["__version"] = archive.SchemaVersion;
var migrated = migrator.MigrateToLatest(row);

// 2. insert into the current table — idempotent
var insertSql = BuildInsertSql(entityType, currentVersion, currentTable, migrated);
await db.Database.ExecuteSqlRawAsync(insertSql.Sql, insertSql.Parameters, ct);
//    ... VALUES (...) ON CONFLICT ("Id") DO NOTHING

// 3. delete from the archive — only if untouched since we read it
var deleteCount = await db.Database.ExecuteSqlRawAsync(
    $"DELETE FROM \"{archive.ArchiveTableName}\" " +
    $"WHERE \"Id\" = {{0}} AND \"_backfill_modified_at\" = {{1}}",
    [entityId, modifiedAt], ct);

// 4. repoint the registry — only if the delete won
if (deleteCount > 0)
{
    await db.Database.ExecuteSqlRawAsync(
        "UPDATE \"__EntityRegistry\" " +
        "SET \"CurrentTable\" = {0}, \"SchemaVersion\" = {1} " +
        "WHERE \"Id\" = {2} AND \"CurrentTable\" = {3}",
        [currentTable, currentVersion, entityId, archive.ArchiveTableName], ct);
    batchMigrated++;
}

Step 1 is by now a familiar refrain: the same walk as lazy reads, JSON backfill, and write-through — attribute steps, hand-written overrides, type migrators. Four consumers, one engine, zero chances for eager and lazy migration to disagree about what a v1 row means.

The order of steps 2–4 is the entire design, so let me defend each edge.

Insert before delete. If the worker dies between the two statements, the row exists in both tables. That is the deliberate failure direction: the registry still points at the archive, so reads still resolve there (FindAsync consults the registry before ever considering Orders); the duplicate in the current table is invisible until the next drain pass completes the delete and repoints. Delete-before-insert would fail in the other direction — a row that exists nowhere — and no retry can un-lose data. When forced to pick a failure mode, pick the one that duplicates rather than the one that destroys.

ON CONFLICT ("Id") DO NOTHING on the insert. This is what makes the retry after a crash safe: the second pass's insert no-ops against the row the first pass already placed, then proceeds to the delete it never reached. It is also the guard against the write-through race — if a user updated this entity between our read and our insert, write-through already put a newer row in the current table, and our stale insert must lose. DO NOTHING guarantees the freshly-written row is never overwritten by backfill's older snapshot.

The conditional delete is the commit point. The _backfill_modified_at predicate means the delete only succeeds if the archive row is byte-for-byte the one we read at the top of the batch. If anything touched it since — and “touched” includes write-through having deleted it — deleteCount comes back 0, and the worker treats the whole row as not-migrated: no registry update, no count. Whatever concurrent thing happened wins; the worker's insert either no-opped or sits inert until a later pass observes the current state and reconciles. The timestamp column was planted by ArchiveCurrentTable (Part 7) precisely so this predicate would exist on day one of every archive's life.

The registry repoint is itself guarded — WHERE "CurrentTable" = {archive} — so if write-through already repointed the row mid-flight, the worker's late update matches zero rows instead of stomping newer state. Every statement in the sequence is written to lose gracefully to anyone who got there first.

And the safety net under all of it: Part 8's mid-move retry in FindAsync. A reader that catches a row precisely between insert and delete-plus-repoint gets a registry pointing at an archive row that still exists (fine), or one that just vanished — in which case the reader re-checks the current table and finds the freshly-inserted copy. The read path and the drain path were designed as a pair.

Small print, honestly

Being precise about the windows this design accepts rather than eliminates:

• Transient duplicates are real. Between insert and delete, the row is in two tables. Anything that queries Orders directly — bypassing the reader — can see a row the registry does not acknowledge yet. The __AllOrders view (Part 8) will genuinely list it twice for that instant. If your reporting cannot tolerate that, run drains in a quiet window; the interval is configurable down to that.
• The timestamp guard is only as good as its writers. Nothing in the library updates archive rows in place — write-through deletes, drains delete — so “modified since read” today mostly means “deleted since read”, which the predicate handles. But if your code UPDATEs archive tables directly without touching _backfill_modified_at, the guard cannot see it. Treat archive tables as delete-only, which is what they are for.
• Rows can be skipped forever. Per-row try/catch (same policy as Part 6) means a row that fails migration — a type-conversion surprise, a constraint violation on insert — logs a warning and stays archived. FullyDrained never flips while it remains, which is your signal: an archive entry that stays undrained across many intervals is a data-quality investigation, not a patience problem.
• Draining marks, but does not drop. When a batch read returns zero rows, the worker flips FullyDrained and stamps DrainedAtUtc — and stops there. Dropping Orders_v1 is a human decision (and a one-line manual migration), because the empty table is also your last cheap rollback if anything about the drain needs forensic review.

if (rows.Count == 0)
{
    archive.FullyDrained = true;
    archive.DrainedAtUtc = DateTime.UtcNow;
    await db.SaveChangesAsync(ct);
}

The lifecycle, end to end

Stand back and the whole relational story is now a closed loop. A schema bump archives the table in milliseconds and opens a fresh one. Reads resolve anywhere, migrating on the fly. Updates promote whatever users touch. The drain worker moves everything else, batch by guarded batch, until the archive is empty, marked, and droppable — and the registry never once told a caller a wrong address. Lazy where it can be, eager where it should be, and every mechanism sharing one migration pipeline declared as attributes on the entity.

What the series has not yet done is sit down with the bill: the static registry, the reflection, the Postgres coupling, the flags that do not do anything yet, and the honest question of when you should refuse to adopt any of this.

Next: The Bill for Versioning Everything — every trade-off in one place, and where VersionKit goes from here.