Skip to content
Cratis

Indexing Read Models

Declare indexes on a Chronicle read model with [Index] or [CompositeIndex] when your queries need indexed properties beyond its key. Chronicle recreates supported indexes when replay replaces the collection or table, so you do not have to reapply them by hand.

Choose the properties and order to match the queries you run against storage. The declaration travels with the read model; the sink, Chronicle’s storage writer, decides how to implement it. MongoDB supports full property paths. SQLite, PostgreSQL, and SQL Server support non-unique indexes on top-level scalar columns and nested JSON scalar paths, with the differences described under SQL storage.

Declaring an index

Section titled “Declaring an index”

Put [Index] on the property — or, for a record, on the constructor parameter:

using Cratis.Chronicle.ReadModels;


[ReadModel]
public record ReadModelsIndexingOrder(
    Guid Id,
    [Index] Guid CustomerId,
    [Index] string Number,
    decimal Total);

View C# snippet source on GitHub

The key of a read model does not need [Index] — the store already indexes it.

The example indexes CustomerId and Number separately. A bare [Index] on a positional record parameter targets the parameter; [property: Index] targets the generated property. Chronicle reads both locations, so either form produces the same declaration, as it does for [Subject].

Nested and child properties

Section titled “Nested and child properties”

Indexes are collected by walking the read model, so a property on a nested object or on a child collection’s element type is indexed at its full path:

using Cratis.Chronicle.ReadModels;


public record ReadModelsIndexingOrderLine(
    [Index] Guid ProductId,
    int Quantity);


[ReadModel]
public record ReadModelsIndexingOrderWithLines(
    Guid Id,
    IEnumerable<ReadModelsIndexingOrderLine> Lines);

View C# snippet source on GitHub

Here, [Index] on ProductId in the line-item type declares lines.productId on the order. The path follows the client’s serialization naming policy, so the index uses the stored property names.

Composite and descending indexes

Section titled “Composite and descending indexes”

Use [CompositeIndex] on a read model to declare an ordered set of property names or dotted paths:

using Cratis.Chronicle.ReadModels;


[ReadModel]
[CompositeIndex(nameof(CustomerId), nameof(CreatedAt), IsDescending = new[] { false, true })]
public record ReadModelsIndexingRecentOrders(
    Guid Id,
    Guid CustomerId,
    DateTimeOffset CreatedAt);

View C# snippet source on GitHub

This example puts CustomerId first and CreatedAt second, with creation time descending. The property order defines the index key order. IsDescending supplies one flag per property; omitting it or using an empty array makes every key ascending. A read model can declare multiple composite indexes. Chronicle resolves each path against the .NET model and applies the serialization naming policy, including paths through nested objects and collection elements.

Use [Index(IsDescending = true)] for a descending single-property index, on either a property or a record parameter:

using Cratis.Chronicle.ReadModels;


[ReadModel]
public record ReadModelsIndexingLatestOrders(
    Guid Id,
    [Index(IsDescending = true)] DateTimeOffset CreatedAt);

View C# snippet source on GitHub

Registration throws InvalidIndexDeclaration before sending definitions to the kernel for an empty composite, an unknown property, repeated properties within a composite, a direction array whose length does not match the properties, or a composite containing more than one distinct array scope. Multiple keys from the same array, such as lines.sku and lines.qty, are supported by MongoDB; parallel arrays are not. Repeated identical declarations coalesce into one index. SQL sinks warn and skip a composite containing an array path. Unique indexes are not part of these attributes; use event constraints to enforce uniqueness.

The definition retains its leading property as the legacy path. Older kernels can therefore create an ascending index on that property, but need an upgrade to honor the complete order and directions. Workbench schema edits carry the full structured definitions; older flat-path edits preserve existing composite and descending indexes instead of flattening them. A structured empty set removes all indexes, while an empty legacy path list continues to preserve composite and descending declarations.

SQL storage

Section titled “SQL storage”

