How many tables does the Supabase auth schema have?

The core Supabase auth schema has 7 tables: users, identities, sessions, refresh_tokens, instances, audit_log_entries, and schema_migrations. GoTrue v2.197.0 also creates 20 optional tables for MFA, SSO/SAML, OAuth, WebAuthn, SCIM, and internal support.

How do users relate to identities in Supabase?

Every auth.users row can have multiple auth.identities rows linked by user_id. Each identity represents a sign-in method (email/password, Google, GitHub, etc.) for that user.

How do I export the Supabase auth schema?

Use the Supabase CLI for your public schema, then pg_dump for auth. The CLI dump filters managed schemas such as auth and storage, so it will not include the tables shown here.

What happens to sessions when a user signs out?

Supabase supports three scopes. The JavaScript client defaults to global, which ends every session for the user. local ends only the current session; others ends every other session. The affected sessions are removed, and refresh tokens for them are destroyed, but access tokens remain valid until they expire.

Can I join auth.users to my public tables?

Yes. auth.users.id is the user UUID. Store it in your public table, for example public.profiles.user_id, and join on that column.

← All schemas

Schema Library

Supabase Auth Schema Diagram

Every Supabase project ships with a built-in auth schema managed by GoTrue. This diagram maps the auth tables - auth.users, auth.identities, auth.sessions, and refresh_tokens - with the primary keys and foreign keys between them. It documents the built-in auth schema, not your app's public tables. To diagram your own Supabase project, connect it with the Supabase ER diagram tool.

Written by Varun Krishnan · Founder, dbdiagramr

Published Aug 2, 2026 · Updated Oct 23, 2026

Source: Supabase auth migrations v2.197.0 (supabase/auth) · License: Apache-2.0

Core tables

Schema

auth

Version

GoTrue 2.197.0

Core auth tables from Supabase Auth (GoTrue) v2.197.0, released 2026-09-09.

Omitted here: MFA, SSO/SAML, OAuth, WebAuthn, and SCIM tables (20 optional tables in the full schema). source migrations.

This page documents Supabase's built-in auth schema, not your app's public tables. To diagram your own Supabase project and see Row Level Security status, use the Supabase ER diagram tool.

Supabase database schema diagram showing every table, column, and foreign key

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

Download SVG Download 2x PNG

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/supabase">
  <img src="https://www.dbdiagramr.space/schemas/supabase-er-diagram.svg" alt="Supabase Auth Schema Diagram: Users, Identities, Sessions" width="1469" height="1944" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/supabase">Supabase Auth Schema Diagram: Users, Identities, Sessions</a> by dbdiagramr</p>

Tables in the Supabase schema

Column	Type	Nullable	Key
users
instance_id	uuid	Yes	-
id	uuid	No	PK
aud	varchar(255)	Yes	-
role	varchar(255)	Yes	-
email	varchar(255)	Yes	-
encrypted_password	varchar(255)	Yes	-
email_confirmed_at	timestamptz	Yes	-
invited_at	timestamptz	Yes	-
confirmation_token	varchar(255)	Yes	-
confirmation_sent_at	timestamptz	Yes	-
recovery_token	varchar(255)	Yes	-
recovery_sent_at	timestamptz	Yes	-
email_change_token_new	varchar(255)	Yes	-
email_change	varchar(255)	Yes	-
email_change_sent_at	timestamptz	Yes	-
last_sign_in_at	timestamptz	Yes	-
raw_app_meta_data	jsonb	Yes	-
raw_user_meta_data	jsonb	Yes	-
is_super_admin	bool	Yes	-
created_at	timestamptz	Yes	-
updated_at	timestamptz	Yes	-
phone	text	Yes	-
phone_confirmed_at	timestamptz	Yes	-
phone_change	text	Yes	-
phone_change_token	varchar(255)	Yes	-
phone_change_sent_at	timestamptz	Yes	-
confirmed_at	timestamptz	Yes	-
email_change_token_current	varchar(255)	Yes	-
email_change_confirm_status	smallint	Yes	-
banned_until	timestamptz	Yes	-
reauthentication_token	varchar(255)	Yes	-
reauthentication_sent_at	timestamptz	Yes	-
is_sso_user	bool	No	-
deleted_at	timestamptz	Yes	-
is_anonymous	bool	No	-
identities
provider_id	text	No	-
user_id	uuid	No	FK → users.id
identity_data	jsonb	No	-
provider	text	No	-
last_sign_in_at	timestamptz	Yes	-
created_at	timestamptz	Yes	-
updated_at	timestamptz	Yes	-
email	text	Yes	-
id	uuid	No	PK
sessions
id	uuid	No	PK
user_id	uuid	No	FK → users.id
created_at	timestamptz	Yes	-
updated_at	timestamptz	Yes	-
factor_id	uuid	Yes	-
aal	aal_level	Yes	-
not_after	timestamptz	Yes	-
refreshed_at	timestamp	Yes	-
user_agent	text	Yes	-
ip	inet	Yes	-
tag	text	Yes	-
oauth_client_id	uuid	Yes	-
refresh_token_hmac_key	text	Yes	-
refresh_token_counter	bigint	Yes	-
scopes	text	Yes	-
refresh_tokens
instance_id	uuid	Yes	-
id	bigint	No	PK
token	varchar(255)	Yes	-
user_id	varchar(255)	Yes	-
revoked	bool	Yes	-
created_at	timestamptz	Yes	-
updated_at	timestamptz	Yes	-
parent	varchar(255)	Yes	-
session_id	uuid	Yes	FK → sessions.id
instances
id	uuid	No	PK
uuid	uuid	Yes	-
raw_base_config	text	Yes	-
created_at	timestamptz	Yes	-
updated_at	timestamptz	Yes	-
audit_log_entries
instance_id	uuid	Yes	-
id	uuid	No	PK
payload	json	Yes	-
created_at	timestamptz	Yes	-
ip_address	varchar(64)	No	-
schema_migrations
version	varchar(255)	No	PK

