ADR-LMS-009: Instructor Dashboard & Admin Interface

Status: Proposed Date: 2025-12-11 Phase: Phase 2 - Core LMS Infrastructure Deciders: Hal Casteel (Founder/CEO/CTO), CODITECT Core Team Technical Story: Enable instructors and administrators to manage courses, learners, content, and organizational settings through dedicated interfaces

Context and Problem Statement

The current CODITECT training system lacks instructor and administrative capabilities:

No Course Management - No interface to create, edit, or organize courses
No Learner Management - No ability to enroll, track, or manage learners
No Content Authoring - No integrated content creation tools
No Role Management - No granular permission system for instructors/admins
No Bulk Operations - No way to perform batch actions on users/content
No Organization Management - No multi-tenant administration capabilities

The Problem: How do we empower instructors and administrators to effectively manage the learning environment while maintaining security, auditability, and scalability?

Decision Drivers

Technical Requirements

R1: Role-based access control (RBAC) with granular permissions
R2: Course lifecycle management (draft → published → archived)
R3: Learner enrollment and group management
R4: Content version control and publishing workflow
R5: Bulk import/export operations (CSV, JSON)
R6: Real-time activity monitoring
R7: Audit logging for all administrative actions

User Experience Goals

UX1: Intuitive drag-and-drop course builder
UX2: At-a-glance dashboards for key metrics
UX3: Quick actions for common tasks
UX4: Mobile-responsive design
UX5: Keyboard shortcuts for power users

Business Requirements

B1: Multi-tenant organization support
B2: White-labeling and customization
B3: Usage-based feature gating
B4: Integration with HR/LMS systems

Decision Outcome

Chosen Solution: Implement a comprehensive instructor/admin dashboard system with role-based permissions, course management workflows, learner administration, and organization settings.

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│           Instructor & Admin Dashboard Architecture                  │
├─────────────────────────────────────────────────────────────────────┤
│                                                                       │
│  ┌─────────────────────────────────────────────────────────────┐    │
│  │                      Access Control Layer                    │    │
│  │                                                              │    │
│  │  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐ │    │
│  │  │   Roles     │  │ Permissions │  │  Organization       │ │    │
│  │  │             │  │             │  │  Hierarchy          │ │    │
│  │  │ • Super     │  │ • course.*  │  │                     │ │    │
│  │  │   Admin     │  │ • user.*    │  │  Org Admin          │ │    │
│  │  │ • Org       │  │ • content.* │  │    └─ Instructor    │ │    │
│  │  │   Admin     │  │ • report.*  │  │        └─ TA        │ │    │
│  │  │ • Instructor│  │ • org.*     │  │            └─Learner│ │    │
│  │  │ • TA        │  │ • admin.*   │  │                     │ │    │
│  │  └─────────────┘  └─────────────┘  └─────────────────────┘ │    │
│  └─────────────────────────────────────────────────────────────┘    │
│                               │                                      │
│                               ▼                                      │
│  ┌─────────────────────────────────────────────────────────────┐    │
│  │                    Dashboard Modules                         │    │
│  │                                                              │    │
│  │  ┌───────────────────────────────────────────────────────┐  │    │
│  │  │              Course Management Module                  │  │    │
│  │  │                                                        │  │    │
│  │  │  • Course Builder (drag-and-drop)                     │  │    │
│  │  │  • Module/Lesson Organization                          │  │    │
│  │  │  • Content Version Control                             │  │    │
│  │  │  • Publishing Workflow                                 │  │    │
│  │  │  • Course Templates                                    │  │    │
│  │  └───────────────────────────────────────────────────────┘  │    │
│  │                                                              │    │
│  │  ┌───────────────────────────────────────────────────────┐  │    │
│  │  │              Learner Management Module                 │  │    │
│  │  │                                                        │  │    │
│  │  │  • User Enrollment                                     │  │    │
│  │  │  • Group Management                                    │  │    │
│  │  │  • Progress Monitoring                                 │  │    │
│  │  │  • Manual Grade Override                               │  │    │
│  │  │  • Learner Communication                               │  │    │
│  │  └───────────────────────────────────────────────────────┘  │    │
│  │                                                              │    │
│  │  ┌───────────────────────────────────────────────────────┐  │    │
│  │  │              Organization Admin Module                 │  │    │
│  │  │                                                        │  │    │
│  │  │  • User Role Management                                │  │    │
│  │  │  • Organization Settings                               │  │    │
│  │  │  • Branding/White-label                               │  │    │
│  │  │  • Integration Configuration                           │  │    │
│  │  │  • Billing & Usage                                     │  │    │
│  │  └───────────────────────────────────────────────────────┘  │    │
│  └─────────────────────────────────────────────────────────────┘    │
│                               │                                      │
│                               ▼                                      │
│  ┌─────────────────────────────────────────────────────────────┐    │
│  │                      Data Layer                              │    │
│  │                                                              │    │
│  │  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐ │    │
│  │  │  Courses    │  │  Users      │  │  Organizations      │ │    │
│  │  │  Content    │  │  Groups     │  │  Settings           │ │    │
│  │  │  Versions   │  │  Enrollment │  │  Integrations       │ │    │
│  │  └─────────────┘  └─────────────┘  └─────────────────────┘ │    │
│  └─────────────────────────────────────────────────────────────┘    │
│                                                                       │
└─────────────────────────────────────────────────────────────────────┘

