1 files changed, 90 insertions, 35 deletions
diff --git a/packages/meshbay-hub/src/meshbay_hub/tasks/cleanup.py b/packages/meshbay-hub/src/meshbay_hub/tasks/cleanup.py
index b0ef4d4..559d872 100644
--- a/packages/meshbay-hub/src/meshbay_hub/tasks/cleanup.py
+++ b/packages/meshbay-hub/src/meshbay_hub/tasks/cleanup.py
@@ -4,7 +4,7 @@ import asyncio
 import logging
 from datetime import UTC, datetime, timedelta
 
-from sqlalchemy import delete, select
+from sqlalchemy import delete, select, update
 from sqlalchemy.ext.asyncio import AsyncSession
 
 from meshbay_hub.db.models import EmailVerification, Group, GroupInviteLink, IPLog, User
@@ -35,9 +35,49 @@ async def purge_expired_verifications(db: AsyncSession) -> int:
 
 async def purge_stale_pending_users(db: AsyncSession,
                                      expiry_days: int = PENDING_USER_EXPIRY_DAYS) -> int:
+    """Delete accounts that never verified their address, and what points at them.
+
+    A pending account is not childless: registering writes an `account_create`
+    IP-log row, a failed sign-in another, and a group owner may have added it to
+    a group. A bare `DELETE FROM users` therefore violates those foreign keys —
+    which PostgreSQL enforces and the SQLite the suite runs on does not — so on
+    the real hub it raised, the stale account stayed, and every later run
+    raised again.
+
+    The IP log is kept and detached, keeping the name, exactly as
+    `erase_account` does for a deleted account: it is the legal record. Every
+    other nullable reference is cleared and every other row deleted, found from
+    the schema so a table added later is covered. An account that owns a group
+    is left alone — a pending account cannot create one, so that would be a
+    state this function did not expect and should not guess about.
+    """
+    from meshbay_hub.db.models import Base, Group
+
     cutoff = datetime.now(UTC) - timedelta(days=expiry_days)
-    result = await db.execute(
-        delete(User).where(User.status == "pending", User.created_at < cutoff))
+    stale = (await db.execute(
+        select(User.id, User.username).where(
+            User.status == "pending", User.created_at < cutoff,
+            ~User.id.in_(select(Group.admin_id))))).all()
+    if not stale:
+        return 0
+    ids = [uid for uid, _ in stale]
+
+    for uid, name in stale:
+        await db.execute(update(IPLog).where(IPLog.user_id == uid)
+                         .values(username=name, user_id=None))
+    for table in Base.metadata.sorted_tables:
+        if table.name in (User.__tablename__, IPLog.__tablename__):
+            continue
+        for fk in table.foreign_keys:
+            if fk.column.table.name != User.__tablename__:
+                continue
+            column = fk.parent
+            if column.nullable:
+                await db.execute(update(table).where(column.in_(ids))
+                                 .values({column.name: None}))
+            else:
+                await db.execute(delete(table).where(column.in_(ids)))
+    result = await db.execute(delete(User).where(User.id.in_(ids)))
     await db.commit()
     return result.rowcount
 
@@ -53,42 +93,57 @@ async def purge_invite_links(db: AsyncSession) -> int:
     return result.rowcount
 
 
+async def _purge_login_throttle(db: AsyncSession) -> int:
+    from meshbay_hub import login_throttle
+    return await login_throttle.purge_expired(db)
+
+
+async def _purge_mail_quota(db: AsyncSession) -> int:
+    from meshbay_hub import mail
+    return await mail.purge_expired_quota(db)
+
+
+# In order, each in its own session and its own try: one step that raises must
+# not cost the others their run. They used to share one `try`, so the day
+# `purge_stale_pending_users` first hit a foreign key on PostgreSQL, the mail
+# counters, the sign-in counters and the invitation links behind it stopped
+# being purged at all — silently, since the loop logged one line and slept.
+_STEPS = (
+    ("IP log entries older than the retention period", purge_old_ip_logs),
+    ("expired email verifications", purge_expired_verifications),
+    ("stale pending users", purge_stale_pending_users),
+    # One row per recipient the hub has written to, and the window is a day:
+    # without this the table grows for the life of the instance.
+    ("expired mail counters", _purge_mail_quota),
+    # Every name anybody types at the sign-in form is a row, real or not.
+    ("expired sign-in counters", _purge_login_throttle),
+    ("spent or expired invitation links", purge_invite_links),
+)
+
+
+async def run_cleanup(get_session) -> dict[str, int | None]:
+    """One pass of every step. A step that failed reports None."""
+    done: dict[str, int | None] = {}
+    for what, step in _STEPS:
+        try:
+            async with get_session() as db:
+                n = await step(db)
+            done[what] = n
+            if n:
+                log.info("Purged %d %s", n, what)
+        except asyncio.CancelledError:
+            raise
+        except Exception as e:
+            done[what] = None
+            log.error("Cleanup of %s failed: %s", what, e)
+    return done
+
+
 async def cleanup_loop(get_session):
     """Run cleanup once at startup, then every 24 hours."""
     try:
         while True:
-            try:
-                async with get_session() as db:
-                    deleted = await purge_old_ip_logs(db)
-                    if deleted:
-                        log.info("Purged %d IP log entries older than %d days",
-                                 deleted, RETENTION_DAYS)
-                    expired = await purge_expired_verifications(db)
-                    if expired:
-                        log.info("Purged %d expired email verifications", expired)
-                    stale = await purge_stale_pending_users(db)
-                    if stale:
-                        log.info("Purged %d stale pending users", stale)
-                    # One row per recipient the hub has written to, and the
-                    # window is a day: without this the table grows for the
-                    # life of the instance and nothing ever reads the old rows.
-                    from meshbay_hub import mail
-                    quota = await mail.purge_expired_quota(db)
-                    if quota:
-                        log.info("Purged %d expired mail counters", quota)
-                    # Every name anybody types at the sign-in form is a row,
-                    # real or not; once its window has passed, nothing reads it.
-                    from meshbay_hub import login_throttle
-                    throttled = await login_throttle.purge_expired(db)
-                    if throttled:
-                        log.info("Purged %d expired sign-in counters", throttled)
-                    links = await purge_invite_links(db)
-                    if links:
-                        log.info("Purged %d spent or expired invitation links", links)
-            except asyncio.CancelledError:
-                raise
-            except Exception as e:
-                log.error("IP log cleanup failed: %s", e)
+            await run_cleanup(get_session)
             await asyncio.sleep(CLEANUP_INTERVAL_HOURS * 3600)
     except asyncio.CancelledError:
         return