← All schemas
Schema Library

Better Auth Database Schema Diagram

Better Auth persists authentication in four core tables. The user table holds identity and profile fields; session rows point at users and carry the token, expiry, and device info; account rows link one authentication method (one provider) to a user with tokens and an optional password for credential logins; verification rows hold one-time values for email verification and password resets. The design detail that matters: account identity is the (providerId, accountId) pair - the local id is only the row identifier you pass to account-management APIs, never the provider identity.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Better Auth core schema docs v1.7.12 (better-auth.com/docs/concepts/database) · License: MIT

Tables
4
Core table
user
Identity
(providerId, accountId)
Version
1.7.12
Better Auth database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/better-auth">
  <img src="https://www.dbdiagramr.space/schemas/better-auth-er-diagram.svg" alt="Better Auth Database Schema Diagram (PostgreSQL)" width="933" height="790" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/better-auth">Better Auth Database Schema Diagram (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Better Auth schema

ColumnTypeNullableKey
user
idtextNoPK
nametextNo-
emailtextNo-
emailVerifiedbooleanNo-
imagetextYes-
createdAttimestamptzNo-
updatedAttimestamptzNo-
session
idtextNoPK
userIdtextNoFK → user.id
tokentextNo-
expiresAttimestamptzNo-
ipAddresstextYes-
userAgenttextYes-
createdAttimestamptzNo-
updatedAttimestamptzNo-
account
idtextNoPK
userIdtextNoFK → user.id
accountIdtextNo-
providerIdtextNo-
accessTokentextYes-
refreshTokentextYes-
accessTokenExpiresAttimestamptzYes-
refreshTokenExpiresAttimestamptzYes-
scopetextYes-
idTokentextYes-
passwordtextYes-
createdAttimestamptzNo-
updatedAttimestamptzNo-
verification
idtextNoPK
identifiertextNo-
valuetextNo-
expiresAttimestamptzNo-
createdAttimestamptzNo-
updatedAttimestamptzNo-

Table relationships

2 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

PostgreSQL requires double quotes around the reserved table name user and around camelCase columns such as userId and expiresAt.

One-to-many

session.userId → user.id

One user can have many sessions (session.userId -> user.id, required).

One-to-many

account.userId → user.id

One user can have many accounts (account.userId -> user.id, required).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Sessions per user

List unexpired sessions with the user and device details.

select s.id, u.email, s."expiresAt", s."ipAddress", s."userAgent"
from session s
join "user" u on u.id = s."userId"
where s."expiresAt" > now()
order by s."expiresAt" desc;

Accounts per provider

Show which sign-in provider each account uses.

select u.email, a."providerId", a."accountId"
from account a
join "user" u on u.id = a."userId"
order by u.email, a."providerId";

Credential accounts

Find accounts that store a password hash for email/password login.

select u.email, a."providerId"
from account a
join "user" u on u.id = a."userId"
where a.password is not null;

Unexpired verification values

Read the one-time values used by email verification and password resets.

select identifier, value, "expiresAt"
from verification
where "expiresAt" > now()
order by "expiresAt";

Quoting the Better Auth table and column names

PostgreSQL folds unquoted identifiers to lowercase. user is a reserved keyword, and Better Auth's columns such as userId, createdAt, and expiresAt are camelCase. Always write "user", "userId", and "createdAt" in SQL.

select u.id, u.email, s."userId", s."expiresAt"
from "user" u
join session s on s."userId" = u.id;

If an ORM quotes identifiers for you, it handles this automatically. The Copy as SQL button on this page also quotes the reserved word and camelCase columns.

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →