alumni_lookup

Phase 18: Alumni Education Data Model Migration

Status: Complete (18.1-18.9 shipped; released in v1.0.65). Degree-model deprecation + dropping the legacy degrees table are intentionally held for a separate post-import cleanup tag once Education coverage ≥ 99% — colleges/majors are retained as active reference tables.
Priority: High
Estimated Sub-Phases: 9

---

Completion Summary (18.2)

Implemented

• Added educations and education_areas_of_study schema (additive, no read-path cutover yet)
• Added Education and EducationAreaOfStudy models with associations and validations
• Added DB-level integrity and idempotency constraints:
  • FK: educations.buid -> alumni.buid
  • Unique: educations (buid, source_education_id)
  • Unique: education_areas_of_study.person_area_of_study_id
• Added model-level derivations:
  • degree_level buckets: undergraduate, masters, doctorate, unknown
  • area_of_study_name_normalized (lowercased + whitespace-collapsed)
• Added and passed model tests:
  • EducationTest
  • EducationAreaOfStudyTest
• Added Phase 18 launch guide and committed sample source CSV references

Files Created (18.2)

• db/migrate/20260422093100_create_educations.rb
• db/migrate/20260422093200_create_education_areas_of_study.rb
• db/migrate/20260422100500_remap_education_degree_levels.rb
• app/models/education.rb
• app/models/education_area_of_study.rb
• test/fixtures/educations.yml
• test/fixtures/education_areas_of_study.yml
• test/models/education_test.rb
• test/models/education_area_of_study_test.rb
• docs/planning/champion-portal/qa/PHASE_18_LAUNCH_GUIDE.md
• docs/planning/champion-portal/phases/phase-18/samples/README.md

Tests

• bin/test => 4274 runs, 11180 assertions, 0 failures, 0 errors, 3 skips

Deferrals

• None from 18.2 deliverables. Remaining work is intentionally scheduled in 18.3-18.7 per this phase plan.

---

Overview

Migrate from the current academic structure:

• Alumni -> Degree -> Major -> College

To a new, person-centered model:

• Alumni -> Education -> EducationAreaOfStudy

This phase introduces a richer representation of educational history while preserving existing downstream integrations during rollout.

Proposed Target Structure

Education (0..n per Alumni)

• degree_level (optional; may be implied by degree_code)
• degree_code
• department_name (free text for post-2000 source data)
• granting_school_code and/or granting_school_name (historical source-of-award school)
• current_school_code and/or current_school_name (where the program lives now)
• date_issued

EducationAreaOfStudy (0..n per Education)

• person_area_of_study_id (person-unique identifier from source, if available)
• area_of_study_name (raw source text; normalization deferred)
• concentration_level (major, minor, concentration)
• current_institutional_unit_code and/or current_institutional_unit_name

---

Why This Phase Exists

The current Degree -> Major -> College model assumes a single major and current-college context attached to each degree row. It is insufficient for:

• Historical school attribution (granting school vs current school)
• Multi-program degrees (major + minor + concentration)
• Source-system records that now provide department-level context
• Future-proofing education records without tightly coupling to static majors reference data

---

Impact Inventory (Current Codebase)

The following systems are directly impacted because they query degrees, majors, and/or colleges:

1. Import and data management

• Settings::AlumniController degree and banner preview/commit flows
• Csv::AlumniImporter
• Csv::BannerImporter
• Csv::CurrentStudentImporter (degree/no-degree assumptions, intended_degree_code, expected_graduation_year)
• lib/tasks/current_students.rake (groups by colleges.college_desc)
• lib/tasks/db_snapshot.rake (core tables list includes degrees/majors/colleges)
• lib/tasks/verify.rake (degree count verification)

2. Lookup Portal search and profile display

• Alumni model scopes and helpers (filter_by_college, filter_by_major, recent_degree, graduation_years, with_degrees, without_degrees, current_student?)
• Degree model scopes (undergraduate, graduate, filter_by_college, by_year, by_fiscal_year, fiscal year helpers)
• AlumniController filters and show display paths
• Verification/search interfaces that render degree summaries
• app/views/layouts/_navbar.html.erb “Degree Stats” link
• app/controllers/concerns/timeout_protection.rb flash copy references “college or year”
• app/controllers/concerns/fiscal_year_helpers.rb defaults to degree_date column

3. Degree stats and engagement analytics

• StatisticsController (Degree Stats pages + CSV download)
• EngagementStatsController (UG/GR derivation logic) + EngagementStats::*Service
• AlumniFilterService college/year filtering
• EngagementStats::DemographicsService college/major breakdowns

4. Alumni Portal (Cp::*) profile, community, and recommendation logic

• Cp::Community model: community_type: :college/:major, college_code, major_code, qualification methods (champion.alumni.degrees.any? { |d| d.major&.college_code == ... }), find_or_create_college_community, find_or_create_major_community, college / major_record accessors
• Cp::CommunityCreationJob (community_types: %w[college major])
• Cp::CareerConnectService (joins alumni: { degrees: :major }, uses majors.major_desc and majors.college_code, candidate_degree_clusters)
• Cp::ChampionRecommendation (eager loads alumni: { degrees: :major } for “Same college/Same major” badges)
• Cp::Champion fields: anticipated_college_code, affiliated_college_code, anticipated_program, anticipated_graduation_year, affiliated_department_or_college_name (resolves via College.find_by)
• Cp::Champion Education Privacy setting (Phase 16.2) — controls visibility of degree/major/year on profile
• app/views/champions/champions/show.html.erb (line 253: degree display; line 170+: anticipated college dropdown)
• app/views/champions/verifications/show.html.erb and _search_results.html.erb (degree rendering, anticipated/affiliated college display)
• app/views/champions/communities/new.html.erb + community_type_form_controller.js (College/Major community creation form)
• Champions::CommunitiesController#autocomplete_majors endpoint
• Champions::VerificationsController (triggers Cp::CommunityCreationJob with college/major types)
• app/helpers/cp/communities_helper.rb and app/helpers/champions/communities_helper.rb (render college/major community names + descriptions)
• app/helpers/cp/home_helper.rb (grad year display from champion.alumni.degrees)
• app/services/legacy_verification_service.rb (recent_degree.major_desc, recent_degree.degree_date)
• app/services/cp/data_export_service.rb (college_last_name field)

