Authentication Data Models

Detailed schema documentation for the current Better Auth database tables. Better Auth records and Lucid domain records use UUID v7 values generated in application code.

User Table

Primary user account table managed by Better Auth.

CREATE TABLE users (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(254) NOT NULL UNIQUE,
  email_verified BOOLEAN NOT NULL DEFAULT false,
  image VARCHAR(255),
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP,
  active_workspace_id UUID,
  theme VARCHAR(255) NOT NULL DEFAULT 'system'
);

Columns

• id: UUID v7 primary key generated by Better Auth
• 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
• active_workspace_id: Optional user-level workspace preference
• theme: UI theme preference (system by default)

Indexes

• Primary key on id
• Unique index on email

Session Table

Database schema available for Better Auth session records with organization context. Under the current auth configuration, runtime sessions are stored in Redis instead of this table because secondaryStorage is configured and session.storeSessionInDatabase is not enabled.

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

Columns

• id: UUID v7 session record ID
• user_id: Reference to user
• token: Unique opaque session token
• expires_at: Session expiration time
• ip_address: Client IP for security
• user_agent: Browser/client info
• active_organization_id: Current workspace context
• active_team_id: Current team context
• created_at: Session start time
• updated_at: Last activity time

Indexes

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

Account Table

Credential records managed by Better Auth. Email/password registration creates a credential account whose Argon2 hash is stored in password. The token columns support Better Auth account types but no social providers are configured in Jubiloop.

CREATE TABLE accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  account_id VARCHAR(255) NOT NULL,
  provider_id VARCHAR(255) NOT NULL,
  access_token TEXT,
  refresh_token TEXT,
  access_token_expires_at TIMESTAMPTZ,
  refresh_token_expires_at TIMESTAMPTZ,
  scope TEXT,
  id_token TEXT,
  password TEXT,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  UNIQUE(provider_id, account_id)
);

Columns

• id: Account record ID
• user_id: Reference to user
• account_id: Provider-specific account identifier
• provider_id: Better Auth provider identifier; email/password accounts use credential
• access_token: Optional provider access token
• refresh_token: Optional provider refresh token
• access_token_expires_at: Optional access-token expiry
• refresh_token_expires_at: Optional refresh-token expiry
• scope: Optional provider scopes
• id_token: Optional provider identity token
• password: Argon2 password hash for credential accounts; excluded from serialization
• 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_id, account_id)

Verification Table

Database schema available for short-lived Better Auth verification records. The current password-reset flow stores verification values in Redis because secondaryStorage is configured and verification.storeInDatabase is not enabled. Email verification is not required for sign-up or sign-in.

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

Columns

• id: Verification record ID
• identifier: Email or user ID
• value: Better Auth verification value
• 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 Storage

There is no passwords table. Better Auth stores the Argon2 hash in accounts.password on the credential account. The Lucid model marks that field serializeAs: null, so API serialization does not expose it.

Security Implementation

Password Storage

• Better Auth calls the Argon2 hash and verify functions configured in app/lib/auth.ts
• Password hashes live in accounts.password, never in users

Session Management

• Better Auth stores runtime sessions and password-reset verification values in Redis
• The PostgreSQL sessions and verifications tables remain available but are not written by the current Better Auth storage configuration
• A signed cookie cache can satisfy session checks for up to 5 minutes
• Sessions expire after 7 days and refresh after 1 day of age
• Active organization context tracked per session