Table relationships

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

sessions.oauth_client_id references auth.oauth_clients. That table is part of the optional OAuth group, so the column is shown here without a foreign-key line.

One-to-many

identities.user_id → users.id

One user can have many identities (identities.user_id -> users.id, required).

One-to-many

sessions.user_id → users.id

One user can have many sessions (sessions.user_id -> users.id, required).

One-to-many

refresh_tokens.session_id → sessions.id

One session can have many refresh tokens (refresh_tokens.session_id -> sessions.id, optional).

Relationship	Type	Meaning
identities.user_id → users.id	One-to-many	One user can have many identities (identities.user_id -> users.id, required).
sessions.user_id → users.id	One-to-many	One user can have many sessions (sessions.user_id -> users.id, required).
refresh_tokens.session_id → sessions.id	One-to-many	One session can have many refresh tokens (refresh_tokens.session_id -> sessions.id, optional).

Common queries

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

Sign-ins by provider

Join each user to their identities and sessions.

select u.email, i.provider, s.created_at
from users u
join identities i on i.user_id = u.id
join sessions s on s.user_id = u.id
order by s.created_at desc
limit 50;

Current sessions

List sessions that have not passed their not_after cutoff.

select s.id, u.email, s.aal, s.oauth_client_id, s.scopes
from sessions s
join users u on u.id = s.user_id
where s.not_after is null or s.not_after > now()
order by s.created_at desc;

Refresh token lineage

Trace each refresh token to its session and parent token.

select rt.id, rt.session_id, s.user_id, rt.parent, rt.revoked
from refresh_tokens rt
left join sessions s on s.id = rt.session_id
order by rt.id desc
limit 100;

Users without an identity

Find auth users that have no sign-in method linked.

select u.id, u.email
from users u
left join identities i on i.user_id = u.id
where i.id is null;

How to download or export your Supabase schema

The Supabase CLI dump is the quickest way to export your app schema:

supabase db dump --db-url "$SUPABASE_SESSION_POOLER_URL" -f supabase/schema.sql

The CLI filters Supabase-managed schemas, including auth and storage, so the file above will not contain the auth tables on this page. Dump auth separately with pg_dump:

pg_dump "$SUPABASE_SESSION_POOLER_URL" --schema-only --schema=auth --no-owner --no-privileges -f auth-schema.sql

Use the session pooler connection string on port 5432 for these commands. Use a pg_dump version at least as new as the PostgreSQL server version: newer pg_dump releases can dump older servers, but older ones cannot dump newer servers. See the Supabase CLI db dump reference and the PostgreSQL pg_dump documentation.

How the auth tables change during sign-in

These tables are written by Supabase Auth, not by your migrations. The main events look like this:

Event	What changes
Sign-up	A row is created in `auth.users`. If email confirmations are enabled, the user must confirm the email before signing in (password auth docs).
Identity link	Each sign-in method gets a row in `auth.identities`, linked by `user_id` (identities docs).
Sign-in	A row is created in `auth.sessions`. The access token carries a `session_id` claim matching the session primary key (sessions docs).
Token refresh	Refresh tokens are one-time use. Reuse outside the documented grace cases terminates the session and revokes all refresh tokens for it (sessions docs).
Sign-out	`global` (the JavaScript default) ends all sessions; `local` ends the current session; `others` ends all but the current one. Affected sessions are removed and their refresh tokens are destroyed. Access tokens stay valid until they expire (sign-out docs).

Supabase does not document the exact row-level timing for every internal write, including changes to auth.refresh_tokens. Treat those rows as managed state rather than an application event log.

How users, identities, and sessions connect

The common query is "who is this user, through which provider, and on which sessions":

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
where u.id = $1;

auth.identities.user_id and auth.sessions.user_id both point at auth.users.id. Your public profile table should use the same UUID, for example public.profiles.user_id references auth.users(id), so a profile joins directly to the auth user. A normal user has at least one identity; sessions are per sign-in and can be many.

Working with auth tables safely

Treat auth as read-only. Create users, identities, and sessions through the Supabase client or Admin API, not with direct inserts.
Do not store passwords yourself. The password flow writes a hash into the managed auth tables; direct writes bypass that handling.
Keep the secret key on the server. The secret key bypasses Row Level Security, so it must never reach a browser.
Use auth.uid() in RLS policies. It returns the current user's UUID, which is the value your public tables should reference.
Export schema, not data. Use --schema-only when the dump is for documentation or review.