Yes — the clean low-level flow should use Keycloak as the central Identity Provider, and Student / Trainer / Consultant services should never handle passwords directly.

Recommended login pattern:

Browser → Student/Trainer App → Keycloak → Student/Trainer App → App Database

Use Authorization Code Flow with PKCE.

---

1. High-Level Login Flow

flowchart TD
    A[Browser / User] --> B[Student Portal]
    A --> C[Trainer Portal]

    B --> D[Keycloak Login Page]
    C --> D[Keycloak Login Page]

    D --> E[User enters username/password]
    E --> F[Keycloak validates credentials]

    F --> G{Login Success?}
    G -->|No| D
    G -->|Yes| H[Keycloak creates SSO session]

    H --> I[Redirect back to Student/Trainer App with authorization code]

    I --> J[App exchanges code for tokens]
    J --> K[Keycloak returns ID Token, Access Token, Refresh Token]

    K --> L[App reads user identity from token]
    L --> M[App checks local DB user mapping]

    M --> N{Role allowed?}
    N -->|Student role| O[Student Dashboard]
    N -->|Trainer role| P[Trainer Dashboard]
    N -->|No role| Q[Access Denied]

---

2. Detailed Student Login Flow

sequenceDiagram
    autonumber

    participant User as User / Browser
    participant StudentFE as Student Frontend
    participant StudentBE as Student Backend
    participant KC as Keycloak
    participant DB as Application DB

    User->>StudentFE: Open student portal
    StudentFE->>StudentBE: GET /student/dashboard

    StudentBE->>StudentBE: Check local app session

    alt No active session
        StudentBE-->>User: Redirect to Keycloak /authorize

        User->>KC: GET /realms/{realm}/protocol/openid-connect/auth
        Note over User,KC: Sends client_id, redirect_uri, scope, state, nonce, code_challenge

        KC-->>User: Show login page
        User->>KC: Submit username/password

        KC->>KC: Validate credentials
        KC->>KC: Create Keycloak SSO session cookie

        KC-->>User: Redirect to Student callback with authorization_code
        User->>StudentBE: GET /auth/callback?code=AUTH_CODE&state=STATE

        StudentBE->>StudentBE: Validate state

        StudentBE->>KC: POST /token
        Note over StudentBE,KC: Sends code, client_id, redirect_uri, code_verifier

        KC-->>StudentBE: Return ID Token, Access Token, Refresh Token

        StudentBE->>StudentBE: Validate ID token signature and claims
        StudentBE->>StudentBE: Extract sub, email, name, roles

        StudentBE->>DB: Find user by keycloak_user_id = token.sub

        alt User does not exist locally
            StudentBE->>DB: Create users row
            StudentBE->>DB: Create or check student role/profile
        else User exists
            StudentBE->>DB: Update last_login_at, email_verified, profile data if needed
        end

        StudentBE->>DB: Check user_service_roles contains student

        alt Has student access
            StudentBE->>StudentBE: Create local app session
            StudentBE-->>User: Set secure HttpOnly session cookie
            StudentBE-->>User: Redirect to /student/dashboard
        else No student role
            StudentBE-->>User: 403 Access Denied
        end

    else Active session exists
        StudentBE->>DB: Load student profile
        StudentBE-->>StudentFE: Return student dashboard
    end

---

3. Detailed Trainer Login Flow

Trainer login is almost the same, but the app checks for the trainer role instead of the student role.

