# BV-SIS Database Planning
## High-Level Database Design (No Migrations)

**Document Version:** 1.0  
**Database Engine:** MySQL 8 / MariaDB 10.6+  
**Conventions:** snake_case tables/columns, `id` BIGINT UNSIGNED PK, audit fields on all tables, soft deletes where noted

---

## Global Conventions

### Standard Audit Fields (All Tables)
| Column | Type | Notes |
|--------|------|-------|
| id | BIGINT UNSIGNED | Primary key, auto-increment |
| created_at | TIMESTAMP | Record creation |
| updated_at | TIMESTAMP | Last modification |
| created_by | BIGINT UNSIGNED FK → users.id | Nullable for system |
| updated_by | BIGINT UNSIGNED FK → users.id | Nullable |
| deleted_at | TIMESTAMP | Soft delete (where applicable) |

### Standard Status Pattern
| Column | Type | Values |
|--------|------|--------|
| status | ENUM/TINYINT | active, inactive, archived |

---

# MODULE 1: INSTITUTION SETUP

## institutions
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(255) | NOT NULL |
| abbrev | VARCHAR(20) | NOT NULL, UNIQUE |
| logo_path | VARCHAR(500) | NULL |
| timezone | VARCHAR(50) | DEFAULT 'Africa/Lusaka' |
| currency | CHAR(3) | DEFAULT 'ZMW' |
| + audit fields | | |

**Indexes:** UNIQUE(abbrev)  
**Soft Delete:** No (singleton)  
**Future Expansion:** multi_institution support via institution_id FK on all tables

---

## campuses
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| institution_id | BIGINT UNSIGNED | FK → institutions.id |
| code | VARCHAR(20) | NOT NULL, UNIQUE |
| name | VARCHAR(255) | NOT NULL |
| address | TEXT | NULL |
| phone | VARCHAR(30) | NULL |
| email | VARCHAR(255) | NULL |
| status | ENUM | active, inactive |
| + audit fields | | |

**Indexes:** UNIQUE(code), INDEX(institution_id), INDEX(status)

---

## faculties
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| institution_id | BIGINT UNSIGNED | FK → institutions.id |
| code | VARCHAR(20) | NOT NULL |
| name | VARCHAR(255) | NOT NULL |
| dean_user_id | BIGINT UNSIGNED | FK → users.id, NULL |
| description | TEXT | NULL |
| status | ENUM | active, inactive |
| + audit fields | | |

**Indexes:** UNIQUE(institution_id, code), INDEX(status)

---

## programs (Area of Study)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| faculty_id | BIGINT UNSIGNED | FK → faculties.id |
| code | VARCHAR(20) | NOT NULL |
| title | VARCHAR(255) | NOT NULL |
| status | ENUM | active, inactive |
| + audit fields | | |

**Indexes:** UNIQUE(faculty_id, code), INDEX(faculty_id)

**Lookup:** programs belong to faculties (Area of Study in BRD)

---

## programmes (Programme of Study)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| program_id | BIGINT UNSIGNED | FK → programs.id |
| code | VARCHAR(20) | NOT NULL |
| name | VARCHAR(255) | NOT NULL |
| abbrev | VARCHAR(20) | NULL |
| study_period_id | BIGINT UNSIGNED | FK → study_period_structures.id, NOT NULL |
| nqf_level | TINYINT | NULL |
| total_credits | DECIMAL(6,2) | NULL |
| status | ENUM | active, inactive, draft |
| + audit fields | | |

**Indexes:** UNIQUE(code), INDEX(program_id), INDEX(study_period_id)

---

## programme_tracks
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| programme_id | BIGINT UNSIGNED | FK → programmes.id |
| track_name | VARCHAR(100) | NOT NULL |
| period_scheme_id | BIGINT UNSIGNED | FK → period_schemes.id |
| is_default | BOOLEAN | DEFAULT false |
| status | ENUM | active, inactive |
| + audit fields | | |

**Indexes:** INDEX(programme_id), UNIQUE(programme_id, track_name)

---

