Edit Issue - .issue.db

Title *

Description

Smoking-gun timeline (run b4e5e56c, 2026-07-19, prod logs worker-7 + api):
- 01:05:12 wrapper (tools.workflow.execute) claims run, sets row 'running', opens ONE transaction spanning the WHOLE workflow execution -> holds the workflow_run row lock 92s (log: 'Starting workflow ... [Transaction Status: INTRANS]').
- 01:05:13 POST /pause: SELECT status pre-check reads 'pending', then its UPDATE (WHERE workflow_run_id only, NO status guard) blocks 91s on the row lock.
- 01:06:44.86 wrapper finishes all 9 steps, commits 'completed' (its own second update logs benign 'already terminal' rejection).
- 01:06:44.97 the queued pause UPDATE acquires the lock and OVERWRITES terminal 'completed' -> 'paused' (log: 'Workflow paused ... previous_status=pending', 91s after the request).
- 01:06:47 /resume -> 'running'. 01:06:50 /cancel -> 409 (wrapper run finished). Row is now a PERMANENT 'running' zombie for a workflow that actually completed. Second identical zombie: 812a91a8 (stuck 'running' since 00:09 for a ~2min chain).

Root causes:
1. Wrapper executes the whole workflow inside one DB transaction holding the workflow_run row lock -> ALL lifecycle endpoints (pause/resume/cancel) and any writer contend for the run's entire duration. Also explains historical 'pause takes 23-60s' latency and 'canceling statement due to statement timeout' errors when workers mark other runs failed (e.g. 01:04:04 'Failed to mark run 019f77e4 as failed').
2. pause/resume endpoints are SELECT-then-UPDATE with no status guard in the UPDATE's WHERE -> TOCTOU across arbitrarily long lock waits. jumper_client.update_workflow_run_status HAS the correct 'status NOT IN (terminal)' guard; the API blueprints' raw UPDATEs do not.

Fixes:
1. pause/resume (api/blueprints/v1/workflows.py): move the status check INTO the UPDATE ... WHERE status IN (...) RETURNING; 0 rows -> 409. Also SET lock_timeout/statement_timeout so requests fail fast instead of queueing minutes.
2. Engine: shorten/commit the wrapper's transaction around the row update at start (don't span execution), or move run-row writes to a separate autocommit connection.
3. Sweeper: reconcile 'running'/'paused' rows whose jumper wrapper run is terminal (cleans existing zombies 812a91a8, b4e5e56c).

Related: #825 (cancel cannot cancel claimed runs / no cooperative boundary cancel).

Due Date (YYYY-MM-DD)

Tags (comma separated)

Related Issues (IDs)

Enter IDs of issues related to this one. They will be linked as 'related'.

Edit Issue #826