Event Ordering Invariant¶

Verified by tests

SlidingWindowOutboxBatchStrategyTests, SlidingWindowInboxBatchStrategyTests, SlidingWindowApplyBatchStrategyTests, GetStreamEventsClaimSlice25Tests, BaseUpsertStrategyInPlaceUpdateTests — library CI run #37346231411 (2026-10-05)

Invariant. Every batch boundary in Whizbang's pump-then-process pipeline must deliver output sorted by event_id (UUIDv7) ascending. Cross-batch ordering is the responsibility of the layer that allocates event_ids; within each batch, the holding layer (sliding window, SQL function, drain fetch) must sort before passing the batch downstream.

Why this exists¶

event_id is a UUIDv7 generated at the point of Dispatcher.PublishAsync(...). UUIDv7 is lexicographically ordered by emission time — but only with respect to a single allocator. Two race conditions can produce non-monotonic event_ids on the same stream:

Parallel emission inside one process. A receptor running on N threads (Parallel.ForEachAsync, concurrent saga handlers) calls TrackedGuid.New() from each thread. The generator issues every id under one lock, so ids are in issue order across threads (see How New() orders ids); what the threads cannot control is which of them reaches the lock first, so the ids follow issue order, not the order the threads' work logically happened in.
Cross-process emission into one saga stream. A saga stream aggregates events emitted by multiple services. Each service is internally monotonic but no two services share a clock or counter. Same-millisecond emissions from different processes interleave at wh_event_store in commit order, not in event_id lex order.
Derived ids that drop the allocator's order. An event a handler returns does not get a freshly issued id: its id is derived from the command that was handled, so a retry reproduces it (see Emission identity). A derivation is an allocator too, and it must keep the order of what it derives from. It keeps the source id's millisecond and the generator's counter, then the emission's ordinal. An earlier derivation kept the millisecond only, so events of two commands issued in the same millisecond sorted at random and could be versioned the wrong way round on their stream.

The downstream cost when ordering breaks: the perspective apply cursor uses event_id as its "last applied" marker. An event arriving after the cursor advanced past a higher event_id triggers Cursor inversion detected → rewind → full replay. If the replay path doesn't upsert, every replay multiplies into 23505 unique-constraint violations.

The sliding windows exist for this reason — they coalesce same-stream emissions over a brief time window so that downstream sees one canonical, sorted batch.

Where the invariant is enforced¶

Every layer that holds events in a batch sorts by event_id before passing the batch on.

1. `SlidingWindowOutboxBatchStrategy._drainBufferAsync`¶

Per-stream-keyed sliding window. On flush, each per-stream batch is sorted by MessageId (= event_id) before the bulk-flush callback fires.

Code: src/Whizbang.Core/Workers/SlidingWindowOutboxBatchStrategy.cs

Test: SlidingWindowOutboxBatchStrategyTests.AppendAsync_SameStream_SingleBatchSortedByMessageIdAsync — appends [m3, m1, m2]; asserts flushed batch is [m1, m2, m3].

2. `SlidingWindowInboxBatchStrategy._drainBufferAsync` (slice 23)¶

Per-stream-keyed sliding window (since slice 23) — same shape as the outbox strategy. Each stream_id has its own bounded channel + drain task + sliding-window batcher + idle eviction. On flush, the per-stream batch is sorted by MessageId ASC.

Why per-stream (not global): cross-service fan-in. Saga aggregates (e.g. BulkImportOrchestration, Order) receive events from multiple concurrent producers. The pre-slice-23 global channel flushed everything in a 50 ms window — two transport messages for the same stream arriving more than 50 ms apart landed in different flush batches. Each batch was internally sorted but cross-batch ordering was arrival order, which on a fan-in stream is guaranteed to deviate from MessageId order. Result on a large bulk-import smoke test: thousands of Cursor inversion detected warnings per run.

With per-stream buffers + 300 ms / 3 s default window, same-stream messages from multiple producers coalesce within the window; the sort runs over the merged per-stream batch; events flush in MessageId order even when arrival was out of order. Different streams remain fully parallel. InboxMessage.StreamId == null (broadcast-style) routes to a default Guid.Empty buffer.

Code: src/Whizbang.Core/Workers/SlidingWindowInboxBatchStrategy.cs

Tests: - SlidingWindowInboxBatchStrategyTests.AppendAsync_OutOfOrderArrivals_FlushedSortedByMessageIdAsync — appends [m3, m1, m2]; asserts flushed batch is [m1, m2, m3]. - SlidingWindowInboxBatchStrategyTests.AppendAsync_SameStream_CrossWindowArrivals_CoalesceInOneFlushAsync — locks the cross-batch coalesce invariant. - SlidingWindowInboxBatchStrategyTests.AppendAsync_DifferentStreams_FlushIndependentBatchesAsync — different streams remain isolated. - SlidingWindowInboxBatchStrategyTests.AppendAsync_NullStreamId_RoutesToDefaultBufferAsync — broadcast routing.

