Best practices for external auth and db foreign-key to user?

Get Help and Help Others
1

In a traditional dbAuth scenario, the User table and tables with foreign key relationship to it all reside on the same server/cluster. However, when auth and db are 2 separate non-collocated services, that foreign key relationship is no longer handled automatically.

Initially, I can think of 2 ways to handle this.

  1. Use the uuid from the external auth service as a unique constraint, and manually manage the foreign key relationship wherever needed. For example:
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String

  // uuid for the user, provided by the external auth service like Netlify or
  authorId String! @unique
}
  1. Create a UserProfile model that is 1-to-1 with the set of users in the auth service, also by using a unique constraint. Then use this UserProfile essentially as a proxy for the user and have all foreign key relations point to UserProfile
model UserProfile {
  id String! @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  realUserId String! @unique
}

model Post {
  id        Int      @id @default(cuid())
  title     String
  body      String

  author      UserProfile     @relation(fields: [authorId], references: [id])
  authorId    Int
}

I’m wondering if anyone here have suggestion or experience to share on the matter. I have a feeling this is one of those small but highly consequential decisions for a web app.

2

I’m trying to work out the same thing so that I can hook Netlify Auth up to the User model.

I think that what I am going to try is to pull some sort of unique identifier from the netlify user object, and save that into my User model as a foreign key, something like:

model User {
   id                     String    @id @default(cuid())
   email                  String
   name                   String
   // yadda yadda yadda
   netifyUserId           String
}

The creating the user in api/src//lib/auth.ts a bit like in this thread:

export const getCurrentUser = async ({ name, email }) => {
  const user =
    (await db.user.findOne({
      where: { email },
    })) || (await createUser(name, email))
  return user
}

export const createUser = async (name, email) => {
  return await db.user.create({
    data: { name, email }
  })
}

The part I need to work out to test this is what property of the netlify identity user object I can pull out to act as the unique Id, (not keen on email since this can change)
I think there is a GUID in there somewhere, I can see that in the decoded object there is a field called sub containing a guid like string which I suspect is Netlify’s identifier for that user.

More digging and some testing required.

3

Regarding the reference to the netlify object, some useful info here too in case anyone else is trying to work this stuff out:

4

I had the same confusion while working with Superbase auth and ended up using the sub claim as id as well since there is nothing else that seem to qualify as id. It seems to be the right choice after a few weeks of use. It also makes sense that the “subject” of a user-authentication token is the user id.

For my application, I also found it to be more convenient to just use that sub id as the id of my User model (instead of having an additional supabaseId field). This way I don’t have to hit the database to lookup the user on every request since most of my requests don’t need any user-specific data other than the id.