alumni_lookup

Champion Portal — Decision Log

⚠️ PLANNING DOCUMENT — This tracks decisions for features that are NOT YET IMPLEMENTED.

Related Documents:

• DATA-ARCHITECTURE.md — Data ownership and table design
• AVOIDING-DUPLICATION.md — Development principles for overlap handling
• ../README.md — Champion Portal technical overview

---

Table of Contents

1. Decision Tiers
2. Tier 1: Foundational Decisions (Resolved)
3. Tier 2: Phase-Specific Decisions (Pending)
4. Tier 3: Build & Learn (Deferred)
5. Decision Template

---

1. Decision Tiers

Tier	When to Decide	Description
Tier 1	Before Phase 1	Foundational decisions affecting database schema, auth architecture
Tier 2	At phase kickoff	Phase-specific decisions, can be informed by earlier phases
Tier 3	Build & Learn	Defer until real usage data informs the decision

---

2. Tier 1: Foundational Decisions (Resolved)

2.1 Onboarding Flow

Attribute	Value
Decision	Progressive Account Creation
Date	November 27, 2025
Options Considered	A) Account First (email+password) B) BUID First C) Progressive (name+email first)
Choice	Option C — Progressive Account Creation
Rationale	Lowest friction to start. Collecting first name, last name, and email feels like “getting started” rather than “creating an account”. Password set after email verification.

Flow (Email signup):

1. Enter first name, last name, email
2. Receive verification email
3. Click link → Set password
4. Complete profile (while awaiting Champion verification)

Flow (SSO signup):

1. Click "Continue with Google", "Sign in with Apple", or "Continue with Facebook"
2. Authorize → Account created as email_verified immediately
3. Enter ZIP code
4. Complete profile (while awaiting Champion verification)

---

2.2 BUID Requirement & Verification Tiers

Attribute	Value
Decision	Optional + Two-tier verification
Date	November 27, 2025
Options Considered	A) Required B) Optional C) Optional + Gated
Choice	Option C — Optional with two-tier access
Rationale	Champions rarely remember their BUID. Engagement Team does the matching. Email verification prevents spam. Two tiers balance access with verification.

Verification Tiers:

Status	Access
Unverified	Cannot log in
Email Verified	Log in, edit own profile only
Champion Verified	Full portal access

Additional Requirements:

• Non-degree alumni supported (can be Champion Verified without degree data)
• Email Verified users see “verification typically within 1 business day” messaging
• BUID matching done by Engagement Team (existing workflow)

---

2.3 Account Data Model

Attribute	Value
Decision	Single cp_champions table with verification status
Date	November 27, 2025
Options Considered	A) Immediate account B) Signup → Approval → Account C) Single table + status
Choice	Option C — Single table with verification_status enum
Rationale	Clean data model. No bottleneck waiting for approval. Status controls access.

Schema:

enum verification_status: { 
  unverified: 0,        # Email not confirmed
  email_verified: 1,    # Email confirmed, limited access
  champion_verified: 2  # BUID linked, full access
}

---

2.4 Messaging Architecture

Attribute	Value
Decision	Email relay now, in-app messaging later
Date	November 27, 2025
Options Considered	A) Email relay only B) Email relay → in-app C) Placeholder until Phase 4
Choice	Option B — Email relay initially, build in-app if demand warrants
Rationale	Provides immediate value in Phase 1. Validates demand. Can upgrade to in-app without breaking existing functionality.

Phase 1 Implementation:

• “Contact” button opens a form
• Message sent via email to recipient
• No reply tracking initially

---

2.5 Mobile-First Design

Attribute	Value
Decision	Mobile-first with desktop support
Date	November 27, 2025
Options Considered	A) Desktop-first B) Mobile-first C) Responsive equal
Choice	Option B — Mobile-first
Rationale	Champions will primarily access on phones. Future native app is possible. Desktop should look good but is secondary.

Implementation Guidelines:

