Technical Design Document: Skills & Competency Engine

1. Introduction & Goals

1.1. Objective

This document outlines the technical design for the Evalium Skills & Competency Engine. The goal is to build a flexible, audit-proof engine that can infer "competence" from immutable submission snapshots, support retroactive mapping and recalculation, and provide UI-ready read models. This feature will be implemented as a view layer over the existing Evaluations and Programmes features, not as a separate HR module.

1.2. Architectural Alignment

The implementation will strictly adhere to the following architectural principles as defined in the project's Architecture Decision Records (ADRs):

• ADR-0008: Modular, Event-Driven Service Architecture: The engine will be implemented as a new, single-responsibility service that communicates with other services via a durable, event-driven pattern.
• ADR-0009: SMB-First Unified UX & Safe Immutability: The engine will support safe, backend-driven impact checks and abstract versioning complexity from the user.
• ADR-0010: The Ledger Pattern for Mutable State: The engine will use the ledger pattern for all high-stakes state changes, such as publishing new mapping set versions and running backfills.
• ADR-0011: Compliance Centre Hybrid Scrub + Compliance Ledger: The engine will integrate with the existing compliance workflows to ensure that skill and competency data is handled correctly during "Forget User" and DSAR export operations.

---

2. High-Level Architecture

2.1. Service Diagram

[ To be added: A component diagram showing the new SkillsService, its database tables (including `skill_work_queue`), and its event-driven interactions with ResultsService, RemediationService, and PrivacyService. ]

2.2. Event Flow (Durable Outbox / Work Queue)

The Skills engine will rely on a durable, outbox-based eventing pattern to ensure no events are missed and to decouple processing from request threads, aligning with NFRs.

1. Trigger: An event occurs in a core service (e.g., SubmissionFinalized, RemediationApplied, UserForgotten, MappingSetPublished).
2. Outbox Write: Within the same transaction, the core service writes a work item to a dedicated skill_work_queue table. This guarantees that the work item is created if and only if the original transaction commits.
3. Worker Poll: A background worker (internal/workers/skills_projection_worker.go) polls the skill_work_queue table for pending jobs (recommended: FOR UPDATE SKIP LOCKED).
4. Process & Project: The worker processes the job, computing evidence facts and rollups. The work item’s idempotency key ensures that re-processing the same event (e.g., due to a worker restart) is safe and does not create duplicate data.
5. Update Status: The worker marks the job as completed or failed (with retry/backoff policy).

This pattern ensures high reliability and respects the "no long-running task blocks request threads" NFR.

2.3. Effective Mapping Resolution (Priority / Cascading Overrides)

The engine supports a cascading override model so different scopes can define “what counts” without breaking tenant-wide consistency.

Scopes (most specific wins):

1. evaluation_version
2. programme (applies only in programme context; see §6.2)
3. org_unit (nearest ancestor binding wins; sub-org overrides org)
4. tenant (default)

Within the same scope:

• highest priority wins,
• tie-breaker: deterministic rule (e.g., newest published mapping set version, then newest binding row).

Every computed fact records which binding/scope was applied for audit and explainability.

---

3. Detailed Data Model

3.1. SQL Schema

-- Skills (Tenant-scoped taxonomy)
CREATE TABLE skills (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    name text NOT NULL,
    description text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skills_tenant_name_unique UNIQUE (tenant_id, name)
);

CREATE TABLE skill_versions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_id uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    version integer NOT NULL,
    name text NOT NULL,
    description text,
    level_scheme jsonb,
    status text NOT NULL DEFAULT 'draft', -- draft | published | archived
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skill_versions_skill_version_unique UNIQUE (skill_id, version)
);

-- Competency Tags (Tenant-scoped taxonomy)
CREATE TABLE competency_tag_keys (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT competency_tag_keys_tenant_name_unique UNIQUE (tenant_id, name)
);

CREATE TABLE competency_tag_values (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    key_id uuid NOT NULL REFERENCES competency_tag_keys(id) ON DELETE CASCADE,
    value text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT competency_tag_values_key_value_unique UNIQUE (key_id, value)
);

-- Mapping Sets (Org-scoped definitions for linking tenant skills to org evidence rules)
CREATE TABLE skill_mapping_sets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    org_unit_id uuid NOT NULL,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skill_mapping_sets_tenant_org_name_unique UNIQUE (tenant_id, org_unit_id, name)
);

CREATE TABLE skill_mapping_set_versions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    mapping_set_id uuid NOT NULL REFERENCES skill_mapping_sets(id) ON DELETE CASCADE,
    version integer NOT NULL,
    status text NOT NULL DEFAULT 'draft', -- draft | published | archived
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skill_mapping_set_versions_mapping_set_version_unique UNIQUE (mapping_set_id, version)
);

