# BV-SIS Foundation Database Report

**Date:** 8 July 2026  
**Database:** `bv_sis` (MySQL)  
**Status:** Foundation tables only

---

## 1. Migrations Executed

| Migration | Table(s) | Purpose |
|-----------|----------|---------|
| `0001_01_01_000000_create_users_table` | users, password_reset_tokens, sessions | Authentication |
| `0001_01_01_000001_create_cache_table` | cache, cache_locks | Cache driver |
| `0001_01_01_000002_create_jobs_table` | jobs, job_batches, failed_jobs | Queue |
| `2026_07_08_000000_create_permission_tables` | permissions, roles, model_has_*, role_has_permissions | RBAC |
| `2026_07_08_000001_add_fields_to_users_table` | users (extended) | phone, status, avatar, audit FKs |
| `2026_07_08_000002_create_system_settings_table` | system_settings | App configuration |
| `2026_07_08_000003_create_user_preferences_table` | user_preferences | Theme, language, locale |
| `2026_07_08_000004_create_saved_filters_table` | saved_filters | Table filter persistence |
| `2026_07_08_000005_create_activity_log_table` | activity_log | Audit trail |
| `2026_07_08_000006_create_media_table` | media | File/avatar storage |

---

## 2. Foundation Table Details

### users (extended)
| Column | Type | Notes |
|--------|------|-------|
| id | BIGINT PK | |
| name | VARCHAR | |
| email | VARCHAR UNIQUE | |
| phone | VARCHAR(30) | Nullable |
| status | VARCHAR(20) | active, inactive, locked |
| last_login_at | TIMESTAMP | Nullable |
| avatar_path | VARCHAR | Nullable |
| password | VARCHAR | Hashed |
| created_by | FK users | Nullable |
| updated_by | FK users | Nullable |
| timestamps | | |

### system_settings
| Column | Type | Notes |
|--------|------|-------|
| id | BIGINT PK | |
| key | VARCHAR UNIQUE | |
| value | JSON | |
| group | VARCHAR | general, mail, sms, etc. |
| description | TEXT | Nullable |
| is_public | BOOLEAN | |
| timestamps | | |

### user_preferences
| Column | Type | Notes |
|--------|------|-------|
| id | BIGINT PK | |
| user_id | FK users | |
| theme | VARCHAR | light, dark, system |
| language | VARCHAR | en |
| locale | VARCHAR | en_ZM |
| sidebar_collapsed | BOOLEAN | |
| preferences | JSON | Extensible |
| timestamps | | |

### saved_filters
| Column | Type | Notes |
|--------|------|-------|
| id | BIGINT PK | |
| user_id | FK users | |
| name | VARCHAR | |
| module | VARCHAR | |
| filters | JSON | |
| is_default | BOOLEAN | |
| timestamps | | |

---

## 3. Spatie Tables

- **permissions** — All module permissions (~200+)
- **roles** — 11 system roles
- **model_has_roles** — User role assignments
- **model_has_permissions** — Direct permissions
- **role_has_permissions** — Role-permission matrix

---

## 4. Seeded Data

### Roles (11)
super_admin, system_admin, registrar, admissions, finance, dean, exams, lecturer, comms, student, id_card_officer

### Users (11)
One user per role + Super Admin at `admin@bigsis.local`

### System Settings
app_name, timezone (Africa/Lusaka), locale, date_format, currency (ZMW), institution_name, support_email

---

## 5. Tables NOT Created (By Design)

No student, finance, course, admission, institution, or examination business tables.

---

## 6. Relationships

```
users 1──N user_preferences
users 1──N saved_filters
users N──M roles (via model_has_roles)
roles N──M permissions (via role_has_permissions)
users 1──N activity_log (as causer)
media ── morphTo ── users (avatars)
```

---

*End of Foundation Database Report*