5. Exports, check-in tools, and batch utilities

• Csv::AlumniExporter (must include both buid and contact_id)
• Csv::EventRsvpConverter
• Event conversion tool output
• Batch search result formatting (recent_degree.major_desc, grad year)

6. Lookup API and downstream contract dependencies

• Api::AlumniController (serializes recent_degree with major_desc, college_name, degree_code, degree_date)
• Api::V1::AlumniSearchController#serialize_alumni (ug_college, ug_program, ug_degree, ug_graduation_year, gr_*, pref_college fields used by downstream apps)
• Api::MajorsController (#index, #search, returns major_code, major_desc, college_code, college_name_short)
• Api::ActivityDescriptionsController (filter_by_college)

7. Reference data admin and configuration

• Settings CRUD for College and Major (/settings/colleges, /settings/majors)
• config/career_clusters.yml (entire mapping is college_codes + major_overrides-based)
• config/welcome_packs.yml (/ interpolation tokens; college: and major: community-type sections)
• config/faq.yml (user-facing copy referencing college filters and “Same college / Same major” recommendation badges)
• lib/tasks/community_bootstrap.rake (creates college/major communities from Degree records)
• lib/tasks/belmont_stories.rake (filters scraping by college_code)

8. News, content, and seeded questions

• Cp::NewsPost colleges association + college_tag string field
• app/views/champions/news_posts/show.html.erb “Colleges” card
• app/views/champions/content_submissions/* (renders college_tag)
• Cp::SeededQuestion target_audience values include college / major
• app/views/champions/seeded_questions/_form.html.erb interpolation help ({college_name})

9. Routes and URL surface

• /stats/degrees.csv
• settings/alumni/upload_degrees, import_degrees_preview, import_degrees_commit, import_degrees
• Banner JH-0026c (SHRDGMR) import routes
• settings/colleges, settings/majors resource routes
• api/majors (#index, #search)
• champions/communities/autocomplete_majors

---

Migration Strategy (High-Level)

Contract-first and backwards-compatible

Phase 18 is designed so downstream consumers can continue reading existing API fields while internals migrate.

1. Add new tables and models
2. Dual-write from import pipelines into old + new structures
3. Import full datasets from new awarded-degrees and areas-of-study exports (no legacy backfill)
4. Pre-seed and maintain school reference data from the granted-school source list before first import
5. Introduce a compatibility presenter/service to produce legacy fields (ug_*, gr_*, pref_college) with per-record fallback to legacy degrees when educations are missing
6. Migrate reads across controllers/services to the presenter/service
7. Track migration coverage and cut over fully only after agreed completeness threshold
8. Keep API payload shape unchanged until explicit deprecation window is approved
9. Optionally retire legacy degree-major paths in a later phase

---

Sub-Phase Plan

Phase 18.1: Data Contract, Mapping Rules, and Resolved Decisions ✅

Goal: Finalize non-ambiguous mapping and data ownership before schema work.

Deliverables

• Field-level mapping spec: old -> new model (including null/unknown behavior)
• Degree-level categorization rules (undergrad / masters / doctorate) from degree_code
• Canonical definitions for granting_school vs current_school
• Decision on area_of_study_name normalization strategy (text-first vs normalized table)
• API compatibility contract document listing guaranteed legacy fields

Resolved Decisions

#	Topic	Decision	Rationale
1.1	Source feed shape	Two CSVs: awarded-degrees + areas-of-study, joined via Education: Education natural key from source	Matches actual exports the data team will provide
1.2	Legacy degrees/majors/colleges fate	Plan toward decommission. Phase 18 keeps them readable for transition, Phase 19+ retires after dependents migrate	Data team is maintaining the new model going forward
1.3	Backfill strategy	No backfill. New exports include all records. Missing/mismatched data flows into a gap report so the CRM team can fix source	Source-of-truth is the CRM; gaps are signals, not data we should fabricate
1.4	Granting school setup	Pre-seed all unique granting-school codes/names from source list before 18.3 import runs	Avoids null school-code mappings for known schools and supports icons/short names immediately
2.1	degree_level storage	Hybrid: physical column auto-derived from degree_code on save	Indexable for stats joins; never drifts because regenerated from canonical field
2.2	granting_school vs current_school	Both stored as *_name (free text from source, never lossy) + *_code (resolved via lookup, nullable when no match) on the same educations row	Lets us preserve historical attribution AND join to the colleges table when known
2.3	department_name storage	Free text on Education for Phase 18. No departments reference table yet. Reserve normalization for a follow-up phase	Source provides free text; existing majors.dept_desc usage is small (4 sites) and routes through compatibility presenter
2.4	concentration_level enum	major, minor, concentration (lowercased on ingest)	Matches source exactly
3.1	API V1 contract	(c) additive: keep all current fields stable; add optional educations: [...] block behind ?include=educations opt-in	Zero-break for downstream apps; new richness is opt-in
3.2	UG/GR derivation	Most recent Education by date_issued per level, focused on concentration_level: "major" areas of study	Simple, deterministic, matches user intent
3.3	Cp::Community college qualification	Match on current_school_code only	Aligns with how the CRM frames the “where it lives now” school
3.4	“Same major” recommendation	Match on normalized area_of_study_name where concentration_level: "major"	Future-proof against legacy major_code rot
3.5	Compatibility fallback	If an alum has zero educations, presenter falls back to legacy degrees for all legacy fields during migration window	Prevents empty API/profile output when source coverage is temporarily incomplete
3.6	Coverage tracking	Add migration coverage stats and make full cutover contingent on threshold	Provides objective readiness signal and protects downstream behavior
4.1	Current-student fields	Stay on Alumni unchanged. No Education row created until graduation	Educations represent awarded degrees, not in-progress study
4.2	Career Clusters / Welcome Packs YAML	Phase 18: unchanged. Compatibility presenter exposes legacy keys (primary_major_desc, primary_college_code) so YAMLs keep working. Phase 19 backlog: rethink YAMLs against new vocabulary	Keeps Phase 18 finite; data won’t structurally change again so YAML refactor is safe to defer
4.3	majors/colleges admin pages	Hide/remove from Settings nav after Phase 18.7	They become legacy reference data; data team owns updates via the new feeds
4.4	Phase 18 scope	All Impact Inventory items in scope. Single-shot transition. No carve-outs into Phase 19 except YAML refactor (4.2)	User wants one decisive transition

Field Mapping Contract

Source: awarded-degrees.csv → educations

Source column	Target column	Type	Notes
Contact: BUID	educations.buid	string, NOT NULL, indexed	FK-style to alumni.buid
Contact: BruinQuest - Contact ID	(matching only, not stored)	—	Used for alumni resolution / gap report
Education: Education	educations.source_education_id	string, NOT NULL	Natural key from CRM. Anchors idempotency + areas-of-study join
Degree Code	educations.degree_code	string, NOT NULL	e.g., BBA, BS, MA
(derived)	educations.degree_level	string, indexed	undergraduate / masters / doctorate / unknown, auto-set from degree_code on save
Granting School	educations.granting_school_name	string, NOT NULL	Preserved verbatim
(resolved)	educations.granting_school_code	string, nullable, indexed	Lookup against colleges.college_name + alias map; nullable on miss
Current School	educations.current_school_name	string, NOT NULL	Preserved verbatim
(resolved)	educations.current_school_code	string, nullable, indexed	Lookup as above
Department	educations.department_name	string, nullable	Free text; populates legacy ug_program/gr_program API field
Date Issued	educations.date_issued	date, indexed
Institutional Unit	(ignored in Phase 18)	—	Frequently blank; redundant with Current School in samples. Revisit if data team confirms semantics
Institutional Units	(ignored in Phase 18)	—	Duplicate of Institutional Unit

Uniqueness: (buid, source_education_id) unique index → idempotent reimports.

Source: areas-of-study.csv → education_areas_of_study

Filter: only ingest rows where Degree Includes this Concentration? = 1.

Source column	Target column	Type	Notes
Education: Education	(join key)	—	Resolves to educations.id via source_education_id
Area of Study: Area of Study Name (col 1, e.g. AS-196770)	education_areas_of_study.person_area_of_study_id	string, unique	Natural key for idempotency
Area of Study: Area of Study Name (col 2, e.g. Business Administration)	education_areas_of_study.area_of_study_name	string, NOT NULL	Free text, preserved verbatim
(derived)	education_areas_of_study.area_of_study_name_normalized	string, indexed	Lowercased + trimmed; powers “same major” matching and future grouping
Concentration Level	education_areas_of_study.concentration_level	string enum	major / minor / concentration (lowercased)
Current Institutional Unit	education_areas_of_study.current_institutional_unit_name	string, nullable	Free text
(resolved)	education_areas_of_study.current_institutional_unit_code	string, nullable, indexed	Lookup against colleges.college_name

School Name Resolution

A short alias map handles known mismatches between source naming and colleges.college_name. Known examples from samples:

Source name	Maps to colleges.college_code
Jack C. Massey College of Business	CB
College of Business (granting, legacy)	CB
Mike Curb College of Entertainment and Music Business	CE
College of Entrmnt/Musc Busnes (legacy)	CE
College of Sciences and Mathematics	CM
College of Sciences & Math (legacy)	CM
College of Music and Performing Arts	MP
College of Vis/Performing Arts (legacy)	VP
College of Pharmacy & Health Sciences	PH
University College	UC

In addition to alias mapping, pre-seed the school reference list from source before migration imports. Source codes provided:

OM, WC, CA, CB, ED, CE, CH, CL, CS, MC, MP, CN, CP, PH, CM, CT, CV, CI, CR, HO, HU, MU, NU, RE, SC, SM, 00, UC, WA.

This pre-seed step should include canonical long name + short name + icon metadata where available so profile and stats surfaces are ready at cutover.

The importer flags these in a gap report (no row inserted into colleges; Education row inserted with *_name populated and *_code NULL). Staff/data team triage from the gap report.

API V1 Compatibility Contract (LOCKED)

These keys in Api::V1::AlumniSearchController#serialize_alumni MUST remain present and semantically equivalent post-Phase 18. Internal data source migrates to educations via a presenter; downstream consumers see no change.

Frozen keys: buid, contact_id, first_name, last_name, email, phone_number, pref_college, ug_college, ug_program, ug_degree, ug_graduation_year, ug_college_desc, gr_college, gr_program, gr_degree, gr_graduation_year, gr_college_desc, current_student, current_school, current_school_desc, current_program, intended_degree, expected_graduation_year, student_status, district, district_code, is_faculty, is_staff, category, company, position.

Derivation rules (presenter):

• ug_education = most recent educations row where degree_level = "undergraduate"
• gr_education = most recent where degree_level IN ("masters", "doctorate")
• ug_program / gr_program = department_name of that education (preserves current dept_desc semantics)
• ug_college / gr_college = current_school_code of that education
• ug_college_desc / gr_college_desc = current_school_name (or resolved colleges.college_name when code present)
• pref_college = ug_college || gr_college || alum.current_school_code
• If educations are empty for an alum during migration, derive all legacy fields using current legacy degrees logic as a temporary fallback

Coverage and Cutover Gate (LOCKED)

Track migration readiness continuously during 18.3-18.5:

• education_coverage_pct = alumni with degrees OR current-student indicators who have at least one educations row / alumni expected in awarded export
• legacy_fallback_pct = API/profile reads where presenter had to use legacy degrees fallback / total reads
• unmapped_school_name_count = distinct granting_school_name/current_school_name values with null resolved code

Cutover expectation:

• Keep fallback enabled until education_coverage_pct meets agreed threshold (recommended: >= 99%) and legacy_fallback_pct trends toward 0.
• After threshold is stable, remove fallback in a controlled follow-up change and decommission legacy schema dependencies.

Additive (opt-in ?include=educations): Full educations: [...] array with nested areas_of_study per education.

Sample Source Data (committed for reference)

Sample CSVs reviewed during 18.1 are now committed in docs/planning/champion-portal/phases/phase-18/samples/:

• awarded-degrees.csv
• areas-of-study.csv

These are the baseline input files for 18.3 import preview/commit tests and should remain stable unless the source extract shape changes.

Phase 18.2: Schema Foundation and Models ✅

Goal: Add new education schema with safe coexistence alongside legacy tables.

Deliverables

• Migration: educations table keyed to alumni.buid (with DB-level FK)
• Migration: education_areas_of_study table keyed to educations
• Model: Education (auto-derives degree_level; optional granting_college / current_college associations)
• Model: EducationAreaOfStudy (auto-derives area_of_study_name_normalized; downcases concentration_level)
• Associations on Alumni (has_many :educations, has_many :education_areas_of_study, through: :educations)
• Query indexes for common filters (buid, date_issued, degree_level, school code fields)
• Uniqueness strategy to prevent duplicate rows on repeated imports ((buid, source_education_id) unique on educations; globally unique person_area_of_study_id)
• Model tests (31 runs covering associations, validations, derivations, scopes, and DB-level FK enforcement)

What Was Implemented

• Two additive migrations (20260422093100_create_educations, 20260422093200_create_education_areas_of_study) with no impact on existing read paths.
• Education#derive_degree_level collapses degree_code into the four buckets defined in 18.1 (undergraduate / masters / doctorate / unknown) on every save, so the column never drifts from the canonical field.
• EducationAreaOfStudy.normalize_name is the single source for the lowercased + whitespace-collapsed form used by future “same major” matching.
• DB-level foreign key on educations.buid -> alumni.buid is enforced (proven by a test that asserts ActiveRecord::InvalidForeignKey on an unknown buid).
• set_fixture_class education_areas_of_study: EducationAreaOfStudy added to test/test_helper.rb because Rails’ default camelization (EducationAreasOfStudy) does not match the model class name.
• Phase 18 launch guide (docs/planning/champion-portal/qa/PHASE_18_LAUNCH_GUIDE.md) and sample data docs/files under phases/phase-18/samples/ created as part of the phase-start scaffolding.

Phase 18.3: Import Pipeline Migration (Dual-Write)

Status: Complete

Goal: Stand up a new-CSV import pipeline (awarded-degrees + areas-of-study) that writes to educations and education_areas_of_study with idempotent upserts and a downloadable gap report. Imports run as background jobs with a polled status page so production uploads cannot time out on Heroku.

Deliverables

• New CRM awarded-degrees.csv import preview/commit -> educations
• New CRM areas-of-study.csv import preview/commit -> education_areas_of_study
• Two-step import support (Educations first, then Areas of Study)
• Idempotency checks for duplicate education records (upsert on (buid, source_education_id) and person_area_of_study_id)
• Import warnings for ambiguous/missing concentration level (rows still imported with concentration_level = "unknown" and surfaced in gap report)
• Soft-school rule: rows with only one of {granting school, current school} are accepted; only rows missing both are skipped
• Explicit skip-reason warnings for areas-of-study (filtered, missing required field, missing parent education) so operators can act on the gap report
• Background-job pipeline (EducationImportScanJob + EducationImportApplyJob) with persistent EducationImportBatch records and Zlib-compressed CSV/manifest stored in DB binary columns (mirrors CurrentStudentImportBatch)
• Polled status page with preview, commit, gap CSV download, and cancel
• Listed under “Data Imports” in the settings sidebar (not “Archived Imports”)
• Import tests covering create/update/duplicate/unknown/skip mappings, batch state transitions, jobs, and full async round-trip
• Granting-school college seed migration (SCHOOL_ALIASES + colleges.college_name resolution)
• Downloadable gap CSV per import batch (missing alumni, unresolved school, invalid date, missing concentration, missing parent education, etc.) — leads with buid, contact_id, … for Advancement Services lookup
• CSP allowance for jsdelivr + cdnjs CDN-loaded scripts (Flowbite, Chart.js, @kurkle/color, Trix) — required by the import preview tables and shared chrome
• Commit-page auto-refresh fix — controller flips batch to running before enqueueing the apply job so the redirect lands on a page that polls (no more “Batch is not ready for commit (status: completed)” alert from a double click)

Spec Deviation

• Original wording: “Banner import preview/commit updated for education + areas-of-study” and “Degree import preview/commit updated for education + areas-of-study.”
• Actual scope: Per phase-3 planning interview, the legacy Banner and Degree import flows were left untouched. 18.3 stood up two new CRM-source CSV importers instead. The legacy importers will be retired (or migrated) in a later sub-phase once read paths and consumers are cut over.
• Approved by: User during 18.3 planning interview (“New CRM CSVs only - legacy Banner/degree imports stay legacy-only for now”).
• Scope expansion (post-initial-ship): User requested (a) sidebar move out of “Archived Imports”, (b) soft-school acceptance rule, (c) AoS skip-reason transparency, and (d) full background-job pipeline to avoid Heroku 30s timeouts on production-sized CSVs. All approved and implemented in 18.3.

Files Created (18.3)

• db/migrate/20260423090000_seed_granting_school_colleges.rb
• db/migrate/20260423110000_create_education_import_batches.rb
• app/models/education_import_batch.rb
• app/services/csv/education_importer.rb
• app/services/csv/education_area_of_study_importer.rb
• app/services/education_import_manifest_store.rb
• app/jobs/education_import_scan_job.rb
• app/jobs/education_import_apply_job.rb
• app/views/settings/alumni/upload_educations.html.erb
• app/views/settings/alumni/upload_areas_of_study.html.erb
• app/views/settings/alumni/show_education_import_batch.html.erb
• app/views/settings/alumni/_education_import_recent_batches.html.erb
• app/views/settings/alumni/_education_import_preview_table.html.erb
• app/views/settings/alumni/_areas_of_study_import_preview_table.html.erb
• test/services/csv/education_importer_test.rb
• test/services/csv/education_area_of_study_importer_test.rb
• test/services/education_import_manifest_store_test.rb
• test/models/education_import_batch_test.rb
• test/jobs/education_import_scan_job_test.rb
• test/jobs/education_import_apply_job_test.rb
• test/controllers/settings/alumni_controller_educations_test.rb

Files Modified (18.3)

• app/models/education_area_of_study.rb (added "unknown" to CONCENTRATION_LEVELS for import-with-fallback)
• config/routes.rb (4 form routes + 4 batch-resource routes under settings/alumni)
• app/controllers/settings/alumni_controller.rb (7 async actions + enqueue_education_import_scan helper; replaces all session-based stash/read helpers from the initial sync ship)
• app/views/settings/_sidebar.html.erb (Educations/Areas of Study moved under “Data Imports”)
• app/views/settings/alumni/upload_degrees.html.erb (added nav buttons to new CRM importers)

Flow

1. Admin uploads awarded-degrees.csv (or areas-of-study.csv) via the form on settings/alumni/upload_educations.
2. Controller creates an EducationImportBatch (Zlib-compressed CSV in csv_content) and enqueues EducationImportScanJob.
3. User is redirected to settings/alumni/education_import_batches/:id. The status page polls every 5 seconds.
4. Scan job inflates the CSV to a Tempfile, runs the importer’s preview, stores the manifest via EducationImportManifestStore (Zlib-compressed JSON in manifest_data), builds the gap CSV (gap_csv_data), and transitions the batch to scanned.
5. The status page now shows the preview table + commit button. Clicking commit enqueues EducationImportApplyJob.
6. Apply job loads the manifest, calls the importer’s commit, transitions the batch to completed, and clears csv_content + manifest_data (gap CSV is preserved for the audit trail).

Tests

• bin/test => 4331 runs, 11389 assertions, 0 failures, 0 errors, 3 skips

Phase 18.4: Read-Path Compatibility Layer

Goal: Centralize education derivation so old and new data can be served consistently.

Status: ✅ Complete

Deliverables

• New presenter/service (Alumni::EducationProfile) that derives:
  • preferred college
  • UG/GR degree/program/year fields
  • display strings currently built from degree.major.major_desc
• Replace direct alumni.degrees formatting calls in core profile/search paths
• Update Alumni#recent_degree/helper methods or provide equivalent wrappers
• Unit tests for derivation rules

What Was Implemented (18.4)

• Alumni::EducationProfile (app/services/alumni/education_profile.rb) — single source of truth for derived education data on an Alumni record. Education-first with Degree fallback per alumni. Value objects: Privacy, Entry, CurrentSchool.
  • Entry carries normalized fields (degree_code, degree_level, major_name, major_code, department_name, college_code, college_name, college_name_short, college_icon, date_issued, source, hide_year) plus legacy aliases (major_desc, degree_date) and a custom as_json that preserves the V1 API contract.
  • degree_level is derived at read time via Education.level_for(degree_code), not trusted from the stored column — resilient against fixtures and stale rows that skip the before_validation callback.
  • Privacy auto-inferred from alumni.champion.education_privacy unless viewer: matches the displayed champion (self-view never redacts). hidden? empties rollups and replaces display_summary with “Belmont University graduate”; hide_year? returns nil from Entry#year.
  • Public API: undergraduate, graduate, all_entries, recent, current_school, college_codes, major_codes, grad_years, display_summary, to_export_hash.
• Alumni#recent_degree and Alumni#graduation_years now delegate to the presenter (memoized via Alumni#education_profile).
• AlumniHelper#generate_checkin_data rewritten to use to_export_hash — single export contract used by the event check-in copy button.
• AlumniHelper#degree_border_color updated to accept either Degree or Entry (classifies by degree_code[0]).
• Lookup Portal views swapped to iterate the presenter: alumni/show.html.erb degree card, alumni/search.html.erb (all three mobile/desktop blocks).
• Champion Portal views swapped: cp/directory/_champion_card.html.erb, cp/directory/_recommendation_card.html.erb, cp/directory/show.html.erb, cp/profile/show.html.erb. Undergrad/grad split now driven by derived degree_level instead of hard-coded code lists.
• Matching services swapped to use presenter rollups: Cp::CommunityMatchingService#check_degree_suggestions, Cp::AlumniLikeMeService#extract_profile, Cp::CareerConnectService#champion_clusters & #candidate_degree_clusters.

Deferred (carries to BACKLOG)

• API V1 serializer (Api::AlumniController) continues using recent_degree.as_json with the contract-preserving payload from Entry#as_json. Full serializer migration is Phase 18.5.
• Champion Portal partials still touching alumni.degrees directly: cp/profile_wizard/_step_confirm_education.html.erb, cp/careers/_career_connect_cards.html.erb, cp/leadership/{community,welcome_message,members}.html.erb. These render but were out of scope for the core profile/search paths; tracked in BACKLOG for follow-up.
• AlumnusEngagementDecorator rename (not an 18.4 concern; in BACKLOG).

Tests

• 23 unit tests in test/services/alumni/education_profile_test.rb
• 5 parity tests in test/services/alumni/education_profile_parity_test.rb (Degree-sourced vs Education-sourced output identical for same data)
• bin/test => 4363 runs, 11548 assertions, 0 failures, 0 errors, 3 skips

Phase 18.5: Lookup API Backwards Compatibility

Goal: Keep API response contract stable while reading from the new model.

Deliverables

• Update API serialization internals to use compatibility layer
• Preserve existing response keys and semantics for downstream apps
• Add optional versioned enrichment (educations block) behind V2 or opt-in flag (deferred → BACKLOG)
• Add per-record legacy fallback when educations are empty during migration window
• Contract tests for current API consumers
• Coverage metrics instrumentation (education_coverage_pct, legacy_fallback_pct, unmapped_school_name_count)
• Migration/deprecation notes for downstream teams

What Was Implemented (18.5)

• Alumni::EducationProfile#source added — returns :education, :degree_fallback, or :none. Powers the new _source advisory field exposed by both API endpoints.
• Api::V1::AlumniSearchController#serialize_alumni refactored to consume profile.to_export_hash instead of inline UG/GR regex logic. Removes the duplicated degree-categorization rules and ensures Education-first sourcing per record. Eager loads expanded to include { educations: :areas_of_study } alongside the legacy degrees: { major: :college } chain.
• Api::AlumniController#index (legacy staff typeahead) extended to emit _source alongside the existing recent_degree payload. Contract for the four legacy keys (major_desc, college_name → short, degree_code, degree_date) preserved via Entry#as_json.

• _source field added to both endpoints’ responses ("education"

  "degree_fallback"

  "none"). All other response keys unchanged — strictly additive.

• EducationCoverageService (app/services/education_coverage_service.rb) — computes coverage metrics (5-min cache). unmapped_school_name_count uses the broad definition: counts both educations.granting_school_name and education_areas_of_study.current_institutional_unit_name rows where the code is blank and the free-text name does not match any colleges.college_name or college_name_short. Also exposes unmapped_school_names (aggregated drill-down with sample BUID + source ID) and class-level legacy_fallback_buids.
• /api/v1/education_coverage — new internal endpoint (API-key authenticated) returning the service output as JSON.
• /settings/data_health — new admin dashboard rendering three coverage cards (Education coverage, Legacy Degree fallback, Unmapped school names) plus a “How these are calculated” explainer. Sidebar link added under Data Imports. Includes an expandable drill-down table of distinct unmapped school names (with occurrence count, source, sample BUID + source ID) and a CSV download (/settings/data_health/unmapped_schools.csv) so the data team can triage and fix them in bulk via the colleges admin.
• bin/rails alumni:legacy_fallback_buids — stopgap rake task printing one BUID per line of alumni still served by the legacy degrees table. Used until Phase 18.7 surfaces this as a filterable column on the Alumni search UI (BACKLOG entry added).

Deferred (carries to BACKLOG)

• V2 enrichment — Optional ?include=educations block exposing the full educations + areas_of_study structure for downstream apps that want richer data than the 18.4 check-in blob. Versioned behind an opt-in flag or a V2 namespace.
• Alumni search “Data source” filter — Front-end filter (education / degree_fallback / none) + results column to expose the legacy-fallback roster in the UI. Belongs with Phase 18.7 UI rollout. bin/rails alumni:legacy_fallback_buids ships in 18.5 as a stopgap.

Tests

• test/services/alumni/education_profile_test.rb — 3 new tests for #source
• test/services/education_coverage_service_test.rb — service unit tests (6 tests)
• test/controllers/api/alumni_controller_test.rb — new file, 5 tests covering legacy typeahead + _source
• test/controllers/api/v1/education_coverage_controller_test.rb — auth + payload shape
• test/controllers/settings/data_health_controller_test.rb — auth + render
• test/controllers/api/v1/alumni_search_controller_test.rb — extended check-in blob assertion to include _source + added two new tests for the degree_fallback and none branches
• bin/test ⇒ 4384 runs, 11605 assertions, 0 failures, 0 errors, 3 skips

Phase 18.6: Stats & Engagement Aggregations Migration

Goal: Move all stats-page aggregation logic from degrees → majors → colleges joins to a shared Education::AggregateScope query object that is Education-first with Degree fallback per BUID. Includes a side-by-side ?source=legacy|education toggle so staff can validate parity before legacy is removed.

Deliverables

• Education::AggregateScope query object (set-based UNION; Education-first w/ Degree fallback per BUID)
• Parity test suite (test/services/education/aggregate_scope_parity_test.rb) comparing degree-derived vs education-derived counts for each migrated query
• Alumni model scopes migrated invisibly to UNION SQL: with_degrees, without_degrees, filter_by_college, filter_by_fiscal_year
• StatisticsController — 7 query patterns migrated (fiscal year, presidential era, top 25 majors, college breakdown, generation cohort, population counts, degrees CSV)
• EngagementStatsController — 3 college/year filter sites migrated
• EngagementStats::BaseService (build_filtered_engagement_scope, build_engaged_degree_scope, build_all_degree_scope)
• EngagementStats::DemographicsService — 7 aggregations migrated (year/level, college, major-with-college, top-25 majors filtered to concentration_level = 'major')
• Side-by-side toggle ?source=legacy|education on /statistics, /engagement_stats, demographics tab
• Coverage banner on stats pages pulling from EducationCoverageService
• New cache namespace aggregate_stats_v2:* (5-min TTL during 18.6; bumped to 1-hour in 18.7); invalidated by both EducationImportBatch and DegreeImportBatch completion + the existing /engagement_stats/clear_cache action
• Sweep remaining CP partials still on alumni.degrees to Alumni::EducationProfile (cp/profile_wizard/_step_confirm_education, cp/careers/_career_connect_cards, cp/leadership/{community,welcome_message,members}) — carried from 18.4 backlog
• Regression coverage: every migrated aggregation gets a parity assertion

Explicitly Deferred

• Minors/concentrations breakdown chart (backlog, no phase target)
• CSV exporter migration (alumni_exporter, event_rsvp_converter, event_converter_controller) → 18.7
• AlumniFilterService population logic full migration → 18.7
• Remove side-by-side toggle + Coverage banner, collapse aggregate_stats_v2:* namespace → 18.8
• Drop legacy degrees / majors / colleges tables and supporting code → 18.8

Phase 18.7: CSV Exporters, Filter Service, and Model Scope Cleanup

Goal: Migrate the data-export surfaces (CSVs, CRM converters) from degrees → majors → colleges to the Education::AggregateScope + Alumni::EducationProfile stack established in 18.6. Stats pages are already cut over by this point; this phase is about contracts that flow to downstream consumers (Advancement Services CSVs, CRM event mapping).

Deliverables

• Csv::AlumniExporter migrated (preserve ug_college, gr_college, ug_program, gr_program, etc. column contract — pull from EducationProfile.to_export_hash)
• Csv::EventRsvpConverter migrated (UG/GR classification via EducationProfile, not direct alumni.degrees.select { ... })
• Tools::EventConverterController migrated (per-attendee degree extraction)
• AlumniFilterService population logic moved fully behind Education::AggregateScope
• Csv::CurrentStudentImporter guard check audited (verify Education-or-Degree presence, not Degree-only)
• Cache namespace bumped to 1-hour TTL; aggregate_stats_v2:* stays in place
• CSV contract tests: column headers and column-by-column values match pre-migration fixtures
• Update QA checklist with CRM exporter regression steps

Explicitly Deferred

• Remove side-by-side toggle + Coverage banner + collapse cache namespace → 18.8
• Drop legacy tables → 18.8
• Alumni search “Data source” filter UI → 18.8 (already tracked)

Phase 18.8: Current Student Data Consolidation (Educations as Source of Truth)

Goal: Move per-enrollment current-student data (school, program, intended degree, expected graduation year, per-record student status) out of alumni denormalized columns and into the educations table where it naturally belongs. Retire the separate Csv::CurrentStudentImporter in favor of a single education-feed pipeline.

Why: Today an alumni who completed a BBA and is enrolled in an MBA has two educations rows but only one alumni.student_status column. The single-column model can’t represent “awarded + currently enrolled” simultaneously. Worse, the separate current-student CSV importer writes denormalized fields to alumni that get overwritten or go stale relative to the education records. Moving per-enrollment data to educations makes each row self-describing and eliminates a whole class of stale-data bugs.

Schema Changes

Add to educations:

• expected_graduation_year integer — populated from “Preferred Year” CSV column when date_issued is NULL
• student_status string — per-record CRM status (awarded / pending / withdrawn)

Remove from alumni (after all read paths migrated):

• current_school_code, current_program_desc, intended_degree_code, expected_graduation_year
• Keep student_status on alumni as a rolled-up summary (awarded is a one-way ratchet)

Per-Education Student Status Rules (Ratchet)

When processing each education row, update alumni.student_status per this table:

Row student_status	date_issued	Effect on alumni.student_status
awarded	present	Always upgrade to awarded
awarded	NULL	Always upgrade to awarded
pending	NULL	Set pending only if not already awarded
withdrawn	NULL	Set withdrawn only if not already awarded

The contact importer’s student_status write path follows the same rule (never downgrades awarded).

Deliverables

• Migration: add expected_graduation_year + student_status columns to educations (and indexes)
• Csv::EducationImporter: accept preferred_year + student_status columns; write to education record; apply ratchet rule to alumni.student_status
• Csv::AffinaquestContactImporter: enforce one-way awarded ratchet (never downgrade)
• Alumni model:
  • Rewrite current_student? to check educations.where(date_issued: nil).exists? (independent of student_status)
  • Rewrite current_students scope to use Education join
  • Rewrite current_enrollment_display to pull from in-progress educations
  • Retire Alumni::STUDENT_STATUSES validation reference if no longer needed
• Alumni::EducationProfile: enrollment display reads from educations.where(date_issued: nil); remove fallback to alumni.current_* fields
• Alumni show page: render Current Enrollment section from any in-progress education, even when alumni also has awarded degrees (e.g., BS + enrolled MS)
• Alumni search results: show “Pending” / “Currently Enrolled” indicator when alumni has in-progress education
• Retire Csv::CurrentStudentImporter:
  • Delete service file
  • Delete Settings::CurrentStudentsController + routes + views
  • Remove from Settings nav
  • Delete associated tests + fixtures
• Migration: drop current_school_code, current_program_desc, intended_degree_code, expected_graduation_year from alumni (separate deploy after new code is stable)
• CHANGELOG + MODEL_RELATIONSHIPS.md updated

Build Order (Safe / Additive-First)

1. Migration: add new columns to educations (purely additive)
2. Education importer changes (write to new columns + ratchet)
3. Contact importer one-way ratchet
4. Model + display layer migration (read from educations)
5. View updates (show + search)
6. Retire current_student_importer + UI
7. Separate deploy: migration to drop 4 columns from alumni

Explicitly Deferred

• Dropping the 4 alumni columns ships as a separate, second deploy after step 1-6 have been live and stable
• Audit of non_degreed_alumni scope semantics (currently uses student_status: ["withdrawn", nil]) — may need re-evaluation once educations-with-withdrawn-status exist

Completion Summary (Deploy 1 of 2)

• Added educations.expected_graduation_year and educations.student_status with indexes
• Replaced inline “in-progress” predicates with shared Education.in_progress scope and migrated alumni enrollment read paths
• Updated Csv::EducationImporter to write per-education enrollment fields and apply one-way awarded ratchet logic
• Enforced one-way awarded status handling in Csv::AffinaquestContactImporter
• Retired Csv::CurrentStudentImporter and removed all related controller, jobs, routes, views, tests, and rake task surface area
• Updated Alumni search/show rendering, including expected degree display formatting and smaller current-student/deceased badges
• Updated tests, fixtures, changelog, and model relationships documentation
• Full suite verification: 4429 runs, 0 failures, 0 errors, 3 skips

Phase 18.9: UI Rollout, Legacy Decommission, and Cleanup

Goal: Complete UI migration, retire legacy dependencies, and ship the public-facing UX changes (data-source filter, search columns) that depend on the new model being fully primary.

Decision (freeze, not drop): Per direction, Phase 18.9 freezes the legacy degrees table — all read paths move to the Education model exclusively, but the Alumni::EducationProfile→degrees fallback and the physical degrees table remain until a later cleanup tag once Education coverage is ≥ 99%. colleges and majors are retained as active reference tables (colleges is the canonical college-code→label source; majors backs the major dropdown, community naming, and banner-import validation) and are not slated for removal. Work is grouped: B (remove toggle/banner/source plumbing), C (migrate per-record + filter reads), A (profile/areas-of-study display polish), D (freeze importer write paths).

Completion Summary — Groups B + C (complete)

• Group B — toggle/banner/source removal: Deleted shared/_stats_source_toggle and shared/_stats_coverage_banner. Removed ?source handling, @source/education_source?, legacy SQL branches, and source: args from StatisticsController, EngagementStatsController, EngagementStats::{BaseService,OverviewService,DemographicsService,ActivityPairsService}. Collapsed the aggregate_stats_v2:* cache namespace back to engagement_stats_*. Removed the *_legacy Alumni scopes and the legacy source test.
• Group C — per-record + filter read migration: Migrated every remaining alumni.degrees read to Alumni::EducationProfile / Education::AggregateScope: engagement-stats alumni tables, alumni/top_engaged, the stats year-filter dropdown, Champion verification show + search results, the Champion show recent-degree block, Cp::CommunityDetectionService (detection loops + member finders), Cp::Community#eligible_for?, Cp::HomeHelper grad years, Cp::AlumniLikeMeService scoring, Csv::AlumniExporter, Csv::EventRsvpConverter, Tools::EventConverterController, Champions::CommunitiesController, and LegacyVerificationService#match. Also migrated EngagementStatsController#calculate_top_alumni_data_optimized (missed in B), removed the dead fiscal_year_sql helper, and migrated the filter joins: Alumni.filter_by_name year filter, Cp::DirectoryController#load_colleges/#grad_years_by_decade, Cp::CareerConnectService cluster matching, and Cp::CommunityMatchingService.find_matching_champions (college + major).
• Privacy convention: data-driven (non-display) reads pass Alumni::EducationProfile::Privacy::NONE; display reads keep inferred privacy.
• Intentionally left on Degree: EducationProfile→degrees fallback and EducationCoverageService raw Degree counts.
• Tests: full suite green — 4426 runs, 0 failures, 0 errors, 3 skips; added a find_matching_champions regression test for an Education-only champion (no Degree row).

Completion Summary — Groups A + D (complete)

• Group D — freeze legacy degree write paths: Added Degree.writes_frozen? (backed by WRITES_FROZEN = true) and Degree::FROZEN_MESSAGE pointing users to the Educations CRM import. Guarded every degree write: Csv::AlumniImporter.import_degrees is now a no-op returning { created: 0, frozen: true }; Csv::BannerImporter#commit_rows still creates/updates alumni but increments @skipped_degrees instead of writing degree rows; Settings::AlumniController#import_degrees/#import_degrees_commit short-circuit the degree-write block and surface FROZEN_MESSAGE. Added amber frozen banners to upload_degrees + upload_banner views.
• Group A — surface areas of study: Alumni::EducationProfile now carries areas_of_study on each Entry (built from Education#areas_of_study), with an areas_of_study_summary that groups by concentration_level (major/minor/concentration), labels + pluralizes each group, orders major→minor→concentration, and joins with •. Rendered under each education entry on the Lookup alumni show page and the Champion Portal directory profile (respecting education privacy). Degree-fallback entries carry no areas and render nothing.
• Tests: full suite green — 4434 runs, 0 failures. Added Degree freeze tests, an AlumniImporter no-op test, updated Banner importer + Banner controller tests to assert frozen behavior, and 5 EducationProfile area-of-study tests.

Completion Summary — Major filter migrated off Degree (complete)

• education_areas_of_study.major_code: New nullable, indexed column sourced from the CRM “Area of Study: External Id” field — the program (major) code, valid for majors, minors, and concentrations. Csv::EducationAreaOfStudyImporter reads it as the appended 8th CSV column (upcased/trimmed), surfaces it in the import preview table, persists it on commit, and includes it in the no-op comparison so re-imports stay idempotent.
• Alumni.filter_by_major: Migrated from the frozen degrees table to match alumni via education_areas_of_study.major_code joined through educations. The alumni search/stats major dropdown already passes major_code, so it matches directly with no name fuzzing. This was the last read path on degrees other than the intentional EducationProfile→degrees fallback.
• Tests: importer tests (External Id → upcased major_code, persistence, no-op re-import, nil when column absent) + filter_by_major model tests (matches major + concentration by code, none for unknown code, ignores blank).

Deliverables

• Alumni Lookup show pages updated to display new education/areas-of-study model (Group A)
• Alumni Network profile pages updated (Group A — Champion Portal directory profile)
• Degree stats labeling and UX updated where terminology changes (Group A — areas of study surfaced under education entries)
• Alumni search “Data source” filter UI (education / degree_fallback / none) with matching results column — supersedes the alumni:legacy_fallback_buids rake task (deferred)
• Side-by-side ?source=legacy toggle removed from stats pages (Group B)
• Coverage banner removed (or moved to admin-only Data Health dashboard) (Group B — removed from stats pages; Data Health dashboard already standalone)
• aggregate_stats_v2:* cache namespace collapsed back to engagement_stats_* (Group B)
• Legacy degree write paths frozen (Degree.writes_frozen?; importers + Settings controller skip degree rows, alumni records still created/updated) (Group D)
• Legacy-table deprecation checklist (not immediate drop): callers audited, Degree model marked deprecated, removal scheduled for a separate clean-up tag (later cleanup tag)
• Final rollout playbook + rollback procedure (later cleanup tag)

---

Testing Strategy

• Write tests as each sub-phase is implemented (no deferred test pass)
• Add migration parity tests comparing legacy-derived and new-derived outputs
• Add API contract tests to guarantee backward-compatible fields
• Add importer idempotency tests for repeated files and duplicate rows
• Add report regression tests for counts grouped by year/college/degree level

---

Risks and Mitigations

1. Silent behavior drift in UG/GR derivation
   • Mitigation: shared compatibility presenter with contract tests
2. Duplicate/partial data from dual-write period
   • Mitigation: uniqueness constraints + idempotent import logic
3. API downstream breakage
   • Mitigation: preserve V1 fields, stage V2 enrichments separately
4. Reporting performance regressions
   • Mitigation: early indexing and query benchmarking on realistic datasets
5. Historical college mapping ambiguities
   • Mitigation: allow both code and name fields, preserve source text

---

Dependencies and Ordering

• 18.1 must complete before schema freeze in 18.2
• 18.2 must complete before import migration in 18.3
• 18.3 and 18.4 unlock API compatibility migration in 18.5
• 18.6 depends on compatibility layer from 18.4
• 18.7 happens after read paths and API compatibility are stable

---

Documentation Updates Required During Implementation

• docs/CHANGELOG.md
• docs/development/MODEL_RELATIONSHIPS.md
• docs/planning/champion-portal/phases/README.md
• app/controllers/champions/roadmap_controller.rb status + sub-phase entries
• API docs for lookup endpoints (legacy and new fields)

---

Planning Checkpoint (Required Before 18.1 Implementation)

Before implementation starts, complete the Sub-Phase Planning Checkpoint:

• In-depth interview
• Outstanding questions resolved
• Scope review and adjustments
• Backlog review for related deferred data tasks
• Written planning summary + user confirmation