-- ============================================================ -- GeoVic EduPro — School Timetable Management & Generation System -- Phase 1 schema: Foundation (auth, school setup, academic structure, -- teachers, subjects, rooms, periods) + stub tables for the scheduling -- engine (Phase 2/3) so nothing needs restructuring later. -- -- Multi-tenancy model: ONE shared database, every tenant-owned table -- carries school_id. super_admin users have school_id = NULL (platform -- level); every other role is scoped to exactly one school. -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ------------------------------------------------------------ -- 1. PLATFORM / SCHOOLS -- ------------------------------------------------------------ CREATE TABLE schools ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, code VARCHAR(20) NOT NULL UNIQUE, -- short slug, e.g. LASALLE-HB school_type VARCHAR(50) NULL, -- primary, secondary, mixed, etc. location VARCHAR(150) NULL, logo_path VARCHAR(255) NULL, primary_color VARCHAR(7) NULL, -- branding hex, e.g. #43190D timezone VARCHAR(50) NOT NULL DEFAULT 'Africa/Nairobi', subscription_plan ENUM('trial','basic','pro','enterprise') NOT NULL DEFAULT 'trial', subscription_status ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active', trial_ends_at DATE NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 2. USERS & ROLES (RBAC) -- ------------------------------------------------------------ CREATE TABLE users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NULL, -- NULL only for super_admin first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, password_hash VARCHAR(255) NOT NULL, role ENUM( 'super_admin', 'school_admin', 'timetable_admin', 'principal', 'deputy_principal', 'hod', 'teacher', 'student', 'parent', 'auditor' ) NOT NULL, status ENUM('active','suspended') NOT NULL DEFAULT 'active', last_login_at TIMESTAMP NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, UNIQUE KEY uniq_school_email (school_id, email), CONSTRAINT fk_users_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 3. ACADEMIC CALENDAR -- ------------------------------------------------------------ CREATE TABLE academic_years ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, year_name VARCHAR(20) NOT NULL, -- e.g. "2026" start_date DATE NOT NULL, end_date DATE NOT NULL, is_current TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_ay_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE terms ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, academic_year_id INT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- e.g. "Term 1" start_date DATE NOT NULL, end_date DATE NOT NULL, is_current TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_term_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_term_ay FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 4. ACADEMIC STRUCTURE (levels -> classes -> streams) -- ------------------------------------------------------------ CREATE TABLE academic_levels ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- e.g. "Primary", "Secondary" sort_order INT NOT NULL DEFAULT 0, CONSTRAINT fk_level_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE classes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, academic_level_id INT UNSIGNED NULL, name VARCHAR(50) NOT NULL, -- e.g. "Grade 6" sort_order INT NOT NULL DEFAULT 0, CONSTRAINT fk_class_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_class_level FOREIGN KEY (academic_level_id) REFERENCES academic_levels(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE streams ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, class_id INT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- e.g. "6A" capacity INT NULL, CONSTRAINT fk_stream_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_stream_class FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 5. SUBJECTS -- ------------------------------------------------------------ CREATE TABLE subjects ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, code VARCHAR(20) NULL, department VARCHAR(100) NULL, lessons_per_week INT NOT NULL DEFAULT 1, lesson_duration_minutes INT NOT NULL DEFAULT 40, requires_room TINYINT(1) NOT NULL DEFAULT 0, -- needs a specific/specialized room requires_lab TINYINT(1) NOT NULL DEFAULT 0, allow_double_lesson TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_subject_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 6. TEACHERS -- ------------------------------------------------------------ CREATE TABLE teachers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, -- linked login account, if any first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(150) NULL, phone VARCHAR(30) NULL, department VARCHAR(100) NULL, employment_type VARCHAR(50) NULL, -- full-time, part-time, visiting max_lessons_per_day INT NULL, max_lessons_per_week INT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at TIMESTAMP NULL, CONSTRAINT fk_teacher_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_teacher_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE teacher_subjects ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, teacher_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL, CONSTRAINT fk_ts_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE, CONSTRAINT fk_ts_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE, UNIQUE KEY uniq_teacher_subject (teacher_id, subject_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Which teacher teaches which subject to which stream — this is the seed -- data the scheduling engine (Phase 2) turns into lesson requirements. CREATE TABLE teacher_stream_subjects ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, teacher_id INT UNSIGNED NOT NULL, stream_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL, CONSTRAINT fk_tss_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_tss_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE, CONSTRAINT fk_tss_stream FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE, CONSTRAINT fk_tss_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE, UNIQUE KEY uniq_stream_subject (stream_id, subject_id) -- one teacher per subject per stream ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE teacher_availability ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, teacher_id INT UNSIGNED NOT NULL, day_of_week TINYINT UNSIGNED NOT NULL, -- 1=Mon .. 7=Sun period_id INT UNSIGNED NULL, -- NULL = applies to whole day is_available TINYINT(1) NOT NULL DEFAULT 1, reason VARCHAR(150) NULL, CONSTRAINT fk_ta_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 7. ROOMS & RESOURCES -- ------------------------------------------------------------ CREATE TABLE rooms ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, room_type ENUM('classroom','laboratory','computer_lab','library','field','pool','music_room','art_room','other') NOT NULL DEFAULT 'classroom', capacity INT NULL, equipment TEXT NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', CONSTRAINT fk_room_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE room_availability ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_id INT UNSIGNED NOT NULL, day_of_week TINYINT UNSIGNED NOT NULL, period_id INT UNSIGNED NULL, is_available TINYINT(1) NOT NULL DEFAULT 1, CONSTRAINT fk_ra_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 8. PERIODS & SCHOOL DAYS -- ------------------------------------------------------------ CREATE TABLE periods ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, -- e.g. "Period 1", "Break" start_time TIME NOT NULL, end_time TIME NOT NULL, sort_order INT NOT NULL DEFAULT 0, period_type ENUM('lesson','break','lunch','registration','activity') NOT NULL DEFAULT 'lesson', is_teaching_period TINYINT(1) NOT NULL DEFAULT 1, CONSTRAINT fk_period_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE school_days ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, day_of_week TINYINT UNSIGNED NOT NULL, -- 1=Mon .. 7=Sun is_active TINYINT(1) NOT NULL DEFAULT 1, CONSTRAINT fk_sd_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, UNIQUE KEY uniq_school_day (school_id, day_of_week) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE school_activities ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, -- e.g. "Assembly", "Games" day_of_week TINYINT UNSIGNED NOT NULL, period_id INT UNSIGNED NOT NULL, applies_to ENUM('whole_school','class','stream') NOT NULL DEFAULT 'whole_school', target_id INT UNSIGNED NULL, -- class_id or stream_id, depending on applies_to CONSTRAINT fk_sa_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_sa_period FOREIGN KEY (period_id) REFERENCES periods(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 9. LESSON REQUIREMENTS (bridges setup -> scheduling engine) -- ------------------------------------------------------------ CREATE TABLE lesson_requirements ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, term_id INT UNSIGNED NOT NULL, stream_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL, teacher_id INT UNSIGNED NOT NULL, lessons_per_week INT NOT NULL DEFAULT 1, allow_double_lesson TINYINT(1) NOT NULL DEFAULT 0, required_room_type VARCHAR(50) NULL, -- matches rooms.room_type, NULL = any classroom CONSTRAINT fk_lr_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_lr_term FOREIGN KEY (term_id) REFERENCES terms(id) ON DELETE CASCADE, CONSTRAINT fk_lr_stream FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE, CONSTRAINT fk_lr_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE, CONSTRAINT fk_lr_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE, UNIQUE KEY uniq_term_stream_subject (term_id, stream_id, subject_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 10. CONSTRAINTS (generic config the scheduling engine reads) -- ------------------------------------------------------------ CREATE TABLE constraint_rules ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, rule_type ENUM('hard','soft') NOT NULL DEFAULT 'hard', rule_key VARCHAR(100) NOT NULL, -- e.g. "max_consecutive_lessons" rule_value JSON NULL, priority TINYINT UNSIGNED NOT NULL DEFAULT 1, -- 1 = highest description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, CONSTRAINT fk_cr_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 11. TIMETABLES (stub for Phase 2/3 — safe to have empty for now) -- ------------------------------------------------------------ CREATE TABLE timetables ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NOT NULL, academic_year_id INT UNSIGNED NOT NULL, term_id INT UNSIGNED NOT NULL, version INT NOT NULL DEFAULT 1, status ENUM('draft','review','approved','published') NOT NULL DEFAULT 'draft', created_by INT UNSIGNED NULL, approved_by INT UNSIGNED NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, published_at TIMESTAMP NULL, CONSTRAINT fk_tt_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_tt_term FOREIGN KEY (term_id) REFERENCES terms(id) ON DELETE CASCADE, CONSTRAINT fk_tt_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_tt_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE timetable_entries ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, timetable_id INT UNSIGNED NOT NULL, day_of_week TINYINT UNSIGNED NOT NULL, period_id INT UNSIGNED NOT NULL, stream_id INT UNSIGNED NOT NULL, subject_id INT UNSIGNED NOT NULL, teacher_id INT UNSIGNED NOT NULL, room_id INT UNSIGNED NULL, lesson_type ENUM('single','double') NOT NULL DEFAULT 'single', status ENUM('scheduled','conflict') NOT NULL DEFAULT 'scheduled', CONSTRAINT fk_te_timetable FOREIGN KEY (timetable_id) REFERENCES timetables(id) ON DELETE CASCADE, CONSTRAINT fk_te_period FOREIGN KEY (period_id) REFERENCES periods(id) ON DELETE CASCADE, CONSTRAINT fk_te_stream FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE, CONSTRAINT fk_te_subject FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE, CONSTRAINT fk_te_teacher FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE, CONSTRAINT fk_te_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------ -- 12. AUDIT LOG (same pattern as the La Salle system) -- ------------------------------------------------------------ CREATE TABLE audit_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, school_id INT UNSIGNED NULL, -- NULL for platform-level (super_admin) actions user_id INT UNSIGNED NULL, event VARCHAR(150) NOT NULL, auditable_type VARCHAR(100) NULL, auditable_id INT UNSIGNED NULL, meta JSON NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_audit_school FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE, CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1;