• Touch-friendly UI (48px minimum tap targets)
• Vertical stacking layouts on mobile
• Swipe gestures where appropriate
• Minimal data transfer for cellular performance
• Consider offline-friendly patterns
• Design with PWA/native app migration in mind

---

2.6 Data Architecture & Table Naming

Attribute	Value
Decision	cp_ prefix for Champion Portal tables
Date	November 27, 2025
Options Considered	A) No prefix B) champion_ prefix C) cp_ prefix D) Separate schema
Choice	Option C — cp_ prefix for Champion Portal tables
Rationale	Clear ownership, prevents confusion with existing Lookup tables, concise naming.

Table Ownership:

Prefix	Owner	Examples
(none)	Lookup Portal	alumni, degrees, engagement_activities
cp_	Champion Portal	cp_champions, cp_profiles, cp_messages

See DATA-ARCHITECTURE.md for full details.

---

2.7 Data Flow Between Portals

Attribute	Value
Decision	Champion data is user-generated; Alumni data is administrative source of truth
Date	November 27, 2025
Options Considered	A) Champions directly edit Alumni B) Champions edit own tables, Staff syncs C) Fully separate
Choice	Option B — Champions edit their tables, Staff can sync to Alumni
Rationale	Maintains data integrity of Alumni records while allowing Champions to self-report. Staff can verify and reconcile.

Data Flow:

Champions → cp_profiles → (Staff review) → alumni (if verified)
Champions → cp_affinities → (Staff review) → alumni_affinities (if confirmed)

---

2.8 Avoiding Duplication Strategy

Attribute	Value
Decision	Systematic evaluation for all overlapping features, data, and code
Date	November 27, 2025
Options Considered	N/A — Process decision
Choice	Decision framework + unification patterns for every new feature
Rationale	Prevents feature/data duplication, ensures consistency, avoids confusing parallel implementations.

Before implementing ANY new feature, determine:

1. Is this the SAME concept or DIFFERENT concepts?
2. If same, which unification pattern applies?

Pattern	Description
A. Single Location	One table/feature, both portals read/write
B. Primary + Override	Lookup is default, Champion can override
C. Champion + Sync	Champion owns, Staff can sync back
D. Staff + Display	Staff owns, Champion can only view

See AVOIDING-DUPLICATION.md for full framework.

---

2.9 Profile Data Ownership

Attribute	Value
Decision	Split ownership by data type
Date	November 28, 2025
Options Considered	A) All on alumni B) All on cp_champions C) Split by purpose
Choice	Option C — Split by purpose
Rationale	Name fields are shared identity; photos serve different purposes per portal; contact info is Champion-owned for privacy

Data Split:

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)

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

---

2.10 ZIP-First Location Strategy

Attribute	Value
Decision	ZIP code is the primary location input
Date	November 28, 2025
Options Considered	A) Full address required B) City/State C) ZIP only D) ZIP with optional full address
Choice	Option D — ZIP required, full address optional
Rationale	ZIP provides maximum value (city, state, district, region) with minimum friction. Full address available for those who want to “keep their data updated.”

ZIP Hierarchy:

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

Implementation:

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

---

2.11 Profile Changelog for CRM Export

Attribute	Value
Decision	Track all profile changes with timestamps for BruinQuest export
Date	November 28, 2025
Options Considered	A) No tracking B) updated_at only C) Full changelog table D) JSONB changelog column
Choice	Option C — Full changelog table
Rationale	Need to export “changes in past X days” for BruinQuest (Salesforce-based CRM). Changelog table enables flexible querying, reporting, and auditing.

See DATA-ARCHITECTURE.md for schema details.

---

2.12 Social Sign-In (SSO)

Attribute	Value
Decision	Support Google, Apple, and Facebook SSO alongside email signup
Date	November 28, 2025
Options Considered	A) Email only B) Google only C) Google + Apple D) Google + Apple + Facebook E) Add LinkedIn for job data
Choice	Option D — Google + Apple + Facebook
Rationale	Reduces friction significantly. Google covers most users. Apple required for iOS App Store submission if native app later. Facebook still popular among alumni demographic. LinkedIn considered but deferred—pulling job info not worth the complexity for just employer/title fields.

