Entity Relationship Diagrams

erDiagram
  CAMPUS ||--o{ FACULTY : has
  FACULTY ||--o{ STAFF_MEMBER : employs
  PERSON ||--o| STAFF_MEMBER : owns
  PERSON ||--|| USER_ACCOUNT : may_login
  USER_ACCOUNT ||--o{ USER_ROLE_GRANT : grants
  ROLE ||--o{ USER_ROLE_GRANT : to_account
  ROLE ||--o{ ROLE_PERMISSION : grants
  PERMISSION ||--o{ ROLE_PERMISSION : used_by
  CAMPUS ||--|| CAMPUS_FINANCE_PROFILE : configures
  CAMPUS ||--o{ CAMPUS_SERVICE_TAX_PROFILE : taxes
  CAMPUS ||--o{ CAMPUS_IMMIGRATION_CONFIG : immigration
  CAMPUS ||--o{ CAMPUS_GRADUATION_CONFIG : graduation
  CAMPUS ||--o| CAMPUS_PRIVACY_SECURITY : privacy
  CAMPUS ||--o{ ANNOUNCEMENT : publishes
  CAMPUS ||--o{ UNIVERSITY_POLICY : policies
  CAMPUS ||--o{ CAMPUS_SERVICE_PAGE : services
  CAMPUS ||--o{ REVIEW_QUESTIONNAIRE : reviews
  CAMPUS ||--o{ CAMPUS_STATUS_MAPPING : status_map
  CAMPUS ||--o{ CAMPUS_ACADEMIC_STATUS_POLICY : academic_status
  CAMPUS ||--o{ CAMPUS_CREDIT_POLICY : credit
  CAMPUS ||--o{ CAMPUS_CGPA_POLICY : cgpa
  CAMPUS ||--o{ CAMPUS_DOCUMENT_NUMBER_POLICY : document_numbers
  CAMPUS ||--o{ CAMPUS_ADJUSTMENT_POLICY : adjustments

  CAMPUS {
    string id PK
    string code
    string name
    string countryCode
    string timeZone
  }
  FACULTY {
    string id PK
    string campusId FK
    string code
    string name
  }
  STAFF_MEMBER {
    string id PK
    string personId FK
    string facultyId FK
    string staffNumber
    string displayName
    string academicTitle
    string emailAddress
  }
  PERSON {
    string id PK
    string displayName
    string personType
    string studentProfileId FK
    string staffMemberId FK
  }
  USER_ACCOUNT {
    string id PK
    string personId FK
    string loginName
    string status
    datetime disabledAt
  }
  ROLE {
    string id PK
    string code
    string name
  }
  PERMISSION {
    string id PK
    string code
    string name
  }
  USER_ROLE_GRANT {
    string id PK
    string userAccountId FK
    string roleId FK
    datetime grantedAt
  }
  CAMPUS_FINANCE_PROFILE {
    string id PK
    string campusId FK
    string currencyCode
    string bankDetails
    int processingDays
  }
  CAMPUS_SERVICE_TAX_PROFILE {
    string id PK
    string campusId FK
    string jurisdiction
    string registration
    int rateBasisPoints
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_IMMIGRATION_CONFIG {
    string id PK
    string campusId FK
    string programmeVersionId FK
    string feeItemId FK
  }
  CAMPUS_GRADUATION_CONFIG {
    string id PK
    string campusId FK
    string programmeVersionId FK
    string feeItemId FK
  }
  ANNOUNCEMENT {
    string id PK
    string campusId FK
    string title
    string department
    datetime publishedAt
    boolean isPinned
  }
  REVIEW_QUESTIONNAIRE {
    string id PK
    string campusId FK
    string version
  }
  CAMPUS_STATUS_MAPPING {
    string id PK
    string campusId FK
    string sourceSystem
    string sourceStatus
    string canonicalStatus
    string target
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_ACADEMIC_STATUS_POLICY {
    string id PK
    string campusId FK
    string academicStatus
    boolean countsAsValidStudyPeriod
    boolean countsAsActiveStudent
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_CREDIT_POLICY {
    string id PK
    string campusId FK
    string outcome
    boolean countsAsAttempted
    boolean countsAsEarned
    boolean countsInGpa
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_CGPA_POLICY {
    string id PK
    string campusId FK
    string repeatRule
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_DOCUMENT_NUMBER_POLICY {
    string id PK
    string campusId FK
    string documentType
    string prefix
    string sequencePattern
    date effectiveFrom
    date effectiveTo
  }
  CAMPUS_ADJUSTMENT_POLICY {
    string id PK
    string campusId FK
    string category
    boolean enabled
    string approvalRequirement
    date effectiveFrom
    date effectiveTo
  }
          

erDiagram
  FACULTY ||--o{ PROGRAMME : organises
  PROGRAMME ||--o{ PROGRAMME_VERSION : versions
  PROGRAMME_VERSION ||--o{ PROGRAMME_CAMPUS_OFFERING : offered_at
  CAMPUS ||--o{ PROGRAMME_CAMPUS_OFFERING : at_campus
  PROGRAMME_VERSION ||--o{ CURRICULUM_MODULE : includes
  MODULE ||--o{ CURRICULUM_MODULE : appears_in
  CURRICULUM_MODULE ||--o{ MODULE_PREREQUISITE : requires
  MODULE ||--o{ MODULE_PREREQUISITE : is_prereq_of
  CAMPUS ||--o{ GRADING_SCHEME : defines
  GRADING_SCHEME ||--o{ GRADE_BAND : bands
  PROGRAMME_VERSION ||--o| FEE_PLAN : priced_by
  FEE_PLAN ||--o{ FEE_ITEM : lines
  MODULE ||--o{ FEE_ITEM : tuition_for
  FUNDING_SCHEME ||--o{ STUDENT_FUNDING_AWARD : awards

  PROGRAMME {
    string id PK
    string facultyId FK
    string code
    string title
    string awardLevel
  }
  PROGRAMME_VERSION {
    string id PK
    string programmeId FK
    string versionLabel
    date effectiveFrom
    date effectiveTo
    int totalSemesterCount
    float totalCreditsRequired
    int minimumSemesterCredits
    int maximumSemesterCredits
    float minimumGraduationCgpa
    float minimumAttendancePercent
  }
  PROGRAMME_CAMPUS_OFFERING {
    string id PK
    string programmeVersionId FK
    string campusId FK
    string localProgrammeCode
    date effectiveFrom
    date effectiveTo
    string status
  }
  MODULE {
    string id PK
    string code
    string title
    string description
    float defaultCredits
  }
  CURRICULUM_MODULE {
    string id PK
    string programmeVersionId FK
    string moduleId FK
    int recommendedSemesterNumber
    float credits
    string requirementType
  }
  MODULE_PREREQUISITE {
    string id PK
    string curriculumModuleId FK
    string prerequisiteModuleId FK
    string minimumGrade
  }
  GRADING_SCHEME {
    string id PK
    string campusId FK
    string name
    float maximumPoints
  }
  GRADE_BAND {
    string id PK
    string gradingSchemeId FK
    string label
    float minimumScore
    float points
    string outcome
  }
  FEE_PLAN {
    string id PK
    string programmeVersionId FK
    string currencyCode
  }
  FEE_ITEM {
    string id PK
    string feePlanId FK
    string moduleId FK
    string category
    string label
    int amountMinor
    string serviceTaxTreatment
    string serviceTaxExemptionCode
  }
  FUNDING_SCHEME {
    string id PK
    string name
    string kind
    boolean renewable
  }
          

erDiagram
  PERSON ||--o| STUDENT_PROFILE : owns
  STUDENT_PROFILE ||--o{ STUDENT_IDENTIFIER : identifiers
  STUDENT_IDENTIFIER ||--o| STUDENT_IDENTIFIER : superseded_by
  STUDENT_PROFILE ||--o{ EMERGENCY_CONTACT : contacts
  STUDENT_PROFILE ||--o{ ACADEMIC_QUALIFICATION : quals
  STUDENT_PROFILE ||--o{ ENGLISH_PROFICIENCY : english
  STUDENT_PROFILE ||--o{ STUDENT_DOCUMENT : documents
  STUDENT_PROFILE ||--o| STUDENT_RESUME : resume
  STUDENT_RESUME ||--o{ RESUME_WORK_EXPERIENCE : work
  STUDENT_RESUME ||--o{ RESUME_EDUCATION : education
  STUDENT_RESUME ||--o{ RESUME_SKILL : skills
  STUDENT_RESUME ||--o{ RESUME_RECOGNITION : awards
  STUDENT_PROFILE ||--o{ PORTFOLIO_PROJECT : portfolio
  STUDENT_PROFILE ||--o{ PROGRAMME_ENROLMENT : enrolments
  STUDENT_PROFILE ||--o{ STUDENT_VISA_PASS : passes
  STUDENT_IDENTIFIER ||--o{ STUDENT_VISA_PASS : passport_used
  PROGRAMME_ENROLMENT ||--o{ STUDENT_VISA_PASS : for_enrolment
  PROGRAMME_VERSION ||--o{ PROGRAMME_ENROLMENT : of_version
  CAMPUS ||--o{ PROGRAMME_ENROLMENT : at
  PROGRAMME_ENROLMENT ||--o{ STUDY_PERIOD : periods
  PROGRAMME_ENROLMENT ||--o{ PROGRAMME_ENROLMENT_STATUS_EVENT : history
  ACADEMIC_TERM ||--o{ STUDY_PERIOD : term
  STUDY_PERIOD ||--o{ STUDY_PERIOD_STATUS_EVENT : history
  STUDY_PERIOD ||--o| CREDIT_LOAD_OVERRIDE : override
  STAFF_MEMBER ||--o{ CREDIT_LOAD_OVERRIDE : approves
  STUDENT_PROFILE ||--o{ STUDENT_ACCOUNT_ISSUE : issues

  PERSON {
    string id PK
    string displayName
    string personType
  }
  STUDENT_PROFILE {
    string id PK
    string personId FK
    string studentNumber
    string displayName
    string nationality
    string emailAddress
    date passportExpiresOn
  }
  STUDENT_IDENTIFIER {
    string id PK
    string studentProfileId FK
    string type
    string countryCode
    string value
    boolean isPrimary
    string supersededByIdentifierId FK
  }
  PROGRAMME_ENROLMENT {
    string id PK
    string studentProfileId FK
    string programmeVersionId FK
    string campusId FK
    datetime enrolledAt
    date expectedCompletionOn
    string status
  }
  PROGRAMME_ENROLMENT_STATUS_EVENT {
    string id PK
    string programmeEnrolmentId FK
    string status
    datetime effectiveFrom
    datetime effectiveTo
    string source
  }
  STUDY_PERIOD {
    string id PK
    string programmeEnrolmentId FK
    string academicTermId FK
    int programmeSemesterNumber
    string status
    string academicStatus
    datetime statusChangedAt
    string statusReason
    datetime registrationOpensAt
    datetime registrationClosesAt
  }
  STUDY_PERIOD_STATUS_EVENT {
    string id PK
    string studyPeriodId FK
    string status
    datetime effectiveFrom
    datetime effectiveTo
    string source
  }
  STUDENT_VISA_PASS {
    string id PK
    string studentProfileId FK
    string programmeEnrolmentId FK
    string passportIdentifierId FK
    string passType
    string passportNumber
    string passNumber
    string status
  }
  CREDIT_LOAD_OVERRIDE {
    string id PK
    string studyPeriodId FK
    int approvedMaximumCredits
    string approvedByStaffMemberId FK
    string status
  }
  STUDENT_ACCOUNT_ISSUE {
    string id PK
    string studentProfileId FK
    string kind
    string status
    int amountMinor
    datetime dueAt
  }
          

erDiagram
  PROGRAMME_ENROLMENT ||--|| FINANCE_ACCOUNT : has
  FINANCE_ACCOUNT ||--o{ INVOICE : invoices
  STUDY_PERIOD ||--o{ INVOICE : may_bill
  INVOICE ||--o{ INVOICE_LINE : lines
  FEE_ITEM ||--o{ INVOICE_LINE : from
  INVOICE ||--o| INVOICE_SERVICE_TAX : tax
  CAMPUS_SERVICE_TAX_PROFILE ||--o{ INVOICE_SERVICE_TAX : profile
  INVOICE ||--o| INVOICE_VOID : voided_by
  INVOICE ||--o{ CREDIT_NOTE : credited
  FINANCE_ACCOUNT ||--o{ PAYMENT : payments
  PAYMENT ||--o{ PAYMENT_ALLOCATION : allocates
  INVOICE ||--o{ PAYMENT_ALLOCATION : receives
  PAYMENT ||--o| PAYMENT_REVERSAL : reversed
  FINANCE_ACCOUNT ||--o{ REFUND : refunds
  PAYMENT ||--o{ REFUND : source
  PROGRAMME_ENROLMENT ||--o{ EDUCATION_SERVICE_TAX_EXEMPTION : exemptions
  FUNDING_SCHEME ||--o{ STUDENT_FUNDING_AWARD : grants
  PROGRAMME_ENROLMENT ||--o{ STUDENT_FUNDING_AWARD : receives
  STUDENT_FUNDING_AWARD ||--o{ STUDENT_FUNDING_BENEFIT : benefits
  FINANCE_ACCOUNT ||--o{ FINANCIAL_ADJUSTMENT : adjustments
  INVOICE ||--o{ FINANCIAL_ADJUSTMENT : may_adjust
  STUDENT_FUNDING_BENEFIT ||--o{ FINANCIAL_ADJUSTMENT : from_benefit

  FINANCE_ACCOUNT {
    string id PK
    string programmeEnrolmentId FK
    string currencyCode
    string status
  }
  INVOICE {
    string id PK
    string financeAccountId FK
    string studyPeriodId FK
    string invoiceNumber
    int totalMinor
    string status
  }
  CREDIT_NOTE {
    string id PK
    string invoiceId FK
    string creditNoteNumber
    int amountMinor
    int serviceTaxAmountMinor
    datetime issuedAt
  }
  INVOICE_VOID {
    string id PK
    string invoiceId FK
    string replacementInvoiceId FK
    datetime voidedAt
    string reason
  }
  PAYMENT {
    string id PK
    string financeAccountId FK
    string reference
    datetime paidAt
    int amountMinor
    string method
    string status
  }
  PAYMENT_REVERSAL {
    string id PK
    string paymentId FK
    datetime reversedAt
    string reason
  }
  REFUND {
    string id PK
    string financeAccountId FK
    string sourcePaymentId FK
    int amountMinor
    datetime paidAt
    string method
  }
  PAYMENT_ALLOCATION {
    string id PK
    string paymentId FK
    string invoiceId FK
    int amountMinor
  }
  FINANCIAL_ADJUSTMENT {
    string id PK
    string financeAccountId FK
    string invoiceId FK
    string studentFundingBenefitId FK
    string kind
    string category
    int amountMinor
  }
          

erDiagram
  APPLICANT_ACCOUNT ||--o{ APPLICATION : submits
  CAMPUS ||--o{ APPLICATION : campus
  PROGRAMME ||--o{ APPLICATION : programme
  APPLICATION ||--o{ EVIDENCE_OBJECT : evidence
  APPLICATION ||--o{ DECLARATION : declarations
  APPLICATION ||--o| OFFER : may_receive
  OFFER ||--o| FIRST_ENROLMENT : on_accept
  FIRST_ENROLMENT ||--|| STUDENT_PROFILE : creates
  FIRST_ENROLMENT ||--|| PROGRAMME_ENROLMENT : seeds

  APPLICANT_ACCOUNT {
    string id PK
    string identitySubject
    string email
    string contactNumber
  }
  APPLICATION {
    string id PK
    string applicantAccountId FK
    string programmeId FK
    string campusId FK
    string intake
    string status
  }
  EVIDENCE_OBJECT {
    string id PK
    string applicationId FK
    string evidenceType
    string fileId
    string checksum
    datetime uploadedAt
  }
  DECLARATION {
    string id PK
    string applicationId FK
    string policyVersion
    datetime acceptedAt
  }
  OFFER {
    string id PK
    string applicationId FK
    string conditions
    date expiresOn
    string decision
  }
  FIRST_ENROLMENT {
    string id PK
    string offerId FK
    string studentId
    string programmeId
    date startOn
  }
          

erDiagram
  STUDENT_PROFILE ||--o{ IMMIGRATION_CASE : cases
  PROGRAMME_ENROLMENT ||--o{ IMMIGRATION_CASE : for
  CAMPUS ||--o{ IMMIGRATION_CASE : at
  IMMIGRATION_CASE ||--o{ IMMIGRATION_DOCUMENT_CHECK : checks
  IMMIGRATION_CASE ||--o{ IMMIGRATION_CASE_EVENT : events
  ONLINE_FORM_SUBMISSION ||--o| IMMIGRATION_CASE : may_drive
  STUDENT_VISA_PASS ||--o| IMMIGRATION_CASE : may_attach
  INVOICE ||--o| IMMIGRATION_CASE : may_bill
  CAMPUS ||--o{ ONLINE_FORM_DEFINITION : forms
  ONLINE_FORM_DEFINITION ||--o{ ONLINE_FORM_SUBMISSION : submissions
  STUDENT_PROFILE ||--o{ ONLINE_FORM_SUBMISSION : submits
  PROGRAMME_ENROLMENT ||--o{ ONLINE_FORM_SUBMISSION : context
  ONLINE_FORM_SUBMISSION ||--o{ ONLINE_FORM_MESSAGE : messages
  STUDENT_PROFILE ||--o{ GRADUATION_RECORD : graduation
  PROGRAMME_ENROLMENT ||--o{ GRADUATION_RECORD : for
  CAMPUS ||--o{ GRADUATION_RECORD : at
  ONLINE_FORM_SUBMISSION ||--o| GRADUATION_RECORD : clearance
  ONLINE_FORM_SUBMISSION ||--o| GRADUATION_RECORD : convocation
  INVOICE ||--o| GRADUATION_RECORD : fee

  IMMIGRATION_CASE {
    string id PK
    string studentProfileId FK
    string programmeEnrolmentId FK
    string campusId FK
    string caseType
    string status
    string formSubmissionId FK
    string visaPassId FK
    string invoiceId FK
  }
  IMMIGRATION_DOCUMENT_CHECK {
    string id PK
    string immigrationCaseId FK
    string label
    string status
  }
  IMMIGRATION_CASE_EVENT {
    string id PK
    string immigrationCaseId FK
    string label
    datetime occurredAt
  }
  ONLINE_FORM_DEFINITION {
    string id PK
    string campusId FK
    string version
    string eligibility
  }
  ONLINE_FORM_SUBMISSION {
    string id PK
    string formDefinitionId FK
    string studentProfileId FK
    string programmeEnrolmentId FK
    int revision
    string status
  }
  ONLINE_FORM_MESSAGE {
    string id PK
    string submissionId FK
    int sequence
    string sender
    string text
  }
  GRADUATION_RECORD {
    string id PK
    string studentProfileId FK
    string programmeEnrolmentId FK
    string campusId FK
    string clearanceSubmissionId FK
    string convocationSubmissionId FK
    string invoiceId FK
  }
          

erDiagram
  PROGRAMME_ENROLMENT ||--o{ PROGRAMME_ENROLMENT_STATUS_EVENT : history
  STUDY_PERIOD ||--o{ STUDY_PERIOD_STATUS_EVENT : history
  MODULE_REGISTRATION ||--o{ MODULE_REGISTRATION_STATUS_EVENT : history
  CAMPUS ||--o{ CAMPUS_ACADEMIC_STATUS_POLICY : policy
  CAMPUS_ACADEMIC_STATUS_POLICY ||--o{ CURRENT_ACTIVE_STUDENT : filters
  CAMPUS_ACADEMIC_STATUS_POLICY ||--o{ LATEST_VALID_STUDY_PERIOD : filters
  PROGRAMME_ENROLMENT ||--o| LATEST_VALID_STUDY_PERIOD : reports
  PROGRAMME_ENROLMENT ||--o| STUDENT_CREDIT_SUMMARY : reports
  FINANCE_ACCOUNT ||--o| STUDENT_OUTSTANDING_BALANCE : reports
  STUDENT_PROFILE ||--o| CURRENT_ACTIVE_STUDENT : may_appear
  LEGACY_ENTITY_MAP }o--|| STUDENT_PROFILE : may_map
  DATA_QUALITY_ISSUE }o--o| STUDENT_PROFILE : may_flag

  PROGRAMME_ENROLMENT_STATUS_EVENT {
    string id PK
    string programmeEnrolmentId FK
    string status
    datetime effectiveFrom
    datetime effectiveTo
    string changedByUserAccountId FK
    string reason
    string source
  }
  CAMPUS_ACADEMIC_STATUS_POLICY {
    string id PK
    string campusId FK
    string academicStatus
    boolean countsAsValidStudyPeriod
    boolean countsAsActiveStudent
    date effectiveFrom
    date effectiveTo
  }
  LATEST_VALID_STUDY_PERIOD {
    string programmeEnrolmentId FK
    string studyPeriodId FK
    string academicStatus
    int programmeSemesterNumber
  }
  CURRENT_ACTIVE_STUDENT {
    string studentProfileId FK
    string studentNumber
    string programmeEnrolmentId FK
    string studyPeriodId FK
    string academicStatus
  }
  STUDENT_CREDIT_SUMMARY {
    string programmeEnrolmentId FK
    float creditsRequired
    float creditsEarned
    float transferredCredits
    float cgpa
    float remainingCredits
  }
  STUDENT_OUTSTANDING_BALANCE {
    string financeAccountId FK
    string programmeEnrolmentId FK
    int balanceMinor
    int openInvoiceBalanceMinor
    string balanceState
  }
  LEGACY_ENTITY_MAP {
    string id PK
    string canonicalEntityType
    string canonicalEntityId
    string sourceSystem
    string legacyTable
    string legacyId
  }
  AUDIT_EVENT {
    bigint id PK
    datetime occurredAt
    bigint revision
    int schemaVersion
    string actorStudentProfileId
    string actorUserAccountId
    string collection
    string recordId
    string action
    string before
    string after
  }
  DATA_QUALITY_ISSUE {
    string id PK
    string entityType
    string entityId
    string issueType
    string severity
    datetime detectedAt
  }
  INTEGRATION_OPERATION {
    string id PK
    string idempotencyKey
    string operationType
    string status
    datetime startedAt
  }