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.
FutsalManager uses PostgreSQL as its primary database and Redis for caching. This page covers how to initialize the schema, describes every table, and explains what is cached and for how long.
Initializing the database
Run the initialization script once against your PostgreSQL instance:
The script creates all ENUM types and 14 tables using CREATE TABLE IF NOT EXISTS, so it is safe to re-run on an existing database. It reads the DATABASE_URL environment variable from .env or the process environment.
init_db.js does not run schema migrations. If the database schema changes after initial setup, apply changes separately before restarting the API.
ENUM types
The schema defines five custom PostgreSQL ENUM types used across multiple tables:
| Type | Values | Used in |
|---|
match_phase | fase_de_grupos, octavos, cuartos, semis, final | matches.phase |
match_status | pendiente, en_curso, finalizado | matches.status |
event_type | gol, tarjeta_amarilla, tarjeta_roja | match_events.type |
role | admin, referee, user | users.role |
vote_type | local, empate, visitante | match_votes.vote |
Tables
seasons
Represents a competition season. Only one season should have is_active = true at a time.
| Column | Type | Description |
|---|
id | serial PK | — |
name | varchar | Season name, e.g. "2025/2026" |
start_date | date | Start date |
end_date | date | End date |
is_active | boolean | Whether this is the current active season |
groups
Competition groups within a season (e.g. Group A, Group B).
| Column | Type | Description |
|---|
id | serial PK | — |
name | varchar | Group name |
season_id | FK → seasons | Owning season (cascades on delete) |
teams
Clubs participating in the league. Not tied to a specific season.
| Column | Type | Description |
|---|
id | serial PK | — |
name | varchar | Team name |
kit_color | varchar | Primary kit color |
logo_url | text | URL to team logo (Cloudinary) |
delegate | varchar | Team delegate contact name |
coach | varchar | Coach name |
phone | varchar | Contact phone number |
players
Individual players, independent of any team or season.
| Column | Type | Description |
|---|
id | serial PK | — |
name | varchar | Full name |
birth_date | date | Date of birth |
photo_url | text | URL to player photo (Cloudinary) |
team_players
Links players to teams for a specific season, including their jersey number.
| Column | Type | Description |
|---|
id | serial PK | — |
team_id | FK → teams | — |
player_id | FK → players | — |
season_id | FK → seasons | — |
jersey_number | varchar(10) | Jersey number for this season |
(team_id, player_id, season_id) is unique — a player can only appear once per team per season.
fields
Futsal pitches where matches are played.
| Column | Type | Description |
|---|
id | serial PK | — |
name | varchar | Field name |
location | text | Address or description |
matches
Scheduled and completed matches between two teams.
| Column | Type | Description |
|---|
id | serial PK | — |
home_team_id | FK → teams | Home team (nullable for placeholder entries) |
away_team_id | FK → teams | Away team (nullable for placeholder entries) |
home_team_placeholder | varchar | Text label when team is not yet determined |
away_team_placeholder | varchar | Text label when team is not yet determined |
group_id | FK → groups | — |
season_id | FK → seasons | — |
field_id | FK → fields | Playing venue (set null on delete) |
date | timestamp | Scheduled kick-off |
home_goals | integer | Full-time goals, home team |
away_goals | integer | Full-time goals, away team |
home_penalty_goals | integer | Penalty shootout goals, home |
away_penalty_goals | integer | Penalty shootout goals, away |
phase | match_phase | Competition phase |
status | match_status | Match lifecycle state |
observations | text | Referee notes |
locked_by | FK → users | User who locked the match for editing |
locked_at | timestamp | When the lock was acquired |
match_events
Individual events recorded during a match (goals, cards).
| Column | Type | Description |
|---|
id | serial PK | — |
match_id | FK → matches | — |
player_id | FK → players | Player who triggered the event |
type | event_type | Event type |
users
Registered users of the platform.
| Column | Type | Description |
|---|
id | serial PK | — |
username | varchar | Unique username |
email | varchar | Unique email address |
password | varchar | Bcrypt-hashed password |
role | role | User role (admin, referee, or user) |
points | integer | Prediction points accumulated |
created_at | timestamp | Account creation time |
team_followers
Many-to-many relationship between users and the teams they follow.
| Column | Type | Description |
|---|
user_id | FK → users | — |
team_id | FK → teams | — |
created_at | timestamp | When the user started following |
(user_id, team_id).
team_stats
Aggregated standings for a team within a specific group and season. Updated as matches are recorded.
| Column | Type | Description |
|---|
id | serial PK | — |
team_id | FK → teams | — |
group_id | FK → groups | — |
season_id | FK → seasons | — |
played | integer | Matches played |
won | integer | Wins |
drawn | integer | Draws |
lost | integer | Losses |
goals_for | integer | Goals scored |
goals_against | integer | Goals conceded |
points | integer | League points |
yellow_cards | integer | Total yellow cards |
red_cards | integer | Total red cards |
(team_id, group_id, season_id) is unique.
player_stats
Aggregated statistics for a player within a season.
| Column | Type | Description |
|---|
id | serial PK | — |
player_id | FK → players | — |
season_id | FK → seasons | — |
goals | integer | Goals scored |
yellow_cards | integer | — |
red_cards | integer | — |
matches_played | integer | — |
(player_id, season_id) is unique.
match_votes
Records user predictions for match outcomes. Each user can vote once per match.
| Column | Type | Description |
|---|
id | serial PK | — |
match_id | FK → matches | — |
user_id | FK → users | — |
vote | vote_type | Predicted outcome |
created_at | timestamp | — |
(match_id, user_id) is unique.
The match_votes table also has a points_awarded column (integer, default 0) added as a post-init migration. It is set to 1 when a match is finalized and the user’s prediction was correct.
audit_logs
Records admin and referee actions for auditing purposes.
| Column | Type | Description |
|---|
id | serial PK | — |
user_id | FK → users | User who performed the action (set null on delete) |
action | varchar | Action identifier, e.g. "update_match" |
entity_type | varchar | Type of entity affected, e.g. "match" |
entity_id | integer | ID of the affected entity |
details | jsonb | Additional context as JSON |
created_at | timestamp | When the action occurred |
Key relationships
team_players is the join table linking teams and players. Because it includes season_id, the same player can appear on different teams in different seasons.team_stats tracks standings per (team_id, group_id, season_id) triple. This allows the same team to have separate standings rows if it participates in multiple groups or seasons.player_stats aggregates per (player_id, season_id), so a player’s stats reset each season.match_votes gives each user one prediction per match, with points accumulated in users.points.
Redis caching
The backend caches two categories of data in Redis to reduce database load:
| Data | Cache key pattern | TTL |
|---|
| Individual match detail | match:{id} | 30 seconds (CACHE_TTL_MS in cache.js) |
| Match lists (all, current, by season) | all_matches, matches:current, matches:season:{id} | 30 seconds |
| Standings | standings, standings:{season_id} | 1 hour |
| Global last admin activity | global_last_activity | No TTL (updated on write) |
All match-related cache keys are invalidated immediately whenever a match is updated or a new event is recorded. Standings are also flushed at that point. This means the TTL values are a safety net rather than the primary invalidation mechanism.