alumni_lookup

CSV Importers Documentation

Version: 2.1
Last Updated: July 2026 (Phase 24 — EmploymentImporter added)
Purpose: Document all CSV import services, their expected formats, and usage

Overview
Importers Summary
Education Importer
Employment Importer
Affinaquest Contact Importer
Alumni Importer
Banner Importer
Event RSVP Converter
Common Patterns
Running Imports

Overview

The Alumni Lookup application uses a collection of CSV importers to ingest data from various university systems. All importers follow a consistent pattern with class methods for:

call(file_path) — Execute the import
preview(file_path, limit: 25) — Preview what would be imported without committing
scan(file_path) — Analyze the file and report statistics

Importer Location

All CSV importers are located in:

app/services/csv/

Standard Interface

# Typical importer usage
Csv::SomeImporter.call("path/to/file.csv")        # Execute import
Csv::SomeImporter.preview("path/to/file.csv")     # Preview only
Csv::SomeImporter.scan("path/to/file.csv")        # File analysis

Importers Summary

Importer	Purpose	Source System	Key Fields
EducationImporter	Import awarded + in-progress degree records	Affinaquest CRM	BUID, source_education_id, degree_code, date_issued, preferred_year, student_status
EducationAreaOfStudyImporter	Import areas of study linked to education records	Affinaquest CRM	person_area_of_study_id, area_of_study_name, concentration_level
EmploymentImporter	Import current employment (per-BUID replace)	BruinQuest CRM	BUID, source_employment_id, employer_name, job_title, start_date, start_date_qualifier
AffinaquestContactImporter	Full alumni contact sync	Affinaquest CRM	All contact fields, addresses, employment
AlumniImporter	Basic alumni data	Various	BUID, name, contact info
BannerImporter	Legacy degree records	Banner SIS	BUID, degree, major, graduation date
EventRsvpConverter	Convert event RSVPs to activities	Event systems	BUID, event date, activity type

Note: The former Csv::CurrentStudentImporter was retired in Phase 18.8. Current enrollment data (expected graduation year, per-record student status) is now carried directly on educations rows produced by EducationImporter. See Education Model Foundation for the canonical Education.in_progress scope.

Education Importer

Files: app/services/csv/education_importer.rb, app/services/csv/education_area_of_study_importer.rb

Purpose

Imports CRM-sourced education records (awarded degrees and in-progress enrollments) into the educations table with idempotent upserts keyed on (buid, source_education_id). Since Phase 18.8, the importer also writes per-enrollment student data (expected_graduation_year, student_status) directly to the education row and applies a one-way awarded ratchet to alumni.student_status.

Expected CSV Format

Column Name	Required	Maps To	Notes
`Contact: BUID`	Yes	`educations.buid`	Belmont University ID
`Education: Education`	Yes	`educations.source_education_id`	CRM natural key; anchors idempotency
`Degree Code`	Yes	`educations.degree_code`	e.g. `BBA`, `BS`, `MA`
`Granting School`	Soft	`educations.granting_school_name`	At least one school required
`Current School`	Soft	`educations.current_school_name`	At least one school required
`Department`	No	`educations.department_name`	Free-text program/department
`Date Issued`	No	`educations.date_issued`	NULL → row treated as in-progress
`Preferred Year`	No	`educations.expected_graduation_year`	Integer; used when `date_issued` is NULL
`Student Status`	No	`educations.student_status`	`pending` / `withdrawn` / `awarded`; drives `alumni.student_status` ratchet

School codes are resolved from the free-text name against colleges.college_name and a built-in alias map; unresolved names are included in the per-batch gap CSV.

Student Status Ratchet (Phase 18.8)

For every education row, alumni.student_status is updated per this one-way rule:

Row `student_status`	Effect on `alumni.student_status`
`awarded`	Always upgrade to `awarded` (never downgrade)
`pending`	Set `pending` only if not already `awarded`
`withdrawn`	Set `withdrawn` only if not already `awarded`

When an awarded row lands (date_issued present), the legacy denormalized enrollment columns on alumni (expected_graduation_year, intended_degree_code, current_program_desc, current_school_code) are cleared so stale readers see a consistent empty state until those columns are dropped in a follow-up deploy.

Upload UI

The education import is triggered through the Lookup Portal Settings, not via rake tasks:

