# Data Model and Index Strategy

## Modeling principles

- Use unsigned `BIGINT` internal keys and separate random public identifiers
  (ULID/UUID) where records appear in URLs or verification endpoints.
- Put `institution_id` on tenant-owned aggregate roots and high-risk child tables.
  Redundant tenant keys are intentional where they enable constraints and safe queries.
- Use foreign keys with deliberate delete behavior. Academic, finance, audit, and
  examination evidence is restricted rather than cascaded.
- Use normalized transactional tables; create reporting read models/materialized
  summaries only after query evidence justifies them.
- Use explicit status enums in PHP backed by constrained strings; use state history
  tables for sensitive lifecycles.
- Store money as `amount_minor BIGINT` plus `currency CHAR(3)`.
- Store UTC timestamps with institution time-zone configuration.
- Soft delete mutable catalogue/content records where recovery is valuable. Never
  “soft edit” audit evidence; append corrections.

## Entity relationship summary

### Tenancy and organization

- `institutions` has many `campuses`, `study_centres`, `faculties`, `users`, and all
  major tenant aggregates.
- `faculties` belongs to an institution and optionally a campus; it has many
  `departments`.
- `departments` has many `programmes` and `courses`.
- A configurable `organizational_units` hierarchy may represent colleges/schools
  without forcing every institution into one naming scheme.
- `programmes` belongs to a department, qualification/award, and institution; it has
  options, curricula, applicants, and students.

### Identity and access

- `users` belongs to a home institution; exceptional cross-tenant access is modeled
  through `institution_user`.
- `roles` and `permissions` are tenant-scoped or system templates.
- `model_has_roles` and `role_has_permissions` assign access; role assignments may
  include institution/campus/department scope and validity dates.
- `login_events`, `user_sessions`, and `user_status_histories` preserve security state.

### Academic structure

- `academic_sessions` has many `semesters`; both belong to an institution.
- `curricula` is a versioned programme aggregate; `curriculum_courses` links courses
  with level, semester, type, and unit rules.
- `course_prerequisites` links curriculum courses with prerequisite/corequisite rules.
- `course_offerings` represents a course taught in a session/semester/campus.
- `course_allocations` links offerings to staff.

### Admissions and people

- An `applicant` has a user identity and many `applications`.
- An application belongs to an admission cycle, programme choice, study mode, and
  location; it has documents, education history, referees, reviews, interviews,
  payments, decisions, and at most one accepted offer.
- Accepted onboarding creates a `student` in one transaction and links provenance to
  the application.
- Students have guardians/sponsors through pivots, documents, adviser assignments,
  status histories, restricted medical/accommodation records, discipline, ministry,
  attendance, and research records.
- Staff profiles link to users, departments, qualifications, contracts, workload,
  courses, and restricted payroll/bank records.

### Registration, LMS, and assessment

- A `course_registration` is unique per student/session/semester and has registration
  items unique by course offering.
- A `virtual_classroom` belongs to a course offering; it has lesson modules, versioned
  materials, discussions, live classes, assignments, and progress records.
- Assignments have rubric criteria and immutable submission versions; grading targets
  a specific version.
- Attendance uses an `attendance_sessions` header and unique participant records.

### CBT

- Question banks contain versioned questions; options and answer keys are separated
  so candidate retrieval never joins answer keys.
- Exams contain immutable configuration snapshots and selected examination questions.
- Candidate eligibility creates an `examination_candidate`.
- Each attempt owns a randomized presentation manifest, server timestamps, answer
  revisions, activity events, incidents, and a final submission.
- Objective marking produces score components; manual responses enter an assigned,
  moderated marking workflow.

### Results and records

- `result_batches` group results by offering and workflow state.
- Results retain score components, grade-scale version, calculated totals, approvals,
  publication, locks, and amendment history.
- Grading systems own non-overlapping grade scales and degree classifications.
- Transcript requests lead to immutable transcript versions containing a data snapshot,
  checksum, signer, and delivery records.
- Certificates similarly reference template version, award facts, signed verification
  token metadata, revocation state, and generation audit.

### Finance

- Fee schedules generate invoices and invoice items.
- Payments and gateway transactions are separate: gateway events are evidence, while
  allocations apply verified funds to invoices.
- A double-entry `ledger_transactions`/`ledger_entries` pair is the accounting source
  of truth. Refunds/reversals create compensating entries, never destructive edits.