3. `_emit_event_store_chain` SQL¶

Belt-and-suspenders: the SQL function that fans the C# batch into wh_event_store rows assigns per-stream versions via ROW_NUMBER() OVER (PARTITION BY stream_id ORDER BY message_id) — version assignment matches canonical event_id order even if a caller forgot to pre-sort. The input array in store_outbox_messages is additionally sorted ORDER BY stream_id NULLS FIRST, message_id so wh_active_streams row locks are acquired in canonical order (deadlock prevention) with message_id as the deterministic tiebreaker.

Code: _emit_event_store_chain / _emit_event_store_chain_for_inbox, defined in 029_ProcessWorkBatch.sql and redefined by several later migrations (most recently 087_StreamDigests.sql); every redefinition preserves the Phase H step 10 slice 1 ORDER BY message_id version assignment.

4. `InboxDispatchWorker` stream-affinity partitioning¶

The slice-14 partition fan-out in InboxDispatchWorker spawns N internal queues + N consumer tasks with stream-affinity hash partitioning: all work for a given stream routes to the same partition queue, so a stream's events are never processed concurrently by two partition consumers. In-order arrival at the partition is guaranteed upstream — the per-stream inbox sliding window flushes sorted batches (layer 2), and the drain fetch SQL (fetch_inbox_batch, migration 040) returns rows ORDER BY stream_id, message_id.

5. `PerspectiveWorker` drain fetch¶

The perspective fetch SQL orders every projection:

fetch_pending_perspective_events (migration 042) — ORDER BY event_id ASC
fetch_events_by_ids (migration 043) — ORDER BY event_id ASC
get_stream_events (migration 038) — ORDER BY stream_id, commit_sequence ASC NULLS LAST, event_id (commit order preferred, event_id fallback/tiebreak)

The runner template applies events in the order it receives them.

Symptoms of a missing sort¶

If a new touchpoint is added that batches events without sorting, a bulk-import load test produces the following pattern:

[WRN] Cursor inversion detected: pending event "<lex-lower>" ≤ cached cursor "<lex-higher>" — many hundreds per minute on saga streams.
[WRN] No qualifying snapshot found ... performing full replay — every inversion triggers one.
[ERR] 23505: duplicate key value violates unique constraint "wh_per_*_pkey" — many per replay (one per pre-existing perspective row that the replay re-inserts).
Perspective backlog grows faster than it drains; UI freshness collapses.

The production-grade regression test is a bulk-import smoke run (a large number of jobs, tens of thousands of events). A clean run produces zero Cursor inversion detected log entries; any non-zero count points at a touchpoint that lost the sort.

What this invariant does not guarantee¶

Cross-batch ordering. If batch A flushes with the only event for stream S, then batch B flushes with a later event for stream S, and B was committed before A despite being emitted later — wh_event_store sees B before A and assigns version = N to B, N+1 to A. That's a separate concern handled by the rewind path (IPerspectiveRunner.RewindAndRunAsync) and the idempotent upsert on perspective tables (see slice 19 below).
Cross-process global monotonicity. No layer in this pipeline coordinates UUID allocation across services. Cross-process inversions on saga streams are tolerated via the rewind + upsert paths, not prevented.

Companion invariant: idempotent perspective upsert (slice 19)¶

Even with every batch boundary sorted, cross-batch and cross-process scenarios can still trigger the rewind path. When that fires, the runner re-applies the stream's full history into perspective tables. The apply target — BaseUpsertStrategy — must therefore be idempotent: re-applying an event whose perspective row already exists must succeed, not throw PostgreSQL 23505.

Updated

The upsert now has two paths. Path 1 (slice 21, preferred): an atomic raw-SQL INSERT … ON CONFLICT (id) DO UPDATE, attempted first on Npgsql. It serializes the document with the serialization registry's persistence profile, which each assembly's generated PerspectivePersistenceJsonContext joins from its own module initializer, so no startup hook has to run and no process-wide slot decides the path; this structurally eliminates the 23505 dup-key storm. Fallback (slice 19): the EF SELECT-then-INSERT/UPDATE pattern wrapped in a bounded retry loop, used when Path 1 does not apply: a provider other than Npgsql, a table or column name that is not a plain identifier, a type the persistence union cannot resolve, or a value Npgsql cannot bind.

The slice-19 fallback retry loop:

On DbUpdateException carrying 23505, the change tracker is cleared and the inner call retries.
After MAX_DUPLICATE_KEY_RETRIES = 3 attempts the exception propagates — the failed work is routed to the failure channel.
BaseUpsertStrategy.DuplicateKeyRetriesRecovered exposes a process-wide counter for observability; a non-zero value under load is expected, growth in step with traffic is the canary signal.

Code: src/Whizbang.Data.EFCore.Postgres/BaseUpsertStrategy.cs

Tests: BaseUpsertStrategyInPlaceUpdateTests.cs covers the in-place update path.

