Database Migrations

This document details all database migrations and schema changes in the STO Education Platform.

Migration System Overview

The platform uses Supabase for database management with SQL migration files stored in the supabase/migrations/ directory. Migrations are applied automatically during deployment and can be run manually for development.

Migration Files Structure

supabase/migrations/
├── 20240101000000_initial_schema.sql
├── 20240102000000_add_user_profiles.sql
├── 20240103000000_add_courses_system.sql
├── 20240104000000_add_sessions_system.sql
├── 20240105000000_add_payment_system.sql
├── 20240106000000_add_quiz_system.sql
├── 20240107000000_add_teacher_mark_schemes.sql
└── 20240108000000_add_analytics_tables.sql

Core Schema Migrations

Initial Schema Setup

File: 20240101000000_initial_schema.sql

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create custom types
CREATE TYPE user_role AS ENUM ('student', 'teacher', 'parent', 'trainee', 'admin');
CREATE TYPE session_status AS ENUM ('scheduled', 'ongoing', 'completed', 'cancelled');
CREATE TYPE payment_status AS ENUM ('pending', 'processing', 'completed', 'failed', 'refunded');
CREATE TYPE course_status AS ENUM ('draft', 'published', 'archived');

-- Create profiles table
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  username TEXT UNIQUE,
  full_name TEXT,
  email TEXT,
  role user_role DEFAULT 'student',
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create education_systems table
CREATE TABLE education_systems (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create subjects table
CREATE TABLE subjects (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  education_system_id UUID REFERENCES education_systems(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE education_systems ENABLE ROW LEVEL SECURITY;
ALTER TABLE subjects ENABLE ROW LEVEL SECURITY;

User Profiles System

File: 20240102000000_add_user_profiles.sql

-- Add additional profile fields
ALTER TABLE profiles ADD COLUMN phone_number TEXT;
ALTER TABLE profiles ADD COLUMN date_of_birth DATE;
ALTER TABLE profiles ADD COLUMN gender TEXT;
ALTER TABLE profiles ADD COLUMN address TEXT;
ALTER TABLE profiles ADD COLUMN bio TEXT;
ALTER TABLE profiles ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Create teacher_subjects junction table
CREATE TABLE teacher_subjects (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(teacher_id, subject_id)
);

-- Create student_parents junction table
CREATE TABLE student_parents (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  parent_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  relationship TEXT DEFAULT 'parent',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(student_id, parent_id)
);

-- Add indexes for performance
CREATE INDEX idx_profiles_role ON profiles(role);
CREATE INDEX idx_profiles_email ON profiles(email);
CREATE INDEX idx_teacher_subjects_teacher ON teacher_subjects(teacher_id);
CREATE INDEX idx_teacher_subjects_subject ON teacher_subjects(subject_id);

Courses System

File: 20240103000000_add_courses_system.sql

-- Create courses table
CREATE TABLE courses (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  slug TEXT UNIQUE NOT NULL,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id),
  education_system_id UUID REFERENCES education_systems(id),
  price DECIMAL(10,2) DEFAULT 0,
  currency TEXT DEFAULT 'EGP',
  status course_status DEFAULT 'draft',
  thumbnail_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create course_enrollments table
CREATE TABLE course_enrollments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  progress_percentage INTEGER DEFAULT 0,
  UNIQUE(course_id, student_id)
);

-- Create course_modules table
CREATE TABLE course_modules (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create course_lessons table
CREATE TABLE course_lessons (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  module_id UUID REFERENCES course_modules(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  video_url TEXT,
  duration_minutes INTEGER,
  order_index INTEGER NOT NULL,
  is_free BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_courses_teacher ON courses(teacher_id);
CREATE INDEX idx_courses_subject ON courses(subject_id);
CREATE INDEX idx_courses_status ON courses(status);
CREATE INDEX idx_enrollments_student ON course_enrollments(student_id);
CREATE INDEX idx_enrollments_course ON course_enrollments(course_id);

Sessions System

File: 20240104000000_add_sessions_system.sql

-- Create sessions table
CREATE TABLE sessions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
  duration_minutes INTEGER DEFAULT 60,
  max_participants INTEGER,
  status session_status DEFAULT 'scheduled',
  room_id TEXT,
  recording_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create session_participants table
CREATE TABLE session_participants (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  joined_at TIMESTAMP WITH TIME ZONE,
  left_at TIMESTAMP WITH TIME ZONE,
  attendance_status TEXT DEFAULT 'pending',
  UNIQUE(session_id, student_id)
);

-- Create session_attendance table
CREATE TABLE session_attendance (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  session_id UUID REFERENCES sessions(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  attended BOOLEAN DEFAULT false,
  attendance_time TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(session_id, student_id)
);

-- Add indexes
CREATE INDEX idx_sessions_teacher ON sessions(teacher_id);
CREATE INDEX idx_sessions_course ON sessions(course_id);
CREATE INDEX idx_sessions_scheduled ON sessions(scheduled_at);
CREATE INDEX idx_participants_session ON session_participants(session_id);
CREATE INDEX idx_attendance_student ON session_attendance(student_id);

Payment System

File: 20240105000000_add_payment_system.sql

-- Create orders table
CREATE TABLE orders (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  order_type TEXT NOT NULL, -- 'course', 'training', 'mark_scheme'
  reference_id UUID, -- ID of the item being purchased
  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  status payment_status DEFAULT 'pending',
  payment_method TEXT,
  payment_reference TEXT,
  paymob_order_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create payments table
CREATE TABLE payments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  amount DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  status payment_status DEFAULT 'pending',
  payment_method TEXT,
  transaction_id TEXT,
  gateway_response JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create training_enrollments table
CREATE TABLE training_enrollments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  trainee_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  progress_percentage INTEGER DEFAULT 0,
  UNIQUE(course_id, trainee_id)
);

-- Add indexes
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_type ON orders(order_type);
CREATE INDEX idx_payments_order ON payments(order_id);
CREATE INDEX idx_training_enrollments_trainee ON training_enrollments(trainee_id);

Quiz System

File: 20240106000000_add_quiz_system.sql

-- Create quizzes table
CREATE TABLE quizzes (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  time_limit_minutes INTEGER,
  max_attempts INTEGER DEFAULT 3,
  passing_score INTEGER DEFAULT 70,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create quiz_questions table
CREATE TABLE quiz_questions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type TEXT NOT NULL, -- 'multiple_choice', 'true_false', 'essay'
  options JSONB, -- For multiple choice questions
  correct_answer TEXT,
  points INTEGER DEFAULT 1,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create quiz_attempts table
CREATE TABLE quiz_attempts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  score INTEGER,
  total_points INTEGER,
  is_passed BOOLEAN,
  attempt_number INTEGER DEFAULT 1
);

-- Create quiz_responses table
CREATE TABLE quiz_responses (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  attempt_id UUID REFERENCES quiz_attempts(id) ON DELETE CASCADE,
  question_id UUID REFERENCES quiz_questions(id) ON DELETE CASCADE,
  answer TEXT,
  is_correct BOOLEAN,
  points_earned INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_quizzes_course ON quizzes(course_id);
CREATE INDEX idx_quizzes_teacher ON quizzes(teacher_id);
CREATE INDEX idx_questions_quiz ON quiz_questions(quiz_id);
CREATE INDEX idx_attempts_quiz ON quiz_attempts(quiz_id);
CREATE INDEX idx_attempts_student ON quiz_attempts(student_id);
CREATE INDEX idx_responses_attempt ON quiz_responses(attempt_id);

Teacher Mark Schemes

File: 20240107000000_add_teacher_mark_schemes.sql

-- Create teacher_mark_schemes table
CREATE TABLE teacher_mark_schemes (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  subject_id UUID REFERENCES subjects(id) ON DELETE CASCADE,
  original_filename TEXT NOT NULL,
  file_url TEXT NOT NULL,
  exam_category TEXT NOT NULL, -- 'igcse', 'as_level', 'a_level', etc.
  paper_year TEXT NOT NULL,
  paper_season TEXT NOT NULL, -- 'summer', 'winter', 'march'
  paper_category TEXT NOT NULL, -- 'paper_1', 'paper_2', etc.
  teacher_explanation TEXT,
  price DECIMAL(10,2) NOT NULL,
  currency TEXT DEFAULT 'EGP',
  is_published BOOLEAN DEFAULT false,
  download_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create teacher_mark_scheme_purchases table
CREATE TABLE teacher_mark_scheme_purchases (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  mark_scheme_id UUID REFERENCES teacher_mark_schemes(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  purchased_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  download_count INTEGER DEFAULT 0,
  last_downloaded_at TIMESTAMP WITH TIME ZONE
);

-- Create assignments table
CREATE TABLE assignments (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  teacher_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  due_date TIMESTAMP WITH TIME ZONE,
  max_points INTEGER DEFAULT 100,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create assignment_submissions table
CREATE TABLE assignment_submissions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  assignment_id UUID REFERENCES assignments(id) ON DELETE CASCADE,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  submission_text TEXT,
  file_url TEXT,
  submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  graded_at TIMESTAMP WITH TIME ZONE,
  grade INTEGER,
  feedback TEXT,
  is_late BOOLEAN DEFAULT false
);

-- Add indexes
CREATE INDEX idx_mark_schemes_teacher ON teacher_mark_schemes(teacher_id);
CREATE INDEX idx_mark_schemes_subject ON teacher_mark_schemes(subject_id);
CREATE INDEX idx_mark_schemes_category ON teacher_mark_schemes(exam_category);
CREATE INDEX idx_purchases_student ON teacher_mark_scheme_purchases(student_id);
CREATE INDEX idx_assignments_course ON assignments(course_id);
CREATE INDEX idx_submissions_student ON assignment_submissions(student_id);

Analytics and Monitoring

File: 20240108000000_add_analytics_tables.sql

-- Create user_analytics table
CREATE TABLE user_analytics (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  event_data JSONB,
  session_id TEXT,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create page_views table
CREATE TABLE page_views (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  page_url TEXT NOT NULL,
  page_title TEXT,
  referrer TEXT,
  session_id TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create mochi_scores table
CREATE TABLE mochi_scores (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  student_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  overall_score INTEGER CHECK (overall_score >= 0 AND overall_score <= 100),
  level INTEGER CHECK (level >= 1 AND level <= 5),
  mood TEXT CHECK (mood IN ('happy', 'neutral', 'sad')),
  calculation_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create blog_posts table
CREATE TABLE blog_posts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  content TEXT NOT NULL,
  excerpt TEXT,
  author_id UUID REFERENCES profiles(id) ON DELETE CASCADE,
  featured_image_url TEXT,
  is_published BOOLEAN DEFAULT false,
  published_at TIMESTAMP WITH TIME ZONE,
  view_count INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create page_content table
CREATE TABLE page_content (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  page_key TEXT UNIQUE NOT NULL,
  title TEXT,
  content TEXT NOT NULL,
  metadata JSONB,
  updated_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes
CREATE INDEX idx_analytics_user ON user_analytics(user_id);
CREATE INDEX idx_analytics_event ON user_analytics(event_type);
CREATE INDEX idx_page_views_user ON page_views(user_id);
CREATE INDEX idx_page_views_url ON page_views(page_url);
CREATE INDEX idx_mochi_scores_student ON mochi_scores(student_id);
CREATE INDEX idx_mochi_scores_course ON mochi_scores(course_id);
CREATE INDEX idx_blog_posts_author ON blog_posts(author_id);
CREATE INDEX idx_blog_posts_published ON blog_posts(is_published);
CREATE INDEX idx_page_content_key ON page_content(page_key);

Row Level Security (RLS) Policies

Profiles Table Policies

-- Users can view their own profile
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles" ON profiles
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

Courses Table Policies

-- Anyone can view published courses
CREATE POLICY "Anyone can view published courses" ON courses
  FOR SELECT USING (status = 'published');

-- Teachers can manage their own courses
CREATE POLICY "Teachers can manage own courses" ON courses
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = auth.uid() AND role = 'teacher'
    ) AND teacher_id = auth.uid()
  );

Sessions Table Policies

-- Students can view sessions for enrolled courses
CREATE POLICY "Students can view enrolled course sessions" ON sessions
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM course_enrollments 
      WHERE course_id = sessions.course_id 
      AND student_id = auth.uid()
    )
  );

-- Teachers can manage their own sessions
CREATE POLICY "Teachers can manage own sessions" ON sessions
  FOR ALL USING (teacher_id = auth.uid());

Functions and Triggers

Updated At Trigger

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply trigger to relevant tables
CREATE TRIGGER update_profiles_updated_at 
  BEFORE UPDATE ON profiles 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_courses_updated_at 
  BEFORE UPDATE ON courses 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_sessions_updated_at 
  BEFORE UPDATE ON sessions 
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Mochi Score Calculation Function

-- Function to calculate Mochi score
CREATE OR REPLACE FUNCTION calculate_mochi_score(
  p_student_id UUID,
  p_course_id UUID
) RETURNS TABLE(
  overall_score INTEGER,
  level INTEGER,
  mood TEXT
) AS $$
DECLARE
  v_assignment_score NUMERIC;
  v_attendance_score NUMERIC;
  v_quiz_score NUMERIC;
  v_final_score INTEGER;
  v_level INTEGER;
  v_mood TEXT;
BEGIN
  -- Calculate assignment score (40% weight)
  SELECT COALESCE(AVG(grade), 0) INTO v_assignment_score
  FROM assignment_submissions 
  WHERE student_id = p_student_id 
  AND assignment_id IN (
    SELECT id FROM assignments WHERE course_id = p_course_id
  );

  -- Calculate attendance score (30% weight)
  SELECT COALESCE(
    (COUNT(*) FILTER (WHERE attended = true)::NUMERIC / 
     NULLIF(COUNT(*), 0)) * 100, 0
  ) INTO v_attendance_score
  FROM session_attendance sa
  JOIN sessions s ON sa.session_id = s.id
  WHERE sa.student_id = p_student_id 
  AND s.course_id = p_course_id;

  -- Calculate quiz score (30% weight)
  SELECT COALESCE(AVG(score), 0) INTO v_quiz_score
  FROM quiz_attempts qa
  JOIN quizzes q ON qa.quiz_id = q.id
  WHERE qa.student_id = p_student_id 
  AND q.course_id = p_course_id;

  -- Calculate final score
  v_final_score := ROUND(
    (v_assignment_score * 0.4) + 
    (v_attendance_score * 0.3) + 
    (v_quiz_score * 0.3)
  );

  -- Determine level (1-5)
  v_level := CASE
    WHEN v_final_score >= 90 THEN 5
    WHEN v_final_score >= 75 THEN 4
    WHEN v_final_score >= 60 THEN 3
    WHEN v_final_score >= 40 THEN 2
    ELSE 1
  END;

  -- Determine mood
  v_mood := CASE
    WHEN v_final_score >= 80 THEN 'happy'
    WHEN v_final_score >= 50 THEN 'neutral'
    ELSE 'sad'
  END;

  RETURN QUERY SELECT v_final_score, v_level, v_mood;
END;
$$ LANGUAGE plpgsql;

Migration Management

Running Migrations

# Run all pending migrations
npm run run-migrations

# Or using Supabase CLI
supabase db reset
supabase db push

Migration Best Practices

Atomic Changes: Each migration should be atomic and reversible
Naming Convention: Use timestamp prefix for ordering
Backward Compatibility: Ensure migrations don’t break existing data
Testing: Test migrations on staging environment first
Rollback Plan: Always have a rollback strategy

Migration Scripts

// scripts/db-migration-runner.js
const { createClient } = require('@supabase/supabase-js')

async function runMigrations() {
  const supabase = createClient(
    process.env.VITE_SUPABASE_URL,
    process.env.VITE_SUPABASE_SERVICE_ROLE_KEY
  )
  
  const migrationsDir = path.join(__dirname, '../supabase/migrations')
  const migrationFiles = fs.readdirSync(migrationsDir)
    .filter(file => file.endsWith('.sql'))
    .sort()
  
  for (const file of migrationFiles) {
    const migrationPath = path.join(migrationsDir, file)
    const sql = fs.readFileSync(migrationPath, 'utf8')
    
    const { error } = await supabase.rpc('exec_sql', { sql })
    
    if (error) {
      console.error(`Migration failed: ${file}`, error)
      process.exit(1)
    }
    
    console.log(`Migration completed: ${file}`)
  }
}

Database Schema - Complete database structure
Supabase Functions - Edge functions and server logic
Utility Scripts - Migration and backup scripts
Backend Development - Backend architecture and services

Getting Started

Frontend Development

Backend Development

Configuration & Setup

Third-Party Integrations

Scripts & Automation

Public Assets & Resources

Database & Migrations

Documentation Files

Database migrations

Database Migrations

Migration System Overview

Migration Files Structure

Core Schema Migrations

Initial Schema Setup

User Profiles System

Courses System

Sessions System

Payment System

Quiz System

Teacher Mark Schemes

Analytics and Monitoring

Row Level Security (RLS) Policies

Profiles Table Policies

Courses Table Policies

Sessions Table Policies

Functions and Triggers

Updated At Trigger

Mochi Score Calculation Function

Migration Management

Running Migrations

Migration Best Practices

Migration Scripts

Getting Started

Frontend Development

Backend Development

Configuration & Setup

Third-Party Integrations

Scripts & Automation

Public Assets & Resources

Database & Migrations

Documentation Files

​Database Migrations

​Migration System Overview

​Migration Files Structure

​Core Schema Migrations

​Initial Schema Setup

​User Profiles System

​Courses System

​Sessions System

​Payment System

​Quiz System

​Teacher Mark Schemes

​Analytics and Monitoring

​Row Level Security (RLS) Policies

​Profiles Table Policies

​Courses Table Policies

​Sessions Table Policies

​Functions and Triggers

​Updated At Trigger

​Mochi Score Calculation Function

​Migration Management

​Running Migrations

​Migration Best Practices

​Migration Scripts

​Related Documentation

Database Migrations

Migration System Overview

Migration Files Structure

Core Schema Migrations

Initial Schema Setup

User Profiles System

Courses System

Sessions System

Payment System

Quiz System

Teacher Mark Schemes

Analytics and Monitoring

Row Level Security (RLS) Policies

Profiles Table Policies

Courses Table Policies

Sessions Table Policies

Functions and Triggers

Updated At Trigger

Mochi Score Calculation Function

Migration Management

Running Migrations

Migration Best Practices

Migration Scripts

Related Documentation