isNewUser props in signIn() events in auth options returns true in certain condition when user is actually not new.

Environment

System:
OS: Windows 11 10.0.22631
CPU: (8) x64 Intel(R) Core(TM) i7-10510U CPU @ 1.80GHz
Memory: 3.76 GB / 15.84 GB
Binaries:
Node: 20.9.0 - C:\Program Files\nodejs\node.EXE
Yarn: 1.22.19 - ~\AppData\Roaming\npm\yarn.CMD
npm: 9.2.0 - C:\Program Files\nodejs\npm.CMD
pnpm: 8.12.1 - ~\AppData\Roaming\npm\pnpm.CMD
Browsers:
Edge: Chromium (120.0.2210.91)
Internet Explorer: 11.0.22621.1
npmPackages:
@auth/drizzle-adapter: ^0.3.9 => 0.3.9
next: ^14.0.4 => 14.0.4
next-auth: ^4.24.5 => 4.24.5
react: ^18.2.0 => 18.2.0

Reproduction URL

https://github.com/dewodt/guess-astro

Describe the issue

Short Description

I use NextAuth with Drizzle ORM adapter and two provider: Google oAuth and Email.
Here's my Auth Options Config.
Here's a production demo of my app Guess Astro

What the issue is?

The signIn() events property in auth options has a parameter with one of it's property isNewUser. So my project app uses two provider Email Provider and Google Provider, where I also enabled account linking. So different login method but same email will lead to the same account.

When user first sign in with Google oAuth, the app sent a welcome email (meaning isNewUser is true). Then, when I log out then sign in again using email (the same email with Google oAuth), the app didn't send a welcome email (meaning isNewUser is false). This is correct.

First welcome email after using Google oAuth (Expected ✅)

No welcome email after using Email (the welcome on the lowest row is the previous welcome email) (Expected ✅)

(For the next step, you must use different email from the last step, to detect different isNewUser state)
Here's the bug: When I flip the order: First, I sign in using email, the app sent a welcome email (isNewUser is true). Then i sign out then sign in again using oAuth (the same email with email login before), the app sent me a welcome email again (isNewUser is true). This is unexpected, since I've signed up my account with email before, so isNewUser should be false.

First welcome email after using Email (Expected ✅)

Second welcome email after using Google oAuth (Unexpected ❌)

What I've observe so far from Next Auth source code?

1. Database behavior

• Google oAuth then email
  When first sign in with oAuth, there's a new row in both users table and account table.
  
  
  Then when sign out then sign in using email, the database doesnt change

• Email then Google oAuth
  When first sign in with Email, there's only a new row in the users table. The account table is still empty.
  
  Then sign out then sign in with Google oAuth, there's a new row in the account table.
  

2. NextAuth core source code

So I immediately try to find code that is related to isNewUser in next-auth node modules. After reading the source code, I think here's the source of the bug (correct me if I'm wrong).

• The first behavior (Google oAuth then Email) works fine because after the user signed in with google, the user table and the account both filled with a new row (from the example before). Then when we try to sign in with email and based on library source code (I copy & paste it below) tries to find user by email using the adapter method. The users table exists so the user is found and isNewUser becomes false.

Adapter @auth/drizzle-adapter/src/lib/pg.ts

async getUserByEmail(data) {
  return await client
    .select()
    .from(users)
    .where(eq(users.email, data))
    .then((res) => res[0] ?? null)
},

NextAuth @auth/core/src/lib/actions/callback/handle-login.ts :

if (account.type === "email") {
  // If signing in with an email, check if an account with the same email address exists already
  const userByEmail = await getUserByEmail(profile.email)
  if (userByEmail) {
    // If they are not already signed in as the same user, this flow will
    // sign them out of the current session and sign them in as the new user
    if (user?.id !== userByEmail.id && !useJwtSession && sessionToken) {
      // Delete existing session if they are currently signed in as another user.
      // This will switch user accounts for the session in cases where the user was
      // already logged in with a different account.
      await deleteSession(sessionToken)
    }

    // Update emailVerified property on the user object
    user = await updateUser({ id: userByEmail.id, emailVerified: new Date() })
    await events.updateUser?.({ user })
  } else {
    const { id: _, ...newUser } = { ...profile, emailVerified: new Date() }
    // Create user account if there isn't one for the email address already
    user = await createUser(newUser)
    await events.createUser?.({ user })
    isNewUser = true
  }

  // Create new session
  session = useJwtSession
    ? {}
    : await createSession({
        sessionToken: generateSessionToken(),
        userId: user.id,
        expires: fromDate(options.session.maxAge),
      })

  return { session, user, isNewUser }
}

• The second behavior is not working as expected because after the user sign in email the only row that is added is to the users table, the account table is still empty. Then when the user tries to sign in with Google oAuth based on the library source code (I copy & paste below) the getUserByAccount returns null because account table is still empty so it will go to the else block. But in the first if in the else block (if (user)) the user value is null and thus returns the last part of the code (isNewUser: true)

Adapter @auth/drizzle-adapter/src/lib/pg.ts

async getUserByAccount(account) {
  const dbAccount =
    (await client
      .select()
      .from(accounts)
      .where(
        and(
          eq(accounts.providerAccountId, account.providerAccountId),
          eq(accounts.provider, account.provider)
        )
      )
      .leftJoin(users, eq(accounts.userId, users.id))
      .then((res) => res[0])) ?? null

  if (!dbAccount) {
    return null
  }

  return dbAccount.user
},

