Competency Framework & Knowledge Graph — System Design Document

EduOne AI Platform | Version 1.0

Field	Value
Author	EduOne Engineering Team
Created	2026-07-17
Status	Draft
Audience	Backend Engineers, Frontend Engineers, QA
Related Docs	User Requirements Document, API Reference

Architecture Overview
Database Schema
API Design
Diagnostic Engine Design
Frontend Components
Seed Data
Migration Plan

1. Architecture Overview

1.1 System Context

The Competency Framework (CF) and Knowledge Graph (KG) subsystem integrates with the existing EduOne AI Platform as an additional vertical that enriches the pedagogical model. It provides structured skill taxonomies, prerequisite dependency graphs, and root-cause diagnostic capabilities.

mermaid

graph TB
    subgraph External["External Systems"]
        EXT_FW["External Frameworks<br/>(ISTE, CSTA, K-12 CS)"]
        LMS["LMS / SIS"]
    end

    subgraph EduOne["EduOne AI Platform"]
        subgraph CF_System["CF / KG Subsystem"]
            CFM["CF Manager<br/>(CRUD Framework Entities)"]
            DGE["Dependency Graph Engine<br/>(Graph Traversal, Path Finding)"]
            DIAG["Diagnostic Engine<br/>(Root-Cause Analysis)"]
        end

        subgraph Existing["Existing Components"]
            EVP["Evidence Processor"]
            MC["Mastery Calculator"]
            REC["Recommendation Engine"]
            CUR["Curriculum Service"]
        end

        subgraph Data["Data Layer"]
            D1["Cloudflare D1<br/>(SQLite)"]
        end
    end

    subgraph Clients["Clients"]
        TEACH["Teacher Dashboard"]
        ADMIN["Admin Panel"]
        STU["Student View"]
    end

    CFM -->|read/write| D1
    DGE -->|query| D1
    DIAG -->|query mastery| MC
    DIAG -->|query graph| DGE
    DIAG -->|feed results| REC
    CFM -->|map skills| CUR
    EXT_FW -->|import mappings| CFM
    LMS -->|sync data| EVP
    EVP -->|evidence| MC
    MC -->|mastery scores| D1
    REC -->|recommendations| D1

    TEACH -->|manage framework| CFM
    TEACH -->|view diagnostics| DIAG
    ADMIN -->|configure graph| DGE
    STU -->|view skill map| DGE

1.2 Component Architecture

mermaid

graph LR
    subgraph Frontend["Frontend Layer"]
        CFD["CF Dashboard"]
        SGV["Skill Graph Viewer"]
        DE["Dependency Editor"]
        FB["Framework Browser"]
    end

    subgraph API["API Layer (Hono Workers)"]
        CFR["CF Routes<br/>/api/cf/*"]
        GR["Graph Routes<br/>/api/cf/graph/*"]
        DR["Diagnostic Routes<br/>/api/cf/diagnose"]
    end

    subgraph Services["Service Layer"]
        CFS["CF Service<br/>(CRUD Operations)"]
        GS["Graph Service<br/>(Traversal, Pathfinding)"]
        DS["Diagnostic Service<br/>(Root-Cause Analysis)"]
        VS["Validation Service<br/>(Cycle Detection, Integrity)"]
    end

    subgraph Database["Database Layer (D1)"]
        CF_T["CF Tables<br/>(domains, areas,<br/>competencies, skills,<br/>indicators, dependencies)"]
        EVD_T["Evidence Tables<br/>(evd_learner_skill_states)"]
        REC_T["Recommendation Tables<br/>(rec_recommendations)"]
    end

    CFD --> CFR
    SGV --> GR
    DE --> GR
    FB --> CFR

    CFR --> CFS
    GR --> GS
    DR --> DS

    CFS --> CF_T
    GS --> CF_T
    DS --> CF_T
    DS --> EVD_T
    DS --> REC_T
    VS --> CF_T

1.3 Key Design Decisions

Decision	Rationale
SQLite (D1) for graph storage	Sufficient for sub-1000 node graphs; avoids Neo4j operational complexity
BFS over Dijkstra	Edge weights are strengths (0-1), not distances; BFS suits prerequisite chains
JSON columns for proficiency	Flexible schema for varying proficiency level definitions per skill
Soft deletes via `status` column	Preserve referential integrity with evidence/mastery records
Adjacency list model	Simple, performant for D1; CTEs handle recursive queries

2. Database Schema

All tables target Cloudflare D1 (SQLite). UUIDs are generated application-side as TEXT primary keys.

2.1 Table Definitions

cf_domains

Top-level grouping of competency areas (e.g., "Technology, STEM & AI").

sql

CREATE TABLE cf_domains (
  id TEXT PRIMARY KEY,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  name TEXT NOT NULL,
  description TEXT,
  icon TEXT,
  color TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'archived', 'draft')),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (organization_id, name)
);

CREATE INDEX idx_cf_domains_org ON cf_domains(organization_id);
CREATE INDEX idx_cf_domains_status ON cf_domains(status);

cf_competency_areas

Second-level grouping within a domain (e.g., "Programming", "Data Literacy").

sql

CREATE TABLE cf_competency_areas (
  id TEXT PRIMARY KEY,
  domain_id TEXT NOT NULL REFERENCES cf_domains(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  name TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'archived', 'draft')),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (domain_id, name)
);

CREATE INDEX idx_cf_areas_domain ON cf_competency_areas(domain_id);
CREATE INDEX idx_cf_areas_org ON cf_competency_areas(organization_id);

cf_competencies

Individual competency statements within an area.

sql

CREATE TABLE cf_competencies (
  id TEXT PRIMARY KEY,
  area_id TEXT NOT NULL REFERENCES cf_competency_areas(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  name TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'archived', 'draft')),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (area_id, name)
);

CREATE INDEX idx_cf_competencies_area ON cf_competencies(area_id);
CREATE INDEX idx_cf_competencies_org ON cf_competencies(organization_id);

cf_skills

Granular, measurable skills. Each skill has proficiency level definitions and a taxonomy classification.

sql

CREATE TABLE cf_skills (
  id TEXT PRIMARY KEY,
  competency_id TEXT NOT NULL REFERENCES cf_competencies(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  cur_skill_id TEXT REFERENCES cur_skills(id),
  code TEXT,
  name TEXT NOT NULL,
  definition TEXT,
  taxonomy_level TEXT NOT NULL DEFAULT 'apply'
    CHECK (taxonomy_level IN (
      'remember', 'understand', 'apply',
      'analyze', 'evaluate', 'create'
    )),
  proficiency_levels TEXT NOT NULL DEFAULT '[]',
  -- JSON array: [{ "level": 1, "name": "Novice", "description": "..." }, ...]
  estimated_hours REAL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'archived', 'draft')),
  metadata TEXT DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (competency_id, name)
);

CREATE INDEX idx_cf_skills_competency ON cf_skills(competency_id);
CREATE INDEX idx_cf_skills_org ON cf_skills(organization_id);
CREATE INDEX idx_cf_skills_cur ON cf_skills(cur_skill_id);
CREATE INDEX idx_cf_skills_taxonomy ON cf_skills(taxonomy_level);
CREATE INDEX idx_cf_skills_code ON cf_skills(code);

Proficiency Levels JSON Schema:

json

[
  {
    "level": 1,
    "name": "Novice",
    "description": "Can recognize the concept but cannot apply it independently",
    "mastery_range": [0.0, 0.25]
  },
  {
    "level": 2,
    "name": "Developing",
    "description": "Can apply the concept with guidance and scaffolding",
    "mastery_range": [0.25, 0.50]
  },
  {
    "level": 3,
    "name": "Proficient",
    "description": "Can apply the concept independently in familiar contexts",
    "mastery_range": [0.50, 0.75]
  },
  {
    "level": 4,
    "name": "Advanced",
    "description": "Can transfer and adapt the concept to novel situations",
    "mastery_range": [0.75, 1.0]
  }
]

cf_indicators

