Out-of-order run projections can regress durable state after newer commits

[Bnjoroge1]

Severity

P1 / high correctness and durability

Review baseline

0bd0e314696e520aa47620cbe8f008d010e01e57

Summary

Concurrent calls to AppState::emit can capture state projections in the correct in-memory mutation order but commit them to SQLite/Postgres in the opposite order. There is no projection revision, compare-and-swap, or ordered persistence queue, so an older projection can overwrite a newer durable run state.

This affects more than the run row: store_run_event also rewrites global claim/message tables, so a stale event for run A can roll back newer claim state belonging to run B.

Source path

1. state.rs::AppState::emit acquires inner, constructs RunProjection, releases the state lock, then awaits store_run_event.
2. Concurrent callers can therefore create projection N and N+1 in order, but reach the store in either order.
3. SQLite uses one connection mutex, but each call is launched through a separate spawn_blocking; mutex acquisition order is not guaranteed to match projection creation order.
4. Postgres also uses a single client mutex but has no projection revision check.
5. store_run_event performs unconditional upserts/deletes, so the later database transaction wins even if its projection is older.

Reproduction

I reproduced the storage mechanism with SQLite WAL and two full projections:

commit order: ['new', 'old']
expected durable status after newer projection: completed
actual durable status: running
expected durable claim set: [('session-new', 2)]
actual durable claim set: [('session-old', 1)]
reproduced regression: True

The reproduction intentionally mirrors the current transaction shape: write a run projection, delete and rebuild the claim table, then allow a stale projection to commit after a newer one.

Impact

After a process restart, Preloop may:

• resurrect a completed job as running;
• restore a stale queue entry;
• lose a newer session claim;
• redeliver an old broker message;
• strand or duplicate work;
• reconcile GitHub check state from a projection that was never the latest in memory.

Recommended remediation

• Allocate a monotonically increasing global revision and/or per-run revision while holding InnerState.
• Persist through one ordered writer queue, preserving revision order.
• Store the last durable revision and reject incoming_revision <= durable_revision in SQL.
• Move claim/message state to keyed revisioned deltas rather than attaching a global full-table rewrite to arbitrary run events.
• Add deterministic tests that delay the older write until after the newer write for both SQLite and Postgres.

Acceptance criteria

• A stale projection cannot overwrite a newer run projection.
• A run-A persistence operation cannot regress run-B claim/message state.
• SQLite and Postgres pass the same forced-reordering contract test.
• Restart state always reflects the highest committed logical revision, not the last task that happened to acquire the DB mutex.