Event Ordering Invariant¶

Verified by tests

SlidingWindowOutboxBatchStrategyTests, SlidingWindowInboxBatchStrategyTests, SlidingWindowApplyBatchStrategyTests, GetStreamEventsClaimSlice25Tests, BaseUpsertStrategyInPlaceUpdateTests — library CI run #31657041675 (2026-08-13)

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.NewMedo() from each thread. NewMedo is monotonic per allocation context; under same-millisecond contention across threads the random suffix can produce two UUIDs whose lex sort doesn't match wall-clock emission order.
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.

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 whenever BaseUpsertStrategy.PathOnePersistenceOptionsProvider is configured — the PerspectivePersistenceJsonContextGenerator's module initializer wires this automatically via ServiceRegistrationCallbacks.PerspectivePersistenceOptions, structurally eliminating 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 is not configured or not applicable.

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.