DEV Community: Varun Krishnan

SQL to Schema Diagram Online: Convert SQL to ERD in Seconds

Varun Krishnan — Thu, 01 Oct 2026 14:30:00 +0000

The short version

You have SQL. You need a diagram. The fastest path from `CREATE TABLE` to a visual schema is a tool that parses SQL and renders an ER diagram on the spot.

[dbdiagramr](https://www.dbdiagramr.space/visualize) does exactly that -- paste your SQL, get an interactive diagram in seconds. No signup, no install, no export limits.

Why convert SQL to a schema diagram?

SQL is precise. Diagrams are comprehensible. When you're onboarding to a new codebase or explaining a schema to a non-technical stakeholder, a visual representation does something SQL can't: it shows relationships at a glance.

Reading 200 lines of `CREATE TABLE` statements, you'll find the foreign keys eventually. Seeing them drawn as lines between boxes takes half a second.

How to convert SQL to a schema diagram online

Step 1: Get your SQL

Grab your schema from one of these sources:


  - `pg_dump --schema-only yourdb`
  - Migration files (Prisma, Drizzle, Rails, Django)
  - Your IDE's schema export
  - A SQL file you already have

Step 2: Paste into dbdiagramr

Go to [dbdiagramr.com/visualize](https://www.dbdiagramr.space/visualize) and paste your SQL in the editor. The tool parses CREATE TABLE statements, detects primary keys, and maps foreign key relationships automatically.

Step 3: Explore and export

Your ER diagram appears instantly. Pan, zoom, drag tables around to arrange them. When you're happy, export as SVG or PNG with one click.

What SQL syntax works?

dbdiagramr understands standard PostgreSQL syntax:


  - `CREATE TABLE` with columns and types
  - `PRIMARY KEY` constraints
  - `REFERENCES` for foreign keys
  - `DEFAULT` values
  - `NOT NULL` and `UNIQUE` constraints
  - `ON DELETE` and `ON UPDATE` actions

If your SQL is valid PostgreSQL, dbdiagramr will parse it.

Example: converting a Laravel migration

If you're using Laravel, your migrations live in `database/migrations/`. Export them to SQL and paste the result:

-- Laravel migration exported to SQL
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  remember_token VARCHAR(100),
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ
);

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) UNIQUE NOT NULL,
  body TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ
);

dbdiagramr renders the `users` and `posts` tables with the foreign key relationship drawn between them. Done in seconds.

