Skip to main content

ADR-LMS-PHASE-1: Learning Management System - Onboarding Database Architecture

Status: Accepted Date: 2025-12-11 Deciders: Hal Casteel (Founder/CEO/CTO), CODITECT Core Team Technical Story: Enable persistent tracking of user onboarding progress, badge achievements, and learning analytics integrated with the /cxq anti-forgetting memory system

Context and Problem Statement​

CODITECT provides a comprehensive onboarding experience through the coditect-onboarding agent and /onboard command. However, the original implementation had no persistent storage:

  1. Progress was ephemeral - Users couldn't resume onboarding across sessions
  2. Badges were display-only - Achievement data wasn't persisted or queryable
  3. No learning history - Users couldn't see what they'd completed
  4. No analytics - No insight into user learning patterns
  5. Disconnected from memory system - Onboarding progress wasn't integrated with /cxq

The Problem: How do we make onboarding progress persistent, queryable, and integrated with the existing anti-forgetting memory infrastructure?

Decision Drivers​

Technical Requirements​

  • R1: Persistent storage of learning progress across sessions
  • R2: Integration with existing context.db SQLite database
  • R3: Query capability through existing /cxq command interface
  • R4: Support for 11 onboarding modules with completion tracking
  • R5: Badge earning and achievement system
  • R6: User profile storage for learning personalization
  • R7: Analytics event capture for insights

User Experience Goals​

  • UX1: Users can resume onboarding where they left off
  • UX2: Users can see earned badges and available achievements
  • UX3: Users can query their progress via familiar /cxq commands
  • UX4: New users get recommended next steps based on progress

Integration Constraints​

  • C1: Must use existing context-storage/context.db database
  • C2: Must not break existing FTS5/semantic search functionality
  • C3: Must integrate with existing scripts/context-db.py CLI
  • C4: Must support the existing onboarding workflow

Decision Outcome​

Chosen Solution: Implement LMS as 6 new SQLite tables within context.db, extending /cxq with 7 new command flags for learning queries.

Architecture Components​

1. Database Schema (6 Tables)​

Core Entities:

learning_modules      - Curriculum content definitions (11 onboarding modules)
learning_badges       - Achievement definitions (10 badges)
learning_users        - User profiles and aggregate statistics
learning_progress     - Per-user, per-module completion tracking
learning_achievements - Per-user badge earning records
learning_analytics_events - Event stream for analytics

Key Design Decisions:

  1. INTEGER Primary Keys with TEXT Foreign Keys

    • Internal IDs use INTEGER PRIMARY KEY AUTOINCREMENT
    • User references use TEXT user_id for flexibility
    • Module/badge references use INTEGER IDs for performance

  2. Normalized Schema

    • Modules and badges stored once in definition tables
    • Progress and achievements link users to content
    • Events capture detailed activity stream

  3. Soft Deletes via is_active Flags

    • Modules and badges can be deactivated without deletion
    • Historical data preserved for analytics

2. Query Integration (7 New /cxq Flags)​

/cxq --onboarding            # Combined progress + badges view
/cxq --learning-progress     # Module completion status
/cxq --learning-badges       # Earned and available badges
/cxq --learning-modules      # List all available modules
/cxq --learning-next         # Recommend next module
/cxq --learning-stats        # Learning statistics
/cxq --learning-search QUERY # Search learning content

3. Migration Script (learning-db-migrate.py)​

Responsibilities:

  • Create all 6 tables with proper schema
  • Create 17 indexes for query performance
  • Seed 11 onboarding modules (3 phases)
  • Seed 10 achievement badges
  • Verify migration success

Seeded Content:

PhaseModulesDescription
1 - Getting Started4Welcome, Commands, Agents, Components
2 - Core Workflows4Git, Memory, Sessions, Best Practices
3 - Advanced3Customization, Integration, Graduation
Badge CategoryCountExamples
Onboarding1First Steps
Achievement7Command Explorer, Agent Whisperer, etc.
Specialization1Integration Architect
Milestone1CODITECT Graduate