Settings → Alumni → Educations CRM Import
Settings → Alumni → Areas of Study CRM Import

Uploads enqueue background jobs (EducationImportScanJob → EducationImportApplyJob) backed by EducationImportBatch. The status page auto-polls, then shows a preview + commit flow.

In-progress Enrollment (Single Source of Truth)

An educations row is considered “in progress” when:

date_issued IS NULL
AND student_status IS NULL OR student_status = 'pending'

This is encoded in Education.in_progress scope. All Alumni enrollment methods (.currently_enrolled?, .current_student?, .current_enrollment_education, etc.) delegate to this scope. Never read alumni.current_school_code or similar legacy columns for enrollment checks — they will be dropped in Phase 18.8 deploy 2.

Employment Importer

Added: Phase 24.2–24.3 (July 2026). Spec: phase-24/README.md

Purpose

Ingest the BruinQuest employment export so staff can answer “which of our alumni work at HCA?” — the question behind corporate partnerships, event hosting and career-connection asks.

The two things that make this importer different

1. It stores current employment only, one-to-many. The source feed emits an alum’s current positions and nothing else. An employment history table would therefore be populated with fiction. An alum genuinely holding two jobs gets two employments rows.

2. It is a per-BUID replace, not an accumulate. For every BUID the file names with at least one usable row, the file’s rows become that alum’s complete current set — existing rows whose source_employment_id the file omits are deleted. This is the only way stale employment gets removed. Two guard rails:

Alumni absent from the file are untouched, so a partial export is safe. A global truncate-and-sync would wipe everyone the export omitted.
A BUID whose every row was rejected is treated as saying nothing. Deleting real rows on the strength of a row we just refused to import would be data loss caused by a broken row.

Because the replace is destructive by design, to_delete is a first-class preview count alongside create/update/skip, and the batch page lists the doomed rows in their own table above the incoming ones. The operator sees the number before committing.

Expected CSV Format

CSV Header	Maps To	Notes
`Contact: BUID`	`buid`	Identity resolves on BUID only; the name columns are ignored
`Employment: ID`	`source_employment_id`	Salesforce id (`a14Uq000004GS3i`). The upsert key
`Employment: Employment`	`source_employment_number`	Display key (`e252537`). Stored, never keyed on
`Employer Name`	`employer_name`	Free text, may be blank
`Title`	`job_title`	Frequently blank
`Started`	`start_date_qualifier`	`On` → `on`, `On or Before` → `on_or_before`, blank → nil
`Start Date`	`start_date`	`M/D/YY`, parsed by `Csv::DateParser`

Columns are resolved by header name, not position, so the import survives column reordering. The export pads with thousands of ,,,,,,,, rows; those are dropped at parse time and excluded from every count.

Gap Reasons

Blocking (row is skipped):

Reason	Trigger
`blank_buid`	`Contact: BUID` empty but the row has data
`missing_alumni`	BUID has no matching `alumni` row
`blank_source_employment_id`	`Employment: ID` empty — nothing to key the upsert on

Reported but not blocking (the record imports, and the gap CSV flags it for upstream cleanup):

Reason	Trigger
`unparseable_start_date`	`Start Date` present but `Csv::DateParser` returns nil
`unknown_started_qualifier`	`Started` is a value other than the two known ones

A blank Employer Name imports silently — it is not a gap reason at all. The row still gets a per-row warning in the preview (“No employer name — imported for the title/date, but it will not match an employer search”), but it does not appear in gap_rows, the gaps collection, or the gap CSV. Blank employer names are the largest blank bucket in the real export; the record imports cleanly and there is nothing upstream to chase — unlike a BUID that doesn’t resolve or a date that won’t parse, a missing employer name isn’t fixable data, it’s just data the CRM doesn’t have.

⚠️ employer_name and employer_name_normalized are nullable. Any employer search must tolerate nulls — a blank-employer row can never match one.

Employer Normalization

employer_name_normalized = transliterate → downcase → strip punctuation → collapse whitespace, backed by a GIN trigram index (the only one in the codebase — justified because the employer filter is trigram-first, not trigram-as-fallback).

Legal suffixes (LLC, Inc., PLC, LLP) are deliberately kept. Trigram search matches across them without stripping, and removing them would collide genuinely distinct entities (“Smith LLC” and “Smith Inc” are not the same company).

