[BUG] : Offset-based pagination in cleanup jobs skips rows because the WHERE-matching set shrinks as rows are mutated/deleted

[hariom888]

Description of the Bug

cleanup_stale_documents, cleanup_old_deleted_documents, and cleanup_inactive_active_documents in backend/app/services/cleanup.py all page through results with LIMIT 100 OFFSET offset, incrementing offset by 100 each loop — while each iteration mutates (status = "failed") or deletes the very rows the WHERE clause filters on. Since the matching set shrinks by up to 100 rows every iteration but OFFSET keeps advancing as if it were stable, the loop skips whole batches of eligible documents.

Steps to Reproduce

1. Seed 250 documents that are stuck in status == "processing" with processing_started_at older than DOC_PROCESSING_TIMEOUT_MINUTES.
2. Run cleanup_stale_documents().
3. First iteration: OFFSET 0 returns rows 1-100, marks them "failed" (removing them from the matching set, now 150 rows).
4. Second iteration: OFFSET 100 is issued against a set that now only has 150 matching rows, skipping straight past what were originally rows 101-200.
5. Loop exits after processing 150 of 250 eligible documents; 100 remain stuck/orphaned.

Expected Behavior

All 250 eligible documents should be processed (marked failed / hard-deleted) in a single run, regardless of how many pages of 100 are needed.

Screenshots / Logs

No response

Environment

Any deployment; reproducible in a single test process with >100 seeded rows matching any of the three cleanup predicates — no concurrency required, purely a pagination logic bug.

GSSoC '26

• Yes, I am participating in GirlScript Summer of Code and would like to fix this.

[github-actions]

Hi @hariom888! 👋

Thanks for opening this issue! We are thrilled to have you contribute to PDF-Assistant-RAG for GSSoC 2026.

📌 Before you start:

• Make sure you have read our Contributing Guidelines.
• Wait for a project admin to assign this issue to you before working on it.
• Remember to branch off dev and not main.

Happy Coding! 🚀

[hariom888]

Technical understanding: pagination-under-mutation bug — all three jobs mutate/delete exactly the rows their own WHERE clause is filtering on, then keep advancing OFFSET as if the filtered set were static.

Affected file: backend/app/services/cleanup.py (all three job functions share this loop shape).

Impact: stale-processing recovery and hard-delete cleanup only process roughly half the eligible backlog per run, silently leaking files/vectors/BM25 indexes for the skipped half.

Proposed fix: drop OFFSET entirely and re-query LIMIT 100 at offset=0 every iteration (the mutation/deletion already shrinks the matching set), or keyset-paginate by primary key if the predicate is ever made non-mutating.

Tests: seed 150-250 rows matching each predicate, run the job once, assert 0 remaining matches (not total - 100).

Requesting this issue for GSSoC'26 — I'd like to submit the OFFSET removal fix with multi-batch seeded tests for all three jobs.