- Scholarships, discounts, and waivers are approved adjustments with reason and scope.

## Planned table groups

The requested major tables are retained, with these clarifications/additions:

- tenancy: `institutions`, `institution_domains`, `institution_user`,
  `organizational_units`, `campuses`, `study_centres`, `system_settings`;
- access: `users`, `roles`, `permissions`, role/permission pivots, `login_events`,
  `user_sessions`, `user_status_histories`;
- academics: `qualifications`, `award_types`, `study_modes`, `academic_levels`,
  `academic_sessions`, `semesters`, `courses`, `curricula`, `curriculum_courses`,
  `course_prerequisites`, `course_offerings`, `course_allocations`;
- admissions/people: all requested applicant, student, guardian, sponsor, staff, and
  history/document tables plus `admission_cycles`, `application_reviews`, `interviews`;
- teaching: all requested LMS tables plus `material_versions`, `discussions`,
  `rubrics`, `submission_versions`, `attendance_sessions`;
- CBT: requested exam tables plus `question_versions`, `answer_keys`,
  `attempt_manifests`, `answer_revisions`, `examination_activity_events`;
- records/results: requested result, grading, transcript, and certificate tables plus
  `result_amendments`, `document_versions`, `document_deliveries`, `revocations`;
- finance: requested fee/payment tables plus `payment_webhook_events`,
  `payment_allocations`, `ledger_transactions`, `ledger_entries`, `refunds`;
- compliance: `audit_logs`, `activity_logs`, `data_access_logs`, `outbox_messages`.

Exact columns are deferred to each implementation phase so migrations are reviewed
with the owning workflows rather than created speculatively.

## Critical constraints

- Unique tenant codes: `(institution_id, code)` for campuses, departments, programmes,
  courses, sessions, and similar catalogues.
- Unique identity: normalized email globally for login unless tenancy UX explicitly
  requires duplicate emails; student numbers unique per institution.
- Registration: unique `(institution_id, student_id, session_id, semester_id)` and
  `(course_registration_id, course_offering_id)`.
- Results: unique active result per `(student_id, course_offering_id)` with amendments
  versioned.
- CBT: one active attempt enforced with a database row/lock plus Redis lock; idempotent
  answer revision keys.
- Payments: unique `(gateway, gateway_reference)` and unique webhook provider event ID.
- Ledger: entries balance per transaction; immutable after posting.
- Verification: random public ID unique globally; checksum unique where appropriate.

## Index strategy

Every foreign key is indexed. High-value composite indexes include:

| Table family | Index |
|---|---|
| tenant records | `(institution_id, status)`, `(institution_id, created_at)` |
| students | `(institution_id, matriculation_number)` unique; `(institution_id, programme_id, status)` |
| applications | `(institution_id, admission_cycle_id, status)`; `(applicant_id, created_at)` |
| course offerings | `(institution_id, session_id, semester_id, course_id, campus_id)` |
| registrations | `(student_id, session_id, semester_id)`; approval/status queue indexes |
| LMS materials | `(virtual_classroom_id, release_at, status)` |
| exam candidates | `(examination_id, student_id)` unique; `(examination_id, status)` |
| attempts | `(examination_id, candidate_id, status)`; `(status, expires_at)` |
| answers | `(attempt_id, examination_question_id)` unique |
| results | `(institution_id, student_id, session_id, semester_id)`; `(result_batch_id, status)` |
| invoices | `(institution_id, student_id, status, due_at)` |
| gateway events | `(gateway, provider_event_id)` unique; `(processing_status, received_at)` |
| audit logs | `(institution_id, occurred_at)`; `(actor_id, occurred_at)`; `(subject_type, subject_id)` |
| notifications | `(notifiable_type, notifiable_id, read_at, created_at)` |

Full-text indexes may support CMS/library search. Sensitive free text is excluded from
general search indexes. Query plans and slow-query logs must validate indexes before
production; redundant indexes will be removed.

## Backup and recovery

- Nightly full backups plus frequent binlog shipping for point-in-time recovery.
- Encrypted, versioned object-storage backups in a separate account/region.
- Documented retention aligned with legal and institutional policy.
- Automated backup success alerts and monthly restore drills.
- Encrypt backups with managed keys and restrict restore permission.
- Capture database and object-storage recovery points consistently for generated
  academic records.