## period_schemes (Lookup)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(50) | NOT NULL, UNIQUE |
| description | VARCHAR(255) | NULL |

**Values:** MONTH 1, SEMESTER, YEAR, etc.

---

## study_period_structures
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| structure_code | VARCHAR(20) | NOT NULL, UNIQUE |
| path_name | VARCHAR(100) | NOT NULL |
| description | TEXT | NULL |
| + audit fields | | |

---

## study_period_nodes
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| structure_id | BIGINT UNSIGNED | FK → study_period_structures.id |
| parent_id | BIGINT UNSIGNED | FK → study_period_nodes.id, NULL |
| name | VARCHAR(100) | NOT NULL |
| sort_order | INT | NOT NULL |
| duration_days | INT | NULL |
| + audit fields | | |

**Indexes:** INDEX(structure_id), INDEX(parent_id)

---

## programme_structures
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| programme_id | BIGINT UNSIGNED | FK → programmes.id |
| track_id | BIGINT UNSIGNED | FK → programme_tracks.id |
| version | INT | DEFAULT 1 |
| is_current | BOOLEAN | DEFAULT true |
| + audit fields | | |

**Indexes:** INDEX(programme_id, track_id)

---

## programme_structure_items
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| structure_id | BIGINT UNSIGNED | FK → programme_structures.id |
| period_node_id | BIGINT UNSIGNED | FK → study_period_nodes.id |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| is_core | BOOLEAN | NOT NULL |
| credit_hours | DECIMAL(4,1) | NOT NULL |
| sort_order | INT | NOT NULL |
| + audit fields | | |

**Indexes:** INDEX(structure_id), INDEX(course_id)

---

## academic_paths
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| path_name | VARCHAR(100) | NOT NULL, UNIQUE |
| path_type | ENUM | regular, short_course, accelerated |
| status | ENUM | active, inactive |
| + audit fields | | |

---

## academic_sessions
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| academic_path_id | BIGINT UNSIGNED | FK → academic_paths.id |
| session_code | VARCHAR(30) | NOT NULL, UNIQUE |
| session_name | VARCHAR(100) | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
| status | ENUM | open, closed, archived |
| + audit fields | | |

**Indexes:** INDEX(academic_path_id), INDEX(start_date, end_date), INDEX(status)

---

## academic_intakes
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| academic_path_id | BIGINT UNSIGNED | FK → academic_paths.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| period_scheme_id | BIGINT UNSIGNED | FK → period_schemes.id |
| period_name | VARCHAR(100) | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
| registration_deadline | DATE | NULL |
| capacity | INT | NULL |
| status | ENUM | open, closed, full |
| + audit fields | | |

**Indexes:** INDEX(academic_path_id), INDEX(session_id), INDEX(status)

---

# MODULE 2: COURSE MANAGEMENT

## courses
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| faculty_id | BIGINT UNSIGNED | FK → faculties.id |
| code | VARCHAR(20) | NOT NULL |
| title | VARCHAR(255) | NOT NULL |
| alternate_title | VARCHAR(255) | NULL |
| credit_hours | DECIMAL(4,1) | NOT NULL |
| pass_mark | DECIMAL(5,2) | DEFAULT 50.00 |
| remark | TEXT | NULL |
| status | ENUM | active, inactive, retired |
| + audit fields | | |

**Indexes:** UNIQUE(faculty_id, code), INDEX(faculty_id), INDEX(status)

---

## course_programme (Pivot)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| programme_id | BIGINT UNSIGNED | FK → programmes.id |

**Indexes:** UNIQUE(course_id, programme_id)

---

## course_prerequisites
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| prerequisite_course_id | BIGINT UNSIGNED | FK → courses.id |
| + audit fields | | |

**Indexes:** UNIQUE(course_id, prerequisite_course_id)

---

## course_managers
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| user_id | BIGINT UNSIGNED | FK → users.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id, NULL |
| is_primary | BOOLEAN | DEFAULT true |
| + audit fields | | |

**Indexes:** INDEX(course_id), INDEX(user_id)

---

