canary: B-01 queue-status coherence reads are not temporally atomic nor backend-consistent (raw sqlite3 vs get_engine)

[AndriiPasternak31]

Summary

Canary B-01 (queue-status coherence) collects its two compared values from two independent reads that are neither temporally atomic nor backend-consistent. On the shipping SQLite backend with the canary default-OFF these gaps are latent, so they were left out of scope for the #1446 P1 (which fixed the test-harness sys.modules leak that was the actual reported flake). This issue tracks the two production-side gaps the #1446 investigation surfaced and deferred.

Follow-up of #1446. Epic #411 (CANARY-001 harness).

The two gaps

src/backend/canary/snapshot.py :: collect_snapshot() builds each AgentSnapshot from two separate reads, per agent:

# snapshot.py ~614
execs = _collect_executions(name)            # raw sqlite3 via db.connection.get_db_connection() at DB_PATH
# snapshot.py ~627
queued_via_service = _collect_queued_count_via_service(name)   # db.get_queued_count → SQLAlchemy get_engine()

B-01 (canary/invariants/b01_queue_status_coherence.py) then fires critical on any inequality between len(queued_exec_ids) (from the first read) and queued_count_via_service (from the second), with no tolerance and no confirm-re-read.

(a) Temporal non-atomicity

The two reads are separate queries at different instants. Under a live canary against real fleet load (e.g. the canary-fleet-burst generator), a concurrent enqueue or backlog-drain landing between the two reads produces a transient count mismatch → a spurious critical B-01 violation and a green→red Slack alert. The window is small but non-zero and the invariant has no confirm-read to absorb it.

(b) Backend divergence under the configurable engine (#300/#1093)

_collect_executions reads raw sqlite3 at db.connection.DB_PATH; _collect_queued_count_via_service goes through get_engine(), which honors DATABASE_URL (incl. PostgreSQL). On SQLite both resolve to the same file (why this is invisible today). On a PostgreSQL backend they are two different databases — the canary would compare Postgres truth to a stale/absent SQLite file, so B-01 (and any other raw-sqlite reader in the collector) is not backend-aware. This becomes fatal under the Postgres direction in docs/planning/TARGET_ARCHITECTURE.md and the SQLite end-of-support date (#1278, 2026-09-01).

Why deferred, not fixed in #1446

#1446's reported symptom was a unit-test flake (B-01 firing only under full-suite load), root-caused to a never-restored sys.modules.setdefault("database", MagicMock()) leak — a harness bug, fixed test-only. These two gaps are genuinely separate latent issues with no evidence of a live-canary fire yet; they're masked because the canary is default-OFF (CANARY_ENABLED=1 only on staging/dev). See docs/memory/learnings.md (2026-07-04 entry) and .plan/issue-1446-context.md Hypothesis B.

Acceptance criteria

1. B-01's two compared sides are sourced from the same backend (route _collect_executions — or at least B-01's queued-id list — through get_engine() so both honor DATABASE_URL). A canary-wide raw-sqlite3 → engine migration of the snapshot collector is the larger, related cleanup; scope this issue to at least B-01 consistency.
2. B-01 tolerates a transient in-flight mismatch — e.g. a single confirm-re-read of both sides before reporting, so a concurrent enqueue/drain doesn't produce a false critical.
3. B-01 stays a genuine coherence check, not a tautology — the two sides must remain independent code paths (production accessor vs independent id-list read), per the invariant's docstring.
4. Do not add a cache / second representation of the queued count (that is exactly the drift B-01 guards against, and moves away from the single-store target).
5. Regression coverage: a test that drives collect_snapshot() on a non-SQLite / diverged-backend configuration (or simulates the temporal race) and proves B-01 does not false-fire.

Notes

• Keep the fix out of the CAS/schedule_executions transaction machinery (feat: transactional agent executions — discard workspace changes unless validated as success (research-gated) #1095 is research-gated NO-GO for broad changes there).
• docs/planning/TARGET_ARCHITECTURE.md: consult before touching the collector; the target makes COUNT(*) WHERE status='queued' the Lever-1 inbox-depth signal — one store, one truth.

[AndriiPasternak31]

/claim

[github-actions]

Claimed by @AndriiPasternak31. Label status-in-progress added.

Next steps:

1. Create branch: feature/1450-<short-slug>
2. Implement the feature
3. Open a PR with Fixes #1450