Fisher on SQLite, Part 2: The event store

· 13 min read · fisher sqlite dotnet critter-stack event-sourcing projections
Fisher on SQLite, Part 2: The event store

Series: Fisher on SQLite

  1. 1 Fisher on SQLite, Part 1: The document store
  2. 2 Fisher on SQLite, Part 2: The event store
  3. 3 Fisher on SQLite, Part 3: Backing it up to S3
← Previous Next →

Part 1 toured Fisher as a document database. The other half is a full event store, and it is not bolted on: streams, events, snapshots and projected read models live in the same SQLite file as your documents and commit in the same transaction. No server, no provisioning, no network in the path.

Part 1 stored a StockMovement as a document. Here a sale is a stream: started, lines scanned, a discount applied, tendered, sometimes a line voided or the whole thing refunded. That is a small workflow with money in it, an operator who will swear the screen said something else, and a manager who needs to know what actually happened at 14:32. Event sourcing earns its keep on exactly that.

Every output block below is from a sample project run against Fisher 1.0.4 on .NET 10.

Events and an aggregate

public record SaleStarted(string TerminalId, string Cashier);
public record LineScanned(string SkuCode, int Quantity, decimal UnitPrice);
public record DiscountApplied(string Code, decimal Amount);
public record LineVoided(string SkuCode, string Reason);
public record Tendered(string TerminalId, string Method, decimal Amount, DateTimeOffset At);
public record SaleRefunded(string TerminalId, decimal Amount, string Reason, DateTimeOffset At);

public class Sale
{
    public Guid Id { get; set; }
    public string TerminalId { get; set; } = "";
    public string Cashier { get; set; } = "";
    public int LineCount { get; set; }
    public decimal Total { get; set; }
    public SaleStatus Status { get; set; }

    public void Apply(SaleStarted e)
    {
        TerminalId = e.TerminalId;
        Cashier = e.Cashier;
        Status = SaleStatus.Open;
    }

    public void Apply(LineScanned e)
    {
        LineCount++;
        Total += e.Quantity * e.UnitPrice;
    }

    public void Apply(DiscountApplied e) => Total -= e.Amount;

    public void Apply(LineVoided e) => LineCount--;

    public void Apply(Tendered e) => Status = SaleStatus.Tendered;

    public void Apply(SaleRefunded e)
    {
        Status = SaleStatus.Refunded;
        Total -= e.Amount;
    }
}

Three things about that class will save you a confusing hour:

  • The Apply dispatcher is source-generated, with no runtime fallback. The generator runs in the assembly that defines the aggregate, so that assembly has to reference Fisher. The analyzer ships inside the package, so there is nothing extra to add.
  • A conventional-method projection class must be partial, because the dispatcher is generated into it.
  • An aggregate needs an identity member. The generator keys the dispatcher on (TDoc, TId), and Fisher says so up front rather than failing later with something cryptic.

Starting and appending

await using var session = store.LightweightSession();

// Fisher assigns the id; StartStream hands back a StreamAction
var stream = session.Events.StartStream<Sale>(
    new SaleStarted("till-01", "asha"),
    new LineScanned("ESP-250", 2, 8.50m));

await session.SaveChangesAsync();
var saleId = stream.Id;

Nothing is written until SaveChangesAsync. When it is, the events land in the same transaction as any documents you stored, patches you queued, raw SQL you enlisted through QueueSqlCommand, and inline projection writes those events triggered. On a store that lives in one file, “the sale and the stock movement, or neither” is the default rather than an ambition.

Appending is what you expect:

session.Events.Append(saleId, new LineVoided("ESP-250", "scanned twice"));

Underneath is QuickAppend: direct INSERT statements, no stored procedures. Versions are assigned client-side from the stream’s current version, read inside the write transaction under BEGIN IMMEDIATE, and the sequence numbers SQLite assigned come back through a trailing SELECT over the stream and version range. Marten needs a bulk function for that, Polecat OUTPUT … INTO.

Three ways to read it back

// live: replay the stream every time
var live = await session.Events.AggregateStreamAsync<Sale>(saleId);

