[Ops] Reconcile orphaned audio directories against the database #406

New issue

Closed

opened 2026-08-25 20:44:45 +00:00 by claude-bot · 0 comments

claude-bot commented

2026-08-25 20:44:45 +00:00

Collaborator

Severity: MEDIUM

Found in the August 2026 session lifecycle review (#319).

Audio directories on the shared volume that no longer correspond to a live database row are never cleaned up by anything — neither the backend's retention sweep, which only ever looks at DB rows, nor the bot's own startup sweep, which deliberately and permanently skips any directory carrying a HANDOFF_MARKER "regardless of age." On a hosted, multi-tenant volume this is unbounded storage growth with a real dollar cost, and it also means the DB and the filesystem can never be reconciled after a restore.

Evidence

webapp/backend/app/routers/campaigns.py:929-953 / webapp/backend/app/services/campaign_service.py:190-193 — a campaign hard-delete cascades away every session row, but nothing in the delete path or any Beat task removes that session's audio directory from the audio_temp volume; the backend's retention sweeps (reminder_tasks.py:2650+) iterate DB rows, so a directory whose row is gone is invisible to them by construction.
bot/questboard_bot/main.py:364-365 — the bot's startup sweep explicitly skips any directory with HANDOFF_MARKER, "regardless of age," which is correct for live sessions but means a handed-off directory whose backend-side row has since been deleted (campaign delete, or a database restored from an older backup) is never swept by either side.
A session stuck permanently in processing (tracked in "[Backend] Add Celery acks_late and a watchdog...") is also exempt from every retention sweep, since those sweeps only ever act on failed or successfully-processed sessions — so a stuck-processing session's directory occupies volume space indefinitely on top of being otherwise unrecoverable.

Failure scenario
A GM deletes an old, inactive campaign (or a database restore drops several months of session rows relative to what's on disk). The audio directories for every session in that campaign remain on the audio_temp volume forever — neither sweeper will ever touch them, since one only looks at DB rows that no longer exist and the other treats "handed off" as permanently hands-off. On a hosted instance serving many tenants, this is a continuously growing, never-reclaimed cost with no operator-visible signal that it's happening.

Proposed fix
Add a periodic reconciliation Beat task that lists directories under the audio temp volume and, for each, checks whether a corresponding session row still exists and (if so) is in a state where the directory is expected to be retained. Directories with no matching row, or matching a row whose retention policy has already expired, are deleted; directories matching a row that's been stuck in an in-progress state past a threshold are flagged for the watchdog (tracked in "[Backend] Build a generic stuck-task watchdog...") rather than swept blindly. This closes the gap in both directions — orphans from deleted rows, and rows stuck too long to ever reach a sweepable state.

Acceptance criteria

A scheduled task reconciles the audio_temp volume against the sessions table in both directions (directory-with-no-row, and stuck-row-with-a-directory-nothing-ever-sweeps).
Directories with no corresponding session row are deleted after a safety grace period.
The reconciliation task's actions are logged/auditable.
Deleting a campaign either cleans up its sessions' audio directories directly or is guaranteed to be caught by the reconciliation task within a bounded time.

**Severity: MEDIUM** Found in the August 2026 session lifecycle review (#319). Audio directories on the shared volume that no longer correspond to a live database row are never cleaned up by anything — neither the backend's retention sweep, which only ever looks at DB rows, nor the bot's own startup sweep, which deliberately and permanently skips any directory carrying a `HANDOFF_MARKER` "regardless of age." On a hosted, multi-tenant volume this is unbounded storage growth with a real dollar cost, and it also means the DB and the filesystem can never be reconciled after a restore. **Evidence** - `webapp/backend/app/routers/campaigns.py:929-953` / `webapp/backend/app/services/campaign_service.py:190-193` — a campaign hard-delete cascades away every session row, but nothing in the delete path or any Beat task removes that session's audio directory from the `audio_temp` volume; the backend's retention sweeps (`reminder_tasks.py:2650+`) iterate DB rows, so a directory whose row is gone is invisible to them by construction. - `bot/questboard_bot/main.py:364-365` — the bot's startup sweep explicitly skips any directory with `HANDOFF_MARKER`, "regardless of age," which is correct for live sessions but means a handed-off directory whose backend-side row has since been deleted (campaign delete, or a database restored from an older backup) is never swept by either side. - A session stuck permanently in `processing` (tracked in "[Backend] Add Celery acks_late and a watchdog...") is also exempt from every retention sweep, since those sweeps only ever act on `failed` or successfully-processed sessions — so a stuck-processing session's directory occupies volume space indefinitely on top of being otherwise unrecoverable. **Failure scenario** A GM deletes an old, inactive campaign (or a database restore drops several months of session rows relative to what's on disk). The audio directories for every session in that campaign remain on the `audio_temp` volume forever — neither sweeper will ever touch them, since one only looks at DB rows that no longer exist and the other treats "handed off" as permanently hands-off. On a hosted instance serving many tenants, this is a continuously growing, never-reclaimed cost with no operator-visible signal that it's happening. **Proposed fix** Add a periodic reconciliation Beat task that lists directories under the audio temp volume and, for each, checks whether a corresponding session row still exists and (if so) is in a state where the directory is expected to be retained. Directories with no matching row, or matching a row whose retention policy has already expired, are deleted; directories matching a row that's been stuck in an in-progress state past a threshold are flagged for the watchdog (tracked in "[Backend] Build a generic stuck-task watchdog...") rather than swept blindly. This closes the gap in both directions — orphans from deleted rows, and rows stuck too long to ever reach a sweepable state. **Acceptance criteria** - [ ] A scheduled task reconciles the audio_temp volume against the sessions table in both directions (directory-with-no-row, and stuck-row-with-a-directory-nothing-ever-sweeps). - [ ] Directories with no corresponding session row are deleted after a safety grace period. - [ ] The reconciliation task's actions are logged/auditable. - [ ] Deleting a campaign either cleans up its sessions' audio directories directly or is guaranteed to be caught by the reconciliation task within a bounded time.

claude-bot added this to the v4.1.0 - Data Durability & Recovery milestone

2026-08-25 20:44:45 +00:00

claude-bot added the

enhancement

backend

labels

2026-08-25 20:44:45 +00:00