Database Schema

New Tables

-- Course Management
CREATE TABLE IF NOT EXISTS courses (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    organization_id TEXT,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    thumbnail_url TEXT,
    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'review', 'published', 'archived')),
    visibility TEXT DEFAULT 'private' CHECK(visibility IN ('private', 'organization', 'public')),
    created_by TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    published_at TEXT,
    archived_at TEXT,
    metadata JSON DEFAULT '{}',
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (created_by) REFERENCES auth_users(id)
);

-- Course Versions (for content versioning)
CREATE TABLE IF NOT EXISTS course_versions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    course_id TEXT NOT NULL,
    version_number INTEGER NOT NULL,
    content_snapshot JSON NOT NULL,
    change_summary TEXT,
    created_by TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    is_published INTEGER DEFAULT 0,
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (created_by) REFERENCES auth_users(id),
    UNIQUE(course_id, version_number)
);

-- Course Modules (sections within a course)
CREATE TABLE IF NOT EXISTS course_modules (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    course_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    position INTEGER NOT NULL,
    unlock_after_module_id TEXT,
    unlock_after_days INTEGER,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (unlock_after_module_id) REFERENCES course_modules(id)
);

-- Course Lessons (content within modules)
CREATE TABLE IF NOT EXISTS course_lessons (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    module_id TEXT NOT NULL,
    title TEXT NOT NULL,
    content_type TEXT NOT NULL CHECK(content_type IN ('markdown', 'video', 'quiz', 'assignment', 'scorm', 'external_url')),
    content_data JSON NOT NULL,
    position INTEGER NOT NULL,
    duration_minutes INTEGER,
    is_required INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (module_id) REFERENCES course_modules(id)
);

-- Learner Groups
CREATE TABLE IF NOT EXISTS learner_groups (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    organization_id TEXT,
    name TEXT NOT NULL,
    description TEXT,
    created_by TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    metadata JSON DEFAULT '{}',
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (created_by) REFERENCES auth_users(id)
);

-- Group Memberships
CREATE TABLE IF NOT EXISTS group_memberships (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    group_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    added_by TEXT NOT NULL,
    added_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (group_id) REFERENCES learner_groups(id),
    FOREIGN KEY (user_id) REFERENCES auth_users(id),
    FOREIGN KEY (added_by) REFERENCES auth_users(id),
    UNIQUE(group_id, user_id)
);

-- Course Enrollments
CREATE TABLE IF NOT EXISTS course_enrollments (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    course_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    enrolled_by TEXT,
    enrollment_type TEXT DEFAULT 'manual' CHECK(enrollment_type IN ('manual', 'self', 'group', 'integration')),
    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'completed', 'dropped', 'expired')),
    enrolled_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT,
    expires_at TEXT,
    progress_percent REAL DEFAULT 0,
    last_activity_at TEXT,
    grade_override REAL,
    grade_override_by TEXT,
    grade_override_reason TEXT,
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (user_id) REFERENCES auth_users(id),
    FOREIGN KEY (enrolled_by) REFERENCES auth_users(id),
    FOREIGN KEY (grade_override_by) REFERENCES auth_users(id),
    UNIQUE(course_id, user_id)
);

-- Organization Settings
CREATE TABLE IF NOT EXISTS organizations (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    domain TEXT,
    logo_url TEXT,
    primary_color TEXT DEFAULT '#3B82F6',
    settings JSON DEFAULT '{}',
    features JSON DEFAULT '[]',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    subscription_tier TEXT DEFAULT 'free' CHECK(subscription_tier IN ('free', 'pro', 'enterprise')),
    subscription_expires_at TEXT
);

-- Organization Members
CREATE TABLE IF NOT EXISTS organization_members (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    organization_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('owner', 'admin', 'instructor', 'ta', 'learner')),
    invited_by TEXT,
    joined_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (user_id) REFERENCES auth_users(id),
    FOREIGN KEY (invited_by) REFERENCES auth_users(id),
    UNIQUE(organization_id, user_id)
);

-- Permission Definitions
CREATE TABLE IF NOT EXISTS permissions (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    category TEXT NOT NULL
);