# MODULE 3: STUDENT REGISTRATION

## computer_number_schemes
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| school_code | VARCHAR(10) | NOT NULL |
| prefix | VARCHAR(20) | NOT NULL |
| number_size | TINYINT | NOT NULL |
| current_number | BIGINT | NOT NULL |
| pattern | VARCHAR(100) | NOT NULL |
| is_active | BOOLEAN | DEFAULT true |
| + audit fields | | |

---

## students
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_number | VARCHAR(30) | NOT NULL, UNIQUE |
| application_id | VARCHAR(30) | NULL, UNIQUE |
| profile_photo_path | VARCHAR(500) | NULL |
| school_id | BIGINT UNSIGNED | FK → campuses.id |
| intake_year | YEAR | NOT NULL |
| study_mode_id | BIGINT UNSIGNED | FK → study_modes.id |
| class_of_program_id | BIGINT UNSIGNED | FK → program_classes.id |
| nrc_passport | VARCHAR(30) | NULL, INDEX |
| surname | VARCHAR(100) | NOT NULL |
| other_names | VARCHAR(200) | NOT NULL |
| full_name | VARCHAR(300) | GENERATED/STORED |
| birth_date | DATE | NULL |
| sex | ENUM | male, female, other |
| marital_status_id | BIGINT UNSIGNED | FK → marital_statuses.id |
| occupation | VARCHAR(100) | NULL |
| sponsorship_id | BIGINT UNSIGNED | FK → sponsorship_types.id |
| religion | VARCHAR(100) | NULL |
| postal_address | TEXT | NOT NULL |
| residential_address | TEXT | NOT NULL |
| city | VARCHAR(100) | NOT NULL |
| state_province | VARCHAR(100) | NOT NULL |
| zip_postal_code | VARCHAR(20) | NULL |
| nationality_id | BIGINT UNSIGNED | FK → countries.id |
| email | VARCHAR(255) | NULL |
| phone | VARCHAR(30) | NOT NULL |
| guardian_name | VARCHAR(200) | NOT NULL |
| guardian_address_phone | TEXT | NOT NULL |
| disability | TEXT | NULL |
| refugee_status | BOOLEAN | DEFAULT false |
| refugee_number | VARCHAR(50) | NULL |
| status | ENUM | active, inactive, graduated, suspended, withdrawn |
| + audit fields | | |

**Indexes:** UNIQUE(student_number), INDEX(nrc_passport), INDEX(full_name), INDEX(status), INDEX(intake_year)

---

## student_id_changes (Audit)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| old_student_number | VARCHAR(30) | NOT NULL |
| new_student_number | VARCHAR(30) | NOT NULL |
| reason | TEXT | NULL |
| changed_by | BIGINT UNSIGNED | FK → users.id |
| created_at | TIMESTAMP | |

---

## student_programs
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| programme_id | BIGINT UNSIGNED | FK → programmes.id |
| track_id | BIGINT UNSIGNED | FK → programme_tracks.id, NULL |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| intake_id | BIGINT UNSIGNED | FK → academic_intakes.id, NULL |
| is_default | BOOLEAN | DEFAULT true |
| status | ENUM | active, completed, switched |
| + audit fields | | |

**Indexes:** INDEX(student_id), INDEX(programme_id), INDEX(session_id)

---

## student_enrollments
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| student_program_id | BIGINT UNSIGNED | FK → student_programs.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| period_node_id | BIGINT UNSIGNED | FK → study_period_nodes.id |
| status | ENUM | registered, not_registered, finished |
| study_period_label | VARCHAR(100) | NULL |
| batch | VARCHAR(50) | NULL |
| + audit fields | | |

---

## student_courses
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| enrollment_id | BIGINT UNSIGNED | FK → student_enrollments.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| period_node_id | BIGINT UNSIGNED | FK → study_period_nodes.id, NULL |
| status | ENUM | normal, exempted, repeat, deferred, overload, withdraw |
| type | ENUM | core, elective |
| + audit fields | | |

**Indexes:** INDEX(student_id), INDEX(course_id), INDEX(session_id), INDEX(status)

