An Archive Table That Outlives Its Source

An archive row has a strange job description. It must be queryable today, readable years from now against a schema that no longer exists, and it must survive the deletion of the very row it describes — which rules out the most instinctive tool in the relational toolbox, the foreign key. This post is about the shape ArchiveKit gives that row and the three deliberate oddities in its model configuration.

One base class, two kinds of columns

Every archive type inherits from ArchiveEntry<TEntity>, which carries the metadata the machinery needs:

public abstract class ArchiveEntry<TEntity> : IArchiveEntry
    where TEntity : class, ISoftDeletable
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public Guid CorrelationId { get; set; }
    public DateTime ArchivedAt { get; set; }
    public bool IsRoot { get; set; }

    public Guid EntityId { get; set; }
    public TEntity? Entity { get; set; }

    public Guid? SnapshotId { get; set; }

    public string ScalarPayload { get; set; } = string.Empty;

    public DateTime? ExpiresAt { get; set; }
}

What you add on your subclass is a decision about queryability. The sample's OrderArchive promotes five properties:

public class OrderArchive : ArchiveEntry<Order>
{
    public Guid CustomerId { get; set; }
    public string OrderNumber { get; set; } = default!;
    public decimal Total { get; set; }
    public string Status { get; set; } = default!;
    public DateTime CreatedAt { get; set; }
}

while OrderLineArchive promotes nothing at all — public class OrderLineArchive : ArchiveEntry<OrderLine> { } is the entire file. The interceptor's reflective copy (part 2) fills whatever matches by name; anything you don't promote isn't lost, because of the second kind of column.

The dual write

Every archived entity gets its scalars written twice in the same insert: once into the typed columns above, and once as a flat JSON document into ScalarPayload, which the model configuration pins to Postgres's binary JSON type:

b.Property(x => x.ScalarPayload)
 .HasColumnType("jsonb")
 .IsRequired();

Redundant? Completely. Deliberately. The two copies answer different questions on different timescales. The typed columns answer today's questions fast: “archived orders for this customer over £500 last quarter” is an ordinary indexed SQL query, no JSON operators, no casts. The jsonb copy answers tomorrow's questions safely: when Order grows a Channel property next year, rows archived before the migration obviously won't have that column — but they also won't lie about it. And if a property is renamed or dropped from the entity, the typed archive column for it goes stale or disappears with the next migration, while the payload keeps the value under the name it had at the moment of deletion. The archive survives schema drift even for columns you never promoted.

The storage bill for the redundancy is real but modest — jsonb compresses well under TOAST, and archive tables are write-once, read-rarely by construction — while the failure it prevents (an unanswerable question about a deleted row) is the one this whole library exists to rule out.

There's one asymmetry between the copies worth knowing about because you'll notice it the first time you inspect a payload. The typed copy explicitly skips the soft-delete bookkeeping:

if (name is "IsDeleted" or "DeletedAt") continue;

but the serializer doesn't — it dumps every non-shadow tracked scalar, so the payload contains "IsDeleted": true and the DeletedAt timestamp. I've decided I like the accident: the typed columns model “an archive row is the deletion record, a flag would be redundant,” while the payload models “exactly what the tracker held, verbatim.” One is a schema; the other is evidence.

An index that refuses to be a foreign key

Now the oddity that the whole design pivots on. EntityId points at the primary row's id, and the configuration goes out of its way to keep it only a column:

b.HasIndex(x => x.CorrelationId);
b.HasIndex(x => x.EntityId);
b.HasIndex(x => x.ArchivedAt);

if (attr.TtlDays >= 0)
    b.HasIndex(x => x.ExpiresAt);

// EntityId is a plain indexed column, not a real FK —
// archive rows outlive the deleted primary entity.
b.Ignore(x => x.Entity);

A real foreign key from order_archive.EntityId to orders.Id would be self-defeating three different ways. With default behavior, deleting the order cascades away the archive row — the exact failure mode part 2's state-flip dodges, now enforced by the database. With RESTRICT, the archive row blocks the delete. With SET NULL, you keep the row but lose the one value that says what it points at. Every option is wrong, because a foreign key asserts “my target exists” and the entire premise of an archive is that its target doesn't. So EntityId gets an index — “show me every archive of entity X” stays fast — and referential integrity is deliberately not asserted. The Entity navigation property exists on the base class purely so generic constraints line up, and b.Ignore() keeps EF from ever mapping it.

The snapshot link, by contrast, is a real FK, because both ends live in archive-land and can honor it:

b.HasOne<TSnapshot>()
 .WithMany()
 .HasForeignKey(x => x.SnapshotId)
 .IsRequired(false)
 .OnDelete(DeleteBehavior.SetNull);

SET NULL here means a deleted snapshot job degrades the archive row gracefully — it loses its pointer, not its life. That choice has a consequence for TTL cleanup that part 8 will poke at.

One method, every table

None of this is written per entity. The consuming DbContext makes a single call:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.ApplyArchiveConfigurations();
    // ...
}

and the extension walks the registry (part 5), closing a generic method over each triple of entity, archive, and snapshot types:

public static ModelBuilder ApplyArchiveConfigurations(this ModelBuilder builder)
{
    foreach (var entityType in ArchiveRegistry.AllArchivableTypes())
    {
        var archiveType  = ArchiveRegistry.GetArchiveType(entityType);
        var snapshotType = ArchiveRegistry.GetSnapshotType(entityType);
        var attr         = ArchiveRegistry.GetAttribute(entityType);

        ConfigureMethod
            .MakeGenericMethod(entityType, archiveType, snapshotType)
            .Invoke(null, [builder, attr]);
    }
    return builder;
}

MakeGenericMethod at model-building time is the cheap kind of reflection — it runs once at startup, not per save. The generic ConfigureArchive<TEntity, TArchive, TSnapshot> then does everything above plus naming: Order becomes order_archive and order_snapshot via a tiny snake-caser, so the archive tables sit in Postgres looking like they belong there. The migration output is pleasingly boring: order_archive with the five promoted columns, the metadata columns, five indexes, one FK to order_snapshot — generated entirely from a subclass with five properties and an attribute.

Notice also what's conditional: the ExpiresAt index only exists when the attribute sets a TTL. Product (TtlDays = 730) gets one; an entity archived forever doesn't pay for an index it will never range-scan.

The trade I haven't paid off yet

The honest wrinkle is the string "jsonb". It's hard-coded, which welds ArchiveKit.EFCore to Npgsql. On SQL Server this exact configuration won't build a valid model — you'd want nvarchar(max) and, from SQL Server 2025 onward, its native json type. The right shape is a small storage-dialect abstraction (or just an option: options.PayloadColumnType = "nvarchar(max)"), and it's high on the list precisely because everything else in the library is provider-neutral. I wrote for the database I run; the seam where the second database goes is at least a one-liner deep, not a rewrite.

The metadata columns I've been waving past — CorrelationId, IsRoot — are not decoration. They're what turns a pile of independent archive rows into a deleted object graph you can put back together.

Next up: correlation ids, cascade archiving, and how a whole family of rows learns to leave together.