Alternative methods (and why they're slower)

    | pgAdmin ERD tool | Already installedMediumSnapshot, manual re-run |
    | Draw.io + manual | ShortSlowHand-draw every table |
    | dbdiagram.io | Account requiredFastMust learn DBML syntax |
    | dbdiagramr | NoneInstantPostgreSQL only (for now) |


dbdiagramr is the only option that requires zero setup and accepts raw SQL directly.

FAQ

**Can I convert MySQL SQL to a diagram?**Not yet. dbdiagramr currently supports PostgreSQL syntax only. MySQL support is planned.

**Does it work with Prisma schema files?**Not directly. Export your Prisma schema to SQL first with `prisma db pull` or `pg_dump`, then paste the SQL.

**How large a schema can it handle?**dbdiagramr handles schemas with dozens of tables comfortably. Very large schemas (100+ tables) may need some manual arrangement, but the parsing works fine.

**Is there a table limit?**No. Paste as many tables as you want. No signup, no limits.

Free Online Database Diagram Tool: Visualize Your Schema in Seconds

Varun Krishnan — Tue, 29 Sep 2026 14:30:00 +0000

The short version

You need a database diagram. You don't want to pay for it, sign up for another account, or install anything. A free online database diagram tool should let you paste SQL or connect to your database and get a visual schema in seconds.

Most tools claim to be free, then hit you with table limits, export restrictions, or require an email before you can do anything. The one that actually works the way you expect is [dbdiagramr](https://www.dbdiagramr.space) -- paste SQL or a PostgreSQL connection string, get an interactive ER diagram, export SVG/PNG. No signup, no limits, no catch.

What a good free database diagram tool should do

    | Paste SQL directly | You already have the schema -- just show it |
    | Live database connection | See what's actually deployed, not what you think is deployed |
    | Interactive diagram | Pan, zoom, drag tables around |
    | Export SVG/PNG | Share with your team or drop in docs |
    | Search tables/columns | Find anything in a large schema fast |
    | No signup required | You're diagramming, not buying enterprise software |

How to use dbdiagramr as your free database diagram tool

Option 1: Paste SQL

If you have a SQL dump or migration file, paste it directly into dbdiagramr and see your ER diagram appear instantly. Works with PostgreSQL CREATE TABLE statements, foreign keys, indexes -- the whole schema.

Option 2: Connect to a live database

If you have a PostgreSQL connection string, paste it into dbdiagramr. It queries `information_schema` for tables, columns, primary keys, and foreign keys, then renders an interactive ER diagram you can pan, zoom, drag, and export as SVG or PNG. Your SQL never leaves your browser -- the connection happens client-side.

Option 3: Upload a file

Drag and drop a `.sql` file or use the file picker. Same result -- instant diagram.

Why most "free" database diagram tools aren't actually free

    | dbdiagram.io | 10 tables on free plan, export requires paid plan |
    | DrawSQL | Limited to 10 diagrams, no collaboration on free plan |
    | Lucidchart | 3 editable documents, 60 shapes per document |
    | QuickDBD | Limited diagrams, watermark on exports |


dbdiagramr has none of these limits. Paste SQL, connect to your database, export as much as you want. The tool is open-source -- you can even self-host it.

Real example: visualizing a Supabase schema

Supabase projects come with auth, storage, and realtime tables out of the box. Understanding how they connect requires a diagram.

Paste this SQL into dbdiagramr:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

In two seconds you see: `users` -- `posts` -- `comments` with all foreign key relationships drawn. Drag the boxes around, zoom in, export as PNG for your README.

When to use a free tool vs. a paid one

**Use a free tool when:**


  - You're exploring an existing schema
  - You need a quick diagram for documentation
  - You're a solo developer or small team
  - You want to understand relationships before writing queries

**Consider paid when:**


  - You need real-time collaboration (Lucidchart, DrawSQL paid)
  - You need version control integration (some enterprise tools)
  - You're designing a schema from scratch with a large team

For 90% of database diagramming tasks, a free tool is enough.

FAQ

**What's the best free online database diagram tool?**dbdiagramr is the best free option -- no table limits, no signup, open-source. Paste SQL or connect to a live PostgreSQL database.

**Can I create a database diagram without signing up?**Yes. dbdiagramr requires no account. Go to the site, paste SQL, get your diagram.

**Does it work with PostgreSQL only?**Currently yes. PostgreSQL is the most common target for ER diagrams, and dbdiagramr specializes in it. SQL paste works with any PostgreSQL-compatible syntax.

**Can I export the diagram?**Yes. Export as SVG or PNG with one click. No paid plan required.

**Is my database connection secure?**Yes. dbdiagramr connects to your database client-side. Your connection string never leaves your browser. No data is sent to any server.

dbdiagram.io vs dbdiagramr vs DrawSQL: Honest Comparison

Varun Krishnan — Fri, 25 Sep 2026 14:30:00 +0000

The short version

dbdiagram.io is the original -- great for writing schemas in code (DBML), but no live database connection. DrawSQL is a paid GUI tool with good auto-generation but requires a desktop app. dbdiagramr is free, runs in the browser, connects directly to Supabase/Neon/Railway, and generates diagrams from your live database in seconds.

Feature comparison

Feature	dbdiagram.io	dbdiagramr	DrawSQL
Pricing	Free (1 diagram), $9/mo for more	Free (unlimited)	$8/mo (individual), $15/mo (team)
Live database connection	No	Yes (Supabase, Neon, Railway, any Postgres)	Yes (MySQL, Postgres, SQLite)
DBML support	Yes (native)	No (generates from DB)	No
Auto-generate from DB	No	Yes (paste connection string)	Yes (desktop app)
Supabase integration	No	Yes (direct connection)	No (generic Postgres)
Neon integration	No	Yes	No
Railway integration	No	Yes	No
Browser-based	Yes	Yes	No (desktop app)
Schema export	DBML, SQL	SQL, PNG	SQL, PNG, PDF
Collaboration	Yes (paid)	No (yet)	Yes (paid)
API access	Yes (paid)	No	No
AI context (llms.txt)	No	Yes	No

dbdiagram.io

Best for: Teams that want to design schemas in code (DBML) before creating them.

Pros:

DBML is a great way to define schemas as code.
Clean, fast editor.
Good for greenfield projects where you're designing from scratch.

Cons:

No live database connection -- you can't diagram an existing database.
Free tier is limited to 1 diagram.
Can't generate from Supabase, Neon, or Railway directly.
Requires learning DBML syntax.

Pricing: Free (1 diagram), $9/mo (10 diagrams), $17/mo (unlimited).

dbdiagramr

Best for: Developers who want to see their existing database schema in seconds.

Pros:

Free and unlimited.
Paste a connection string, get a diagram -- no signup, no install.
Direct Supabase, Neon, Railway integration.
Browser-based, works on any device.
Generates AI context (llms.txt) for your schema.
Open source.

Cons:

Read-only (can't edit schemas in the UI, yet).
No collaboration features (yet).
No DBML support.

Pricing: Free.

DrawSQL

Best for: Teams that want a desktop GUI for database exploration and documentation.

Pros:

Good auto-generation from live databases.
Clean UI with export options (PNG, PDF, SQL).
Supports MySQL, Postgres, SQLite.
Collaboration features for teams.

Cons:

Desktop app -- not browser-based.
Paid (no free tier for most features).
No direct Supabase integration (generic Postgres only).
No AI context generation.

Pricing: $8/mo (individual), $15/mo (team), $25/mo (organization).

Which one should you use?

If you...	Use...
Want to diagram an existing Supabase/Neon database	dbdiagramr
Want to design a schema from scratch in code	dbdiagram.io
Need a desktop GUI with export options	DrawSQL
Want free, unlimited diagrams	dbdiagramr
Need team collaboration	dbdiagram.io or DrawSQL
Want AI context for your schema	dbdiagramr
Are on a tight budget	dbdiagramr

The real question

The tools aren't competitors -- they serve different workflows:

Design first (dbdiagram.io): You write DBML, generate migrations, deploy to production.
Existing database (dbdiagramr): You already have a database, you want to see what it looks like.
GUI exploration (DrawSQL): You want a desktop app to browse, query, and export your schema.

Most developers use dbdiagramr to understand what they have, then dbdiagram.io if they want to redesign it.

FAQ

Can I import a dbdiagram.io diagram into dbdiagramr?

Not directly. dbdiagram.io uses DBML format. dbdiagramr connects to live databases. You'd need to deploy your DBML schema to a database first, then connect dbdiagramr to it.

Does dbdiagramr support MySQL?

Not yet. Currently supports PostgreSQL only. MySQL support is planned.

Is DrawSQL worth the money?

If you need a desktop GUI with export options and team collaboration, yes. If you just need to see your schema, dbdiagramr does it for free in the browser.

Can I use dbdiagramr with Prisma?

Yes. Prisma connects to PostgreSQL. Use your Prisma connection string (with ?pgbouncer=true if using Supabase pooler) and dbdiagramr will diagram your entire schema.

Supabase Connection String: IPv6, ENOTFOUND, and the Transaction Pooler Fix

Varun Krishnan — Thu, 24 Sep 2026 14:30:00 +0000

The short version

Supabase uses IPv6 by default. Most local dev environments and many hosting providers don't support IPv6, so DNS resolution fails with ENOTFOUND or hangs with ETIMEDOUT. The fix: use the transaction pooler connection string (port 6543) instead of the direct connection (port 5432). It routes through Supabase's IPv4-compatible proxy.

The error

You're running your app locally and you see:

Error: getaddrinfo ENOTFOUND db.xxxxxxxxx.supabase.co
    at GetAddrInfoReq.onlookup [as oncomplete] (node:dns:1107:26)

Or:

Error: connect ETIMEDOUT 2600:1ff:f000:e000::1:5432

The first one means DNS can't resolve the hostname. The second means DNS resolved it to an IPv6 address, but the connection timed out because your network doesn't route IPv6.

Why it happens

Supabase assigns each project a hostname like db.xxxxxxxxx.supabase.co. This hostname resolves to an IPv6 address by default. If your machine or hosting provider doesn't support IPv6, the connection fails.

Environment	IPv6 support
macOS (most setups)	Yes
Linux (most setups)	Yes
Windows (most setups)	No
Vercel	Yes
Railway	Yes
Render	Yes
Cloudflare Workers	No
Local Docker	Depends on config

The three connection strings

Supabase gives you three connection strings in the dashboard (Settings → Database):

1. Direct connection (port 5432)

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxxxxxx.supabase.co:5432/postgres

Full Postgres protocol support
Supports prepared statements, SET commands, LISTEN/NOTIFY
Uses IPv6 -- may fail locally or on some hosts

2. Transaction pooler (port 6543)

postgresql://postgres.[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres

Routes through Supabase's connection pooler (PgBouncer)
IPv4-compatible -- works everywhere
Does NOT support prepared statements or SET commands
Best for most web apps

3. Session pooler (port 6543, mode=session)

postgresql://postgres.[YOUR-PROJECT-REF]:[YOUR-PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true&session_mode=true

Same as transaction pooler but preserves session state
Supports SET commands but not prepared statements
Use this if you need SET search_path or similar

Which one to use

Use case	Connection string
Local dev (Node.js, Python, etc.)	Transaction pooler (6543)
Vercel / Railway / Render	Transaction pooler (6543)
Cloudflare Workers	Transaction pooler (6543)
Prisma ORM	Transaction pooler (6543)
Drizzle ORM	Transaction pooler (6543)
Django	Session pooler (6543 + session_mode)
Laravel	Transaction pooler (6543)
pg_dump / pg_restore	Direct connection (5432)
Migrations (Prisma, Knex)	Direct connection (5432)

The Prisma gotcha

Prisma uses prepared statements by default. The transaction pooler doesn't support prepared statements, so you'll get:

Error: prepared statement "stmt_1" does not exist

Fix: Add ?pgbouncer=true to your connection string, or use the direct connection for migrations and the pooler for runtime queries.

# .env
DATABASE_URL="postgresql://postgres.xxx:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"

Quick diagnosis

Test if IPv6 works

# If this returns an address, IPv6 works
dig AAAA db.xxxxxxxxx.supabase.co

# If this returns an address, IPv4 works
dig A db.xxxxxxxxx.supabase.co

Test the connection

# Direct connection (may fail with IPv6)
psql "postgresql://postgres:password@db.xxxxxxxxx.supabase.co:5432/postgres"

# Transaction pooler (should always work)
psql "postgresql://postgres.xxx:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

FAQ

Why does my app work on Vercel but not locally?

Vercel supports IPv6. Your local machine might not. Use the transaction pooler (6543) locally and you'll get the same behavior as production.

Can I force IPv4 on the direct connection?

Yes. Add ?sslmode=require&ip=4 to the direct connection string, but this isn't officially supported and may break. Use the pooler instead.

What's the difference between transaction and session pooler?

Transaction pooler resets the connection after each transaction (faster, more scalable). Session pooler preserves session state (needed for SET commands, LISTEN/NOTIFY). Most apps should use transaction pooler.

Does the pooler affect performance?

Minimal. The pooler adds ~1-2ms of latency per query. For most web apps, this is negligible compared to network latency and query execution time.

Can I use both pooler and direct connections?

Yes. Use the pooler for runtime queries (better scalability) and the direct connection for migrations and admin tasks (full protocol support).

Why Schema Diagrams Go Stale (and the Fix)

Varun Krishnan — Tue, 22 Sep 2026 02:30:00 +0000

The short version

Schema diagrams go stale because they're static snapshots of a moving target. A migration adds a column, someone renames a table, and suddenly your beautiful diagram is wrong. The fix: generate diagrams from the live database, not from a file. Run it on every deploy or PR.

Why it happens

Reason 1: Migrations don't update diagrams

You run ALTER TABLE orders ADD COLUMN shipping_cost_cents integer; and the migration succeeds. But your ER diagram still shows the old schema. Nobody thinks to update the diagram because the migration "just works."

Reason 2: Diagrams live in the wrong place

If your diagram is in a Confluence page, a Figma file, or a static image in your repo, it's disconnected from the code. The code changes, the diagram doesn't.

Reason 3: Nobody owns it

Diagram maintenance falls between "frontend" and "backend" and "DevOps." Everyone assumes someone else will update it. Nobody does.

The three fixes

Fix 1: Generate from the live database

Instead of maintaining a static diagram, generate it from the actual database state:

# Using dbdiagramr CLI (or paste your connection string at dbdiagramr.space)
# Your schema is always current -- no manual updates needed.

This is the nuclear option. Your diagram is always 100% accurate because it's generated from the source of truth.

Fix 2: Generate in CI/CD

Add a schema diagram step to your CI pipeline:

# GitHub Actions example
- name: Generate schema diagram
  run: |
    pg_dump --schema-only $DATABASE_URL > schema.sql
    # Generate diagram from schema.sql
    # Upload as artifact or commit to repo

Run it on every push to main. The diagram is always one commit behind, but it's close enough.

Fix 3: One-page SCHEMA.md

Maintain a single markdown file with the schema overview. Update it in the same PR that adds or changes a column:

# SCHEMA.md

## orders
- `id` (uuid, PK)
- `user_id` (uuid, FK → users)
- `status` (text) -- pending | confirmed | shipped | delivered
- `total_amount_cents` (integer) -- added 2026-09-15
- `shipping_cost_cents` (integer) -- added 2026-09-20

Put the update in your PR checklist:

## PR checklist
- [ ] Tests pass
- [ ] SCHEMA.md updated (if schema changed)
- [ ] Migration is reversible

What doesn't work

Manual updates to Figma/Confluence

Nobody does it. The diagram is always 3 months out of date.

Screenshots of pgAdmin

The moment you take the screenshot, it's stale. And you can't diff a screenshot.

Auto-generated docs from migration files

Migration files show the history of changes, not the current state. You'd need to replay all migrations to get the current schema, which is slow and error-prone.

The self-healing diagram

The ideal setup:

Source of truth: the live database.
Diagram generation: runs on every deploy (or on-demand).
Storage: committed to the repo as an SVG or markdown.
Enforcement: CI fails if the diagram doesn't match the schema.

This is overkill for most projects. But if you've ever debugging a "why does the diagram show a column that doesn't exist?" issue, it's worth it.

FAQ

How often should I update my schema diagram?

Every time you add, remove, or rename a column. If you're using a tool like dbdiagramr that generates from the live database, it's always current.

Should I commit the diagram to git?

Yes. Commit it as an SVG or markdown file. That way it's versioned with your code and you can see when it changed.

What's the minimum viable schema documentation?

A one-page SCHEMA.md file in your repo with table names, key columns, and relationships. Update it in the same PR that changes the schema.

Can I auto-generate schema docs from Prisma/Drizzle schema files?

Partially. Prisma's prisma-docs-generator and Drizzle's drizzle-kit generate can create basic docs. But they miss business rules, enum values, and deletion policies. Use them as a starting point, not the final doc.

How to Document Your Database Schema for a Team

Varun Krishnan — Thu, 17 Sep 2026 14:30:00 +0000

The short version

Document your schema in three layers:
(1) name things well so they're self-documenting,
(2) add inline column comments for the non-obvious stuff,
(3) generate a one-page visual diagram that shows the relationships. Skip the 40-page Confluence page nobody reads it.

Layer 1: Name things well

The fastest documentation is a good name. If your column names are clear, you barely need comments.

Tables

Use plural nouns: users, orders, order_items.
Use snake_case: order_items, not OrderItems.
Be specific: payment_methods, not methods.

Columns

Use created_at and updated_at for timestamps.
Use {table}_id for foreign keys: user_id, not userId or user.
Prefix booleans with is_ or has_: is_active, has_paid.
Avoid abbreviations: customer_id, not cust_id.

Foreign keys

Name them after the parent table: orders.user_id → users.id.
Don't use generic names like ref_id or parent_id (unless it's a tree structure).

Layer 2: Inline comments

PostgreSQL supports column-level comments. Use them for anything that isn't obvious from the name:

COMMENT ON COLUMN orders.status IS
  'pending | confirmed | shipped | delivered | cancelled. Never delete orders set status to cancelled instead.';

COMMENT ON COLUMN users.phone_number IS
  'E.164 format (+1234567890). Required for SMS auth. May be null if user signed up via OAuth.';

COMMENT ON COLUMN products.sku IS
  'Stock Keeping Unit. Format: CATEGORY-NNNN (e.g., ELEC-0042). Must be unique across all products.';

What to comment

Enum values: What are the possible values for status?
Format requirements: What format does phone_number expect?
Business rules: Why is email nullable? (OAuth users may not share it.)
Deletion policy: Do you delete rows or soft-delete?

What NOT to comment

id - everyone knows what id is.
created_at / updated_at - self-explanatory.
Foreign keys named after their parent table - user_id is obvious.

Layer 3: Visual diagram

A one-page ER diagram replaces 40 pages of documentation. Generate it automatically:

dbdiagramr - paste your connection string, get a visual schema in seconds. No signup required.
pgAdmin - built-in ERD tool for PostgreSQL.
DBeaver - database tool with auto-generated ER diagrams.

What to show

Table names and primary keys.
Foreign key relationships (the lines between tables).
Column types for non-obvious fields (JSONB, arrays, enums).

What to skip

Every single column - just show the important ones.
Indexes - they're implementation details.
Default values - put those in comments instead.

The one-page cheat sheet

Create a single markdown file in your repo called SCHEMA.md:

# Database Schema

## Overview
- 12 tables, 3 schemas (public, auth, storage)
- Last updated: 2026-09-01

## Tables

### users
Core user table. Created by Supabase Auth.
- `id` (uuid, PK) - Auth user ID
- `email` (text) - May be null for phone auth
- `raw_user_meta_data` (jsonb) - Profile data from OAuth

### orders
Customer orders. Never delete - use status instead.
- `id` (uuid, PK)
- `user_id` (uuid, FK → users) - Customer
- `status` (text) - pending | confirmed | shipped | delivered | cancelled
- `total_amount_cents` (integer) - Price in cents to avoid float rounding

...

ER Diagram

Naming conventions by team size

Team size	What works
1 person	Whatever you remember. Add comments for future-you.
2-5 people	Naming conventions + `SCHEMA.md` cheat sheet.
5-15 people	Naming conventions + comments + auto-generated docs (dbdiagramr, pgAdmin).
15+ people	All of the above + dedicated data docs (DataHub, Atlan, or dbt docs).

FAQ

Should I document every table?

No. Document the tables that matter: the ones new engineers will touch, the ones with complex business logic, and the ones with non-obvious schemas. Skip internal Django/Laravel/NextAuth tables framework docs cover those.

How often should I update the docs?

When you add or change a column. The best way to enforce this: put the SCHEMA.md update in your PR template as a checklist item.

What's the best tool for auto-generating schema docs?

dbdiagramr for visual diagrams. For text-based docs, use dbt's docs generate if you're already on dbt. For everything else, a markdown file in your repo beats any SaaS tool.

Can I generate schema docs from migration files?

Partially. Migration files show the changes, not the current state. You'd need to run pg_dump --schema-only or query information_schema to get the actual current schema, then generate docs from that.

information_schema vs pg_catalog: Which Should You Query?

Varun Krishnan — Tue, 15 Sep 2026 14:30:00 +0000

The short version

information_schema is the SQL-standard way to query metadata portable across databases, slower, limited to what the standard defines. pg_catalog is PostgreSQL-specific faster, more detailed, has everything. Use information_schema for simple queries you might run on MySQL too. Use pg_catalog when you need Postgres-specific details or performance.

What each one is

information_schema

A set of views defined by the SQL standard. Every relational database (Postgres, MySQL, SQL Server) has the same views with the same column names.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public';

Pros: Portable. Readable. Standard.

Cons: Slower. Missing Postgres-specific types (arrays, JSONB, ranges). Can't see internal system tables.

pg_catalog

PostgreSQL's internal catalog. It's a schema that's automatically search path and contains tables, views, and functions that describe every object in the database.

SELECT c.relname AS table_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

Pros: Faster. More detailed. Has Postgres-specific types. Can see system tables.

Cons: Not portable. Harder to read. Syntax is verbose.

Head-to-head comparison

Query	information_schema	pg_catalog
List tables	`SELECT table_name FROM information_schema.tables`	`SELECT relname FROM pg_class WHERE relkind = 'r'`
List columns	`SELECT column_name, data_type FROM information_schema.columns`	`SELECT attname, format_type(...) FROM pg_attribute`
List indexes	`SELECT indexname FROM information_schema.statistics`	`SELECT indexrelname FROM pg_stat_user_indexes`
List constraints	`SELECT constraint_name FROM information_schema.table_constraints`	`SELECT conname FROM pg_constraint`
List foreign keys	`SELECT ... FROM information_schema.key_column_usage`	`SELECT ... FROM pg_constraint WHERE contype = 'f'`
Table size	Not available	`SELECT pg_total_relation_size(oid)`
Table owner	Not available	`SELECT pg_catalog.get_owner(c.oid)`
View definitions	Not available	`SELECT definition FROM pg_views`

When to use which

Use information_schema when:

You need portable SQL (might run on MySQL/SQLite too).
You're writing a simple query and don't care about performance.
You want readable, standard syntax.
You're building a tool that works across databases.

-- Simple: list all tables in the public schema
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE';

Use pg_catalog when:

You need Postgres-specific info (table owners, indexes, sizes, permissions).
You're querying large databases pg_catalog is faster.
You need internal system tables (pg_stat_activity, pg_locks, etc.).
You're building a Postgres-only tool.

-- See active queries with full details
SELECT pid, usename, application_name, query, state
FROM pg_catalog.pg_stat_activity
WHERE state = 'active';

Common queries

List all columns with types (information_schema)

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

List all columns with types (pg_catalog)

SELECT c.relname AS table_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
       NOT a.attnotnull AS nullable
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY c.relname, a.attnum;

List foreign keys

-- information_schema
SELECT
  kcu.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table,
  ccu.column_name AS foreign_column
FROM information_schema.key_column_usage kcu
JOIN information_schema.constraint_column_usage ccu
  ON kcu.constraint_name = ccu.constraint_name
WHERE kcu.constraint_type = 'FOREIGN KEY';

Show table sizes

-- pg_catalog only
SELECT
  c.relname AS table_name,
  pg_catalog.pg_size_pretty(pg_catalog.pg_total_relation_size(c.oid)) AS total_size
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY pg_catalog.pg_total_relation_size(c.oid) DESC;

See who's connected right now

-- pg_catalog only
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_catalog.pg_stat_activity
WHERE backend_type = 'client backend';

Performance note

For simple metadata queries on small databases, the performance difference doesn't matter. But on large databases (thousands of tables, millions of rows), pg_catalog can be 2-10x faster because it's indexed internally.

FAQ

Can I query pg_catalog from MySQL?

No. pg_catalog is PostgreSQL-specific. If you need cross-database portability, stick with information_schema.

Which one does pg_dump use?

pg_catalog. It needs Postgres-specific details like table OIDs, ACLs, and storage parameters that information_schema doesn't expose.

Do tools like Prisma or Drizzle use one over the other?

They use both. Prisma uses information_schema for schema introspection. Drizzle uses pg_catalog for more detailed Postgres metadata. Both fall back to the other when one doesn't have the info they need.

Can I see system tables (pg_stat_activity, pg_locks) from information_schema?

No. System tables are only in pg_catalog. Use pg_catalog.pg_stat_activity to see active queries, pg_catalog.pg_locks to see locks, etc.

Django Auth Tables and Permissions Explained

Varun Krishnan — Thu, 10 Sep 2026 14:34:00 +0000

Django creates 5 core auth tables: auth_user, auth_group, auth_permission, django_content_type, and two join tables (auth_user_groups, auth_group_permissions). The permission system is built on content types each model gets a default add, change, delete, and view permission, and you can create custom ones.

The core tables

auth_user

Column	Type	What it means
`id`	int (PK)	Auto-incrementing primary key.
`password`	varchar(128)	Hashed password (PBKDF2 by default).
`last_login`	datetime	When the user last logged in. Null if never.
`is_superuser`	bool	Bypasses all permission checks.
`username`	varchar(150)	Unique username.
`first_name`	varchar(150)	User's first name.
`last_name`	varchar(150)	User's last name.
`email`	varchar(254)	Email address. Not necessarily unique.
`is_staff`	bool	Can access the Django admin.
`is_active`	bool	Set to False instead of deleting users.
`date_joined`	datetime	Registration time.

auth_group

Column	Type	What it means
`id`	int (PK)	Auto-incrementing ID.
`name`	varchar(150)	Unique group name (e.g., "Editors", "Moderators").

Groups are role containers. Assign permissions to groups, then add users to groups.

auth_permission

Column	Type	What it means
`id`	int (PK)	Auto-incrementing ID.
`name`	varchar(255)	Human-readable name (e.g., "Can add post").
`content_type_id`	int (FK)	Which model this permission applies to.
`codename`	varchar(100)	Code identifier (e.g., `add_post`).

Each model gets 4 default permissions: add_modelname, change_modelname, delete_modelname, view_modelname.

django_content_type

Column	Type	What it means
`id`	int (PK)	Auto-incrementing ID.
`app_label`	varchar(100)	The Django app (e.g., `blog`).
`model`	varchar(100)	The model name (e.g., `post`).

This table maps every model in your project to an ID. The permission system uses it to know which model a permission applies to.

auth_user_groups

Join table for many-to-many: users ↔ groups.

Column	Type	What it means
`id`	int (PK)	Auto-incrementing ID.
`user_id`	int (FK)	→ `auth_user.id`.
`group_id`	int (FK)	→ `auth_group.id`.

auth_group_permissions

Join table for many-to-many: groups ↔ permissions.

Column	Type	What it means
`id`	int (PK)	Auto-incrementing ID.
`group_id`	int (FK)	→ `auth_group.id`.
`permission_id`	int (FK)	→ `auth_permission.id`.

How permissions work

User ──M──M── Group ──M──M── Permission ──M──1── ContentType

A permission is tied to a content type (a specific model).
Groups collect permissions (e.g., "Editors" get add_post, change_post).
Users are added to groups to inherit their permissions.
You can also assign permissions directly to users (bypass groups).

In code:

# Check permission
user.has_perm('blog.add_post')

# Add user to group
from django.contrib.auth.models import Group
editors = Group.objects.get(name='Editors')
user.groups.add(editors)

# Create custom permission
from django.contrib.auth.models import Permission
from django.contrib.contenttypes.models import ContentType
from blog.models import Post
content_type = ContentType.objects.get_for_model(Post)
custom_perm = Permission.objects.create(
    codename='publish_post',
    name='Can publish post',
    content_type=content_type,
)

What to change

Add a role field to auth_user if you need simple role-based access (or use groups).
Add an avatar field for profile pictures.
Create custom permissions for fine-grained access control (e.g., publish_post, archive_post).
Add unique=True to email if you want unique emails.

What to leave alone

Don't modify django_content_type it's managed by Django's migration framework.
Don't change auth_permission.codename it's referenced by has_perm() and decorators.
Don't delete auth_user.is_active use it instead of deleting users (preserves foreign keys).

FAQ

Does Django create all these tables automatically?

Yes. Running python manage.py migrate creates all auth tables. They're part of Django's built-in django.contrib.auth app.

Can I use Django without the permission system?

Yes. Remove django.contrib.auth from INSTALLED_APPS and you lose the permission tables but keep the auth_user model (or replace it entirely).

What's the difference between `is_superuser` and `is_staff`?

is_superuser bypasses all permission checks. is_staff only controls access to the Django admin. A superuser automatically has is_staff=True.

How do I see my Django auth tables?

Use dbdiagramr paste your connection string and get a visual schema of your Django auth tables.

NextAuth / Auth.js Database Schema Explained

Varun Krishnan — Mon, 07 Sep 2026 18:30:00 +0000

The short version

NextAuth (now Auth.js) creates 4 tables in your database: users, accounts, sessions, and verification_tokens. The users and accounts tables have a one-to-one relationship via accounts.user_id. Sessions link to users via sessions.user_id. Verification tokens are short-lived and self-cleaning.

The 4 tables

users

Column	Type	What it means
`id`	text / UUID	Primary key. Generated by NextAuth.
`name`	text	Display name from the OAuth provider (Google, GitHub, etc.)
`email`	text	User's email. May be null if the provider doesn't share it.
`email_verified`	timestamp	When the email was verified. Null if never verified.
`image`	text	Profile picture URL from the provider.
`created_at`	timestamp	When the user first signed in.
`updated_at`	timestamp	Last profile sync from the provider.

accounts

This table links a user to an OAuth provider. One user can have multiple accounts (e.g., Google + GitHub).

Column	Type	What it means
`id`	text / UUID	Primary key.
`user_id`	text	Foreign key → `users.id`.
`type`	text	Always `"oauth"` or `"oidc"`.
`provider`	text	`"google"`, `"github"`, `"discord"`, etc.
`provider_account_id`	text	The provider's unique ID for this user.
`refresh_token`	text	OAuth refresh token (encrypted in production).
`access_token`	text	OAuth access token (encrypted in production).
`expires_at`	integer	When the access token expires (Unix timestamp).
`token_type`	text	Usually `"Bearer"`.
`scope`	text	Permissions granted by the provider.
`id_token`	text	OIDC ID token (if using OIDC).
`session_state`	text	Provider-specific session state.

sessions

Active sessions for each user. NextAuth creates a new row here on every sign-in.

Column	Type	What it means
`id`	text / UUID	Primary key.
`session_token`	text	The session token stored in the user's cookie.
`user_id`	text	Foreign key → `users.id`.
`expires`	timestamp	When this session expires.

verification_tokens

Short-lived tokens for email verification, password reset, etc. Self-cleaning old tokens are deleted automatically.

Column	Type	What it means
`identifier`	text	Email or user ID the token is for.
`token`	text	The actual token value.
`expires`	timestamp	When this token expires.

How they connect

users ──1──1── accounts
   │
   1
   │
   ∞
sessions

users ──1──∞── verification_tokens (via identifier)

One user → one or more accounts (Google, GitHub, etc.)
One user → many sessions (different devices/browsers)
Verification tokens are temporary and don't have a foreign key

What to change

Add a role column to users if you need role-based access control.
Add a phone_number column to users if you're using SMS auth.
Encrypt access_token and refresh_token in production NextAuth doesn't do this by default.

What to leave alone

Don't modify the verification_tokens table it's managed automatically.
Don't change the session_token format it's a signed JWT.
Don't add indexes to provider_account_id unless you're querying it directly (it's already unique).

FAQ

Does NextAuth store passwords?

No. NextAuth is an OAuth-first library. It doesn't handle passwords. If you need email/password auth, use next-auth/providers/credentials with bcrypt, or use a service like Clerk or Lucia.

How do I see what's in my NextAuth tables?

Use dbdiagramr paste your connection string and get a visual schema of your NextAuth tables in seconds.

Can I add custom fields to the users table?

Yes. Add columns to the users table directly. NextAuth will ignore columns it doesn't know about, so you can safely add role, phone_number, preferences, etc.

What happens when a user deletes their account?

NextAuth doesn't cascade deletes by default. You need to manually delete from users, accounts, and sessions. Or add ON DELETE CASCADE to your foreign key constraints.

Is Auth.js the same as NextAuth?

Yes. Auth.js is the rebranded version of NextAuth. The database schema is identical. If you're on NextAuth v4, you're using the same tables.

Laravel Default Database Tables Explained

Varun Krishnan — Thu, 03 Sep 2026 17:00:00 +0000

A fresh Laravel install creates 7-8 tables depending on your packages. The core ones are users, password_resets, failed_jobs, and personal_access_tokens. The cache, sessions, jobs, and batches tables are only created if you run the corresponding Artisan commands. None of them are sacred -- you can rename, extend, or replace any of them.

The core tables

users

Column	Type	What it means
`id`	bigint (PK)	Auto-incrementing primary key.
`name`	varchar(255)	User's display name.
`email`	varchar(255)	Unique email address.
`email_verified_at`	timestamp	When email was verified. Null if unverified.
`password`	varchar(255)	Hashed password (bcrypt). Never store plain text.
`remember_token`	varchar(100)	Token for "remember me" functionality.
`created_at`	timestamp	Registration time.
`updated_at`	timestamp	Last profile update.

password_resets

Column	Type	What it means
`email`	varchar(255)	The email requesting a reset.
`token`	varchar(255)	The reset token (hashed).
`created_at`	timestamp	When the token was generated.

This table is self-cleaning -- old tokens are garbage collected.

failed_jobs

Column	Type	What it means
`id`	bigint (PK)	Auto-incrementing ID.
`uuid`	varchar(255)	Unique identifier for the job.
`connection`	text	Queue connection that failed.
`queue`	queue name	Which queue the job was on.
`payload`	longText	The job's serialized data.
`exception`	longText	The full exception stack trace.
`failed_at`	timestamp	When it failed.

Use this table to debug failed queue jobs. The exception column has the full stack trace.

personal_access_tokens

Created by Laravel Sanctum for API token authentication.

Column	Type	What it means
`id`	bigint (PK)	Auto-incrementing ID.
`tokenable_type`	varchar(255)	The model this token belongs to (e.g., `App\Models\User`).
`tokenable_id`	bigint	The ID of that model.
`name`	varchar(255)	Token name (e.g., "Mobile App").
`token`	varchar(60)	The hashed token value.
`abilities`	text	JSON array of allowed abilities (e.g., `["*"]`).
`last_used_at`	timestamp	When the token was last used.
`expires_at`	timestamp	When the token expires (null = never).

Optional tables

sessions

Created by php artisan session:table. Stores HTTP session data in the database instead of files.

Column	Type	What it means
`id`	varchar(255) (PK)	Session ID (from the cookie).
`user_id`	bigint (FK)	The user this session belongs to. Null for guests.
`ip_address`	varchar(45)	Client IP.
`user_agent`	text	Browser user agent string.
`payload`	longText	Serialized session data.
`last_activity`	integer	Unix timestamp of last activity.

cache

Created by php artisan cache:table. Stores key-value cache entries in the database.

Column	Type	What it means
`key`	varchar(255) (PK)	Cache key.
`value`	longText	Serialized cache value.
`expiration`	integer	Unix timestamp when it expires.

jobs

Created by php artisan queue:table. Stores pending queue jobs.

Column	Type	What it means
`id`	bigint (PK)	Auto-incrementing ID.
`queue`	varchar(255)	Queue name.
`payload`	longText	Serialized job data.
`attempts`	integer	How many times it's been tried.
`reserved_at`	integer	When it was reserved by a worker.
`available_at`	integer	When it can be picked up.
`created_at`	integer	When it was dispatched.

batches

Created by php artisan queue:batches-table. Tracks batch progress for parallel job processing.

Column	Type	What it means
`id`	bigint (PK)	Auto-incrementing ID.
`name`	varchar(255)	Batch name.
`total_jobs`	integer	Total jobs in the batch.
`pending_jobs`	integer	Jobs still to run.
`failed_jobs`	integer	Jobs that failed.
`failed_job_ids`	longText	JSON array of failed job IDs.
`options`	longText	Serialized batch options.
`cancelled_at`	timestamp	When cancelled (null if not).
`created_at`	timestamp	When the batch was created.
`finished_at`	timestamp	When the batch completed.

What to change

Add a role column to users for role-based access.
Add a phone_number column for SMS auth.
Add an avatar_url column for profile pictures.
Rename password_resets to password_reset_tokens (Laravel 8+ convention).

What to leave alone

Don't modify the failed_jobs.exception column -- it needs to hold full stack traces.
Don't remove personal_access_tokens.tokenable_type -- it's a polymorphic relation.
Don't add indexes to cache.key -- it's already the primary key.

FAQ

Does Laravel create all these tables automatically?

No. Only users, password_resets, and failed_jobs are created by php artisan migrate. The others (sessions, cache, jobs, batches) require separate Artisan commands.

Can I use Redis instead of the database tables?

Yes. Laravel supports Redis for sessions, cache, and queues out of the box. Switch your .env driver to redis and the database tables become unnecessary.

What's the difference between `password_resets` and `password_reset_tokens`?

Same table, different names. Laravel 8+ renamed it to password_reset_tokens. If you're on an older version, it's still password_resets.

How do I visualize my Laravel schema?

Use dbdiagramr -- paste your connection string and get a visual schema of all your Laravel tables.

What Is an ER Diagram and How to Read One

Varun Krishnan — Thu, 03 Sep 2026 03:00:00 +0000

The short version

An entity-relationship diagram is a picture of your database's tables, columns, and connections. You read it left to right: boxes are tables, lines are foreign-key relationships, and the symbols on each end tell you one-to-one, one-to-many, or many-to-many.

What each part means

Entities (tables)

Every box is a table. The bold name on top is the table name. The list underneath is the columns. Primary keys are usually marked with a key icon or bolded. Foreign keys have a link icon.

Example:

users
id (PK)
name
email
created_at

Attributes (columns)

Each line under the table name is a column. Common types you'll see:

id -- primary key (unique identifier)
name, email -- simple text fields
created_at -- timestamp of when the row was inserted
user_id -- foreign key pointing to another table (look for the line connecting it)

Relationships (lines)

Lines between boxes represent foreign key references. If you see a line from orders.user_id to users.id, that means "each order belongs to one user."

Cardinality (symbols)

The symbols at the end of each line tell you how many:

1 -- ∞ (one to many): one user has many orders. This is the most common.
1 -- 1 (one to one): one user has one profile.
∞ -- ∞ (many to many): many users have many roles. Usually resolved with a join table.

Reading a real example

Suppose you see this diagram:

users ──1──∞── orders ──1──∞── order_items ──∞──1── products
   │
   1
   │
   ∞
addresses

Reading left to right:

One user has many orders.
One order has many order items.
One product appears in many order items.
One user has many addresses.

The join table here is order_items ,it connects orders and products in a many-to-many relationship.

Why ER diagrams matter

Debugging: "Why is my query returning duplicates?" look for a missing join or unintended many-to-many.
Onboarding: New engineers understand the data model in 5 minutes instead of reading migration files.
Schema reviews: Spot missing indexes, redundant tables, or circular dependencies before they hit production.

Common mistakes

Assuming every line is one-to-many. Check the symbols. A many-to-many without a join table is a red flag.
Ignoring nullable columns. A dashed line or optional symbol means the relationship is optional.
Forgetting to check the foreign key direction. orders.user_id → users.id is different from users.order_id → orders.id.

Try it yourself

Open your own database schema in dbdiagramr and trace the foreign keys. Start from the table you're most familiar with and follow the lines outward.

FAQ

What does PK mean in an ER diagram?

PK stands for primary key the unique identifier for each row in a table. It's usually id and is referenced by foreign keys in other tables.

What does FK mean?

FK stands for foreign key a column in one table that points to the primary key of another table. It creates the relationship between the two tables.

How do you show a many-to-many relationship?

With a join table. For example, users ↔ user_roles ↔ roles. The user_roles table holds user_id and role_id foreign keys, and the many-to-many becomes two one-to-many relationships.

What's the difference between an ER diagram and a schema diagram?

They're the same thing in practice. ER diagram is the academic term; schema diagram is the engineering term. Both show tables, columns, and relationships.

Do I need to draw an ER diagram by hand?

No. Tools like dbdiagramr generate ER diagrams automatically from your Supabase, Neon, or PostgreSQL connection string. Paste your connection string and get a visual schema in seconds.

Supabase Auth Schema Explained: Users, Identities, Sessions

Varun Krishnan — Tue, 25 Aug 2026 15:00:00 +0000

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.