Querying the Graveyard

Eight posts ago this series promised that “what did this look like before it was deleted?” would stop being a database-restore question. Everything since has been about the write side — intercepting, storing, correlating, snapshotting, expiring. But an archive is judged on its read side: the day someone actually asks, what does getting an answer look like? This final part walks the consuming patterns from the sample API, the jsonb layer underneath them, and the one door the library still hasn't built — restore.

Typed columns pay off first

Part 4's dual-write design promised that promoted properties become ordinary queryable columns. Here's that promise being collected — the sample's archived-orders endpoint is EF at its most boring, which is the highest compliment I can pay an archive:

app.MapGet("/orders/archived", async (AppDbContext db) =>
{
    var archived = await db.Set<OrderArchive>()
        .OrderByDescending(a => a.ArchivedAt)
        .Take(50)
        .Select(a => new
        {
            a.Id,
            a.EntityId,
            a.CorrelationId,
            a.ArchivedAt,
            a.OrderNumber,
            a.Total,
            a.Status,
            SnapshotReady = a.SnapshotId != null
        })
        .ToListAsync();

    return Results.Ok(archived);
});

No JSON operators, no casts, no special API — OrderArchive is just an entity, mapped to order_archive, indexed on the columns you filter by. Support tooling, admin screens, “show me everything this customer deleted last quarter” — all of it is Where/OrderBy against real columns. Two details earn their place: the endpoint queries db.Set<OrderArchive>() directly (archive DbSet properties are optional; the sample leaves them commented out and uses Set<T>()), and SnapshotReady = a.SnapshotId != null shows the archive row doubling as a status indicator without touching the snapshot table.

There's a subtle absence here too: no IgnoreQueryFilters(), because none is needed. Archive types don't implement ISoftDeletable, so the global soft-delete filter never attaches to them. Live tables hide deleted rows; archive tables are the deleted rows; the type system keeps the two regimes from leaking into each other.

When the column wasn't promoted

OrderLineArchive promoted nothing — so is a line's ProductName unreachable? No: it's in the ScalarPayload, and Postgres treats jsonb as data, not as a string. The operators do the work SQL columns would:

SELECT "ScalarPayload"->>'ProductName' AS product,
       ("ScalarPayload"->>'Quantity')::int AS qty
FROM   order_line_archive
WHERE  "ScalarPayload"->>'ProductName' = 'Widget'
ORDER  BY "ArchivedAt" DESC;

From EF, the same reach-in works without leaving LINQ if you map a shadow accessor, or more pragmatically via EF.Functions/raw SQL for the occasional forensic query. And if a payload query graduates from occasional to hot path, that's the design nudging you: promote the property onto the archive subclass, add a migration, and it becomes a real column for all rows archived from then on — with the payload still holding the history for the old ones. A GIN index on the payload column is the intermediate step for genuinely ad-hoc estates; the sample doesn't ship one because indexed-by-default JSON is a cost most archives never need.

This is the schema-drift insurance from part 4 working in the read direction: the archive can answer questions you didn't know to promote columns for, at forensic speed rather than dashboard speed, and the promotion path upgrades any question that turns out to matter.

Polling for a snapshot, honestly

The snapshot endpoint is my favorite piece of the sample because it maps part 7's job state machine onto HTTP without pretending async work is synchronous:

app.MapGet("/orders/snapshot/{correlationId:guid}", async (
    Guid correlationId, AppDbContext db) =>
{
    var snapshot = await db.Set<OrderSnapshot>()
        .FirstOrDefaultAsync(s => s.CorrelationId == correlationId);

    if (snapshot is null) return Results.NotFound();

    return snapshot.Status switch
    {
        SnapshotStatus.Pending  => Results.Accepted(
                                       "/orders/snapshot/" + correlationId,
                                       new { status = "pending" }),
        SnapshotStatus.Complete => Results.Ok(new
                                   {
                                       status  = "complete",
                                       payload = snapshot.Payload
                                   }),
        SnapshotStatus.Failed   => Results.Problem(snapshot.FailureReason),
        _                       => Results.NoContent()
    };
});

Each job state gets its correct HTTP citizen: Pending returns 202 Accepted with a Location pointing back at itself — the standard “not done, poll here” contract — Complete returns the tree, Failed returns a problem document carrying the FailureReason the service recorded. The lookup key is the correlation id, which the delete endpoint could return to the caller at deletion time; the id minted in the interceptor becomes the claim ticket for the eventual snapshot. Ten seconds of default poll interval means a client usually gets one 202 and then the payload. (The NotRequested state never reaches this endpoint in practice — a job row only exists because the interceptor created it Pending — which is why the switch's discard arm returns the honest shrug of a 204.)

The door that isn't built: restore

Now the gap I owe you before the series closes. The abstractions hint at it — IArchiveEntry's doc comment mentions “the interceptor and restore service” — but there is no restore service. Nothing in the library takes an archive row or a snapshot and puts a live row back. Un-deleting today is a manual affair, and it's worth being precise about what that involves, because everything is almost there:

// rehydrate an order from its archive payload — consumer code, not library code
var payload = JsonSerializer.Deserialize<Dictionary<string, JsonElement>>(
    archiveRow.ScalarPayload)!;

var order = new Order
{
    Id          = payload["Id"].GetGuid(),        // or a fresh id — see below
    OrderNumber = payload["OrderNumber"].GetString()!,
    Total       = payload["Total"].GetDecimal(),
    // ... IsDeleted = false, and decide what DeletedAt means now
};
db.Orders.Add(order);

The mechanics are easy; the semantics are why this isn't a weekend feature. Restore with the original id and you're asserting nothing claimed that id since — no unique constraint (an OrderNumber?) was reused, no downstream system tombstoned it. Restore the graph and you're back in part 6's territory in reverse: which archive rows across which tables constitute the family (the correlation id knows), in what order do parents and children insert, and what happens when the Product a line referenced was also deleted — or worse, TTL-reaped in the meantime? Restore is a workflow with policy questions, not a method, and shipping a naive RestoreAsync() that works in demos and corrupts invariants in production would betray everything the honest sections of this series stood for. It's the headline of the library's next chapter, designed as: rehydrate-as-new-entity by default (fresh id, provenance columns pointing at the archive row), original-id restore as the explicit, eyes-open overload.

The whole machine, once around

That's the series. One attribute declares policy; a scanner and registry validate it at startup (part 5). A SaveChangesInterceptor turns soft-delete intent into a physical delete plus an archive row built from the change tracker with zero extra reads, atomically (part 2), within batching limits and interception boundaries you now know precisely (part 3). The row lands in a table designed to outlive its source — typed columns plus jsonb, an index that refuses to be a foreign key (part 4) — stamped with the correlation id that makes a family reconstructable (part 6), reassembled into a tree by an outbox-style snapshot service (part 7), and eventually reaped by a TTL policy frozen at archive time (part 8). And the ledger of IOUs is public: the outbox, cascade enforcement, multi-table snapshot assembly, snapshot retention, the source generator, restore.

If you have ever typed IsDeleted = true and felt the small dishonesty of it — the row that's neither here nor gone — the whole point of ArchiveKit is that delete can finally mean delete, without meaning forgotten. The graveyard is organized, indexed, expiring on schedule, and it answers questions. Go delete something.