alumni_lookup

Education Model Foundation (Phase 18.2)

Summary

Phase 18.2 introduces the new education data foundation used by the migration from the legacy Degree -> Major -> College chain to Education -> EducationAreaOfStudy.

This is a backend/schema release only. No user-facing read paths were migrated in 18.2.

What Was Added

Tables

• educations
• education_areas_of_study

Models

• Education
• EducationAreaOfStudy

Alumni Associations

• Alumni has_many :educations (ordered by date_issued desc)
• Alumni has_many :education_areas_of_study, through: :educations

Data Integrity

Database constraints

• FK: educations.buid references alumni.buid
• Unique: educations (buid, source_education_id)
• Unique: education_areas_of_study.person_area_of_study_id

Derived fields

• Education#degree_level auto-derived from degree_code:
  • A*, B* -> undergraduate
  • M* -> masters
  • D*, J*, P* -> doctorate
  • otherwise -> unknown
• EducationAreaOfStudy#area_of_study_name_normalized auto-derived by lowercasing, trimming, and collapsing whitespace
• EducationAreaOfStudy#concentration_level normalized to lowercase

Compatibility Notes

• Legacy scopes still available as aliases on Education during migration:
  • graduate -> masters
  • professional -> doctorate
• Existing UI/API read paths still use legacy sources until later sub-phases.

Migrations

• 20260422093100_create_educations
• 20260422093200_create_education_areas_of_study
• 20260422100500_remap_education_degree_levels

Test Coverage

Model tests added:

• test/models/education_test.rb
• test/models/education_area_of_study_test.rb

Coverage includes:

• associations
• validations
• uniqueness constraints
• derivation logic
• scope behavior
• DB-level FK enforcement

Related Docs (Prior to Phase 18.8)

• docs/planning/champion-portal/phases/phase-18/README.md
• docs/planning/champion-portal/qa/PHASE_18_LAUNCH_GUIDE.md
• docs/planning/champion-portal/phases/phase-18/samples/README.md

---

Phase 18.8 — Per-Enrollment Student Data

New Columns on educations

• expected_graduation_year (integer) — populated from the “Preferred Year” CRM column when date_issued is NULL (in-progress row)
• student_status (string) — per-record CRM status: awarded, pending, withdrawn, or nil

Education.in_progress Scope

The canonical single source of truth for current enrollment:

scope :in_progress, -> {
  where(date_issued: nil)
    .where("educations.student_status IS NULL OR educations.student_status = 'pending'")
}

The column is table-qualified (educations.student_status) to avoid PG::AmbiguousColumn when joined to alumni, which also has student_status. Never use where(student_status: ...) in a scope that may be composed with an alumni join.

Education::ENROLLMENT_COLLEGE_CODE_SQL

Canonical COALESCE SQL constant resolving an in-progress row’s college code:

ENROLLMENT_COLLEGE_CODE_SQL = <<~SQL.squish
  COALESCE(
    NULLIF(NULLIF(educations.current_school_code, ''), '00'),
    educations.granting_school_code
  )
SQL

Use this constant anywhere a query needs the “effective college” for an in-progress education row.

Alumni Enrollment Delegation

All Alumni enrollment methods now delegate to Education.in_progress rather than reading alumni.current_school_code and siblings:

# Scopes
scope :currently_enrolled, -> { joins(:educations).merge(Education.in_progress).distinct }
scope :current_students,   -> { currently_enrolled.where.not(buid: Education::AggregateScope.buid_subquery) }

# Instance methods
def currently_enrolled? = educations.merge(Education.in_progress).exists?
alias current_student? currently_enrolled?
def current_enrollment_education = educations.merge(Education.in_progress).order(created_at: :desc).first

Csv::EducationImporter Phase 18.8 Additions

Two new private methods called after each education save:

• apply_student_status_ratchet!(buid, student_status) — enforces one-way awarded rule on alumni.student_status
• clear_legacy_enrollment_fields!(buid) — when an awarded row lands (date_issued present), blanks the 4 legacy alumni enrollment columns

New Migrations

Migration	Purpose
20260528191923_add_honorary_degree_college	Seeds the honorary-degree college row so education.college always resolves
20260528221509_add_current_student_fields_to_educations	Adds expected_graduation_year (integer) + student_status (string) with indexes

Deferrals (Phase 18.8 Deploy 2)

The following are intentionally deferred to a second deploy to allow safe rollout:

• Migration to drop current_school_code, current_program_desc, intended_degree_code, expected_graduation_year from alumni
• Removal of Csv::AffinaquestContactImporter#clear_enrollment_fields_if_awarded! (only safe to drop after the alumni columns are gone)

What Was Retired

• Csv::CurrentStudentImporter — fully deleted (service file, controller action, background jobs, views, rake tasks)
• All Settings UI routes pointing to the current-student import batch UI
• EducationImportBatch variants that drove the current-student workflow