---

## student_documents
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| category | ENUM | previous_academics, national_identity, references, other |
| file_path | VARCHAR(500) | NOT NULL |
| file_name | VARCHAR(255) | NOT NULL |
| mime_type | VARCHAR(100) | NULL |
| + audit fields | | |

---

## student_offences
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| offence_type_id | BIGINT UNSIGNED | FK → offence_types.id |
| offence_date | DATE | NOT NULL |
| description | TEXT | NOT NULL |
| severity | ENUM | minor, major, severe |
| outcome | TEXT | NULL |
| status | ENUM | open, resolved, appealed |
| + audit fields | | |

---

## online_registration_requests
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id, NULL |
| request_data | JSON | NOT NULL |
| status | ENUM | pending, approved, rejected |
| reviewed_by | BIGINT UNSIGNED | FK → users.id, NULL |
| review_comment | TEXT | NULL |
| reviewed_at | TIMESTAMP | NULL |
| + audit fields | | |

---

# MODULE 4: STUDENT ADMISSION

## admission_settings
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| admission_year | YEAR | NOT NULL |
| selection_round | TINYINT | NOT NULL |
| is_active | BOOLEAN | DEFAULT true |
| + audit fields | | |

---

## admission_schemes (Lookup)
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL, UNIQUE |

**Values:** Matric & High School, Diploma holders, Degree holder, Nursing, Masters, PhD

---

## entry_requirements
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| programme_id | BIGINT UNSIGNED | FK → programmes.id, NULL |
| scheme_id | BIGINT UNSIGNED | FK → admission_schemes.id, NULL |
| requirement_text | TEXT | NOT NULL |
| + audit fields | | |

---

## admission_letter_templates
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| body_html | LONGTEXT | NOT NULL |
| programme_id | BIGINT UNSIGNED | FK → programmes.id, NULL |
| is_default | BOOLEAN | DEFAULT false |
| + audit fields | | |

---

## applications
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| application_number | VARCHAR(30) | NOT NULL, UNIQUE |
| admission_form_template_id | BIGINT UNSIGNED | FK → admission_form_templates.id |
| student_id | BIGINT UNSIGNED | FK → students.id, NULL |
| mode_of_entry_id | BIGINT UNSIGNED | FK → entry_modes.id |
| intake_year | YEAR | NOT NULL |
| intake_month | TINYINT | NOT NULL |
| study_mode_id | BIGINT UNSIGNED | FK → study_modes.id |
| title | VARCHAR(20) | NULL |
| first_name | VARCHAR(100) | NOT NULL |
| last_name | VARCHAR(100) | NOT NULL |
| other_names | VARCHAR(200) | NULL |
| nrc_passport | VARCHAR(30) | NOT NULL |
| birth_date | DATE | NULL |
| sex | ENUM | male, female, other |
| marital_status_id | BIGINT UNSIGNED | FK → marital_statuses.id, NULL |
| nationality_id | BIGINT UNSIGNED | FK → countries.id |
| disability | TEXT | NULL |
| refugee_status | BOOLEAN | DEFAULT false |
| refugee_number | VARCHAR(50) | NULL |
| postal_address | TEXT | NOT NULL |
| residential_address | TEXT | NOT NULL |
| city | VARCHAR(100) | NOT NULL |
| state_province | VARCHAR(100) | NULL |
| zip_postal_code | VARCHAR(20) | NULL |
| email | VARCHAR(255) | NULL |
| phone_mobile | VARCHAR(30) | NOT NULL |
| next_of_kin_name | VARCHAR(200) | NOT NULL |
| next_of_kin_relationship | VARCHAR(50) | NOT NULL |
| next_of_kin_address_phone | TEXT | NOT NULL |
| sponsorship_id | BIGINT UNSIGNED | FK → sponsorship_types.id, NULL |
| status | ENUM | draft, submitted, under_review, approved, rejected, admitted |
| + audit fields | | |

---

