Supabase Auth Schema Explained: Users, Identities, Sessions

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 auth tables directly. Use the Supabase client / Admin API. Direct inserts create inconsistent state (orphaned identities, unhashed passwords).
• Foreign-key joins work across schemas. auth.users.id is the same UUID you'd use in public join public.profiles.user_id → auth.users.id for your own profile data.
• A user with no identity row is a sign of trouble. Every normal user has at least one. Orphaned identities without a users row 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.