Auto-Prune Never-Activated Auth Accounts

[b-at-neu]

Background

#437 creates a neon_auth.user row at email submit so that Neon dispatches the send.otp webhook. A consequence is that anyone who enters an email and never completes the OTP leaves behind a permanent unverified auth row — typos, bots, and abandoned sign-ups all accumulate.

This does not pollute business data. The admin users list reads prisma.user.findMany (prisma/data/users.ts:9), and a public User row is only created on first successful authentication. So this is housekeeping inside the auth directory, not a data-integrity problem.

An example already exists on dev: winkler.b+2@northeastern.edu, created 2026-06-26, emailVerified: false, no session.

Prune predicate

Remove neon_auth.user rows where all of the following hold:

• emailVerified = false
• no row in neon_auth.session for that user
• no public User row with a matching neonAuthId
• createdAt older than the retention window (suggest 7 days — confirm before implementing)

The third clause is load-bearing and must not be dropped: it protects pending admin invitations. #239 creates the public row immediately after the auth row and rolls back with deleteNeonAuthUser if that insert fails, so an admin-invited user always has a public row. An auth row without one can only be an abandoned self-service sign-up.

Deletion

Use deleteNeonAuthUser() from lib/auth/admin.ts. Do not issue DELETE against neon_auth directly — Neon manages that schema.

Trigger

Opportunistic rather than scheduled. Vercel Cron would require an API route, which this repo forbids.

• Persist a lastPrunedAt marker
• Claim the run atomically (UPDATE … WHERE lastPrunedAt < threshold RETURNING) so concurrent requests cannot double-run it
• Execute off the response path so no user absorbs the latency. Next 16's after() is the natural primitive, but it is not covered in .claude/docs/nextjs-notes.md — verify current behaviour before relying on it
• Host it on a low-traffic authenticated path such as an admin page load, not resolveRealUser, which runs on nearly every request

Acceptance criteria

• Abandoned auth rows older than the retention window are removed
• Pending admin invitations are never removed
• Verified users, and any user with a session, are never removed
• Concurrent requests execute the prune at most once per window
• The triggering request shows no measurable added latency

[b-at-neu]

Obsolete as written. This was scoped against Neon Auth's account model; #461 replaced it with self-hosted Better Auth tables, so the accounts this would prune no longer exist in the same shape. If pruning is still wanted it needs a fresh ticket written against the Better Auth schema.