Spaces:
Running
Running
| -- Phase 1: Database Schema for New Certification Flow | |
| -- This creates tables to track structured certification sessions and their steps | |
| -- Table 1: certification_sessions | |
| -- Tracks each unique certification attempt with status progression | |
| CREATE TABLE IF NOT EXISTS certification_sessions ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, | |
| course_name TEXT NOT NULL, | |
| status TEXT NOT NULL DEFAULT 'in_progress' | |
| CHECK (status IN ('in_progress', 'assessment_completed', 'project_submitted', 'certified')), | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), | |
| updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- Table 2: session_steps | |
| -- Logs every action within a session for chat history reconstruction | |
| CREATE TABLE IF NOT EXISTS session_steps ( | |
| id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), | |
| session_id UUID NOT NULL REFERENCES certification_sessions(id) ON DELETE CASCADE, | |
| step_type TEXT NOT NULL | |
| CHECK (step_type IN ('course_selected', 'learning_started', 'assessment_submitted', 'results_viewed', 'project_generated')), | |
| step_data JSONB NOT NULL DEFAULT '{}', | |
| created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() | |
| ); | |
| -- Row Level Security (RLS) Policies | |
| -- Enable RLS on both tables | |
| ALTER TABLE certification_sessions ENABLE ROW LEVEL SECURITY; | |
| ALTER TABLE session_steps ENABLE ROW LEVEL SECURITY; | |
| -- RLS Policy for certification_sessions: Users can only access their own sessions | |
| CREATE POLICY "Users can only access their own certification sessions" ON certification_sessions | |
| FOR ALL USING (auth.uid() = user_id); | |
| -- RLS Policy for session_steps: Users can only access steps from their own sessions | |
| CREATE POLICY "Users can only access their own session steps" ON session_steps | |
| FOR ALL USING ( | |
| EXISTS ( | |
| SELECT 1 FROM certification_sessions | |
| WHERE certification_sessions.id = session_steps.session_id | |
| AND certification_sessions.user_id = auth.uid() | |
| ) | |
| ); | |
| -- Indexes for performance | |
| CREATE INDEX IF NOT EXISTS idx_certification_sessions_user_id ON certification_sessions(user_id); | |
| CREATE INDEX IF NOT EXISTS idx_certification_sessions_status ON certification_sessions(status); | |
| CREATE INDEX IF NOT EXISTS idx_session_steps_session_id ON session_steps(session_id); | |
| CREATE INDEX IF NOT EXISTS idx_session_steps_step_type ON session_steps(step_type); | |
| CREATE INDEX IF NOT EXISTS idx_session_steps_created_at ON session_steps(created_at); | |
| -- Update trigger for certification_sessions.updated_at | |
| CREATE OR REPLACE FUNCTION update_updated_at_column() | |
| RETURNS TRIGGER AS $$ | |
| BEGIN | |
| NEW.updated_at = NOW(); | |
| RETURN NEW; | |
| END; | |
| $$ language 'plpgsql'; | |
| CREATE TRIGGER update_certification_sessions_updated_at | |
| BEFORE UPDATE ON certification_sessions | |
| FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); | |
| -- Sample step_data structures (for documentation): | |
| /* | |
| For 'course_selected': | |
| { | |
| "course_name": "JavaScript Fundamentals", | |
| "difficulty": "Beginner", | |
| "estimated_duration": "2-3 hours" | |
| } | |
| For 'learning_started': | |
| { | |
| "modules": [ | |
| { | |
| "title": "Introduction to JavaScript", | |
| "youtube_url": "https://youtube.com/watch?v=...", | |
| "duration": "15 mins" | |
| } | |
| ] | |
| } | |
| For 'assessment_submitted': | |
| { | |
| "score": 85, | |
| "max_score": 100, | |
| "assigned_difficulty": "Intermediate", | |
| "submission_text": "User's assessment response...", | |
| "evaluation_feedback": "AI feedback on submission..." | |
| } | |
| For 'results_viewed': | |
| { | |
| "score": 85, | |
| "grade": "B+", | |
| "passed": true, | |
| "recommended_difficulty": "Intermediate", | |
| "available_difficulties": ["Beginner", "Intermediate", "Advanced"] | |
| } | |
| For 'project_generated': | |
| { | |
| "project_title": "Build a To-Do List Application", | |
| "project_description": "Create a full-featured to-do list...", | |
| "difficulty": "Intermediate", | |
| "submission_form_link": "https://forms.google.com/...", | |
| "requirements": ["Feature 1", "Feature 2"] | |
| } | |
| */ | |