alumni_lookup

Affinaquest Contact Import

This feature imports contact information from Affinaquest (CRM) CSV exports to keep alumni data synchronized for search and matching purposes.

Overview

The import process:

1. Matches alumni by BUID (primary) or Contact ID (secondary)
2. Uses recency-aware updates - only overwrites local data if the import data is newer
3. Logs conflicts for review and potential CRM export
4. Processes large files via background jobs to avoid timeouts

Architecture

Data Model Changes

Alumni Model (new fields)
├── email_school        # School email address
├── email_personal      # Personal email address  
├── email_business      # Business email address
├── email_other         # Other email address
├── zip                 # ZIP code for district lookup
├── affinaquest_updated_at   # Last modified in Affinaquest
└── affinaquest_synced_at    # Last sync timestamp

Email Field Mapping

CSV Column	Affinaquest Field	Alumni Field	Purpose
email	Email Address	email	Primary email (already existed)
email_school	School Email	email_school	.edu addresses
email_personal	Personal Email	email_personal	Gmail, Yahoo, etc.
email_business	Business Email	email_business	Work addresses
email_other	Alternate Email	email_other	Catch-all

CRM Sync Architecture

Recency conflicts are logged to the unified crm_data_changes table for eventual export back to the CRM. This infrastructure is shared with the Champion Portal for self-service updates.

See: Unified CRM Sync Architecture

How to Use

1. Access the Import Page

Navigate to Settings → Affinaquest Import (/settings/affinaquest)

2. Upload a CSV File

The CSV must include these columns:

• buid (required) - Belmont University ID (e.g., B00421123)
• contact_id - Affinaquest Contact ID (e.g., C-000220688)
• affinaquest_updated_at - Last modified timestamp in Affinaquest

Optional contact fields:

• email, email_personal, email_business, email_school, email_other
• phone
• city, state, zip
• birthdate
• pref_name, maiden_name

3. Preview the Import

Click Preview to see:

• How many records will be created, updated, or skipped
• Any ID conflicts (BUID in file but Contact ID doesn’t match our records)
• Field-level changes that would be made

4. Commit the Import

Click Commit to process the import. Large files (5000+ rows) run as background jobs.

5. Review Conflicts

After import, review:

• ID Conflicts - Records where the Contact ID in the file doesn’t match our database
• Recency Conflicts - Fields where we kept local data because it was newer

Recency Logic

For each field, the import compares timestamps:

• Local timestamp: alumni.updated_at for general fields, or field-specific timestamps if available
• Import timestamp: affinaquest_updated_at from the CSV

If local data is newer, the import keeps local data and logs a CrmDataChange record for potential export back to the CRM.

Data Flow

CSV Upload → Preview → Commit → Background Job (if large)
                                      ↓
                              Process each row:
                              1. Find alumni by BUID
                              2. Check Contact ID match
                              3. Compare timestamps
                              4. Update if import is newer
                              5. Log conflicts if local is newer

Related Database Tables

Table	Purpose
alumni	Main contact records
affinaquest_import_batches	Tracks each import batch
affinaquest_import_conflicts	ID mismatch conflicts
crm_data_changes	Field-level recency conflicts for export

Maintenance Tasks

Check for Duplicate BUIDs

bin/rails alumni:check_duplicate_buids

Cleanup Duplicate BUIDs

If duplicates exist (shouldn’t happen with unique index):

bin/rails alumni:cleanup_duplicate_buids         # Dry run
bin/rails alumni:cleanup_duplicate_buids CONFIRM=1  # Actually merge

Cleanup Duplicate CRM Changes

If duplicate conflict records exist:

bin/rails alumni:cleanup_duplicate_crm_changes         # Dry run
bin/rails alumni:cleanup_duplicate_crm_changes CONFIRM=1  # Actually delete

Key Files

File	Purpose
app/services/csv/affinaquest_contact_importer.rb	Main import logic
app/jobs/affinaquest_import_job.rb	Background job wrapper
app/controllers/settings/affinaquest_controller.rb	UI controller
app/models/affinaquest_import_batch.rb	Batch tracking
app/models/affinaquest_import_conflict.rb	ID conflicts
app/models/crm_data_change.rb	Recency conflict logging
lib/tasks/cleanup_duplicate_buids.rake	Maintenance tasks

Troubleshooting

“Contact has already been taken” Errors

This indicates duplicate BUID records in the database. Run:

bin/rails alumni:check_duplicate_buids

If duplicates exist, merge them:

bin/rails alumni:cleanup_duplicate_buids CONFIRM=1

Import Stuck at “Processing”

Background jobs require Sidekiq + Redis. Check:

1. Redis is running: redis-cli ping
2. Sidekiq is running: bundle exec sidekiq

Conflicts Not Appearing

Ensure the CrmDataChange model matches the database schema. Check:

• change_source (not source)
• export_status (not status)
• source_table is required

Large Import Timeouts

Files with 5000+ rows automatically use background jobs. Ensure Sidekiq is configured.

Database Constraints

• alumni.buid has a unique index - prevents duplicate BUID records
• alumni.contact_id has a unique index - prevents duplicate Contact IDs
• crm_data_changes deduplicates on (buid, field_name, change_source, export_status) for pending records

Search Capabilities

The imported contact data enhances search functionality:

Multi-Email Search

The filter_by_any_email scope searches across all email fields:

• email (primary)
• email_school
• email_personal
• email_business
• email_other

Used in: Alumni search, batch search, event RSVP matching

District Search

ZIP codes enable district-based filtering:

• Autocomplete API: GET /api/districts/autocomplete?query=...
• Shows state for disambiguation: “TN-04 (TN)”
• Links to districts table via zip_codes lookup

ZIP Integration

Alumni.zip → ZipCode.zip → ZipCode.district_id → District
                        → ZipCode.region_id → Region

Methods available:

• alumni.zip_district - Returns District record
• alumni.zip_region - Returns Region record

Future Enhancements

1. CRM Export - Export crm_data_changes back to Affinaquest
2. Conflict Resolution UI - Allow manual resolution of ID conflicts
3. Scheduled Imports - Automated daily/weekly sync