FutsalLeague Manager uses PostgreSQL as its primary data store. The repository includes an initialization script that creates all 15 tables and their associated ENUM types in the correct dependency order. This guide takes you from a blank PostgreSQL instance to a fully initialized database ready for the backend API.Documentation Index
Fetch the complete documentation index at: https://mintlify.com/Danielsl4/TFG_DAM_2526/llms.txt
Use this file to discover all available pages before exploring further.
Prerequisites
- PostgreSQL 14 or newer — earlier versions are untested.
- The
unaccentextension, which ships with the standardpostgresql-contribpackage on most distributions. It is required for accent-insensitive username and search queries used by the auth and player search endpoints.
The
unaccent extension must be enabled in the target database before running init_db.js. The backend’s auth queries use unaccent() directly in SQL, so the API will fail to handle login and registration if the extension is missing.Create the database and enable extensions
Connect to PostgreSQL as a superuser and run:Connection pooling
The backend connects viapg.Pool configured in db.js with the following settings:
| Setting | Value | Description |
|---|---|---|
connectionString | DATABASE_URL env var | Full PostgreSQL connection string |
max | 50 | Maximum concurrent connections in the pool |
idleTimeoutMillis | 30000 | Connections idle for 30 s are closed |
connectionTimeoutMillis | 5000 | Acquiring a connection times out after 5 s |
ssl.rejectUnauthorized | false | Accepts self-signed certificates (suitable for managed cloud databases) |
DATABASE_URL in your environment before starting the API:
Run the initialization script
From thebackend/ directory, run:
CREATE TABLE IF NOT EXISTS, so it is safe to re-run against an existing database — it will not drop or alter existing data. On success it prints:
Schema overview
ENUM types
| Type | Values |
|---|---|
match_phase | fase_de_grupos, octavos, cuartos, semis, final |
match_status | pendiente, en_curso, finalizado |
event_type | gol, tarjeta_amarilla, tarjeta_roja |
role | admin, referee, user |
vote_type | local, empate, visitante |
Tables
seasons
Season records. The
is_active boolean identifies the current season. All other tables reference season_id for data partitioning.groups
Competition groups within a season. Cascades delete from
seasons.teams
Team master records including kit color, logo URL, delegate, coach, and phone. Soft-deleted via
is_active.players
Player master records with birth date and photo URL. Soft-deleted via
is_active.team_players
Player–team–season association with jersey numbers. Enforces
UNIQUE(team_id, player_id, season_id) so a player can only appear once per team per season.fields
Playing fields and venues. Soft-deleted via
is_active.matches
Match records including home/away teams, group, season, field, date, goal tallies, penalty goals, phase, status, observations, and referee lock info. Enforces
home_team_id <> away_team_id.match_events
Individual in-match events (goals, yellow cards, red cards) linked to a player and match.
users
User accounts with hashed passwords, roles, email verification status, verification token, and password-reset token with expiry.
user_points
Prediction points per user per season. Composite primary key
(user_id, season_id).team_followers
Many-to-many follow relationship between users and teams. Composite primary key
(user_id, team_id).team_stats
Aggregated team statistics (played, won, drawn, lost, goals for/against, points, cards) per group per season. Enforces
UNIQUE(team_id, group_id, season_id).player_stats
Aggregated player statistics (goals, cards, matches played) per season. Enforces
UNIQUE(player_id, season_id).match_votes
User match predictions (local / draw / away). Enforces
UNIQUE(match_id, user_id) so each user casts at most one vote per match.audit_logs
Admin action audit trail. Stores the acting user, action name, entity type, entity ID, and a JSONB details blob with a timestamp.
Key constraints
| Table | Constraint | Purpose |
|---|---|---|
team_players | UNIQUE(team_id, player_id, season_id) | Prevents duplicate player registrations per team per season |
match_votes | UNIQUE(match_id, user_id) | Ensures one prediction per user per match |
team_stats | UNIQUE(team_id, group_id, season_id) | One stats row per team per group per season |
player_stats | UNIQUE(player_id, season_id) | One stats row per player per season |
matches | CHECK (home_team_id <> away_team_id) | Prevents a team from being scheduled against itself |
users | UNIQUE on username and email | Enforces unique accounts |
Backup considerations
Because stats tables (team_stats, player_stats) are derived from match events, a full pg_dump of all tables is the safest backup strategy: