University sports departments run on spreadsheets and paper forms. At UCAB (Universidad Catolica Andres Bello), the athletics office managed dozens of teams across multiple sports. They tracked athletes, scheduled games, recorded scores, and maintained standings. All by hand.
The “Reto al Ingenio” competition challenged students to build solutions for real university problems. The sports department needed software to manage their operations. Spreadsheets were not scaling. Information got lost. Updates took days to propagate.
I built UCAB Sports in 48 hours during the competition. The application won the “Best Application” award.
What UCAB Sports Does
The platform manages the complete lifecycle of university sports competitions.
Administrators create tournaments with start and end dates. Each tournament supports multiple events for different sports: soccer, basketball, volleyball, rugby, and baseball. Events can run as group stages or knockout rounds.
Teams register for tournaments. The system tracks which athletes belong to each team, including their positions and active status. When athletes transfer between teams, the system maintains their history.
Match scheduling works through phases. Group stages assign teams to tables and track points, wins, losses, and goal differences. Knockout rounds handle elimination brackets with advancing winners.
During games, staff record live events: goals, assists, fouls, cards, and substitutions. The system updates scores in real time and recalculates standings automatically.
The Technical Architecture
The backend runs on Node.js with Express. PostgreSQL handles data persistence. The frontend uses Materialize CSS for a responsive interface that works on phones during field events.
I chose to push most business logic into the database layer. PostgreSQL stored procedures handle complex operations like calculating standings, tracking statistics, and managing phase transitions. This kept the API layer thin and focused on routing.
The Data Model
The database schema reflects the complexity of sports management.
Tournaments (competitions) sit at the top. Each tournament happens at a location and runs between specific dates. Tournaments contain events, and events belong to sports with gender divisions.
Events split into phases: group classifications or elimination rounds. Group phases track standings through a points system. Knockout phases maintain bracket progression.
Matches connect two teams at a location and time. Each match stores scores for both sides. The phase type determines how results affect advancement.
Athletes belong to teams through a membership record that tracks status and dates. When athletes move between teams, the system creates new membership records rather than updating existing ones. This preserves the complete history.
Sport-specific statistics require separate tables. Soccer tracks goals, assists, fouls, and cards. Basketball tracks points, rebounds, blocks, and three-pointers. Baseball tracks batting, runs, and pitching statistics. Each sport has its own schema for game performance.
Stored Procedures for Business Logic
The standings calculation happens entirely in PostgreSQL. A stored procedure takes a classification ID and returns the complete table with points, games played, wins, losses, draws, goals for, goals against, and goal difference.
This runs fast even with many teams and matches. The database handles the joins and aggregations natively. The API just calls the procedure and returns the results.
Other procedures handle common operations:
- Login verification with email and password
- Team retrieval by sport
- Match creation with automatic phase assignment
- Event situation recording (goals, cards, substitutions)
- Free agent queries for player transfers
- Statistics aggregation for top scorers and assistants
The API Design
The REST API follows resource-oriented patterns. Tournaments, teams, matches, and players each have their own endpoints.
Creating a tournament requires a single POST request with basic info. The server returns the new tournament ID. Client code then adds characteristics (rules, descriptions) and participating teams through follow-up requests.
Match creation specifies the phase, type, teams, location, and date. The server assigns the match to the correct classification or elimination round based on the phase type parameter.
During games, a dedicated situations endpoint accepts events with minute, type, and involved players. The server handles score updates through database triggers that fire when goal situations are inserted.
Real-Time Statistics
The statistics system aggregates data across matches, phases, and tournaments.
Top scorer queries join situation records with player data. The database groups by player and sums goals, returning results ordered by total. The same pattern works for assists, with a secondary join to find the assisting player on goal situations.
Team statistics come from the standings procedure. It calculates everything from match results: points from wins and draws, games played from completed matches, goals from score fields.
Managing Athlete Transfers
Athletes do not permanently belong to teams. They join, they leave, they transfer. The data model handles this through dated membership records.
When querying team rosters, the API accepts an optional date parameter. This returns only athletes who were active on that specific date. Historical rosters matter for verifying past match eligibility.
Free agent queries find athletes with specific sport skills who are not currently on a team. This helps coaches discover available players.
The Service Worker
The frontend registers a service worker for offline capability. During games in areas with poor connectivity, the application continues working from cache. Data syncs when connection returns.
This mattered for field events. Not every sports facility has reliable internet. Staff needed to record match events without worrying about dropped connections.
Testing
Mocha and Chai handle API testing. The test suite covers all major endpoints: authentication, tournament management, team operations, and match recording.
Tests run against a real PostgreSQL database with seeded data. This catches issues that unit tests with mocks would miss, like stored procedure bugs or constraint violations.
Challenges I Faced
The 48-hour constraint forced hard decisions. I prioritized the core flow: create tournaments, schedule matches, record results, show standings. User management stayed minimal. Reporting features got cut.
The sport-specific statistics tables created complexity. Each sport tracks different metrics. The schema grew large. Some queries needed sport-type conditionals to hit the right tables.
PostgreSQL stored procedures took time to debug. Errors inside procedures gave minimal feedback. I wrote test queries to validate procedure logic before calling them from the API.
What I Learned
Building under time pressure teaches prioritization. Not every feature matters equally. The judges cared about the working demo, not the completeness of the code.
Database-centric architecture works well for data-heavy applications. Moving business logic to stored procedures simplified the Node.js code and improved performance.
Real users have complex workflows. Sports management involves many edge cases: postponed games, disqualified teams, player injuries. The MVP handled the common paths. A production system would need more.
The competition format forced rapid iteration. Build something, show it, get feedback, adjust. This tight loop produced better results than weeks of isolated development would have.
