User Authentication System ERD

Designing an Auth System Schema

A robust user authentication system requires several related tables. This ERD covers the core entities needed for user registration, login, role-based access control (RBAC), and session management.

Core Entities

1. users — The central entity

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(320) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  is_email_verified BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

2. roles — Named permission groups

CREATE TABLE roles (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT
);

3. user_roles — Junction table (M:N between users and roles)

CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

4. permissions — Granular access rights

CREATE TABLE permissions (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  resource VARCHAR(100) NOT NULL,
  action VARCHAR(50) NOT NULL
);

5. role_permissions — Junction table (M:N between roles and permissions)

CREATE TABLE role_permissions (
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

6. sessions — Active user sessions (1:N from users)

CREATE TABLE sessions (
  id UUID PRIMARY KEY,
  user_id INT NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Relationship Summary

From	To	Type
users	user_roles	1:N
roles	user_roles	1:N
roles	role_permissions	1:N
permissions	role_permissions	1:N
users	sessions	1:N

This creates an RBAC system where users are assigned roles, roles contain permissions, and you can check if a user has a specific permission by traversing users → user_roles → roles → role_permissions → permissions.