Remote Note Cleanup Processing#

CleanRemoteNotesProcessorService is a BullMQ queue processor that batch-deletes old remote notes from the database. It is the backend for the admin-configurable feature controlled by meta.enableRemoteNotesCleaning 1.

Source files:

• Implementation: CleanRemoteNotesProcessorService.ts
• Tests: CleanRemoteNotesProcessorService.ts (unit/integration)

Configuration#

Three MiMeta fields govern the job 2:

Deletion Eligibility Criteria#

A note is eligible for deletion only if it meets all of the following conditions 3:

• id < newestLimit — older than the configured expiry age
• userHost IS NOT NULL — remote note only (local notes are never deleted)
• clippedCount = 0 — not clipped by any user
• pageCount = 0 — not embedded in a page
• Not in user_note_pining — not pinned on any profile
• Not in note_favorite — not favorited by any user
• Not in note_reaction (joined with user where host IS NULL) — not reacted to by any local user (reactions from remote users alone do not protect the note)

Recursive CTE with Anti-Join Tree Logic#

The core query is a recursive CTE that walks note trees starting from root notes (those without replyId or renoteId).

Base case: selects root notes satisfying removalCriteria, ordered by ID ASC, limited to currentLimit 4.

Inductive case: joins children (replyId or renoteId) to their parents, propagating rootId and re-evaluating isRemovable for each child 5.

Anti-join: the outer query performs a LEFT JOIN of the CTE against itself (unremovable) on rootId, filtering out any tree that contains at least one non-removable node. If any member of a tree fails eligibility, the entire tree is preserved 6. For example, a single clipped reply protects its entire ancestor chain.

This "whole-tree-or-nothing" guarantee means a note's children can block the parent's deletion, and vice versa 7.

Batch Size and Cursor Advancement#

The processor iterates in a for(;;) loop over note ID space using a cursorLeft pointer 8:

• Starting limit: 100 root notes per batch.
• Scale up: if a query takes <1 s and returns <1,000 rows, currentLimit is multiplied by 1.5 9.
• Scale down: if a query takes >5 s or returns >5,000 rows, currentLimit is halved 9.
• Hard bounds: clamped to [10, 5000].
• Cursor advance: after each batch, cursorLeft is set to the maximum id among base-level (root) notes returned, ensuring strict monotonic progress and no re-processing 10.
• Low-throughput warning: if >50% of the time budget is consumed but <50% of note space has been scanned, a warning is logged 11.

Between batches, the processor sleeps for min(5 s, queryDuration) to avoid overwhelming the database 12.

Statement Timeout Recovery (PostgreSQL error 57014)#

When a recursive CTE hits a large or complex note tree it can exceed the PostgreSQL statement_timeout, producing error code 57014 13.

Recovery strategy:

1. Reduce limit: if currentLimit > minimumLimit (10), cut it to 25% of its current value and retry.
2. Skip tree: if already at the minimum, run a lightweight fallback query — intentionally omitting the expensive NOT EXISTS subqueries — to find the next root note ID beyond the offending range, then advance cursorLeft past it 14. The comment notes this was introduced to fix issue #17057 where the heavy subqueries themselves would timeout.

Transient Error Handling#

PostgreSQL integrity-violation errors (class 23) during DELETE are counted and logged as transient errors rather than failing the job 15. This handles race conditions where a note gains a new reference (e.g., a reaction or favorite) between the CTE eligibility check and the actual delete. The job returns the transientErrors count so operators know a second pass may be needed.

Progress Reporting#

Progress is computed as a linear interpolation of the current cursorLeft timestamp against the [minId, newestLimit] timestamp range 16. This is reported to BullMQ via job.updateProgress() each iteration 17.