## application_references
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| application_id | BIGINT UNSIGNED | FK → applications.id |
| name_address | TEXT | NOT NULL |
| mobile_phone | VARCHAR(30) | NULL |
| email | VARCHAR(255) | NULL |
| sort_order | TINYINT | NOT NULL |

---

## admission_form_templates
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| fields_config | JSON | NOT NULL |
| is_active | BOOLEAN | DEFAULT true |
| + audit fields | | |

---

## registration_fees
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| amount | DECIMAL(12,2) | NOT NULL |
| programme_id | BIGINT UNSIGNED | FK → programmes.id, NULL |
| form_template_id | BIGINT UNSIGNED | FK → admission_form_templates.id, NULL |
| + audit fields | | |

---

# MODULE 5: STUDENT FINANCE

## fee_categories
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| parent_id | BIGINT UNSIGNED | FK → fee_categories.id, NULL |
| description | TEXT | NULL |
| + audit fields | | |

---

## fees
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| category_id | BIGINT UNSIGNED | FK → fee_categories.id |
| name | VARCHAR(100) | NOT NULL |
| amount | DECIMAL(12,2) | NOT NULL |
| effective_from | DATE | NOT NULL |
| effective_to | DATE | NULL |
| + audit fields | | |

---

## fee_assignments
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| fee_id | BIGINT UNSIGNED | FK → fees.id |
| assignable_type | VARCHAR(50) | programme, student, course |
| assignable_id | BIGINT UNSIGNED | NOT NULL |
| + audit fields | | |

**Indexes:** INDEX(assignable_type, assignable_id)

---

## course_fees
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| fee_id | BIGINT UNSIGNED | FK → fees.id |
| amount | DECIMAL(12,2) | NOT NULL |
| + audit fields | | |

---

## invoices
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| invoice_number | VARCHAR(30) | NOT NULL, UNIQUE |
| student_id | BIGINT UNSIGNED | FK → students.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id, NULL |
| total_amount | DECIMAL(12,2) | NOT NULL |
| paid_amount | DECIMAL(12,2) | DEFAULT 0 |
| balance | DECIMAL(12,2) | GENERATED |
| status | ENUM | draft, issued, partial, paid, overdue, cancelled |
| due_date | DATE | NULL |
| + audit fields | | |

---

## invoice_items
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| invoice_id | BIGINT UNSIGNED | FK → invoices.id |
| fee_id | BIGINT UNSIGNED | FK → fees.id, NULL |
| description | VARCHAR(255) | NOT NULL |
| amount | DECIMAL(12,2) | NOT NULL |

---

## receipts
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| receipt_number | VARCHAR(30) | NOT NULL, UNIQUE |
| student_id | BIGINT UNSIGNED | FK → students.id |
| invoice_id | BIGINT UNSIGNED | FK → invoices.id, NULL |
| amount | DECIMAL(12,2) | NOT NULL |
| payment_method | ENUM | cash, bank, mobile, cheque, other |
| payment_reference | VARCHAR(100) | NULL |
| received_by | BIGINT UNSIGNED | FK → users.id |
| + audit fields | | |

---

## minimum_payment_rules
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| rule_type | ENUM | percentage, fixed |
| value | DECIMAL(12,2) | NOT NULL |
| applies_to | ENUM | registration, examination, results |
| programme_id | BIGINT UNSIGNED | FK → programmes.id, NULL |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id, NULL |
| fee_category_id | BIGINT UNSIGNED | FK → fee_categories.id, NULL |
| is_active | BOOLEAN | DEFAULT true |
| + audit fields | | |

---

## student_clearances
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id, NULL |
| clearance_type | ENUM | financial, library, departmental, full |
| status | ENUM | pending, cleared, blocked |
| cleared_by | BIGINT UNSIGNED | FK → users.id, NULL |
| cleared_at | TIMESTAMP | NULL |
| notes | TEXT | NULL |
| + audit fields | | |

---

## result_releases
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id, NULL |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| release_type | ENUM | student_final, session_group, session_individual |
| finance_cleared | BOOLEAN | NOT NULL |
| released_by | BIGINT UNSIGNED | FK → users.id |
| released_at | TIMESTAMP | NOT NULL |
| override_reason | TEXT | NULL |
| + audit fields | | |

