Never remove the last way to sign in

How to do it

List every passkey, password, provider login and second factor on one settings page, with the date each was added and last used. Count the remaining methods inside the same transaction as the removal. To link a provider, the person signs in to the existing account first, then connects it. Ask for fresh proof first, as ask again before changing how someone logs in says.

Example

const LAST_METHOD = 'You need at least one way to sign in. Add another before removing this one.';

export async function removeSignInMethod(db, userId, methodId) {
  await db.tx(async tx => {
    await tx.none('SELECT 1 FROM users WHERE id = $1 FOR UPDATE', [userId]);
    const { n } = await tx.one('SELECT count(*)::int AS n FROM sign_in_methods WHERE user_id = $1', [userId]);
    if (n <= 1) throw new ConflictError(LAST_METHOD);
    await tx.none('DELETE FROM sign_in_methods WHERE id = $1 AND user_id = $2', [methodId, userId]);
  });
}

LAST_METHOD = "You need at least one way to sign in. Add another before removing this one."

def remove_sign_in_method(conn, user_id: str, method_id: str) -> None:
    with conn.transaction():
        conn.execute("SELECT 1 FROM users WHERE id = %s FOR UPDATE", (user_id,))
        (count,) = conn.execute("SELECT count(*) FROM sign_in_methods WHERE user_id = %s", (user_id,)).fetchone()
        if count <= 1:
            raise Conflict(LAST_METHOD)
        conn.execute("DELETE FROM sign_in_methods WHERE id = %s AND user_id = %s", (method_id, user_id))

How to check

On a test account with one passkey only, call the remove endpoint for it. The answer is 409 and the passkey stays. Sign in with a new provider login whose email matches an existing account. No link happens until that account signs in.