// the command shape: fetch, decide, append
var stream = await session.Events.FetchForWriting<Sale>(saleId);
if (stream.Aggregate is { Status: SaleStatus.Open })
{
    stream.AppendOne(new Tendered("till-01", "card", stream.Aggregate.Total, DateTimeOffset.UtcNow));
}
await session.SaveChangesAsync();

// a stored snapshot, kept current by a projection
var stored = await session.LoadAsync<Sale>(saleId);
[live] Open, 1 lines, total 17.00
[inline] loaded as a document: Open, total 17.00
[fetch-for-writing] now Tendered, version 3

FetchForWriting folds the stream on every call whatever the aggregate’s lifecycle. That differs from FetchLatest, which reads an inline aggregate’s projected document, and the two answer different questions: FetchLatest reports current state, while FetchForWriting is the read half of a read-modify-write whose guard is the stream version, so it has to fold the stream that version describes.

Concurrency, and the one real divergence

Optimistic appends take an expected version, and a loser throws at commit:

session.Events.Append(saleId, expectedVersion: 2, new LineVoided("ESP-250", "scanned twice"));
await session.SaveChangesAsync();
[optimistic] stale expectedVersion rejected: EventStreamUnexpectedMaxEventIdException

So far, so Marten. Now the sharpest behavioural difference in the library:

AppendExclusive, FetchForExclusiveWriting and WriteExclusivelyToAggregate are optimistic methods on Fisher. On Marten and Polecat a competing session waits its turn. Here it fails.

Two sessions fetch the same sale for exclusive writing, both apply a discount, and the second commits:

var a = await one.Events.FetchForExclusiveWriting<Sale>(saleId);
var b = await two.Events.FetchForExclusiveWriting<Sale>(saleId);

a.AppendOne(new DiscountApplied("STAFF10", 1.70m));
await one.SaveChangesAsync();

b.AppendOne(new DiscountApplied("LOYALTY", 0.85m));
await two.SaveChangesAsync();     // <-- this one
[exclusive] the second writer failed with EventStreamUnexpectedMaxEventIdException

Marten takes an advisory lock, Polecat an UPDLOCK, HOLDLOCK row lock. SQLite has neither, and one writer per file means the equivalent would hold a BEGIN IMMEDIATE open from the fetch to SaveChangesAsync, blocking every other writer in the process for as long as the caller holds the session. For an embedded database that is a worse trade than failing.

The safety property is unchanged. The version guard still runs inside the write transaction, so there is no lost update either way, and no discount is applied twice. What changes is your error handling: code ported from Marten that relied on waiting needs a retry around the exception. If you already write DCB-style handlers with an explicit ConcurrencyException retry cycle, this will feel familiar.

Projections

A snapshot is registered with a lifecycle, one line each:

opts.Projections.Snapshot<Sale>(SnapshotLifecycle.Inline);   // same transaction
opts.Projections.Snapshot<Sale>(SnapshotLifecycle.Async);    // background daemon

Those are the only two values SnapshotLifecycle carries. Storing nothing is the third option and needs no registration at all: AggregateStreamAsync folds the stream on demand, which is the [live] line above. The neighbouring ProjectionLifecycle enum, the one Projections.Add takes, does have Inline, Async and Live.

An inline projection is applied inside the same transaction as the append that triggered it, which is why the sample could LoadAsync<Sale> immediately after SaveChangesAsync and get current state. No daemon to run, no staleness to reason about. For a receipt you are about to print, that is the lifecycle you want.

Inline has a cost specific to this engine: everything it does happens while holding the file’s one write lock. A single-stream projection is cheap, one document write per append. A multi-stream projection is not, because unrelated sales end up writing the same row, contending with each other on top of contending for the lock. Async is usually the better lifecycle for those.

Here is one, the day’s takings per terminal:

public partial class DailyTakings : MultiStreamProjection<Takings, string>
{
    public DailyTakings()
    {
        Identity<Tendered>(e => $"{e.TerminalId}:{e.At:yyyy-MM-dd}");
    }

    public void Apply(Tendered e, Takings view)
    {
        view.Total += e.Amount;
        view.Sales++;
    }
}
opts.Projections.Add(new DailyTakings(), ProjectionLifecycle.Async);