4. Query Module (learning_db_query.py)​

Functions Provided:

# Data retrieval
get_learning_progress(user_id)     # Module completion status
get_earned_badges(user_id)         # User's earned badges
get_available_badges(user_id)      # Badges not yet earned
get_available_modules()            # All active modules
get_next_module(user_id)           # Recommended next step
get_learning_stats(user_id)        # Aggregate statistics
search_learning_content(query)     # Full-text search

# Display formatting
format_progress_display(progress)  # Human-readable progress
format_badges_display(badges)      # Human-readable badges
format_onboarding_display(user_id) # Combined view
format_stats_display(stats)        # Statistics summary
format_next_display(module)        # Next step recommendation

Implementation Details​

Database Location​

  • Path: context-storage/context.db
  • Shared with: Anti-forgetting memory system (messages, FTS5, embeddings)

Foreign Key Integrity​

  • CASCADE deletes for user-linked progress/achievements
  • SET NULL for optional module/badge references in events
  • UNIQUE constraints prevent duplicate progress/achievement records

Index Strategy​

  • Primary queries indexed: user_id, module_id, status, completed_at
  • Analytics queries indexed: event_type, timestamp, session_id
  • Lookup queries indexed: module_key, badge_key, category

Error Handling​

  • Graceful fallback if LMS tables don't exist
  • Empty results returned for unknown users
  • Schema validation on startup

Alternatives Considered​

Alternative 1: Separate SQLite Database​

Description: Create learning.db separate from context.db

Pros:

  • Clean separation of concerns
  • Independent backup/restore

Cons:

  • Two database connections to manage
  • No integration with existing /cxq infrastructure
  • Additional complexity for users

Decision: Rejected - integration benefits outweigh separation

Alternative 2: JSON File Storage​

Description: Store progress in JSON files like learning-progress.json

Pros:

  • Simple implementation
  • Human-readable storage

Cons:

  • No query capability
  • Race conditions with concurrent writes
  • Limited scalability
  • No relational integrity

Decision: Rejected - query capability essential for /cxq integration

Alternative 3: Full ORM (SQLAlchemy)​

Description: Use SQLAlchemy for database abstraction

Pros:

  • Cleaner Python code
  • Database portability
  • Built-in migration support

Cons:

  • Additional dependency (65MB+ package)
  • Overkill for 6 tables
  • Inconsistent with existing raw SQL approach

Decision: Rejected - raw SQL matches existing codebase patterns

Consequences​

Positive​

  • P1: Users can resume onboarding across sessions
  • P2: Badge achievements persist and are queryable
  • P3: Seamless integration with existing /cxq command
  • P4: Foundation for advanced learning analytics
  • P5: Consistent with anti-forgetting memory architecture

Negative​

  • N1: Additional database tables increase schema complexity
  • N2: Migration must be run to enable LMS features
  • N3: Users must use database-aware /cxq commands

Risks​

  • Risk 1: Schema evolution requires migration scripts
    • Mitigation: Version tracking in migration script
  • Risk 2: Database corruption could lose progress
    • Mitigation: Regular backups via /cx workflow

Validation​

Test Coverage​

  • 40 automated tests in tests/test_learning_management_system.py
  • Schema validation tests for all 6 tables
  • Query function tests for all 7 operations
  • CLI integration tests for /cxq flags
  • Edge case tests for empty/unknown users

Manual Validation​

# Verify migration
python3 scripts/learning-db-migrate.py

# Test queries
/cxq --onboarding
/cxq --learning-progress
/cxq --learning-badges
/cxq --learning-next
/cxq --learning-stats
/cxq --learning-search "agent"

Implementation Checklist​

  • Create migration script (learning-db-migrate.py)
  • Create query module (learning_db_query.py)
  • Extend context-db.py with learning flags
  • Update /cxq command documentation
  • Create schema documentation
  • Implement 40 automated tests
  • Update CHANGELOG with v1.7.3

Last Updated: 2025-12-11 Version: 1.0.0