Skip to content

SSE streams end cleanly on DB errors

The timestamptz half of #2956 (four naive columns, one migration) ships separately, so this fix merges without a database change.

SSE streams: mid-stream DB errors

Problem

job_stream_by_id and pipeline_stream_by_id in api/v1/data_sync.py open a SessionLocal() on every poll, after the 200 has been sent. main.py registers db_unavailable_handler for DBAPIError and SQLAlchemy's pool TimeoutError. Once a response has started, Starlette (_exception_handler.py) re-raises any exception with a registered handler as RuntimeError("Caught handled exception, but response already started."). The DB error survived only as __cause__, so log queries keyed on OperationalError missed it (plan 2049, last section).

Decisions

  1. One wrapper, two callers. _end_stream_on_db_error re-yields the stream's events and catches DBAPIError and the pool TimeoutError only. It logs the real exception with exc_info and the request path, then ends the stream. The poll is the only DB access inside either generator, so wrapping the generator is the same as catching in the poll loop. Any other exception still propagates.
  2. Every DBAPIError, not only "unavailable". The stream has no 503 to pick, so it doesn't need db_unavailable_handler's predicate. The terminal message therefore says "database error", not "temporarily unavailable".
  3. Job stream: the "Job not found" shape. It emits {"job_id", "status_message": "Job status unavailable: database error"} and returns. subscribeToJobUpdates (stores/backofficeDataManagement.ts) treats neither payload as finished. The server close fires onerror, which unsubscribes and calls the caller's onError ("connection lost"). A state: FINISHED, result: ERROR payload was rejected: it would write a fake failed row into syncJobs and fire onFail for a job that may still be running.
  4. Pipeline stream: no event. Its only terminal shape is a pipeline-update with stream_closed: true, which needs real jobs and progress. Without them it would clobber the store entry. usePipelineStream relies on native EventSource retry. The reconnect runs the pre-stream check, where a DB that is still down answers 503 when db_unavailable_handler judges it unavailable, 500 otherwise. Either non-200 closes the EventSource for good. Client behaviour matches the aborted connection of before; only the log changes.
  5. No frontend change.

Found, not fixed

  • ModuleTable.vue passes no onError to subscribeToJobUpdates, and useSubmoduleConfig.ts's onError only resets the spinner. Both were already silent when the stream ended on "Job not found".
  • No other stream reads the DB after the response starts. The audit exports (api/v1/audit.py) and the backoffice exports (api/v1/backoffice.py) build the whole body before responding. report_detailed's lazy generator only reads a temporary zip file.
  • The pipeline badge freezes silently when a DB outage outlasts the browser's retry delay. usePipelineStream.ts has no onerror, and activeStreams.delete only runs in closeStream, so the closed source stays registered and a re-subscribe does not reopen it. Same as before this PR.

Tests

tests/unit/v1/test_data_sync_stream_db_errors.py drives both generators with a repository that fails on the second poll:

  • job stream, for OperationalError and the pool TimeoutError: the first event is the real state, the second and last is the terminal payload, and the log record's exc_info holds the raised error;
  • pipeline stream: one pipeline-update, then the stream ends and the error is logged;
  • a ValueError still propagates.

The first two fail without the fix: the DB error escapes the generator.