---

# MODULE 6: BULKSMS AND EMAIL

## contact_groups
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| description | TEXT | NULL |
| + audit fields | | |

---

## contact_group_members
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| group_id | BIGINT UNSIGNED | FK → contact_groups.id |
| student_id | BIGINT UNSIGNED | FK → students.id, NULL |
| phone | VARCHAR(30) | NULL |
| email | VARCHAR(255) | NULL |

---

## message_templates
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| channel | ENUM | sms, email |
| body | TEXT | NOT NULL |
| + audit fields | | |

---

## message_logs
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| channel | ENUM | sms, email |
| recipient | VARCHAR(255) | NOT NULL |
| body | TEXT | NOT NULL |
| status | ENUM | queued, sent, delivered, failed |
| provider_reference | VARCHAR(100) | NULL |
| sent_by | BIGINT UNSIGNED | FK → users.id |
| sent_at | TIMESTAMP | NULL |
| + audit fields | | |

---

# MODULE 7: STUDENT IDENTITY CARDS

## student_id_cards
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| card_number | VARCHAR(30) | NOT NULL, UNIQUE |
| template_id | BIGINT UNSIGNED | FK → id_card_templates.id |
| issue_date | DATE | NOT NULL |
| expiry_date | DATE | NULL |
| status | ENUM | active, expired, lost, replaced |
| qr_code_path | VARCHAR(500) | NULL |
| + audit fields | | |

---

## id_card_templates
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| layout_config | JSON | NOT NULL |
| is_default | BOOLEAN | DEFAULT false |
| + audit fields | | |

---

# MODULE 8: EXAMINATION (Enterprise)

## grading_schemes
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(100) | NOT NULL |
| config | JSON | NOT NULL |
| + audit fields | | |

---

## exam_schedules
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| exam_date | DATETIME | NOT NULL |
| venue | VARCHAR(255) | NULL |
| duration_minutes | INT | NULL |
| + audit fields | | |

---

## student_marks
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| student_id | BIGINT UNSIGNED | FK → students.id |
| course_id | BIGINT UNSIGNED | FK → courses.id |
| session_id | BIGINT UNSIGNED | FK → academic_sessions.id |
| mark | DECIMAL(5,2) | NULL |
| grade | VARCHAR(5) | NULL |
| status | ENUM | draft, submitted, moderated, published |
| entered_by | BIGINT UNSIGNED | FK → users.id |
| + audit fields | | |

**Indexes:** UNIQUE(student_id, course_id, session_id)

---

# MODULE 9: SYSTEM

## users
| Column | Type | Constraints |
|--------|------|-------------|
| id | BIGINT UNSIGNED | PK |
| name | VARCHAR(255) | NOT NULL |
| email | VARCHAR(255) | NOT NULL, UNIQUE |
| password | VARCHAR(255) | NOT NULL |
| phone | VARCHAR(30) | NULL |
| status | ENUM | active, inactive, locked |
| last_login_at | TIMESTAMP | NULL |
| + audit fields | | |

---

## roles, permissions (Spatie Permission pattern)
Standard Spatie tables: roles, permissions, model_has_roles, model_has_permissions, role_has_permissions

---

## activity_log (Spatie Activity Log)
Standard activity log for audit trail

---

# LOOKUP TABLES SUMMARY

| Table | Purpose |
|-------|---------|
| countries | Nationality dropdown with search |
| study_modes | Full-time, part-time, distance, etc. |
| program_classes | Certificate, Diploma, Degree, etc. |
| marital_statuses | Single, Married, etc. |
| sponsorship_types | Self, State, Employer |
| entry_modes | Direct entry, transfer, etc. |
| offence_types | Configurable offence categories |
| period_schemes | MONTH 1, SEMESTER, etc. |
| admission_schemes | Matric, Diploma, Degree, etc. |

---

*End of Database Planning — See 06_ENTITY_RELATIONSHIPS.md for ERD*
