studyield · nymulinfoinlet · Apr 12, 2026 · Apr 12, 2026 · Apr 12, 2026 · Apr 12, 2026
diff --git a/backend/.env.example b/backend/.env.example
@@ -186,3 +186,12 @@ FIREBASE_CLIENT_EMAIL=firebase-adminsdk-xxxxx@your-project.iam.gserviceaccount.c
 UPLOAD_DESTINATION=./uploads
 MAX_FILE_SIZE=10485760
 TEMP_FILES_DIR=./temp
+
+# =====================================================
+# LTI 1.3 Configuration
+# =====================================================
+
+LTI_ENABLED=false
+LTI_KEY=your-lti-key
+LTI_SECRET=your-lti-secret
+# Platform registrations are done via the admin API
diff --git a/backend/migrations/015_lti_tables.sql b/backend/migrations/015_lti_tables.sql
@@ -0,0 +1,35 @@
+-- LTI 1.3 Provider Support
+-- Stores registered LMS platforms and LTI launch contexts
+
+-- Registered LMS platforms (Canvas, Moodle, Blackboard, etc.)
+CREATE TABLE IF NOT EXISTS lti_platforms (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    name VARCHAR(255) NOT NULL,
+    platform_url TEXT NOT NULL,
+    client_id VARCHAR(255) NOT NULL,
+    auth_endpoint TEXT NOT NULL,
+    token_endpoint TEXT NOT NULL,
+    jwks_url TEXT NOT NULL,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE UNIQUE INDEX idx_lti_platforms_client_url ON lti_platforms(client_id, platform_url);
+
+-- LTI launch contexts linking LTI users to Studyield users
+CREATE TABLE IF NOT EXISTS lti_contexts (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    platform_id UUID NOT NULL REFERENCES lti_platforms(id) ON DELETE CASCADE,
+    lti_user_id VARCHAR(255) NOT NULL,
+    course_id VARCHAR(255),
+    resource_link_id VARCHAR(255),
+    roles JSONB DEFAULT '[]',
+    last_launch_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE UNIQUE INDEX idx_lti_contexts_user_platform ON lti_contexts(user_id, platform_id);
+CREATE INDEX idx_lti_contexts_user_id ON lti_contexts(user_id);
+CREATE INDEX idx_lti_contexts_platform_id ON lti_contexts(platform_id);
+CREATE INDEX idx_lti_contexts_lti_user_id ON lti_contexts(lti_user_id);
diff --git a/backend/migrations/016_gamification.sql b/backend/migrations/016_gamification.sql
@@ -0,0 +1,64 @@
+-- Gamification system tables
+
+-- XP ledger: every XP award is recorded here
+CREATE TABLE IF NOT EXISTS xp_ledger (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    action VARCHAR(100) NOT NULL,
+    amount INTEGER NOT NULL,
+    metadata JSONB DEFAULT '{}',
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+CREATE INDEX idx_xp_ledger_user_id ON xp_ledger(user_id);
+CREATE INDEX idx_xp_ledger_created_at ON xp_ledger(created_at);
+CREATE INDEX idx_xp_ledger_user_action ON xp_ledger(user_id, action);
+
+-- User gamification profile: aggregated stats
+CREATE TABLE IF NOT EXISTS user_gamification (
+    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
+    total_xp INTEGER NOT NULL DEFAULT 0,
+    level INTEGER NOT NULL DEFAULT 0,
+    current_streak INTEGER NOT NULL DEFAULT 0,
+    longest_streak INTEGER NOT NULL DEFAULT 0,
+    last_study_date DATE,
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+-- Achievement definitions
+CREATE TABLE IF NOT EXISTS achievement_definitions (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    name VARCHAR(255) NOT NULL,
+    description TEXT NOT NULL,
+    icon VARCHAR(100) NOT NULL DEFAULT 'trophy',
+    condition_type VARCHAR(100) NOT NULL,
+    condition_value INTEGER NOT NULL,
+    xp_reward INTEGER NOT NULL DEFAULT 0,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+
+-- User achievements: which achievements a user has earned
+CREATE TABLE IF NOT EXISTS user_achievements (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    achievement_id UUID NOT NULL REFERENCES achievement_definitions(id) ON DELETE CASCADE,
+    earned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+    UNIQUE(user_id, achievement_id)
+);
+
+CREATE INDEX idx_user_achievements_user_id ON user_achievements(user_id);
+
+-- Seed achievement definitions
+INSERT INTO achievement_definitions (id, name, description, icon, condition_type, condition_value, xp_reward)
+VALUES
+    (uuid_generate_v4(), 'First Steps', 'Review 10 flashcards', 'star', 'total_reviews', 10, 10),
+    (uuid_generate_v4(), 'Card Shark', 'Review 100 flashcards', 'cards', 'total_reviews', 100, 50),
+    (uuid_generate_v4(), 'Quiz Master', 'Complete 10 quizzes', 'brain', 'quizzes_completed', 10, 50),
+    (uuid_generate_v4(), 'Perfect Score', 'Get 100% on any quiz', 'bullseye', 'perfect_score_count', 1, 25),
+    (uuid_generate_v4(), 'Week Warrior', '7-day study streak', 'fire', 'streak', 7, 100),
+    (uuid_generate_v4(), 'Month Master', '30-day study streak', 'calendar', 'streak', 30, 500),
+    (uuid_generate_v4(), 'Knowledge Builder', 'Upload 5 study materials', 'upload', 'materials_uploaded', 5, 50),
+    (uuid_generate_v4(), 'Teacher', 'Complete 5 teach-back sessions', 'chalkboard', 'teach_back_completed', 5, 75),
+    (uuid_generate_v4(), 'Exam Ready', 'Complete 10 practice exams', 'clipboard', 'exams_completed', 10, 100),
+    (uuid_generate_v4(), 'Scholar', 'Reach Level 10', 'graduation-cap', 'level', 10, 200)
+ON CONFLICT DO NOTHING;