Review found the backfill's fourth predicate `AND email_verified = false` strands
exactly the subset that needs it most. The revoke does not invalidate the account's
outstanding verification token, so clicking the emailed link is the natural next
thing such a user does — and it sets email_verified=True while password_hash stays
NULL. That row then has no route back at all: reset ineligible, resend refuses an
already-verified address, set-password needs a session the account cannot obtain,
and nothing re-stamps it because the revoke itself is guarded on being unverified.
The remaining three predicates already pin the victim set exactly: registration and
the setup wizard always set a hash, every Google row carries a google_sub (adoption
assigns it before revoking), and nothing in the codebase ever clears google_sub — so
a local, non-demo, passwordless row can only have come from the revoke. Widening is
therefore safe, and it is the argument the docstring was already making while the
SQL contradicted it.
Also corrects that docstring: set-password is unreachable because the account cannot
authenticate at all, not because the epoch bump killed a session — an unverified
account is refused at login before a session is ever minted, so it never had one.
Two independent bugs made an email+password registration unrecoverable in prod.
The verification link did nothing. urlWithParams/clearParams rebuilt the URL as
pathname+query and dropped the fragment — but the one-time auth tokens ride the
fragment (#verify=/#reset=, SB3), and the providers that mirror nav params
(useUrlMirror in FeedFilters/Playlists/Plex/Channels) sit ABOVE the lazily-loaded
<App> in main.tsx. Their mount effect replaceState'd the token away before the auth
card's chunk had loaded, so the card read an empty hash: the link opened a bare
sign-in page, no request, no banner. Both helpers now carry the fragment over.
The account was then locked out for good. SEC-10 nulls password_hash when an
unverified account is attested (here: admin approval landing before the user could
verify — which the bug above made impossible). That is indistinguishable from a
Google-only account, and reset eligibility keyed off password_hash alone, so no
reset link was ever issued: no password to sign in with, and no way to get one.
users.password_revoked_at records the revocation, reset eligibility honours it, and
setting a password again (reset-confirm or set-password) clears it. An account that
never had a password stays ineligible on purpose — its owner adds one from Settings.
/auth/verify now also answers password_reset_required so the card can say why the
registered password stopped working, instead of leaving the user at the uniform
"Invalid email or password". Telling the holder of a valid verify token leaks
nothing: they have already proved they own the mailbox.
The 0078 migration backfills the stamp for rows carrying the exact lockout signature
(local, non-demo, no password, unverified), so existing victims recover unaided.
peter