Natural Keys

Natural keys let you look up an event stream by a domain-meaningful identifier (like an order number or invoice code) instead of by its internal stream id. Polecat maintains a separate lookup table that maps natural key values to stream ids, so you can use FetchForWriting and FetchLatest with your natural key in a single database round-trip.

When to Use Natural Keys

Use natural keys when:

• External systems or users reference aggregates by a business identifier (e.g., "ORD-12345") rather than a Guid stream id
• You need to look up streams by a human-readable identifier without maintaining your own separate index
• Your aggregate has a stable "business key" that may occasionally change (natural keys support mutation)

Declaring Natural Keys

Mark a property on your aggregate with [NaturalKey], and mark the methods that set or change the key value with [NaturalKeySource]:

public record OrderNumber(string Value);

public class OrderAggregate
{
    public Guid Id { get; set; }

    [NaturalKey]
    public OrderNumber OrderNum { get; set; } = null!;

    public decimal TotalAmount { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public bool IsComplete { get; set; }

    [NaturalKeySource]
    public void Apply(NkOrderCreated e)
    {
        OrderNum = e.OrderNumber;
        CustomerName = e.CustomerName;
    }

    public void Apply(NkOrderItemAdded e)
    {
        TotalAmount += e.Price;
    }

    [NaturalKeySource]
    public void Apply(NkOrderNumberChanged e)
    {
        OrderNum = e.NewOrderNumber;
    }

    public void Apply(NkOrderCompleted e)
    {
        IsComplete = true;
    }
}

public class InvoiceAggregate
{
    public Guid Id { get; set; }

    [NaturalKey]
    public string InvoiceCode { get; set; } = string.Empty;

    public decimal Amount { get; set; }

    [NaturalKeySource]
    public void Apply(NkInvoiceCreated e)
    {
        InvoiceCode = e.Code;
        Amount = e.Amount;
    }
}

public record NkOrderCreated(OrderNumber OrderNumber, string CustomerName);
public record NkOrderItemAdded(string ItemName, decimal Price);
public record NkOrderNumberChanged(OrderNumber NewOrderNumber);
public record NkOrderCompleted;
public record NkInvoiceCreated(string Code, decimal Amount);

^{snippet source | anchor}

The [NaturalKeySource] attribute tells Polecat which Create / Apply methods produce or change the natural key value. Polecat uses this information to keep the lookup table in sync whenever events are appended.

Event-to-Key Mappings

Every event type that sets or changes the natural key must be declared through the [NaturalKeySource] attribute. When Polecat processes events during an append operation, it extracts the key value from these mapped events and writes it to the lookup table.

Events that do not affect the natural key (like NkOrderItemAdded in the example above) do not need any mapping.

Storage

Polecat automatically creates and manages a lookup table (prefixed with pc_) for each aggregate type that has a natural key configured. The table maps natural key values to stream ids and is:

• Created automatically during schema migrations
• Updated transactionally alongside event appends using MERGE statements
• Archive-aware via an is_archived bit column (archived streams are excluded from lookups)

You do not need to create or manage this table yourself.

INFO

Unlike Marten's PostgreSQL-based implementation which uses table partitioning for multi-tenancy, Polecat's SQL Server implementation uses an is_archived bit column and does not use partitioning for the natural key lookup table.

FetchForWriting by Natural Key

The primary use case for natural keys is looking up a stream for writing without knowing its stream id:

// FetchForWriting by the business identifier instead of stream id
var stream = await session2.Events.FetchForWriting<OrderAggregate, OrderNumber>(orderNumber);

stream.Aggregate.ShouldNotBeNull();
stream.Aggregate!.OrderNum.ShouldBe(orderNumber);

// Append new events through the stream
stream.AppendOne(new NkOrderItemAdded("Gadget", 19.99m));
await session2.SaveChangesAsync();

^{snippet source | anchor}

This resolves the natural key to a stream id and fetches the aggregate in a single database round-trip. Polecat uses WITH (UPDLOCK, HOLDLOCK) hints to ensure safe concurrent access during the lookup.

FetchLatest by Natural Key

For read-only access, you can use FetchLatest with a natural key:

// Read-only access by natural key
var aggregate = await session2.Events.FetchLatest<OrderAggregate, OrderNumber>(orderNumber);

^{snippet source | anchor}

Mutability

Natural keys can change over the lifetime of a stream. When an event mapped with [NaturalKeySource] is appended, Polecat updates the lookup table with the new value using a MERGE statement. The old key value is replaced, so lookups using the previous key will no longer resolve to that stream.

Null and Default Keys

If a mapped event produces a null or default key value, Polecat silently skips writing to the lookup table. This means streams where the natural key has not yet been assigned will not appear in natural key lookups, but will still be accessible by stream id.

Clean and Maintenance Operations

The natural key lookup table is maintained automatically as part of normal event appending. If you need to rebuild the lookup table (for example, after a data migration), you can do so through Polecat's schema management tools as part of a projection rebuild.

Testing Considerations

When writing integration tests:

• Natural key lookups work against the same session's uncommitted data, so you can append events and look up by natural key within the same unit of work
• If you are using FetchForWriting with a natural key that does not exist, the behavior is the same as with a stream id that does not exist

Integration with Wolverine

Natural keys integrate with Wolverine's aggregate handler workflow. See the Wolverine documentation on natural keys with Polecat for details on how Wolverine resolves natural keys from command properties.