-- Role Permissions (which permissions each role has)
CREATE TABLE IF NOT EXISTS role_permissions (
    role TEXT NOT NULL,
    permission_id TEXT NOT NULL,
    PRIMARY KEY (role, permission_id),
    FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

-- Admin Audit Log
CREATE TABLE IF NOT EXISTS admin_audit_log (
    id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
    organization_id TEXT,
    user_id TEXT NOT NULL,
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id TEXT,
    old_values JSON,
    new_values JSON,
    ip_address TEXT,
    user_agent TEXT,
    timestamp TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (user_id) REFERENCES auth_users(id)
);

-- Indexes for Performance
CREATE INDEX IF NOT EXISTS idx_courses_organization ON courses(organization_id);
CREATE INDEX IF NOT EXISTS idx_courses_status ON courses(status);
CREATE INDEX IF NOT EXISTS idx_course_enrollments_user ON course_enrollments(user_id);
CREATE INDEX IF NOT EXISTS idx_course_enrollments_course ON course_enrollments(course_id);
CREATE INDEX IF NOT EXISTS idx_organization_members_user ON organization_members(user_id);
CREATE INDEX IF NOT EXISTS idx_admin_audit_log_org ON admin_audit_log(organization_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_admin_audit_log_user ON admin_audit_log(user_id, timestamp);

Default Permissions

-- Insert default permissions
INSERT OR IGNORE INTO permissions (id, name, description, category) VALUES
    -- Course permissions
    ('p001', 'course.create', 'Create new courses', 'courses'),
    ('p002', 'course.read', 'View course content', 'courses'),
    ('p003', 'course.update', 'Edit course content', 'courses'),
    ('p004', 'course.delete', 'Delete courses', 'courses'),
    ('p005', 'course.publish', 'Publish/unpublish courses', 'courses'),
    ('p006', 'course.archive', 'Archive courses', 'courses'),

    -- User management permissions
    ('p010', 'user.list', 'List users', 'users'),
    ('p011', 'user.view', 'View user details', 'users'),
    ('p012', 'user.create', 'Create users', 'users'),
    ('p013', 'user.update', 'Update user details', 'users'),
    ('p014', 'user.delete', 'Delete users', 'users'),
    ('p015', 'user.impersonate', 'Impersonate users', 'users'),

    -- Enrollment permissions
    ('p020', 'enrollment.view', 'View enrollments', 'enrollments'),
    ('p021', 'enrollment.create', 'Enroll users', 'enrollments'),
    ('p022', 'enrollment.update', 'Update enrollments', 'enrollments'),
    ('p023', 'enrollment.delete', 'Remove enrollments', 'enrollments'),
    ('p024', 'enrollment.grade_override', 'Override grades', 'enrollments'),

    -- Content permissions
    ('p030', 'content.create', 'Create content', 'content'),
    ('p031', 'content.update', 'Update content', 'content'),
    ('p032', 'content.delete', 'Delete content', 'content'),
    ('p033', 'content.version', 'Manage content versions', 'content'),

    -- Report permissions
    ('p040', 'report.view_own', 'View own reports', 'reports'),
    ('p041', 'report.view_team', 'View team reports', 'reports'),
    ('p042', 'report.view_org', 'View organization reports', 'reports'),
    ('p043', 'report.export', 'Export reports', 'reports'),

    -- Organization permissions
    ('p050', 'org.settings', 'Manage organization settings', 'organization'),
    ('p051', 'org.branding', 'Manage branding', 'organization'),
    ('p052', 'org.integrations', 'Manage integrations', 'organization'),
    ('p053', 'org.billing', 'Manage billing', 'organization'),

    -- Admin permissions
    ('p060', 'admin.audit_log', 'View audit logs', 'admin'),
    ('p061', 'admin.system', 'System administration', 'admin');

-- Assign permissions to roles
-- Owner (all permissions)
INSERT OR IGNORE INTO role_permissions (role, permission_id)
SELECT 'owner', id FROM permissions;

-- Admin (all except billing and system)
INSERT OR IGNORE INTO role_permissions (role, permission_id)
SELECT 'admin', id FROM permissions WHERE name NOT IN ('org.billing', 'admin.system');

-- Instructor (course and enrollment management)
INSERT OR IGNORE INTO role_permissions (role, permission_id)
SELECT 'instructor', id FROM permissions
WHERE category IN ('courses', 'content', 'enrollments')
   OR name IN ('user.list', 'user.view', 'report.view_own', 'report.view_team');

-- TA (limited course assistance)
INSERT OR IGNORE INTO role_permissions (role, permission_id)
SELECT 'ta', id FROM permissions
WHERE name IN ('course.read', 'enrollment.view', 'user.list', 'user.view', 'report.view_own');

-- Learner (basic access)
INSERT OR IGNORE INTO role_permissions (role, permission_id)
SELECT 'learner', id FROM permissions
WHERE name IN ('course.read', 'report.view_own');

Implementation

Course Management Service

# coditect_lms/services/course_service.py
"""
Course Management Service for CODITECT LMS.
Handles course lifecycle, versioning, and publishing workflow.
"""

import sqlite3
import json
from datetime import datetime
from typing import Optional, List, Dict, Any
from dataclasses import dataclass, asdict
from enum import Enum

class CourseStatus(Enum):
    DRAFT = "draft"
    REVIEW = "review"
    PUBLISHED = "published"
    ARCHIVED = "archived"

class Visibility(Enum):
    PRIVATE = "private"
    ORGANIZATION = "organization"
    PUBLIC = "public"

@dataclass
class Course:
    id: str
    title: str
    slug: str
    description: Optional[str]
    status: CourseStatus
    visibility: Visibility
    organization_id: Optional[str]
    created_by: str
    created_at: str
    updated_at: str
    published_at: Optional[str]
    metadata: Dict[str, Any]

class CourseService:
    def __init__(self, db_path: str = "~/.coditect/lms.db"):
        import os
        self.db_path = os.path.expanduser(db_path)

    def _get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn

    def create_course(
        self,
        title: str,
        created_by: str,
        description: str = None,
        organization_id: str = None,
        metadata: Dict[str, Any] = None
    ) -> Course:
        """Create a new course in draft status."""
        import secrets
        import re

        course_id = secrets.token_hex(16)
        # Generate slug from title
        slug = re.sub(r'[^a-z0-9]+', '-', title.lower()).strip('-')
        # Ensure uniqueness
        slug = f"{slug}-{secrets.token_hex(4)}"

        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO courses (
                    id, organization_id, title, slug, description,
                    status, visibility, created_by, metadata
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                course_id,
                organization_id,
                title,
                slug,
                description,
                CourseStatus.DRAFT.value,
                Visibility.PRIVATE.value,
                created_by,
                json.dumps(metadata or {})
            ))

            # Create initial version
            cursor.execute("""
                INSERT INTO course_versions (
                    id, course_id, version_number, content_snapshot,
                    change_summary, created_by
                ) VALUES (?, ?, ?, ?, ?, ?)
            """, (
                secrets.token_hex(16),
                course_id,
                1,
                json.dumps({"modules": [], "settings": {}}),
                "Initial version",
                created_by
            ))

            # Log action
            self._log_action(
                cursor, organization_id, created_by,
                "course.create", "course", course_id,
                None, {"title": title, "slug": slug}
            )

            conn.commit()
            return self.get_course(course_id)
        finally:
            conn.close()

    def get_course(self, course_id: str) -> Optional[Course]:
        """Retrieve a course by ID."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM courses WHERE id = ?", (course_id,))
            row = cursor.fetchone()
            if row:
                return Course(
                    id=row['id'],
                    title=row['title'],
                    slug=row['slug'],
                    description=row['description'],
                    status=CourseStatus(row['status']),
                    visibility=Visibility(row['visibility']),
                    organization_id=row['organization_id'],
                    created_by=row['created_by'],
                    created_at=row['created_at'],
                    updated_at=row['updated_at'],
                    published_at=row['published_at'],
                    metadata=json.loads(row['metadata'] or '{}')
                )
            return None
        finally:
            conn.close()

    def update_course(
        self,
        course_id: str,
        updated_by: str,
        **updates
    ) -> Course:
        """Update course metadata."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            # Get current values for audit
            cursor.execute("SELECT * FROM courses WHERE id = ?", (course_id,))
            old_row = dict(cursor.fetchone())

            # Build update query
            allowed_fields = ['title', 'description', 'thumbnail_url', 'visibility', 'metadata']
            set_clauses = []
            values = []

            for field, value in updates.items():
                if field in allowed_fields:
                    set_clauses.append(f"{field} = ?")
                    if field == 'metadata':
                        values.append(json.dumps(value))
                    else:
                        values.append(value)

            if set_clauses:
                set_clauses.append("updated_at = datetime('now')")
                values.append(course_id)

                cursor.execute(f"""
                    UPDATE courses SET {', '.join(set_clauses)}
                    WHERE id = ?
                """, values)

                # Log action
                self._log_action(
                    cursor, old_row['organization_id'], updated_by,
                    "course.update", "course", course_id,
                    {k: old_row[k] for k in updates.keys() if k in old_row},
                    updates
                )

                conn.commit()

            return self.get_course(course_id)
        finally:
            conn.close()

    def publish_course(
        self,
        course_id: str,
        published_by: str
    ) -> Course:
        """Publish a course (requires review or draft status)."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            # Get current status
            cursor.execute("SELECT status, organization_id FROM courses WHERE id = ?", (course_id,))
            row = cursor.fetchone()

            if row['status'] not in ['draft', 'review']:
                raise ValueError(f"Cannot publish course in {row['status']} status")

            # Update status
            cursor.execute("""
                UPDATE courses
                SET status = 'published',
                    published_at = datetime('now'),
                    updated_at = datetime('now')
                WHERE id = ?
            """, (course_id,))

            # Mark current version as published
            cursor.execute("""
                UPDATE course_versions
                SET is_published = 1
                WHERE course_id = ?
                AND version_number = (
                    SELECT MAX(version_number) FROM course_versions WHERE course_id = ?
                )
            """, (course_id, course_id))

            # Log action
            self._log_action(
                cursor, row['organization_id'], published_by,
                "course.publish", "course", course_id,
                {"status": row['status']}, {"status": "published"}
            )

            conn.commit()
            return self.get_course(course_id)
        finally:
            conn.close()

    def archive_course(
        self,
        course_id: str,
        archived_by: str
    ) -> Course:
        """Archive a published course."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            cursor.execute("SELECT status, organization_id FROM courses WHERE id = ?", (course_id,))
            row = cursor.fetchone()

            if row['status'] != 'published':
                raise ValueError("Only published courses can be archived")

            cursor.execute("""
                UPDATE courses
                SET status = 'archived',
                    archived_at = datetime('now'),
                    updated_at = datetime('now')
                WHERE id = ?
            """, (course_id,))

            self._log_action(
                cursor, row['organization_id'], archived_by,
                "course.archive", "course", course_id,
                {"status": "published"}, {"status": "archived"}
            )

            conn.commit()
            return self.get_course(course_id)
        finally:
            conn.close()

    def list_courses(
        self,
        organization_id: str = None,
        status: CourseStatus = None,
        created_by: str = None,
        limit: int = 50,
        offset: int = 0
    ) -> List[Course]:
        """List courses with filters."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            query = "SELECT * FROM courses WHERE 1=1"
            params = []

            if organization_id:
                query += " AND organization_id = ?"
                params.append(organization_id)
            if status:
                query += " AND status = ?"
                params.append(status.value)
            if created_by:
                query += " AND created_by = ?"
                params.append(created_by)

            query += " ORDER BY updated_at DESC LIMIT ? OFFSET ?"
            params.extend([limit, offset])

            cursor.execute(query, params)

            courses = []
            for row in cursor.fetchall():
                courses.append(Course(
                    id=row['id'],
                    title=row['title'],
                    slug=row['slug'],
                    description=row['description'],
                    status=CourseStatus(row['status']),
                    visibility=Visibility(row['visibility']),
                    organization_id=row['organization_id'],
                    created_by=row['created_by'],
                    created_at=row['created_at'],
                    updated_at=row['updated_at'],
                    published_at=row['published_at'],
                    metadata=json.loads(row['metadata'] or '{}')
                ))

            return courses
        finally:
            conn.close()

    def _log_action(
        self,
        cursor,
        organization_id: str,
        user_id: str,
        action: str,
        resource_type: str,
        resource_id: str,
        old_values: Dict,
        new_values: Dict
    ):
        """Log administrative action to audit log."""
        import secrets
        cursor.execute("""
            INSERT INTO admin_audit_log (
                id, organization_id, user_id, action,
                resource_type, resource_id, old_values, new_values
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            secrets.token_hex(16),
            organization_id,
            user_id,
            action,
            resource_type,
            resource_id,
            json.dumps(old_values) if old_values else None,
            json.dumps(new_values) if new_values else None
        ))