Observable, assessable indicators tied to a skill. Each indicator defines what evidence proves mastery.

sql

CREATE TABLE cf_indicators (
  id TEXT PRIMARY KEY,
  skill_id TEXT NOT NULL REFERENCES cf_skills(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  cur_indicator_id TEXT REFERENCES cur_indicators(id),
  code TEXT,
  name TEXT NOT NULL,
  description TEXT,
  assessment_criteria TEXT,
  -- JSON: { "type": "rubric|checklist|score", "criteria": [...] }
  mastery_threshold REAL NOT NULL DEFAULT 0.7
    CHECK (mastery_threshold >= 0.0 AND mastery_threshold <= 1.0),
  weight REAL NOT NULL DEFAULT 1.0
    CHECK (weight > 0.0 AND weight <= 10.0),
  sort_order INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'archived', 'draft')),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (skill_id, name)
);

CREATE INDEX idx_cf_indicators_skill ON cf_indicators(skill_id);
CREATE INDEX idx_cf_indicators_org ON cf_indicators(organization_id);
CREATE INDEX idx_cf_indicators_cur ON cf_indicators(cur_indicator_id);

cf_skill_dependencies

The core knowledge graph: directed edges between skills representing prerequisite relationships.

sql

CREATE TABLE cf_skill_dependencies (
  id TEXT PRIMARY KEY,
  source_skill_id TEXT NOT NULL REFERENCES cf_skills(id) ON DELETE CASCADE,
  target_skill_id TEXT NOT NULL REFERENCES cf_skills(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  relationship_type TEXT NOT NULL DEFAULT 'prerequisite'
    CHECK (relationship_type IN (
      'prerequisite',   -- source must be mastered before target
      'corequisite',    -- source should be learned alongside target
      'recommended',    -- source is helpful but not required
      'conceptual'      -- source shares conceptual foundation
    )),
  required_source_level INTEGER DEFAULT 3
    CHECK (required_source_level >= 1 AND required_source_level <= 4),
  strength REAL NOT NULL DEFAULT 0.8
    CHECK (strength >= 0.0 AND strength <= 1.0),
  confidence REAL NOT NULL DEFAULT 0.5
    CHECK (confidence >= 0.0 AND confidence <= 1.0),
  rationale TEXT DEFAULT '{}',
  -- JSON: { "pedagogical": "...", "empirical": "...", "source": "..." }
  diagnostic_signals TEXT DEFAULT '[]',
  -- JSON: [{ "error_pattern": "...", "indicator_id": "...", "weight": 0.8 }]
  remediation_candidates TEXT DEFAULT '[]',
  -- JSON: [{ "activity_type": "...", "description": "...", "estimated_minutes": 30 }]
  source_type TEXT NOT NULL DEFAULT 'expert'
    CHECK (source_type IN ('expert', 'data_driven', 'imported', 'ai_suggested')),
  validation_status TEXT NOT NULL DEFAULT 'pending'
    CHECK (validation_status IN ('pending', 'validated', 'rejected', 'needs_review')),
  validated_by TEXT REFERENCES iam_users(id),
  validated_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (source_skill_id, target_skill_id),
  CHECK (source_skill_id != target_skill_id)
);

CREATE INDEX idx_cf_deps_source ON cf_skill_dependencies(source_skill_id);
CREATE INDEX idx_cf_deps_target ON cf_skill_dependencies(target_skill_id);
CREATE INDEX idx_cf_deps_org ON cf_skill_dependencies(organization_id);
CREATE INDEX idx_cf_deps_type ON cf_skill_dependencies(relationship_type);
CREATE INDEX idx_cf_deps_validation ON cf_skill_dependencies(validation_status);

Diagnostic Signals JSON Schema:

json

[
  {
    "error_pattern": "Student uses = instead of == in conditionals",
    "indicator_id": "ind_boolean_ops",
    "weight": 0.9,
    "description": "Assignment vs comparison confusion indicates weak boolean logic foundation"
  },
  {
    "error_pattern": "Student cannot trace variable state through loop iterations",
    "indicator_id": "ind_var_tracing",
    "weight": 0.7,
    "description": "Loop tracing failure may indicate variable scope misunderstanding"
  }
]

cf_proficiency_levels

Organization-wide proficiency level templates that can be applied to skills.

sql

CREATE TABLE cf_proficiency_levels (
  id TEXT PRIMARY KEY,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  name TEXT NOT NULL,
  level_number INTEGER NOT NULL CHECK (level_number >= 1 AND level_number <= 10),
  description TEXT,
  mastery_min REAL NOT NULL CHECK (mastery_min >= 0.0 AND mastery_min <= 1.0),
  mastery_max REAL NOT NULL CHECK (mastery_max >= 0.0 AND mastery_max <= 1.0),
  color TEXT,
  icon TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (organization_id, level_number),
  CHECK (mastery_min < mastery_max)
);

CREATE INDEX idx_cf_prof_org ON cf_proficiency_levels(organization_id);

cf_framework_mappings

Maps internal CF skills to external standards and frameworks (ISTE, CSTA, etc.).

sql

CREATE TABLE cf_framework_mappings (
  id TEXT PRIMARY KEY,
  skill_id TEXT NOT NULL REFERENCES cf_skills(id) ON DELETE CASCADE,
  organization_id TEXT NOT NULL REFERENCES iam_organizations(id),
  external_framework TEXT NOT NULL,
  -- e.g., 'ISTE', 'CSTA_K12', 'NGSS', 'Common_Core'
  external_code TEXT NOT NULL,
  -- e.g., 'ISTE.1c', 'CSTA.2-AP-12'
  external_name TEXT,
  external_url TEXT,
  alignment_strength TEXT NOT NULL DEFAULT 'strong'
    CHECK (alignment_strength IN ('exact', 'strong', 'partial', 'related')),
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE (skill_id, external_framework, external_code)
);

CREATE INDEX idx_cf_mappings_skill ON cf_framework_mappings(skill_id);
CREATE INDEX idx_cf_mappings_framework ON cf_framework_mappings(external_framework);
CREATE INDEX idx_cf_mappings_org ON cf_framework_mappings(organization_id);

2.2 Entity Relationship Diagram

mermaid

erDiagram
    cf_domains ||--o{ cf_competency_areas : contains
    cf_competency_areas ||--o{ cf_competencies : contains
    cf_competencies ||--o{ cf_skills : contains
    cf_skills ||--o{ cf_indicators : measures
    cf_skills ||--o{ cf_skill_dependencies : "is source"
    cf_skills ||--o{ cf_skill_dependencies : "is target"
    cf_skills ||--o{ cf_framework_mappings : "maps to"
    cf_skills |o--o| cur_skills : "linked to"
    cf_indicators |o--o| cur_indicators : "linked to"
    iam_organizations ||--o{ cf_domains : owns

    cf_domains {
        TEXT id PK
        TEXT organization_id FK
        TEXT name
        TEXT description
        TEXT status
    }

    cf_competency_areas {
        TEXT id PK
        TEXT domain_id FK
        TEXT name
        TEXT description
    }

    cf_competencies {
        TEXT id PK
        TEXT area_id FK
        TEXT name
        TEXT description
    }

    cf_skills {
        TEXT id PK
        TEXT competency_id FK
        TEXT cur_skill_id FK
        TEXT name
        TEXT definition
        TEXT taxonomy_level
        TEXT proficiency_levels
    }

    cf_indicators {
        TEXT id PK
        TEXT skill_id FK
        TEXT name
        REAL mastery_threshold
        REAL weight
    }

    cf_skill_dependencies {
        TEXT id PK
        TEXT source_skill_id FK
        TEXT target_skill_id FK
        TEXT relationship_type
        REAL strength
        REAL confidence
        TEXT validation_status
    }

    cf_framework_mappings {
        TEXT id PK
        TEXT skill_id FK
        TEXT external_framework
        TEXT external_code
    }

2.3 Key Indexes Summary

Table	Index Name	Columns	Purpose
`cf_domains`	`idx_cf_domains_org`	`organization_id`	Filter domains by org
`cf_skills`	`idx_cf_skills_competency`	`competency_id`	List skills under competency
`cf_skills`	`idx_cf_skills_cur`	`cur_skill_id`	Join with existing curriculum
`cf_skills`	`idx_cf_skills_taxonomy`	`taxonomy_level`	Filter by Bloom's level
`cf_skill_dependencies`	`idx_cf_deps_source`	`source_skill_id`	Find what a skill depends on
`cf_skill_dependencies`	`idx_cf_deps_target`	`target_skill_id`	Find what depends on a skill
`cf_skill_dependencies`	`idx_cf_deps_validation`	`validation_status`	Filter pending validations
`cf_indicators`	`idx_cf_indicators_skill`	`skill_id`	List indicators for a skill
`cf_framework_mappings`	`idx_cf_mappings_framework`	`external_framework`	Query by external standard

3. API Design

All endpoints are served via Hono Workers on Cloudflare. Authentication is handled by existing middleware (requireAuth, requireRole). Base path: /api/cf.

3.1 Framework Management

Domains

Method	Path	Auth Role	Description
GET	`/api/cf/domains`	`teacher+`	List all domains for org
POST	`/api/cf/domains`	`admin`	Create a new domain
GET	`/api/cf/domains/:id`	`teacher+`	Get domain detail
PUT	`/api/cf/domains/:id`	`admin`	Update domain
DELETE	`/api/cf/domains/:id`	`admin`	Archive domain (soft delete)

POST /api/cf/domains — Request:

json

{
  "name": "Technology, STEM & AI",
  "description": "Core technology and computational skills for K-12 students",
  "icon": "cpu",
  "color": "#6366f1",
  "sort_order": 1
}

POST /api/cf/domains — Response (201):

json

{
  "success": true,
  "data": {
    "id": "dom_01HXYZ",
    "organization_id": "org_01ABC",
    "name": "Technology, STEM & AI",
    "description": "Core technology and computational skills for K-12 students",
    "icon": "cpu",
    "color": "#6366f1",
    "sort_order": 1,
    "status": "active",
    "created_at": "2026-07-17T09:00:00Z",
    "updated_at": "2026-07-17T09:00:00Z"
  }
}

Competency Areas, Competencies, Skills, Indicators

Method	Path	Auth Role	Description
GET	`/api/cf/competency-areas`	`teacher+`	List areas, filter by `?domain_id`
POST	`/api/cf/competency-areas`	`admin`	Create competency area
PUT	`/api/cf/competency-areas/:id`	`admin`	Update competency area
GET	`/api/cf/competencies`	`teacher+`	List, filter by `?area_id`
POST	`/api/cf/competencies`	`admin`	Create competency
PUT	`/api/cf/competencies/:id`	`admin`	Update competency
GET	`/api/cf/skills`	`teacher+`	List skills, filterable
GET	`/api/cf/skills/:id`	`teacher+`	Get skill with dependencies
POST	`/api/cf/skills`	`admin`	Create skill
PUT	`/api/cf/skills/:id`	`admin`	Update skill
GET	`/api/cf/indicators`	`teacher+`	List, filter by `?skill_id`
POST	`/api/cf/indicators`	`admin`	Create indicator
PUT	`/api/cf/indicators/:id`	`admin`	Update indicator

GET /api/cf/skills/:id — Response (200):

json

{
  "success": true,
  "data": {
    "id": "sk_loops",
    "name": "Loop Structures",
    "definition": "Ability to use for, while, and do-while loops to repeat actions",
    "taxonomy_level": "apply",
    "proficiency_levels": [
      { "level": 1, "name": "Novice", "mastery_range": [0.0, 0.25] },
      { "level": 2, "name": "Developing", "mastery_range": [0.25, 0.50] },
      { "level": 3, "name": "Proficient", "mastery_range": [0.50, 0.75] },
      { "level": 4, "name": "Advanced", "mastery_range": [0.75, 1.0] }
    ],
    "competency": {
      "id": "comp_ctrl_flow",
      "name": "Control Flow"
    },
    "indicators": [
      {
        "id": "ind_for_loop",
        "name": "Uses for-loop correctly",
        "mastery_threshold": 0.7,
        "weight": 1.0
      }
    ],
    "prerequisites": [
      {
        "dependency_id": "dep_var_loops",
        "skill_id": "sk_variables",
        "skill_name": "Variables & Data Types",
        "relationship_type": "prerequisite",
        "strength": 0.9,
        "required_source_level": 3
      }
    ],
    "dependents": [
      {
        "dependency_id": "dep_loops_nested",
        "skill_id": "sk_nested_loops",
        "skill_name": "Nested Loops & Complexity",
        "relationship_type": "prerequisite",
        "strength": 0.95
      }
    ]
  }
}

3.2 Dependency Management

Method	Path	Auth Role	Description
GET	`/api/cf/dependencies`	`teacher+`	List all dependency edges
POST	`/api/cf/dependencies`	`admin`	Create dependency edge
PUT	`/api/cf/dependencies/:id`	`admin`	Update dependency
DELETE	`/api/cf/dependencies/:id`	`admin`	Remove dependency edge
PUT	`/api/cf/dependencies/:id/validate`	`admin`	Set validation status

POST /api/cf/dependencies — Request:

json

{
  "source_skill_id": "sk_variables",
  "target_skill_id": "sk_loops",
  "relationship_type": "prerequisite",
  "required_source_level": 3,
  "strength": 0.9,
  "confidence": 0.85,
  "rationale": {
    "pedagogical": "Understanding variables is essential for loop counters and accumulators",
    "empirical": "85% of students who struggle with loops have weak variable understanding"
  },
  "diagnostic_signals": [
    {
      "error_pattern": "Student does not initialize loop counter variable",
      "indicator_id": "ind_var_init",
      "weight": 0.8
    }
  ],
  "remediation_candidates": [
    {
      "activity_type": "practice",
      "description": "Variable tracing exercises with visual debugger",
      "estimated_minutes": 20
    }
  ],
  "source_type": "expert"
}

Validation Rules (enforced server-side):

source_skill_id != target_skill_id (no self-loops)
Adding the edge must not create a cycle (checked via DFS from target → source)
Both skills must belong to the same organization_id
Duplicate (source_skill_id, target_skill_id) pairs are rejected

3.3 Graph Queries

Method	Path	Auth Role	Description
GET	`/api/cf/graph`	`teacher+`	Full graph: all nodes + edges
GET	`/api/cf/graph/prerequisites/:skillId`	`teacher+`	Recursive prerequisites (BFS)
GET	`/api/cf/graph/dependents/:skillId`	`teacher+`	Recursive dependents (forward BFS)
GET	`/api/cf/graph/path`	`teacher+`	Shortest path: `?from=X&to=Y`
GET	`/api/cf/graph/stats`	`admin`	Graph statistics (nodes, edges, density)

GET /api/cf/graph — Response (200):

json

{
  "success": true,
  "data": {
    "nodes": [
      {
        "id": "sk_variables",
        "name": "Variables & Data Types",
        "taxonomy_level": "understand",
        "competency_area": "Programming",
        "domain": "Technology, STEM & AI"
      }
    ],
    "edges": [
      {
        "id": "dep_var_loops",
        "source": "sk_variables",
        "target": "sk_loops",
        "relationship_type": "prerequisite",
        "strength": 0.9,
        "validation_status": "validated"
      }
    ],
    "stats": {
      "total_nodes": 20,
      "total_edges": 35,
      "max_depth": 6,
      "connected_components": 1
    }
  }
}

GET /api/cf/graph/path?from=sk_variables&to=sk_ml_basics — Response (200):

json

{
  "success": true,
  "data": {
    "path": [
      { "skill_id": "sk_variables", "name": "Variables & Data Types" },
      { "skill_id": "sk_loops", "name": "Loop Structures" },
      { "skill_id": "sk_arrays", "name": "Arrays & Lists" },
      { "skill_id": "sk_data_proc", "name": "Data Processing" },
      { "skill_id": "sk_ml_basics", "name": "ML Fundamentals" }
    ],
    "total_edges": 4,
    "cumulative_strength": 0.72
  }
}

3.4 Diagnostic Endpoint

Method	Path	Auth Role	Description
POST	`/api/cf/diagnose`	`teacher+`	Root-cause diagnosis for a student

POST /api/cf/diagnose — Request:

json

{
  "student_id": "stu_01ABC",
  "skill_id": "sk_loops",
  "class_id": "cls_01XYZ"
}

POST /api/cf/diagnose — Response (200):

json

{
  "success": true,
  "data": {
    "student_id": "stu_01ABC",
    "target_skill": {
      "id": "sk_loops",
      "name": "Loop Structures",
      "current_mastery": 0.25
    },
    "root_causes": [
      {
        "skill_id": "sk_variables",
        "skill_name": "Variables & Data Types",
        "current_mastery": 0.30,
        "required_level": 3,
        "required_mastery_min": 0.50,
        "gap": 0.20,
        "edge_strength": 0.9,
        "root_cause_score": 0.82,
        "diagnostic_signals_matched": [
          "Student does not initialize loop counter variable"
        ],
        "remediation": [
          {
            "activity_type": "practice",
            "description": "Variable tracing exercises with visual debugger",
            "estimated_minutes": 20
          }
        ]
      },
      {
        "skill_id": "sk_boolean",
        "skill_name": "Boolean Logic & Conditions",
        "current_mastery": 0.45,
        "required_level": 2,
        "required_mastery_min": 0.25,
        "gap": 0.0,
        "edge_strength": 0.7,
        "root_cause_score": 0.15,
        "diagnostic_signals_matched": [],
        "remediation": []
      }
    ],
    "remediation_path": [
      {
        "order": 1,
        "skill_id": "sk_variables",
        "skill_name": "Variables & Data Types",
        "action": "remediate",
        "estimated_minutes": 20
      },
      {
        "order": 2,
        "skill_id": "sk_loops",
        "skill_name": "Loop Structures",
        "action": "reteach",
        "estimated_minutes": 30
      }
    ]
  }
}

3.5 Error Response Format

All error responses follow a consistent structure:

json

{
  "success": false,
  "error": {
    "code": "CYCLE_DETECTED",
    "message": "Adding this dependency would create a cycle: sk_loops → sk_variables → sk_loops",
    "details": {
      "cycle_path": ["sk_loops", "sk_variables", "sk_loops"]
    }
  }
}

HTTP Status	Error Code	Description
400	`VALIDATION_ERROR`	Invalid request body or parameters
400	`CYCLE_DETECTED`	Dependency would create a cycle
400	`SELF_REFERENCE`	Source and target skill are the same
404	`NOT_FOUND`	Resource does not exist
409	`DUPLICATE_DEPENDENCY`	Dependency edge already exists
422	`CROSS_ORG_REFERENCE`	Skills belong to different organizations

4. Diagnostic Engine Design

4.1 Root-Cause Analysis Algorithm

The diagnostic engine identifies why a student is failing at a particular skill by traversing the prerequisite graph backward and evaluating mastery gaps.

function diagnoseRootCause(studentId, failedSkillId, classId):
  // Step 1: Gather prerequisites via BFS
  prerequisites = BFS_backward(failedSkillId)
  // Returns: [{ skill, edge }] ordered by depth (shallowest first)

  // Step 2: Retrieve student mastery for all relevant skills
  masteryMap = getMasteryScores(studentId, classId, prerequisites.skillIds)
  // Returns: Map<skillId, { mastery, confidence, last_updated }>

  // Step 3: Calculate Root Cause Score for each prerequisite
  rootCauses = []
  for each (prereq, edge) in prerequisites:
    mastery = masteryMap[prereq.id] ?? { mastery: 0, confidence: 0 }
    requiredMin = getProficiencyMin(edge.required_source_level)

    // Core scoring formula
    masteryGap = max(0, requiredMin - mastery.mastery)
    evidenceConfidence = mastery.confidence
    errorMatch = matchDiagnosticSignals(studentId, edge.diagnostic_signals)
    remediationLeverage = len(edge.remediation_candidates) > 0 ? 1.2 : 1.0

    score = edge.strength
          * masteryGap
          * (1 + errorMatch)
          * remediationLeverage
          * (0.5 + 0.5 * evidenceConfidence)

    if score > THRESHOLD (0.1):
      rootCauses.append({
        skill: prereq,
        mastery: mastery,
        gap: masteryGap,
        score: score,
        signalsMatched: matchedSignals,
        remediation: edge.remediation_candidates
      })

  // Step 4: Sort by score descending
  rootCauses.sortByDescending(rc => rc.score)

  // Step 5: Build remediation path via topological sort
  remediationPath = topologicalSort(
    rootCauses.filter(rc => rc.gap > 0).map(rc => rc.skill)
  )

  return { rootCauses, remediationPath }

4.2 Scoring Formula Breakdown

Component	Symbol	Range	Description
Edge Strength	`S`	[0, 1]	How strongly the prerequisite impacts the target
Mastery Gap	`G`	[0, 1]	Difference between required and actual mastery
Error Match	`E`	[0, N]	Number of diagnostic signal patterns matched
Remediation Leverage	`L`		Bonus if remediation activities are defined
Evidence Confidence	`C`	[0, 1]	Confidence of the mastery estimate

Formula: Score = S × G × (1 + E) × L × (0.5 + 0.5 × C)

4.3 Graph Traversal Algorithms

BFS for Prerequisites (Backward Traversal)

function BFS_backward(skillId):
  queue = [skillId]
  visited = Set()
  result = []

  while queue is not empty:
    current = queue.dequeue()
    if current in visited: continue
    visited.add(current)

    edges = SELECT * FROM cf_skill_dependencies
            WHERE target_skill_id = current
              AND validation_status = 'validated'

    for each edge in edges:
      result.append({ skill: edge.source_skill_id, edge: edge })
      queue.enqueue(edge.source_skill_id)

  return result

Cycle Detection (DFS)

function hasCycle(sourceId, targetId):
  // Check if adding edge source→target creates a cycle
  // A cycle exists if there's already a path from target→source
  visited = Set()
  stack = [targetId]

  while stack is not empty:
    current = stack.pop()
    if current == sourceId: return true
    if current in visited: continue
    visited.add(current)

    successors = SELECT target_skill_id FROM cf_skill_dependencies
                 WHERE source_skill_id = current

    for each successor in successors:
      stack.push(successor)

  return false

Topological Sort for Remediation Ordering

function topologicalSort(skills):
  inDegree = Map()
  adjacency = Map()

  // Build subgraph of only the skills to remediate
  for each skill in skills:
    inDegree[skill] = 0

  for each edge in dependencies WHERE both source and target are in skills:
    adjacency[edge.source].add(edge.target)
    inDegree[edge.target] += 1

  // Kahn's algorithm
  queue = skills.filter(s => inDegree[s] == 0)
  sorted = []

  while queue is not empty:
    node = queue.dequeue()
    sorted.append(node)
    for each neighbor in adjacency[node]:
      inDegree[neighbor] -= 1
      if inDegree[neighbor] == 0:
        queue.enqueue(neighbor)

  return sorted  // Foundation skills first → target skill last

4.4 Integration with Recommendation Engine

Diagnostic results automatically feed into the existing recommendation engine:

mermaid

sequenceDiagram
    participant T as Teacher
    participant API as CF API
    participant Diag as Diagnostic Engine
    participant Graph as Graph Service
    participant Mastery as Mastery Calculator
    participant Rec as Recommendation Engine
    participant DB as D1 Database

    T->>API: POST /api/cf/diagnose
    API->>Diag: diagnoseRootCause(student, skill)
    Diag->>Graph: BFS_backward(skillId)
    Graph->>DB: Query cf_skill_dependencies
    DB-->>Graph: Prerequisite edges
    Graph-->>Diag: Prerequisites list
    Diag->>Mastery: getMasteryScores(student, skills)
    Mastery->>DB: Query evd_learner_skill_states
    DB-->>Mastery: Mastery records
    Mastery-->>Diag: Mastery map
    Diag->>Diag: Calculate root cause scores
    Diag->>Diag: Build remediation path
    Diag-->>API: Diagnostic result
    API->>Rec: createRecommendation(result)
    Rec->>DB: INSERT rec_recommendations
    Note over DB: reason = diagnostic JSON trace
    API-->>T: Diagnostic response

The rec_recommendations entry stores the diagnostic trace:

json

{
  "type": "remediation",
  "reason": {
    "source": "cf_diagnostic",
    "target_skill": "sk_loops",
    "root_cause_skill": "sk_variables",
    "root_cause_score": 0.82,
    "mastery_gap": 0.20
  }
}

5. Frontend Components

5.1 Skill Graph Viewer

An interactive, force-directed graph visualization for exploring skill dependencies and mastery states.

Technology: vis-network (lightweight, no heavy D3 dependency)

Node Representation:

Mastery State	Color	Border	Description
Mastered	`#22c55e`	solid	Mastery ≥ 0.75
Proficient	`#84cc16`	solid	Mastery 0.50–0.75
Developing	`#eab308`	solid	Mastery 0.25–0.50
Weak	`#ef4444`	solid	Mastery < 0.25, evidence > 0
Unexposed	`#9ca3af`	dashed	No evidence recorded
Root Cause	`#ef4444`	pulsing	Identified by diagnostic

Edge Representation:

Relationship	Style	Color	Arrow
Prerequisite	solid	`#6366f1`	→
Corequisite	dashed	`#8b5cf6`	↔
Recommended	dotted	`#a78bfa`	→
Conceptual	dotted	`#c4b5fd`	- -
Remediation Path	solid	`#ef4444`	→ (thick)

Interactions:

Click node → Side panel shows skill card: name, definition, mastery score, proficiency level, indicators, prerequisites/dependents
Right-click node → Context menu: "Diagnose from here", "Show prerequisites", "Show dependents"
Hover edge → Tooltip with strength, type, rationale
Toggle views: Full graph / Student overlay / Remediation path
Zoom/pan with mouse; minimap in corner

5.2 Framework Browser

A hierarchical tree view for navigating the full competency framework.

📁 Technology, STEM & AI (Domain)
├── 📂 Programming (Competency Area)
│   ├── 📋 Sequential Thinking (Competency)
│   │   ├── 🎯 Variables & Data Types (Skill)
│   │   │   ├── ✅ Declares variables with correct type (Indicator)
│   │   │   └── ✅ Traces variable values through code (Indicator)
│   │   └── 🎯 Operators & Expressions (Skill)
│   ├── 📋 Control Flow (Competency)
│   │   ├── 🎯 Conditionals (Skill)
│   │   ├── 🎯 Loop Structures (Skill)
│   │   └── 🎯 Nested Loops & Complexity (Skill)
│   └── 📋 Modularity (Competency)
│       ├── 🎯 Functions & Parameters (Skill)
│       └── 🎯 Code Reuse & Libraries (Skill)
├── 📂 Data Literacy (Competency Area)
│   └── ...
└── 📂 AI & Machine Learning (Competency Area)
    └── ...

Features:

Expand/collapse all levels
Search by skill name, code, or keyword
Filter by taxonomy level, status, mastery state
Drag-and-drop reordering (admin only)
Inline editing of sort_order (admin only)

5.3 Dependency Editor (Admin)

A specialized interface for managing the knowledge graph edges.

Features:

Visual Mode: Draw edges on the graph by clicking source → target
Table Mode: Spreadsheet-like view of all edges with inline editing
Validation Panel: Queue of pending validations with approve/reject actions
Cycle Warning: Real-time cycle detection before saving
Bulk Import: CSV upload for mass edge creation

Validation Workflow:

mermaid

stateDiagram-v2
    [*] --> Pending : Edge created
    Pending --> Validated : Admin approves
    Pending --> Rejected : Admin rejects
    Pending --> NeedsReview : Flagged for review
    NeedsReview --> Validated : Admin approves
    NeedsReview --> Rejected : Admin rejects
    Validated --> NeedsReview : Re-flagged
    Rejected --> Pending : Re-submitted

6. Seed Data

SQL INSERT statements for the hackathon demo. All IDs use readable prefixes for debugging.

6.1 Domain

sql

INSERT INTO cf_domains (id, organization_id, name, description, icon, color, sort_order, status)
VALUES (
  'dom_tech_stem_ai',
  'org_demo',
  'Technology, STEM & AI',
  'Core technology, computational thinking, and AI literacy skills for K-12 students',
  'cpu',
  '#6366f1',
  1,
  'active'
);

6.2 Competency Areas (5)

sql

INSERT INTO cf_competency_areas (id, domain_id, organization_id, name, description, sort_order) VALUES
  ('area_programming',   'dom_tech_stem_ai', 'org_demo', 'Programming',            'Core programming constructs and paradigms', 1),
  ('area_data_literacy',  'dom_tech_stem_ai', 'org_demo', 'Data Literacy',           'Data collection, analysis, and visualization', 2),
  ('area_ai_ml',          'dom_tech_stem_ai', 'org_demo', 'AI & Machine Learning',   'Foundational AI/ML concepts and applications', 3),
  ('area_comp_thinking',  'dom_tech_stem_ai', 'org_demo', 'Computational Thinking',  'Problem-solving strategies and algorithmic thinking', 4),
  ('area_math_thinking',  'dom_tech_stem_ai', 'org_demo', 'Mathematical Thinking',   'Mathematical foundations for technology', 5);

6.3 Competencies (15)

sql

INSERT INTO cf_competencies (id, area_id, organization_id, name, description, sort_order) VALUES
  -- Programming (5)
  ('comp_seq_think',      'area_programming',    'org_demo', 'Sequential Thinking',       'Understanding sequential execution of instructions', 1),
  ('comp_ctrl_flow',      'area_programming',    'org_demo', 'Control Flow',              'Managing program flow with conditions and loops', 2),
  ('comp_modularity',     'area_programming',    'org_demo', 'Modularity',                'Organizing code into reusable components', 3),
  -- Data Literacy (3)
  ('comp_data_collect',   'area_data_literacy',  'org_demo', 'Data Collection',           'Gathering and organizing data systematically', 1),
  ('comp_data_analysis',  'area_data_literacy',  'org_demo', 'Data Analysis',             'Interpreting and drawing conclusions from data', 2),
  ('comp_data_viz',       'area_data_literacy',  'org_demo', 'Data Visualization',        'Representing data graphically', 3),
  -- AI & ML (3)
  ('comp_ai_concepts',    'area_ai_ml',          'org_demo', 'AI Concepts',               'Understanding what AI is and how it works', 1),
  ('comp_ml_foundations', 'area_ai_ml',          'org_demo', 'ML Foundations',             'Core machine learning concepts', 2),
  ('comp_ai_ethics',      'area_ai_ml',          'org_demo', 'AI Ethics & Society',       'Responsible AI use and societal impacts', 3),
  -- Computational Thinking (3)
  ('comp_decomposition',  'area_comp_thinking',  'org_demo', 'Decomposition',             'Breaking problems into smaller sub-problems', 1),
  ('comp_pattern_rec',    'area_comp_thinking',  'org_demo', 'Pattern Recognition',       'Identifying patterns and regularities', 2),
  ('comp_abstraction',    'area_comp_thinking',  'org_demo', 'Abstraction',               'Focusing on essential details, ignoring irrelevant', 3),
  -- Mathematical Thinking (3)
  ('comp_number_sense',   'area_math_thinking',  'org_demo', 'Number Sense',              'Understanding numbers, operations, and relationships', 1),
  ('comp_algebraic',      'area_math_thinking',  'org_demo', 'Algebraic Thinking',        'Working with variables, expressions, and equations', 2),
  ('comp_statistics',     'area_math_thinking',  'org_demo', 'Statistical Reasoning',     'Understanding probability and statistical measures', 3);

6.4 Skills (20)

sql

INSERT INTO cf_skills (id, competency_id, organization_id, code, name, definition, taxonomy_level, proficiency_levels, estimated_hours, sort_order) VALUES
  -- Sequential Thinking
  ('sk_variables',     'comp_seq_think',      'org_demo', 'PROG-01', 'Variables & Data Types',     'Declare, assign, and use variables of different types', 'understand', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 1),
  ('sk_operators',     'comp_seq_think',      'org_demo', 'PROG-02', 'Operators & Expressions',    'Use arithmetic, comparison, and logical operators', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 3, 2),
  ('sk_io',            'comp_seq_think',      'org_demo', 'PROG-03', 'Input/Output Operations',    'Read input and display output in programs', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 2, 3),
  -- Control Flow
  ('sk_boolean',       'comp_ctrl_flow',      'org_demo', 'PROG-04', 'Boolean Logic & Conditions', 'Evaluate boolean expressions and use if/else', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 5, 1),
  ('sk_loops',         'comp_ctrl_flow',      'org_demo', 'PROG-05', 'Loop Structures',            'Use for, while, and do-while loops to repeat actions', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 6, 2),
  ('sk_nested_loops',  'comp_ctrl_flow',      'org_demo', 'PROG-06', 'Nested Loops & Complexity',  'Construct and trace nested loop structures', 'analyze', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 3),
  -- Modularity
  ('sk_functions',     'comp_modularity',     'org_demo', 'PROG-07', 'Functions & Parameters',     'Define and call functions with parameters and return values', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 6, 1),
  ('sk_code_reuse',    'comp_modularity',     'org_demo', 'PROG-08', 'Code Reuse & Libraries',     'Import and use external libraries and modules', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 2),
  -- Data Literacy
  ('sk_arrays',        'comp_data_collect',   'org_demo', 'DATA-01', 'Arrays & Lists',             'Store and manipulate ordered collections of data', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 5, 1),
  ('sk_data_structs',  'comp_data_collect',   'org_demo', 'DATA-02', 'Data Structures',            'Use dictionaries, sets, and other structures', 'analyze', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 6, 2),
  ('sk_data_proc',     'comp_data_analysis',  'org_demo', 'DATA-03', 'Data Processing',            'Filter, sort, aggregate, and transform datasets', 'analyze', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 5, 1),
  ('sk_charts',        'comp_data_viz',       'org_demo', 'DATA-04', 'Charts & Graphs',            'Create and interpret bar, line, pie, and scatter charts', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 1),
  -- AI & ML
  ('sk_ai_intro',      'comp_ai_concepts',    'org_demo', 'AI-01',   'Introduction to AI',         'Understand what AI is, its types, and real-world applications', 'understand', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 3, 1),
  ('sk_ml_basics',     'comp_ml_foundations',  'org_demo', 'AI-02',   'ML Fundamentals',            'Understand supervised/unsupervised learning and basic workflow', 'understand', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 6, 1),
  ('sk_ai_ethics',     'comp_ai_ethics',      'org_demo', 'AI-03',   'AI Ethics & Bias',           'Recognize bias in AI systems and discuss ethical implications', 'evaluate', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 3, 1),
  -- Computational Thinking
  ('sk_decomp',        'comp_decomposition',  'org_demo', 'CT-01',   'Problem Decomposition',      'Break complex problems into manageable sub-problems', 'analyze', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 1),
  ('sk_pattern',       'comp_pattern_rec',    'org_demo', 'CT-02',   'Pattern Recognition',        'Identify recurring patterns in data and processes', 'analyze', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 3, 1),
  ('sk_algorithm',     'comp_abstraction',    'org_demo', 'CT-03',   'Algorithm Design',           'Design step-by-step solutions and flowcharts', 'create', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 5, 1),
  -- Mathematical Thinking
  ('sk_number_ops',    'comp_number_sense',   'org_demo', 'MATH-01', 'Number Operations',          'Perform arithmetic operations and understand number properties', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 3, 1),
  ('sk_algebra',       'comp_algebraic',      'org_demo', 'MATH-02', 'Algebraic Expressions',      'Manipulate variables in equations and expressions', 'apply', '[{"level":1,"name":"Novice","mastery_range":[0,0.25]},{"level":2,"name":"Developing","mastery_range":[0.25,0.5]},{"level":3,"name":"Proficient","mastery_range":[0.5,0.75]},{"level":4,"name":"Advanced","mastery_range":[0.75,1]}]', 4, 1);

6.5 Skill Dependencies (35 edges)

sql

INSERT INTO cf_skill_dependencies (id, source_skill_id, target_skill_id, organization_id, relationship_type, required_source_level, strength, confidence, rationale, diagnostic_signals, remediation_candidates, source_type, validation_status) VALUES
  -- Programming foundations
  ('dep_01', 'sk_variables',    'sk_operators',     'org_demo', 'prerequisite', 3, 0.95, 0.90, '{"pedagogical":"Must understand variables before using them in expressions"}', '[{"error_pattern":"Uses undeclared variables in expressions","weight":0.9}]', '[{"activity_type":"practice","description":"Variable declaration drills","estimated_minutes":15}]', 'expert', 'validated'),
  ('dep_02', 'sk_variables',    'sk_io',            'org_demo', 'prerequisite', 2, 0.80, 0.85, '{"pedagogical":"Variables needed to store input values"}', '[]', '[]', 'expert', 'validated'),
  ('dep_03', 'sk_operators',    'sk_boolean',       'org_demo', 'prerequisite', 3, 0.90, 0.88, '{"pedagogical":"Comparison operators are a subset of operators"}', '[{"error_pattern":"Confuses = with ==","weight":0.95}]', '[{"activity_type":"practice","description":"Operator comparison exercises","estimated_minutes":20}]', 'expert', 'validated'),
  ('dep_04', 'sk_variables',    'sk_boolean',       'org_demo', 'prerequisite', 3, 0.85, 0.87, '{"pedagogical":"Conditions evaluate variable states"}', '[{"error_pattern":"Cannot trace variable values in conditions","weight":0.8}]', '[{"activity_type":"practice","description":"Variable tracing through conditionals","estimated_minutes":20}]', 'expert', 'validated'),
  ('dep_05', 'sk_boolean',      'sk_loops',         'org_demo', 'prerequisite', 3, 0.90, 0.92, '{"pedagogical":"Loop conditions require boolean logic"}', '[{"error_pattern":"Infinite loop due to incorrect condition","weight":0.9}]', '[{"activity_type":"practice","description":"Boolean expression evaluation drills","estimated_minutes":25}]', 'expert', 'validated'),
  ('dep_06', 'sk_variables',    'sk_loops',         'org_demo', 'prerequisite', 3, 0.90, 0.90, '{"pedagogical":"Loop counters and accumulators are variables"}', '[{"error_pattern":"Does not initialize loop counter","weight":0.85}]', '[{"activity_type":"practice","description":"Variable tracing with visual debugger","estimated_minutes":20}]', 'expert', 'validated'),
  ('dep_07', 'sk_loops',        'sk_nested_loops',  'org_demo', 'prerequisite', 3, 0.95, 0.93, '{"pedagogical":"Must master single loops before nesting"}', '[{"error_pattern":"Cannot trace inner loop independently","weight":0.9}]', '[{"activity_type":"practice","description":"Single loop mastery exercises","estimated_minutes":30}]', 'expert', 'validated'),
  ('dep_08', 'sk_boolean',      'sk_nested_loops',  'org_demo', 'recommended',  2, 0.60, 0.70, '{"pedagogical":"Complex conditions often appear in nested structures"}', '[]', '[]', 'expert', 'validated'),
  ('dep_09', 'sk_variables',    'sk_functions',     'org_demo', 'prerequisite', 3, 0.85, 0.88, '{"pedagogical":"Parameters and return values are variable concepts"}', '[{"error_pattern":"Confuses local and global scope","weight":0.85}]', '[{"activity_type":"practice","description":"Scope visualization exercises","estimated_minutes":20}]', 'expert', 'validated'),
  ('dep_10', 'sk_loops',        'sk_functions',     'org_demo', 'recommended',  2, 0.50, 0.65, '{"pedagogical":"Functions often contain loop logic"}', '[]', '[]', 'expert', 'validated'),
  ('dep_11', 'sk_functions',    'sk_code_reuse',    'org_demo', 'prerequisite', 3, 0.90, 0.85, '{"pedagogical":"Must understand function abstraction before library usage"}', '[{"error_pattern":"Cannot understand library function signatures","weight":0.8}]', '[{"activity_type":"tutorial","description":"Reading function documentation","estimated_minutes":25}]', 'expert', 'validated'),

  -- Data Literacy chain
  ('dep_12', 'sk_variables',    'sk_arrays',        'org_demo', 'prerequisite', 3, 0.90, 0.90, '{"pedagogical":"Arrays store multiple variable values"}', '[{"error_pattern":"Confuses array index with value","weight":0.85}]', '[{"activity_type":"practice","description":"Array indexing exercises","estimated_minutes":20}]', 'expert', 'validated'),
  ('dep_13', 'sk_loops',        'sk_arrays',        'org_demo', 'prerequisite', 2, 0.80, 0.82, '{"pedagogical":"Loops are essential for array traversal"}', '[{"error_pattern":"Cannot iterate through array elements","weight":0.8}]', '[{"activity_type":"practice","description":"Loop-based array operations","estimated_minutes":25}]', 'expert', 'validated'),
  ('dep_14', 'sk_arrays',       'sk_data_structs',  'org_demo', 'prerequisite', 3, 0.85, 0.80, '{"pedagogical":"Arrays are the simplest data structure, foundation for others"}', '[]', '[]', 'expert', 'validated'),
  ('dep_15', 'sk_arrays',       'sk_data_proc',     'org_demo', 'prerequisite', 3, 0.90, 0.88, '{"pedagogical":"Data processing operates on collections"}', '[]', '[{"activity_type":"project","description":"Process a CSV file into arrays","estimated_minutes":40}]', 'expert', 'validated'),
  ('dep_16', 'sk_loops',        'sk_data_proc',     'org_demo', 'prerequisite', 3, 0.85, 0.85, '{"pedagogical":"Filtering and aggregation require iteration"}', '[]', '[]', 'expert', 'validated'),
  ('dep_17', 'sk_data_proc',    'sk_charts',        'org_demo', 'prerequisite', 2, 0.75, 0.78, '{"pedagogical":"Must process data before visualizing it"}', '[]', '[]', 'expert', 'validated'),
  ('dep_18', 'sk_number_ops',   'sk_charts',        'org_demo', 'recommended',  2, 0.50, 0.60, '{"pedagogical":"Understanding scales and proportions helps with chart reading"}', '[]', '[]', 'expert', 'validated'),

  -- AI & ML prerequisites
  ('dep_19', 'sk_data_proc',    'sk_ml_basics',     'org_demo', 'prerequisite', 3, 0.90, 0.85, '{"pedagogical":"ML requires data preparation skills"}', '[]', '[{"activity_type":"project","description":"Prepare a dataset for ML training","estimated_minutes":45}]', 'expert', 'validated'),
  ('dep_20', 'sk_charts',       'sk_ml_basics',     'org_demo', 'recommended',  2, 0.55, 0.60, '{"pedagogical":"Visualizing model performance is important"}', '[]', '[]', 'expert', 'validated'),
  ('dep_21', 'sk_ai_intro',     'sk_ml_basics',     'org_demo', 'prerequisite', 3, 0.85, 0.90, '{"pedagogical":"Must understand AI landscape before diving into ML"}', '[]', '[]', 'expert', 'validated'),
  ('dep_22', 'sk_pattern',      'sk_ml_basics',     'org_demo', 'prerequisite', 2, 0.70, 0.72, '{"pedagogical":"ML is essentially automated pattern recognition"}', '[]', '[]', 'expert', 'validated'),
  ('dep_23', 'sk_ai_intro',     'sk_ai_ethics',     'org_demo', 'prerequisite', 2, 0.80, 0.88, '{"pedagogical":"Must understand AI capabilities before discussing ethics"}', '[]', '[]', 'expert', 'validated'),
  ('dep_24', 'sk_ml_basics',    'sk_ai_ethics',     'org_demo', 'recommended',  2, 0.60, 0.65, '{"pedagogical":"Understanding ML bias requires ML knowledge"}', '[]', '[]', 'expert', 'validated'),

  -- Computational Thinking connections
  ('dep_25', 'sk_decomp',       'sk_algorithm',     'org_demo', 'prerequisite', 3, 0.90, 0.90, '{"pedagogical":"Algorithm design requires decomposing the problem first"}', '[]', '[{"activity_type":"practice","description":"Decomposition-to-algorithm exercises","estimated_minutes":30}]', 'expert', 'validated'),
  ('dep_26', 'sk_pattern',      'sk_algorithm',     'org_demo', 'prerequisite', 2, 0.75, 0.78, '{"pedagogical":"Pattern recognition informs algorithm strategy selection"}', '[]', '[]', 'expert', 'validated'),
  ('dep_27', 'sk_algorithm',    'sk_functions',     'org_demo', 'corequisite',  2, 0.65, 0.70, '{"pedagogical":"Functions implement algorithmic steps"}', '[]', '[]', 'expert', 'validated'),
  ('dep_28', 'sk_decomp',       'sk_functions',     'org_demo', 'conceptual',   2, 0.55, 0.60, '{"pedagogical":"Decomposed sub-problems map to functions"}', '[]', '[]', 'expert', 'validated'),
  ('dep_29', 'sk_pattern',      'sk_loops',         'org_demo', 'conceptual',   2, 0.50, 0.55, '{"pedagogical":"Recognizing repetition patterns leads to loop constructs"}', '[]', '[]', 'expert', 'validated'),

  -- Mathematical foundations
  ('dep_30', 'sk_number_ops',   'sk_variables',     'org_demo', 'prerequisite', 2, 0.70, 0.75, '{"pedagogical":"Number sense needed for numeric variable operations"}', '[]', '[]', 'expert', 'validated'),
  ('dep_31', 'sk_number_ops',   'sk_operators',     'org_demo', 'prerequisite', 3, 0.85, 0.82, '{"pedagogical":"Arithmetic operations are a subset of programming operators"}', '[]', '[]', 'expert', 'validated'),
  ('dep_32', 'sk_algebra',      'sk_variables',     'org_demo', 'corequisite',  2, 0.75, 0.78, '{"pedagogical":"Algebraic variables and programming variables share concepts"}', '[]', '[]', 'expert', 'validated'),
  ('dep_33', 'sk_algebra',      'sk_boolean',       'org_demo', 'recommended',  2, 0.55, 0.58, '{"pedagogical":"Algebraic equations relate to conditional expressions"}', '[]', '[]', 'expert', 'validated'),
  ('dep_34', 'sk_number_ops',   'sk_data_proc',     'org_demo', 'recommended',  2, 0.50, 0.55, '{"pedagogical":"Aggregation operations use arithmetic"}', '[]', '[]', 'expert', 'validated'),
  ('dep_35', 'sk_decomp',       'sk_data_proc',     'org_demo', 'conceptual',   2, 0.45, 0.50, '{"pedagogical":"Data pipelines require decomposing the transformation steps"}', '[]', '[]', 'expert', 'validated');

6.6 Seed Data Summary

Entity	Count	Notes
Domains	1	Technology, STEM & AI
Competency Areas	5	Programming, Data, AI, CT, Math
Competencies	15	3–5 per area
Skills	20	With full proficiency levels
Indicators	—	To be added per skill (3–5 each)
Dependency Edges	35	Mixed types, fully connected graph
Framework Mappings	—	To be added for ISTE/CSTA alignment

7. Migration Plan

7.1 New Migration File

Create migrations/0008_competency_framework.sql containing all CF table definitions from Section 2.

Migration execution order:

0001_init.sql                      -- existing
0002_evidence.sql                  -- existing
0003_recommendations.sql           -- existing
...
0008_competency_framework.sql      -- NEW: all CF tables + indexes
0009_cf_seed_data.sql              -- NEW: seed data for hackathon demo

7.2 Data Migration Strategy

Map existing curriculum data to the new CF schema:

mermaid

graph LR
    subgraph Existing["Existing Schema"]
        CS["cur_skills"]
        CI["cur_indicators"]
        ELSS["evd_learner_skill_states"]
    end

    subgraph New["CF Schema"]
        CFS["cf_skills<br/>(cur_skill_id FK)"]
        CFI["cf_indicators<br/>(cur_indicator_id FK)"]
        CFSD["cf_skill_dependencies"]
    end

    CS -->|"map via cur_skill_id"| CFS
    CI -->|"map via cur_indicator_id"| CFI
    ELSS -->|"join via cur_skill_id"| CFS

Migration script (data):

sql

-- Step 1: Create CF skills from existing cur_skills
INSERT INTO cf_skills (id, competency_id, organization_id, cur_skill_id, name, definition, taxonomy_level)
SELECT
  'cf_' || cs.id,
  'comp_unassigned',  -- assign to a default competency initially
  cs.organization_id,
  cs.id,
  cs.name,
  cs.description,
  'apply'
FROM cur_skills cs
WHERE cs.status = 'active';

-- Step 2: Create CF indicators from existing cur_indicators
INSERT INTO cf_indicators (id, skill_id, organization_id, cur_indicator_id, name, description, mastery_threshold)
SELECT
  'cf_' || ci.id,
  'cf_' || ci.skill_id,
  ci.organization_id,
  ci.id,
  ci.name,
  ci.description,
  0.7
FROM cur_indicators ci
WHERE ci.status = 'active';

7.3 Backward Compatibility

Aspect	Strategy
Existing `GET /api/skills`	Unchanged; continues to query `cur_skills`
Existing evidence flow	Unchanged; `evd_learner_skill_states` references `cur_skills`
Mastery calculation	Unchanged; Mastery Calculator reads from `evd_*` tables
New CF routes	Additive; mounted at `/api/cf/*`, no conflicts
Recommendation engine	Enhanced with diagnostic reason type; existing reasons preserved
Dashboard	New CF Dashboard tab added; existing tabs unchanged
`cur_skill_id` FK in cf_skills	Nullable; allows CF skills to exist without curriculum mapping

7.4 Rollback Plan

If the CF feature needs to be rolled back:

Remove routes: Delete CF route registrations from the Hono app
Preserve data: CF tables remain in D1 but are unused
No cascade impact: Existing tables have no FK references to CF tables
Recommendation cleanup: Remove rec_recommendations entries where reason->>'source' = 'cf_diagnostic'

Appendix A: Technology Stack

Layer	Technology	Purpose
Runtime	Cloudflare Workers	Serverless edge compute
Framework	Hono	Lightweight HTTP framework
Database	Cloudflare D1 (SQLite)	Structured data storage
Auth	Custom JWT middleware	Organization-scoped authentication
Frontend	Vanilla JS + Web Components	Lightweight, no framework overhead
Graph Viz	vis-network	Interactive force-directed graph
Validation	Zod	Request/response schema validation
Testing	Vitest	Unit and integration tests

Appendix B: Performance Considerations

Operation	Expected Latency	Strategy
Full graph query (20 nodes)	< 50ms	Single CTE query, cached response
BFS prerequisites (depth 6)	< 100ms	Recursive CTE in SQLite
Diagnostic analysis	< 200ms	Graph + mastery queries batched
Cycle detection on edge add	< 50ms	DFS limited to subgraph reachability
Framework tree load	< 100ms	Eager-load full hierarchy in one query

Appendix C: Future Enhancements

AI-Suggested Dependencies: Use student performance data to discover latent prerequisite relationships
Adaptive Proficiency Levels: Machine-learned thresholds based on cohort performance
Multi-Tenant Framework Sharing: Organizations can publish and subscribe to shared frameworks
Real-Time Graph Analytics: Live dashboard showing organization-wide skill coverage heatmaps
External Framework Import: Automated import from CASE (Competency & Academic Standards Exchange) format

Competency Framework & Knowledge Graph — System Design Document ​

EduOne AI Platform | Version 1.0 ​

Table of Contents ​

1. Architecture Overview ​

1.1 System Context ​

1.2 Component Architecture ​

1.3 Key Design Decisions ​

2. Database Schema ​

2.1 Table Definitions ​

cf_domains ​

cf_competency_areas ​

cf_competencies ​

cf_skills ​

cf_indicators ​

cf_skill_dependencies ​

cf_proficiency_levels ​

cf_framework_mappings ​

2.2 Entity Relationship Diagram ​

2.3 Key Indexes Summary ​

3. API Design ​

3.1 Framework Management ​

Domains ​

Competency Areas, Competencies, Skills, Indicators ​

3.2 Dependency Management ​

3.3 Graph Queries ​

3.4 Diagnostic Endpoint ​

3.5 Error Response Format ​

4. Diagnostic Engine Design ​

4.1 Root-Cause Analysis Algorithm ​

4.2 Scoring Formula Breakdown ​

4.3 Graph Traversal Algorithms ​

BFS for Prerequisites (Backward Traversal) ​

Cycle Detection (DFS) ​

Topological Sort for Remediation Ordering ​

4.4 Integration with Recommendation Engine ​

5. Frontend Components ​

5.1 Skill Graph Viewer ​

5.2 Framework Browser ​

5.3 Dependency Editor (Admin) ​

6. Seed Data ​

6.1 Domain ​

6.2 Competency Areas (5) ​

6.3 Competencies (15) ​

6.4 Skills (20) ​

6.5 Skill Dependencies (35 edges) ​

6.6 Seed Data Summary ​

7. Migration Plan ​

7.1 New Migration File ​

7.2 Data Migration Strategy ​

7.3 Backward Compatibility ​

7.4 Rollback Plan ​

Appendix A: Technology Stack ​

Appendix B: Performance Considerations ​

Appendix C: Future Enhancements ​

Competency Framework & Knowledge Graph — System Design Document

EduOne AI Platform | Version 1.0

Table of Contents

1. Architecture Overview

1.1 System Context

1.2 Component Architecture

1.3 Key Design Decisions

2. Database Schema

2.1 Table Definitions

cf_domains

cf_competency_areas

cf_competencies

cf_skills

cf_indicators

cf_skill_dependencies

cf_proficiency_levels

cf_framework_mappings

2.2 Entity Relationship Diagram

2.3 Key Indexes Summary

3. API Design

3.1 Framework Management

Domains

Competency Areas, Competencies, Skills, Indicators

3.2 Dependency Management

3.3 Graph Queries

3.4 Diagnostic Endpoint

3.5 Error Response Format

4. Diagnostic Engine Design

4.1 Root-Cause Analysis Algorithm

4.2 Scoring Formula Breakdown

4.3 Graph Traversal Algorithms

BFS for Prerequisites (Backward Traversal)

Cycle Detection (DFS)

Topological Sort for Remediation Ordering

4.4 Integration with Recommendation Engine

5. Frontend Components

5.1 Skill Graph Viewer

5.2 Framework Browser

5.3 Dependency Editor (Admin)

6. Seed Data

6.1 Domain

6.2 Competency Areas (5)

6.3 Competencies (15)

6.4 Skills (20)

6.5 Skill Dependencies (35 edges)

6.6 Seed Data Summary

7. Migration Plan

7.1 New Migration File

7.2 Data Migration Strategy

7.3 Backward Compatibility

7.4 Rollback Plan

Appendix A: Technology Stack

Appendix B: Performance Considerations

Appendix C: Future Enhancements