Database Schema#

AuthHero uses PostgreSQL as its primary database, managed through Prisma 7 (with the new PrismaPg driver adapter). The schema contains 6 models that cover users, sessions, email verification, password resets, OAuth accounts, and MFA secrets.

Entity Relationship Overview#

Relationships

User (1) ─── (many) Session
User (1) ─── (many) EmailVerification
User (1) ─── (many) PasswordReset
User (1) ─── (many) OAuthAccount
User (1) ─── (0..1) MFASecret

All relations use onDelete: Cascade — when a user is deleted, all their related records are automatically removed.

User#

The core user model. Supports both email/password and OAuth-only users.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	gen_random_uuid()
`fullname`	String	Required	User's display name
`email`	String	Unique	Login identifier; always lowercase
`passwordHash`	String?	Nullable	Null for OAuth-only users. Argon2 hash.
`emailVerified`	Boolean	Default: false	Flipped after email verification
`mfaEnabled`	Boolean	Default: false	Flipped after first successful TOTP verify
`createdAt`	Timestamp(6)	Auto	Record creation time
`updatedAt`	Timestamp(6)	Auto	Last update time

💡OAuth-only users

When a user signs up exclusively via OAuth (Google, GitHub, etc.),passwordHash is null. These users cannot use the password login flow until they set a password (if such functionality is added).

Session#

Represents an active or revoked user session. Each session maps to one refresh token.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Session identifier
`userId`	UUID	FK → User	Owner of the session
`refreshTokenHash`	String	Unique, Indexed	SHA-256 hash of the refresh token
`expiresAt`	DateTime	Required	When the session expires (30 days)
`revokedAt`	DateTime?	Nullable	Set when session is revoked (logout / reuse detection)
`lastRotatedAt`	DateTime?	Nullable	Updated on each token refresh
`userAgent`	String?	Nullable	Browser / client user-agent
`ipAddress`	String?	Nullable	Client IP address
`createdAt`	Timestamp(6)	Auto	Session creation time

Indexes:

@@index([refreshTokenHash, userId]) — fast lookup during refresh

⚠️Refresh token rotation

The refresh token is never stored in plaintext — only its SHA-256 hash is persisted. When a token is rotated, the old hash is replaced with the new one. If the old token is reused (detected by hash mismatch + valid session), all sessions for that user are revoked immediately.

EmailVerification#

Tracks email verification tokens sent to users during registration.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User	User this token belongs to
`tokenHash`	String	Indexed	SHA-256 hash of verification token
`expiresAt`	DateTime	Required	Token expires after 10 minutes
`usedAt`	DateTime?	Nullable	Set when token is consumed
`createdAt`	Timestamp(6)	Auto	Record creation time

Indexes:

@@index([tokenHash]) — fast token lookup
@@index([userId, tokenHash]) — compound lookup for user + token

PasswordReset#

Tracks password reset tokens sent via the forgot-password flow.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User	User this token belongs to
`tokenHash`	String	Indexed	SHA-256 hash of reset token
`expiresAt`	DateTime	Required	Token expires after 15 minutes
`usedAt`	DateTime?	Nullable	Set when token is consumed
`createdAt`	Timestamp(6)	Auto	Record creation time

Indexes:

@@index([tokenHash]) — fast token lookup
@@index([userId, tokenHash]) — compound lookup

OAuthAccount#

Links external OAuth providers to a user account. A user can have multiple OAuth accounts (e.g., Google + GitHub).

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User, Indexed	Owner of this OAuth link
`provider`	String	Part of unique compound	Provider name: google, github, facebook
`providerUserId`	String	Part of unique compound	User ID from the OAuth provider
`createdAt`	Timestamp(6)	Auto	When the link was created

Constraints:

@@unique([provider, providerUserId]) — ensures the same provider account can't be linked to multiple AuthHero users
@@index([userId]) — fast lookup by user

💡Account linking

If a user registers with email/password and later logs in via Google with the same email, the OAuth account is linked to the existing user. This happens inside a Prisma transaction.

MFASecret#

Stores the encrypted TOTP secret and hashed backup codes. One-to-one relationship with User.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	Unique, FK → User	One MFA secret per user
`secretHash`	String	Required	AES-256-GCM encrypted TOTP secret (not a hash — encrypted so it can be decrypted for verification)
`backupCodes`	String[]	Required	Argon2 hashes of the 8 backup codes
`verified`	Boolean	Default: false	True after user confirms setup with a valid TOTP code
`enabledAt`	DateTime?	Nullable	Timestamp when MFA was fully enabled
`createdAt`	Timestamp(6)	Auto	Record creation time

⚠️secretHash naming

Despite the name secretHash, this field contains an AES-256-GCM encrypted value (not a one-way hash). This is necessary because the TOTP secret must be decrypted at runtime to verify codes. The encryption key is MFA_ENCRYPTION_KEY.

Running Migrations#

Prisma manages database migrations. Common commands:

Terminal

