Database Schema

Complete data model and database schema for OMOBL SSO

PostgreSQL

Prisma ORM

16 Models

Schema Overview

Core entities and their relationships

Identity & Access

• User - Core user accounts
• Group - Hierarchical organizations
• UserGroup - Group memberships with roles
• Invitation - User invitation workflow

Applications

• Application - OAuth/OIDC applications
• GroupApp - Group-to-app assignments
• UserAppAccess - User-specific app visibility

Sessions & Security

• Session - User session tracking
• AppSession - Application sessions with OAuth tokens
• AuditLog - Comprehensive audit trail

Analytics & Customization

• AppAnalytics - Pre-aggregated metrics
• Theme - Custom theme configurations

Core Models

User

Core user account data extending Clerk authentication

model User {
  id                String         @id @default(uuid())
  clerkId           String         @unique
  email             String         @unique
  firstName         String?
  lastName          String?
  avatarUrl         String?
  role              UserRole       @default(MEMBER)

  // Theme & Preferences
  themePreference   String         @default("system")
  customThemeId     String?
  customTheme       Theme?         @relation(fields: [customThemeId])

  // Session Tracking
  lastLoginAt       DateTime?
  lastActiveAt      DateTime?
  loginCount        Int            @default(0)

  // Metadata
  createdAt         DateTime       @default(now())
  updatedAt         DateTime       @updatedAt
  deletedAt         DateTime?

  // Relations
  groupMemberships  UserGroup[]
  sessions          Session[]
  appSessions       AppSession[]
  appAccess         UserAppAccess[]
  invitations       Invitation[]
  auditLogs         AuditLog[]
}

Primary Key:id (UUID)

Unique Indexes:clerkId, email

Group

Hierarchical organization groups with unlimited nesting

model Group {
  id              String      @id @default(uuid())
  name            String
  description     String?

  // Hierarchy
  parentId        String?
  parent          Group?      @relation("GroupHierarchy", fields: [parentId])
  subgroups       Group[]     @relation("GroupHierarchy")

  // Theme
  themeId         String?
  theme           Theme?      @relation(fields: [themeId])

  // Metadata
  createdAt       DateTime    @default(now())
  updatedAt       DateTime    @updatedAt

  // Relations
  members         UserGroup[]
  apps            GroupApp[]
}

Application

OAuth/OIDC-enabled third-party applications

model Application {
  id                String         @id @default(uuid())
  name              String
  description       String?

  // OAuth/OIDC Configuration
  clientId          String         @unique
  clientSecret      String         // Encrypted with AES-256-GCM
  redirectUris      String         // JSON array stored as string
  allowedScopes     String?        // JSON array
  grantTypes        String         // JSON array

  // Metadata
  logoUrl           String?
  websiteUrl        String?
  privacyPolicyUrl  String?
  termsOfServiceUrl String?

  // Status
  isActive          Boolean        @default(true)
  createdAt         DateTime       @default(now())
  updatedAt         DateTime       @updatedAt

  // Relations
  groupApps         GroupApp[]
  userAccess        UserAppAccess[]
  sessions          AppSession[]
  analytics         AppAnalytics[]
}

Junction Tables

UserGroup

Many-to-many relationship between users and groups with roles

model UserGroup {
  id          String     @id @default(uuid())
  userId      String
  user        User       @relation(fields: [userId])
  groupId     String
  group       Group      @relation(fields: [groupId])

  // Group-level role
  groupRole   GroupRole  @default(MEMBER)

  // Metadata
  joinedAt    DateTime   @default(now())

  @@unique([userId, groupId])
}

GroupRole enum: GROUP_ADMIN, MEMBER

UserAppAccess

User-specific application visibility overrides

model UserAppAccess {
  id            String      @id @default(uuid())
  userId        String
  user          User        @relation(fields: [userId])
  applicationId String
  application   Application @relation(fields: [applicationId])

  // Visibility control
  isVisible     Boolean     @default(true)

  // Metadata
  grantedAt     DateTime    @default(now())
  grantedBy     String?     // User ID who granted access

  @@unique([userId, applicationId])
}

Session Management

Session & AppSession

User sessions and application-specific OAuth sessions

model Session {
  id          String    @id @default(uuid())
  userId      String
  user        User      @relation(fields: [userId])
  clerkId     String    @unique

  // Session metadata
  ipAddress   String?
  userAgent   String?
  device      String?
  location    String?

  // Timestamps
  startedAt   DateTime  @default(now())
  lastActiveAt DateTime @default(now())
  endedAt     DateTime?
}

model AppSession {
  id              String      @id @default(uuid())
  userId          String
  user            User        @relation(fields: [userId])
  applicationId   String
  application     Application @relation(fields: [applicationId])

  // OAuth tokens (encrypted)
  accessToken     String
  refreshToken    String?
  expiresAt       DateTime

  // Timestamps
  createdAt       DateTime    @default(now())
  lastUsedAt      DateTime    @default(now())
  revokedAt       DateTime?
}

Enumerations

UserRole

MASTER_ADMIN

GROUP_ADMIN

MEMBER

GroupRole

GROUP_ADMIN

MEMBER

AuditAction

• CREATE, UPDATE, DELETE

• LOGIN, LOGOUT

• GRANT_ACCESS, REVOKE_ACCESS

InvitationStatus

• PENDING

• ACCEPTED

• EXPIRED

Related Documentation

Authentication & Clerk Integration - How Clerk syncs with the User model
Authorization & RBAC - Permission system using UserRole and GroupRole
Group Hierarchy - Managing nested groups and permissions
Audit Logging - AuditLog model and compliance