Sign-In Buttons (order matters for mobile UX):

┌──────────────────────────────────────────┐
│        Continue with Google              │  ← Primary (most users)
├──────────────────────────────────────────┤
│        Sign in with Apple                │  ← Secondary
├──────────────────────────────────────────┤
│        Continue with Facebook            │  ← Tertiary
├──────────────────────────────────────────┤
│              ── or ──                    │
├──────────────────────────────────────────┤
│    Email                      [        ] │  ← Fallback for those who
│    Password                   [        ] │    prefer email/password
└──────────────────────────────────────────┘

SSO Behavior:

Scenario	Behavior
New user via SSO	Create account, mark email_verified, prompt for ZIP code
Existing user via SSO (same email)	Link SSO provider to existing account, log in
SSO email differs from account email	Reject, show “account exists with different email”
Account has password + SSO	User can use either method to log in

Database Additions:

# Add to cp_champions table
t.string :google_uid              # Google OAuth UID
t.string :apple_uid               # Apple Sign In UID
t.string :facebook_uid            # Facebook OAuth UID
t.datetime :google_linked_at
t.datetime :apple_linked_at
t.datetime :facebook_linked_at

add_index :cp_champions, :google_uid, unique: true
add_index :cp_champions, :apple_uid, unique: true
add_index :cp_champions, :facebook_uid, unique: true

Effort Estimates:

Provider	Complexity	Time
Google OAuth2	Low	2–4 hours
Apple Sign In (Web)	Medium	4–8 hours
Facebook	Low	2–4 hours

Phase Recommendation: Implement with Phase 1 Authentication to avoid re-testing login flows later.

---

3. Tier 2: Phase-Specific Decisions (Pending)

Phase 2: Contributions

Decision	Options	Status
Can Champions edit events after CLC approval?	Yes / No / Limited time window	❓ Pending
Should stories auto-publish or always require review?	Auto-publish / Always review / Hybrid	❓ Pending
How detailed should budget requests be?	Simple (Y/N) / Detailed breakdown	❓ Pending
Self-service mentor matching vs facilitated?	Self-service / Facilitated / Hybrid	❓ Pending

Phase 3: Discussion Boards

Decision	Options	Status
Allow anonymous posting?	Yes / No	❓ Pending (recommend No)
Post edit time limit?	None / 15 min / 1 hour / etc.	❓ Pending
Housing/roommate board?	Yes / No / Build if demand	❓ Pending (recommend defer)

Phase 4: Messaging

Decision	Options	Status
Message retention policy	Forever / 2 years / 1 year / User-controlled	❓ Pending
Group messaging?	1:1 only / Groups allowed	❓ Pending
Read receipts?	Always / Optional / Never	❓ Pending

Phase 5: Advanced Features

Decision	Options	Status
Map view opt-in required?	Yes / No	❓ Pending (recommend Yes)
Save search history?	Yes / No / User opt-in	❓ Pending

---

4. Tier 3: Build & Learn (Deferred)

Topic	Current Stance	Revisit When
Housing/roommate boards	Not initially	If organic demand in discussion boards
Detailed notification preferences	Basic (on/off)	After observing notification engagement
Advanced CLC regional tools	Basic features first	After CLC feedback on Phase 2

---

5. Decision Template

When making new decisions, use this format:

### [Decision Name]

| Attribute | Value |
|-----------|-------|
| **Decision** | [Choice made] |
| **Date** | [Date] |
| **Options Considered** | A) ... B) ... C) ... |
| **Choice** | Option [X] — [Name] |
| **Rationale** | [Why this choice] |

**Implementation Notes:**
- [Any technical or UX details]

---

Related Documentation

Document	Purpose
DATA-ARCHITECTURE.md	Data ownership and table naming
AVOIDING-DUPLICATION.md	When to unify, reuse, extract, or separate
../README.md	Champion Portal overview
../FEATURES/	Feature specifications