# Apply all pending migrations (use in development)
npx prisma migrate dev

# Apply migrations in production (non-interactive)
npx prisma migrate deploy

# Create a new migration after changing schema.prisma
npx prisma migrate dev --name your_migration_name

# Reset the database (drops all data + re-applies migrations)
npx prisma migrate reset

# Open Prisma Studio (visual database browser)
npx prisma studio

Full Schema#

The complete Prisma schema is located at prisma/schema.prisma:

prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

model User {
  id            String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  fullname      String
  email         String  @unique
  passwordHash  String? // null for OAuth-only users
  emailVerified Boolean @default(false)
  mfaEnabled    Boolean @default(false)

  createdAt DateTime @default(now()) @db.Timestamp(6)
  updatedAt DateTime @updatedAt @db.Timestamp(6)

  sessions           Session[]
  emailVerifications EmailVerification[]
  passwordResets     PasswordReset[]
  oauthAccounts      OAuthAccount[]
  mfaSecrets         MFASecret?
}

model Session {
  id               String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId           String    @db.Uuid
  refreshTokenHash String    @unique
  expiresAt        DateTime
  revokedAt        DateTime?
  lastRotatedAt    DateTime?
  userAgent        String?
  ipAddress        String?
  createdAt        DateTime  @default(now()) @db.Timestamp(6)
  user             User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([refreshTokenHash, userId])
}

model EmailVerification {
  id        String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId    String    @db.Uuid
  tokenHash String
  expiresAt DateTime
  usedAt    DateTime?
  createdAt DateTime  @default(now()) @db.Timestamp(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([tokenHash])
  @@index([userId, tokenHash])
}

model PasswordReset {
  id        String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId    String    @db.Uuid
  tokenHash String
  expiresAt DateTime
  usedAt    DateTime?
  createdAt DateTime  @default(now()) @db.Timestamp(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([tokenHash])
  @@index([userId, tokenHash])
}

model OAuthAccount {
  id             String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId         String   @db.Uuid
  provider       String
  providerUserId String
  createdAt      DateTime @default(now()) @db.Timestamp(6)
  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerUserId])
  @@index([userId])
}

model MFASecret {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId      String    @unique @db.Uuid
  secretHash  String
  backupCodes String[]
  verified    Boolean   @default(false)
  enabledAt   DateTime?
  createdAt   DateTime  @default(now()) @db.Timestamp(6)
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Prisma Configuration#

AuthHero uses the PrismaPg driver adapter for optimal PostgreSQL performance. The Prisma client is initialized in src/config/prisma.ts:

src/config/prisma.ts

import { PrismaClient } from "../generated/prisma";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter });

The driver adapter approach means Prisma communicates directly through the pg driver rather than its default engine binary, which can reduce overhead in production containers.

Database Schema#

Entity Relationship Overview#

Relationships

User (1) ─── (many) Session
User (1) ─── (many) EmailVerification
User (1) ─── (many) PasswordReset
User (1) ─── (many) OAuthAccount
User (1) ─── (0..1) MFASecret

All relations use onDelete: Cascade — when a user is deleted, all their related records are automatically removed.

User#

The core user model. Supports both email/password and OAuth-only users.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	gen_random_uuid()
`fullname`	String	Required	User's display name
`email`	String	Unique	Login identifier; always lowercase
`passwordHash`	String?	Nullable	Null for OAuth-only users. Argon2 hash.
`emailVerified`	Boolean	Default: false	Flipped after email verification
`mfaEnabled`	Boolean	Default: false	Flipped after first successful TOTP verify
`createdAt`	Timestamp(6)	Auto	Record creation time
`updatedAt`	Timestamp(6)	Auto	Last update time

💡OAuth-only users

When a user signs up exclusively via OAuth (Google, GitHub, etc.),passwordHash is null. These users cannot use the password login flow until they set a password (if such functionality is added).

Session#

Represents an active or revoked user session. Each session maps to one refresh token.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Session identifier
`userId`	UUID	FK → User	Owner of the session
`refreshTokenHash`	String	Unique, Indexed	SHA-256 hash of the refresh token
`expiresAt`	DateTime	Required	When the session expires (30 days)
`revokedAt`	DateTime?	Nullable	Set when session is revoked (logout / reuse detection)
`lastRotatedAt`	DateTime?	Nullable	Updated on each token refresh
`userAgent`	String?	Nullable	Browser / client user-agent
`ipAddress`	String?	Nullable	Client IP address
`createdAt`	Timestamp(6)	Auto	Session creation time

Indexes:

@@index([refreshTokenHash, userId]) — fast lookup during refresh

⚠️Refresh token rotation

EmailVerification#

Tracks email verification tokens sent to users during registration.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User	User this token belongs to
`tokenHash`	String	Indexed	SHA-256 hash of verification token
`expiresAt`	DateTime	Required	Token expires after 10 minutes
`usedAt`	DateTime?	Nullable	Set when token is consumed
`createdAt`	Timestamp(6)	Auto	Record creation time