NextAuth @auth/core/src/lib/actions/callback/handle-login.ts :

// If signing in with OAuth account, check to see if the account exists already
const userByAccount = await getUserByAccount({
  providerAccountId: account.providerAccountId,
  provider: account.provider,
})
if (userByAccount) {
  if (user) {
    // If the user is already signed in with this account, we don't need to do anything
    if (userByAccount.id === user.id) {
      return { session, user, isNewUser }
    }
    // If the user is currently signed in, but the new account they are signing in
    // with is already associated with another user, then we cannot link them
    // and need to return an error.
    throw new OAuthAccountNotLinked(
      "The account is already associated with another user",
      { provider: account.provider }
    )
  }
  // If there is no active session, but the account being signed in with is already
  // associated with a valid user then create session to sign the user in.
  session = useJwtSession
    ? {}
    : await createSession({
        sessionToken: generateSessionToken(),
        userId: userByAccount.id,
        expires: fromDate(options.session.maxAge),
      })

  return { session, user: userByAccount, isNewUser }
} else {
  const { provider: p } = options as InternalOptions<"oauth" | "oidc">
  const { type, provider, providerAccountId, userId, ...tokenSet } = account
  const defaults = { providerAccountId, provider, type, userId }
  account = Object.assign(p.account(tokenSet) ?? {}, defaults)

  if (user) {
    // If the user is already signed in and the OAuth account isn't already associated
    // with another user account then we can go ahead and link the accounts safely.
    await linkAccount({ ...account, userId: user.id })
    await events.linkAccount?.({ user, account, profile })

    // As they are already signed in, we don't need to do anything after linking them
    return { session, user, isNewUser }
  }

  // If the user is not signed in and it looks like a new OAuth account then we
  // check there also isn't an user account already associated with the same
  // email address as the one in the OAuth profile.
  //
  // This step is often overlooked in OAuth implementations, but covers the following cases:
  //
  // 1. It makes it harder for someone to accidentally create two accounts.
  //    e.g. by signin in with email, then again with an oauth account connected to the same email.
  // 2. It makes it harder to hijack a user account using a 3rd party OAuth account.
  //    e.g. by creating an oauth account then changing the email address associated with it.
  //
  // It's quite common for services to automatically link accounts in this case, but it's
  // better practice to require the user to sign in *then* link accounts to be sure
  // someone is not exploiting a problem with a third party OAuth service.
  //
  // OAuth providers should require email address verification to prevent this, but in
  // practice that is not always the case; this helps protect against that.
  const userByEmail = profile.email
    ? await getUserByEmail(profile.email)
    : null
  if (userByEmail) {
    const provider = options.provider as OAuthConfig<any>
    if (provider?.allowDangerousEmailAccountLinking) {
      // If you trust the oauth provider to correctly verify email addresses, you can opt-in to
      // account linking even when the user is not signed-in.
      user = userByEmail
    } else {
      // We end up here when we don't have an account with the same [provider].id *BUT*
      // we do already have an account with the same email address as the one in the
      // OAuth profile the user has just tried to sign in with.
      //
      // We don't want to have two accounts with the same email address, and we don't
      // want to link them in case it's not safe to do so, so instead we prompt the user
      // to sign in via email to verify their identity and then link the accounts.
      throw new OAuthAccountNotLinked(
        "Another account already exists with the same e-mail address",
        { provider: account.provider }
      )
    }
  } else {
    // If the current user is not logged in and the profile isn't linked to any user
    // accounts (by email or provider account id)...
    //
    // If no account matching the same [provider].id or .email exists, we can
    // create a new account for the user, link it to the OAuth account and
    // create a new session for them so they are signed in with it.
    const { id: _, ...newUser } = { ...profile, emailVerified: null }
    user = await createUser(newUser)
  }
  await events.createUser?.({ user })

  await linkAccount({ ...account, userId: user.id })
  await events.linkAccount?.({ user, account, profile })

  session = useJwtSession
    ? {}
    : await createSession({
        sessionToken: generateSessionToken(),
        userId: user.id,
        expires: fromDate(options.session.maxAge),
      })

  return { session, user, isNewUser: true }
}

Thank you.

How to reproduce

Summary from what I described before:

1. Prepare two test emails/google account
2. First, go to the sign in page then sign in using Google oAuth. Check your email, you should see a welcome email. ✅
3. Then, sign out. After that, sign in again but this time using Email provider. Check your email, you shouldn't see a welcome email. ✅
4. With your different account, First sign in using email provider. Check your email, you should see a welcome email. ✅
5. Then sign out. After that, with the same email in step number 4, sign in using Google oAuth. Check your email, you should see a welcome email. ❌

Expected behavior

If the behavior is correct, in step number 5, we shouldn't receive a welcome email because the same email is previously used to sign in in step 4. So it doesn't make sense for isNewUser to be true in step 5. This is really an annoying behavior especially if you want to send a welcome email.

Start with @auth/core/src/lib/actions/callback/handle-login.ts and compare the email and OAuth branches described in the report, then inspect @auth/drizzle-adapter/src/lib/pg.ts for the related account lookups. Use the linked reproduction to verify both provider-ordering scenarios; done means isNewUser is false when the same existing user switches providers.