Enrollment Management Service

# coditect_lms/services/enrollment_service.py
"""
Enrollment Management Service for CODITECT LMS.
Handles learner enrollment, progress tracking, and grade management.
"""

import sqlite3
import json
from datetime import datetime
from typing import Optional, List, Dict, Any
from dataclasses import dataclass
from enum import Enum

class EnrollmentStatus(Enum):
    ACTIVE = "active"
    COMPLETED = "completed"
    DROPPED = "dropped"
    EXPIRED = "expired"

class EnrollmentType(Enum):
    MANUAL = "manual"
    SELF = "self"
    GROUP = "group"
    INTEGRATION = "integration"

@dataclass
class Enrollment:
    id: str
    course_id: str
    user_id: str
    status: EnrollmentStatus
    enrollment_type: EnrollmentType
    enrolled_at: str
    completed_at: Optional[str]
    progress_percent: float
    grade_override: Optional[float]

class EnrollmentService:
    def __init__(self, db_path: str = "~/.coditect/lms.db"):
        import os
        self.db_path = os.path.expanduser(db_path)

    def _get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn

    def enroll_user(
        self,
        course_id: str,
        user_id: str,
        enrolled_by: str,
        enrollment_type: EnrollmentType = EnrollmentType.MANUAL,
        expires_at: str = None
    ) -> Enrollment:
        """Enroll a user in a course."""
        import secrets

        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            # Check course exists and is published
            cursor.execute("SELECT status FROM courses WHERE id = ?", (course_id,))
            course = cursor.fetchone()
            if not course or course['status'] != 'published':
                raise ValueError("Course not available for enrollment")

            enrollment_id = secrets.token_hex(16)

            cursor.execute("""
                INSERT INTO course_enrollments (
                    id, course_id, user_id, enrolled_by,
                    enrollment_type, expires_at
                ) VALUES (?, ?, ?, ?, ?, ?)
            """, (
                enrollment_id,
                course_id,
                user_id,
                enrolled_by,
                enrollment_type.value,
                expires_at
            ))

            conn.commit()
            return self.get_enrollment(enrollment_id)
        except sqlite3.IntegrityError:
            raise ValueError("User already enrolled in this course")
        finally:
            conn.close()

    def bulk_enroll(
        self,
        course_id: str,
        user_ids: List[str],
        enrolled_by: str,
        enrollment_type: EnrollmentType = EnrollmentType.MANUAL
    ) -> Dict[str, Any]:
        """Bulk enroll multiple users in a course."""
        results = {
            "success": [],
            "failed": [],
            "already_enrolled": []
        }

        for user_id in user_ids:
            try:
                enrollment = self.enroll_user(
                    course_id, user_id, enrolled_by, enrollment_type
                )
                results["success"].append({
                    "user_id": user_id,
                    "enrollment_id": enrollment.id
                })
            except ValueError as e:
                if "already enrolled" in str(e):
                    results["already_enrolled"].append(user_id)
                else:
                    results["failed"].append({
                        "user_id": user_id,
                        "error": str(e)
                    })

        return results

    def enroll_group(
        self,
        course_id: str,
        group_id: str,
        enrolled_by: str
    ) -> Dict[str, Any]:
        """Enroll all members of a group in a course."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT user_id FROM group_memberships WHERE group_id = ?",
                (group_id,)
            )
            user_ids = [row['user_id'] for row in cursor.fetchall()]

            return self.bulk_enroll(
                course_id, user_ids, enrolled_by,
                EnrollmentType.GROUP
            )
        finally:
            conn.close()

    def get_enrollment(self, enrollment_id: str) -> Optional[Enrollment]:
        """Get enrollment by ID."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT * FROM course_enrollments WHERE id = ?",
                (enrollment_id,)
            )
            row = cursor.fetchone()
            if row:
                return Enrollment(
                    id=row['id'],
                    course_id=row['course_id'],
                    user_id=row['user_id'],
                    status=EnrollmentStatus(row['status']),
                    enrollment_type=EnrollmentType(row['enrollment_type']),
                    enrolled_at=row['enrolled_at'],
                    completed_at=row['completed_at'],
                    progress_percent=row['progress_percent'],
                    grade_override=row['grade_override']
                )
            return None
        finally:
            conn.close()

    def update_progress(
        self,
        course_id: str,
        user_id: str,
        progress_percent: float
    ):
        """Update learner progress in a course."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE course_enrollments
                SET progress_percent = ?,
                    last_activity_at = datetime('now')
                WHERE course_id = ? AND user_id = ?
            """, (min(100, max(0, progress_percent)), course_id, user_id))

            # Auto-complete if 100%
            if progress_percent >= 100:
                cursor.execute("""
                    UPDATE course_enrollments
                    SET status = 'completed',
                        completed_at = datetime('now')
                    WHERE course_id = ? AND user_id = ?
                    AND status = 'active'
                """, (course_id, user_id))

            conn.commit()
        finally:
            conn.close()

    def override_grade(
        self,
        enrollment_id: str,
        grade: float,
        override_by: str,
        reason: str
    ):
        """Manually override a learner's grade."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE course_enrollments
                SET grade_override = ?,
                    grade_override_by = ?,
                    grade_override_reason = ?
                WHERE id = ?
            """, (grade, override_by, reason, enrollment_id))

            # Get org_id for audit log
            cursor.execute("""
                SELECT c.organization_id
                FROM course_enrollments e
                JOIN courses c ON e.course_id = c.id
                WHERE e.id = ?
            """, (enrollment_id,))
            row = cursor.fetchone()

            # Log action
            import secrets
            cursor.execute("""
                INSERT INTO admin_audit_log (
                    id, organization_id, user_id, action,
                    resource_type, resource_id, new_values
                ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """, (
                secrets.token_hex(16),
                row['organization_id'] if row else None,
                override_by,
                "enrollment.grade_override",
                "enrollment",
                enrollment_id,
                json.dumps({"grade": grade, "reason": reason})
            ))

            conn.commit()
        finally:
            conn.close()

    def get_course_enrollments(
        self,
        course_id: str,
        status: EnrollmentStatus = None,
        limit: int = 100,
        offset: int = 0
    ) -> List[Dict[str, Any]]:
        """Get all enrollments for a course with user details."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            query = """
                SELECT e.*, u.email, u.display_name
                FROM course_enrollments e
                JOIN auth_users u ON e.user_id = u.id
                WHERE e.course_id = ?
            """
            params = [course_id]

            if status:
                query += " AND e.status = ?"
                params.append(status.value)

            query += " ORDER BY e.enrolled_at DESC LIMIT ? OFFSET ?"
            params.extend([limit, offset])

            cursor.execute(query, params)

            return [dict(row) for row in cursor.fetchall()]
        finally:
            conn.close()

Permission Checking Service

# coditect_lms/services/permission_service.py
"""
Permission Service for CODITECT LMS.
Handles role-based access control and permission verification.
"""

import sqlite3
from typing import Set, Optional
from functools import lru_cache

class PermissionService:
    def __init__(self, db_path: str = "~/.coditect/lms.db"):
        import os
        self.db_path = os.path.expanduser(db_path)
        self._permission_cache = {}

    def _get_connection(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn

    def get_user_role(
        self,
        user_id: str,
        organization_id: str
    ) -> Optional[str]:
        """Get user's role in an organization."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT role FROM organization_members
                WHERE user_id = ? AND organization_id = ?
            """, (user_id, organization_id))
            row = cursor.fetchone()
            return row['role'] if row else None
        finally:
            conn.close()

    def get_role_permissions(self, role: str) -> Set[str]:
        """Get all permissions for a role."""
        if role in self._permission_cache:
            return self._permission_cache[role]

        conn = self._get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT p.name FROM permissions p
                JOIN role_permissions rp ON p.id = rp.permission_id
                WHERE rp.role = ?
            """, (role,))

            permissions = {row['name'] for row in cursor.fetchall()}
            self._permission_cache[role] = permissions
            return permissions
        finally:
            conn.close()

    def has_permission(
        self,
        user_id: str,
        organization_id: str,
        permission: str
    ) -> bool:
        """Check if user has a specific permission in an organization."""
        role = self.get_user_role(user_id, organization_id)
        if not role:
            return False

        permissions = self.get_role_permissions(role)

        # Check exact permission
        if permission in permissions:
            return True

        # Check wildcard permission (e.g., course.* grants course.read)
        category = permission.split('.')[0]
        if f"{category}.*" in permissions:
            return True

        return False

    def require_permission(
        self,
        user_id: str,
        organization_id: str,
        permission: str
    ):
        """Raise exception if user lacks permission."""
        if not self.has_permission(user_id, organization_id, permission):
            raise PermissionError(
                f"User lacks required permission: {permission}"
            )

    def get_user_permissions(
        self,
        user_id: str,
        organization_id: str
    ) -> Set[str]:
        """Get all permissions a user has in an organization."""
        role = self.get_user_role(user_id, organization_id)
        if not role:
            return set()
        return self.get_role_permissions(role)

    def can_access_course(
        self,
        user_id: str,
        course_id: str
    ) -> bool:
        """Check if user can access a specific course."""
        conn = self._get_connection()
        try:
            cursor = conn.cursor()

            # Get course details
            cursor.execute("""
                SELECT organization_id, visibility, created_by
                FROM courses WHERE id = ?
            """, (course_id,))
            course = cursor.fetchone()

            if not course:
                return False

            # Creator always has access
            if course['created_by'] == user_id:
                return True

            # Public courses
            if course['visibility'] == 'public':
                return True

            # Organization courses
            if course['visibility'] == 'organization':
                role = self.get_user_role(user_id, course['organization_id'])
                return role is not None

            # Private courses - check enrollment or instructor role
            cursor.execute("""
                SELECT 1 FROM course_enrollments
                WHERE course_id = ? AND user_id = ?
            """, (course_id, user_id))
            if cursor.fetchone():
                return True

            # Check if instructor/admin
            return self.has_permission(
                user_id, course['organization_id'], 'course.read'
            )
        finally:
            conn.close()

