Core tables
users, oauth_accounts, sessions
Auth example
OAuth Login ERD Example
An OAuth login ERD example for users, OAuth accounts, sessions, email verification, and password resets.
Scenario
OAuth Login ERD Example
An authentication model that supports both email/password login and Google OAuth login.
Key relation
users 1:N oauth_accounts
Main caution
Never store raw tokens
Requirements
Requirements
- A user can connect multiple OAuth provider accounts.
- Login sessions must support expiration and revocation.
- Verification and reset tokens should be stored as hashes only.
Table design
Table design
- id PK
- email UNIQUE
- name
- email_verified_at
- password_hash
- id PK
- user_id FK
- provider
- provider_user_id
- linked_at
- id PK
- user_id FK
- token_hash
- expires_at
- revoked_at
- id PK
- user_id FK
- token_hash
- expires_at
- used_at
- id PK
- user_id FK
- token_hash
- expires_at
- used_at
Relationships
Relationships
users 1:N oauth_accountsusers 1:N sessionsusers 1:N email_verificationsusers 1:N password_resets
Design notes
Design notes
Make provider identity unique as a pair
Use a unique constraint on provider and provider_user_id rather than provider_user_id alone.
Store session token hashes
Hash session tokens before storing them so a database leak cannot directly replay sessions.
Split auth events from audit logs
For operations, keep login and account-link events in audit_events without mixing them with credential tables.
Implementation checks
Implementation checks
- Add UNIQUE(provider, provider_user_id) on oauth_accounts.
- Index sessions.token_hash but never store the raw token.
- Clean up expired verification and reset tokens periodically.
More ERD examples
More ERD examples
Use these structures as a starting point, or open a demo canvas before creating an account.