alumni_lookup

Champion Portal — Data Architecture

⚠️ PLANNING DOCUMENT — This describes features that are NOT YET IMPLEMENTED.

Related Documents:

DECISIONS.md — Architectural decision log

AVOIDING-DUPLICATION.md — Data overlap handling framework

../README.md — Champion Portal technical overview

Core Principles
Data Ownership Model
Table Naming Convention
Data Flow Between Portals
Specific Data Patterns
Profile Data Ownership
ZIP-Based Location Strategy
Profile Changelog for CRM Export
Future Considerations

1. Core Principles

1.1 Mobile-First Design

The Champion Portal is designed mobile-first. Desktop is secondary.

Design Implications:

Touch-friendly UI (48px minimum tap targets)
Vertical stacking layouts on mobile
Minimal data transfer (fast loading on cellular)
Offline-friendly patterns where possible
PWA/native app migration path considered

Future Vision: The Champion Portal may eventually become a native mobile app, so all architecture decisions should consider:

RESTful API endpoints from the start
JWT tokens instead of session cookies (when needed)
Offline-first data sync patterns
Push notifications infrastructure

1.2 Clear Data Ownership

The same PostgreSQL database serves both portals, but data has clear ownership:

Domain	Owner	Description
Administrative Data	Lookup Portal	Data imported/entered by Staff and Admins
User-Generated Data	Champion Portal	Data entered/created by Champions

2. Data Ownership Model

┌─────────────────────────────────────────────────────────────────┐
│                        SHARED DATABASE                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌──────────────────────┐       ┌──────────────────────────┐   │
│  │   LOOKUP PORTAL      │       │    CHAMPION PORTAL       │   │
│  │   (Administrative)   │       │    (User-Generated)      │   │
│  ├──────────────────────┤       ├──────────────────────────┤   │
│  │                      │       │                          │   │
│  │  alumni              │◄──────┤  cp_champions            │   │
│  │  degrees             │ (via  │  cp_profiles             │   │
│  │  alumni_affinities   │ BUID) │  cp_affinities           │   │
│  │  engagement_activities│      │  cp_contributions        │   │
│  │  users (Staff/Admin) │       │  cp_messages             │   │
│  │                      │       │  cp_posts                │   │
│  └──────────────────────┘       └──────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

3. Table Naming Convention

3.1 Lookup Portal Tables (Administrative)

Owned by the Lookup Portal — data managed by Staff/Admins:

Table	Purpose
`alumni`	Master alumni records (imported from CRM)
`degrees`	Degree records (imported from Registrar)
`affinities`	Master list of affinity groups (reference data)
`alumni_affinities`	Alumni-to-affinity links (imported/staff-managed)
`engagement_activities`	Tracked engagement (imported from CRM/systems)
`engagement_types`	Types of engagement activities (reference data)
`colleges`	College reference data
`majors`	Major reference data
`users`	Lookup Portal users (Staff, Admins)
`champion_signups`	Interest form submissions (processed by Staff)

3.2 Champion Portal Tables (User-Generated)

Use the cp_ prefix to clearly indicate ownership:

Table	Purpose
`cp_champions`	Champion user accounts (self-registered)
`cp_profiles`	Champion profile data (user-editable)
`cp_affinities`	Champion-claimed affinities (self-reported)
`cp_contributions`	“What I can help with” offers
`cp_messages`	Champion-to-champion messages
`cp_message_threads`	Message conversation threads
`cp_posts`	Discussion board posts
`cp_comments`	Comments on posts
`cp_follows`	Follow relationships between champions

3.3 Shared/Bridge Tables

Tables that bridge both domains:

Table	Purpose
`cp_verifications`	Links `cp_champions` to `alumni` (BUID match)
`crm_data_changes`	Unified changelog for CRM export (all portals)
`crm_data_export_batches`	Track CRM export batches
`zip_codes`	ZIP code reference data
`districts`	Metro area groupings
`regions`	Large geographic areas

Note: crm_data_changes replaces the original cp_profile_changes design. See UNIFIED_DATA_SYNC.md for full architecture.

3.4 Future Tables (Event Check-in Integration)

Planned for a separate Event Check-in project:

Table	Purpose
`contacts`	Any person who attends events (alumni, parents, guests)
`events`	Event reference data
`registrants`	Event attendance/check-in records

4. Data Flow Between Portals

4.1 Flow Direction Rules

Direction	Flow Type	Example
Administrative → Champion	Read-Only	Champions VIEW degree data
Champion → Administrative	Via Staff Review	Champion updates → Staff syncs to `alumni`
Champion-Only	Isolated	Messages, posts never flow to Lookup