CLI Commands

Course Management Commands

# Create a new course
/course create "Introduction to CODITECT" --org my-org

# Update course details
/course update <course-id> --title "New Title" --description "Updated description"

# View course details
/course view <course-id>

# List courses
/course list --org my-org --status draft
/course list --mine

# Publish course
/course publish <course-id>

# Archive course
/course archive <course-id>

# Add module to course
/course add-module <course-id> "Module 1: Getting Started"

# Add lesson to module
/course add-lesson <module-id> --type markdown --title "Welcome" --file welcome.md
/course add-lesson <module-id> --type quiz --title "Knowledge Check" --quiz <quiz-id>
/course add-lesson <module-id> --type video --title "Demo" --url https://...

# Reorder modules/lessons
/course reorder <course-id> --module <module-id> --position 2

Enrollment Management Commands

# Enroll user(s)
/enroll user <course-id> <user-email>
/enroll users <course-id> --file users.csv
/enroll group <course-id> <group-id>

# View enrollments
/enroll list <course-id>
/enroll list <course-id> --status completed

# Override grade
/enroll grade <enrollment-id> --score 95 --reason "Manual adjustment per appeal"

# Remove enrollment
/enroll drop <enrollment-id> --reason "Student request"

Group Management Commands

# Create group
/group create "Fall 2025 Cohort" --org my-org

