1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

-- Core LMS Database Schema
 
-- Multi-tenant support: Every table includes tenant_id
CREATE TABLE institutions (
    institution_id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    domain VARCHAR(255) UNIQUE,
    settings JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Users with role flexibility (one user can be student in one course, TA in another)
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    institution_id UUID NOT NULL REFERENCES institutions(institution_id),
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMPTZ,
    UNIQUE(institution_id, email)
);
 
-- Course as container for all content
CREATE TABLE courses (
    course_id UUID PRIMARY KEY,
    institution_id UUID NOT NULL REFERENCES institutions(institution_id),
    code VARCHAR(50) NOT NULL,  -- "CS101"
    name VARCHAR(255) NOT NULL,  -- "Introduction to Computer Science"
    term_id UUID REFERENCES terms(term_id),
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    settings JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Enrollments link users to courses with roles
CREATE TABLE enrollments (
    enrollment_id UUID PRIMARY KEY,
    course_id UUID NOT NULL REFERENCES courses(course_id),
    user_id UUID NOT NULL REFERENCES users(user_id),
    role VARCHAR(20) NOT NULL,  -- STUDENT, INSTRUCTOR, TA, OBSERVER
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(course_id, user_id)
);
 
-- Modular content structure
CREATE TABLE modules (
    module_id UUID PRIMARY KEY,
    course_id UUID NOT NULL REFERENCES courses(course_id),
    name VARCHAR(255) NOT NULL,
    position INT NOT NULL,
    published BOOLEAN NOT NULL DEFAULT false,
    unlock_at TIMESTAMPTZ,
    prerequisite_module_id UUID REFERENCES modules(module_id)
);
 
-- Content items within modules (assignments, pages, quizzes, etc.)
CREATE TABLE content_items (
    content_id UUID PRIMARY KEY,
    module_id UUID NOT NULL REFERENCES modules(module_id),
    content_type VARCHAR(50) NOT NULL,  -- ASSIGNMENT, PAGE, QUIZ, FILE, LINK
    title VARCHAR(255) NOT NULL,
    position INT NOT NULL,
    content_data JSONB NOT NULL,  -- Type-specific data
    published BOOLEAN NOT NULL DEFAULT false,
    version INT NOT NULL DEFAULT 1
);
 
-- Indexes for common access patterns
CREATE INDEX idx_enrollments_user ON enrollments(user_id, status);
CREATE INDEX idx_enrollments_course ON enrollments(course_id, role);
CREATE INDEX idx_content_items_module ON content_items(module_id, position);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

-- Comprehensive gradebook schema
 
-- Assignments (anything that can be graded)
CREATE TABLE assignments (
    assignment_id UUID PRIMARY KEY,
    course_id UUID NOT NULL REFERENCES courses(course_id),
    assignment_group_id UUID REFERENCES assignment_groups(group_id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    points_possible DECIMAL(10,2) NOT NULL,
    grading_type VARCHAR(20) NOT NULL,  -- POINTS, PERCENTAGE, PASS_FAIL, LETTER
    due_at TIMESTAMPTZ,
    lock_at TIMESTAMPTZ,  -- No submissions after this
    submission_types TEXT[] NOT NULL,  -- ['online_upload', 'online_text']
    is_published BOOLEAN NOT NULL DEFAULT false
);
 
-- Grading weights by assignment group
CREATE TABLE assignment_groups (
    group_id UUID PRIMARY KEY,
    course_id UUID NOT NULL REFERENCES courses(course_id),
    name VARCHAR(100) NOT NULL,  -- "Homework", "Exams", "Projects"
    weight DECIMAL(5,2),  -- For weighted grading
    drop_lowest INT DEFAULT 0,  -- Drop lowest N scores
    drop_highest INT DEFAULT 0
);
 
-- Student submissions
CREATE TABLE submissions (
    submission_id UUID PRIMARY KEY,
    assignment_id UUID NOT NULL REFERENCES assignments(assignment_id),
    student_id UUID NOT NULL REFERENCES users(user_id),
    attempt_number INT NOT NULL DEFAULT 1,
    submitted_at TIMESTAMPTZ,
    submission_type VARCHAR(50),
    submission_data JSONB,  -- URL, text, file references
    workflow_state VARCHAR(20) NOT NULL DEFAULT 'UNSUBMITTED',
    UNIQUE(assignment_id, student_id, attempt_number)
);
 
-- Grades (can be given without submission for participation, etc.)
CREATE TABLE grades (
    grade_id UUID PRIMARY KEY,
    assignment_id UUID NOT NULL REFERENCES assignments(assignment_id),
    student_id UUID NOT NULL REFERENCES users(user_id),
    submission_id UUID REFERENCES submissions(submission_id),
    score DECIMAL(10,2),
    grade VARCHAR(10),  -- For letter grades
    graded_at TIMESTAMPTZ,
    grader_id UUID REFERENCES users(user_id),
    grading_feedback TEXT,
    is_excused BOOLEAN NOT NULL DEFAULT false,
    UNIQUE(assignment_id, student_id)
);
 
-- Calculated course grades (materialized for performance)
CREATE MATERIALIZED VIEW student_course_grades AS
WITH weighted_scores AS (
    SELECT 
        e.course_id,
        e.user_id,
        ag.group_id,
        ag.name AS group_name,
        ag.weight,
        ag.drop_lowest,
        SUM(CASE WHEN g.is_excused THEN 0 ELSE COALESCE(g.score, 0) END) AS earned_points,
        SUM(CASE WHEN g.is_excused THEN 0 ELSE a.points_possible END) AS possible_points
    FROM enrollments e
    JOIN assignments a ON a.course_id = e.course_id AND a.is_published = true
    LEFT JOIN assignment_groups ag ON a.assignment_group_id = ag.group_id
    LEFT JOIN grades g ON g.assignment_id = a.assignment_id AND g.student_id = e.user_id
    WHERE e.role = 'STUDENT'
    GROUP BY e.course_id, e.user_id, ag.group_id, ag.name, ag.weight, ag.drop_lowest
)
SELECT 
    course_id,
    user_id,
    ROUND(
        SUM(CASE WHEN possible_points > 0 
            THEN (earned_points / possible_points) * COALESCE(weight, 1) 
            ELSE 0 END) / NULLIF(SUM(COALESCE(weight, 1)), 0) * 100, 
        2
    ) AS current_grade_percent,
    SUM(earned_points) AS total_earned,
    SUM(possible_points) AS total_possible
FROM weighted_scores
GROUP BY course_id, user_id;
 
-- Refresh periodically or on grade changes
-- REFRESH MATERIALIZED VIEW CONCURRENTLY student_course_grades;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- Learning event tracking (xAPI-inspired)
 
CREATE TABLE learning_events (
    event_id BIGSERIAL PRIMARY KEY,
    
    -- Actor (who did it)
    user_id UUID NOT NULL REFERENCES users(user_id),
    
    -- Verb (what they did)
    verb VARCHAR(50) NOT NULL,  -- 'viewed', 'completed', 'answered', 'submitted'
    
    -- Object (what it was done to)
    object_type VARCHAR(50) NOT NULL,  -- 'page', 'video', 'question', 'assignment'
    object_id UUID NOT NULL,
    
    -- Context
    course_id UUID REFERENCES courses(course_id),
    module_id UUID REFERENCES modules(module_id),
    session_id UUID,  -- Group events in a learning session
    
    -- Result (optional outcome)
    result_score DECIMAL(5,2),  -- For questions
    result_success BOOLEAN,  -- Pass/fail
    result_completion BOOLEAN,  -- Completed the activity
    result_duration_seconds INT,  -- Time spent
    
    -- Extensions (flexible additional data)
    extensions JSONB,
    
    -- Timestamp
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Partitioning for large-scale analytics
    created_date DATE NOT NULL DEFAULT CURRENT_DATE
) PARTITION BY RANGE (created_date);
 
-- Create monthly partitions
CREATE TABLE learning_events_2024_01 PARTITION OF learning_events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
    
-- Common analytics indexes
CREATE INDEX idx_events_user_time ON learning_events(user_id, occurred_at DESC);
CREATE INDEX idx_events_object ON learning_events(object_type, object_id, occurred_at DESC);
CREATE INDEX idx_events_course ON learning_events(course_id, occurred_at DESC);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

-- 1. Early Warning: Students falling behind
WITH recent_engagement AS (
    SELECT 
        user_id,
        course_id,
        COUNT(*) AS events_last_week,
        MAX(occurred_at) AS last_activity
    FROM learning_events
    WHERE occurred_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
    GROUP BY user_id, course_id
),
class_average AS (
    SELECT 
        course_id,
        AVG(events_last_week) AS avg_events
    FROM recent_engagement
    GROUP BY course_id
)
SELECT 
    u.name AS student_name,
    c.name AS course_name,
    COALESCE(re.events_last_week, 0) AS student_events,
    ca.avg_events AS class_average_events,
    re.last_activity
FROM enrollments e
JOIN users u ON e.user_id = u.user_id
JOIN courses c ON e.course_id = c.course_id
LEFT JOIN recent_engagement re ON e.user_id = re.user_id AND e.course_id = re.course_id
JOIN class_average ca ON e.course_id = ca.course_id
WHERE e.role = 'STUDENT'
  AND (re.events_last_week IS NULL OR re.events_last_week < ca.avg_events * 0.5)
ORDER BY re.events_last_week NULLS FIRST;
 
-- 2. Content Effectiveness: Which questions are too hard/easy?
SELECT 
    q.question_id,
    q.question_text,
    COUNT(*) AS total_attempts,
    AVG(CASE WHEN le.result_success THEN 1 ELSE 0 END) AS success_rate,
    AVG(le.result_duration_seconds) AS avg_time_seconds
FROM learning_events le
JOIN questions q ON le.object_id = q.question_id
WHERE le.verb = 'answered'
  AND le.object_type = 'question'
GROUP BY q.question_id, q.question_text
HAVING COUNT(*) > 10
ORDER BY success_rate ASC;  -- Hardest questions first
 
-- 3. Learning Path Analysis: Common navigation patterns
SELECT 
    from_content.title AS from_page,
    to_content.title AS to_page,
    COUNT(*) AS transition_count,
    AVG(le2.occurred_at - le1.occurred_at) AS avg_time_between
FROM learning_events le1
JOIN learning_events le2 ON le1.user_id = le2.user_id
    AND le1.session_id = le2.session_id
    AND le2.occurred_at > le1.occurred_at
    AND le2.occurred_at < le1.occurred_at + INTERVAL '30 minutes'
JOIN content_items from_content ON le1.object_id = from_content.content_id
JOIN content_items to_content ON le2.object_id = to_content.content_id
WHERE le1.verb = 'viewed' AND le2.verb = 'viewed'
  AND NOT EXISTS (
      SELECT 1 FROM learning_events le_between
      WHERE le_between.user_id = le1.user_id
        AND le_between.session_id = le1.session_id
        AND le_between.occurred_at > le1.occurred_at
        AND le_between.occurred_at < le2.occurred_at
        AND le_between.verb = 'viewed'
  )
GROUP BY from_content.content_id, from_content.title, 
         to_content.content_id, to_content.title
ORDER BY transition_count DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

-- Adaptive learning data model
 
-- Knowledge taxonomy (what can be learned)
CREATE TABLE knowledge_components (
    kc_id UUID PRIMARY KEY,
    domain_id UUID REFERENCES domains(domain_id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    parent_kc_id UUID REFERENCES knowledge_components(kc_id)
);
 
-- Content-to-knowledge mapping
CREATE TABLE content_knowledge_mapping (
    content_id UUID NOT NULL REFERENCES content_items(content_id),
    kc_id UUID NOT NULL REFERENCES knowledge_components(kc_id),
    teaches BOOLEAN DEFAULT true,  -- Content teaches this KC
    assesses BOOLEAN DEFAULT false,  -- Content evaluates this KC
    difficulty_level DECIMAL(3,2),  -- 0.0 to 1.0
    PRIMARY KEY (content_id, kc_id)
);
 
-- Learner knowledge state (BKT model - Bayesian Knowledge Tracing)
CREATE TABLE learner_knowledge_state (
    user_id UUID NOT NULL REFERENCES users(user_id),
    kc_id UUID NOT NULL REFERENCES knowledge_components(kc_id),
    p_know DECIMAL(5,4) NOT NULL DEFAULT 0.1,  -- Probability of mastery
    last_practice_at TIMESTAMPTZ,
    practice_count INT NOT NULL DEFAULT 0,
    correct_count INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, kc_id)
);
 
-- Update knowledge state after each practice
CREATE OR REPLACE FUNCTION update_knowledge_state(
    p_user_id UUID,
    p_kc_id UUID,
    p_correct BOOLEAN
) RETURNS void AS $$
DECLARE
    v_current_p DECIMAL(5,4);
    v_p_learn DECIMAL(5,4) := 0.1;  -- Learning rate
    v_p_slip DECIMAL(5,4) := 0.1;   -- Probability of slip (knows but wrong)
    v_p_guess DECIMAL(5,4) := 0.25; -- Probability of guess (doesn't know but correct)
    v_new_p DECIMAL(5,4);
BEGIN
    -- Get current knowledge probability
    SELECT COALESCE(p_know, 0.1) INTO v_current_p
    FROM learner_knowledge_state
    WHERE user_id = p_user_id AND kc_id = p_kc_id;
    
    -- Bayesian update based on response
    IF p_correct THEN
        -- P(know | correct) using Bayes' rule
        v_new_p := (v_current_p * (1 - v_p_slip)) / 
                   (v_current_p * (1 - v_p_slip) + (1 - v_current_p) * v_p_guess);
    ELSE
        -- P(know | incorrect)
        v_new_p := (v_current_p * v_p_slip) /
                   (v_current_p * v_p_slip + (1 - v_current_p) * (1 - v_p_guess));
    END IF;
    
    -- Learning: probability increases even after wrong answer (they learned something)
    v_new_p := v_new_p + (1 - v_new_p) * v_p_learn;
    
    -- Upsert knowledge state
    INSERT INTO learner_knowledge_state (user_id, kc_id, p_know, practice_count, correct_count)
    VALUES (p_user_id, p_kc_id, v_new_p, 1, CASE WHEN p_correct THEN 1 ELSE 0 END)
    ON CONFLICT (user_id, kc_id) DO UPDATE SET
        p_know = v_new_p,
        practice_count = learner_knowledge_state.practice_count + 1,
        correct_count = learner_knowledge_state.correct_count + CASE WHEN p_correct THEN 1 ELSE 0 END,
        last_practice_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;
 
-- Recommend next content based on knowledge gaps
CREATE OR REPLACE FUNCTION get_recommended_content(
    p_user_id UUID,
    p_course_id UUID,
    p_limit INT DEFAULT 5
) RETURNS TABLE (content_id UUID, title VARCHAR, target_kc VARCHAR, mastery DECIMAL) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        ci.content_id,
        ci.title,
        kc.name AS target_kc,
        COALESCE(lks.p_know, 0.0) AS mastery
    FROM content_items ci
    JOIN content_knowledge_mapping ckm ON ci.content_id = ckm.content_id
    JOIN knowledge_components kc ON ckm.kc_id = kc.kc_id
    LEFT JOIN learner_knowledge_state lks 
        ON lks.user_id = p_user_id AND lks.kc_id = ckm.kc_id
    JOIN modules m ON ci.module_id = m.module_id
    WHERE m.course_id = p_course_id
      AND ci.is_published = true
      AND COALESCE(lks.p_know, 0.0) < 0.8  -- Not yet mastered
      -- Ensure prerequisites are met
      AND NOT EXISTS (
          SELECT 1 FROM knowledge_components prereq
          JOIN learner_knowledge_state prereq_state 
              ON prereq_state.kc_id = prereq.kc_id AND prereq_state.user_id = p_user_id
          WHERE prereq.kc_id = kc.parent_kc_id
            AND prereq_state.p_know < 0.7
      )
    ORDER BY 
        -- Prioritize content at the edge of current knowledge
        ABS(COALESCE(lks.p_know, 0.0) - 0.5) ASC,
        -- Then by content that hasn't been practiced recently
        COALESCE(lks.last_practice_at, '1970-01-01'::TIMESTAMPTZ) ASC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

-- Privacy-conscious educational database design
 
-- User data with privacy controls
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    -- PII stored separately for easier export/deletion
    pii_record_id UUID UNIQUE REFERENCES user_pii_encrypted(record_id),
    
    -- Non-PII profile data
    display_name VARCHAR(100),  -- User-chosen, may be pseudonym
    account_type VARCHAR(20) NOT NULL,  -- ADULT, MINOR, PARENT
    
    -- Privacy preferences
    directory_opt_out BOOLEAN NOT NULL DEFAULT false,
    analytics_consent BOOLEAN NOT NULL DEFAULT false,
    consent_timestamp TIMESTAMPTZ,
    consent_version VARCHAR(20),  -- Track which privacy policy was consented to
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- PII stored encrypted, separately managed
CREATE TABLE user_pii_encrypted (
    record_id UUID PRIMARY KEY,
    -- All fields encrypted at application layer
    email_encrypted BYTEA NOT NULL,
    name_encrypted BYTEA NOT NULL,
    dob_encrypted BYTEA,  -- For age verification
    
    -- Emergency data export
    last_export_at TIMESTAMPTZ,
    pending_deletion BOOLEAN DEFAULT false,
    deletion_requested_at TIMESTAMPTZ
);
 
-- Parental consent tracking for COPPA
CREATE TABLE parental_consents (
    consent_id UUID PRIMARY KEY,
    child_user_id UUID NOT NULL REFERENCES users(user_id),
    parent_email_hash BYTEA NOT NULL,  -- Hashed for verification
    consent_method VARCHAR(50) NOT NULL,  -- 'email', 'credit_card', 'video_call'
    consent_granted BOOLEAN NOT NULL,
    consent_timestamp TIMESTAMPTZ NOT NULL,
    consent_document_version VARCHAR(20) NOT NULL,
    ip_address INET,  -- For audit
    revoked_at TIMESTAMPTZ
);
 
-- Data access audit (FERPA requires access logging)
CREATE TABLE data_access_log (
    log_id BIGSERIAL PRIMARY KEY,
    accessed_user_id UUID NOT NULL REFERENCES users(user_id),
    accessor_user_id UUID REFERENCES users(user_id),
    accessor_type VARCHAR(50) NOT NULL,  -- 'self', 'parent', 'admin', 'researcher'
    access_purpose VARCHAR(100) NOT NULL,
    data_categories TEXT[],  -- What was accessed
    access_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Data deletion procedure (Right to be Forgotten)
CREATE OR REPLACE PROCEDURE delete_user_data(p_user_id UUID) AS $$
BEGIN
    -- Log the deletion request
    INSERT INTO data_deletion_log (user_id, requested_at, status)
    VALUES (p_user_id, CURRENT_TIMESTAMP, 'STARTED');
    
    -- Anonymize learning data (preserve for analytics, remove PII)
    UPDATE learning_events SET user_id = '00000000-0000-0000-0000-000000000000'
    WHERE user_id = p_user_id;
    
    -- Delete enrollments
    DELETE FROM enrollments WHERE user_id = p_user_id;
    
    -- Delete submissions (but not grades - may need for institution records)
    DELETE FROM submissions WHERE student_id = p_user_id;
    
    -- Delete PII
    DELETE FROM user_pii_encrypted 
    WHERE record_id = (SELECT pii_record_id FROM users WHERE user_id = p_user_id);
    
    -- Soft-delete user record
    UPDATE users SET 
        pii_record_id = NULL,
        display_name = 'Deleted User',
        deleted_at = CURRENT_TIMESTAMP
    WHERE user_id = p_user_id;
    
    UPDATE data_deletion_log SET status = 'COMPLETED', completed_at = CURRENT_TIMESTAMP
    WHERE user_id = p_user_id AND status = 'STARTED';
END;
$$ LANGUAGE plpgsql;