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)
• Champion 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   │  │ Champion 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.

Column	Type	Description
id	bigint	Primary key
exported_by_id	bigint	User who triggered export
exported_at	datetime	When batch was exported
record_count	integer	Number of changes in batch
notes	text	Optional notes
created_at	datetime	When batch was created

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 (Champion 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')

Exporting Changes

# Create export batch
batch = CrmDataExportBatch.create_with_pending_changes(
  exported_by: current_user,
  notes: 'Weekly sync'
)

# Get CSV for Advancement
batch.to_csv

Integration Points

Affinaquest Import

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

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

See: Affinaquest Import Feature

Champion Portal (Planned)

When champions update their contact info:

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

See: Champion 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

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