sequenceDiagram
    autonumber

    participant User as User / Browser
    participant TrainerFE as Trainer Frontend
    participant TrainerBE as Trainer Backend
    participant KC as Keycloak
    participant DB as Application DB

    User->>TrainerFE: Open trainer portal
    TrainerFE->>TrainerBE: GET /trainer/dashboard

    TrainerBE->>TrainerBE: Check local app session

    alt No active session
        TrainerBE-->>User: Redirect to Keycloak authorization endpoint

        User->>KC: Authorization request
        Note over User,KC: client_id=trainer-app, redirect_uri, scope, state, nonce, code_challenge

        alt User already logged in to Keycloak from Student portal
            KC->>KC: Detect existing Keycloak SSO session
            KC-->>User: Redirect back with authorization_code
        else User not logged in
            KC-->>User: Show login page
            User->>KC: Submit credentials
            KC->>KC: Validate credentials
            KC->>KC: Create SSO session
            KC-->>User: Redirect back with authorization_code
        end

        User->>TrainerBE: GET /auth/callback?code=AUTH_CODE&state=STATE

        TrainerBE->>KC: Exchange authorization code for tokens
        KC-->>TrainerBE: ID Token, Access Token, Refresh Token

        TrainerBE->>TrainerBE: Validate token
        TrainerBE->>TrainerBE: Extract token.sub and roles

        TrainerBE->>DB: Find users.keycloak_user_id = token.sub
        TrainerBE->>DB: Check user_service_roles contains trainer
        TrainerBE->>DB: Check trainer_profiles.approval_status

        alt User has trainer access and approved
            TrainerBE->>TrainerBE: Create trainer app session
            TrainerBE-->>User: Set secure HttpOnly session cookie
            TrainerBE-->>User: Redirect to trainer dashboard
        else User is not trainer
            TrainerBE-->>User: 403 Not allowed for trainer portal
        else Trainer pending approval
            TrainerBE-->>User: Show pending approval page
        end

    else Active trainer session exists
        TrainerBE->>DB: Load trainer profile
        TrainerBE-->>TrainerFE: Return trainer dashboard
    end

---

4. What Data Passes Between Each System?

From	To	Data Passed	Purpose
Browser	Student/Trainer App	Request URL, cookies	User opens dashboard
App	Browser	Redirect to Keycloak	Start login
Browser	Keycloak	client_id, redirect_uri, scope, state, nonce, code_challenge	Start OIDC login
User	Keycloak	Username/password/OTP	Authentication
Keycloak	Browser	Authorization code	Temporary login code
Browser	App callback URL	code, state	Return login result
App Backend	Keycloak	code, client_id, code_verifier	Exchange code for tokens
Keycloak	App Backend	id_token, access_token, refresh_token	Authenticated user data
App Backend	App DB	keycloak_user_id, email, roles	Map Keycloak user to local app user
App Backend	Browser	Secure session cookie	Maintain app login session

---

5. Token Exchange Flow

flowchart LR
    A[Browser] -->|1. Open Student App| B[Student Backend]

    B -->|2. Redirect to /authorize| C[Keycloak]

    C -->|3. Login Page| A

    A -->|4. Username Password OTP| C

    C -->|5. Redirect with auth code| B

    B -->|6. Exchange code using token endpoint| C

    C -->|7. Return tokens| B

    B -->|8. Validate token and create app session| D[Application DB]

    B -->|9. Set HttpOnly cookie| A

---

6. Example Authorization Request to Keycloak

When user opens Student portal:

GET https://auth.example.com/realms/myrealm/protocol/openid-connect/auth
    ?client_id=student-app
    &redirect_uri=https://student.example.com/auth/callback
    &response_type=code
    &scope=openid profile email
    &state=random_csrf_value
    &nonce=random_nonce_value
    &code_challenge=generated_pkce_challenge
    &code_challenge_method=S256

For Trainer portal:

GET https://auth.example.com/realms/myrealm/protocol/openid-connect/auth
    ?client_id=trainer-app
    &redirect_uri=https://trainer.example.com/auth/callback
    &response_type=code
    &scope=openid profile email
    &state=random_csrf_value
    &nonce=random_nonce_value
    &code_challenge=generated_pkce_challenge
    &code_challenge_method=S256

---

7. Example Token Response from Keycloak

{
  "access_token": "eyJhbGciOi...",
  "expires_in": 300,
  "refresh_expires_in": 1800,
  "refresh_token": "eyJhbGciOi...",
  "token_type": "Bearer",
  "id_token": "eyJhbGciOi...",
  "scope": "openid profile email"
}

---

8. Important Claims Inside Token

Your backend mainly needs these fields:

{
  "sub": "8d5f9b18-16c2-49f3-90e2-123456789abc",
  "email": "user@example.com",
  "email_verified": true,
  "preferred_username": "rajesh",
  "given_name": "Rajesh",
  "family_name": "Kumar",
  "realm_access": {
    "roles": [
      "student",
      "trainer"
    ]
  }
}

Application DB mapping:

token.sub  → users.keycloak_user_id
email      → users.email
roles      → user_service_roles

---

9. Student to Trainer SSO Flow

If the user already logged in as Student and then opens Trainer portal, they should not enter password again.

sequenceDiagram
    autonumber

    participant User as Browser
    participant Student as Student App
    participant Trainer as Trainer App
    participant KC as Keycloak
    participant DB as App DB

    User->>Student: Login to student portal
    Student->>KC: Redirect user to Keycloak
    KC->>User: Login page
    User->>KC: Submit credentials
    KC->>KC: Create Keycloak SSO session
    KC-->>Student: Return authorization code
    Student->>KC: Exchange code for tokens
    KC-->>Student: Tokens
    Student->>DB: Check student role
    Student-->>User: Student dashboard

    User->>Trainer: Open trainer portal
    Trainer->>KC: Redirect to Keycloak authorize endpoint

    KC->>KC: Existing SSO session found
    KC-->>Trainer: Return authorization code without asking password

    Trainer->>KC: Exchange code for tokens
    KC-->>Trainer: Tokens

    Trainer->>DB: Check trainer role/profile

    alt User has trainer role
        Trainer-->>User: Trainer dashboard
    else User does not have trainer role
        Trainer-->>User: Access denied or apply as trainer
    end

---

10. Access Control Decision Flow

flowchart TD
    A[User logged in via Keycloak] --> B[App receives token]
    B --> C[Extract keycloak_user_id from token.sub]
    C --> D[Find user in local DB]

    D --> E{User exists?}
    E -->|No| F[Create local user profile]
    E -->|Yes| G[Load existing user]

    F --> H[Check token roles]
    G --> H[Check token roles]

    H --> I{Requested service?}

    I -->|Student Portal| J{Has student role?}
    I -->|Trainer Portal| K{Has trainer role?}
    I -->|Consultant Portal| L{Has consultant role?}

    J -->|Yes| M[Allow student dashboard]
    J -->|No| N[Deny or show registration]

    K -->|Yes| O[Check trainer profile approval]
    K -->|No| P[Deny or apply as trainer]

    O -->|Approved| Q[Allow trainer dashboard]
    O -->|Pending| R[Show pending approval]

    L -->|Yes| S[Check consultant approval]
    L -->|No| T[Deny or apply as consultant]

---

11. Refresh Token Flow

When access token expires, the app should refresh it.

sequenceDiagram
    autonumber

    participant Browser as Browser
    participant App as Student/Trainer Backend
    participant KC as Keycloak
    participant DB as App DB

    Browser->>App: Request dashboard/API
    App->>App: Check app session and token expiry

    alt Access token still valid
        App->>DB: Load required data
        App-->>Browser: Return response
    else Access token expired
        App->>KC: POST /token with grant_type=refresh_token
        KC-->>App: New access token and refresh token

        alt Refresh success
            App->>App: Update session token data
            App->>DB: Load required data
            App-->>Browser: Return response
        else Refresh failed
            App-->>Browser: Redirect to Keycloak login
        end
    end

---

12. Logout Flow

sequenceDiagram
    autonumber

    participant User as Browser
    participant App as Student/Trainer App
    participant KC as Keycloak

    User->>App: Click logout

    App->>App: Destroy local app session
    App-->>User: Clear app session cookie

    App-->>User: Redirect to Keycloak logout endpoint

    User->>KC: GET /logout?id_token_hint=ID_TOKEN&post_logout_redirect_uri=APP_URL

    KC->>KC: Destroy Keycloak SSO session
    KC-->>User: Redirect back to public homepage

---

13. Recommended Keycloak Clients

Service	Keycloak Client ID	Type	Redirect URI
Student Portal	student-app	Confidential or Public with PKCE	https://student.example.com/auth/callback
Trainer Portal	trainer-app	Confidential or Public with PKCE	https://trainer.example.com/auth/callback
Consultant Portal	consultant-app	Confidential or Public with PKCE	https://consultant.example.com/auth/callback
Admin Portal	admin-app	Confidential	https://admin.example.com/auth/callback

For backend-based apps, use confidential clients.

For pure SPA/mobile apps, use public clients with PKCE.

---

14. Recommended Local Tables Used During Login

users
user_service_roles
student_profiles
trainer_profiles
consultant_profiles
audit_logs

Main lookup:

SELECT *
FROM users
WHERE keycloak_user_id = '<token.sub>';

Check student role:

SELECT *
FROM user_service_roles
WHERE user_id = 10
AND service_type = 'student'
AND status = 'active';

Check trainer role:

SELECT *
FROM user_service_roles
WHERE user_id = 10
AND service_type = 'trainer'
AND status = 'active';

Check trainer approval:

SELECT *
FROM trainer_profiles
WHERE user_id = 10
AND approval_status = 'approved'
AND status = 'active';

---

15. Best Final Design

flowchart TD
    Browser[Browser] --> StudentApp[Student App]
    Browser --> TrainerApp[Trainer App]
    Browser --> ConsultantApp[Consultant App]

    StudentApp --> Keycloak[Keycloak]
    TrainerApp --> Keycloak
    ConsultantApp --> Keycloak

    Keycloak --> StudentApp
    Keycloak --> TrainerApp
    Keycloak --> ConsultantApp

    StudentApp --> AppDB[(Application Database)]
    TrainerApp --> AppDB
    ConsultantApp --> AppDB

    AppDB --> Users[users table]
    AppDB --> Roles[user_service_roles table]
    AppDB --> StudentProfile[student_profiles table]
    AppDB --> TrainerProfile[trainer_profiles table]
    AppDB --> ConsultantProfile[consultant_profiles table]

The golden rule:

Keycloak decides: Who are you?
Application DB decides: What can you do inside Student/Trainer/Consultant service?

Below is a low-level database schema for your platform with 3 services:

1. Student Service
2. Trainer Service
3. Consultant Service
4. Authentication handled by Keycloak

Important: Do not store passwords in your application database. Keycloak will manage login, password, MFA, social login, sessions, roles, etc. Your app should only store the Keycloak user reference.

---

Recommended Architecture

Keycloak
  |
  | JWT Token
  |
Application Backend
  |
  | keycloak_user_id / sub
  |
Application Database
  |
  |-- common user profile
  |-- student profile
  |-- trainer profile
  |-- consultant profile
  |-- courses
  |-- batches
  |-- enrollments
  |-- bookings
  |-- consulting requests
  |-- payments

---

1. Common User Tables

Table: users

This table maps your application user with Keycloak user.

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    keycloak_user_id VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(150) NULL,

    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    phone VARCHAR(30) NULL,
    profile_image VARCHAR(500) NULL,

    status ENUM('active', 'inactive', 'blocked', 'deleted') DEFAULT 'active',

    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,

    last_login_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_keycloak_user_id (keycloak_user_id),
    INDEX idx_users_email (email),
    INDEX idx_users_status (status)
);

---

Table: user_service_roles

One user can be a student, trainer, consultant, or all three.

CREATE TABLE user_service_roles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL,
    service_type ENUM('student', 'trainer', 'consultant') NOT NULL,

    status ENUM('active', 'inactive', 'pending', 'rejected') DEFAULT 'active',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_user_service (user_id, service_type),

    CONSTRAINT fk_user_service_roles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

Example:

User Rajesh can have:
- student
- trainer
- consultant

So instead of creating separate login tables, keep one user identity and attach multiple service roles.

---

2. Student Service Tables

Table: student_profiles

CREATE TABLE student_profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL UNIQUE,

    student_code VARCHAR(50) UNIQUE NULL,
    education_level VARCHAR(100) NULL,
    current_company VARCHAR(150) NULL,
    job_title VARCHAR(150) NULL,

    country VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    timezone VARCHAR(100) NULL,

    learning_goal TEXT NULL,
    experience_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',

    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_student_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

Table: courses

CREATE TABLE courses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,

    short_description VARCHAR(500) NULL,
    description LONGTEXT NULL,

    category VARCHAR(150) NULL,
    level ENUM('foundation', 'essential', 'intermediate', 'advanced') DEFAULT 'foundation',

    duration_hours INT DEFAULT 0,
    language VARCHAR(100) DEFAULT 'English',

    price DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',

    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_courses_slug (slug),
    INDEX idx_courses_status (status),
    INDEX idx_courses_category (category)
);

---

Table: course_modules

CREATE TABLE course_modules (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    course_id BIGINT NOT NULL,

    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    sort_order INT DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_course_modules_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE
);

---

Table: lessons

