Verified finding
Tracks the 2026-09-11 Jules notification, N+1 DB Flush Issue in Reply SLA Escalation Service, against backend/services/reply_sla_escalation_service.py.
Inspected identities:
The ordinary escalation path already batches task creation and commits once. The first fallback insertion also flushes once after the add loop. The actual linear round-trip defect is the per-task SAVEPOINT/flush fallback after repeated uniqueness conflicts; merely moving/removing the first batch flush would be incorrect.
Additional verified transaction defects: savepoint rollback already detaches newly inserted objects, so develop's subsequent expunge() raises InvalidRequestError; outer rollback expires loaded Email attributes even with expire_on_commit=False, and develop accesses them without an explicit reload. #1486 removes the invalid expunge calls and refreshes Email objects individually, but retains the row-at-a-time savepoints and introduces N explicit refresh SELECTs. A failure in begin_nested()'s pre-savepoint flush also requires an outer rollback before any reconciliation query.
No external LLM/network call or explicit row-lock statement was found in this inspected escalation/reply-tracking path. This is not evidence of a production deadlock or measured p95 regression.
Repair and acceptance
A narrow develop-based fix will preserve existing task identity, owner scoping, response order, completed-task state, and response-wins behavior tracked by #1648. It will:
- use at most three batch SAVEPOINT attempts, reconciling visible duplicate winners in batched reads rather than switching to per-row flushes;
- roll back local changes and raise the existing
ReplySlaTaskConflict (HTTP 409) on exhausted contention, without a partial commit;
- retain primitive email IDs before rollback and reload selected inputs in one owner-scoped query, failing closed if a selected source disappears or changes owner;
- avoid double-expunge and preserve non-duplicate/pre-savepoint integrity errors after cleanup.
Three attempts is an explicit bounded-contention policy, not a benchmark-derived optimal value. Sustained contention can now return 409 instead of extending the transaction through N individual attempts. No schema, scheduler lease, frontend, credential, or CI/security-gate changes are requested.
Evidence so far
A hash-verified copy of the actual develop service was exercised with real SQLAlchemy 2.0.50 unit-of-work/SQLite constraints and SAVEPOINTs, reduced test models, and scripted conflict visibility. Initial baseline: 7 failed / 3 passed. Removing only the two invalid expunge calls for a diagnostic projection exposed 11 and 51 explicit flushes at 10 and 50 inputs; this is not an execution receipt for the whole #1486 head. The candidate regression suite currently passes 17 tests, including pre-savepoint failure cleanup, source deletion/scope changes, stable response identity, done-state preservation, and complete rollback.
These are isolated SQLAlchemy/SQLite regressions, not real async PostgreSQL concurrency, a production benchmark, or full backend/PR CI. PostgreSQL execution, required exact-head checks, and independent review remain acceptance gates. Keep this issue open until the fix is protected-integrated with qualifying evidence.
Overlap
#1486 must preserve its workspace-aware selection and scheduler changes when integrating the equivalent service repair; do not replace that branch's entire service with the develop version. #1648's same-ID authoritative-response test remains valid and should not be rewritten.
Primary references
Verified finding
Tracks the 2026-09-11 Jules notification, N+1 DB Flush Issue in Reply SLA Escalation Service, against
backend/services/reply_sla_escalation_service.py.Inspected identities:
develop@042b0c70531b229af3acbd0421a2f23098d848b3, service blob7800f121c8c9d5704de6cf33e26583c7836051c0;1709ebb8d79f55c688a141aa932fa00468bf836d;8ad819786c6c2a99e0a158d2192054eb8189b2f4(a frontend response-authority regression stacked on fix(deps): patch frontend audit security floors #1623; its backend service is identical to develop).The ordinary escalation path already batches task creation and commits once. The first fallback insertion also flushes once after the add loop. The actual linear round-trip defect is the per-task SAVEPOINT/flush fallback after repeated uniqueness conflicts; merely moving/removing the first batch flush would be incorrect.
Additional verified transaction defects: savepoint rollback already detaches newly inserted objects, so develop's subsequent
expunge()raisesInvalidRequestError; outer rollback expires loaded Email attributes even withexpire_on_commit=False, and develop accesses them without an explicit reload. #1486 removes the invalid expunge calls and refreshes Email objects individually, but retains the row-at-a-time savepoints and introduces N explicit refresh SELECTs. A failure inbegin_nested()'s pre-savepoint flush also requires an outer rollback before any reconciliation query.No external LLM/network call or explicit row-lock statement was found in this inspected escalation/reply-tracking path. This is not evidence of a production deadlock or measured p95 regression.
Repair and acceptance
A narrow develop-based fix will preserve existing task identity, owner scoping, response order, completed-task state, and response-wins behavior tracked by #1648. It will:
ReplySlaTaskConflict(HTTP 409) on exhausted contention, without a partial commit;Three attempts is an explicit bounded-contention policy, not a benchmark-derived optimal value. Sustained contention can now return 409 instead of extending the transaction through N individual attempts. No schema, scheduler lease, frontend, credential, or CI/security-gate changes are requested.
Evidence so far
A hash-verified copy of the actual develop service was exercised with real SQLAlchemy 2.0.50 unit-of-work/SQLite constraints and SAVEPOINTs, reduced test models, and scripted conflict visibility. Initial baseline: 7 failed / 3 passed. Removing only the two invalid expunge calls for a diagnostic projection exposed 11 and 51 explicit flushes at 10 and 50 inputs; this is not an execution receipt for the whole #1486 head. The candidate regression suite currently passes 17 tests, including pre-savepoint failure cleanup, source deletion/scope changes, stable response identity, done-state preservation, and complete rollback.
These are isolated SQLAlchemy/SQLite regressions, not real async PostgreSQL concurrency, a production benchmark, or full backend/PR CI. PostgreSQL execution, required exact-head checks, and independent review remain acceptance gates. Keep this issue open until the fix is protected-integrated with qualifying evidence.
Overlap
#1486 must preserve its workspace-aware selection and scheduler changes when integrating the equivalent service repair; do not replace that branch's entire service with the develop version. #1648's same-ID authoritative-response test remains valid and should not be rewritten.
Primary references