==========
Data Model
==========
The platform's relational data is defined in PostgreSQL
(``OIM Postgres Data Model.sql``). This page summarizes the enumerated types,
the main tables, and the JSON structures used by the AI engine.
Enumerated Types
================
.. list-table::
:header-rows: 1
:widths: 30 70
* - Enum
- Values
* - ``user_type_enum``
- ``BENEFICIARY``, ``EXPERT``, ``GOVERNMENT_PARTNER``, ``NGO_PARTNER``,
``PRIVATE_SECTOR``, ``ADMINISTRATOR``
* - ``user_status_enum``
- ``ACTIVE``, ``INACTIVE``, ``SUSPENDED``, ``PENDING_VERIFICATION``
* - ``beneficiary_category_enum``
- ``RETURNING_MIGRANT``, ``IDP``, ``FORMER_ARMED_GROUP_MEMBER``,
``AFFECTED_COMMUNITY``, ``CROSS_BORDER_TRADER``, ``DIASPORA_MEMBER``
* - ``education_level_enum``
- ``NO_FORMAL_EDUCATION``, ``PRIMARY``, ``SECONDARY``, ``VOCATIONAL``,
``UNDERGRADUATE``, ``POSTGRADUATE``
* - ``opportunity_type_enum``
- ``PERMANENT_EMPLOYMENT``, ``TEMPORARY_EMPLOYMENT``, ``INTERNSHIP``,
``APPRENTICESHIP``, ``ENTREPRENEURSHIP``, ``VOCATIONAL_TRAINING``,
``PROFESSIONAL_CERTIFICATION``, ``BUSINESS_GRANT``, ``MICRO_CREDIT``
* - ``permission_action_enum``
- ``CREATE``, ``READ``, ``UPDATE``, ``DELETE``, ``PUBLISH``, ``APPROVE``,
``REJECT``, ``REVIEW``, ``EXPORT``, ``IMPORT``, ``MANAGE``
Core Tables
===========
The schema is organized into functional groups.
Identity and access
--------------------
``users``, ``roles``, ``permissions``, ``role_permissions``, ``user_roles``,
``user_permissions``.
Beneficiaries and opportunities
-------------------------------
``beneficiaries``, ``skill_profiles``, ``economic_opportunities``,
``training_programs``, ``organizations``, ``locations``.
Recommendations and applications
--------------------------------
``recommendations``, ``applications``, ``data_sources``, ``favorites``,
``saved_searches``, ``search_queries``.
Notifications
-------------
``notifications``, ``notification_preferences``.
Analytics and audit
-------------------
``analytics_events``, ``dashboard_metrics``, ``activity_logs``, ``audit_logs``,
``login_history``, ``session_logs``, ``security_events``.
Governance and AI
-----------------
``consent_records``, ``system_configurations``, ``ml_model_versions``.
Entity Relationships
====================
The class diagram in :doc:`architecture` (Figure 3) shows the object-level
relationships. The entity-relationship diagrams below are derived directly from
the PostgreSQL schema (``OIM Postgres Data Model.sql``) and its foreign keys.
Use the on-diagram zoom controls (or scroll) to explore them.
Core domain (beneficiaries, opportunities, recommendations)
-----------------------------------------------------------
.. mermaid::
:caption: Figure 8. Core domain entity-relationship diagram.
erDiagram
USERS ||--o| BENEFICIARIES : "is a"
USERS ||--o{ ORGANIZATIONS : "represents"
USERS ||--o{ USER_ROLES : "assigned"
ROLES ||--o{ USER_ROLES : "granted to"
ROLES ||--o{ ROLE_PERMISSIONS : "has"
PERMISSIONS ||--o{ ROLE_PERMISSIONS : "in"
USERS ||--o{ USER_PERMISSIONS : "granted"
PERMISSIONS ||--o{ USER_PERMISSIONS : "in"
BENEFICIARIES ||--|| SKILL_PROFILES : "has"
BENEFICIARIES }o--|| LOCATIONS : "located at"
ORGANIZATIONS }o--|| LOCATIONS : "based at"
DATA_SOURCES ||--o{ ECONOMIC_OPPORTUNITIES : "feeds"
ORGANIZATIONS ||--o{ ECONOMIC_OPPORTUNITIES : "publishes"
LOCATIONS ||--o{ ECONOMIC_OPPORTUNITIES : "located at"
ORGANIZATIONS ||--o{ TRAINING_PROGRAMS : "provides"
LOCATIONS ||--o{ TRAINING_PROGRAMS : "located at"
BENEFICIARIES ||--o{ RECOMMENDATIONS : "receives"
ECONOMIC_OPPORTUNITIES ||--o{ RECOMMENDATIONS : "matched in"
BENEFICIARIES ||--o{ APPLICATIONS : "submits"
ECONOMIC_OPPORTUNITIES ||--o{ APPLICATIONS : "targeted by"
USERS ||--o{ APPLICATIONS : "reviews"
USERS {
uuid user_id PK
string email
enum status
string preferred_language
int profile_completion_pct
}
BENEFICIARIES {
uuid beneficiary_id PK
uuid user_id FK
enum category
enum education_level
uuid current_location_id FK
}
SKILL_PROFILES {
uuid skill_profile_id PK
uuid beneficiary_id FK
json skills
json work_experience
}
ECONOMIC_OPPORTUNITIES {
uuid opportunity_id PK
enum type
uuid organization_id FK
uuid location_id FK
uuid source_id FK
string external_url
}
TRAINING_PROGRAMS {
uuid training_id PK
uuid provider_id FK
uuid location_id FK
}
RECOMMENDATIONS {
uuid recommendation_id PK
uuid beneficiary_id FK
uuid opportunity_id FK
decimal match_score
bool applied
}
APPLICATIONS {
uuid application_id PK
uuid beneficiary_id FK
uuid opportunity_id FK
enum status
uuid reviewed_by FK
}
ORGANIZATIONS {
uuid org_id PK
enum type
uuid location_id FK
bool verified
}
DATA_SOURCES {
uuid source_id PK
string name
string type
}
LOCATIONS {
uuid location_id PK
string region
decimal latitude
decimal longitude
}
User activity, notifications, and audit
---------------------------------------
.. mermaid::
:caption: Figure 9. User-centric supporting tables (notifications, engagement, audit, governance).
erDiagram
USERS ||--o{ NOTIFICATIONS : "receives"
USERS ||--o| NOTIFICATION_PREFERENCES : "configures"
USERS ||--o{ FAVORITES : "saves"
ECONOMIC_OPPORTUNITIES ||--o{ FAVORITES : "favorited in"
USERS ||--o{ SEARCH_QUERIES : "runs"
USERS ||--o{ SAVED_SEARCHES : "stores"
USERS ||--o{ CONSENT_RECORDS : "grants"
USERS ||--o{ ANALYTICS_EVENTS : "generates"
USERS ||--o{ ACTIVITY_LOGS : "generates"
USERS ||--o{ AUDIT_LOGS : "generates"
USERS ||--o{ LOGIN_HISTORY : "has"
USERS ||--o{ SESSION_LOGS : "has"
USERS ||--o{ SECURITY_EVENTS : "triggers"
NOTIFICATIONS {
uuid notification_id PK
uuid user_id FK
enum type
enum channel
bool read
}
NOTIFICATION_PREFERENCES {
uuid preference_id PK
uuid user_id FK
decimal min_match_score
bool enabled
}
FAVORITES {
uuid favorite_id PK
uuid user_id FK
uuid opportunity_id FK
}
CONSENT_RECORDS {
uuid consent_id PK
uuid user_id FK
enum consent_type
bool granted
}
SECURITY_EVENTS {
uuid event_id PK
uuid user_id FK
uuid resolved_by FK
string severity
}
AUDIT_LOGS {
uuid audit_id PK
uuid user_id FK
string action
json changes
}
Role-based access control (RBAC)
--------------------------------
Access control is modelled with roles, permissions, and two join tables, plus
an optional direct user-to-permission grant. Each permission targets a
``resource`` and an ``action`` (from ``permission_action_enum``).
.. mermaid::
:caption: Figure 10. Role-based access control model.
erDiagram
USERS ||--o{ USER_ROLES : "assigned"
ROLES ||--o{ USER_ROLES : "granted to"
ROLES ||--o{ ROLE_PERMISSIONS : "groups"
PERMISSIONS ||--o{ ROLE_PERMISSIONS : "included in"
USERS ||--o{ USER_PERMISSIONS : "direct grant"
PERMISSIONS ||--o{ USER_PERMISSIONS : "granted as"
USERS ||--o{ USER_ROLES : "assigned_by"
ROLES {
uuid role_id PK
string name
string display_name
bool is_system_role
}
PERMISSIONS {
uuid permission_id PK
string name
string resource
enum action
}
USER_ROLES {
uuid user_id FK
uuid role_id FK
uuid assigned_by FK
}
ROLE_PERMISSIONS {
uuid role_id FK
uuid permission_id FK
}
USER_PERMISSIONS {
uuid user_id FK
uuid permission_id FK
uuid granted_by FK
}
Opportunity type hierarchy
--------------------------
A single ``economic_opportunities`` table stores all opportunity types, typed by
``opportunity_type_enum``. Conceptually each type specialises the base record
with its own attributes; ``training_programs`` is a separate table for training
providers.
.. mermaid::
:caption: Figure 11. Opportunity specialisation and key typed attributes.
classDiagram
class EconomicOpportunity {
+UUID opportunity_id
+OpportunityType type
+Sector sector
+String external_url
+UUID source_id
+UUID organization_id
+UUID location_id
+OpportunityStatus status
}
class PermanentEmployment {
+Decimal salary_min
+Decimal salary_max
+ContractType contract_type
+Integer required_experience_years
}
class TemporaryEmployment {
+Decimal salary_min
+String duration
+ContractType contract_type
}
class Internship {
+String duration
+EducationLevel required_education
}
class VocationalTraining {
+String duration
+EducationLevel required_education
}
class Entrepreneurship {
+Decimal investment_required
+Boolean is_for_vulnerable
}
EconomicOpportunity <|-- PermanentEmployment
EconomicOpportunity <|-- TemporaryEmployment
EconomicOpportunity <|-- Internship
EconomicOpportunity <|-- VocationalTraining
EconomicOpportunity <|-- Entrepreneurship
Enumerated types map
--------------------
The schema is heavily typed by PostgreSQL enums. The map below groups the key
enums and their values.
.. mermaid::
:caption: Figure 12. Key enumerated types and their values.
graph LR
UT["user_type_enum"] --> UT1["BENEFICIARY"] & UT2["EXPERT"] & UT3["GOVERNMENT_PARTNER"] & UT4["NGO_PARTNER"] & UT5["PRIVATE_SECTOR"] & UT6["ADMINISTRATOR"]
BC["beneficiary_category_enum"] --> BC1["RETURNING_MIGRANT"] & BC2["IDP"] & BC3["FORMER_ARMED_GROUP_MEMBER"] & BC4["AFFECTED_COMMUNITY"] & BC5["CROSS_BORDER_TRADER"] & BC6["DIASPORA_MEMBER"]
OT["opportunity_type_enum"] --> OT1["PERMANENT_EMPLOYMENT"] & OT2["TEMPORARY_EMPLOYMENT"] & OT3["INTERNSHIP"] & OT4["APPRENTICESHIP"] & OT5["ENTREPRENEURSHIP"] & OT6["VOCATIONAL_TRAINING"] & OT7["BUSINESS_GRANT"] & OT8["MICRO_CREDIT"]
AS["application_status_enum"] --> AS1["DRAFT"] & AS2["SUBMITTED"] & AS3["UNDER_REVIEW"] & AS4["ACCEPTED"] & AS5["REJECTED"] & AS6["WITHDRAWN"]
classDef enum fill:#FFF3E0,stroke:#F57C00,stroke-width:2px,color:#000,font-weight:bold
classDef val fill:#E3F2FD,stroke:#1976D2,color:#000
class UT,BC,OT,AS enum
class UT1,UT2,UT3,UT4,UT5,UT6,BC1,BC2,BC3,BC4,BC5,BC6,OT1,OT2,OT3,OT4,OT5,OT6,OT7,OT8,AS1,AS2,AS3,AS4,AS5,AS6 val
Recommendation record lifecycle
-------------------------------
Each row in ``recommendations`` tracks the beneficiary's engagement with a
matched opportunity, from generation through feedback. These transitions update
the ``viewed_at``, ``clicked``, ``applied``, and ``feedback_rating`` columns.
.. mermaid::
:caption: Figure 13. Lifecycle of a recommendation record.
stateDiagram-v2
[*] --> GENERATED : AI engine creates record
GENERATED --> VIEWED : Beneficiary opens it (viewed_at set)
VIEWED --> CLICKED : Opens external link (clicked = true)
VIEWED --> DISMISSED : Ignored / low relevance
CLICKED --> APPLIED : Confirms application (applied = true)
CLICKED --> ABANDONED : Did not apply
APPLIED --> FEEDBACK : feedback_rating recorded
FEEDBACK --> [*] : Feeds model learning
DISMISSED --> [*]
ABANDONED --> [*]
Beneficiary 360 view
--------------------
All data connected to a single beneficiary across the schema.
.. mermaid::
:caption: Figure 14. Beneficiary-centric view of related records.
graph TB
B["BENEFICIARY"]
U["USERS
account, auth, consents"]
SP["SKILL_PROFILES
skills, experience"]
REC["RECOMMENDATIONS
matches + scores"]
APP["APPLICATIONS
external applications"]
FAV["FAVORITES
saved opportunities"]
LOC["LOCATIONS
current location"]
NOT["NOTIFICATIONS
alerts"]
CON["CONSENT_RECORDS
GDPR consents"]
U --- B
B --- SP
B --- REC
B --- APP
B --- FAV
B --- LOC
U --- NOT
U --- CON
classDef center fill:#F57C00,stroke:#E65100,stroke-width:3px,color:#fff,font-weight:bold
classDef rel fill:#E8F5E9,stroke:#2E7D32,color:#000
class B center
class U,SP,REC,APP,FAV,LOC,NOT,CON rel
AI Engine JSON Structures
=========================
In the recommendation engine, profiles and recommendations are also exchanged
and stored as JSON / JSONB:
* **Beneficiary profile** — common fields (``id``, ``category``, ``user``,
``skillProfile``, ``educationLevel``, ``mobility``, ``availableCapital``) plus
category-specific fields.
* **Opportunity** — ``opportunityType``, ``title``, ``description``, and a
``metadata`` object (``Amount``, ``source``, ``ApplyUrl``, ``location``,
``sector``).
* **Recommendation** — ``opportunityType``, ``title``, ``description``,
``relevance_score`` (0.0–1.0), ``reason``, and ``metadata``.
External Data Sources
=====================
Opportunity content is collected from external sources (including Cameroonian
job sites, partner feeds, and training catalogues) and normalized through an ETL
pipeline before indexing. The data-source inventory and collection strategy are
maintained in the project's data-definition documentation and tracked in the
``data_sources`` table.