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

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 Technical 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)

        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
    }

Figure 8. Core domain entity-relationship diagram.

User activity, notifications, and audit

        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
    }

Figure 9. User-centric supporting tables (notifications, engagement, audit, governance).

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).

        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
    }

Figure 10. Role-based access control model.

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.

        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

Figure 11. Opportunity specialisation and key typed attributes.

Enumerated types map

The schema is heavily typed by PostgreSQL enums. The map below groups the key enums 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

Figure 12. Key enumerated types and their values.

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.

        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 --> [*]

Figure 13. Lifecycle of a recommendation record.

Beneficiary 360 view

All data connected to a single beneficiary across the schema.

        graph TB
    B["BENEFICIARY"]
    U["USERS<br/>account, auth, consents"]
    SP["SKILL_PROFILES<br/>skills, experience"]
    REC["RECOMMENDATIONS<br/>matches + scores"]
    APP["APPLICATIONS<br/>external applications"]
    FAV["FAVORITES<br/>saved opportunities"]
    LOC["LOCATIONS<br/>current location"]
    NOT["NOTIFICATIONS<br/>alerts"]
    CON["CONSENT_RECORDS<br/>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

Figure 14. Beneficiary-centric view of related records.

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.