Indexes:

@@index([tokenHash]) — fast token lookup
@@index([userId, tokenHash]) — compound lookup for user + token

PasswordReset#

Tracks password reset tokens sent via the forgot-password flow.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User	User this token belongs to
`tokenHash`	String	Indexed	SHA-256 hash of reset token
`expiresAt`	DateTime	Required	Token expires after 15 minutes
`usedAt`	DateTime?	Nullable	Set when token is consumed
`createdAt`	Timestamp(6)	Auto	Record creation time

Indexes:

@@index([tokenHash]) — fast token lookup
@@index([userId, tokenHash]) — compound lookup

OAuthAccount#

Links external OAuth providers to a user account. A user can have multiple OAuth accounts (e.g., Google + GitHub).

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	FK → User, Indexed	Owner of this OAuth link
`provider`	String	Part of unique compound	Provider name: google, github, facebook
`providerUserId`	String	Part of unique compound	User ID from the OAuth provider
`createdAt`	Timestamp(6)	Auto	When the link was created

Constraints:

@@unique([provider, providerUserId]) — ensures the same provider account can't be linked to multiple AuthHero users
@@index([userId]) — fast lookup by user

💡Account linking

If a user registers with email/password and later logs in via Google with the same email, the OAuth account is linked to the existing user. This happens inside a Prisma transaction.

MFASecret#

Stores the encrypted TOTP secret and hashed backup codes. One-to-one relationship with User.

Column	Type	Constraints	Description
`id`	UUID	PK, auto-generated	Record ID
`userId`	UUID	Unique, FK → User	One MFA secret per user
`secretHash`	String	Required	AES-256-GCM encrypted TOTP secret (not a hash — encrypted so it can be decrypted for verification)
`backupCodes`	String[]	Required	Argon2 hashes of the 8 backup codes
`verified`	Boolean	Default: false	True after user confirms setup with a valid TOTP code
`enabledAt`	DateTime?	Nullable	Timestamp when MFA was fully enabled
`createdAt`	Timestamp(6)	Auto	Record creation time

⚠️secretHash naming

Running Migrations#

Prisma manages database migrations. Common commands:

Terminal

# Apply all pending migrations (use in development)
npx prisma migrate dev

# Apply migrations in production (non-interactive)
npx prisma migrate deploy

# Create a new migration after changing schema.prisma
npx prisma migrate dev --name your_migration_name

# Reset the database (drops all data + re-applies migrations)
npx prisma migrate reset

# Open Prisma Studio (visual database browser)
npx prisma studio

Full Schema#

The complete Prisma schema is located at prisma/schema.prisma:

prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

model User {
  id            String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  fullname      String
  email         String  @unique
  passwordHash  String? // null for OAuth-only users
  emailVerified Boolean @default(false)
  mfaEnabled    Boolean @default(false)

  createdAt DateTime @default(now()) @db.Timestamp(6)
  updatedAt DateTime @updatedAt @db.Timestamp(6)

  sessions           Session[]
  emailVerifications EmailVerification[]
  passwordResets     PasswordReset[]
  oauthAccounts      OAuthAccount[]
  mfaSecrets         MFASecret?
}

model Session {
  id               String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId           String    @db.Uuid
  refreshTokenHash String    @unique
  expiresAt        DateTime
  revokedAt        DateTime?
  lastRotatedAt    DateTime?
  userAgent        String?
  ipAddress        String?
  createdAt        DateTime  @default(now()) @db.Timestamp(6)
  user             User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([refreshTokenHash, userId])
}

model EmailVerification {
  id        String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId    String    @db.Uuid
  tokenHash String
  expiresAt DateTime
  usedAt    DateTime?
  createdAt DateTime  @default(now()) @db.Timestamp(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([tokenHash])
  @@index([userId, tokenHash])
}

model PasswordReset {
  id        String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId    String    @db.Uuid
  tokenHash String
  expiresAt DateTime
  usedAt    DateTime?
  createdAt DateTime  @default(now()) @db.Timestamp(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([tokenHash])
  @@index([userId, tokenHash])
}

model OAuthAccount {
  id             String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId         String   @db.Uuid
  provider       String
  providerUserId String
  createdAt      DateTime @default(now()) @db.Timestamp(6)
  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerUserId])
  @@index([userId])
}

model MFASecret {
  id          String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId      String    @unique @db.Uuid
  secretHash  String
  backupCodes String[]
  verified    Boolean   @default(false)
  enabledAt   DateTime?
  createdAt   DateTime  @default(now()) @db.Timestamp(6)
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Prisma Configuration#

AuthHero uses the PrismaPg driver adapter for optimal PostgreSQL performance. The Prisma client is initialized in src/config/prisma.ts:

src/config/prisma.ts

import { PrismaClient } from "../generated/prisma";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({ connectionString: env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter });

The driver adapter approach means Prisma communicates directly through the pg driver rather than its default engine binary, which can reduce overhead in production containers.