The short version
Supabase stores auth in a separate auth schema not your public schema. Every user has exactly one row in auth.users, one or more rows in auth.identities (one per login provider), and can hold multiple active auth.sessions, each backed by a refresh_tokens row. If you've ever poked at a Supabase database and seen a cluster of tables you didn't create, these are the ones, and this is what they do.
auth.users 1───* auth.identities
auth.users 1───* auth.sessions
auth.sessions 1───* auth.refresh_tokens
Why an auth schema at all
Supabase deliberately keeps auth separate from your app's tables. Your public schema is where your own models live; auth is locked down and managed by the auth service (GoTrue). You read from it, you don't write to it. That separation is why your migrations never touch these tables and why introspecting a Supabase database shows you a schema you didn't write.
The four tables that matter
auth.users one row per user.
| Column | What it holds |
|---|---|
id |
UUID primary key; the user's stable identifier |
email / phone
|
Contact + login identity |
encrypted_password |
Bcrypt hash (only for email/password auth) |
raw_app_meta_data |
Provider claims: provider, providers, email
|
raw_user_meta_data |
Custom metadata you set on the user |
is_sso_user |
True when sign-in came through SSO |
confirmed_at |
When the primary identity was confirmed |
auth.identities - one row per login method.
A user signing in with email and GitHub gets two rows here. provider_id is the provider's identifier for that user; identity_data is the raw claim payload (name, avatar, email) the provider returned. The FK user_id → users.id is what joins them.
auth.sessions - a browser/token session.
One session per logged-in device roughly. aal (assurance level), user_agent, ip, and not_after all live here. factor_id links to MFA factors when you have TOTP enabled.
auth.refresh_tokens - the long-lived token backing a session.
Access tokens are short (JWT, ~1h). Refresh tokens are long and stored here, revoked flag included, with parent used to detect token reuse and rotate.
How they relate (the joins you'll actually write)
select u.email, i.provider, s.id as session_id
from auth.users u
join auth.identities i on i.user_id = u.id
join auth.sessions s on s.user_id = u.id;
That query is the 80% case: "who is signed in, through which provider, on which sessions." Every relationship is a plain foreign key identities.user_id, sessions.user_id, refresh_tokens.session_id which is exactly what a schema diagram turns into readable arrows. If you'd rather trace them visually, paste a read-only connection string into dbdiagramr and it will pull and render the same tables.
What's usually NOT your business
auth.instances, auth.audit_log_entries, and auth.schema_migrations are Supabase's own bookkeeping. audit_log_entries records admin actions inside the auth service; instances is leftover multi-tenancy plumbing. If a diagram shows them, ignore them your reads live in the other four.
Practical tips
-
Never write to
authtables directly. Use the Supabase client / Admin API. Direct inserts create inconsistent state (orphaned identities, unhashed passwords). -
Foreign-key joins work across schemas.
auth.users.idis the same UUID you'd use inpublicjoinpublic.profiles.user_id → auth.users.idfor your own profile data. -
A user with no identity row is a sign of trouble. Every normal user has at least one. Orphaned
identitieswithout ausersrow point at a cleanup/import bug. - Sessions accumulate. Old sessions linger; your diagram showing many sessions per user isn't a leak, it's devices and tabs.
This whole picture is easier to keep straight when you can see it. The Supabase auth schema diagram was generated by introspecting a live Supabase database users, identities, sessions, and refresh_tokens with their foreign keys rendered as relationships. Open it next time you're debugging a sign-in flow.
FAQ
Where is the Supabase auth schema?
In the auth schema, separate from your public schema auth.users, auth.identities, auth.sessions, auth.refresh_tokens, plus internal tables.
What is auth.users used for?
It's the single source of truth for who can sign in. One row per user, with email/phone, password hash, metadata, and provider flags.
Why does one user have multiple identities?
Each login method is a separate auth.identities row. Email + GitHub + Google = three rows, all pointing at the same users.id.
What's the difference between a session and a refresh token?
A session is the device/token context; the refresh token is the long-lived credential that renews the short-lived JWT. One session maps to one refresh token chain.
Can I join auth tables to my public tables?
Yes the auth schema isn't isolated for queries. Use auth.users.id as the join key with your public.* tables.
Try It
Live: https://dbdiagramr.space
GitHub: https://github.com/VarunKvK/dbdiagramr
If this is useful to you, a GitHub star helps a solo dev keep building in public. I started this tool because I kept needing to visualize exactly these tables after reading migrations.
