Database Concepts
Understanding the database structure and table relationships for the authentication system
The authentication system uses a simplified three-table structure to manage users, authentication accounts, and sessions.
Tables Overview
User Table
Stores core user information and profile data.
| Field | Type | Key | Description |
|---|---|---|---|
| id | varchar(24) | PK | Unique identifier for each user |
| name | varchar(100) | INDEX | User's display name (required) |
| varchar(255) | UNIQUE | User's email address (required for authentication) | |
| image | varchar(500) | URL to user's profile image | |
| created_at | timestamp | Timestamp when user account was created (auto-set) | |
| updated_at | timestamp | Timestamp when user data was last updated (auto-updated) |
Primary Key: id
Unique Index: email
Index: name
Account Table
Links users to their authentication providers (OAuth or credentials).
| Field | Type | Key | Description |
|---|---|---|---|
| id | varchar(24) | PK | Unique account row identifier |
| user_id | varchar(24) | FK, INDEX | References users.id - the user this account belongs to |
| provider | varchar(50) | Authentication provider name (google, github, discord, credentials) | |
| account_id | varchar(100) | Unique account ID from the provider or email for credentials | |
| password | text | Hashed password (only for credentials provider, null for OAuth) |
Primary Key: id
Foreign Key: user_id references users.id with cascade delete
Unique Index: (provider, account_id)
Index: user_id
Session Table
Manages user sessions for authentication state.
| Field | Type | Key | Description |
|---|---|---|---|
| id | varchar(24) | PK | Unique session row identifier |
| user_id | varchar(24) | FK, INDEX | References users.id - the user this session belongs to |
| token | varchar(64) | Unique session token identifying the session | |
| expires_at | timestamp | Timestamp when session expires | |
| ip_address | varchar(45) | IP address for the session (optional) | |
| user_agent | text | User agent string for the session (optional) |
Primary Key: id
Foreign Key: user_id references users.id with cascade delete
Unique Index: (id, token)
Index: user_id
Relationships
- User → Account: One-to-Many (a user can have multiple auth methods: OAuth + credentials)
- User → Session: One-to-Many (a user can have multiple active sessions across devices)
- Account → User: Many-to-One (each account belongs to one user)
- Session → User: Many-to-One (each session belongs to one user)
Key Features
- Cascade Deletes: When a user is deleted, all their accounts and sessions are automatically removed
- Unique Constraints: Prevent duplicate provider accounts and duplicate emails
- Indexes: Improve lookup performance for user, account, and session queries
- Auto-Update Timestamps: User updated_at field automatically updates on changes
Database Adapters
The schema is implemented across different ORMs:
- Drizzle: PostgreSQL schema with varchar and timestamp support
- Prisma: Cross-database schema with auto-generated types
- Mongoose: MongoDB schemas with document validation
Example SQL Schema
CREATE TABLE "users" (
"id" varchar(24) PRIMARY KEY NOT NULL,
"name" varchar(100) NOT NULL,
"email" varchar(255) NOT NULL,
"image" varchar(500),
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
CREATE TABLE "accounts" (
"id" varchar(24) PRIMARY KEY NOT NULL,
"user_id" varchar(24) NOT NULL,
"provider" varchar(50) NOT NULL,
"account_id" varchar(100) NOT NULL,
"password" text
);
CREATE TABLE "sessions" (
"id" varchar(24) PRIMARY KEY NOT NULL,
"user_id" varchar(24) NOT NULL,
"token" varchar(64) NOT NULL,
"expires_at" timestamp NOT NULL,
"ip_address" varchar(45),
"user_agent" text
);
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
CREATE INDEX "accounts_user_id_idx" ON "accounts" USING btree ("user_id");
CREATE UNIQUE INDEX "accounts_provider_account_id_uq_idx" ON "accounts" USING btree ("provider","account_id");
CREATE INDEX "sessions_user_id_idx" ON "sessions" USING btree ("user_id");
CREATE UNIQUE INDEX "sessions_id_token_uq_idx" ON "sessions" USING btree ("id","token");
CREATE INDEX "users_name_idx" ON "users" USING btree ("name");
CREATE UNIQUE INDEX "users_email_uq_idx" ON "users" USING btree ("email");