LMS Training Integration (Phase 6)

Overview

The LMS Training Integration module provides automated, risk-based training assignment and comprehensive training management. It integrates with the Predictive Safety Intelligence system to automatically assign training courses when risk scores exceed thresholds.

Features

• Risk-Based Auto-Assignment: Automatically assigns training courses when predictive risk scores exceed course thresholds
• Training Course Management: Create and manage training courses with categories, difficulty levels, and risk thresholds
• Assignment Tracking: Track manual, auto, and risk-based assignments
• Completion Tracking: Monitor course completions with scores, certificates, and expiry dates
• Training Analytics: Comprehensive analytics dashboard for admins
• Worker Training Center: User-friendly interface for workers to view and complete courses

Database Schema

training_courses Table

CREATE TABLE public.training_courses (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  title text NOT NULL,
  description text,
  category text NOT NULL CHECK (category IN (
    'safety_fundamentals', 'hazard_awareness', 'equipment_safety', 
    'emergency_response', 'compliance', 'risk_management', 'other'
  )),
  content_url text,
  duration_minutes integer,
  difficulty_level text CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced')),
  risk_threshold numeric, -- Minimum predictive risk score to trigger auto-assignment (0-100)
  entity_type text CHECK (entity_type IN ('zone', 'driver', 'worker', 'equipment', 'general')),
  auto_assign_enabled boolean NOT NULL DEFAULT false,
  required boolean NOT NULL DEFAULT false,
  expiry_months integer,
  created_by uuid NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

training_assignments Table

CREATE TABLE public.training_assignments (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  course_id uuid NOT NULL,
  user_id uuid NOT NULL,
  assigned_by uuid,
  assignment_type text NOT NULL DEFAULT 'manual' CHECK (assignment_type IN ('manual', 'auto', 'risk_based')),
  assignment_reason text,
  due_date date,
  status text NOT NULL DEFAULT 'assigned' CHECK (status IN ('assigned', 'in_progress', 'completed', 'overdue', 'cancelled')),
  assigned_at timestamp with time zone DEFAULT now(),
  started_at timestamp with time zone,
  completed_at timestamp with time zone,
  cancelled_at timestamp with time zone,
  cancelled_by uuid,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

training_completions Table

CREATE TABLE public.training_completions (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  assignment_id uuid NOT NULL,
  course_id uuid NOT NULL,
  user_id uuid NOT NULL,
  score numeric,
  passed boolean NOT NULL DEFAULT true,
  completion_time_minutes integer,
  certificate_url text,
  completed_at timestamp with time zone DEFAULT now(),
  expires_at date,
  created_at timestamp with time zone DEFAULT now()
);

Edge Function: auto-assign-training

Automatically assigns training courses based on predictive risk scores.

Endpoint

POST /functions/v1/auto-assign-training

Request Body

{
  "entity_type": "worker",  // Optional: 'zone', 'driver', 'worker', 'equipment'
  "entity_id": "uuid",       // Optional: specific entity ID
  "min_risk_score": 50       // Optional: minimum risk score threshold (default: 0)
}

Response

{
  "success": true,
  "tenant_id": "uuid",
  "assignments_created": 5,
  "courses_checked": 12,
  "scores_checked": 8,
  "assignments": [
    {
      "tenant_id": "uuid",
      "course_id": "uuid",
      "user_id": "uuid",
      "assignment_type": "risk_based",
      "assignment_reason": "High risk score (75) for worker uuid",
      "status": "assigned"
    }
  ]
}

Usage

import { autoAssignTraining } from "@/lib/api-edge";

// Auto-assign for all high-risk entities
const result = await autoAssignTraining();

// Auto-assign for specific entity type
const result = await autoAssignTraining('worker');

// Auto-assign with minimum risk score
const result = await autoAssignTraining(undefined, undefined, 60);

Algorithm

1. Fetches all courses with auto_assign_enabled = true and risk_threshold set
2. Fetches predictive scores that exceed the minimum threshold
3. For each score:
   • Finds matching courses (risk_threshold <= score.score and entity_type matches)
   • Maps entity IDs to user IDs:
     • worker/driver: entity_id is the user profile ID
     • zone: finds workers assigned to the zone via shift_assignments
     • equipment: skipped (not implemented)
4. Creates assignments for each user (skips if already assigned or completed recently)
5. Returns summary of created assignments

React Query Hooks

useTrainingCourses(filters?)

Fetch all training courses:

const { data: courses } = useTrainingCourses({
  category: 'safety_fundamentals',
  autoAssignEnabled: true
});

useUserTrainingAssignments(userId, status?)

Fetch user's training assignments:

const { data: assignments } = useUserTrainingAssignments(userId, 'assigned');

useTrainingAnalytics()

Fetch training analytics summary:

const { data: analytics } = useTrainingAnalytics();
// Returns: { total_courses, total_assignments, completed_count, in_progress_count, overdue_count, completion_rate, avg_completion_time }

useCreateTrainingCourse()

Create a new training course:

const createCourse = useCreateTrainingCourse();

await createCourse.mutateAsync({
  title: 'Forklift Safety Training',
  category: 'equipment_safety',
  risk_threshold: 60,
  entity_type: 'worker',
  auto_assign_enabled: true,
  // ... other fields
});

useAssignTrainingCourse()

Manually assign a course to a user:

const assignCourse = useAssignTrainingCourse();

await assignCourse.mutateAsync({
  course_id: courseId,
  user_id: userId,
  due_date: '2025-03-01',
  assignment_reason: 'Required for new equipment operation'
});

useStartTrainingAssignment()

Start a training assignment:

const startAssignment = useStartTrainingAssignment();

await startAssignment.mutateAsync(assignmentId);

useCompleteTrainingAssignment()

Complete a training assignment:

const completeAssignment = useCompleteTrainingAssignment();

await completeAssignment.mutateAsync({
  assignment_id: assignmentId,
  score: 85,
  passed: true,
  completion_time_minutes: 45
});

UI Components

Training Center (Workers)

Location: /admin → Safety → Training Center

Features:

• View assigned, in-progress, and completed courses
• Start courses
• Complete courses with score and time tracking
• View assignment reasons (especially for risk-based assignments)
• Track completion rate and statistics

Training Analytics (Admins)

Location: /admin → Safety → Training Analytics

Features:

• Analytics dashboard with key metrics
• Course management (create, view, edit)
• Assignment management (manual assignment, view all assignments)
• Auto-assign training button (triggers Edge Function)
• Status breakdown (completed, in-progress, overdue)

Course Categories

• Safety Fundamentals: Basic safety principles and practices
• Hazard Awareness: Identifying and responding to hazards
• Equipment Safety: Safe operation of equipment
• Emergency Response: Emergency procedures and protocols
• Compliance: Regulatory compliance training
• Risk Management: Risk assessment and mitigation
• Other: Miscellaneous training topics

Assignment Types

• Manual: Assigned by admin or safety officer
• Auto: System-assigned (not yet implemented)
• Risk-Based: Automatically assigned based on predictive risk scores

Workflow

1. Course Creation: Admin creates course with risk threshold and entity type
2. Auto-Assignment: Edge Function runs (manually triggered or scheduled) and assigns courses based on risk scores
3. Worker Notification: Worker sees assignment in Training Center
4. Course Start: Worker starts the course
5. Course Completion: Worker completes course with score/time
6. Certificate: Certificate generated (if configured)
7. Expiry Tracking: Certificate expiry tracked (if expiry_months set)

RLS Policies

Training Courses

• Admins: Full access
• Safety Officers: Full access within tenant
• Workers: Can view courses

Training Assignments

• Admins: Full access
• Safety Officers: Full access within tenant
• Workers: Can view and update their own assignments

Training Completions

• Admins: Full access
• Safety Officers: Full access within tenant
• Workers: Can view and create their own completions

Helper Functions

get_user_training_assignments(tenant_id, user_id, status?)

Returns training assignments for a user:

SELECT * FROM get_user_training_assignments('tenant-uuid', 'user-uuid', 'assigned');

get_training_analytics(tenant_id)

Returns training analytics summary:

SELECT * FROM get_training_analytics('tenant-uuid');

Integration with Predictive Safety

The LMS Training Integration automatically integrates with the Predictive Safety Intelligence system:

1. Risk Score Monitoring: Monitors predictive_scores table for high-risk entities
2. Threshold Matching: Matches risk scores to course risk thresholds
3. Automatic Assignment: Creates assignments when thresholds are exceeded
4. Assignment Reason: Records the reason (e.g., "High risk score (75) for worker uuid")

Best Practices

1. Set Appropriate Thresholds: Configure risk thresholds based on your risk tolerance
2. Regular Auto-Assignment: Run auto-assign function regularly (daily/weekly)
3. Monitor Completion Rates: Track completion rates and follow up on overdue assignments
4. Update Courses: Keep course content up-to-date with latest safety standards
5. Certificate Management: Set expiry months for courses that require recertification

Future Enhancements

• Scheduled auto-assignment (cron job)
• Email notifications for new assignments
• Course content integration (embedded videos, interactive modules)
• Quiz/test functionality with automatic scoring
• Certificate PDF generation
• Training history and recertification reminders
• Integration with external LMS systems