CREATE TABLE lessons (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    course_id BIGINT NOT NULL,
    module_id BIGINT NULL,

    title VARCHAR(255) NOT NULL,
    content LONGTEXT NULL,

    lesson_type ENUM('video', 'text', 'quiz', 'assignment', 'live_session') DEFAULT 'text',

    video_url VARCHAR(500) NULL,
    duration_minutes INT DEFAULT 0,

    sort_order INT DEFAULT 0,
    is_free_preview BOOLEAN DEFAULT FALSE,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_lessons_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_lessons_module
        FOREIGN KEY (module_id) REFERENCES course_modules(id)
        ON DELETE SET NULL
);

---

Table: student_enrollments

CREATE TABLE student_enrollments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,

    enrollment_status ENUM('enrolled', 'in_progress', 'completed', 'cancelled') DEFAULT 'enrolled',

    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,

    progress_percentage DECIMAL(5,2) DEFAULT 0.00,

    certificate_issued BOOLEAN DEFAULT FALSE,
    certificate_url VARCHAR(500) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_student_course (student_id, course_id),

    CONSTRAINT fk_student_enrollments_student
        FOREIGN KEY (student_id) REFERENCES student_profiles(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_student_enrollments_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE
);

---

Table: student_lesson_progress

CREATE TABLE student_lesson_progress (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    lesson_id BIGINT NOT NULL,

    status ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started',

    started_at DATETIME NULL,
    completed_at DATETIME NULL,

    watched_seconds INT DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_student_lesson (student_id, lesson_id),

    CONSTRAINT fk_progress_student
        FOREIGN KEY (student_id) REFERENCES student_profiles(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_progress_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_progress_lesson
        FOREIGN KEY (lesson_id) REFERENCES lessons(id)
        ON DELETE CASCADE
);

---

3. Trainer Service Tables

Table: trainer_profiles

CREATE TABLE trainer_profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL UNIQUE,

    trainer_code VARCHAR(50) UNIQUE NULL,

    headline VARCHAR(255) NULL,
    bio TEXT NULL,

    total_experience_years DECIMAL(4,1) DEFAULT 0.0,
    primary_skill VARCHAR(150) NULL,

    linkedin_url VARCHAR(500) NULL,
    website_url VARCHAR(500) NULL,

    hourly_rate DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',

    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_trainer_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

Table: trainer_skills

CREATE TABLE trainer_skills (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    trainer_id BIGINT NOT NULL,

    skill_name VARCHAR(150) NOT NULL,
    experience_years DECIMAL(4,1) DEFAULT 0.0,

    skill_level ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_trainer_skills_trainer
        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)
        ON DELETE CASCADE,

    INDEX idx_trainer_skills_skill_name (skill_name)
);

---

Table: trainer_courses

This connects trainers with courses.

CREATE TABLE trainer_courses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    trainer_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,

    role ENUM('primary', 'assistant', 'guest') DEFAULT 'primary',

    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_trainer_course (trainer_id, course_id),

    CONSTRAINT fk_trainer_courses_trainer
        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_trainer_courses_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE
);

---

Table: training_batches

CREATE TABLE training_batches (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    course_id BIGINT NOT NULL,
    trainer_id BIGINT NOT NULL,

    batch_name VARCHAR(255) NOT NULL,

    start_date DATE NOT NULL,
    end_date DATE NULL,

    start_time TIME NULL,
    end_time TIME NULL,

    timezone VARCHAR(100) DEFAULT 'UTC',

    mode ENUM('online', 'offline', 'hybrid') DEFAULT 'online',

    meeting_url VARCHAR(500) NULL,
    location VARCHAR(500) NULL,

    max_students INT DEFAULT 0,

    status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_training_batches_course
        FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_training_batches_trainer
        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)
        ON DELETE CASCADE
);

---

Table: batch_students

