alumni_lookup

Model Relationships & ActiveRecord Associations

This document details the critical ActiveRecord model relationships in the alumni_lookup application. Getting these associations wrong will cause ActiveRecord::ConfigurationError exceptions.

See Also: REPO_OVERVIEW.md for complete entity relationship diagrams and architecture.

🎯 Quick Reference

Model	Association	Target	Key Details
EngagementActivity	belongs_to :alumni	Alumni	⚠️ NOT :alumnus
Alumni	has_many :degrees	Degree	Via buid
Alumni	has_many :engagement_activities	EngagementActivity	Via buid
Alumni	has_many :champion_signups	ChampionSignup	Via buid
ChampionSignup	belongs_to :alumni	Alumni	Via buid
Degree	belongs_to :major	Major	Via major_code
Major	belongs_to :college	College	Via college_code

🔗 Association Chain

Alumni → Degrees → Major → College
  ↓        ↓        ↓       ↓
 buid   major_code  college_code

Alumni → ChampionSignups (prospect/champion status)
  ↓         ↓
 buid    status enum (1-5)

📋 Model Definitions

EngagementActivity

class EngagementActivity < ApplicationRecord
  belongs_to :alumni, primary_key: :buid, foreign_key: :buid, optional: true
  # ⚠️ CRITICAL: Association name is :alumni (NOT :alumnus)
end

Alumni

class Alumni < ApplicationRecord
  # Existing associations
  has_many :degrees, primary_key: :buid, foreign_key: :buid
  has_many :engagement_activities, foreign_key: :buid, primary_key: :buid
  
  # Champion/Prospect system (Added August 2025)
  has_many :champion_signups, foreign_key: :buid, primary_key: :buid
  enum prospect_status: { not_prospect: 0, prospect: 1 }
  
  # Contact ID for CRM integration (Added August 2025)
  validates :contact_id, format: { with: /\AC-\d{9}\z/, message: "must be in format C-000000000" }, allow_blank: true
  validates :contact_id, uniqueness: true, allow_blank: true
  
  # Helper methods for champion status
  def manually_flagged_prospect?
    prospect_status == 'prospect'
  end
  
  def automatic_prospect?
    has_champion_signup? && !has_completed_champion_signup?
  end
end

ChampionSignup (Added August 2025)

class ChampionSignup < ApplicationRecord
  belongs_to :alumni, primary_key: :buid, foreign_key: :buid, optional: true
  
  enum status: {
    started: 1,
    completed_questions: 2,
    selected_role: 3,
    interests: 4,
    zip_code: 5  # Complete champion designation
  }
  
  scope :completed, -> { where(status: 5) }
  scope :in_progress, -> { where(status: 1..4) }
  
  # Data integrity notes:
  # - lifestage_interest field uses comma-separated keywords format
  # - Valid values: 'almost-alumni', 'young-alumni', 'tower-society', 'families', 'other'
  # - See docs/features/CHAMPION_SIGNUP_SYSTEM.md for data details
end

Degree

class Degree < ApplicationRecord
  belongs_to :major, foreign_key: :major_code, primary_key: :major_code
  # ⚠️ NO direct college association - must go through major
end

Major

class Major < ApplicationRecord
  belongs_to :college, foreign_key: :college_code, primary_key: :college_code
  has_many :degrees, foreign_key: :major_code, primary_key: :major_code
end

College

class College < ApplicationRecord
  has_many :majors, foreign_key: :college_code, primary_key: :college_code
  has_many :degrees, through: :majors
end

🔄 Common Join Patterns

✅ Correct Joins

# Get engagement activities with alumni info
EngagementActivity.joins(:alumni)

# Filter engagement activities by college (requires nested joins)
EngagementActivity.joins(alumni: { degrees: { major: :college } })
                  .where(colleges: { college_code: 'CS' })

# Filter alumni by graduation year
Alumni.joins(:degrees)
      .where("CASE WHEN EXTRACT(MONTH FROM degrees.degree_date) >= 6 
              THEN EXTRACT(YEAR FROM degrees.degree_date) + 1 
              ELSE EXTRACT(YEAR FROM degrees.degree_date) END = ?", 2024)

# Get alumni with college information
Alumni.joins(degrees: { major: :college })

# Filter alumni by champion status (Added August 2025)
Alumni.joins(:champion_signups)
      .where(champion_signups: { status: 5 })  # Completed champions

# Get manual prospects only
Alumni.where(prospect_status: 1)
      .where.not(id: Alumni.joins(:champion_signups).select(:id))

# Complex champion/prospect filtering
Alumni.filter_by_alumni_status('champions_and_prospects')  # Custom scope

❌ Common Mistakes

# WRONG: Association name
EngagementActivity.joins(:alumnus)  # Should be :alumni

# WRONG: Skipping major in college join
EngagementActivity.joins(alumni: { degrees: :college })  # Missing major link

# WRONG: Direct college association on degrees
Degree.joins(:college)  # Degrees don't directly associate with colleges

# WRONG: Ambiguous column references in complex joins (Added August 2025)
Alumni.joins(:champion_signups, degrees: { major: :college })
      .where(status: 5)  # Should be champion_signups.status = 5

🚨 Error Messages & Solutions

Can't join 'EngagementActivity' to association named 'alumnus'

• Cause: Using :alumnus instead of :alumni
• Fix: Change all instances of :alumnus to :alumni in joins

Can't join 'Degree' to association named 'college'

• Cause: Trying to join degrees directly to colleges
• Fix: Use nested join: { degrees: { major: :college } }

Column ambiguity errors in complex joins (Added August 2025)

• Cause: Multiple tables with same column names (e.g., status, id)
• Fix: Use table prefixes: alumni.prospect_status = 1 or champion_signups.status = 5

🧪 Testing Associations in Rails Console

# Verify the association chain works
alumni = Alumni.first
alumni.degrees.first&.major&.college

# Test engagement activity association  
activity = EngagementActivity.first
activity.alumni

# Test college filtering
EngagementActivity.joins(alumni: { degrees: { major: :college } })
                  .where(colleges: { college_code: 'CS' })
                  .count

# Test champion signup associations (Added August 2025)
champion_signup = ChampionSignup.first
champion_signup.alumni

# Test prospect filtering
Alumni.where(prospect_status: 1).count
Alumni.joins(:champion_signups).where(champion_signups: { status: 1..4 }).count

💾 Database Keys

• Alumni: Primary key is buid (string)
• Degrees: Foreign key buid links to Alumni, major_code links to Major
• Majors: Foreign key college_code links to College
• EngagementActivities: Foreign key buid links to Alumni
• ChampionSignups: Foreign key buid links to Alumni (Added August 2025)

📈 Performance Notes

• College filtering requires a 3-table join (Alumni → Degrees → Major → College)
• Consider adding database indexes on join keys: buid, major_code, college_code
• Use includes() for N+1 query prevention when loading associated records
• Champion status filtering may require complex subqueries for performance (Added August 2025)

---

Last Updated: November 2025
Critical for: Engagement statistics, alumni filtering, report generation