Outbox relay strands events in processing state after a crash #417

Labels / Complexity: bug, reliability, database, Backend · Extremely High — 500

Problem

OutboxRelay.process_pending_events in quantara/web_app/tasks/outbox_relay.py marks an event "processing" and commits it before publishing to Celery:

event.status = "processing"
db.commit()                    # ← committed before the message is actually queued
process_position_opened_task.delay(str(event.id))

The re-scan query only selects status.in_(["pending", "failed"]), so an event stuck in "processing" is never picked up again. A crash, broker outage, or exception between db.commit() and delay() (or a delay() that raises because Redis/Celery is down) permanently strands the event: it will never be re-queued, never fail, and never be retried, even though retry_count remains below max_retries.

Secondary defects in the same path:

• The outer except Exception catches the failed delay() but only logs; it does not revert the event to "pending" or "failed", so the stranding window is the default outcome on any publish error.
• process_position_opened_task calls position_db_connector.get_object(Position, position_id) where position_id is the raw JSON string from the payload; Position.id is a PostgreSQL UUID column (quantara/web_app/db/models.py), so the comparison can raise a DataError on invalid UUID syntax and skip legitimately processed events.
• There is no transactional hand-off between marking the event and enqueuing it, so the outbox pattern's at-least-once guarantee is not met.

Root cause

event.status = "processing"
db.commit()                                    # ← state committed before enqueue
process_position_opened_task.delay(str(event.id))  # ← failure here strands the event

Why this is architecturally hard

1. The naive fix — revert to "pending" in the except — only narrows the window; it does not eliminate the crash-between-commit-and-publish gap. A correct fix needs an atomic claim (e.g. a conditional UPDATE ... WHERE status='pending' RETURNING id with a claim timestamp, or moving the enqueue before the commit and rolling back on failure).
2. The relay and the Celery task each open their own SessionLocal() sessions and run in different processes, so there is no shared transaction to make the mark-and-enqueue atomic; the contributor must introduce an explicit lease/claim mechanism rather than reuse db.commit().
3. A visibility/lease timeout is required to recover events stranded by a hard crash (mark processing with updated_at/claimed_at, and re-claim events whose lease has expired), which changes the re-scan query and needs a backoff so hot events are not double-queued.
4. The UUID-vs-string mismatch in get_object must be fixed or the task's payload validated, otherwise the reliability fix will still fail at the resource level.

Proposed design

Atomic claim with lease expiry:

UPDATE event_outbox
   SET status = 'processing', claimed_at = now()
 WHERE id = :id AND status IN ('pending','failed')
RETURNING id;

and a re-scan that reclaims processing events older than a lease timeout. Offer this as one option; the maintainer decision is the lease duration and whether to use Celery's own retry or the outbox retry_count.

Downstream impact

No public API change. The OutboxEvent table (quantara/web_app/db/models.py) may need a claimed_at column, which is an Alembic migration. Existing rows stuck in processing must be handled by the migration or a one-off reclaim.

Acceptance criteria

Service

• A crash or failed delay() between claim and enqueue does not permanently strand an event; the event is reclaimed and re-queued within a bounded time.
• The re-scan query reclaims expired processing events.
• process_position_opened_task parses/validates the position_id before the UUID comparison.

Tests

• Tests simulate a failed publish and assert the event is re-queued on the next scan.
• Tests simulate a stale processing event and assert it is reclaimed after the lease expires.
• Tests run via cd quantara && poetry run pytest web_app/tests.

Out of scope

Do not rebuild the Celery wiring or add a dead-letter store in this issue; only make the outbox claim/reclaim durable.

Getting started

Files in scope: quantara/web_app/tasks/outbox_relay.py, quantara/web_app/db/models.py, plus an Alembic migration. Verify with:

cd quantara && poetry run pytest web_app/tests -k outbox

Good first files to read: quantara/web_app/tasks/outbox_relay.py, quantara/web_app/db/models.py (the OutboxEvent model), quantara/web_app/db/crud/position.py.