SQL stores nested objects and arrays in JSON columns. A nested scalar declaration uses an expression index on SQLite (json_extract) or PostgreSQL (#>>). PostgreSQL casts integer and number leaves to numeric, preserving unsigned and large JSON integers without an Int64 range limit, and boolean leaves to boolean. SQL Server indexes a non-persisted computed column using CAST(JSON_VALUE(...) AS nvarchar(450)) for every scalar kind. SQL Server therefore orders nested values lexically as text, not numerically or as booleans. These columns do not store a second copy of your read-model data; adding them changes metadata only. The index itself still requires a build.

Declaration SQLite PostgreSQL SQL Server
Top-level scalar Column index Column index Column index
Nested scalar Expression index Typed expression index Computed-column index
Path through an array Warning; not created GIN with jsonb_path_ops on the containing JSON column Warning; requires SQL Server 2025 JSON indexes
Object without a scalar leaf Warning; not created Warning; not created Warning; not created

GIN indexes support JSON containment and JSONPath predicates, not arbitrary scalar comparisons. They have no ascending or descending key order. PostgreSQL ignores an array declaration’s direction with a warning and creates the same GIN index for either direction; SQLite and SQL Server still warn and skip array declarations. Directions apply to scalar indexes. Queries must use the indexed expression and compatible casts for the database to use an expression index. Chronicle does not rewrite your application queries. For SQL Server, a matching nested numeric predicate is CAST(JSON_VALUE([details], '$."total"') AS nvarchar(450)) = N'42.5'. Compare text to text: a numeric literal forces implicit conversion and is not an equivalent indexed predicate. Ordering is lexical ('10' precedes '2'), booleans are text, and comparisons use the 450-character bounded value rather than the full JSON value. Expression matching, compatible types and collation, and the optimizer’s cost estimate determine whether the index is used; declaring an index does not guarantee a seek.

SQL Server requires compatible session options for writes to read models with nested indexes. Chronicle uses ARITHABORT and ANSI_WARNINGS ON. A connection that sets either ARITHABORT or ANSI_WARNINGS OFF (a non-default configuration) fails inserts and updates against those indexed computed columns. Keep both options enabled on connections that write these read models.

SQL Server bounds newly projected indexed strings to a supported key size; existing unbounded columns are not resized during activation. A declaration on an unbounded column produces a warning and requires a replay to apply the bound. The combined nonclustered key must fit in 1700 bytes. All nested scalar keys use nvarchar(450), truncating index values beyond 450 characters without changing the stored JSON, including exponent-form numbers. Each nested key consumes a 900-byte budget. SQL Server skips a composite whose combined key exceeds 1700 bytes and logs a warning. An unsupported computed expression fails visibly in the kernel log; Chronicle does not fall back to persisted computed columns or alter the stored values.

A composite SQL index is created only when every path is supported; arrays cannot be mixed into a composite. MongoDB supports compound keys sharing one array but rejects keys spanning distinct array scopes. Legacy flat-path updates preserve existing ordered definitions rather than flattening or deleting them.

Index names are deterministic and stay within provider identifier limits. Single ascending scalar index names remain unchanged. Changes to property order, direction, or expression produce a different name. Replay promotion preserves the complete index definition, including expressions and directions; SQLite rebuilds indexes inside the swap transaction because it cannot rename them.

Routine activation adds missing indexes without pruning existing ones. An explicit definition reconciliation removes undeclared Chronicle-owned indexes and unused Chronicle-owned computed columns, but preserves user indexes and computed columns still used by an index.

Why it lives on the read model

Section titled “Why it lives on the read model”

During replay, Chronicle builds a separate collection or table, called a shadow container, then swaps it in when replay completes. Only indexes created on that replacement are available afterward. A manually created index on the old collection does not survive the swap.

Keeping declarations on the read model gives Chronicle the information to recreate supported indexes on both the original storage and its replacement. MongoDB implements them as ordered single-property or compound indexes; a single ascending index is named chronicle_idx_<path>. SQL providers implement the same declarations using the column and expression indexes described above.

When it takes effect

Section titled “When it takes effect”

Indexes are ensured when Chronicle sets up storage for the read model — when the sink is first built for it, and when a replay begins filling its shadow container. Creating an index that already exists is skipped, so this is safe to run repeatedly.

Adding [Index] to a read model that is already in production applies on the next start of the kernel. It does not require a replay — the index is created against the existing container.

What it does not do

Section titled “What it does not do”

[Index] creates storage indexes; it does not add query methods to Chronicle’s keyed read model API or guarantee that a database uses an index. To search by properties other than the key, follow Querying Read Models and write queries suited to your storage provider and indexes.

Community: Discord