CREATE TABLE skill_mapping_rules (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    mapping_set_version_id uuid NOT NULL REFERENCES skill_mapping_set_versions(id) ON DELETE CASCADE,
    rule_type text NOT NULL, -- tag_predicate | evaluation | evaluation_version | question_version | framework
    selector_json jsonb NOT NULL,
    target_skill_id uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    contribution_strategy text NOT NULL, -- score_threshold | average | best_of_n | pass_fail
    precedence integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Mapping Bindings (Priority / cascading override selection)
CREATE TABLE skill_mapping_bindings (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,

    -- scope_type determines override precedence (more specific wins)
    scope_type text NOT NULL, -- tenant | org_unit | programme | evaluation | evaluation_version
    scope_id uuid,            -- NULL for tenant default

    -- Optional org boundary for bindings that should only apply within a specific org tree.
    -- For scope_type='org_unit', org_unit_id == scope_id.
    org_unit_id uuid,

    mapping_set_id uuid NOT NULL REFERENCES skill_mapping_sets(id) ON DELETE RESTRICT,
    priority integer NOT NULL DEFAULT 0,
    is_enabled boolean NOT NULL DEFAULT true,

    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

-- Work Queue (durable outbox)
CREATE TABLE skill_work_queue (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    org_unit_id uuid, -- nullable for tenant-scoped jobs
    work_type text NOT NULL, -- submission_finalized | remediation_applied | remediation_reverted | mapping_published | user_forgotten | recalc_requested
    idempotency_key text NOT NULL,
    payload jsonb NOT NULL,
    status text NOT NULL DEFAULT 'pending', -- pending | in_progress | completed | failed
    attempts integer NOT NULL DEFAULT 0,
    available_at timestamptz NOT NULL DEFAULT now(),
    last_error text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skill_work_queue_idempotency_unique UNIQUE (tenant_id, idempotency_key)
);

-- Projections
CREATE TABLE skill_evidence_facts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    org_unit_id uuid NOT NULL,
    user_id uuid NOT NULL,

    submission_id uuid NOT NULL,
    skill_id uuid NOT NULL,
    evidence_unit_id text NOT NULL, -- e.g., question_version_id or evaluation_version_id
    contribution_value numeric,
    pass_fail boolean,
    weight numeric,
    evidence_source text NOT NULL, -- K | O | E

    -- Provenance for audit and explainability
    mapping_set_version_id uuid NOT NULL REFERENCES skill_mapping_set_versions(id) ON DELETE RESTRICT,
    skill_version_id uuid NOT NULL REFERENCES skill_versions(id) ON DELETE RESTRICT,
    submission_score_version_id uuid NOT NULL, -- FK to submission score version table when available
    mapping_rule_id uuid NOT NULL REFERENCES skill_mapping_rules(id) ON DELETE RESTRICT,

    -- Binding provenance (why this mapping set applied)
    mapping_binding_id uuid NOT NULL REFERENCES skill_mapping_bindings(id) ON DELETE RESTRICT,
    applied_scope_type text NOT NULL,
    applied_scope_id uuid,

    evidence_ref jsonb, -- Details of what matched (e.g., item_id, tag_key/value)
    computed_at timestamptz NOT NULL DEFAULT now(),

    -- Uniqueness constraint for idempotency (safe reprocessing)
    CONSTRAINT skill_evidence_facts_unique_for_idempotency
      UNIQUE (tenant_id, org_unit_id, submission_id, skill_id, evidence_unit_id, mapping_set_version_id, submission_score_version_id)
);

CREATE TABLE skill_rollups (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    org_unit_id uuid NOT NULL,
    user_id uuid NOT NULL,
    skill_id uuid NOT NULL,
    status text NOT NULL, -- not_started | in_progress | achieved
    last_updated_at timestamptz NOT NULL,
    confidence numeric,
    coverage numeric,
    last_evidence_summary jsonb,
    CONSTRAINT skill_rollups_tenant_org_user_skill_unique UNIQUE (tenant_id, org_unit_id, user_id, skill_id)
);

-- Jobs (admin-triggered, long-running recalcs/backfills; may enqueue work queue items)
CREATE TABLE skill_projection_jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NOT NULL,
    org_unit_id uuid NOT NULL,
    job_type text NOT NULL, -- recalc | backfill
    idempotency_key text NOT NULL,
    date_range_start timestamptz,
    date_range_end timestamptz,
    mapping_set_version_id uuid,
    status text NOT NULL DEFAULT 'pending', -- pending | in_progress | completed | failed
    progress jsonb,
    result_summary jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT skill_projection_jobs_idempotency_key_unique UNIQUE (tenant_id, org_unit_id, idempotency_key)
);

