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

Table of Contents

  1. Overview
  2. Importers Summary
  3. Education Importer
  4. Employment Importer
  5. Giving Importer
  6. Affinaquest Contact Importer
  7. Event RSVP Converter
  8. Common Patterns
  9. Running Imports

Removed in Phase 30.3: the Alumni CSV (Csv::AlumniImporter), Degrees CSV and Banner JH-0026c (Csv::BannerImporter) importers, and the /people import. CRM data arrives through /settings/imports/:type.

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:

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
GivingImporter Import the FY gift report as giving-level engagement activity (per-fiscal-year replace) Affinaquest CRM salesforce_contact_id, gift date, recognition credit amount
AffinaquestContactImporter Full alumni contact sync Affinaquest CRM All contact fields, addresses, employment
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

Settings → Imports → Educations        /settings/imports/educations
Settings → Imports → Areas of Study    /settings/imports/areas-of-study

Both run through the shared import flow (Phase 30; see “Upload UI” under Employment Importer for how it works), backed by EducationImportBatch with a kind of educations or areas_of_study.

In-progress Enrollment (Single Source of Truth)

An educations row is considered “in progress” when:

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:

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 → Imports → Employment        /settings/imports/employment

The shared import flow (Phase 30). Every CRM import runs through Settings::ImportsController, with per-type differences (batch table, jobs, manifest store, partials, confirm text) registered in Imports::Type:

  1. Upload (POST /settings/imports/:type) creates the batch as scanning and enqueues the scan job. It is not created as pending, because the batch page only polls while a batch is in progress.
  2. Scan: the batch page shows live progress (import-progress Stimulus controller, polling …/batches/:id/status) and reloads when the status changes.
  3. Review: the type’s _review partial, plus Commit (with a data-turbo-confirm) and Cancel.
  4. Import: the apply job runs in Csv::ChunkedCommit chunks. Cancel stops it at the next chunk boundary; committed chunks stay.
  5. Results: counts, and the issues CSV (…/batches/:id/issues.csv) when the scan found gap rows.

A batch with no progress write for 10 minutes (ImportBatch::STALE_AFTER) auto-fails on poll, which catches Heroku R14/R15 OOM kills that prevent the job’s own rescue from running.

The manifest (manifest_data) is stored with StreamingManifest: a header line (summary, gaps, row count) followed by one row per line. The apply job and review page read it through the store’s header and rows methods, one row at a time. Never fetch it on those paths: a 66K-row manifest parsed whole is ~180MB, which is what OOM-killed the worker.

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:

  1. 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.
  2. 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.
  3. 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.

Giving Importer

Added: Phase 29 (September 2026). Spec: phase-29/README.md

File: app/services/csv/giving_importer.rb UI: Settings → Engagement Activities → Giving Import (/settings/engagement_activities/import_giving, admin only)

Purpose

Replace the manual pipeline that turned the comprehensive fiscal-year gift report into engagement activity: a case-sensitive VLOOKUP to recover BUIDs, a pivot to FY totals, a hand-built level column, a manual delete of the fiscal year, and an upload. One file, one Import button.

Expected CSV Format

The report is uploaded exactly as it comes out of Affinaquest. Columns are resolved by header name after normalization, so reordering and spelling variants are fine.

CSV Header Maps To Notes
Contact: Contact ID / Contact ID / Contact: Full Contact Id alumni.salesforce_contact_id The match key. 15-char, case-sensitive; an 18-char value is truncated
Contact: First Name / First Name issues CSV only Never written to alumni
Contact: Last Name / Last Name issues CSV only  
Contact: Degree Year / Degree Year issues CSV only  
Gift Date / Transaction Date decides the fiscal year and the engagement_date Parsed by Csv::DateParser
Recognition Credit Amount / Amount summed per person per fiscal year $1,234.56, (50.00) and -50.00 all parse
VSE Category ignored Deliberately not a filter — see below