4.2 Detailed Flow Patterns

Administrative → Champion (Read-Only):

Champions can VIEW their imported degree data
Champions can VIEW affinity reference data
Staff-verified BUID links Champion to Alumni record

Champion → Administrative (Via Staff Review):

Champion self-reports affinities → Staff can review/import to alumni_affinities
Champion profile updates → Staff can review/update alumni record
This keeps alumni as the “source of truth” with optional Champion enrichment

Champion-Only (Isolated):

Messages between champions stay in cp_messages
Discussion posts stay in cp_posts
These never flow to the Lookup Portal

Key Principle: Champion Portal activity does NOT create engagement_activities records. Those remain Staff-managed data from CRM imports.

5. Specific Data Patterns

5.1 Affinities: Three-Layer Architecture

Layer	Table	Who Manages	Purpose
Reference	`affinities`	Staff	Master list of valid affinities
Imported	`alumni_affinities`	Staff	CRM-imported alumni-affinity links
Self-Reported	`cp_affinities`	Champions	User-claimed affinities

Why Separate?

alumni_affinities = verified, imported data (trusted)
cp_affinities = self-reported (may need verification)
Staff can compare and reconcile as needed
Champions see a unified view of “their” affinities (merged read-only)

cp_affinities Table Schema:

create_table :cp_affinities do |t|
  t.bigint :cp_champion_id, null: false
  t.string :affinity_code, null: false
  t.timestamps
end
add_index :cp_affinities, [:cp_champion_id, :affinity_code], unique: true
add_foreign_key :cp_affinities, :cp_champions
add_foreign_key :cp_affinities, :affinities, column: :affinity_code, primary_key: :affinity_code

Export for Advancement Services:

Self-reported affinities can use unified crm_data_changes table for CRM export
Export format TBD pending Affinaquest data structure clarification
Track adds/removes with timestamps for delta exports

Future Enhancement:

“Sync your affinities” feature for verified champions to import alumni_affinities to their profile (see BACKLOG.md)

5.2 Engagement: Clear Boundary

Type	Table	Source
System-Tracked	`engagement_activities`	CRM imports, email clicks, event attendance
Champion-Generated	(not tracked here)	Portal activity is NOT engagement

Decision: Champion Portal activity (logging in, posting, messaging) does NOT create engagement_activities records.

If we ever want to track Champion Portal activity, it would go in a separate table like cp_activity_log with clear separation.

6. Profile Data Ownership

6.1 Split by Purpose

Data	Location	Who Edits	Visible To
`pref_name`, `maiden_name`	`alumni`	Staff OR Champion (if verified)	Both portals
Photo (ID)	`alumni.photo`	Staff only	Lookup Portal only
Photo (profile)	`cp_champions.photo`	Champion only	Champion Portal only
Contact (CRM)	`alumni.email/phone/city/state`	Staff only	Lookup Portal only
Contact (portal)	`cp_champions.*`	Champion only	Champion Portal
Bio	`cp_champions.bio`	Champion only	Champion Portal
Prospect notes	`alumni.prospect_notes`	Staff only	Lookup Portal only
Degrees	`degrees`	Staff only (imports)	Both (read-only for Champion)

6.2 Key Rules

Staff-uploaded photos NEVER appear in Champion Portal
Champion contact info is separate from CRM contact info (no sync)
Champions edit name fields directly on alumni record when verified

7. ZIP-Based Location Strategy

7.1 ZIP-First Approach

ZIP code is the primary location input — minimal friction, maximum value.

ZIP Hierarchy:

ZIP (37027) 
  → City (Brentwood)
  → District (Nashville-Davidson--Murfreesboro--Franklin, TN)
  → Region (Southeast)

7.2 Implementation

Collect ZIP during signup (after email verification)
Auto-populate city/state from ZIP lookup
Allow optional full address editing in profile settings
Auto-assign district and region from ZIP

7.3 Tables

Table	Purpose
`zip_codes`	Reference: ZIP → city, state
`districts`	Metro area groupings
`regions`	Large geographic areas
`cp_champions.zip_code`	Required
`cp_champions.city/state/street_address`	Optional, auto-populated or manual

8. Unified CRM Data Sync

🔗 Full Documentation: See UNIFIED_DATA_SYNC.md for complete architecture.

8.1 Purpose

A unified system tracks ALL changes that need export to Advancement Services (BruinQuest/Salesforce CRM), regardless of source:

Source	Description
Champion Portal	Champions edit their profile (name, email, phone, address)
Lookup Portal (Manual)	Staff manually correct alumni records
Affinaquest Import	Our data is newer than the import file (recency conflicts)

8.2 Terminology

Term	Definition
BUID	Belmont University ID (internal primary key)
BQID	BruinQuest ID (`alumni.contact_id`, format: `C-000000000`)

Note: Exports include BOTH identifiers for data team compatibility.

8.3 Schema: `crm_data_changes`

This replaces the original cp_profile_changes design with a unified table:

create_table :crm_data_changes do |t|
  # Identity - WHO is affected
  t.string :buid
  t.string :contact_id
  t.bigint :alumni_id
  t.bigint :cp_champion_id
  
  # What changed
  t.string :source_table, null: false     # "alumni", "cp_champions"
  t.string :field_name, null: false       # e.g., "phone", "city", "pref_name"
  t.text :old_value
  t.text :new_value
  
  # Context
  t.string :change_source, null: false    # "champion_portal", "lookup_manual", "affinaquest_conflict"
  t.string :changed_by_type               # "Cp::Champion", "User", "system"
  t.bigint :changed_by_id
  t.text :notes
  
  # Export tracking
  t.string :export_status, default: 'pending'  # pending, exported, dismissed
  t.datetime :exported_at
  t.bigint :exported_by_id
  t.bigint :export_batch_id
  
  t.timestamps
end

add_index :crm_data_changes, :buid
add_index :crm_data_changes, :contact_id
add_index :crm_data_changes, :export_status
add_index :crm_data_changes, :change_source
add_index :crm_data_changes, :created_at

8.4 Tracked Fields (Champion Portal)

Field	Table	Export Priority
`pref_name`, `maiden_name`	`cp_champions`	High
`email`, `phone`	`cp_champions`	High
`city`, `state`, `zip_code`, `street_address`	`cp_champions`	High
`employer`, `job_title`, `industry`	`cp_champions`	Medium
`bio`	`cp_champions`	Low
`photo`	`cp_champions`	Low (track changed_at only)

8.5 How Champion Portal Uses This

When a Champion edits their profile:

# app/models/concerns/crm_tracked.rb
module CrmTracked
  extend ActiveSupport::Concern

  included do
    after_update :log_crm_changes
  end

  def log_crm_changes
    tracked_fields.each do |field|
      if saved_change_to_attribute?(field)
        CrmDataChange.create!(
          buid: linked_alumni&.buid,
          contact_id: linked_alumni&.contact_id,
          alumni_id: linked_alumni&.id,
          cp_champion_id: id,
          source_table: 'cp_champions',
          field_name: field.to_s,
          old_value: attribute_before_last_save(field),
          new_value: send(field),
          change_source: 'champion_portal',
          changed_by_type: 'Cp::Champion',
          changed_by_id: id
        )
      end
    end
  end
end

8.6 Export Service (Unified)

class CrmDataExportService
  def pending_changes
    CrmDataChange.pending.recent
  end

  def changes_since(date)
    CrmDataChange
      .where('created_at >= ?', date)
      .order(:created_at)
  end
  
  def export_batch(change_ids, user:)
    CrmDataExportBatch.create_export!(
      user: user,
      change_ids: change_ids
    )
  end
  
  def to_csv(changes)
    # Unified format for BruinQuest/Salesforce import
    # Columns: BQID, BUID, Field, Old Value, New Value, Source, Changed At
  end
end

8.7 Staff UI

Unified “CRM Data Changes” page in Lookup Portal admin
Filter by source: Champion Portal, Manual Edits, Affinaquest Conflicts
Date range selector (last 7 days, 30 days, custom)
Select changes to export → CSV download
Mark exported changes to prevent duplicate exports
Dismiss changes that shouldn’t be sent to CRM

9. Future Considerations

9.1 Native App Migration

When converting to a native app:

API endpoints designed RESTful from the start
JWT tokens instead of session cookies
Offline-first data sync patterns (SQLite local → PostgreSQL server)
Push notifications infrastructure

9.2 Scaling

cp_ tables may need separate indexes optimized for Champion queries
Consider read replicas if Champion Portal traffic grows significantly
Message/notification tables may need different retention policies

Document	Purpose
DECISIONS.md	Architectural decision log
AVOIDING-DUPLICATION.md	Data overlap handling
UNIFIED_DATA_SYNC.md	Complete CRM sync architecture
../README.md	Champion Portal overview
../../development/MODEL_RELATIONSHIPS.md	Existing model associations