-- Partial unique indexes (Postgres-correct) for "one published version"
CREATE UNIQUE INDEX skill_versions_one_published_version
ON skill_versions (skill_id)
WHERE status = 'published';

CREATE UNIQUE INDEX skill_mapping_set_versions_one_published_version
ON skill_mapping_set_versions (mapping_set_id)
WHERE status = 'published';

3.2. Indexing strategy

• skill_evidence_facts:

  • (tenant_id, org_unit_id, user_id, skill_id)
  • (submission_id)
  • (mapping_set_version_id)
  • (mapping_binding_id)
  • Unique constraint provides an additional index for idempotency.

• skill_rollups:

  • (tenant_id, org_unit_id, user_id)
  • (tenant_id, org_unit_id, skill_id)

• skill_work_queue:

  • (tenant_id, status, available_at)
  • (tenant_id, org_unit_id, status, available_at) (optional if you shard polling by org)
  • Unique (tenant_id, idempotency_key)

• skill_projection_jobs:

  • (tenant_id, org_unit_id, status)
  • Unique (tenant_id, org_unit_id, idempotency_key)

3.3. Tenancy & Scoping Model

The Skills engine adopts a hybrid model:

• Tenant-scoped taxonomy: skills, skill_versions, competency_tag_* are tenant-level resources (no org_unit_id). This enables a unified skills framework across a tenant.
• Org-scoped execution and evidence: skill_mapping_sets, projections, jobs and the work queue are org-scoped (or org-aware) to preserve strict silo behaviour.
• Cascading override bindings: skill_mapping_bindings provides tenant defaults plus scoped overrides (org/sub-org/programme/evaluation_version). The worker resolves the effective binding for each submission context and records the applied scope in evidence facts.

---

4. Service Implementation (internal/services/skills)

[ To be further detailed. Will include: mapping resolution, rule evaluation, fact/rollup upserts, and “explain” assembly. ]

---

5. Worker & Job Implementation (internal/workers)

[ To be further detailed. Will include: skills_projection_worker consuming skill_work_queue, retry/backoff, and admin jobs enqueueing work items. ]

---

6. API Specification (BFF)

The API provides UI-ready view models to minimize frontend logic, leveraging provenance stored in projection tables and binding resolution.

6.1. Admin Endpoints

• GET /skills (Skills Library): Lists all tenant skills + published version; includes org-scoped coverage/confidence for the caller’s current org.
• GET /skills/{skillId} (Skill Detail): Returns skill definition + published mapping context (effective binding) for current org; includes draft mapping sets and optional impact preview references.
• GET /skills/mapping/effective (Effective Mapping Resolver): Returns the effective mapping binding and a resolution trace for a given context (org unit + optional programme + optional evaluation version).
• POST /skill-projection-jobs (Run Recalculation): Creates a job to backfill/recalculate for a scope and mapping set version (may enqueue work items).

6.2. User-Facing Endpoints (Overall vs Programme Context)

Programme overrides apply only when the view is requested in programme context (to avoid confusing “overall skill” changes).

• GET /me/skills?view=overall (User Skills Profile – Overall): Uses tenant/org/sub-org defaults and evaluation/version overrides.

• GET /me/skills?view=programme&programmeId=... (User Skills Profile – Programme): Uses programme binding overrides (and any evaluation/version overrides) only within that programme view.

• GET /users/\{userId\}/skills/{skillId}/explain (Explain Skill Result): Returns:

  • mapping_set_version_id, skill_version_id
  • mapping_binding_id, applied_scope_type, applied_scope_id
  • matched mapping_rule_ids
  • evidence submissions + submission_score_version_id for each

---

7. Compliance & Data Lifecycle

The Skills engine must integrate with Evalium’s compliance flows (Forget/Anonymize/DSAR). Forget/anonymize must delete or anonymize corresponding rows in:

• skill_evidence_facts
• skill_rollups
• skill_projection_jobs (or scrub user_id fields)
• skill_work_queue (scrub payload user references where applicable)

---

8. Testing strategy

[ To be further detailed. Explicitly cover: mapping resolution precedence, idempotency, worker retry semantics, remediation propagation, and explainability provenance. ]

---

9. Open Questions & Risks

• Performance of projection worker on large datasets needs benchmarking.
• skill_mapping_rules.selector_json complexity may require strict validation + tests.
• Impact preview for draft mapping sets may require asynchronous calculation.
• Define definitive submission_score_version_id FK target and remediation semantics for re-projection.