CREATE TABLE batch_students (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    batch_id BIGINT NOT NULL,
    student_id BIGINT NOT NULL,

    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    status ENUM('active', 'completed', 'dropped') DEFAULT 'active',

    UNIQUE KEY uq_batch_student (batch_id, student_id),

    CONSTRAINT fk_batch_students_batch
        FOREIGN KEY (batch_id) REFERENCES training_batches(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_batch_students_student
        FOREIGN KEY (student_id) REFERENCES student_profiles(id)
        ON DELETE CASCADE
);

---

Table: batch_sessions

CREATE TABLE batch_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    batch_id BIGINT NOT NULL,

    session_title VARCHAR(255) NOT NULL,
    session_description TEXT NULL,

    session_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,

    meeting_url VARCHAR(500) NULL,

    status ENUM('scheduled', 'completed', 'cancelled') DEFAULT 'scheduled',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_batch_sessions_batch
        FOREIGN KEY (batch_id) REFERENCES training_batches(id)
        ON DELETE CASCADE
);

---

Table: student_attendance

CREATE TABLE student_attendance (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    session_id BIGINT NOT NULL,
    student_id BIGINT NOT NULL,

    attendance_status ENUM('present', 'absent', 'late') DEFAULT 'absent',

    joined_at DATETIME NULL,
    left_at DATETIME NULL,

    remarks TEXT NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_session_student_attendance (session_id, student_id),

    CONSTRAINT fk_attendance_session
        FOREIGN KEY (session_id) REFERENCES batch_sessions(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_attendance_student
        FOREIGN KEY (student_id) REFERENCES student_profiles(id)
        ON DELETE CASCADE
);

---

4. Consultant Service Tables

Table: consultant_profiles

CREATE TABLE consultant_profiles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL UNIQUE,

    consultant_code VARCHAR(50) UNIQUE NULL,

    headline VARCHAR(255) NULL,
    bio TEXT NULL,

    total_experience_years DECIMAL(4,1) DEFAULT 0.0,

    primary_domain VARCHAR(150) NULL,

    hourly_rate DECIMAL(10,2) DEFAULT 0.00,
    daily_rate DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',

    linkedin_url VARCHAR(500) NULL,
    portfolio_url VARCHAR(500) NULL,

    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_consultant_profiles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

Table: consultant_skills

CREATE TABLE consultant_skills (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    consultant_id BIGINT NOT NULL,

    skill_name VARCHAR(150) NOT NULL,
    experience_years DECIMAL(4,1) DEFAULT 0.0,

    skill_level ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'expert',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_consultant_skills_consultant
        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)
        ON DELETE CASCADE,

    INDEX idx_consultant_skills_skill_name (skill_name)
);

---

Table: consulting_services

These are services offered by consultants.

CREATE TABLE consulting_services (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    consultant_id BIGINT NOT NULL,

    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,

    description LONGTEXT NULL,

    service_category VARCHAR(150) NULL,

    pricing_type ENUM('hourly', 'fixed', 'daily', 'custom') DEFAULT 'hourly',

    price DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',

    estimated_duration_hours INT DEFAULT 0,

    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_consulting_services_consultant
        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)
        ON DELETE CASCADE
);

---

Table: consulting_requests

This is when a student/client requests consulting.