# Add/remove members
/group add <group-id> user@example.com
/group add <group-id> --file members.csv
/group remove <group-id> user@example.com

# List groups
/group list --org my-org

# View group members
/group members <group-id>

Organization Management Commands

# View organization
/org view my-org

# Update settings
/org settings my-org --primary-color "#3B82F6"
/org settings my-org --logo-url "https://..."

# Manage members
/org members my-org
/org invite my-org user@example.com --role instructor
/org role my-org user@example.com --set admin
/org remove my-org user@example.com

# View audit log
/org audit my-org --last 7d
/org audit my-org --user user@example.com --action course.*

Dashboard Views

Instructor Dashboard

┌─────────────────────────────────────────────────────────────────────┐
│  Instructor Dashboard                                     [Settings]│
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  Quick Stats                                                         │
│  ┌────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐       │
│  │ Active     │ │ Total      │ │ Avg        │ │ Pending    │       │
│  │ Courses    │ │ Learners   │ │ Progress   │ │ Grades     │       │
│  │     5      │ │    127     │ │    68%     │ │     12     │       │
│  └────────────┘ └────────────┘ └────────────┘ └────────────┘       │
│                                                                      │
│  My Courses                                       [+ Create Course] │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │ Course Name               │ Status   │ Learners │ Progress  │   │
│  ├───────────────────────────┼──────────┼──────────┼───────────┤   │
│  │ CODITECT Fundamentals     │ Published│    45    │    72%    │   │
│  │ Advanced Agent Patterns   │ Published│    32    │    58%    │   │
│  │ Security Best Practices   │ Draft    │     -    │     -     │   │
│  │ LMS Administration        │ Review   │     -    │     -     │   │
│  │ Custom Integrations       │ Published│    50    │    81%    │   │
│  └─────────────────────────────────────────────────────────────┘   │
│                                                                      │
│  Recent Activity                                                     │
│  • 3 learners completed "CODITECT Fundamentals" today               │
│  • 12 new quiz submissions need review                               │
│  • "Advanced Agent Patterns" certificate requests: 5                 │
│                                                                      │
│  Learner Spotlight (Needs Attention)                                │
│  • John D. - Stuck on Module 3 for 7 days                           │
│  • Sarah M. - Quiz score dropped below passing                       │
│  • Team Alpha - 3 members behind schedule                            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Organization Admin Dashboard

