Event ID changes in events.json break existing user enrollments

[AdamEXu]

Problem

From CLAUDE.md:214:

Event ID changes: Changing event IDs in events.json breaks existing user enrollments (users.events array references old IDs)

Event enrollments are stored as JSON arrays in the database:

# users table
{
    "email": "user@example.com",
    "events": ["counterspell", "hacksv_2025"]  # Array of event IDs
}

If we rename an event ID in static/events.json:

// Before
{"id": "hacksv_2025", "name": "Hack.SV 2025"}

// After  
{"id": "hacksv_25", "name": "Hack.SV 2025"}

All users enrolled in hacksv_2025 will now have a broken reference!

Current Risk

Medium - Happens whenever we rename/reorganize events

Impact

• Users lose their event enrollments
• Discord roles might not get assigned
• Event registration counts become incorrect
• Historical data becomes inconsistent

Root Cause

Using mutable string IDs as foreign keys instead of immutable record IDs

Recommended Solutions

Option 1: Use Teable Record IDs (Best)

Instead of storing event string IDs, store Teable event record IDs:

# users table
{
    "email": "user@example.com",
    "events": ["rec_abc123", "rec_def456"]  # Teable record IDs
}

Pros:
✅ IDs never change
✅ Can rename events safely
✅ Proper relational integrity

Cons:
❌ Requires migration script
❌ Slightly more complex queries

Option 2: Migration Script for ID Changes

Create a utility to update all user records when event IDs change:

# utils/migrate_event_id.py
def migrate_event_id(old_id: str, new_id: str):
    """Update all user enrollments when event ID changes"""
    users = get_all_users()
    
    for user in users:
        events = user.get('events', [])
        if old_id in events:
            events = [new_id if e == old_id else e for e in events]
            update_user(user['id'], events=events)
            print(f"Updated user {user['email']}")

Pros:
✅ Simple to implement
✅ No schema changes

Cons:
❌ Manual process
❌ Easy to forget
❌ Doesn't prevent the problem

Option 3: Add Event Aliases

Support multiple IDs per event:

{
    "id": "hacksv_25",
    "aliases": ["hacksv_2025", "hacksv"],
    "name": "Hack.SV 2025"
}

Pros:
✅ Backward compatible
✅ Can gradually migrate

Cons:
❌ More complex lookup logic
❌ Technical debt accumulates

Option 4: Validate Event References

Add validation to prevent orphaned references:

def validate_user_events(user_id: str):
    """Check that all user events still exist"""
    user = get_user_by_id(user_id)
    valid_event_ids = {e['id'] for e in get_all_events()}
    
    invalid_events = [e for e in user['events'] if e not in valid_event_ids]
    if invalid_events:
        logger.warning(f"User {user['email']} has invalid events: {invalid_events}")

Immediate Action Items

1. Document the risk in CLAUDE.md ✅ (already done)
2. Add validation to warn about orphaned event references
3. Create migration script for future event ID changes
4. Decide on long-term solution (Option 1 recommended)

Migration Script Example

# utils/fix_event_references.py
"""Fix orphaned event references after event ID changes"""

EVENT_ID_MIGRATIONS = {
    'old_event_id': 'new_event_id',
    'counterspell': 'counterspell_2024',
}

def fix_event_references():
    users = get_all_users()
    fixed_count = 0
    
    for user in users:
        events = user.get('events', [])
        original_events = events.copy()
        
        # Replace old IDs with new IDs
        events = [EVENT_ID_MIGRATIONS.get(e, e) for e in events]
        
        if events != original_events:
            update_user(user['id'], events=events)
            fixed_count += 1
            print(f"Fixed {user['email']}: {original_events} -> {events}")
    
    print(f"Fixed {fixed_count} users")

Labels

bug, data-integrity, enhancement, medium-priority