Courses

Views for the LMS course catalog, content modules, enrollments, approvals, classroom trainings, and on-the-job training sessions.

These views cover the course management side of the Maecos Learning Management System. They let you analyze the course catalog, track enrollments and completion, monitor approvals, and report on classroom and on-the-job training sessions.

---

reporting.lms_courses

The course catalog. Each row is a course definition.

Field	Type	Description
course_id	uuid	Unique identifier of the course
course_kind	text	Kind of course (e.g., standard, refresher)
language	text	Language of the course
title	text	Course title
price	numeric	Course price
duration	integer	Duration in minutes
description	text	Course description
objective	text	Learning objective
skill_id	uuid	Skill this course qualifies for
published_in_catalogue	boolean	Whether the course is visible in the catalog
is_refresher	boolean	Whether this is a refresher course
approver	uuid	User who approves enrollments (if required)
is_active	boolean	Whether the course is active
deadline_in_months	integer	Default completion deadline in months
content_in_order	boolean	Whether content must be completed in sequence
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

Joins: Join with lms_course_content on course_id for content modules. Join with lms_course_enrollments on course_id for enrollment data. Join with lms_skills on skill_id.

---

reporting.lms_course_content

Content modules within a course (e.g., online module, classroom session, on-the-job task).

Field	Type	Description
course_content_id	uuid	Unique identifier of the content module
course_id	uuid	Parent course
course_type	text	Type of content (e.g., online, classroom, on-the-job)
title	text	Content module title
duration	integer	Duration in minutes
teamleaders_as_trainers	boolean	Whether team leaders can serve as trainers
scoring	text	Scoring method
trainers	uuid[]	Array of trainer user IDs
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_course_enrollments

Course-level enrollments — one row per user per course.

Field	Type	Description
course_enrollment_id	uuid	Unique enrollment identifier
course_id	uuid	The course
user_id	uuid	The enrolled user
status	text	Enrollment status
enrolled_at	timestamp	When the user was enrolled
completed_at	timestamp	When the user completed the course
deadline	timestamp	Completion deadline
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

Joins: Join with lms_courses on course_id. Join with users on user_id. Join with lms_course_content_enrollments on course_enrollment_id for per-module progress.

---

reporting.lms_course_content_enrollments

Per-module enrollment progress. Each row tracks one user's progress on one content module.

Field	Type	Description
course_content_enrollment_id	uuid	Unique identifier
course_enrollment_id	uuid	Parent course enrollment
course_id	uuid	The course
course_content_id	uuid	The content module
user_id	uuid	The enrolled user
status	text	Module completion status
enrolled_at	timestamp	When the user started this module
completed_at	timestamp	When the user completed this module
score	numeric	Score achieved (if applicable)
is_cancelled	boolean	Whether the enrollment was cancelled
trainer_id	uuid	Trainer for on-the-job modules
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_course_approvals

Tracks enrollment approval requests and their outcomes.

Field	Type	Description
course_enrollment_id	uuid	The enrollment awaiting approval
course_id	uuid	The course
user_id	uuid	The user requesting enrollment
is_approved	boolean	Whether the enrollment was approved
approved_by	uuid	User who approved or denied
updated_at	timestamp	Last modification timestamp

---

reporting.lms_classroom_trainings

Scheduled classroom training sessions.

Field	Type	Description
classroom_training_id	uuid	Unique identifier
course_content_id	uuid	The content module this session belongs to
start_date	timestamp	Scheduled start
end_date	timestamp	Scheduled end
location	text	Training location
is_deleted	boolean	Whether the session was deleted
updated_at	timestamp	Last modification timestamp
trainer_id	uuid	Assigned trainer

---

reporting.lms_classroom_training_enrollments

User enrollments in specific classroom training sessions.

Field	Type	Description
course_content_id	uuid	The content module
classroom_training_id	uuid	The classroom session
enrolled_user_id	uuid	The enrolled user
status	text	Enrollment status
is_deleted	boolean	Whether the enrollment was deleted
updated_at	timestamp	Last modification timestamp

---

reporting.lms_on_the_job_trainings

On-the-job training assignments.

Field	Type	Description
course_content_id	uuid	The content module
planned_date	date	Scheduled date for the training
enrolled_user_id	uuid	The trainee
trainer_id	uuid	The assigned trainer
is_deleted	boolean	Whether the record was deleted
greatest	timestamp	Last modification timestamp

---

reporting.lms_employee_info

Additional employee information relevant to the LMS.

Field	Type	Description
user_id	uuid	The user
info	text	Additional employee information
updated_at	timestamp	Last modification timestamp

---

Example queries

Course completion rates

SELECT
    c.title AS course,
    COUNT(e.course_enrollment_id) AS enrolled,
    COUNT(e.completed_at) AS completed,
    ROUND(100.0 * COUNT(e.completed_at) / NULLIF(COUNT(e.course_enrollment_id), 0), 1) AS completion_pct
FROM reporting.lms_courses c
LEFT JOIN reporting.lms_course_enrollments e USING (course_id)
WHERE c.is_active = true
    AND e.deleted_at IS NULL
GROUP BY c.title
ORDER BY completion_pct ASC;

Overdue enrollments

SELECT
    u.first_name || ' ' || u.last_name AS employee,
    c.title AS course,
    e.deadline,
    e.status
FROM reporting.lms_course_enrollments e
JOIN reporting.lms_courses c USING (course_id)
JOIN reporting.users u USING (user_id)
WHERE e.completed_at IS NULL
    AND e.deleted_at IS NULL
    AND e.deadline < NOW()
ORDER BY e.deadline ASC;