Skills, Profiles and Organization

Views for skills, qualifications, positions, profiles, teams, learning paths, skill blocks, and the skill matrix.

These views cover the competency and organizational structure side of the Maecos LMS. They enable skill gap analysis, skill matrix reporting, qualification tracking, and organizational mapping through positions, profiles, and teams.

---

reporting.lms_skills

The skill catalog. Each skill represents a competency that can be acquired through courses or other means.

Field	Type	Description
skill_id	uuid	Unique identifier of the skill
skill_category_id	uuid	Category this skill belongs to
title	text	Skill title
expiration_in_months	integer	How many months a qualification for this skill remains valid
prerequisite_skill_ids	uuid[]	Skills that must be acquired before this one
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp
external_id	text	External reference ID

Joins: Join with lms_skill_categories on skill_category_id. Join with lms_qualifications on skill_id to see who holds this skill. Join with lms_courses on skill_id to see which courses teach this skill.

---

reporting.lms_skill_categories

Categories for organizing skills.

Field	Type	Description
skill_category_id	uuid	Unique identifier
title	text	Category title
color	text	Display color
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_skill_blocks

Groups of skills that can be assigned together to profiles.

Field	Type	Description
skill_block_id	uuid	Unique identifier
title	text	Skill block title
skill_ids	uuid[]	Array of skill IDs in this block
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_profiles

Competency profiles — a collection of required and optional skills, skill blocks, and learning paths.

Field	Type	Description
profile_id	uuid	Unique identifier
title	text	Profile title
skill_ids	uuid[]	Required skill IDs
optional_skill_ids	uuid[]	Optional skill IDs
skill_block_ids	uuid[]	Skill block IDs
learning_path_ids	uuid[]	Associated learning path IDs
profile_group_ids	uuid[]	Profile groups this profile belongs to
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp
external_id	text	External reference ID

---

reporting.lms_profile_groups

Groups of profiles for organizational structuring.

Field	Type	Description
profile_group_id	uuid	Unique identifier
name	text	Group name
profile_ids	uuid[]	Profiles in this group
skill_ids	uuid[]	Direct skill associations
skill_block_ids	uuid[]	Skill block associations
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_positions

Job positions that users can be assigned to.

Field	Type	Description
position_id	uuid	Unique identifier
title	text	Position title
profile_ids	uuid[]	Competency profiles required for this position
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_positions_teams

Maps positions to teams, including whether the position is a leadership role.

Field	Type	Description
position_id	uuid	The position
team_id	uuid	The team
is_leader	boolean	Whether this is a team leader position
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_position_enrollments

User assignments to positions over time.

Field	Type	Description
position_id	uuid	The position
user_id	uuid	The assigned user
start_date	date	When the assignment starts
end_date	date	When the assignment ends (null if current)
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_teams

Organizational teams.

Field	Type	Description
team_id	uuid	Unique identifier
name	text	Team name
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_learning_paths

Learning paths — ordered sequences of skills or courses for structured development.

Field	Type	Description
learning_path_id	uuid	Unique identifier
name	text	Learning path name
description	text	Description
maximum_duration	integer	Maximum duration in months
skill_ids	uuid[]	Skills included in this path
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_learning_paths_enrollments

User enrollments in learning paths.

Field	Type	Description
learning_path_enrollment_id	uuid	Unique identifier
learning_path_id	uuid	The learning path
user_id	uuid	The enrolled user
enrolled_on	date	Enrollment date
deadline	date	Completion deadline
status	text	Enrollment status
finished_on	date	Completion date
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.lms_qualifications

Acquired qualifications — the record of a user having obtained a skill.

Always filter on deleted_at IS NULL and check expires_at when determining current compliance. A qualification with a past expires_at is no longer valid.

Field	Type	Description
qualification_id	uuid	Unique identifier
user_id	uuid	The qualified user
skill_id	uuid	The skill acquired
course_enrollment_id	text	Course enrollment that led to this qualification (if any)
acquired_at	timestamp	When the qualification was acquired
expires_at	timestamp	When the qualification expires
remarks	text	Additional notes
duration_in_minutes	integer	Training duration
price	numeric	Training cost
is_internal	boolean	Whether the training was internal
is_exemption	boolean	Whether this is an exemption (qualification without training)
updated_at	timestamp	Last modification timestamp
deleted_at	timestamp	Soft delete timestamp

---

reporting.skill_matrix_need

The computed skill requirements per user. Each row represents one skill that one user needs, along with the source of the requirement (position, profile, learning path, etc.).

Field	Type	Description
user_id	uuid	The user
skill_id	uuid	The required skill
optional	boolean	Whether this is an optional skill requirement
training_need_id	uuid	Training need identifier
position_id	uuid	Position creating this requirement
profile_group_id	uuid	Profile group creating this requirement
profile_id	uuid	Profile creating this requirement
skill_block_id	uuid	Skill block creating this requirement
learning_path_id	uuid	Learning path creating this requirement
prerequisite_for_skill_id	uuid	Skill for which this is a prerequisite
required_from	date	When the requirement starts
required_until	date	When the requirement ends

This view is the foundation for skill gap analysis. Compare it with lms_qualifications to find gaps: users who need a skill but don't have a valid qualification.

---

Example queries

Skill gap analysis — users missing required skills

SELECT
    u.first_name || ' ' || u.last_name AS employee,
    s.title AS skill,
    smn.required_from
FROM reporting.skill_matrix_need smn
JOIN reporting.users u USING (user_id)
JOIN reporting.lms_skills s USING (skill_id)
LEFT JOIN reporting.lms_qualifications q
    ON smn.user_id = q.user_id
    AND smn.skill_id = q.skill_id
    AND q.deleted_at IS NULL
    AND (q.expires_at IS NULL OR q.expires_at > NOW())
WHERE smn.optional = false
    AND q.qualification_id IS NULL
ORDER BY u.last_name, s.title;

Expiring qualifications in the next 90 days

SELECT
    u.first_name || ' ' || u.last_name AS employee,
    s.title AS skill,
    q.expires_at
FROM reporting.lms_qualifications q
JOIN reporting.users u USING (user_id)
JOIN reporting.lms_skills s USING (skill_id)
WHERE q.deleted_at IS NULL
    AND q.expires_at BETWEEN NOW() AND NOW() + INTERVAL '90 days'
ORDER BY q.expires_at ASC;