Grouping has three shapes: Identity<T> for one document, Identities<T> for several, FanOut<T, TChild> to explode a collection member into one event per element. CustomGrouping covers what they cannot express.

The async daemon

In a host, opt in alongside the store:

builder.Services.AddFisher(opts => { /* … */ })
    .ApplyAllDatabaseChangesOnStartup()
    .AddAsyncDaemon(DaemonMode.Solo);

Or build one by hand, which is what the sample does:

var daemon = await store.BuildProjectionDaemonAsync();
await daemon.StartAllAsync();
await daemon.WaitForNonStaleData(TimeSpan.FromSeconds(20));

After a day of trading across three terminals:

[async] takings: till-01:2026-08-28=202.50, till-02:2026-08-28=193.60, till-03:2026-08-28=177.70

Solo is not a default papering over something. HotCold is refused, and the refusal says why:

[hotcold] NotSupportedException: Fisher has no hot-cold daemon coordination. Leadership failover
requires several nodes sharing one database, and a SQLite store is a file — SQLite does not make one
safe to share across nodes. Use DaemonMode.Solo, or DaemonMode.ExternallyManaged if something else
runs the projections.

Accepting HotCold and running Solo underneath would give an application the exact opposite of the guarantee it asked for: every node projecting at once.

WAL matters here more than anywhere else in the library, because WAL is what lets the daemon read while a session writes. It is on by default. Turn it off and Fisher logs a warning at daemon startup rather than refusing to run, because a non-WAL store still projects correctly, it just serialises the daemon against every writer and presents as a mysteriously slow projection rather than a misconfiguration.

Where the single writer pays you back

Marten and Polecat both distinguish the highest sequence number issued from the highest safe to read, because a Postgres sequence or a SQL Server IDENTITY hands out numbers outside the transaction. A writer can sit on an uncommitted 7 while 8 commits ahead of it, so the daemon needs gap detection before it can advance.

On SQLite, one writer per file plus BEGIN IMMEDIATE means a transaction’s sequence numbers commit before the next writer allocates any. Committed sequences are contiguous, so the high-water mark is max(seq_id):

[storage] fi_events rows=124 seq 1..124
[storage] sequence contiguous: 1
[storage] progression rows: HighWaterMark, Takings:All

124 rows, sequence 1 through 124, no holes. A whole category of daemon machinery has nothing to do here. The constraint that costs write throughput buys back correctness elsewhere, which is the honest shape of the trade.

The tables:

CREATE TABLE IF NOT EXISTS fi_streams (
    id             TEXT       PRIMARY KEY,
    type           TEXT       ,
    version        INTEGER    NOT NULL DEFAULT 0,
    timestamp      TEXT       NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    created        TEXT       NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    tenant_id      TEXT       NOT NULL DEFAULT '*DEFAULT*',
    is_archived    INTEGER    NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS fi_events (
    seq_id         INTEGER    PRIMARY KEY AUTOINCREMENT,
    id             TEXT       NOT NULL,
    stream_id      TEXT       NOT NULL,
    version        INTEGER    NOT NULL,
    data           TEXT       NOT NULL,
    data_binary    BLOB       ,
    type           TEXT       NOT NULL,
    timestamp      TEXT       NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    tenant_id      TEXT       NOT NULL DEFAULT '*DEFAULT*',
    dotnet_type    TEXT       ,
    is_archived    INTEGER    NOT NULL DEFAULT 0
);

data_binary is a seam rather than a feature: an event body can be a BLOB instead of JSON text, opted into per event type, for when a format decision matters more than readability. Fisher ships no binary serializer, because choosing MessagePack or protobuf is your application’s decision about how its data ages.

Running the daemon in production

One node. Solo is not a soft default, it is the only safe mode: two processes projecting the same file both believe they own the high-water mark. If head office runs a reporting service beside the till software, exactly one of them owns the daemon, which is what DaemonMode.ExternallyManaged is for.

Watch the mark, not the process. A shop that is closed advances nothing, so “the mark has not moved” cannot distinguish quiet from dead. The high-water agent re-stamps its row on idle cycles, and the health check reads that poll-cycle age:

opts.Events.HighWaterLivenessInterval = TimeSpan.FromSeconds(5);

services.AddHealthChecks()
    .AddFisherHighWaterHealthCheck(staleThreshold: TimeSpan.FromSeconds(30), minimumGap: 1);

Keep staleThreshold comfortably above the liveness interval or a healthy agent reports unhealthy between two of its own touches. This check is also how you find out that somebody turned WAL off, since the daemon only warns about that.

Poison events. By default a failing Apply stops the shard. opts.Projections.Errors.SkipApplyErrors = true quarantines the event into fi_dead_letters instead and keeps going, and the dead letter is written on its own connection because the batch recording it is about to roll back.

Rebuilds can empty a read model you did not name. Teardown clears the whole of every table the named projection publishes into. Two projections publishing the same document type share one fi_doc_* table, so rebuilding one deletes both, then replays only the one you asked for:

[rebuild] after the first run: refunds:till-01=40.00, takings:till-01=100.00
[rebuild] rebuilding TakingsByTerminal...
[rebuild] after rebuilding TakingsByTerminal: takings:till-01=100.00

The rebuild succeeded. The refund ledger is silently gone, and nothing in the system knows it should be there. Rebuilding it afterwards does not fix it either, because that clears the shared table again and discards what the first rebuild wrote. Rewind instead, which replays onto the rows already there:

await daemon.RewindSubscriptionAsync("RefundsByTerminal", token, sequenceFloor: 0);
[rebuild] after rewinding RefundsByTerminal: refunds:till-01=40.00, takings:till-01=100.00

Marten behaves the same way. Fisher at least warns at rebuild time, naming the table and the other projections that publish into it.

Archiving, and actually removing things

session.Events.ArchiveStream(saleId);
await session.SaveChangesAsync();
[archive] is_archived = 1
[archive] events still readable: 4

Archiving flips a flag on fi_streams. The events stay, remain visible to stream reads and to the daemon, and the stream drops out of natural key lookups and the explorer’s recent-streams read. Fisher does this with a direct operation rather than by appending an Archived event, which is why its natural key tables carry no archived flag of their own: nothing is watching for anything, so there is nothing to keep in sync or repopulate after a rebuild.

TombstoneStream is the neighbouring move for a stream created in error, whose events should not be interpreted at all.

Neither is deletion. If you want events gone, the options are stream compacting, which collapses a stream’s history into one snapshot event, and data masking, which rewrites bodies in place. Both matter for the reason they do on Marten: a customer will eventually ask you to erase their card details and their name from an append-only log, and “append-only” is not an answer that survives a privacy regulator.

What else is in the box

Not exercised by the sample above, but worth knowing exists:

  • Subscriptions, for running your own code over the event stream through the same daemon machinery. The obvious use here is pushing the day’s sales to head office when connectivity returns.
  • DCB tags, so a command can guard a rule spanning several streams instead of forcing everything into one aggregate. I have written about DCB in Marten at length; the API is the same one.
  • Natural keys, for addressing a stream by a receipt number rather than its Guid.
  • Flat table projections, writing ordinary relational rows for reporting, cheap inline since each is one upsert.
  • EF Core integration, where a DbContext writes inside Fisher’s transaction.
  • Projection side effects, the seam a bus integration hooks. There is no message bus in Fisher and there will not be one.

Next

Every sale’s history, the read models built from it, and the daemon’s progress table live in one file you can copy, hand to a colleague, or restore onto a replacement terminal. Which is the setup for Part 3, and for the sentence that started this series: backup is cp. True right up until your application is running, and the way it stops being true is silent.

Fisher is on NuGet, documented at fisher.jasperfx.net, and the source is at github.com/JasperFX/fisher.

Series: Fisher on SQLite

  1. 1 Fisher on SQLite, Part 1: The document store
  2. 2 Fisher on SQLite, Part 2: The event store
  3. 3 Fisher on SQLite, Part 3: Backing it up to S3
← Previous Next →
#fisher #sqlite #dotnet #critter-stack #event-sourcing #projections