Known follow-up (fallback path only): EF Core logs each 23505 conflict at [ERR] level before the retry can catch and recover. The retry is correct (data integrity preserved) but the log is noisy under high contention on the fallback path.

Post-slice-18 residual: cursor-advances-past-orphaned-rows race (slice 25)¶

After slice 23 narrowed the cross-batch ordering window, production still produced residual inversions with multi-second MessageId deltas. Investigation found the source was not a missing sort — it was an atomicity gap in the perspective worker's fetch path.

The pre-slice-25 get_stream_events SQL filtered by instance_id = p_instance_id AND lease_expiry > p_now. Rows whose instance_id was NULL (orphaned at insert), or whose lease had expired, were invisible to the fetch. The worker would:

Fetch and apply only the rows currently leased to this instance.
Advance its cursor through those events' MessageIds.
claim_orphaned_perspective_events would later claim the orphaned rows.
The next fetch would surface them — now behind the cursor — triggering a rewind.

Slice 25 fix. get_stream_events now performs UPDATE (claim every eligible row for the requested streams) and SELECT (return everything now leased) in one PL/pgSQL function, sharing one MVCC snapshot:

Eligible rows: unprocessed AND (orphaned OR expired-lease OR already ours).
attempts increments only on lease takeover (instance change); same-instance re-lease doesn't inflate the counter.
Caller invariant after the call: no unprocessed rows for the requested streams exist unleased to anyone else.

Code: src/Whizbang.Data.Postgres/Migrations/038_GetStreamEvents.sql

Tests: GetStreamEventsClaimSlice25Tests — orphan-row claim, expired-lease reclaim, other-instance-valid-lease NOT poached, attempts bumps only on takeover.

Companion API: IWorkCoordinator.ClaimAndFetchPendingPerspectiveEventsAsync (per-stream-per-perspective variant) for callers that want the same atomic claim+fetch semantics scoped to one (stream_id, perspective_name) pair instead of multi-stream batches.

Updated

Two later migrations refine get_stream_events eligibility on top of the slice-25 claim+fetch:

058 (unstamped gate): rows whose commit_sequence is still NULL are neither claimed nor returned — closing the stamper-lag inversion window — but only within a grace window; rows pending longer than the grace flow again NULLS-LAST, so a lagging/absent stamper degrades to pre-058 behavior instead of stalling the drain.
059 (ownership gate): a row whose stream is owned by a different, live instance is not claimable even if its row lease expired — enforcing single-writer-per-stream in the live drain. Unowned streams and streams whose owner is dead still fail over.

Cheap rewinds: intermediate snapshots during replay (slice 24c)¶

The existing rewind path (PerspectiveRunner.RewindAndRunAsync) wrote ONE snapshot at the end of each replay. For "very late" events whose MessageId falls between the end-of-rewind snapshot and earlier events, that snapshot didn't qualify (GetLatestSnapshotBeforeAsync requires snapshot.event_id < triggeringEventId), so the rewind replayed from event zero — the "No qualifying snapshot found" log line.

Slice 24c fix. During replay, the runner takes an intermediate snapshot every RewindSnapshotIntervalEvents events (default 10). A 50-event rewind ends up with ~5 snapshots at events 10, 20, 30, 40, 50; MaxSnapshotsPerStream (default 5) bounds storage. Future late events almost always find a qualifying snapshot at or below their MessageId. Intermediate snapshot failures are best-effort — logged at Debug, don't break the replay.

Code: src/Whizbang.Generators/Templates/PerspectiveRunnerTemplate.cs

Options: PerspectiveSnapshotOptions.RewindSnapshotIntervalEvents (set to 0 to disable and keep end-of-rewind-only legacy behavior).

Apply-boundary batching (slice 22c)¶

Independent of ordering, the perspective drain path also benefits from coalescing same-stream signals before applying. The pre-slice-22c default sliding window for drain stream-ids was 50 ms / 1 s — too short for a production hot-spot where a single stream (Order saga aggregate) receives many events in rapid succession. Each tick triggered a separate apply cycle (read snapshot → apply 1 event → atomic UPSERT) even though all events were already pending.

Slice 22c.1 introduces IApplyBatchStrategy + per-stream SlidingWindowApplyBatchStrategy as a pluggable strategy interface for the apply boundary. Slice 22c.2 retunes the existing in-loop _accumulateDrainSignalsWithinWindowAsync accumulator default to 300 ms / 3 s / 1000 — the same window used by the inbox per-stream batcher in slice 23.

Result: many events for one stream collapse into one drain cycle; the worker reads the snapshot once, applies all of them in one Apply pass, writes one atomic UPSERT. CPU on hot streams drops; PG round-trips drop accordingly.

Code: src/Whizbang.Core/Messaging/IApplyBatchStrategy.cs — pluggable interface.

Tests: SlidingWindowApplyBatchStrategyTests covers per-stream coalesce + independent flush + drain semantics.