CREATE TABLE consulting_requests (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    requested_by_user_id BIGINT NOT NULL,
    consultant_id BIGINT NULL,
    consulting_service_id BIGINT NULL,

    title VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,

    budget DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'USD',

    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',

    request_status ENUM(
        'open',
        'assigned',
        'accepted',
        'in_progress',
        'completed',
        'cancelled',
        'rejected'
    ) DEFAULT 'open',

    expected_start_date DATE NULL,
    expected_end_date DATE NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_consulting_requests_user
        FOREIGN KEY (requested_by_user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_consulting_requests_consultant
        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_consulting_requests_service
        FOREIGN KEY (consulting_service_id) REFERENCES consulting_services(id)
        ON DELETE SET NULL
);

---

Table: consulting_bookings

CREATE TABLE consulting_bookings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    consulting_request_id BIGINT NOT NULL,
    consultant_id BIGINT NOT NULL,
    booked_by_user_id BIGINT NOT NULL,

    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,

    timezone VARCHAR(100) DEFAULT 'UTC',

    meeting_url VARCHAR(500) NULL,

    booking_status ENUM(
        'pending',
        'confirmed',
        'completed',
        'cancelled',
        'rescheduled'
    ) DEFAULT 'pending',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_consulting_bookings_request
        FOREIGN KEY (consulting_request_id) REFERENCES consulting_requests(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_consulting_bookings_consultant
        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_consulting_bookings_user
        FOREIGN KEY (booked_by_user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

5. Payment Tables

Table: payments

Use this for course enrollment, trainer booking, consultant booking, etc.

CREATE TABLE payments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL,

    payment_for ENUM('course', 'batch', 'consulting', 'subscription') NOT NULL,

    reference_id BIGINT NOT NULL,

    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',

    payment_gateway ENUM('paypal', 'stripe', 'razorpay', 'paytm', 'bank_transfer') NULL,

    gateway_transaction_id VARCHAR(255) NULL,

    payment_status ENUM(
        'pending',
        'paid',
        'failed',
        'refunded',
        'cancelled'
    ) DEFAULT 'pending',

    paid_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_payments_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_payments_reference (payment_for, reference_id),
    INDEX idx_payments_status (payment_status)
);

---

Table: invoices

CREATE TABLE invoices (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    payment_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,

    invoice_number VARCHAR(100) NOT NULL UNIQUE,

    invoice_date DATE NOT NULL,

    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,

    currency VARCHAR(10) DEFAULT 'USD',

    invoice_url VARCHAR(500) NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_invoices_payment
        FOREIGN KEY (payment_id) REFERENCES payments(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_invoices_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

6. Review and Rating Tables

Table: reviews

CREATE TABLE reviews (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    reviewer_user_id BIGINT NOT NULL,

    review_for ENUM('course', 'trainer', 'consultant', 'consulting_service') NOT NULL,
    reference_id BIGINT NOT NULL,

    rating INT NOT NULL,
    review_text TEXT NULL,

    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_reviews_user
        FOREIGN KEY (reviewer_user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_reviews_reference (review_for, reference_id),
    INDEX idx_reviews_rating (rating)
);

---

7. Documents and Verification

Useful for trainer and consultant approval.

Table: user_documents

CREATE TABLE user_documents (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL,

    document_type ENUM(
        'resume',
        'certificate',
        'id_proof',
        'address_proof',
        'experience_letter',
        'other'
    ) NOT NULL,

    document_name VARCHAR(255) NULL,
    document_url VARCHAR(500) NOT NULL,

    verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',

    remarks TEXT NULL,

    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    verified_at DATETIME NULL,

    CONSTRAINT fk_user_documents_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

---

8. Notification Tables

Table: notifications

CREATE TABLE notifications (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NOT NULL,

    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,

    notification_type ENUM(
        'course',
        'batch',
        'payment',
        'consulting',
        'system'
    ) DEFAULT 'system',

    is_read BOOLEAN DEFAULT FALSE,

    read_at DATETIME NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_notifications_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_notifications_user_read (user_id, is_read)
);

---

9. Audit Log Table

Very useful for admin and security.

CREATE TABLE audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT NULL,

    action VARCHAR(255) NOT NULL,
    module VARCHAR(100) NOT NULL,

    ip_address VARCHAR(100) NULL,
    user_agent TEXT NULL,

    old_value JSON NULL,
    new_value JSON NULL,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_audit_logs_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_audit_logs_user (user_id),
    INDEX idx_audit_logs_module (module)
);

---

Keycloak Role Mapping

In Keycloak, create these roles:

student
trainer
consultant
admin
super_admin

Recommended Keycloak groups:

/students
/trainers
/consultants
/admins

Your JWT token may contain:

{
  "sub": "keycloak-user-id",
  "email": "user@example.com",
  "preferred_username": "rajesh",
  "realm_access": {
    "roles": ["student", "trainer"]
  }
}

Your application will use:

JWT sub = users.keycloak_user_id

---

Final Table List

Module	Tables
Common	users, user_service_roles
Student	student_profiles, student_enrollments, student_lesson_progress
Trainer	trainer_profiles, trainer_skills, trainer_courses, training_batches, batch_students, batch_sessions, student_attendance
Consultant	consultant_profiles, consultant_skills, consulting_services, consulting_requests, consulting_bookings
Course	courses, course_modules, lessons
Payment	payments, invoices
Review	reviews
Document	user_documents
Notification	notifications
Security	audit_logs

---

Best Practice

Use this structure:

Keycloak = Authentication + Authorization
Application DB = Business Data

Do not create separate login tables for student, trainer, and consultant. Keep one central users table and then create separate profile tables for each service. This keeps the system clean, scalable, and easy to maintain.