Gifts are grouped by (buid, fiscal_year), summed, and mapped to a level. The written row is dated the person’s latest gift in that fiscal year, and its description comes from EngagementType#name, so renaming the seed renames every row this importer writes.

Level From To
1 $0.01 $10.00
2 $10.01 $100.00
3 $100.01 $1,000.00
4 $1,000.01 $10,000.00
5 $10,000.01 —

A fiscal-year total of $0.00 or less produces no record at all. Negative line items count toward the total; they are corrections, not noise. No dollar amount is ever persisted — not on the activity, not in the manifest between preview and commit, not in the issues CSV, not in the batch log. The preview displays each person’s total on screen because that is the sanity check that makes the preview worth reading, and nothing stores it.

The coverage rule — why the form asks what the file could tell you

A report pulled November 1, and a report pulled June 1 in a year where nobody gave in June, produce identical earliest-gift dates. Any “does this file reach back to June?” flag derived from min(gift_date) is therefore a guess, and it guesses wrong in the expensive direction: a genuinely quiet June looks like a truncated report, the warning fires on a good file, and within a month it is clicked through reflexively.

So the coverage window is an input. The upload form asks for it (a Stimulus controller pre-fills it from the file’s first and last gift purely to save typing; the server never infers it). Coverage is then a fact:

An FY is AUTHORITATIVE iff
  window_start <= FiscalYear.first_day(fy)         # reaches back to June 1
  AND window_end >= required_end_for(fy, today)

required_end_for is where the two kinds of fiscal year part company:

Fiscal year Must be covered to Why
Complete its last day (May 31) A report stopping May 20 does not describe FY26, and no amount of recency makes it so
In progress within 45 days of today It cannot be covered to May 31, and demanding today is unsatisfiable — see below

The in-progress allowance is not slop, it is the difference between two dates that look like one. The rule originally read window_end >= today for the in-progress year, and it refused every real report. Two reasons compound: no report is pulled and uploaded in the same instant, and — the one that is easy to miss — gift processing lags giving, so the newest gift in a report pulled this morning is routinely a week old. Neither makes the report partial. Both made it rejected. Above 45 days the year is refused, which still catches re-importing a report pulled three months ago (it would lower levels for everyone who has given since); between 14 and 45 days it imports behind a confirm.

A corollary worth stating because it is a real trap: the last gift in the file is not the end of the coverage window. The upload form’s pre-fill deliberately takes window_start from the file’s earliest gift (conservative — it can only err toward “provisional”, which skips a year rather than replacing it wrongly) and window_end from today, on the stated assumption that the report was pulled today. Filling window_end from the file’s latest gift understates coverage on essentially every import.

Per fiscal year present in the file, independently:

Delete and insert run in one transaction, so a failure cannot leave a fiscal year emptied and unrefilled.

“Merge instead of replace” is deliberately not built. Adding rather than replacing for a provisional year — raising a level but never lowering it — cannot be made correct without stored amounts. Someone at $600 who gives another $600 has a true level 4; a partial report sees $600, computes level 3, finds an existing level 3, and changes nothing. It understates silently, and nothing on the record marks which levels are trustworthy.

Guards

Guard Behavior
Mass loss — an authoritative FY losing more than 20% of its existing distinct BUIDs Blocks commit behind a separate confirm checkbox spelling out the real numbers (FY26: 1,240 existing → 180 incoming, 1,060 net loss). Re-evaluated at commit, not trusted from the preview form. This is the guard that catches an accidentally-filtered report, which replace would otherwise turn into a wiped fiscal year that looks like it worked.
Stale window — window_end more than 14 days ago, and the file contains the in-progress fiscal year Warns plus a confirm checkbox naming the age. Never a block: past 45 days the year is already provisional, so nothing reaches here to block. Scoped to the in-progress year because a completed year’s report is supposed to end months ago — warning about it would fire on every prior-year import, and noise is how a guard gets clicked through.
Window cross-check — claimed window_start more than 30 days before the file’s earliest gift, on a file of more than 100 rows Warns. Never blocks — a small report legitimately has gaps.

