Rolando Andrade
Back to projects

Built in 2019

UCAB Sports

Sports Management System MVP

UCAB Sports preview

MVP of an managing system for the sports direction of UCAB, developed in the competition "Reto al ingenio" and winner of the "Best application" prize.

At UCAB (Universidad Catolica Andres Bello), the athletics office ran dozens of teams across several sports. It tracked athletes, scheduled games, recorded scores and kept the standings. All by hand, on spreadsheets and paper forms.

The “Reto al Ingenio” competition asked students to build solutions for real university problems. The sports department needed software for its work. The spreadsheets could not keep up. Information got lost. Updates took days to reach everyone.

I built UCAB Sports in 48 hours during the competition. It won the “Best Application” award.

From tournament to standings

UCAB Sports manages a university sports competition from start to finish.

Administrators create tournaments with start and end dates. Each tournament can hold events for different sports: soccer, basketball, volleyball, rugby and baseball. An event can run as a group stage or as knockout rounds.

Teams sign up for tournaments. The system tracks which athletes belong to each team, with their positions and whether they are active. When an athlete moves to another team, the system keeps their history.

Matches are scheduled in phases. Group stages put teams in tables and track points, wins, losses and goal difference. Knockout rounds run elimination brackets where winners move on.

During games, staff record live events: goals, assists, fouls, cards and substitutions. The system updates scores in real time and recalculates the standings on its own.

Logic lives in the database

The backend runs on Node.js with Express. PostgreSQL stores the data. The frontend uses Materialize CSS, so the interface works on phones at the field.

I put most of the business logic in the database. PostgreSQL stored procedures (functions that live and run inside the database) handle the hard work. They calculate standings, track statistics and move tournaments between phases. The API stays thin and only handles routing.

Tournaments, phases, matches

The database schema shows how much there is to manage in sports.

Tournaments (competitions) sit at the top. Each one happens at a location between two dates. Tournaments hold events. Each event belongs to a sport and a gender division.

Events split into phases, either group classifications or elimination rounds. Group phases track standings with a points system. Knockout phases track the bracket.

A match connects two teams at a place and time. It stores the score for both sides. The phase type decides how the result affects who moves on.

Athletes belong to teams through a membership record with a status and dates. When an athlete changes teams, the system creates a new membership record. It never edits the old one, so the full history stays.

Each sport needs its own statistics tables. Soccer tracks goals, assists, fouls and cards. Basketball tracks points, rebounds, blocks and three pointers. Baseball tracks batting, runs and pitching. Each sport has its own schema for game performance.

One call for a table

PostgreSQL calculates the standings by itself. A stored procedure takes a classification ID. It returns the full table: points, games played, wins, losses, draws, goals for, goals against and goal difference.

This stays fast even with many teams and matches. The database does the joins and sums natively. The API calls the procedure and returns the result.

Other procedures handle common tasks:

  • 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

A thin REST API

The REST API is organized around resources. Tournaments, teams, matches and players each have their own endpoints.

To create a tournament, the client sends one POST request with the basic details. The server returns the new tournament ID. The client then adds characteristics (rules and descriptions) and the teams in follow up requests.

To create a match, the client sends the phase, type, teams, location and date. The server puts the match in the right classification or elimination round, based on the phase type.

During games, a situations endpoint receives each event with its minute, type and players. Database triggers update the score. A trigger is code that runs on its own when a goal situation is saved.

Top scorers in one query

The statistics system adds up data across matches, phases and tournaments.

The top scorer query joins situation records with player data. The database groups by player, adds up the goals and sorts by the total. Assists work the same way, with one more join to find the player who assisted each goal.

Team statistics come from the standings procedure. It works everything out from match results. Points come from wins and draws. Games played come from finished matches. Goals come from the score fields.

Rosters with a date

Athletes do not belong to one team forever. They join, they leave, they transfer. Dated membership records handle this.

When you ask for a team roster, the API takes an optional date. It returns only the athletes who were active on that day. Old rosters matter when you need to check who could play in a past match.

Free agent queries find athletes with a given sport skill who are not on a team right now. Coaches use them to find available players.

Working without a signal

The frontend registers a service worker for offline use. A service worker is a script that lets the browser serve the app from a cache. In places with bad connections, the app keeps working. Data syncs when the connection comes back.

This mattered at the field. Not every sports facility has reliable internet. Staff had to record match events without worrying about a lost connection.

Tests on a real database

Mocha and Chai run the API tests. The suite covers all the main endpoints: authentication, tournaments, teams and match recording.

The tests run against a real PostgreSQL database with seeded data. They catch problems that mocked unit tests would miss, like bugs inside stored procedures or broken constraints.

What 48 hours cut

The 48 hour limit forced hard choices. I kept the core flow: create tournaments, schedule matches, record results and show standings. User management stayed minimal. Reports got cut.

The statistics tables for each sport added complexity. Every sport tracks different numbers, so the schema grew large. Some queries needed conditions on the sport type to reach the right tables.

Stored procedures were slow to debug. Errors inside them gave very little feedback. I wrote test queries to check each procedure’s logic before calling it from the API.

A demo beats a plan

Building under time pressure teaches you to choose. Not every feature matters the same. The judges cared about the working demo, not about how complete the code was.

Putting logic in the database works well for apps full of data. Stored procedures made the Node.js code simpler and faster.

Real users have messy workflows. Sports management has many edge cases: postponed games, disqualified teams, injured players. The MVP handled the common paths. A production system would need more.

The competition format forced a fast loop. Build something, show it, get feedback, adjust. That loop gave better results than weeks of working alone would have.

Interested in working together? Check out my services and pricing to see how we can collaborate on your next project. Let's Build