[Bug]: user status lifecycle leaves undeletable orphans

[miaulalala]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Summary

On 33.0.7, users randomly go Offline while active and never come back Online.

PR #61619 (stable33 backport of #59535) is the only user_status change between 33.0.6 and 33.0.7. That PR stops new corruption from one specific path, but it repairs none of the existing damage, and one of its two changes introduces a new permanent-failure mode of its own.

All of this was investigated on real containers. Time was simulated by ageing status_timestamp backwards.

The inspection query used throughout:

SELECT user_id, status, message_id, is_backup, clear_at, status_timestamp
FROM oc_user_status WHERE user_id IN ('alice', '_alice');

Constants: automated status message ids are meeting / call / availability / out-of-office; INVALIDATE_STATUS_THRESHOLD is 15 minutes; the client heartbeat fires every 5 minutes.

Revert of #61619 🛑

• Reverting only buys self-healing for stranded backups that carry a clear_at. The clear_at IS NULL case — a plain online status, the common one — is immortal on 33.0.6 too, because clearOlderThanClearAt requires clear_at IS NOT NULL.
• Brings back the following issues:
  • backups get flattened to offline after 15 minutes, so every meeting longer than 15 minutes ends with the user shown as offline
  • fresh orphans appear whenever a healthy backup's clear_at expires mid-meeting
  • backup rows leak into findAll() as phantom _user entries.
• Scenario B, the main orphan generator, is byte-identical either way.

Not covered by the PR, and worth tracking as follow-ups:

• Making is_backup NOT NULL — E is worked around by the repair command, not actually fixed.
• A bounded backup lifetime to replace the clear_at-based cleanup that [stable33] fix(user_status): stabilise the user_status #61619 removed. That removal assumed orphans were self-limiting; scenario B disproves that assumption, and this is probably the single most useful thing to take away from this issue.

Note: the calendar-busy early-return in UserLiveStatusListener should not be treated as safe. It's what turns a recoverable state into a permanent one.

Steps to reproduce

Reproduction

A. New in 33.0.7: a stranded backup silently kills all future automated statuses

Reproduces on a clean 33.0.7, no historical corruption needed.

1. alice has a normal status. Create a calendar event that marks her busy and let the calendar automation run.
   • Expected DB state: a live row for alice with message_id = 'meeting', plus a backup row _alice with is_backup = 1.
2. While the meeting is still running, have alice click "Clear status message" (setting a custom message or picking a predefined status does the same thing).
   • The live row's message_id becomes NULL.
3. Let the meeting end.
   • revertUserStatus() matches on the live row still carrying the automated message id. It no longer matches, so the backup is neither restored nor deleted.
   • Observed: _alice stays in the table indefinitely.
4. Trigger any further automated status — another meeting, a Talk call, out-of-office.
   • backupCurrentStatus() hits the unique constraint on user_id because _alice already exists, returns false, and setUserStatus() silently aborts, returning null.
   • Observed: no automated status is ever applied to alice again. No error surfaces.

On 33.0.6 this self-healed, but only when the backup carried a clear_at: the unfiltered clearOlderThanClearAt eventually deleted the row. #61619 added is_backup = false to that query, and nothing else ever deletes a _userId row.

B. Permanent stuck-offline for a user with no prior status row

Predates #61619 and affects all versions. This is the main generator of the reported symptom.

1. Take a user who has never set a status — no oc_user_status row at all.
2. Create a calendar event marking them busy.
   • createBackupStatus() runs UPDATE ... SET is_backup = true, user_id = '_' || uid WHERE user_id = uid. With no row to update, it affects 0 rows and returns false.
   • backupCurrentStatus() discards that return value and returns true as long as no exception was thrown.
   • Observed: a live automated-status row gets inserted, but no backup row is created.
3. Let the meeting end.
   • revertUserStatus() finds no backup and returns early, leaving the automated status on the live row. Nothing else removes it: clearStatusesOlderThan() skips user-defined statuses, and calendar statuses carry no clear_at.
4. Have the user set themselves Online manually in the UI.
5. Wait 15 minutes (or age status_timestamp back by 15 minutes).
   • processStatus() / cleanStatus() flips them to Offline — ONLINE is not protected from cleanStatus.
6. Leave the client running so heartbeats keep firing every 5 minutes.
   • UserLiveStatusListener early-returns on message_id === MESSAGE_CALENDAR_BUSY, so no heartbeat can restore Online.
   • Observed: permanently stuck Offline. The only escape found was "Clear status message".

C. Pre-existing damage carried across the upgrade

Same end state as B, reached differently, and the reason 33.0.7 alone doesn't fix already-affected instances.

1. On 33.0.6, a user is on an automated status with a valid backup row whose clear_at expires while the meeting is still running.
2. 33.0.6's unfiltered clearOlderThanClearAt deletes the backup row, orphaning the live row.
3. Upgrade to 33.0.7.
   • Observed: the user is now in state B (step 3 onwards). 33.0.7 ships no migration and no repair command, so they stay stuck.

D. Meeting longer than 15 minutes restores a stale status

1. Put a user on an automated status lasting longer than 15 minutes (a 90-minute meeting).
2. Let it end normally, with a healthy backup present.
   • revertUserStatus() restores the backup without refreshing status_timestamp, unless revertedManually is set.
   • Observed: the restored status is already older than INVALIDATE_STATUS_THRESHOLD, so the very next read runs cleanStatus() and rewrites the user to Offline.
3. Wait for the next heartbeat (up to 5 minutes).
   • Observed: it recovers. So this is a visibility window, not a permanent stick.

E. Latent, upgraded-instances-only — NULL is_backup

Not reproducible on a fresh install; noting this as background rather than a repro.

• is_backup is tinyint(1) DEFAULT 0 but nullable.
• is_backup = false doesn't match NULL, so a NULL row is invisible to findAll() / findAllRecent() — other users see that person as offline — and it's skipped by both cleanup queries.

Expected behavior

User status are consistent and revert properly, don't leave orphaned rows