# BV-SIS Phase 1 — Database Report

**Module:** Institution Setup  
**Migration:** `2026_07_09_000000_create_institution_setup_tables.php`  
**Date:** 9 July 2026  

---

## Tables Created

### 1. `institutions`
Root tenant entity. Stores institution profile, branding, contact, and configuration.

| Column | Type | Notes |
|--------|------|-------|
| id | bigint PK | Auto-increment |
| uuid | uuid | Unique identifier |
| name | string | Required |
| short_name | string(50) | Abbreviation |
| motto | string | Nullable |
| logo_path, favicon_path | string | File paths |
| email, phone, alt_phone, website | string | Contact |
| physical_address, postal_address | text | Addresses |
| country, province, district | string | Location |
| latitude, longitude | decimal(10,7) | GPS coordinates |
| timezone, currency, academic_calendar, language | string | Configuration |
| institution_type | string(30) | Enum: university, college, etc. |
| registration_number, tax_number | string | Legal |
| status | string(20) | Enum: active, inactive, archived, draft |
| description, mission, vision | text | Profile |
| core_values, social_media, email_settings, sms_settings, brand_colors | json | Structured data |
| created_by, updated_by | FK → users | Audit |
| timestamps, deleted_at | | Soft deletes |

**Indexes:** `status`, `name`

---

### 2. `campuses`
| Column | Type | Notes |
|--------|------|-------|
| id, uuid | | Primary identifiers |
| institution_id | FK → institutions | CASCADE delete |
| code | string(20) | Unique per institution |
| name | string | |
| campus_type | string(30) | main, satellite, regional, online |
| address | text | |
| latitude, longitude | decimal | |
| dean_id, principal_id | FK → users | Nullable |
| capacity | unsigned int | |
| status | string(20) | |
| description | text | |
| images, buildings, rooms, maps | json | Campus assets |
| audit + soft deletes | | |

**Unique:** `(institution_id, code)`  
**Indexes:** `status`, `name`

---

### 3. `faculties`
| Column | Type | Notes |
|--------|------|-------|
| institution_id | FK → institutions | CASCADE |
| campus_id | FK → campuses | NULL on delete |
| code | string(20) | Unique per institution |
| name, dean_name, vice_dean_name | string | |
| office, email, phone | string | |
| logo_path | string | |
| status | string(20) | |

---

### 4. `areas_of_study`
| Column | Type | Notes |
|--------|------|-------|
| faculty_id | FK → faculties | CASCADE |
| code | string(20) | Unique per faculty |
| name, description | | |
| status | string(20) | |

---

### 5. `programmes`
| Column | Type | Notes |
|--------|------|-------|
| institution_id | FK | CASCADE |
| faculty_id | FK | CASCADE |
| area_of_study_id | FK | NULL on delete |
| campus_id | FK | NULL on delete |
| code | string(30) | Unique per institution |
| name, qualification, award | string | |
| duration_years, duration_semesters | tinyint | |
| total_credits | smallint | |
| delivery_mode | string(30) | full_time, part_time, etc. |
| requirements, description | text | |
| status | string(20) | |

---

### 6. `programme_tracks`
| Column | Type | Notes |
|--------|------|-------|
| programme_id | FK → programmes | CASCADE |
| code | string(30) | Unique per programme |
| name, description | | |
| status | string(20) | |

---

### 7. `academic_sessions`
| Column | Type | Notes |
|--------|------|-------|
| institution_id | FK | CASCADE |
| code | string(20) | Unique per institution |
| name | string | |
| year_start, year_end | smallint | |
| registration_start/end | date | |
| teaching_start/end | date | |
| examination_start/end | date | |
| graduation_date | date | |
| holiday_periods | json | |
| is_current | boolean | |
| status | string(20) | |

**Indexes:** `status`, `is_current`

---

### 8. `study_plan_periods`
| Column | Type | Notes |
|--------|------|-------|
| academic_session_id | FK | CASCADE |
| code | string(20) | Unique per session |
| name | string | |
| period_type | string(20) | semester, term, year, trimester |
| sequence | tinyint | |
| start_date, end_date | date | |
| credits | smallint | |
| status | string(20) | |

---

### 9. `programme_structures`
| Column | Type | Notes |
|--------|------|-------|
| programme_id | FK | CASCADE |
| study_plan_period_id | FK | NULL on delete |
| name | string | |
| level | tinyint | Year level |
| semester_number | tinyint | |
| total_credits | smallint | |
| status | string(20) | |

---

### 10. `programme_structure_items`
| Column | Type | Notes |
|--------|------|-------|
| programme_structure_id | FK | CASCADE |
| course_code | string(30) | |
| course_name | string | |
| credits | smallint | |
| is_core, is_elective | boolean | |
| prerequisites | json | |
| sort_order | smallint | |

---

### 11. `academic_paths`
| Column | Type | Notes |
|--------|------|-------|
| programme_id | FK | CASCADE |
| programme_track_id | FK | NULL on delete |
| name, description | | |
| progression_rules, promotion_rules, graduation_rules | json | |
| status | string(20) | |

---

### 12. `academic_intakes`
| Column | Type | Notes |
|--------|------|-------|
| academic_session_id | FK | CASCADE |
| programme_id | FK | CASCADE |
| code | string(30) | Unique per session+programme |
| name | string | |
| capacity | unsigned int | |
| application_start/end | date | |
| admission_start/end | date | |
| registration_start/end | date | |
| status | string(20) | |

---

### 13. `institution_documents`
Polymorphic document storage for institution entities.

| Column | Type | Notes |
|--------|------|-------|
| uuid | uuid | |
| documentable_type, documentable_id | morphs | |
| title | string | |
| file_path, file_type | string | |
| file_size | bigint | |
| uploaded_by | FK → users | |

---

## Entity Relationship Diagram

```
institutions
├── campuses
│   └── faculties (optional campus_id)
│       └── areas_of_study
│           └── programmes (also links faculty, campus)
│               ├── programme_tracks
│               │   └── academic_paths
│               ├── programme_structures
│               │   └── programme_structure_items
│               └── academic_intakes
├── academic_sessions
│   ├── study_plan_periods
│   │   └── programme_structures
│   └── academic_intakes
└── institution_documents (polymorphic)
```

---

## Features

- **Soft Deletes:** All main entities
- **UUID Support:** All main entities
- **Foreign Keys:** CASCADE and NULL ON DELETE as appropriate
- **Composite Unique Constraints:** Code uniqueness scoped to parent
- **Indexes:** status, name, is_current, composite programme_id+level
- **Audit Fields:** created_by, updated_by on all main tables

---

## Seeder

`InstitutionSetupSeeder` creates:
- Big Voice University (BVU) institution profile
- 2 campuses (Main, Bulawayo)
- 2 faculties (Engineering, Business)
- 3 areas of study
- 2 programmes (BSc CS, BEng Civil)
- 2 programme tracks
- 1 academic session (2025/2026, current)
- 2 study plan periods
- 1 programme structure with 2 course items
- 1 academic path with progression rules
- 1 academic intake

---

*BV-SIS Database Report — Institution Setup Phase 1*