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.

1 Like