Skip to content

UNIVERSITY_STATISTICS_IMPLEMENTATION.md

Latest commit

 

History

History
141 lines (105 loc) · 4.63 KB

UNIVERSITY_STATISTICS_IMPLEMENTATION.md

File metadata and controls

141 lines (105 loc) · 4.63 KB

University Statistics Implementation

Problem Solved

The confessions page under the university tab was only showing confession counts but was missing:

  • Student Count: Number of students from each university
  • Total Votes: Total votes from students who attended that university

Root Cause: The original implementation was trying to match users by university ID, but users actually store university names as strings in the university field.

Solution Implemented

1. New API Endpoints

Created three API endpoints for university statistics:

/api/universities/stats-optimized (Primary)

  • Uses MongoDB aggregation pipelines for maximum performance
  • Calculates student counts and vote totals in parallel
  • Includes aggressive caching (30 minutes)
  • Best for production use

/api/universities/stats-simple (Fallback)

  • Uses simple Prisma queries
  • More reliable but slower
  • Includes moderate caching (15 minutes)
  • Fallback when optimized endpoint fails

/api/universities/stats (Legacy)

  • Basic implementation for reference
  • Not used in production

2. Database Optimizations

Added performance indexes:

// User collection
{ university: 1 } // For fast student count queries

// ConfessionVote collection  
{ userId: 1, confessionId: 1 } // For vote aggregation

// University collection
{ confessionCount: -1, isVerified: -1, name: 1 } // For sorting
{ region: 1, country: 1 } // For filtering

3. Client-Side Implementation

Updated useUniversities hook with:

  • Fallback mechanism: Tries optimized endpoint first, falls back to simple endpoint
  • Error handling: Graceful degradation if endpoints fail
  • Caching: Development cache-busting with production caching
  • Performance logging: Shows which endpoint was used

4. Data Structure

Each university now returns:

{
  id: string;
  name: string;
  domain?: string;
  isVerified: boolean;
  confessionCount: number;  // ✅ Already working
  studentCount: number;     // ✅ NEW: Students from this university
  totalVotes: number;       // ✅ NEW: Votes from students of this university
  createdAt: string;
  updatedAt: string;
}

Performance Considerations

Database Level

  • Indexes: Added strategic indexes for fast queries
  • Aggregation: Uses MongoDB aggregation pipelines for bulk operations
  • Parallel Processing: Calculates stats for multiple universities simultaneously

API Level

  • Caching: 30-minute cache for optimized endpoint, 15-minute for simple
  • Pagination: Efficient pagination with limit/offset
  • Error Handling: Graceful fallbacks prevent complete failures

Client Level

  • Lazy Loading: Only loads data when universities tab is active
  • Debounced Search: Prevents excessive API calls during typing
  • Infinite Scroll: Efficient pagination for large datasets

Usage

The university tab now displays:

  1. Confession Count: Number of confessions from this university
  2. Student Count: Number of students registered from this university
  3. Total Votes: Total votes cast by students from this university

Testing

Created test scripts:

  • scripts/test-university-stats.ts: Validates data accuracy
  • scripts/create-university-stats-indexes.ts: Sets up performance indexes

Monitoring

The implementation includes:

  • Console logging for endpoint usage
  • Error tracking for failed requests
  • Performance metrics for query execution

Future Enhancements

Potential improvements:

  1. Real-time Updates: WebSocket updates for live statistics
  2. Advanced Analytics: Trend analysis and growth metrics
  3. Caching Layer: Redis for even faster response times
  4. Background Jobs: Scheduled recalculation of statistics

Files Modified

New Files

  • src/app/api/universities/stats/route.ts
  • src/app/api/universities/stats-optimized/route.ts
  • src/app/api/universities/stats-simple/route.ts
  • scripts/create-university-stats-indexes.ts
  • scripts/test-university-stats.ts
  • UNIVERSITY_STATISTICS_IMPLEMENTATION.md

Modified Files

  • src/hooks/useUniversities.ts: Updated to use new endpoints with fallback
  • src/app/meetups/confessions/page.tsx: Now displays student count and total votes

Database Schema

The implementation works with the existing schema:

  • User.university: String field storing university name (not ID)
  • ConfessionVote: Links votes to users
  • University.confessionCount: Pre-calculated confession count

Key Fix: Changed queries to match users by university name instead of university ID, since users store the university name as a string.

No schema changes required - the solution works with existing data structure.