┌─────────────────────────────────────────────────────────────────────┐
│  Organization Admin: AZ1.AI                              [Settings] │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  Organization Overview                                               │
│  ┌────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐       │
│  │ Team       │ │ Active     │ │ Completion │ │ Certificates│       │
│  │ Members    │ │ Courses    │ │ Rate       │ │ Issued      │       │
│  │    156     │ │     12     │ │    73%     │ │     89      │       │
│  └────────────┘ └────────────┘ └────────────┘ └────────────┘       │
│                                                                      │
│  Subscription: Enterprise (expires Dec 2026)                         │
│  API Calls This Month: 45,231 / 100,000                             │
│                                                                      │
│  Team Management                                                     │
│  ┌─────────────────────────────────────────────────────────────┐   │
│  │ Role        │ Count │ Active │ Actions                      │   │
│  ├─────────────┼───────┼────────┼──────────────────────────────┤   │
│  │ Admins      │   3   │   3    │ [Manage]                     │   │
│  │ Instructors │   8   │   7    │ [Manage] [Invite]            │   │
│  │ TAs         │  15   │  12    │ [Manage] [Invite]            │   │
│  │ Learners    │ 130   │ 118    │ [Manage] [Bulk Import]       │   │
│  └─────────────────────────────────────────────────────────────┘   │
│                                                                      │
│  Compliance Status                                                   │
│  ✅ SOC2 Training - 98% compliant (3 users pending)                 │
│  ✅ HIPAA Training - 100% compliant                                 │
│  ⚠️ Security Awareness - 87% compliant (due in 14 days)            │
│                                                                      │
│  Recent Audit Events                                                 │
│  • Admin: Updated branding settings                    5 min ago    │
│  • Instructor: Published "Q4 Updates"                  2 hours ago  │
│  • System: Auto-archived 3 completed courses           1 day ago    │
│                                                                      │
│  [Full Audit Log] [Export Report] [Compliance Report]               │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Consequences

