Database Schema¶

The authentication system uses PostgreSQL with Drizzle ORM to manage users, organizations, teams, and permissions.

Entity Relationship Diagram¶

erDiagram
    USER ||--o{ MEMBER : "has many"
    USER ||--o{ TEAM_MEMBER : "has many"
    USER ||--o{ SESSION : "has many"
    USER ||--o{ INVITATION : "sends"

    ORGANIZATION ||--o{ MEMBER : "has many"
    ORGANIZATION ||--o{ TEAM : "has many"
    ORGANIZATION ||--o{ INVITATION : "has many"

    TEAM ||--o{ TEAM_MEMBER : "has many"
    TEAM }|--|| ORGANIZATION : "belongs to"

    MEMBER }|--|| USER : "references"
    MEMBER }|--|| ORGANIZATION : "references"

    TEAM_MEMBER }|--|| USER : "references"
    TEAM_MEMBER }|--|| TEAM : "references"

Core Entities¶

User¶

The base user table storing user account information.

{
  id: string (primary key)
  name: string (required)
  email: string (required, unique)
  emailVerified: boolean (default: false)
  image: string (optional)
  createdAt: timestamp
  updatedAt: timestamp
  twoFactorEnabled: boolean (default: false)
  stripeCustomerId: string (optional)
  phoneNumber: string (optional, unique)
  phoneNumberVerified: boolean (optional)
  role: string (optional)
  banned: boolean (default: false)
  banReason: string (optional)
  banExpires: timestamp (optional)
  position: string (default: "assistant") // Custom field
  tutorialProgress: jsonb (default: "{}") // Custom field
}

Custom Fields: - position: User role/position classification ("assistant", "coach", "administrator", "player") - tutorialProgress: JSONB field tracking user tutorial completion state

Organization¶

Represents a top-level organization entity (e.g., a sports team).

{
  id: string (primary key)
  name: string (required, 2-100 characters)
  slug: string (required, unique, lowercase alphanumeric + hyphens)
  logo: string (optional)
  createdAt: timestamp
  metadata: text (JSON string containing):
    - slug: string
    - name: string
    - serverHostURL: string
    - organizationType: "enterprise" | "subscription" | "free"
    - licenseType: string
    - enterpriseContract: object
}

The metadata field stores structured JSON data including organization type, licensing information, and server configuration.

Team¶

Teams belong to organizations and contain team members for granular access control.

{
  id: string (primary key)
  name: string (required, 2-100 characters, unique within org)
  organizationId: string (foreign key → organization.id)
  createdAt: timestamp
  updatedAt: timestamp
}

Team names must be unique within their parent organization.

Member¶

Junction table linking users to organizations with roles.

{
  id: string (primary key)
  organizationId: string (foreign key → organization.id)
  userId: string (foreign key → user.id)
  role: "owner" | "admin" | "member" (default: "member")
  createdAt: timestamp
}

Team Member¶

Junction table linking users to teams within an organization.

{
  id: string (primary key)
  teamId: string (foreign key → team.id)
  userId: string (foreign key → user.id)
  createdAt: timestamp
}

Invitation¶

Stores pending invitations to organizations, supporting role assignment and expiration.

{
  id: string (primary key)
  organizationId: string (foreign key → organization.id)
  email: string (required)
  role: "owner" | "admin" | "member" (optional)
  status: "pending" | "accepted" | "rejected" (default: "pending")
  expiresAt: timestamp (default: 7 days from creation)
  createdAt: timestamp
  inviterId: string (foreign key → user.id)
  teamId: string (optional, foreign key → team.id)
}

Invitations expire after 7 days and can optionally be scoped to a specific team.

Session¶

User session management with active organization and team context.

{
  id: string (primary key)
  expiresAt: timestamp (required)
  token: string (required, unique)
  createdAt: timestamp
  updatedAt: timestamp
  ipAddress: string (optional)
  userAgent: string (optional)
  userId: string (foreign key → user.id)
  activeOrganizationId: string (optional)
  activeTeamId: string (optional)
  impersonatedBy: string (optional)
}

Sessions track the user's current organizational and team context for request handling.

Supporting Tables¶

The system also manages several supporting tables provided by Better Auth:

Account: OAuth provider accounts and linked identities
Verification: Email and phone verification tokens
Two Factor: 2FA secrets and backup codes
Passkey: WebAuthn passkey credentials
Subscription: Stripe subscription data
JWKS: JSON Web Key Set for JWT signing

Entity Relationships¶

User Relationships¶

User
├── has many → Accounts (OAuth providers)
├── has many → Sessions (active sessions)
├── has many → Members (organization memberships)
├── has many → Team Members (team memberships)
├── has many → Invitations (sent invitations)
├── has many → Passkeys (WebAuthn credentials)
└── has many → Two Factor records (2FA secrets)

Organization Relationships¶

Organization
├── has many → Members (users with roles)
├── has many → Teams (sub-groups)
└── has many → Invitations (pending invites)

Team Relationships¶

Team
├── belongs to → Organization
└── has many → Team Members (users assigned to team)

Permission Flow¶

User
  → Member (with role)
    → Organization
      → Team
        → Team Member

A user's permission level is determined by their role in the Member record. Team Members extend access to specific team resources.

Constraints & Indexes¶

Unique Constraints¶

User email (globally unique)
User phone number (globally unique)
Organization slug (globally unique)
Session token (globally unique)
Organization name (case-insensitive, enforced in application)
Team name (unique within organization)

Foreign Key Constraints¶

All foreign key relationships use cascade deletes to prevent orphaned records when parent entities are deleted.

Query Indexes¶

Optimized indexes exist for common query patterns:

User lookups by email or phone number
Organization lookups by slug
Member lookups by userId or organizationId
Team member lookups by userId or teamId
Session lookups by userId or token

Best Practices¶

Database Guidelines

Follow these practices to maintain data integrity.

Always validate input before database operations
Use database constraints as the source of truth for uniqueness
Handle cascade deletes carefully to avoid data loss
Log important events for auditing and debugging
Sync organization changes with the Go backend service
Maintain at least one owner per organization