getcert-backend / src /lib /courseSessionPersistence.ts
ggop213's picture
fix(cert): auto-issue certificate on payment confirmation & sync course to 100% certified
46ab86e
Raw History Blame Contribute Delete
25.7 kB
/**
* Course Session Persistence Service
*
* This service handles persistent v2 certification sessions.
* Features:
* - Save and restore complete certification flow state
* - Track course progress across sessions
* - Manage assessment question generation and regeneration
* - Integration with My Courses dashboard
* - Window focus detection for question regeneration
*/
import { supabase } from './supabase';
import type { CertificationFlowState } from '../types';
import { logger } from '../utils/secureLogger';
const lastCourseSessionFingerprintByKey = new Map<string, string>();
const lastMyCourseFingerprintByKey = new Map<string, string>();
const profileSyncInFlightByUserId = new Map<string, Promise<void>>();
function buildPersistenceKey(userId: string, courseName: string): string {
return `${userId}:${courseName.trim().toLowerCase()}`;
}
function buildSessionFingerprint(payload: {
flowState: CertificationFlowState;
learningModules?: any;
generatedQuestions?: any;
assessmentAnswers?: string;
projectData?: any;
currentStep: string;
progressPercentage: number;
}): string {
return JSON.stringify({
flowState: payload.flowState,
learningModules: payload.learningModules,
generatedQuestions: payload.generatedQuestions,
assessmentAnswers: payload.assessmentAnswers,
projectData: payload.projectData,
currentStep: payload.currentStep,
progressPercentage: payload.progressPercentage,
});
}
function buildMyCourseFingerprint(updates: {
status?: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
currentStep?: string;
progressPercentage?: number;
assessmentScore?: number | null;
hasGeneratedQuestions?: boolean;
questionsGeneratedAt?: string | null;
completedAt?: string | null;
}): string {
return JSON.stringify(updates);
}
async function ensurePublicUserProfile(userId: string): Promise<void> {
const existingPromise = profileSyncInFlightByUserId.get(userId);
if (existingPromise) {
try {
await existingPromise;
} catch {
// Safe no-op
}
return;
}
const syncPromise = (async () => {
try {
const { data: authResult, error: authError } = await supabase.auth.getUser();
if (authError || !authResult?.user) {
return;
}
const authUser = authResult.user;
if (authUser.id !== userId) return;
const { data: profile, error: profileError } = await supabase
.from('users')
.select('id')
.eq('id', authUser.id)
.maybeSingle();
if (profileError) {
logger.warn(' Could not verify public user profile existence:', profileError.message);
}
if (!profile) {
logger.warn(` User profile for ${userId} not yet found in public.users.`);
}
} catch (err) {
logger.warn(' Non-blocking warning verifying user profile:', err);
}
})();
profileSyncInFlightByUserId.set(userId, syncPromise);
try {
await syncPromise;
} finally {
profileSyncInFlightByUserId.delete(userId);
}
}
export interface CourseSession {
id: string;
user_id: string;
course_name: string;
flow_state: CertificationFlowState;
learning_modules: any;
generated_questions: any;
assessment_answers: string | null;
project_data: any;
current_step: string;
progress_percentage: number;
last_resumed_at: string | null;
created_at: string | null;
updated_at: string | null;
}
export interface MyCourse {
id: string;
user_id: string;
course_name: string;
status: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
current_step: string;
progress_percentage: number;
assessment_score: number | null;
has_generated_questions: boolean;
questions_generated_at: string | null;
last_activity: string | null;
started_at: string | null;
completed_at: string | null;
created_at: string | null;
updated_at: string | null;
certification_track: string | null;
}
export interface AssessmentQuestion {
title: string;
question: string;
}
export interface AssessmentQuestionHistory {
id: string;
user_id: string;
course_name: string;
questions: AssessmentQuestion[];
generation_reason: 'initial' | 'window_focus_lost' | 'manual_refresh';
generated_at: string | null;
created_at: string | null;
}
/**
* Save or update a course session
*/
export async function saveCourseSession(
userId: string,
courseName: string,
sessionData: {
flowState: CertificationFlowState;
learningModules?: any;
generatedQuestions?: any;
assessmentAnswers?: string;
projectData?: any;
currentStep: string;
progressPercentage: number;
}
): Promise<{ success: boolean; error?: string }> {
try {
logger.log(` Saving course session for user ${userId}, course: ${courseName}`);
await ensurePublicUserProfile(userId);
const persistenceKey = buildPersistenceKey(userId, courseName);
const fingerprint = buildSessionFingerprint(sessionData);
if (lastCourseSessionFingerprintByKey.get(persistenceKey) === fingerprint) {
logger.log(` Skipping duplicate course session save for ${courseName}`);
return { success: true };
}
lastCourseSessionFingerprintByKey.set(persistenceKey, fingerprint);
// Use limit(1) instead of maybeSingle so duplicate historical rows
// do not crash the save path while we self-heal via updates.
const { data: existingSessionRows, error: existingLookupError } = await supabase
.from('course_sessions')
.select('id')
.eq('user_id', userId)
.eq('course_name', courseName)
.order('updated_at', { ascending: false })
.limit(1);
if (existingLookupError) {
logger.error(' Error looking up existing course session:', existingLookupError);
return { success: false, error: existingLookupError.message };
}
const existingSessionId = existingSessionRows?.[0]?.id;
let error;
const payload = {
user_id: userId,
course_name: courseName,
flow_state: sessionData.flowState,
learning_modules: sessionData.learningModules,
generated_questions: sessionData.generatedQuestions,
assessment_answers: sessionData.assessmentAnswers,
project_data: sessionData.projectData,
current_step: sessionData.currentStep,
progress_percentage: sessionData.progressPercentage
};
if (existingSessionId) {
const { error: updateError } = await supabase
.from('course_sessions')
.update(payload)
.eq('id', existingSessionId);
error = updateError;
} else {
const { error: insertError } = await supabase
.from('course_sessions')
.insert(payload);
if (insertError?.code === '23505') {
// Another in-flight request inserted the row first.
const { error: retryUpdateError } = await supabase
.from('course_sessions')
.update(payload)
.eq('user_id', userId)
.eq('course_name', courseName);
error = retryUpdateError;
} else {
error = insertError;
}
}
if (error) {
logger.error(' Error saving course session:', error);
return { success: false, error: error.message };
}
logger.log(` Course session saved successfully for ${courseName}`);
return { success: true };
} catch (error) {
logger.error(' Unexpected error saving course session:', error);
return { success: false, error: 'Unexpected error occurred' };
}
}
/**
* Load a course session for resuming
*/
export async function loadCourseSession(
userId: string,
courseName: string
): Promise<{ session: CourseSession | null; error?: string }> {
try {
logger.log(` Loading course session for user ${userId}, course: ${courseName}`);
const { data, error } = await supabase
.from('course_sessions')
.select('*')
.eq('user_id', userId)
.eq('course_name', courseName)
.maybeSingle();
if (error && error.code !== 'PGRST116') { // PGRST116 = no rows found
logger.error(' Error loading course session:', error);
return { session: null, error: error.message };
}
if (!data) {
logger.log(`No existing session found for ${courseName}`);
return { session: null };
}
logger.log(` Course session loaded for ${courseName}, step: ${data.current_step}`);
return { session: data };
} catch (error) {
logger.error(' Unexpected error loading course session:', error);
return { session: null, error: 'Unexpected error occurred' };
}
}
/**
* Update or create My Courses entry
*/
export async function updateMyCourse(
userId: string,
courseName: string,
updates: {
status?: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused';
currentStep?: string;
progressPercentage?: number;
assessmentScore?: number | null;
hasGeneratedQuestions?: boolean;
questionsGeneratedAt?: string | null;
completedAt?: string | null;
certificationTrack?: string | null;
}
): Promise<{ success: boolean; error?: string }> {
try {
logger.log(` Updating My Courses for user ${userId}, course: ${courseName}`);
await ensurePublicUserProfile(userId);
const persistenceKey = buildPersistenceKey(userId, courseName);
const fingerprint = buildMyCourseFingerprint(updates);
if (lastMyCourseFingerprintByKey.get(persistenceKey) === fingerprint) {
logger.log(` Skipping duplicate My Courses update for ${courseName}`);
return { success: true };
}
const nowIso = new Date().toISOString();
const updateData: any = {
user_id: userId,
course_name: courseName,
last_activity: nowIso,
updated_at: nowIso,
};
if (updates.status !== undefined) updateData.status = updates.status;
if (updates.currentStep !== undefined) updateData.current_step = updates.currentStep;
if (updates.progressPercentage !== undefined) updateData.progress_percentage = updates.progressPercentage;
if (updates.assessmentScore !== undefined) updateData.assessment_score = updates.assessmentScore;
if (updates.hasGeneratedQuestions !== undefined) updateData.has_generated_questions = updates.hasGeneratedQuestions;
if (updates.questionsGeneratedAt !== undefined) updateData.questions_generated_at = updates.questionsGeneratedAt;
if (updates.completedAt !== undefined) updateData.completed_at = updates.completedAt;
if (updates.certificationTrack !== undefined) updateData.certification_track = updates.certificationTrack;
// Use limit(1) instead of maybeSingle so duplicate historical rows
// do not crash the save path while we self-heal via updates.
const { data: existingCourseRows, error: existingLookupError } = await supabase
.from('my_courses')
.select('id')
.eq('user_id', userId)
.eq('course_name', courseName)
.limit(1);
if (existingLookupError) {
logger.error(' Error looking up existing My Courses row:', existingLookupError);
}
const existingCourseId = existingCourseRows?.[0]?.id;
let error;
if (existingCourseId) {
const { error: updateError } = await supabase
.from('my_courses')
.update(updateData)
.eq('id', existingCourseId);
error = updateError;
} else {
const { error: insertError } = await supabase
.from('my_courses')
.insert(updateData);
if (insertError?.code === '23505') {
// Another in-flight request inserted the row first.
const { error: retryUpdateError } = await supabase
.from('my_courses')
.update(updateData)
.eq('user_id', userId)
.eq('course_name', courseName);
error = retryUpdateError;
} else {
error = insertError;
}
}
if (error) {
logger.error(' Error updating My Courses:', error);
return { success: false, error: error.message };
}
// Only cache fingerprint on SUCCESS
lastMyCourseFingerprintByKey.set(persistenceKey, fingerprint);
logger.log(` My Courses updated for ${courseName}`);
if (typeof window !== 'undefined') {
window.dispatchEvent(new CustomEvent('getcert:course_updated', { detail: { courseName } }));
}
return { success: true };
} catch (error) {
logger.error(' Unexpected error updating My Courses:', error);
return { success: false, error: 'Unexpected error occurred' };
}
}
/**
* Get all courses for a user (for My Courses page)
* Cross-references both my_courses and certificates table to guarantee zero lost progress.
*/
export async function getMyCourses(userId: string, customClient?: any): Promise<{ courses: MyCourse[]; error?: string }> {
try {
logger.log(` Loading My Courses for user ${userId}`);
const client = customClient || supabase;
// Query 1: Courses from my_courses table
const coursesQuery = client
.from('my_courses')
.select('*')
.eq('user_id', userId)
.order('last_activity', { ascending: false });
// Query 2: Earned certificates from certificates table
const certsQuery = client
.from('certificates')
.select('id, user_id, topic, project_title, issued_at, metadata')
.eq('user_id', userId)
.order('issued_at', { ascending: false });
const [coursesRes, certsRes] = await Promise.all([coursesQuery, certsQuery]);
if (coursesRes.error) {
logger.error(' Error loading My Courses:', coursesRes.error);
}
if (certsRes.error) {
logger.error(' Error loading Certificates:', certsRes.error);
}
const dbCourses: MyCourse[] = coursesRes.data || [];
const dbCerts = certsRes.data || [];
// Synthesize courses from certificates if missing in my_courses
const existingCourseNames = new Set(
dbCourses.map((c) => c.course_name.toLowerCase().trim())
);
const syntheticCourses: MyCourse[] = [];
for (const cert of dbCerts) {
const meta = (cert.metadata as Record<string, any>) || {};
const courseName = cert.project_title || `${cert.topic} (Foundation Course)`;
const normalizedName = courseName.toLowerCase().trim();
const topicNormalized = (cert.topic || '').toLowerCase().trim();
// Check if an existing enrolled course in my_courses matches this certificate
const matchingCourse = dbCourses.find((c) => {
const cNorm = c.course_name.toLowerCase().trim();
return (
cNorm === normalizedName ||
(topicNormalized && (cNorm.includes(topicNormalized) || topicNormalized.includes(cNorm)))
);
});
let track: 'quickcert' | 'verifiedcert' | 'free_completion' = 'quickcert';
if (
meta.certification_track === 'free_completion' ||
meta.track === 'free_completion' ||
meta.free_course ||
meta.completion_type === 'free_starter_course' ||
String(meta.certificate_id || cert.id).startsWith('FC')
) {
track = 'free_completion';
} else if (
meta.certification_track === 'verifiedcert' ||
meta.track === 'verifiedcert' ||
String(meta.certificate_id || cert.id).startsWith('VC')
) {
track = 'verifiedcert';
}
if (matchingCourse) {
// Upgrade existing course to 100% certified immediately
if (matchingCourse.status !== 'certified' || matchingCourse.progress_percentage !== 100) {
matchingCourse.status = 'certified';
matchingCourse.current_step = 'certified';
matchingCourse.progress_percentage = 100;
matchingCourse.completed_at = cert.issued_at || matchingCourse.completed_at || new Date().toISOString();
matchingCourse.last_activity = cert.issued_at || matchingCourse.last_activity || new Date().toISOString();
if (meta.final_score) {
matchingCourse.assessment_score = meta.final_score;
}
// Asynchronously self-heal database row
void client
.from('my_courses')
.update({
status: 'certified',
current_step: 'certified',
progress_percentage: 100,
completed_at: matchingCourse.completed_at,
last_activity: matchingCourse.last_activity,
updated_at: new Date().toISOString(),
})
.eq('id', matchingCourse.id);
}
} else {
// Synthesize course from certificate if no course row existed
const synthetic: MyCourse = {
id: cert.id,
user_id: cert.user_id,
course_name: courseName,
status: 'certified',
current_step: 'certified',
progress_percentage: 100,
assessment_score: meta.final_score ?? 100,
has_generated_questions: true,
questions_generated_at: cert.issued_at,
last_activity: cert.issued_at || new Date().toISOString(),
started_at: cert.issued_at || new Date().toISOString(),
completed_at: cert.issued_at || new Date().toISOString(),
created_at: cert.issued_at || new Date().toISOString(),
updated_at: cert.issued_at || new Date().toISOString(),
certification_track: track,
};
syntheticCourses.push(synthetic);
existingCourseNames.add(normalizedName);
// Self-heal: backfill into my_courses table asynchronously
void client
.from('my_courses')
.upsert({
user_id: cert.user_id,
course_name: courseName,
status: 'certified',
current_step: 'certified',
progress_percentage: 100,
assessment_score: meta.final_score ?? 100,
certification_track: track,
completed_at: cert.issued_at,
last_activity: cert.issued_at,
}, { onConflict: 'user_id,course_name' });
}
}
const allCourses = [...dbCourses, ...syntheticCourses].sort((a, b) => {
const tA = new Date(a.last_activity || a.updated_at || a.started_at).getTime();
const tB = new Date(b.last_activity || b.updated_at || b.started_at).getTime();
return tB - tA;
});
logger.log(` Loaded ${allCourses.length} courses (${dbCourses.length} directly from DB, ${syntheticCourses.length} synthesized from certificates)`);
return { courses: allCourses };
} catch (error) {
logger.error(' Unexpected error loading My Courses:', error);
return { courses: [], error: 'Unexpected error occurred' };
}
}
/**
* Save assessment questions to history
*/
export async function saveAssessmentQuestions(
userId: string,
courseName: string,
questions: AssessmentQuestion[],
reason: 'initial' | 'window_focus_lost' | 'manual_refresh' = 'initial'
): Promise<{ success: boolean; error?: string }> {
try {
logger.log(` Saving assessment questions for user ${userId}, course: ${courseName}, reason: ${reason}`);
const { error } = await supabase
.from('assessment_questions_history')
.insert({
user_id: userId,
course_name: courseName,
questions: questions,
generation_reason: reason,
generated_at: new Date().toISOString()
});
if (error) {
logger.error(' Error saving assessment questions:', error);
return { success: false, error: error.message };
}
// Also update the course session with the new questions
await supabase
.from('course_sessions')
.update({
generated_questions: questions
})
.eq('user_id', userId)
.eq('course_name', courseName);
logger.log(` Assessment questions saved for ${courseName}`);
return { success: true };
} catch (error) {
logger.error(' Unexpected error saving assessment questions:', error);
return { success: false, error: 'Unexpected error occurred' };
}
}
/**
* Get the latest assessment questions for a course
*/
export async function getLatestAssessmentQuestions(
userId: string,
courseName: string
): Promise<{ questions: AssessmentQuestion[] | null; error?: string }> {
try {
logger.log(` Getting latest assessment questions for user ${userId}, course: ${courseName}`);
// First try to get from course session
const { data: sessionData } = await supabase
.from('course_sessions')
.select('generated_questions')
.eq('user_id', userId)
.eq('course_name', courseName)
.maybeSingle();
if (sessionData?.generated_questions) {
logger.log(` Found questions in course session for ${courseName}`);
return { questions: sessionData.generated_questions };
}
// If not in session, get from history
const { data: historyData, error } = await supabase
.from('assessment_questions_history')
.select('questions')
.eq('user_id', userId)
.eq('course_name', courseName)
.order('generated_at', { ascending: false })
.limit(1)
.maybeSingle();
if (error && error.code !== 'PGRST116') {
logger.error(' Error getting assessment questions:', error);
return { questions: null, error: error.message };
}
if (!historyData) {
logger.log(`No assessment questions found for ${courseName}`);
return { questions: null };
}
logger.log(` Found questions in history for ${courseName}`);
return { questions: historyData.questions };
} catch (error) {
logger.error(' Unexpected error getting assessment questions:', error);
return { questions: null, error: 'Unexpected error occurred' };
}
}
/**
* Delete a course session (when user wants to restart)
*/
export async function deleteCourseSession(
userId: string,
courseName: string
): Promise<{ success: boolean; error?: string }> {
try {
logger.log(` Deleting course session for user ${userId}, course: ${courseName}`);
// Delete from course_sessions
const { error: sessionError } = await supabase
.from('course_sessions')
.delete()
.eq('user_id', userId)
.eq('course_name', courseName);
if (sessionError) {
logger.error(' Error deleting course session:', sessionError);
return { success: false, error: sessionError.message };
}
// Update my_courses status to indicate restart
await updateMyCourse(userId, courseName, {
status: 'in_progress',
currentStep: 'selection',
progressPercentage: 0,
assessmentScore: null,
hasGeneratedQuestions: false,
questionsGeneratedAt: null,
completedAt: null,
});
const persistenceKey = buildPersistenceKey(userId, courseName);
lastCourseSessionFingerprintByKey.delete(persistenceKey);
logger.log(` Course session deleted for ${courseName}`);
if (typeof window !== 'undefined') {
window.dispatchEvent(new CustomEvent('getcert:course_updated', { detail: { courseName } }));
}
return { success: true };
} catch (error) {
logger.error(' Unexpected error deleting course session:', error);
return { success: false, error: 'Unexpected error occurred' };
}
}
/**
* Calculate progress percentage based on current step
*/
export function calculateProgressPercentage(currentStep: string): number {
const stepProgress = {
'selection': 0,
'loading': 10,
'learning': 40,
'assessment': 70,
'results': 85,
'project': 100
};
return stepProgress[currentStep as keyof typeof stepProgress] || 0;
}
/**
* Auto-save course session (to be called on state changes)
*/
export async function autoSaveCourseSession(
userId: string,
courseName: string,
flowState: CertificationFlowState,
additionalData: {
learningModules?: any;
generatedQuestions?: any;
assessmentAnswers?: string;
projectData?: any;
} = {}
): Promise<void> {
const currentStep = flowState.currentStep;
let progressPercentage = calculateProgressPercentage(currentStep);
let status: 'in_progress' | 'assessment_completed' | 'project_submitted' | 'certified' | 'paused' = 'in_progress';
if (currentStep === 'results') {
const assessmentScore = flowState.assessmentScore || 0;
const assessmentPassed = assessmentScore >= 60;
progressPercentage = assessmentPassed ? 85 : 60;
status = assessmentPassed ? 'assessment_completed' : 'in_progress';
} else if (currentStep === 'project') {
if (flowState.issuedCertificate) {
progressPercentage = 100;
status = 'certified';
} else {
progressPercentage = 95;
status = 'project_submitted';
}
} else if (currentStep === 'assessment') {
progressPercentage = 70;
status = 'in_progress';
} else if (currentStep === 'learning') {
progressPercentage = 40;
status = 'in_progress';
} else if (currentStep === 'loading') {
progressPercentage = 10;
status = 'in_progress';
}
const hasPersistableLearningState = Boolean(
flowState.courseData ||
additionalData.learningModules ||
flowState.assessmentPack ||
flowState.projectData ||
additionalData.generatedQuestions ||
additionalData.projectData
);
if (currentStep === 'loading' && !hasPersistableLearningState) {
logger.log(` Skipping transient loading session save for ${courseName}`);
} else {
// Save the full session
await saveCourseSession(userId, courseName, {
flowState,
currentStep,
progressPercentage,
...additionalData
});
}
const certificationTrack = flowState.certificationTrack || 'verifiedcert';
// Update My Courses dashboard
await updateMyCourse(userId, courseName, {
status,
currentStep,
progressPercentage,
certificationTrack,
});
}