alumni_lookup

Accent-Insensitive Search Feature

Overview

The alumni lookup system now supports accent-insensitive searching, allowing users to search for names using normal letters while still finding results that contain the same letters with accent marks.

Examples

• Searching for “sharde” will find “Shardé Curry”
• Searching for “jose” will find “José González”
• Searching for “francois” will find “François Dubois”

Technical Implementation

Database Extension

We utilize PostgreSQL’s unaccent extension, which is available on all Heroku Postgres plans and most PostgreSQL installations.

• Extension: unaccent
• Migration: 20250806022423_enable_unaccent_extension.rb
• Function: Removes accent marks from text for comparison purposes

Search Functionality

The accent-insensitive search is implemented in two key scopes in the Alumni model:

1. filter_by_name Scope

• Location: app/models/alumni.rb
• Purpose: Primary search functionality used by the alumni search interface
• Implementation: Uses unaccent() function on both database fields and search terms

-- Example generated query
SELECT "alumni".* FROM "alumni" 
WHERE (
  (unaccent(alumni.first_name) ILIKE unaccent('%sharde%') OR 
   unaccent(alumni.pref_name) ILIKE unaccent('%sharde%') OR 
   unaccent(alumni.last_name) ILIKE unaccent('%sharde%') OR 
   unaccent(alumni.maiden_name) ILIKE unaccent('%sharde%'))
)

2. filter_by_first_last Scope

• Location: app/models/alumni.rb
• Purpose: Component-based search functionality used by AlumniMatcher service
• Implementation: Applies unaccent() to first name and last name searches

Areas Affected

The accent-insensitive search functionality works across all search interfaces:

1. Main Alumni Search (/alumni/search)
   • Name field searches
   • Advanced search options
2. Batch Search (/batch_search)
   • Multiple name searches
   • Uses AlumniMatcher service
3. API Endpoints (/api/alumni)
   • JSON API name searches
4. Direct BUID Lookup
   • Name-based fallback searches

Performance Considerations

• Database Level: Processing happens at the PostgreSQL level for optimal performance
• Indexing: Consider adding functional indexes on unaccent() columns if search volume is high
• Compatibility: Works with existing nickname variations and name cleaning logic

Deployment Notes

Heroku Postgres

• ✅ The unaccent extension is supported on all Heroku Postgres plans
• ✅ No special configuration required
• ✅ Extension is enabled via standard Rails migration

Local Development

• Requires PostgreSQL with contrib modules installed
• Extension enabled via migration: enable_extension "unaccent"

Testing

Console Testing

# Test extension availability
ActiveRecord::Base.connection.execute("SELECT unaccent('José') as test")

# Test search functionality
Alumni.filter_by_name('sharde').first
Alumni.filter_by_name('Shardé').first # Should return same result

Web Interface Testing

1. Navigate to /alumni/search
2. Search for names without accents (e.g., “sharde”)
3. Verify results include names with accents (e.g., “Shardé Curry”)

Backward Compatibility

• ✅ Existing search functionality unchanged for users
• ✅ All current name variations and nickname mappings still work
• ✅ No breaking changes to API or search interfaces
• ✅ Graceful fallback if extension unavailable (standard ILIKE matching)

Future Enhancements

1. Functional Indexes: Add indexes on unaccent(column_name) for improved performance
2. Extended Language Support: Consider additional text normalization for other languages
3. Search Analytics: Track accent-based search patterns for further optimization

---

Last Updated: August 5, 2025 Feature Version: 1.0