Database Schema

AuthHero uses a comprehensive database schema designed for multi-tenant authentication and authorization. This page documents the complete database structure and relationships.

Entity Relationship Diagram

Key Tables and Their Purpose

Core Multi-Tenant Tables

tenants

The root table for multi-tenancy. Every other table references this through tenant_id to ensure complete data isolation between tenants.

users

Stores user accounts with comprehensive profile information. Supports:

• Social and password-based authentication
• Account linking (users can link multiple social accounts)
• Detailed profile data and metadata
• Email and phone verification status

applications

OAuth/OIDC client applications that can authenticate users. Each application has:

• Client secrets for confidential clients
• Allowed redirect URIs and origins
• OAuth flow configuration
• Custom add-ons and settings

connections

Identity providers (social logins, enterprise connections, etc.) that users can authenticate through:

• Strategy-based configuration (Google, Facebook, SAML, etc.)
• Custom options for each provider
• Response type and mode settings

Authentication Flow Tables

login_sessions

Tracks the authentication flow from start to completion:

• CSRF protection tokens
• OAuth parameters (client_id, scope, redirect_uri, etc.)
• Authorization URL construction
• Login completion status

sessions

Active user sessions after successful authentication:

• Session expiration and idle timeout
• Device and client information
• Session lifecycle tracking

refresh_tokens

Enables token renewal without re-authentication:

• Rotating refresh token support
• Resource server scoping
• Device binding

codes

Temporary authorization codes for OAuth flows:

• Multiple code types (authorization, password reset, etc.)
• PKCE support (code challenge/verifier)
• Expiration and usage tracking

passwords

Secure password storage:

• Algorithm specification for password hashing
• Linked to users via composite foreign key

Role-Based Access Control (RBAC)

roles

User roles within a tenant:

• Role names and descriptions
• Tenant-scoped roles

resource_servers

APIs that can be accessed through AuthHero:

• JWT signing configuration
• Token lifetime settings
• Scope definitions
• Verification keys

role_permissions

Links roles to specific permissions on resource servers:

• Composite primary key (tenant_id, role_id, resource_server_identifier, permission_name)
• Enables fine-grained access control

user_permissions

Direct user permissions (bypass roles):

• Same structure as role permissions
• Allows for user-specific access grants

user_roles

Assigns roles to users:

• Many-to-many relationship between users and roles
• Tenant-scoped assignments

Organizations (Sub-Tenancy)

organizations

Enables hierarchical multi-tenancy within a tenant:

• Organization branding and metadata
• Enabled connections per organization
• Token quotas and limits

user_organizations

Maps users to organizations (many-to-many relationship):

• Allows users to belong to multiple organizations
• Tracks organization membership per tenant

invites

Manages organization invitations for user onboarding:

• Pre-configured user attributes (roles, metadata)
• Invitation tracking with inviter/invitee information
• Expiration management (default 7 days, max 30 days)
• Connection specification for authentication
• Unique invitation URLs with tickets
• Optional email delivery

Customization and Branding

branding

Simple branding configuration per tenant:

• Logo and favicon URLs
• Primary colors and page backgrounds
• Font customization

themes

Comprehensive UI theming (more detailed than branding):

• Complete color palette customization
• Typography settings
• Border and layout configurations
• Widget positioning and styling

custom_domains

White-label domain support:

• Domain verification and status
• TLS policy configuration
• Custom client IP headers

Configuration and Settings

prompt_settings

Controls the login flow behavior:

• Universal login experience settings
• Username-first vs password-first flows
• WebAuthn configuration

email_providers

Custom email delivery configuration:

• Provider credentials (SendGrid, Mailgun, etc.)
• Email templates and settings
• Per-tenant email customization

Forms and Extensibility

forms

Custom forms for various workflows:

• Multi-language support
• Node-based form definition
• Custom styling and branding

hooks

Webhooks for extending AuthHero functionality:

• Trigger-based execution
• Synchronous and asynchronous hooks
• Form integration
• Priority-based ordering

Security and Cryptography

keys

Cryptographic keys for JWT signing and other security operations:

• Key rotation support
• Connection-specific keys
• Certificate and fingerprint storage
• Revocation tracking

Audit and Logging

logs

Comprehensive audit trail:

• All authentication events
• User actions and administrative changes
• Detailed context including IP, user agent, etc.
• Performance and security monitoring

Administrative Tables

members

Administrative users who can manage tenants:

• Separate from regular users
• Role-based access to admin functions
• Multi-tenant administration support

migrations

Tracks data migrations and imports:

• Migration from other auth providers
• Audit trail for data movement
• Client and domain mapping

Database Design Principles

Multi-Tenancy

Every table (except tenants and system tables) includes a tenant_id foreign key, ensuring complete data isolation between tenants.

Audit Trail

Most tables include created_at and updated_at timestamps for audit purposes and change tracking.

Soft Relationships

Many relationships use varchar IDs rather than integer foreign keys, providing flexibility for distributed systems and easier data migration.

JSON Storage

Complex configuration data is often stored as JSON strings in varchar/text fields, allowing for flexible schema evolution without database migrations.

Composite Keys

Several tables use composite primary keys (typically including tenant_id) to enforce tenant isolation at the database level.

This schema supports AuthHero's core mission of providing a flexible, secure, and scalable multi-tenant authentication system while maintaining compatibility with Auth0 APIs.