Why there is no VSE-category filter

VSE Student and Alumni Non-Degree may matter later, and filtering on a category is a guess about intent. Failing to resolve a BUID is a fact. Rows whose contact id resolves to no BUID are skipped and reported.

The issues CSV

salesforce_contact_id, last_name, first_name, degree_year, fiscal_year, reason — no amounts. Downloadable from the preview. Reasons: Unmatched contact ID, Multiple BUIDs (the column has no unique index, so a contact id resolving to two alumni rows is caught here rather than by failing the contact import mid-batch), Provisional fiscal year, Zero or negative total, Invalid gift date.

What is deliberately NOT checked

The gap between the stated window_end and the newest gift in the file. That gap is gift processing lag, it is normal, and it says nothing about coverage. Flagging it is what made the first version of this importer refuse a report that had been pulled that morning.

Verifying an import

Each commit writes one engagement_batch_logs row with source: "giving_import", a deleted_count, and a metadata jsonb carrying the window, the authoritative and provisional fiscal years, and per-level counts — never sums. Nothing displays that table yet; read it in the console:

EngagementBatchLog.where(source: "giving_import").order(created_at: :desc).first.metadata

Deploy order (two deploys, not optional)

  1. alumni.salesforce_contact_id + the contact-importer mapping. Deploy, then run a full contact import to populate the column.
  2. The giving importer. Meaningless before step 1’s backfill — every row would report Unmatched contact ID.

Known constraint

The preview → commit handoff stashes a Tempfile path in the session. With more than one web dyno, a commit landing on a different dyno than the preview would find no file and import zero rows. Production runs one web dyno (verified September 23, 2026), and both existing engagement importers already work this way. Scaling web past one dyno is a prerequisite for moving the manifest into the database, the way the education and employment importers do with manifest_data — and it matters more here than for them, because this is the first importer that pairs a silent zero-row run with a delete.

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.

Upload UI

Settings → Imports → Contacts          /settings/imports/contacts

Runs through the shared import flow (see “Upload UI” under Employment Importer): the scan (AffinaquestScanJob) reports progress every 500 rows, and the apply job (AffinaquestApplyJob) streams the manifest’s changes from ManifestStore in 500-record chunks, checking for a cancel between chunks. The review section shows ID mismatches, protected (recency) fields, the detected column mapping and sample creates/updates. Once finished, the results section lists the batch’s ID conflicts and the field changes it wrote. ID Conflicts and CRM Data Changes stay at /settings/bruinquest/conflicts and /settings/bruinquest/crm_changes (Phase 30 Decision C).

Key Features

Expected CSV Columns

Primary fields include:

Usage

bin/rails alumni:import_contacts CSV=/path/to/affinaquest_export.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:

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

Apply passes commit once per chunk, not once per row. Every commit waits for the database to confirm the write, so on a 50K-row import that waiting is most of the run time. Each row still gets its own savepoint, so a row that fails at the DB level rolls back alone and is counted as skipped:

Csv::ChunkedCommit.each_row(parsed_rows, chunk_size: PROGRESS_STRIDE) do |row, index|
  ActiveRecord::Base.transaction(requires_new: true) do  # savepoint inside the chunk
    apply(row)
  end
  report_progress(progress, processed: index + 1, ...)   # visible at the chunk's commit
rescue ActiveRecord::RecordInvalid, ActiveRecord::InvalidForeignKey => e
  skipped += 1
end

Used by the education, area-of-study, and employment importers. AffinaquestContactImporter#apply_manifest_to_batch uses the same shape inline with 500-record chunks, and also collects each chunk’s CrmDataChange audit rows and writes them with one insert_all. Without the savepoint, a single DB error would make Postgres abort the entire chunk’s transaction.

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

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]}" }

Document maintained by the Alumni Engagement and Engineering teams.