Positive

Scalable Management: Support for organizations with hundreds of courses and thousands of learners
Audit Trail: Complete history of all administrative actions
Flexible Permissions: Role-based access control supports diverse organizational structures
Efficient Operations: Bulk operations reduce administrative overhead

Negative

Complexity: Permission system adds complexity to all API endpoints
Database Growth: Audit logs can grow large over time
Learning Curve: Instructors need training on dashboard features

Risks and Mitigations

Risk	Impact	Mitigation
Permission misconfiguration	High	Default deny, comprehensive testing
Audit log data growth	Medium	Implement archival/retention policies
Performance degradation	Medium	Optimize queries, add pagination
Feature creep	Medium	Strict scope control per phase

Implementation Phases

Phase 2A: Core Instructor Tools (Weeks 1-3)

Course CRUD operations
Module/lesson management
Basic enrollment management
CLI commands for course management

Phase 2B: Organization Admin (Weeks 4-5)

Organization settings
Role/permission management
Member management
Audit logging

Phase 2C: Advanced Features (Weeks 6-7)

Bulk import/export
Group management
Course templates
Advanced dashboards

Phase 2D: Polish (Week 8)

Performance optimization
Documentation
Integration tests
User acceptance testing

ADR-LMS-004: User Authentication (prerequisite)
ADR-LMS-007: Assessment Engine (course content)
ADR-LMS-008: Learning Analytics (dashboard data)
ADR-LMS-010: External Integration (API exposure)

References

Document History:

Version	Date	Author	Changes
1.0	2025-12-11	Claude Code	Initial ADR

Context and Problem Statement​

Decision Drivers​

Technical Requirements​

User Experience Goals​

Business Requirements​

Decision Outcome​

Architecture Overview​

Database Schema​

New Tables​

Default Permissions​

Implementation​

Course Management Service​

Enrollment Management Service​

Permission Checking Service​

CLI Commands​

Course Management Commands​

Enrollment Management Commands​

Group Management Commands​

Organization Management Commands​

Dashboard Views​

Instructor Dashboard​

Organization Admin Dashboard​

Consequences​

Positive​

Negative​

Risks and Mitigations​

Implementation Phases​

Phase 2A: Core Instructor Tools (Weeks 1-3)​

Phase 2B: Organization Admin (Weeks 4-5)​

Phase 2C: Advanced Features (Weeks 6-7)​

Phase 2D: Polish (Week 8)​

Related ADRs​

References​