alumni_lookup

Unified CRM Sync Architecture

This document describes the shared infrastructure for tracking contact data changes across multiple sources and exporting them back to the CRM (Affinaquest/Salesforce).

Overview

The crm_data_changes table provides a unified way to track changes that need to be synced back to the CRM, regardless of their origin:

Affinaquest Import - Recency conflicts (local data is newer than import)
Alumni Portal - Self-service contact updates by alumni
Admin Edits - Manual corrections by staff (future)

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                    crm_data_changes table                       │
│  (Unified change tracking for eventual CRM export)              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│  │   Affinaquest   │  │  Alumni Portal  │  │   Admin Edits   │ │
│  │   Import        │  │ Self-Updates    │  │   (Future)      │ │
│  │   Conflicts     │  │                 │  │                 │ │
│  └────────┬────────┘  └────────┬────────┘  └────────┬────────┘ │
│           │                    │                    │          │
│           ▼                    ▼                    ▼          │
│  ┌─────────────────────────────────────────────────────────────┐│
│  │ change_source: 'affinaquest_import' | 'champion_portal' |..││
│  │ export_status: 'pending' | 'exported' | 'skipped'          ││
│  │ source_table:  'alumni' | 'champion_signups' | ...         ││
│  └─────────────────────────────────────────────────────────────┘│
│                              │                                  │
│                              ▼                                  │
│  ┌─────────────────────────────────────────────────────────────┐│
│  │              crm_data_export_batches                        ││
│  │  (Groups changes into export batches for Advancement)       ││
│  └─────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────┘

Data Model

crm_data_changes

Tracks individual field-level changes that need CRM export.

Column	Type	Description
`id`	bigint	Primary key
`buid`	string	Alumni BUID (B00421123)
`contact_id`	string	Affinaquest Contact ID (C-000220688)
`field_name`	string	Field that changed (e.g., `email_personal`)
`old_value`	text	Previous value
`new_value`	text	New value
`change_source`	string	Origin: `affinaquest_import`, `champion_portal`, `admin_edit`
`source_table`	string	Table where change originated: `alumni`, `champion_signups`
`source_record_id`	bigint	ID of the source record
`export_status`	string	`pending`, `exported`, `skipped`
`exported_at`	datetime	When exported to CRM
`export_batch_id`	bigint	FK to export batch
`created_at`	datetime	When change was logged
`updated_at`	datetime	Last modified

Unique constraint: (buid, field_name, change_source, export_status) for pending records prevents duplicates.

crm_data_export_batches

Groups changes into batches for export to Advancement Services, and makes a “Mark All Exported” click reversible.

Column	Type	Description
`id`	bigint	Primary key
`exported_at`	datetime	When the batch was marked exported (NOT NULL)
`changes_count`	integer	Number of changes in the batch
`filename`	string	CSV filename this batch corresponds to, if any
`exported_by_id`	bigint	User who marked them exported
`notes`	text	Optional notes
`undone_at`	datetime	Set when the batch was undone (Aug 2026)
`undone_by_id`	bigint	User who undid it (Aug 2026)
`created_at`	datetime	When batch was created
`updated_at`	datetime	Last modified

This table was documented incorrectly until Aug 2026, and so was the model. Both described record_count, export_type, status, file_name, started_at and completed_at — columns the migration never created. The doc and CrmDataExportBatch agreed with each other and both disagreed with the database, which is why the mismatch survived: checking the doc confirmed the wrong answer. Nothing called the model, so it never raised. Verify against db/schema.rb, not against this table.

Note also that crm_data_changes.export_batch_id does not follow Rails’ inferred name — has_many :crm_data_changes needs an explicit foreign_key: :export_batch_id, or it searches for crm_data_export_batch_id.

Usage Patterns

Logging a Change (Affinaquest Import)

CrmDataChange.log_affinaquest_conflict(
  alumni: alumni_record,
  field_name: 'email_personal',
  local_value: 'current@email.com',
  import_value: 'old@email.com'
)

Logging a Change (Alumni Portal)

CrmDataChange.log_champion_update(
  champion: champion_record,
  field_name: 'phone',
  old_value: '615-555-0000',
  new_value: '615-555-1234'
)

Querying Pending Changes

# All pending changes
CrmDataChange.pending

# By source
CrmDataChange.pending.where(change_source: 'champion_portal')

# By field
CrmDataChange.pending.where(field_name: 'email_personal')

Marking Changes Exported (and undoing it)

Staff download the CSV from /settings/bruinquest/crm_changes, hand it to Advancement Services, then mark the rows exported so they leave the pending list. That second step is recorded as a batch so it can be undone.

# Mark a set of pending changes as exported, as one reversible batch.
# Returns nil when there was nothing pending.
batch = CrmDataExportBatch.mark_exported!(
  changes: CrmDataChange.exportable.pending,
  user: current_user,
  notes: 'Weekly sync'
)

batch.changes_count      # how many were marked
batch.revertable_count   # how many an undo would actually move
batch.undo!(user: current_user)   # => rows reverted, or false if already undone

Two behaviors worth knowing before relying on this:

Undo skips rows that have since been verified. A verified row was confirmed present in the CRM by a later Affinaquest import; returning it to pending would re-export a change Advancement Services already holds.
Undo does not clear export_batch_id. The link is the audit record of what the batch touched — that is the history the feature exists to provide. A later real export overwrites it.

Use CrmDataExportBatch.mark_exported!; never flip export_status with a bare update_all. A bulk write with no batch leaves no record of which rows it touched, which is what made the Aug 2026 mis-click recoverable only by hand-written SQL.

Integration Points

Affinaquest Import

When importing contacts, if local data is newer than import data:

Keep the local value
Log a CrmDataChange with change_source: 'affinaquest_import'
Staff can export these to update CRM with our newer data

See: Affinaquest Import Feature

Alumni Portal (Planned)

When champions update their contact info:

Update the champion_signups or alumni record
Log a CrmDataChange with change_source: 'champion_portal'
Changes queue for export to CRM

See: Alumni Portal Planning

Key Files

File	Purpose
`app/models/crm_data_change.rb`	Change tracking model
`app/models/crm_data_export_batch.rb`	Export batch model
`db/migrate/*_create_crm_data_changes.rb`	Table creation
`db/migrate/*_create_crm_data_export_batches.rb`	Batch table creation

Future Enhancements

Automated CRM Export - Direct API integration with Salesforce
Conflict Resolution UI - Review and approve changes before export
Change Audit Trail - Track who approved/rejected changes
Real-time Sync - Webhook-based immediate updates