185 lines
15 KiB
Markdown
185 lines
15 KiB
Markdown
# Africa Alert PWA - Database Schema
|
|
|
|
This document outlines the SQLite database schema for the Africa Alert PWA.
|
|
|
|
## Core Modules
|
|
|
|
### Users & Authentication
|
|
- **users**: Core user accounts (admin, teacher, student, parent, etc.)
|
|
- `id`, `uid`, `email`, `password`, `role`, `first_name`, `last_name`, `phone`, `address`, `date_of_birth`, `gender`, `id_number`, `profile_image`, `emergency_contact`, `emergency_phone`, `department_id`, `is_active`, `last_login`, `created_at`, `updated_at`, `last_synced_at`, `sync_status`, `is_deleted`
|
|
|
|
### Academic Structure
|
|
- **departments**: Academic departments
|
|
- `id`, `uid`, `name`, `head_teacher_id`, `description`, ... (sync fields)
|
|
- **classes**: Grade levels/sections
|
|
- `id`, `uid`, `name`, `section`, `capacity`, `class_teacher_id`, `description`, ... (sync fields)
|
|
- **subjects_new** (Replaces `subjects`): Academic subjects
|
|
- `id`, `uid`, `name`, `code`, `department_id`, `teacher_id`, `credits`, `description`, `class_id`, ... (sync fields)
|
|
- **courses**: Specific course offerings
|
|
- `id`, `uid`, `name`, `subject_id`, `teacher_id`, `description`, `schedule`, `start_date`, `end_date`, `max_students`, `status`, ... (sync fields)
|
|
- **course_enrolments**: Student enrollment in courses
|
|
- `id`, `uid`, `student_id`, `course_id`, `enrolled_at`, `status`, `progress`, `completed_at`, ... (sync fields)
|
|
|
|
### Attendance & Grading
|
|
- **attendance_new** (Replaces `attendance`): Student attendance records
|
|
- `id`, `uid`, `student_id`, `subject_id`, `date`, `status`, `remarks`, `marked_by`, ... (sync fields)
|
|
- **assignments**: Course assignments
|
|
- `id`, `uid`, `course_id`, `title`, `description`, `instructions`, `due_date`, `max_score`, `submission_type`, `allow_late_submission`, `is_published`, ... (sync fields)
|
|
- **submissions**: Assignment submissions
|
|
- `id`, `uid`, `assignment_id`, `student_id`, `content`, `file_path`, `submitted_at`, `status`, `grade`, `feedback`, `graded_by`, `graded_at`, ... (sync fields)
|
|
- **assignment_submissions**: Detailed assignment submissions
|
|
- `id`, `uid`, `assignment_id`, `student_id`, `submission_text`, `file_path`, `submitted_at`, `status`, `marks_obtained`, `feedback`, `graded_by`, `graded_at`, ... (sync fields)
|
|
- **course_grades**: Final course grades
|
|
- `id`, `uid`, `student_id`, `course_id`, `score`, `letter_grade`, `term`, `academic_year`, `remarks`, `graded_by`, `graded_at`, ... (sync fields)
|
|
- **grades**: General exam grades
|
|
- `id`, `uid`, `student_id`, `subject_id`, `exam_type`, `marks`, `total_marks`, `grade`, `remarks`, `graded_by`, `graded_at`, `academic_year`, `term`, ... (sync fields)
|
|
|
|
### Finance & Payroll
|
|
- **fee_groups**: Categories of fees
|
|
- `id`, `uid`, `name`, `description`, `amount`, `type`, `frequency`, `academic_year`, `due_date`, ... (sync fields)
|
|
- **student_fees**: Fees assigned to students
|
|
- `id`, `uid`, `student_id`, `fee_group_id`, `amount`, `paid_amount`, `discount_amount`, `fine_amount`, `status`, `due_date`, `paid_at`, ... (sync fields)
|
|
- **payments**: Payment transactions
|
|
- `id`, `uid`, `student_fee_id`, `amount`, `payment_date`, `payment_method`, `reference_number`, `transaction_id`, `paynow_poll_url`, `collected_by`, `notes`, ... (sync fields)
|
|
- **expenses**: School expenses
|
|
- `id`, `uid`, `category`, `subcategory`, `description`, `amount`, `expense_date`, `vendor`, `invoice_number`, `payment_method`, `created_by`, `approved_by`, `receipt_image`, `status`, ... (sync fields)
|
|
- **payroll_runs**: Monthly payroll processing
|
|
- `id`, `uid`, `period_month`, `period_year`, `description`, `status`, `total_gross`, `total_deductions`, `total_net`, `approved_by`, `paid_at`, `created_by`, ... (sync fields)
|
|
- **payslips**: Individual staff payslips
|
|
- `id`, `uid`, `staff_id`, `payroll_run_id`, `basic_salary`, `housing_allowance`, `transport_allowance`, `medical_allowance`, `other_allowances`, `gross_salary`, `tax_deduction`, `pension_deduction`, `other_deductions`, `total_deductions`, `net_salary`, `bank_reference`, `paid_at`, `status`, ... (sync fields)
|
|
|
|
### HR & Staff Management
|
|
- **staff_roles**: Staff positions/titles
|
|
- `id`, `uid`, `name`, `description`, `is_teaching_role`, `permissions`, ... (sync fields)
|
|
- **salary_grades**: Salary structure levels
|
|
- `id`, `uid`, `name`, `grade_level`, `basic_salary`, `housing_allowance`, `transport_allowance`, `medical_allowance`, `other_allowances`, `description`, `effective_date`, ... (sync fields)
|
|
- **staff_records**: Employment details
|
|
- `id`, `uid`, `user_id`, `staff_number`, `role_id`, `salary_grade_id`, `employment_type`, `appointment_date`, `confirmation_date`, `termination_date`, `termination_reason`, `bank_name`, `bank_branch`, `account_number`, `account_name`, `department`, `designation`, `reports_to`, `is_active`, ... (sync fields)
|
|
- **leave_types**: Categories of leave
|
|
- `id`, `uid`, `name`, `code`, `description`, `max_days_per_year`, `is_paid`, `requires_approval`, `color`, ... (sync fields)
|
|
- **leave_requests**: Staff leave applications
|
|
- `id`, `uid`, `staff_id`, `leave_type_id`, `start_date`, `end_date`, `days_count`, `reason`, `status`, `approved_by`, `approved_at`, `rejection_reason`, ... (sync fields)
|
|
- **staff_attendance**: Staff daily attendance
|
|
- `id`, `uid`, `staff_id`, `date`, `check_in`, `check_out`, `status`, `remarks`, `marked_by`, ... (sync fields)
|
|
|
|
### Teacher Tools & Student Profile
|
|
- **syllabuses**: Course syllabuses
|
|
- `id`, `uid`, `subject_id`, `title`, `description`, `file_path`, `version`, `is_active`, `created_by`, ... (sync fields)
|
|
- **schemes_of_work**: Teaching schemes (Schemas)
|
|
- `id`, `uid`, `subject_id`, `teacher_id`, `term`, `academic_year`, `content`, `status`, `approved_by`, ... (sync fields)
|
|
- **focus_points**: Key topics within a scheme
|
|
- `id`, `uid`, `scheme_id`, `topic`, `objectives`, `key_concepts`, `resources_needed`, ... (sync fields)
|
|
- **lesson_plans**: Daily lesson plans
|
|
- `id`, `uid`, `teacher_id`, `subject_id`, `scheme_id`, `focus_point_id`, `date`, `topic`, `objectives`, `introduction`, `development`, `conclusion`, `assessment_method`, `homework`, `reflections`, `status`, ... (sync fields)
|
|
- **student_social_docs**: Extended student profile/social documentation
|
|
- `id`, `uid`, `student_id`, `learner_type`, `past_behavior`, `family_size`, `parents_available`, `home_environment`, `medical_notes`, `social_strengths`, `social_challenges`, `interests`, `other_data`, `updated_by`, ... (sync fields)
|
|
|
|
### Inventory & Store
|
|
- **item_categories**: Inventory categories
|
|
- `id`, `uid`, `name`, `code`, `description`, `parent_id`, ... (sync fields)
|
|
- **suppliers**: Inventory suppliers
|
|
- `id`, `uid`, `name`, `code`, `contact_person`, `email`, `phone`, `address`, `city`, `country`, `tax_id`, `bank_details`, `notes`, `is_active`, ... (sync fields)
|
|
- **store_locations**: Physical store/warehouse locations
|
|
- `id`, `uid`, `name`, `code`, `description`, `address`, `manager_id`, `is_warehouse`, ... (sync fields)
|
|
- **items**: Inventory items
|
|
- `id`, `uid`, `name`, `code`, `barcode`, `description`, `category_id`, `unit`, `purchase_price`, `selling_price`, `reorder_level`, `expiry_tracking`, `serial_tracking`, `supplier_id`, `is_active`, ... (sync fields)
|
|
- **item_stock**: Current stock levels per location
|
|
- `id`, `uid`, `item_id`, `store_id`, `quantity`, `min_quantity`, `max_quantity`, `last_stocktake`, ... (sync fields)
|
|
- **stock_transactions**: Stock movements (purchase, sale, transfer, etc.)
|
|
- `id`, `uid`, `item_id`, `store_id`, `type`, `quantity`, `unit_price`, `total_amount`, `reference_number`, `supplier_id`, `transaction_date`, `notes`, `created_by`, ... (sync fields)
|
|
- **item_issues**: Items issued to staff/students
|
|
- `id`, `uid`, `item_id`, `issued_to_type`, `issued_to_id`, `quantity`, `purpose`, `issue_date`, `return_due_date`, `returned_date`, `returned_quantity`, `condition_on_return`, `issued_by`, `status`, `notes`, ... (sync fields)
|
|
|
|
### Student Medical Records (feature/student-medical-records)
|
|
- **student_medical_profiles** (one row per student): blood group, allergies, dietary_restrictions, contraindications, chronic_conditions, emergency_instructions, updated_by, ... (sync fields)
|
|
- **student_medication_logs**: school-administered medications; `medication_name`, `dosage`, `administered_at`, `administered_by`, `reason`, `notes`, ... (sync fields)
|
|
- **student_medical_history**: illness / injury / hospitalization events with severity, onset/resolution dates, treatment details, private `doctor_notes`, and `is_visible_to_teachers` flag (controls teacher visibility row-by-row), ... (sync fields)
|
|
|
|
### Online Exams
|
|
- **exam_groups**: Grouping of exams/tests
|
|
- `id`, `uid`, `name`, `description`, `exam_type`, `duration_minutes`, `total_marks`, `passing_marks`, `is_random_order`, `show_results`, `allow_review`, `max_attempts`, `academic_year`, `term`, ... (sync fields)
|
|
- **question_banks**: Pool of exam questions
|
|
- `id`, `uid`, `exam_group_id`, `subject_id`, `question_type`, `question`, `options`, `correct_answer`, `marks`, `explanation`, `difficulty`, `tags`, ... (sync fields)
|
|
- **exam_schedules**: When exams are scheduled for specific classes
|
|
- `id`, `uid`, `exam_group_id`, `class_id`, `subject_id`, `start_time`, `end_time`, `duration_minutes`, `instructions`, `is_active`, ... (sync fields)
|
|
- **exam_attempts**: Student exam results/attempts
|
|
- `id`, `uid`, `student_id`, `exam_group_id`, `schedule_id`, `started_at`, `submitted_at`, `time_spent_seconds`, `total_marks`, `obtained_marks`, `percentage`, `status`, `ip_address`, `user_agent`, ... (sync fields)
|
|
- **exam_answers**: Individual answers in an attempt
|
|
- `id`, `uid`, `attempt_id`, `question_id`, `answer`, `is_correct`, `marks_obtained`, `answered_at`, ... (sync fields)
|
|
|
|
### Hostel & Transport
|
|
- **hostels**: Hostel buildings
|
|
- `id`, `uid`, `name`, `code`, `type`, `address`, `warden_id`, `phone`, `description`, `total_rooms`, `capacity`, `is_active`, ... (sync fields)
|
|
- **room_types**: Types of rooms (single, shared, etc.)
|
|
- `id`, `uid`, `name`, `code`, `description`, `bed_count`, `price_per_semester`, `facilities`, ... (sync fields)
|
|
- **rooms**: Individual rooms in hostels
|
|
- `id`, `uid`, `hostel_id`, `room_type_id`, `room_number`, `floor`, `bed_count`, `status`, `description`, ... (sync fields)
|
|
- **room_assignments**: Student room assignments
|
|
- `id`, `uid`, `student_id`, `room_id`, `bed_number`, `start_date`, `end_date`, `status`, `remarks`, `assigned_by`, ... (sync fields)
|
|
- **hostel_fees**: Hostel-specific fees
|
|
- `id`, `uid`, `student_id`, `room_id`, `amount`, `period`, `due_date`, `paid_amount`, `status`, `paid_at`, ... (sync fields)
|
|
- **vehicles**: School vehicles
|
|
- `id`, `uid`, `registration_number`, `model`, `make`, `year`, `color`, `capacity`, `vehicle_type`, `fuel_type`, `insurance_expiry`, `roadworthy_expiry`, `status`, `last_service_date`, `next_service_date`, ... (sync fields)
|
|
- **routes**: Transport routes
|
|
- `id`, `uid`, `name`, `code`, `description`, `start_point`, `end_point`, `distance_km`, `estimated_time_minutes`, `status`, ... (sync fields)
|
|
- **pickup_points**: Stops along a route
|
|
- `id`, `uid`, `route_id`, `name`, `address`, `latitude`, `longitude`, `pickup_time`, `dropoff_time`, `sequence_order`, ... (sync fields)
|
|
- **vehicle_routes**: Assignment of vehicles to routes
|
|
- `id`, `uid`, `vehicle_id`, `route_id`, `driver_id`, `conductor_id`, `start_time`, `end_time`, `days_of_week`, `is_active`, ... (sync fields)
|
|
- **transport_allocations**: Students assigned to transport
|
|
- `id`, `uid`, `student_id`, `route_id`, `pickup_point_id`, `vehicle_route_id`, `fee_amount`, `is_active`, `effective_from`, `effective_to`, ... (sync fields)
|
|
|
|
### Front Office & Communication
|
|
- **admission_enquiries**: New student enquiries
|
|
- `id`, `uid`, `full_name`, `email`, `phone`, `address`, `class_interested`, `source`, `enquiry_date`, `follow_up_date`, `status`, `assigned_to`, `notes`, `next_action`, ... (sync fields)
|
|
- **visitor_logs**: School visitor records
|
|
- `id`, `uid`, `visitor_name`, `visitor_type`, `phone`, `email`, `id_number`, `purpose`, `person_to_visit`, `check_in`, `check_out`, `badge_number`, `remarks`, `created_by`, ... (sync fields)
|
|
- **phone_call_logs**: Log of phone communications
|
|
- `id`, `uid`, `caller_name`, `caller_phone`, `caller_type`, `direction`, `call_time`, `duration_seconds`, `purpose`, `response`, `follow_up_required`, `follow_up_date`, `handled_by`, `notes`, ... (sync fields)
|
|
- **postal_dispatch**: Incoming/Outgoing mail log
|
|
- `id`, `uid`, `type`, `reference_number`, `sender`, `receiver`, `address`, `courier`, `tracking_number`, `dispatch_date`, `received_date`, `received_by`, `description`, `status`, `created_by`, ... (sync fields)
|
|
- **complaints**: School complaint management
|
|
- `id`, `uid`, `complainant_name`, `complainant_type`, `contact_phone`, `contact_email`, `category`, `priority`, `subject`, `description`, `date_received`, `assigned_to`, `status`, `resolution`, `resolved_by`, `resolved_at`, `rating`, `feedback`, ... (sync fields)
|
|
- **messages_new** (Replaces `messages`): Internal messaging/notifications
|
|
- `id`, `uid`, `from_user_id`, `to_user_id`, `subject`, `body`, `is_read`, `is_announcement`, `priority`, ... (sync fields)
|
|
- **chat_groups**: Group chat channels
|
|
- `id`, `uid`, `name`, `created_by`, `created_at`, `updated_at`, `is_deleted`
|
|
- **chat_group_members**: Members enrolled in group chats
|
|
- `group_id`, `user_id`, `joined_at`
|
|
- **notices**: Notice board announcements
|
|
- `id`, `uid`, `title`, `content`, `category`, `priority`, `audience`, `is_pinned`, `created_by`, `expiry_date`, ... (sync fields)
|
|
|
|
### Library
|
|
- **library_books**: Books in the library
|
|
- `id`, `uid`, `isbn`, `title`, `author`, `category`, `publisher`, `year_published`, `total_copies`, `available_copies`, `shelf_location`, `status`, `is_deleted`
|
|
- **library_issues**: Book borrowing records
|
|
- `id`, `uid`, `book_id`, `user_id`, `issue_date`, `due_date`, `return_date`, `status`, `issued_by`, `is_deleted`
|
|
|
|
### Edutainment & Games
|
|
- **edutainment_games**: Educational game setup
|
|
- `id`, `uid`, `game_type`, `title`, `description`, `subject_id`, `created_by`, `difficulty`, `reward_points`, `time_limit_seconds`, `game_config`, `is_published`, ... (sync fields)
|
|
- **edutainment_content**: Question repository for scramble and trivia games
|
|
- `id`, `uid`, `game_type`, `subject_id`, `created_by`, `question_text`, `hint_text`, `options`, `correct_answer`, `is_deleted`
|
|
- **edutainment_sessions**: Student edutainment game play sessions and results
|
|
- `id`, `uid`, `game_id`, `student_id`, `score`, `completed_at`, `points_earned`, ... (sync fields)
|
|
|
|
### System & Sync
|
|
- **system_settings**: Key-value system configurations
|
|
- `id`, `key`, `value`, `description`, `category`, ... (sync fields)
|
|
- **settings** (Replaces/Augments `system_settings`): Enhanced settings
|
|
- `id`, `key`, `value`, `description`, `category`, `is_encrypted`, ... (sync fields)
|
|
- **calendar_events** (Replaces `events`): School calendar
|
|
- `id`, `uid`, `title`, `description`, `date`, `time`, `end_time`, `location`, `type`, `is_all_day`, `is_public`, `academic_year`, `created_by`, ... (sync fields)
|
|
- **sync_logs**: History of synchronization operations
|
|
- `id`, `table_name`, `record_id`, `operation`, `status`, `error_message`, `retry_count`, `created_at`, `synced_at`
|
|
- **sync_config**: Sync state configuration
|
|
- `id`, `key`, `value`, `updated_at`
|
|
|
|
## Note on Sync Fields
|
|
Most tables include the following standard fields for synchronization and soft deletion:
|
|
- `created_at`: Creation timestamp
|
|
- `updated_at`: Last update timestamp
|
|
- `last_synced_at`: Last time this record was synced with the cloud
|
|
- `sync_status`: Current sync state (`synced`, `pending`, `conflict`)
|
|
- `is_deleted`: Soft deletion flag (0 for active, 1 for deleted)
|