Authentication Data Models

Detailed schema documentation for authentication-related database tables.

User Table

Primary user account table managed by Better Auth.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255),
  email VARCHAR(255) NOT NULL UNIQUE,
  email_verified BOOLEAN DEFAULT false,
  image TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

Columns

• id: UUID primary key for security
• name: User's display name (optional)
• email: Login email address (unique)
• email_verified: Email verification status
• image: Profile image URL (optional)
• created_at: Account creation timestamp
• updated_at: Last profile update

Indexes

• Primary key on id
• Unique index on email
• Index on created_at for sorting

Session Table

Active user sessions with organization context.

CREATE TABLE sessions (
  id VARCHAR(255) PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at TIMESTAMP NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  active_organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

Columns

• id: Session token (opaque string)
• user_id: Reference to user
• expires_at: Session expiration time
• ip_address: Client IP for security
• user_agent: Browser/client info
• active_organization_id: Current workspace context
• created_at: Session start time
• updated_at: Last activity time

Indexes

• Primary key on id
• Foreign key index on user_id
• Index on expires_at for cleanup
• Index on active_organization_id

Account Table

OAuth provider accounts for future social login.

CREATE TABLE accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider VARCHAR(255) NOT NULL,
  provider_account_id VARCHAR(255) NOT NULL,
  access_token TEXT,
  refresh_token TEXT,
  expires_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP,
  UNIQUE(provider, provider_account_id)
);

Columns

• id: Account record ID
• user_id: Reference to user
• provider: OAuth provider name (google, github, etc.)
• provider_account_id: User ID from provider
• access_token: OAuth access token (encrypted)
• refresh_token: OAuth refresh token (encrypted)
• expires_at: Token expiration
• created_at: Account link time
• updated_at: Last token refresh

Indexes

• Primary key on id
• Foreign key index on user_id
• Unique composite index on (provider, provider_account_id)

Verification Table

Email verification and password reset tokens.

CREATE TABLE verifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  identifier VARCHAR(255) NOT NULL,
  value VARCHAR(255) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

Columns

• id: Verification record ID
• identifier: Email or user ID
• value: Verification token (hashed)
• expires_at: Token expiration
• created_at: Token generation time
• updated_at: Last update

Indexes

• Primary key on id
• Index on identifier
• Index on expires_at for cleanup

Password Table

Separate password storage for Better Auth.

CREATE TABLE passwords (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP
);

Columns

• user_id: Reference to user (primary key)
• hash: Argon2 password hash
• created_at: Password set time
• updated_at: Last password change

Indexes

• Primary key on user_id

Security Implementation

Password Storage

• Passwords are hashed using Argon2 (configured in config/hash.ts)
• Managed by Better Auth's password table

Session Management

• Sessions stored in database with expiration timestamps
• Session tokens managed by Better Auth
• Active organization context tracked per session