This document describes the database schemas, data relationships, and model definitions used in OpenLN.
OpenLN uses MongoDB as its primary database with Mongoose ODM for data modeling. The database follows a document-based approach with embedded and referenced relationships.
- Denormalization: Optimize for read performance
- Embedding vs. Referencing: Based on access patterns
- Indexes: Strategic indexing for query performance
- Validation: Schema-level and application-level validation
The User model represents registered users in the system.
// server/models/User.js
import mongoose from 'mongoose';
import validator from 'validator';
import bcrypt from 'bcryptjs';
const userSchema = new mongoose.Schema({
// Authentication
email: {
type: String,
required: [true, 'Email is required'],
unique: true,
lowercase: true,
trim: true,
validate: [validator.isEmail, 'Please provide a valid email'],
index: true,
},
password: {
type: String,
required: function() {
return !this.googleId; // Password required if not OAuth user
},
minlength: [8, 'Password must be at least 8 characters long'],
select: false, // Don't include in queries by default
},
// OAuth Integration
googleId: {
type: String,
sparse: true,
index: true,
},
// Basic Information
name: {
type: String,
required: [true, 'Name is required'],
trim: true,
maxlength: [100, 'Name cannot exceed 100 characters'],
},
// Profile Information
profile: {
avatar: {
type: String,
validate: [validator.isURL, 'Avatar must be a valid URL'],
},
bio: {
type: String,
maxlength: [500, 'Bio cannot exceed 500 characters'],
},
location: {
type: String,
maxlength: [100, 'Location cannot exceed 100 characters'],
},
website: {
type: String,
validate: [validator.isURL, 'Website must be a valid URL'],
},
dateOfBirth: Date,
occupation: String,
},
// User Preferences
preferences: {
theme: {
type: String,
enum: ['light', 'dark', 'system'],
default: 'system',
},
language: {
type: String,
default: 'en',
},
timezone: {
type: String,
default: 'UTC',
},
notifications: {
email: { type: Boolean, default: true },
push: { type: Boolean, default: false },
marketing: { type: Boolean, default: false },
courseUpdates: { type: Boolean, default: true },
goalReminders: { type: Boolean, default: true },
},
learningStyle: {
type: String,
enum: ['visual', 'auditory', 'kinesthetic', 'reading'],
},
difficultyPreference: {
type: String,
enum: ['beginner', 'intermediate', 'advanced'],
default: 'beginner',
},
},
// System Information
role: {
type: String,
enum: ['user', 'instructor', 'admin'],
default: 'user',
},
isActive: {
type: Boolean,
default: true,
},
isEmailVerified: {
type: Boolean,
default: false,
},
emailVerificationToken: String,
passwordResetToken: String,
passwordResetExpires: Date,
// Activity Tracking
lastLogin: Date,
loginCount: {
type: Number,
default: 0,
},
// Learning Statistics
stats: {
coursesEnrolled: { type: Number, default: 0 },
coursesCompleted: { type: Number, default: 0 },
totalLearningTime: { type: Number, default: 0 }, // in minutes
currentStreak: { type: Number, default: 0 },
longestStreak: { type: Number, default: 0 },
achievementsEarned: { type: Number, default: 0 },
},
}, {
timestamps: true,
toJSON: { virtuals: true },
toObject: { virtuals: true },
});
// Indexes
userSchema.index({ email: 1 });
userSchema.index({ googleId: 1 }, { sparse: true });
userSchema.index({ 'profile.name': 'text' });
userSchema.index({ role: 1 });
userSchema.index({ isActive: 1 });
userSchema.index({ createdAt: -1 });
// Virtual fields
userSchema.virtual('fullName').get(function() {
return this.name;
});
userSchema.virtual('completionRate').get(function() {
if (this.stats.coursesEnrolled === 0) return 0;
return Math.round((this.stats.coursesCompleted / this.stats.coursesEnrolled) * 100);
});
// Instance methods
userSchema.methods.toProfileJSON = function() {
return {
id: this._id,
email: this.email,
name: this.name,
profile: this.profile,
preferences: this.preferences,
role: this.role,
stats: this.stats,
isEmailVerified: this.isEmailVerified,
createdAt: this.createdAt,
updatedAt: this.updatedAt,
};
};
userSchema.methods.comparePassword = async function(candidatePassword) {
return bcrypt.compare(candidatePassword, this.password);
};
// Static methods
userSchema.statics.findByEmail = function(email) {
return this.findOne({ email: email.toLowerCase() });
};
// Middleware
userSchema.pre('save', async function(next) {
if (!this.isModified('password')) return next();
this.password = await bcrypt.hash(this.password, 12);
next();
});
export const User = mongoose.model('User', userSchema);// client/src/types/user.ts
export interface User {
id: string;
email: string;
name: string;
profile: UserProfile;
preferences: UserPreferences;
role: UserRole;
stats: UserStats;
isEmailVerified: boolean;
createdAt: string;
updatedAt: string;
}
export interface UserProfile {
avatar?: string;
bio?: string;
location?: string;
website?: string;
dateOfBirth?: string;
occupation?: string;
}
export interface UserPreferences {
theme: 'light' | 'dark' | 'system';
language: string;
timezone: string;
notifications: {
email: boolean;
push: boolean;
marketing: boolean;
courseUpdates: boolean;
goalReminders: boolean;
};
learningStyle?: 'visual' | 'auditory' | 'kinesthetic' | 'reading';
difficultyPreference: 'beginner' | 'intermediate' | 'advanced';
}
export type UserRole = 'user' | 'instructor' | 'admin';
export interface UserStats {
coursesEnrolled: number;
coursesCompleted: number;
totalLearningTime: number;
currentStreak: number;
longestStreak: number;
achievementsEarned: number;
}The Course model represents learning content and courses.
// server/models/Course.js
import mongoose from 'mongoose';
const lessonSchema = new mongoose.Schema({
title: {
type: String,
required: true,
trim: true,
},
description: String,
type: {
type: String,
enum: ['video', 'text', 'quiz', 'assignment', 'interactive'],
required: true,
},
content: {
url: String,
text: String,
duration: Number, // in seconds
fileSize: Number, // in bytes
},
order: {
type: Number,
required: true,
},
isRequired: {
type: Boolean,
default: true,
},
prerequisites: [String], // Lesson IDs
});
const moduleSchema = new mongoose.Schema({
title: {
type: String,
required: true,
trim: true,
},
description: String,
lessons: [lessonSchema],
order: {
type: Number,
required: true,
},
estimatedDuration: Number, // in minutes
});
const courseSchema = new mongoose.Schema({
// Basic Information
title: {
type: String,
required: [true, 'Course title is required'],
trim: true,
maxlength: [200, 'Title cannot exceed 200 characters'],
index: 'text',
},
description: {
type: String,
required: [true, 'Course description is required'],
maxlength: [2000, 'Description cannot exceed 2000 characters'],
index: 'text',
},
shortDescription: {
type: String,
maxlength: [300, 'Short description cannot exceed 300 characters'],
},
// Media
thumbnail: {
type: String,
required: true,
},
banner: String,
// Course Structure
modules: [moduleSchema],
// Categorization
category: {
type: String,
required: true,
enum: [
'programming',
'design',
'marketing',
'business',
'data-science',
'personal-development',
'language',
'other'
],
index: true,
},
subcategory: String,
tags: [{
type: String,
lowercase: true,
trim: true,
}],
// Difficulty and Prerequisites
difficulty: {
type: String,
enum: ['beginner', 'intermediate', 'advanced'],
required: true,
index: true,
},
prerequisites: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Course',
}],
skillsGained: [String],
// Learning Objectives
learningObjectives: [String],
// Instructor Information
instructor: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
required: true,
},
coInstructors: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
}],
// Course Metadata
language: {
type: String,
default: 'en',
},
estimatedDuration: {
type: Number, // in minutes
required: true,
},
// Pricing
price: {
amount: { type: Number, default: 0 },
currency: { type: String, default: 'USD' },
},
isFree: {
type: Boolean,
default: true,
},
// Status
status: {
type: String,
enum: ['draft', 'published', 'archived'],
default: 'draft',
index: true,
},
isActive: {
type: Boolean,
default: true,
},
// Statistics
stats: {
enrollments: { type: Number, default: 0 },
completions: { type: Number, default: 0 },
averageRating: { type: Number, default: 0 },
totalRatings: { type: Number, default: 0 },
views: { type: Number, default: 0 },
},
// Dates
publishedAt: Date,
lastUpdated: Date,
}, {
timestamps: true,
toJSON: { virtuals: true },
toObject: { virtuals: true },
});
// Indexes
courseSchema.index({ title: 'text', description: 'text' });
courseSchema.index({ category: 1, difficulty: 1 });
courseSchema.index({ instructor: 1 });
courseSchema.index({ status: 1, isActive: 1 });
courseSchema.index({ 'stats.averageRating': -1 });
courseSchema.index({ 'stats.enrollments': -1 });
courseSchema.index({ createdAt: -1 });
// Virtual fields
courseSchema.virtual('totalLessons').get(function() {
return this.modules.reduce((total, module) => total + module.lessons.length, 0);
});
courseSchema.virtual('completionRate').get(function() {
if (this.stats.enrollments === 0) return 0;
return Math.round((this.stats.completions / this.stats.enrollments) * 100);
});
// Instance methods
courseSchema.methods.toListJSON = function() {
return {
id: this._id,
title: this.title,
shortDescription: this.shortDescription,
thumbnail: this.thumbnail,
category: this.category,
difficulty: this.difficulty,
estimatedDuration: this.estimatedDuration,
isFree: this.isFree,
price: this.price,
stats: this.stats,
instructor: this.instructor,
createdAt: this.createdAt,
};
};
export const Course = mongoose.model('Course', courseSchema);The Goal model represents user learning goals and objectives.
// server/models/Goal.js
import mongoose from 'mongoose';
const milestoneSchema = new mongoose.Schema({
title: {
type: String,
required: true,
trim: true,
},
description: String,
targetValue: Number,
completed: {
type: Boolean,
default: false,
},
completedAt: Date,
order: Number,
});
const goalSchema = new mongoose.Schema({
// Basic Information
title: {
type: String,
required: [true, 'Goal title is required'],
trim: true,
maxlength: [200, 'Title cannot exceed 200 characters'],
},
description: {
type: String,
maxlength: [1000, 'Description cannot exceed 1000 characters'],
},
// User Reference
userId: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
required: true,
index: true,
},
// Goal Type and Target
type: {
type: String,
enum: [
'course_completion',
'skill_development',
'learning_time',
'certification',
'project_completion',
'habit_formation',
'custom'
],
required: true,
},
target: {
type: {
type: String,
enum: ['completion', 'time', 'count', 'score', 'date'],
required: true,
},
value: {
type: Number,
required: true,
},
unit: String, // 'minutes', 'hours', 'courses', 'points', etc.
deadline: Date,
},
// Progress Tracking
progress: {
current: {
type: Number,
default: 0,
},
percentage: {
type: Number,
default: 0,
min: 0,
max: 100,
},
lastUpdated: Date,
},
// Milestones
milestones: [milestoneSchema],
// Related Resources
relatedCourses: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Course',
}],
relatedSkills: [String],
// Goal Settings
priority: {
type: String,
enum: ['low', 'medium', 'high'],
default: 'medium',
},
category: {
type: String,
enum: [
'academic',
'professional',
'personal',
'health',
'hobby',
'skill',
'career'
],
},
isPublic: {
type: Boolean,
default: false,
},
// Status
status: {
type: String,
enum: ['active', 'completed', 'paused', 'cancelled'],
default: 'active',
index: true,
},
// Dates
startDate: {
type: Date,
default: Date.now,
},
completedAt: Date,
// Reminders
reminders: {
enabled: { type: Boolean, default: true },
frequency: {
type: String,
enum: ['daily', 'weekly', 'monthly'],
default: 'weekly',
},
lastReminder: Date,
},
// Notes and Updates
notes: String,
updates: [{
content: String,
progress: Number,
createdAt: { type: Date, default: Date.now },
}],
}, {
timestamps: true,
toJSON: { virtuals: true },
toObject: { virtuals: true },
});
// Indexes
goalSchema.index({ userId: 1, status: 1 });
goalSchema.index({ userId: 1, type: 1 });
goalSchema.index({ status: 1, 'target.deadline': 1 });
goalSchema.index({ createdAt: -1 });
// Virtual fields
goalSchema.virtual('isOverdue').get(function() {
return this.target.deadline &&
this.target.deadline < new Date() &&
this.status !== 'completed';
});
goalSchema.virtual('daysRemaining').get(function() {
if (!this.target.deadline) return null;
const diffTime = this.target.deadline - new Date();
return Math.ceil(diffTime / (1000 * 60 * 60 * 24));
});
// Instance methods
goalSchema.methods.updateProgress = function(newProgress) {
this.progress.current = newProgress;
this.progress.percentage = Math.min(100, (newProgress / this.target.value) * 100);
this.progress.lastUpdated = new Date();
if (this.progress.percentage >= 100) {
this.status = 'completed';
this.completedAt = new Date();
}
return this.save();
};
goalSchema.methods.addUpdate = function(content, progress) {
this.updates.push({
content,
progress: progress || this.progress.current,
});
if (progress !== undefined) {
this.updateProgress(progress);
}
return this.save();
};
export const Goal = mongoose.model('Goal', goalSchema);The Progress model tracks user progress through courses and learning materials.
// server/models/Progress.js
import mongoose from 'mongoose';
const lessonProgressSchema = new mongoose.Schema({
lessonId: {
type: String,
required: true,
},
completed: {
type: Boolean,
default: false,
},
timeSpent: {
type: Number,
default: 0, // in seconds
},
score: Number,
attempts: {
type: Number,
default: 0,
},
lastAccessed: Date,
completedAt: Date,
notes: String,
});
const moduleProgressSchema = new mongoose.Schema({
moduleId: {
type: String,
required: true,
},
lessons: [lessonProgressSchema],
completed: {
type: Boolean,
default: false,
},
completedAt: Date,
timeSpent: {
type: Number,
default: 0,
},
});
const progressSchema = new mongoose.Schema({
// References
userId: {
type: mongoose.Schema.Types.ObjectId,
ref: 'User',
required: true,
index: true,
},
courseId: {
type: mongoose.Schema.Types.ObjectId,
ref: 'Course',
required: true,
index: true,
},
// Progress Data
modules: [moduleProgressSchema],
// Overall Progress
overallProgress: {
completed: {
type: Boolean,
default: false,
},
percentage: {
type: Number,
default: 0,
min: 0,
max: 100,
},
completedLessons: {
type: Number,
default: 0,
},
totalLessons: {
type: Number,
required: true,
},
},
// Time Tracking
timeSpent: {
type: Number,
default: 0, // in seconds
},
// Course Performance
averageScore: Number,
// Status
status: {
type: String,
enum: ['not_started', 'in_progress', 'completed', 'dropped'],
default: 'not_started',
},
// Dates
enrolledAt: {
type: Date,
default: Date.now,
},
startedAt: Date,
completedAt: Date,
lastAccessed: Date,
// Current Position
currentModule: String,
currentLesson: String,
// Certificates
certificate: {
issued: { type: Boolean, default: false },
issuedAt: Date,
certificateId: String,
downloadUrl: String,
},
// Notes and Bookmarks
notes: String,
bookmarks: [{
lessonId: String,
note: String,
timestamp: Number, // for video bookmarks
createdAt: { type: Date, default: Date.now },
}],
}, {
timestamps: true,
toJSON: { virtuals: true },
toObject: { virtuals: true },
});
// Indexes
progressSchema.index({ userId: 1, courseId: 1 }, { unique: true });
progressSchema.index({ userId: 1, status: 1 });
progressSchema.index({ courseId: 1, status: 1 });
progressSchema.index({ lastAccessed: -1 });
// Virtual fields
progressSchema.virtual('estimatedTimeRemaining').get(function() {
// Calculate based on course duration and current progress
// This would need course data to calculate accurately
return null;
});
// Instance methods
progressSchema.methods.updateLessonProgress = function(moduleId, lessonId, data) {
const module = this.modules.find(m => m.moduleId === moduleId);
if (!module) return false;
const lesson = module.lessons.find(l => l.lessonId === lessonId);
if (!lesson) {
module.lessons.push({
lessonId,
...data,
lastAccessed: new Date(),
});
} else {
Object.assign(lesson, data, { lastAccessed: new Date() });
}
// Update overall progress
this.calculateOverallProgress();
return this.save();
};
progressSchema.methods.calculateOverallProgress = function() {
const totalLessons = this.modules.reduce((total, module) =>
total + module.lessons.length, 0
);
const completedLessons = this.modules.reduce((total, module) =>
total + module.lessons.filter(lesson => lesson.completed).length, 0
);
this.overallProgress.totalLessons = totalLessons;
this.overallProgress.completedLessons = completedLessons;
this.overallProgress.percentage = totalLessons > 0 ?
Math.round((completedLessons / totalLessons) * 100) : 0;
if (this.overallProgress.percentage >= 100 && !this.overallProgress.completed) {
this.overallProgress.completed = true;
this.status = 'completed';
this.completedAt = new Date();
}
};
export const Progress = mongoose.model('Progress', progressSchema);User (1) ──────── (M) Goal
│
│ (1)
│
└── (M) Progress ──── (1) Course
│
│ (1)
│
└── (1) User (Instructor)
// Get user with populated courses and goals
const user = await User.findById(userId)
.populate('enrolledCourses')
.populate('goals');
// Get course with instructor details
const course = await Course.findById(courseId)
.populate('instructor', 'name profile.avatar')
.populate('prerequisites');
// Get user progress with course details
const progress = await Progress.find({ userId })
.populate('courseId', 'title thumbnail difficulty');// client/src/types/models.ts
export interface Course {
id: string;
title: string;
description: string;
shortDescription?: string;
thumbnail: string;
banner?: string;
modules: Module[];
category: CourseCategory;
subcategory?: string;
tags: string[];
difficulty: Difficulty;
prerequisites: string[];
skillsGained: string[];
learningObjectives: string[];
instructor: string | User;
coInstructors: string[] | User[];
language: string;
estimatedDuration: number;
price: {
amount: number;
currency: string;
};
isFree: boolean;
status: CourseStatus;
isActive: boolean;
stats: CourseStats;
publishedAt?: string;
lastUpdated?: string;
createdAt: string;
updatedAt: string;
}
export interface Module {
id: string;
title: string;
description?: string;
lessons: Lesson[];
order: number;
estimatedDuration?: number;
}
export interface Lesson {
id: string;
title: string;
description?: string;
type: LessonType;
content: LessonContent;
order: number;
isRequired: boolean;
prerequisites: string[];
}
export interface Goal {
id: string;
title: string;
description?: string;
userId: string;
type: GoalType;
target: GoalTarget;
progress: GoalProgress;
milestones: Milestone[];
relatedCourses: string[];
relatedSkills: string[];
priority: Priority;
category?: GoalCategory;
isPublic: boolean;
status: GoalStatus;
startDate: string;
completedAt?: string;
reminders: GoalReminders;
notes?: string;
updates: GoalUpdate[];
createdAt: string;
updatedAt: string;
}
export interface Progress {
id: string;
userId: string;
courseId: string;
modules: ModuleProgress[];
overallProgress: OverallProgress;
timeSpent: number;
averageScore?: number;
status: ProgressStatus;
enrolledAt: string;
startedAt?: string;
completedAt?: string;
lastAccessed?: string;
currentModule?: string;
currentLesson?: string;
certificate?: Certificate;
notes?: string;
bookmarks: Bookmark[];
createdAt: string;
updatedAt: string;
}
// Enums and Union Types
export type CourseCategory =
| 'programming'
| 'design'
| 'marketing'
| 'business'
| 'data-science'
| 'personal-development'
| 'language'
| 'other';
export type Difficulty = 'beginner' | 'intermediate' | 'advanced';
export type CourseStatus = 'draft' | 'published' | 'archived';
export type LessonType = 'video' | 'text' | 'quiz' | 'assignment' | 'interactive';
export type GoalType =
| 'course_completion'
| 'skill_development'
| 'learning_time'
| 'certification'
| 'project_completion'
| 'habit_formation'
| 'custom';
export type Priority = 'low' | 'medium' | 'high';
export type GoalStatus = 'active' | 'completed' | 'paused' | 'cancelled';
export type ProgressStatus = 'not_started' | 'in_progress' | 'completed' | 'dropped';This data model design provides a robust foundation for the OpenLN learning platform, supporting complex learning paths, progress tracking, and goal management while maintaining flexibility for future enhancements.