Employer entity resolution is out of scope — “VUMC Vanderbilt University Medical Center” appearing 12 times is one string, not 12 entities, but deduplicating into an employers table is a separate project. See BACKLOG.

Upload UI

Settings → Data Imports → Employment (CRM)

Uploads enqueue background jobs (EmploymentImportScanJob → EmploymentImportApplyJob) backed by EmploymentImportBatch, mirroring the education import flow. The status page polls for live progress; batches stuck >30 minutes auto-fail on poll, which catches Heroku R14/R15 OOM kills that prevent the job’s own rescue from running.

Verifying an Import

Re-uploading an identical file must report 0 created, 0 updated, 0 deleted — every row no-change. Anything else means the upsert key or the comparison set has drifted.

Where the Imported Data Shows Up (Phase 24.4–24.5)

The import is only half the feature — here is every surface that reads employments, which is also the list to spot-check after a production import:

Surface	Shows	Rule
`alumni#show` — Employment card (after Degrees)	All current positions	`by_recency`; `Since Aug 2013` / `Since on or before Mar 2026` / no line when undated
`alumni#search` — result row, directly under the name	The most recent position only	`Alumni#current_employment`; title then employer, each its own line, before district/BUID
`alumni#search` — Employer input, in Advanced Search Options	—	`filter_by_employer` (ILIKE) → `filter_by_fuzzy_employer` (trigram) fallback
`Csv::AlumniExporter`	The most recent position only	`employer` + `job_title` columns

Three properties of the search worth knowing when debugging it:

The term is normalized before matching, through the same Employment.normalize_employer the importer applied to the stored column. "Prince Properties, LLC" typed verbatim matches the stored prince properties llc. A term that normalizes to nothing returns no results rather than matching everything.
Matching goes through a subquery, not a join. Employment is one-to-many, so a join would list an alum once per matching position — in the results and in the CSV.
A blank-employer row can never match. employer_name_normalized is null for those rows. That is expected, not a bug: they are imported for their title and start date.

Fuzzy fallback shares the fuzzy_applied flag with the name search, so an employer typo shows the same “No exact match found — showing closest spelling matches” notice. soundex is deliberately not used here — it is tuned for single surnames and produces false matches on multi-word company names.

Affinaquest Contact Importer

File: app/services/csv/affinaquest_contact_importer.rb

Purpose

Full alumni contact synchronization from the Affinaquest CRM system. This is the primary source of alumni biographical, contact, and relationship data.

Key Features

Creates or updates alumni records by BUID
Handles address normalization and deduplication
Maps employment information
Processes preferred contact methods
Manages constituent codes and attributes

Expected CSV Columns

Primary fields include:

BUID — Belmont University ID (required)
CONTACT_ID — BruinQuest/Salesforce ID
FIRST_NAME, LAST_NAME, MIDDLE_NAME
PREFERRED_NAME (nickname)
EMAIL, PHONE
ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP
EMPLOYER, JOB_TITLE
BIRTHDATE / DATE_OF_BIRTH / DOB — Date of birth (recency-aware, US date format supported)
And many more constituent-related fields

Usage

bin/rails alumni:import_contacts CSV=/path/to/affinaquest_export.csv

Alumni Importer

File: app/services/csv/alumni_importer.rb

Purpose

Basic alumni data import for simpler use cases or supplemental data loads.

Expected CSV Format

Column	Required	Description
`BUID`	Yes	Belmont University ID
`FIRST_NAME`	Yes	First name
`LAST_NAME`	Yes	Last name
`EMAIL`	No	Email address
`PHONE`	No	Phone number

Usage

bin/rails alumni:import CSV=/path/to/file.csv

File: app/services/csv/banner_importer.rb

Purpose

Imports degree records from the Banner Student Information System. This creates the formal academic history linking students to their majors, colleges, and graduation dates.

Expected CSV Format

Column	Required	Description
`BUID`	Yes	Belmont University ID
`DEGREE_CODE`	Yes	Degree type (BS, BA, MBA, etc.)
`MAJOR_CODE`	Yes	Major code
`DEGREE_DATE`	Yes	Graduation date
`COLLEGE_CODE`	No	College code (derived from major if not provided)

Relationship to Degrees

The Banner import creates records in the degrees table, which are then joined to:

majors table (via major_code)
colleges table (via college_code on major)

Usage

bin/rails degrees:import CSV=/path/to/banner_degrees.csv

Event RSVP Converter

File: app/services/csv/event_rsvp_converter.rb

Purpose

Converts event RSVP records from event management systems into engagement activities. This supports the engagement tracking and scoring system.

Expected CSV Format

Column	Required	Description
`BUID`	Yes	Belmont University ID
`EVENT_DATE`	Yes	Date of the event
`EVENT_NAME`	No	Name of the event
`RSVP_STATUS`	No	RSVP response (attending, declined, etc.)

Activity Mapping

RSVPs are converted to engagement activities with:

Activity type: event_rsvp
Engagement level: Based on event type
Description: Event name

Usage

bin/rails events:convert_rsvps CSV=/path/to/rsvps.csv

Common Patterns

Field Mapping

All importers use a FIELD_MAPPING constant to map CSV column headers to internal field names:

FIELD_MAPPING = {
  "BUID" => :buid,
  "FIRST_NAME" => :first_name,
  "CURRENT_SCHOOL" => :current_school
}.freeze

Safe Processing

Importers wrap processing in transactions and handle errors gracefully:

def call(file_path)
  results = { processed: 0, created: 0, updated: 0, errors: [] }
  
  CSV.foreach(file_path, headers: true) do |row|
    ActiveRecord::Base.transaction do
      process_row(row, results)
    rescue => e
      results[:errors] << { row: row, error: e.message }
    end
  end
  
  results
end

Preview Mode

All importers support preview mode that analyzes without committing:

def preview(file_path, limit: 25)
  # Analyze and return preview data
  # Does NOT commit any changes
end

Running Imports

Rake Task Pattern

Most imports follow this rake task pattern:

# Standard import task structure
bin/rails namespace:task CSV=/path/to/file.csv

# With confirmation for destructive operations
bin/rails namespace:task CSV=/path/to/file.csv CONFIRM=1

# Limit rows for testing
bin/rails namespace:task CSV=/path/to/file.csv LIMIT=100

Available Rake Tasks

Task	Description
`alumni:import`	Basic alumni import
`alumni:import_contacts`	Full Affinaquest contact import
`alumni:legacy_fallback_buids`	Print BUIDs still on legacy `degrees` (no `educations` rows)
`degrees:import`	Import legacy Banner degree records
`events:convert_rsvps`	Convert RSVPs to activities

Note: Education and Areas-of-Study imports are managed through the Settings UI (Settings → Alumni), not rake tasks.

Environment Considerations

Development: Safe to run imports freely
Staging: Test imports before production
Production: Always preview first, consider off-hours for large imports

Error Handling

All imports collect errors and report at the end:

results = Csv::SomeImporter.call("file.csv")
puts "Processed: #{results[:processed]}"
puts "Created: #{results[:created]}"
puts "Updated: #{results[:updated]}"
puts "Errors: #{results[:errors].count}"
results[:errors].each { |e| puts "  - #{e[:error]}" }

Affinaquest Import — Detailed Affinaquest import documentation
Degree Upload Preview — Degree import workflow
Engagement Stats System — How activities affect engagement

Document maintained by the Alumni Engagement and Engineering teams.

alumni_lookup

CSV Importers Documentation

Table of Contents

Overview

Importer Location

Standard Interface

Importers Summary

Education Importer

Purpose

Expected CSV Format

Student Status Ratchet (Phase 18.8)

Upload UI

In-progress Enrollment (Single Source of Truth)

Employment Importer

Purpose

The two things that make this importer different

Expected CSV Format

Gap Reasons

Employer Normalization

Upload UI

Verifying an Import

Where the Imported Data Shows Up (Phase 24.4–24.5)

Affinaquest Contact Importer

Purpose

Key Features

Expected CSV Columns

Usage

Alumni Importer

Purpose

Expected CSV Format

Usage

Banner Importer

Purpose

Expected CSV Format

Relationship to Degrees

Usage

Event RSVP Converter

Purpose

Expected CSV Format

Activity Mapping

Usage

Common Patterns

Field Mapping

Safe Processing

Preview Mode

Running Imports

Rake Task Pattern

Available Rake Tasks

Environment Considerations

Error Handling

Related Documentation