{"id":1003,"date":"2026-05-22T07:21:02","date_gmt":"2026-05-22T07:21:02","guid":{"rendered":"https:\/\/www.devopsschool.com\/tutorials\/?p=1003"},"modified":"2026-05-22T07:21:03","modified_gmt":"2026-05-22T07:21:03","slug":"low-level-authentication-flow-design-for-student-trainer-and-consultant-services-using-keycloak","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/tutorials\/low-level-authentication-flow-design-for-student-trainer-and-consultant-services-using-keycloak\/","title":{"rendered":"Low-Level Authentication Flow Design for Student, Trainer, and Consultant Services Using Keycloak"},"content":{"rendered":"\n

Yes \u2014 the clean low-level flow should use Keycloak as the central Identity Provider<\/strong>, and Student \/ Trainer \/ Consultant services should never handle passwords directly<\/strong>.<\/p>\n\n\n\n

Recommended login pattern:<\/p>\n\n\n\n

Browser \u2192 Student\/Trainer App \u2192 Keycloak \u2192 Student\/Trainer App \u2192 App Database\n<\/code><\/pre>\n\n\n\n
Use Authorization Code Flow with PKCE<\/strong>.<\/p>\n\n\n\n
\n\n\n\n
1. High-Level Login Flow<\/h1>\n\n\n\n
flowchart TD\n    A[Browser \/ User] --> B[Student Portal]\n    A --> C[Trainer Portal]\n\n    B --> D[Keycloak Login Page]\n    C --> D[Keycloak Login Page]\n\n    D --> E[User enters username\/password]\n    E --> F[Keycloak validates credentials]\n\n    F --> G{Login Success?}\n    G -->|No| D\n    G -->|Yes| H[Keycloak creates SSO session]\n\n    H --> I[Redirect back to Student\/Trainer App with authorization code]\n\n    I --> J[App exchanges code for tokens]\n    J --> K[Keycloak returns ID Token, Access Token, Refresh Token]\n\n    K --> L[App reads user identity from token]\n    L --> M[App checks local DB user mapping]\n\n    M --> N{Role allowed?}\n    N -->|Student role| O[Student Dashboard]\n    N -->|Trainer role| P[Trainer Dashboard]\n    N -->|No role| Q[Access Denied]\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
2. Detailed Student Login Flow<\/h1>\n\n\n\n
sequenceDiagram\n    autonumber\n\n    participant User as User \/ Browser\n    participant StudentFE as Student Frontend\n    participant StudentBE as Student Backend\n    participant KC as Keycloak\n    participant DB as Application DB\n\n    User->>StudentFE: Open student portal\n    StudentFE->>StudentBE: GET \/student\/dashboard\n\n    StudentBE->>StudentBE: Check local app session\n\n    alt No active session\n        StudentBE-->>User: Redirect to Keycloak \/authorize\n\n        User->>KC: GET \/realms\/{realm}\/protocol\/openid-connect\/auth\n        Note over User,KC: Sends client_id, redirect_uri, scope, state, nonce, code_challenge\n\n        KC-->>User: Show login page\n        User->>KC: Submit username\/password\n\n        KC->>KC: Validate credentials\n        KC->>KC: Create Keycloak SSO session cookie\n\n        KC-->>User: Redirect to Student callback with authorization_code\n        User->>StudentBE: GET \/auth\/callback?code=AUTH_CODE&state=STATE\n\n        StudentBE->>StudentBE: Validate state\n\n        StudentBE->>KC: POST \/token\n        Note over StudentBE,KC: Sends code, client_id, redirect_uri, code_verifier\n\n        KC-->>StudentBE: Return ID Token, Access Token, Refresh Token\n\n        StudentBE->>StudentBE: Validate ID token signature and claims\n        StudentBE->>StudentBE: Extract sub, email, name, roles\n\n        StudentBE->>DB: Find user by keycloak_user_id = token.sub\n\n        alt User does not exist locally\n            StudentBE->>DB: Create users row\n            StudentBE->>DB: Create or check student role\/profile\n        else User exists\n            StudentBE->>DB: Update last_login_at, email_verified, profile data if needed\n        end\n\n        StudentBE->>DB: Check user_service_roles contains student\n\n        alt Has student access\n            StudentBE->>StudentBE: Create local app session\n            StudentBE-->>User: Set secure HttpOnly session cookie\n            StudentBE-->>User: Redirect to \/student\/dashboard\n        else No student role\n            StudentBE-->>User: 403 Access Denied\n        end\n\n    else Active session exists\n        StudentBE->>DB: Load student profile\n        StudentBE-->>StudentFE: Return student dashboard\n    end\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
3. Detailed Trainer Login Flow<\/h1>\n\n\n\n
Trainer login is almost the same, but the app checks for the trainer role<\/strong> instead of the student role.<\/p>\n\n\n\n
sequenceDiagram\n    autonumber\n\n    participant User as User \/ Browser\n    participant TrainerFE as Trainer Frontend\n    participant TrainerBE as Trainer Backend\n    participant KC as Keycloak\n    participant DB as Application DB\n\n    User->>TrainerFE: Open trainer portal\n    TrainerFE->>TrainerBE: GET \/trainer\/dashboard\n\n    TrainerBE->>TrainerBE: Check local app session\n\n    alt No active session\n        TrainerBE-->>User: Redirect to Keycloak authorization endpoint\n\n        User->>KC: Authorization request\n        Note over User,KC: client_id=trainer-app, redirect_uri, scope, state, nonce, code_challenge\n\n        alt User already logged in to Keycloak from Student portal\n            KC->>KC: Detect existing Keycloak SSO session\n            KC-->>User: Redirect back with authorization_code\n        else User not logged in\n            KC-->>User: Show login page\n            User->>KC: Submit credentials\n            KC->>KC: Validate credentials\n            KC->>KC: Create SSO session\n            KC-->>User: Redirect back with authorization_code\n        end\n\n        User->>TrainerBE: GET \/auth\/callback?code=AUTH_CODE&state=STATE\n\n        TrainerBE->>KC: Exchange authorization code for tokens\n        KC-->>TrainerBE: ID Token, Access Token, Refresh Token\n\n        TrainerBE->>TrainerBE: Validate token\n        TrainerBE->>TrainerBE: Extract token.sub and roles\n\n        TrainerBE->>DB: Find users.keycloak_user_id = token.sub\n        TrainerBE->>DB: Check user_service_roles contains trainer\n        TrainerBE->>DB: Check trainer_profiles.approval_status\n\n        alt User has trainer access and approved\n            TrainerBE->>TrainerBE: Create trainer app session\n            TrainerBE-->>User: Set secure HttpOnly session cookie\n            TrainerBE-->>User: Redirect to trainer dashboard\n        else User is not trainer\n            TrainerBE-->>User: 403 Not allowed for trainer portal\n        else Trainer pending approval\n            TrainerBE-->>User: Show pending approval page\n        end\n\n    else Active trainer session exists\n        TrainerBE->>DB: Load trainer profile\n        TrainerBE-->>TrainerFE: Return trainer dashboard\n    end\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
4. What Data Passes Between Each System?<\/h1>\n\n\n\n
From<\/th>To<\/th>Data Passed<\/th>Purpose<\/th><\/tr><\/thead>
Browser<\/td>Student\/Trainer App<\/td>Request URL, cookies<\/td>User opens dashboard<\/td><\/tr>
App<\/td>Browser<\/td>Redirect to Keycloak<\/td>Start login<\/td><\/tr>
Browser<\/td>Keycloak<\/td>client_id<\/code>, redirect_uri<\/code>, scope<\/code>, state<\/code>, nonce<\/code>, code_challenge<\/code><\/td>Start OIDC login<\/td><\/tr>
User<\/td>Keycloak<\/td>Username\/password\/OTP<\/td>Authentication<\/td><\/tr>
Keycloak<\/td>Browser<\/td>Authorization code<\/td>Temporary login code<\/td><\/tr>
Browser<\/td>App callback URL<\/td>code<\/code>, state<\/code><\/td>Return login result<\/td><\/tr>
App Backend<\/td>Keycloak<\/td>code<\/code>, client_id<\/code>, code_verifier<\/code><\/td>Exchange code for tokens<\/td><\/tr>
Keycloak<\/td>App Backend<\/td>id_token<\/code>, access_token<\/code>, refresh_token<\/code><\/td>Authenticated user data<\/td><\/tr>
App Backend<\/td>App DB<\/td>keycloak_user_id<\/code>, email, roles<\/td>Map Keycloak user to local app user<\/td><\/tr>
App Backend<\/td>Browser<\/td>Secure session cookie<\/td>Maintain app login session<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
5. Token Exchange Flow<\/h1>\n\n\n\n
flowchart LR\n    A[Browser] -->|1. Open Student App| B[Student Backend]\n\n    B -->|2. Redirect to \/authorize| C[Keycloak]\n\n    C -->|3. Login Page| A\n\n    A -->|4. Username Password OTP| C\n\n    C -->|5. Redirect with auth code| B\n\n    B -->|6. Exchange code using token endpoint| C\n\n    C -->|7. Return tokens| B\n\n    B -->|8. Validate token and create app session| D[Application DB]\n\n    B -->|9. Set HttpOnly cookie| A\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
6. Example Authorization Request to Keycloak<\/h1>\n\n\n\n
When user opens Student portal:<\/p>\n\n\n\n
GET https:\/\/auth.example.com\/realms\/myrealm\/protocol\/openid-connect\/auth\n    ?client_id=student-app\n    &redirect_uri=https:\/\/student.example.com\/auth\/callback\n    &response_type=code\n    &scope=openid profile email\n    &state=random_csrf_value\n    &nonce=random_nonce_value\n    &code_challenge=generated_pkce_challenge\n    &code_challenge_method=S256\n<\/code><\/pre>\n\n\n\n
For Trainer portal:<\/p>\n\n\n\n
GET https:\/\/auth.example.com\/realms\/myrealm\/protocol\/openid-connect\/auth\n    ?client_id=trainer-app\n    &redirect_uri=https:\/\/trainer.example.com\/auth\/callback\n    &response_type=code\n    &scope=openid profile email\n    &state=random_csrf_value\n    &nonce=random_nonce_value\n    &code_challenge=generated_pkce_challenge\n    &code_challenge_method=S256\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
7. Example Token Response from Keycloak<\/h1>\n\n\n\n
{\n  \"access_token\": \"eyJhbGciOi...\",\n  \"expires_in\": 300,\n  \"refresh_expires_in\": 1800,\n  \"refresh_token\": \"eyJhbGciOi...\",\n  \"token_type\": \"Bearer\",\n  \"id_token\": \"eyJhbGciOi...\",\n  \"scope\": \"openid profile email\"\n}\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
8. Important Claims Inside Token<\/h1>\n\n\n\n
Your backend mainly needs these fields:<\/p>\n\n\n\n
{\n  \"sub\": \"8d5f9b18-16c2-49f3-90e2-123456789abc\",\n  \"email\": \"user@example.com\",\n  \"email_verified\": true,\n  \"preferred_username\": \"rajesh\",\n  \"given_name\": \"Rajesh\",\n  \"family_name\": \"Kumar\",\n  \"realm_access\": {\n    \"roles\": [\n      \"student\",\n      \"trainer\"\n    ]\n  }\n}\n<\/code><\/pre>\n\n\n\n
Application DB mapping:<\/p>\n\n\n\n
token.sub  \u2192 users.keycloak_user_id\nemail      \u2192 users.email\nroles      \u2192 user_service_roles\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
9. Student to Trainer SSO Flow<\/h1>\n\n\n\n
If the user already logged in as Student and then opens Trainer portal, they should not enter password again.<\/p>\n\n\n\n
sequenceDiagram\n    autonumber\n\n    participant User as Browser\n    participant Student as Student App\n    participant Trainer as Trainer App\n    participant KC as Keycloak\n    participant DB as App DB\n\n    User->>Student: Login to student portal\n    Student->>KC: Redirect user to Keycloak\n    KC->>User: Login page\n    User->>KC: Submit credentials\n    KC->>KC: Create Keycloak SSO session\n    KC-->>Student: Return authorization code\n    Student->>KC: Exchange code for tokens\n    KC-->>Student: Tokens\n    Student->>DB: Check student role\n    Student-->>User: Student dashboard\n\n    User->>Trainer: Open trainer portal\n    Trainer->>KC: Redirect to Keycloak authorize endpoint\n\n    KC->>KC: Existing SSO session found\n    KC-->>Trainer: Return authorization code without asking password\n\n    Trainer->>KC: Exchange code for tokens\n    KC-->>Trainer: Tokens\n\n    Trainer->>DB: Check trainer role\/profile\n\n    alt User has trainer role\n        Trainer-->>User: Trainer dashboard\n    else User does not have trainer role\n        Trainer-->>User: Access denied or apply as trainer\n    end\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
10. Access Control Decision Flow<\/h1>\n\n\n\n
flowchart TD\n    A[User logged in via Keycloak] --> B[App receives token]\n    B --> C[Extract keycloak_user_id from token.sub]\n    C --> D[Find user in local DB]\n\n    D --> E{User exists?}\n    E -->|No| F[Create local user profile]\n    E -->|Yes| G[Load existing user]\n\n    F --> H[Check token roles]\n    G --> H[Check token roles]\n\n    H --> I{Requested service?}\n\n    I -->|Student Portal| J{Has student role?}\n    I -->|Trainer Portal| K{Has trainer role?}\n    I -->|Consultant Portal| L{Has consultant role?}\n\n    J -->|Yes| M[Allow student dashboard]\n    J -->|No| N[Deny or show registration]\n\n    K -->|Yes| O[Check trainer profile approval]\n    K -->|No| P[Deny or apply as trainer]\n\n    O -->|Approved| Q[Allow trainer dashboard]\n    O -->|Pending| R[Show pending approval]\n\n    L -->|Yes| S[Check consultant approval]\n    L -->|No| T[Deny or apply as consultant]\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
11. Refresh Token Flow<\/h1>\n\n\n\n
When access token expires, the app should refresh it.<\/p>\n\n\n\n
sequenceDiagram\n    autonumber\n\n    participant Browser as Browser\n    participant App as Student\/Trainer Backend\n    participant KC as Keycloak\n    participant DB as App DB\n\n    Browser->>App: Request dashboard\/API\n    App->>App: Check app session and token expiry\n\n    alt Access token still valid\n        App->>DB: Load required data\n        App-->>Browser: Return response\n    else Access token expired\n        App->>KC: POST \/token with grant_type=refresh_token\n        KC-->>App: New access token and refresh token\n\n        alt Refresh success\n            App->>App: Update session token data\n            App->>DB: Load required data\n            App-->>Browser: Return response\n        else Refresh failed\n            App-->>Browser: Redirect to Keycloak login\n        end\n    end\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
12. Logout Flow<\/h1>\n\n\n\n
sequenceDiagram\n    autonumber\n\n    participant User as Browser\n    participant App as Student\/Trainer App\n    participant KC as Keycloak\n\n    User->>App: Click logout\n\n    App->>App: Destroy local app session\n    App-->>User: Clear app session cookie\n\n    App-->>User: Redirect to Keycloak logout endpoint\n\n    User->>KC: GET \/logout?id_token_hint=ID_TOKEN&post_logout_redirect_uri=APP_URL\n\n    KC->>KC: Destroy Keycloak SSO session\n    KC-->>User: Redirect back to public homepage\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
13. Recommended Keycloak Clients<\/h1>\n\n\n\n
Service<\/th>Keycloak Client ID<\/th>Type<\/th>Redirect URI<\/th><\/tr><\/thead>
Student Portal<\/td>student-app<\/code><\/td>Confidential or Public with PKCE<\/td>https:\/\/student.example.com\/auth\/callback<\/code><\/td><\/tr>
Trainer Portal<\/td>trainer-app<\/code><\/td>Confidential or Public with PKCE<\/td>https:\/\/trainer.example.com\/auth\/callback<\/code><\/td><\/tr>
Consultant Portal<\/td>consultant-app<\/code><\/td>Confidential or Public with PKCE<\/td>https:\/\/consultant.example.com\/auth\/callback<\/code><\/td><\/tr>
Admin Portal<\/td>admin-app<\/code><\/td>Confidential<\/td>https:\/\/admin.example.com\/auth\/callback<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
For backend-based apps, use confidential clients<\/strong>.<\/p>\n\n\n\n
For pure SPA\/mobile apps, use public clients with PKCE<\/strong>.<\/p>\n\n\n\n
\n\n\n\n
14. Recommended Local Tables Used During Login<\/h1>\n\n\n\n
users\nuser_service_roles\nstudent_profiles\ntrainer_profiles\nconsultant_profiles\naudit_logs\n<\/code><\/pre>\n\n\n\n
Main lookup:<\/p>\n\n\n\n
SELECT *\nFROM users\nWHERE keycloak_user_id = '<token.sub>';\n<\/code><\/pre>\n\n\n\n
Check student role:<\/p>\n\n\n\n
SELECT *\nFROM user_service_roles\nWHERE user_id = 10\nAND service_type = 'student'\nAND status = 'active';\n<\/code><\/pre>\n\n\n\n
Check trainer role:<\/p>\n\n\n\n
SELECT *\nFROM user_service_roles\nWHERE user_id = 10\nAND service_type = 'trainer'\nAND status = 'active';\n<\/code><\/pre>\n\n\n\n
Check trainer approval:<\/p>\n\n\n\n
SELECT *\nFROM trainer_profiles\nWHERE user_id = 10\nAND approval_status = 'approved'\nAND status = 'active';\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
15. Best Final Design<\/h1>\n\n\n\n
flowchart TD\n    Browser[Browser] --> StudentApp[Student App]\n    Browser --> TrainerApp[Trainer App]\n    Browser --> ConsultantApp[Consultant App]\n\n    StudentApp --> Keycloak[Keycloak]\n    TrainerApp --> Keycloak\n    ConsultantApp --> Keycloak\n\n    Keycloak --> StudentApp\n    Keycloak --> TrainerApp\n    Keycloak --> ConsultantApp\n\n    StudentApp --> AppDB[(Application Database)]\n    TrainerApp --> AppDB\n    ConsultantApp --> AppDB\n\n    AppDB --> Users[users table]\n    AppDB --> Roles[user_service_roles table]\n    AppDB --> StudentProfile[student_profiles table]\n    AppDB --> TrainerProfile[trainer_profiles table]\n    AppDB --> ConsultantProfile[consultant_profiles table]\n<\/code><\/pre>\n\n\n\n
The golden rule:<\/p>\n\n\n\n
Keycloak decides: Who are you?\nApplication DB decides: What can you do inside Student\/Trainer\/Consultant service?\n<\/code><\/pre>\n\n\n\n
Below is a low-level database schema<\/strong> for your platform with 3 services<\/strong>:<\/p>\n\n\n\n
    \n
  1. Student Service<\/strong><\/li>\n\n\n\n
  2. Trainer Service<\/strong><\/li>\n\n\n\n
  3. Consultant Service<\/strong><\/li>\n\n\n\n
  4. Authentication handled by Keycloak<\/strong><\/li>\n<\/ol>\n\n\n\n
    Important: Do not store passwords in your application database.<\/strong> Keycloak will manage login, password, MFA, social login, sessions, roles, etc. Your app should only store the Keycloak user reference.<\/p>\n\n\n\n
    \n\n\n\n
    Recommended Architecture<\/h1>\n\n\n\n
    Keycloak\n  |\n  | JWT Token\n  |\nApplication Backend\n  |\n  | keycloak_user_id \/ sub\n  |\nApplication Database\n  |\n  |-- common user profile\n  |-- student profile\n  |-- trainer profile\n  |-- consultant profile\n  |-- courses\n  |-- batches\n  |-- enrollments\n  |-- bookings\n  |-- consulting requests\n  |-- payments\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    1. Common User Tables<\/h1>\n\n\n\n
    Table: users<\/code><\/h2>\n\n\n\n
    This table maps your application user with Keycloak user.<\/p>\n\n\n\n
    CREATE TABLE users (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    keycloak_user_id VARCHAR(100) NOT NULL UNIQUE,\n    email VARCHAR(255) NOT NULL UNIQUE,\n    username VARCHAR(150) NULL,\n\n    first_name VARCHAR(100) NULL,\n    last_name VARCHAR(100) NULL,\n    phone VARCHAR(30) NULL,\n    profile_image VARCHAR(500) NULL,\n\n    status ENUM('active', 'inactive', 'blocked', 'deleted') DEFAULT 'active',\n\n    email_verified BOOLEAN DEFAULT FALSE,\n    phone_verified BOOLEAN DEFAULT FALSE,\n\n    last_login_at DATETIME NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    INDEX idx_users_keycloak_user_id (keycloak_user_id),\n    INDEX idx_users_email (email),\n    INDEX idx_users_status (status)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: user_service_roles<\/code><\/h2>\n\n\n\n
    One user can be a student, trainer, consultant, or all three.<\/p>\n\n\n\n
    CREATE TABLE user_service_roles (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL,\n    service_type ENUM('student', 'trainer', 'consultant') NOT NULL,\n\n    status ENUM('active', 'inactive', 'pending', 'rejected') DEFAULT 'active',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    UNIQUE KEY uq_user_service (user_id, service_type),\n\n    CONSTRAINT fk_user_service_roles_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    Example:<\/p>\n\n\n\n
    User Rajesh can have:\n- student\n- trainer\n- consultant\n<\/code><\/pre>\n\n\n\n
    So instead of creating separate login tables, keep one user identity and attach multiple service roles.<\/p>\n\n\n\n
    \n\n\n\n
    2. Student Service Tables<\/h1>\n\n\n\n
    Table: student_profiles<\/code><\/h2>\n\n\n\n
    CREATE TABLE student_profiles (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL UNIQUE,\n\n    student_code VARCHAR(50) UNIQUE NULL,\n    education_level VARCHAR(100) NULL,\n    current_company VARCHAR(150) NULL,\n    job_title VARCHAR(150) NULL,\n\n    country VARCHAR(100) NULL,\n    city VARCHAR(100) NULL,\n    timezone VARCHAR(100) NULL,\n\n    learning_goal TEXT NULL,\n    experience_level ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'beginner',\n\n    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_student_profiles_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: courses<\/code><\/h2>\n\n\n\n
    CREATE TABLE courses (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    title VARCHAR(255) NOT NULL,\n    slug VARCHAR(255) NOT NULL UNIQUE,\n\n    short_description VARCHAR(500) NULL,\n    description LONGTEXT NULL,\n\n    category VARCHAR(150) NULL,\n    level ENUM('foundation', 'essential', 'intermediate', 'advanced') DEFAULT 'foundation',\n\n    duration_hours INT DEFAULT 0,\n    language VARCHAR(100) DEFAULT 'English',\n\n    price DECIMAL(10,2) DEFAULT 0.00,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    INDEX idx_courses_slug (slug),\n    INDEX idx_courses_status (status),\n    INDEX idx_courses_category (category)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: course_modules<\/code><\/h2>\n\n\n\n
    CREATE TABLE course_modules (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    course_id BIGINT NOT NULL,\n\n    title VARCHAR(255) NOT NULL,\n    description TEXT NULL,\n    sort_order INT DEFAULT 0,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_course_modules_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: lessons<\/code><\/h2>\n\n\n\n
    CREATE TABLE lessons (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    course_id BIGINT NOT NULL,\n    module_id BIGINT NULL,\n\n    title VARCHAR(255) NOT NULL,\n    content LONGTEXT NULL,\n\n    lesson_type ENUM('video', 'text', 'quiz', 'assignment', 'live_session') DEFAULT 'text',\n\n    video_url VARCHAR(500) NULL,\n    duration_minutes INT DEFAULT 0,\n\n    sort_order INT DEFAULT 0,\n    is_free_preview BOOLEAN DEFAULT FALSE,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_lessons_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_lessons_module\n        FOREIGN KEY (module_id) REFERENCES course_modules(id)\n        ON DELETE SET NULL\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: student_enrollments<\/code><\/h2>\n\n\n\n
    CREATE TABLE student_enrollments (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    student_id BIGINT NOT NULL,\n    course_id BIGINT NOT NULL,\n\n    enrollment_status ENUM('enrolled', 'in_progress', 'completed', 'cancelled') DEFAULT 'enrolled',\n\n    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    completed_at DATETIME NULL,\n\n    progress_percentage DECIMAL(5,2) DEFAULT 0.00,\n\n    certificate_issued BOOLEAN DEFAULT FALSE,\n    certificate_url VARCHAR(500) NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    UNIQUE KEY uq_student_course (student_id, course_id),\n\n    CONSTRAINT fk_student_enrollments_student\n        FOREIGN KEY (student_id) REFERENCES student_profiles(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_student_enrollments_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: student_lesson_progress<\/code><\/h2>\n\n\n\n
    CREATE TABLE student_lesson_progress (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    student_id BIGINT NOT NULL,\n    course_id BIGINT NOT NULL,\n    lesson_id BIGINT NOT NULL,\n\n    status ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started',\n\n    started_at DATETIME NULL,\n    completed_at DATETIME NULL,\n\n    watched_seconds INT DEFAULT 0,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    UNIQUE KEY uq_student_lesson (student_id, lesson_id),\n\n    CONSTRAINT fk_progress_student\n        FOREIGN KEY (student_id) REFERENCES student_profiles(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_progress_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_progress_lesson\n        FOREIGN KEY (lesson_id) REFERENCES lessons(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    3. Trainer Service Tables<\/h1>\n\n\n\n
    Table: trainer_profiles<\/code><\/h2>\n\n\n\n
    CREATE TABLE trainer_profiles (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL UNIQUE,\n\n    trainer_code VARCHAR(50) UNIQUE NULL,\n\n    headline VARCHAR(255) NULL,\n    bio TEXT NULL,\n\n    total_experience_years DECIMAL(4,1) DEFAULT 0.0,\n    primary_skill VARCHAR(150) NULL,\n\n    linkedin_url VARCHAR(500) NULL,\n    website_url VARCHAR(500) NULL,\n\n    hourly_rate DECIMAL(10,2) DEFAULT 0.00,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',\n    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_trainer_profiles_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: trainer_skills<\/code><\/h2>\n\n\n\n
    CREATE TABLE trainer_skills (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    trainer_id BIGINT NOT NULL,\n\n    skill_name VARCHAR(150) NOT NULL,\n    experience_years DECIMAL(4,1) DEFAULT 0.0,\n\n    skill_level ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_trainer_skills_trainer\n        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)\n        ON DELETE CASCADE,\n\n    INDEX idx_trainer_skills_skill_name (skill_name)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: trainer_courses<\/code><\/h2>\n\n\n\n
    This connects trainers with courses.<\/p>\n\n\n\n
    CREATE TABLE trainer_courses (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    trainer_id BIGINT NOT NULL,\n    course_id BIGINT NOT NULL,\n\n    role ENUM('primary', 'assistant', 'guest') DEFAULT 'primary',\n\n    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    UNIQUE KEY uq_trainer_course (trainer_id, course_id),\n\n    CONSTRAINT fk_trainer_courses_trainer\n        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_trainer_courses_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: training_batches<\/code><\/h2>\n\n\n\n
    CREATE TABLE training_batches (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    course_id BIGINT NOT NULL,\n    trainer_id BIGINT NOT NULL,\n\n    batch_name VARCHAR(255) NOT NULL,\n\n    start_date DATE NOT NULL,\n    end_date DATE NULL,\n\n    start_time TIME NULL,\n    end_time TIME NULL,\n\n    timezone VARCHAR(100) DEFAULT 'UTC',\n\n    mode ENUM('online', 'offline', 'hybrid') DEFAULT 'online',\n\n    meeting_url VARCHAR(500) NULL,\n    location VARCHAR(500) NULL,\n\n    max_students INT DEFAULT 0,\n\n    status ENUM('scheduled', 'ongoing', 'completed', 'cancelled') DEFAULT 'scheduled',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_training_batches_course\n        FOREIGN KEY (course_id) REFERENCES courses(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_training_batches_trainer\n        FOREIGN KEY (trainer_id) REFERENCES trainer_profiles(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: batch_students<\/code><\/h2>\n\n\n\n
    CREATE TABLE batch_students (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    batch_id BIGINT NOT NULL,\n    student_id BIGINT NOT NULL,\n\n    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    status ENUM('active', 'completed', 'dropped') DEFAULT 'active',\n\n    UNIQUE KEY uq_batch_student (batch_id, student_id),\n\n    CONSTRAINT fk_batch_students_batch\n        FOREIGN KEY (batch_id) REFERENCES training_batches(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_batch_students_student\n        FOREIGN KEY (student_id) REFERENCES student_profiles(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: batch_sessions<\/code><\/h2>\n\n\n\n
    CREATE TABLE batch_sessions (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    batch_id BIGINT NOT NULL,\n\n    session_title VARCHAR(255) NOT NULL,\n    session_description TEXT NULL,\n\n    session_date DATE NOT NULL,\n    start_time TIME NOT NULL,\n    end_time TIME NOT NULL,\n\n    meeting_url VARCHAR(500) NULL,\n\n    status ENUM('scheduled', 'completed', 'cancelled') DEFAULT 'scheduled',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_batch_sessions_batch\n        FOREIGN KEY (batch_id) REFERENCES training_batches(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: student_attendance<\/code><\/h2>\n\n\n\n
    CREATE TABLE student_attendance (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    session_id BIGINT NOT NULL,\n    student_id BIGINT NOT NULL,\n\n    attendance_status ENUM('present', 'absent', 'late') DEFAULT 'absent',\n\n    joined_at DATETIME NULL,\n    left_at DATETIME NULL,\n\n    remarks TEXT NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    UNIQUE KEY uq_session_student_attendance (session_id, student_id),\n\n    CONSTRAINT fk_attendance_session\n        FOREIGN KEY (session_id) REFERENCES batch_sessions(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_attendance_student\n        FOREIGN KEY (student_id) REFERENCES student_profiles(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    4. Consultant Service Tables<\/h1>\n\n\n\n
    Table: consultant_profiles<\/code><\/h2>\n\n\n\n
    CREATE TABLE consultant_profiles (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL UNIQUE,\n\n    consultant_code VARCHAR(50) UNIQUE NULL,\n\n    headline VARCHAR(255) NULL,\n    bio TEXT NULL,\n\n    total_experience_years DECIMAL(4,1) DEFAULT 0.0,\n\n    primary_domain VARCHAR(150) NULL,\n\n    hourly_rate DECIMAL(10,2) DEFAULT 0.00,\n    daily_rate DECIMAL(10,2) DEFAULT 0.00,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    linkedin_url VARCHAR(500) NULL,\n    portfolio_url VARCHAR(500) NULL,\n\n    approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',\n    status ENUM('active', 'inactive', 'blocked') DEFAULT 'active',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_consultant_profiles_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: consultant_skills<\/code><\/h2>\n\n\n\n
    CREATE TABLE consultant_skills (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    consultant_id BIGINT NOT NULL,\n\n    skill_name VARCHAR(150) NOT NULL,\n    experience_years DECIMAL(4,1) DEFAULT 0.0,\n\n    skill_level ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'expert',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_consultant_skills_consultant\n        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)\n        ON DELETE CASCADE,\n\n    INDEX idx_consultant_skills_skill_name (skill_name)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: consulting_services<\/code><\/h2>\n\n\n\n
    These are services offered by consultants.<\/p>\n\n\n\n
    CREATE TABLE consulting_services (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    consultant_id BIGINT NOT NULL,\n\n    title VARCHAR(255) NOT NULL,\n    slug VARCHAR(255) NOT NULL UNIQUE,\n\n    description LONGTEXT NULL,\n\n    service_category VARCHAR(150) NULL,\n\n    pricing_type ENUM('hourly', 'fixed', 'daily', 'custom') DEFAULT 'hourly',\n\n    price DECIMAL(10,2) DEFAULT 0.00,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    estimated_duration_hours INT DEFAULT 0,\n\n    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_consulting_services_consultant\n        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: consulting_requests<\/code><\/h2>\n\n\n\n
    This is when a student\/client requests consulting.<\/p>\n\n\n\n
    CREATE TABLE consulting_requests (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    requested_by_user_id BIGINT NOT NULL,\n    consultant_id BIGINT NULL,\n    consulting_service_id BIGINT NULL,\n\n    title VARCHAR(255) NOT NULL,\n    description LONGTEXT NULL,\n\n    budget DECIMAL(10,2) DEFAULT 0.00,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',\n\n    request_status ENUM(\n        'open',\n        'assigned',\n        'accepted',\n        'in_progress',\n        'completed',\n        'cancelled',\n        'rejected'\n    ) DEFAULT 'open',\n\n    expected_start_date DATE NULL,\n    expected_end_date DATE NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_consulting_requests_user\n        FOREIGN KEY (requested_by_user_id) REFERENCES users(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_consulting_requests_consultant\n        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)\n        ON DELETE SET NULL,\n\n    CONSTRAINT fk_consulting_requests_service\n        FOREIGN KEY (consulting_service_id) REFERENCES consulting_services(id)\n        ON DELETE SET NULL\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: consulting_bookings<\/code><\/h2>\n\n\n\n
    CREATE TABLE consulting_bookings (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    consulting_request_id BIGINT NOT NULL,\n    consultant_id BIGINT NOT NULL,\n    booked_by_user_id BIGINT NOT NULL,\n\n    booking_date DATE NOT NULL,\n    start_time TIME NOT NULL,\n    end_time TIME NOT NULL,\n\n    timezone VARCHAR(100) DEFAULT 'UTC',\n\n    meeting_url VARCHAR(500) NULL,\n\n    booking_status ENUM(\n        'pending',\n        'confirmed',\n        'completed',\n        'cancelled',\n        'rescheduled'\n    ) DEFAULT 'pending',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_consulting_bookings_request\n        FOREIGN KEY (consulting_request_id) REFERENCES consulting_requests(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_consulting_bookings_consultant\n        FOREIGN KEY (consultant_id) REFERENCES consultant_profiles(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_consulting_bookings_user\n        FOREIGN KEY (booked_by_user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    5. Payment Tables<\/h1>\n\n\n\n
    Table: payments<\/code><\/h2>\n\n\n\n
    Use this for course enrollment, trainer booking, consultant booking, etc.<\/p>\n\n\n\n
    CREATE TABLE payments (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL,\n\n    payment_for ENUM('course', 'batch', 'consulting', 'subscription') NOT NULL,\n\n    reference_id BIGINT NOT NULL,\n\n    amount DECIMAL(10,2) NOT NULL,\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    payment_gateway ENUM('paypal', 'stripe', 'razorpay', 'paytm', 'bank_transfer') NULL,\n\n    gateway_transaction_id VARCHAR(255) NULL,\n\n    payment_status ENUM(\n        'pending',\n        'paid',\n        'failed',\n        'refunded',\n        'cancelled'\n    ) DEFAULT 'pending',\n\n    paid_at DATETIME NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_payments_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE,\n\n    INDEX idx_payments_reference (payment_for, reference_id),\n    INDEX idx_payments_status (payment_status)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Table: invoices<\/code><\/h2>\n\n\n\n
    CREATE TABLE invoices (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    payment_id BIGINT NOT NULL,\n    user_id BIGINT NOT NULL,\n\n    invoice_number VARCHAR(100) NOT NULL UNIQUE,\n\n    invoice_date DATE NOT NULL,\n\n    subtotal DECIMAL(10,2) NOT NULL,\n    tax_amount DECIMAL(10,2) DEFAULT 0.00,\n    total_amount DECIMAL(10,2) NOT NULL,\n\n    currency VARCHAR(10) DEFAULT 'USD',\n\n    invoice_url VARCHAR(500) NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_invoices_payment\n        FOREIGN KEY (payment_id) REFERENCES payments(id)\n        ON DELETE CASCADE,\n\n    CONSTRAINT fk_invoices_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    6. Review and Rating Tables<\/h1>\n\n\n\n
    Table: reviews<\/code><\/h2>\n\n\n\n
    CREATE TABLE reviews (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    reviewer_user_id BIGINT NOT NULL,\n\n    review_for ENUM('course', 'trainer', 'consultant', 'consulting_service') NOT NULL,\n    reference_id BIGINT NOT NULL,\n\n    rating INT NOT NULL,\n    review_text TEXT NULL,\n\n    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_reviews_user\n        FOREIGN KEY (reviewer_user_id) REFERENCES users(id)\n        ON DELETE CASCADE,\n\n    INDEX idx_reviews_reference (review_for, reference_id),\n    INDEX idx_reviews_rating (rating)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    7. Documents and Verification<\/h1>\n\n\n\n
    Useful for trainer and consultant approval.<\/p>\n\n\n\n
    Table: user_documents<\/code><\/h2>\n\n\n\n
    CREATE TABLE user_documents (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL,\n\n    document_type ENUM(\n        'resume',\n        'certificate',\n        'id_proof',\n        'address_proof',\n        'experience_letter',\n        'other'\n    ) NOT NULL,\n\n    document_name VARCHAR(255) NULL,\n    document_url VARCHAR(500) NOT NULL,\n\n    verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',\n\n    remarks TEXT NULL,\n\n    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n    verified_at DATETIME NULL,\n\n    CONSTRAINT fk_user_documents_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    8. Notification Tables<\/h1>\n\n\n\n
    Table: notifications<\/code><\/h2>\n\n\n\n
    CREATE TABLE notifications (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NOT NULL,\n\n    title VARCHAR(255) NOT NULL,\n    message TEXT NOT NULL,\n\n    notification_type ENUM(\n        'course',\n        'batch',\n        'payment',\n        'consulting',\n        'system'\n    ) DEFAULT 'system',\n\n    is_read BOOLEAN DEFAULT FALSE,\n\n    read_at DATETIME NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_notifications_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE CASCADE,\n\n    INDEX idx_notifications_user_read (user_id, is_read)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    9. Audit Log Table<\/h1>\n\n\n\n
    Very useful for admin and security.<\/p>\n\n\n\n
    CREATE TABLE audit_logs (\n    id BIGINT AUTO_INCREMENT PRIMARY KEY,\n\n    user_id BIGINT NULL,\n\n    action VARCHAR(255) NOT NULL,\n    module VARCHAR(100) NOT NULL,\n\n    ip_address VARCHAR(100) NULL,\n    user_agent TEXT NULL,\n\n    old_value JSON NULL,\n    new_value JSON NULL,\n\n    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n\n    CONSTRAINT fk_audit_logs_user\n        FOREIGN KEY (user_id) REFERENCES users(id)\n        ON DELETE SET NULL,\n\n    INDEX idx_audit_logs_user (user_id),\n    INDEX idx_audit_logs_module (module)\n);\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Keycloak Role Mapping<\/h1>\n\n\n\n
    In Keycloak, create these roles:<\/p>\n\n\n\n
    student\ntrainer\nconsultant\nadmin\nsuper_admin\n<\/code><\/pre>\n\n\n\n
    Recommended Keycloak groups:<\/p>\n\n\n\n
    \/students\n\/trainers\n\/consultants\n\/admins\n<\/code><\/pre>\n\n\n\n
    Your JWT token may contain:<\/p>\n\n\n\n
    {\n  \"sub\": \"keycloak-user-id\",\n  \"email\": \"user@example.com\",\n  \"preferred_username\": \"rajesh\",\n  \"realm_access\": {\n    \"roles\": [\"student\", \"trainer\"]\n  }\n}\n<\/code><\/pre>\n\n\n\n
    Your application will use:<\/p>\n\n\n\n
    JWT sub = users.keycloak_user_id\n<\/code><\/pre>\n\n\n\n
    \n\n\n\n
    Final Table List<\/h1>\n\n\n\n
    Module<\/th>Tables<\/th><\/tr><\/thead>
    Common<\/td>users<\/code>, user_service_roles<\/code><\/td><\/tr>
    Student<\/td>student_profiles<\/code>, student_enrollments<\/code>, student_lesson_progress<\/code><\/td><\/tr>
    Trainer<\/td>trainer_profiles<\/code>, trainer_skills<\/code>, trainer_courses<\/code>, training_batches<\/code>, batch_students<\/code>, batch_sessions<\/code>, student_attendance<\/code><\/td><\/tr>
    Consultant<\/td>consultant_profiles<\/code>, consultant_skills<\/code>, consulting_services<\/code>, consulting_requests<\/code>, consulting_bookings<\/code><\/td><\/tr>
    Course<\/td>courses<\/code>, course_modules<\/code>, lessons<\/code><\/td><\/tr>
    Payment<\/td>payments<\/code>, invoices<\/code><\/td><\/tr>
    Review<\/td>reviews<\/code><\/td><\/tr>
    Document<\/td>user_documents<\/code><\/td><\/tr>
    Notification<\/td>notifications<\/code><\/td><\/tr>
    Security<\/td>audit_logs<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \n\n\n\n
    Best Practice<\/h1>\n\n\n\n
    Use this structure:<\/p>\n\n\n\n
    Keycloak = Authentication + Authorization\nApplication DB = Business Data\n<\/code><\/pre>\n\n\n\n
    Do not create separate login tables for student, trainer, and consultant. Keep one central users<\/code> table and then create separate profile tables for each service. This keeps the system clean, scalable, and easy to maintain.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Yes \u2014 the clean low-level flow should use Keycloak as the central Identity Provider, and Student \/ Trainer \/ Consultant services should never handle passwords directly. Recommended… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1003","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/comments?post=1003"}],"version-history":[{"count":1,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1003\/revisions"}],"predecessor-version":[{"id":1005,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/posts\/1003\/revisions\/1005"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/media?parent=1003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/categories?post=1003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/tutorials\/wp-json\/wp\/v2\/tags?post=1003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}