Can we make our auth schema better?

• In our current setup, OAuth providers are required to have dummy usernames and passwords
• Adding new type of providers (magic link e.g.) would require changes to the schema, can it be more flexibile?
• If the auth entities are hidden, changing the auth settings shouldn’t require Prisma migrations (?)
• We are exploring to migrate to Lucia as our auth SDK, we should adjust our schema to work with Lucia (they have sessions table)

<aside> ➡️ We’ll go through a few auth schema possibilities. For each schema, I’ve written the Prisma models and some example JSON objects (click on the triangle to expand).

I’ve then listed out some pros 🟩 and cons 🟨, feel free to edit them.

</aside>

Just dump everything in a single place

Let’s start with the first approach where all the needed fields are stored on the Auth entity. In the future, if would need extra fields, we would add them there as well.

model Auth {
  id                      String               @id @default(uuid())

  // Both email and username are here and both are optional (read more below)
  email                   String?              @unique
  username                String?              @unique
  password                String?
  isEmailVerified         Boolean              @default(false)
  emailVerificationSentAt DateTime?
  passwordResetSentAt     DateTime?

	// Connection to possible social auth providers
  providers               OAuth2Provider[]

	// Connection to the business logic user
  userId                  String?              @unique
	user                    User?                @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model OAuth2Provider {
  id         String @id @default(uuid())

  provider   String
  providerId String

  authId     String
  auth       Auth   @relation(fields: [authId], references: [id], onDelete: Cascade)
}

• Examples

Pros 🟩

• It’s simple
• This kitchen sink approach is used by some of the other auth solutions successfully (Supabase)

Cons 🟨

• We have empty fields
• The types need to be coerced based on the auth method (if the email auth is used, then we know email is required and username doesn’t exist)
• Email & username have a special structure vs. OAuth2 providers

Let’s try making all providers uniform (using JSON fields)

model Auth {
  id                      String               @id @default(uuid())

	// Connection to possible auth providers
  identities              AuthIdentity[]

	// Connection to the business logic user
  userId                  String?              @unique
	user                    User?                @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model AuthIdentity {
  providerName     String
  providerUserId   String

	// The bread and butter of this idea: dynamic JSON field
	providerData Json

  authId       String
  auth         Auth   @relation(fields: [authId], references: [id], onDelete: Cascade)

	@@id([providerName, providerUserId])
}

• Examples