Database
The app uses PostgreSQL with SQLAlchemy as the ORM layer. All table definitions live in models.py and all database interaction lives in database.py.
Tables
sessions
Created and maintained by coaching staff, typically on a weekly basis. Not required for player registrations — the session_id string is the join key used by the reporting app.
| Column | Type | Notes |
|---|---|---|
| id | integer | Surrogate primary key |
| session_id | varchar(64) | Natural key, unique (e.g. "20240903U21") |
| date | date | Session date |
| duration | integer | Planned session duration in minutes |
| session_type | varchar(64) | e.g. "training", "match" |
| team | varchar(64) | e.g. "U21", "U18" |
| weeknumber | integer | ISO week number (1–53) |
players
The club roster. Managed by admin staff at the start of each season.
| Column | Type | Notes |
|---|---|---|
| id | integer | Surrogate primary key |
| player_id | integer | Unique ID shown to players in the app |
| player_first_name | varchar(64) | |
| player_last_name | varchar(64) | |
| team | varchar(64) | e.g. "U21", "U18" |
player_rpe
Post-training RPE registrations submitted via the Post-Training tab.
| Column | Type | Notes |
|---|---|---|
| id | integer | Surrogate primary key |
| session_id | varchar(64) | Join key to sessions (no FK constraint — see below) |
| player_id | integer | FK → players.player_id |
| rpe_score | integer | Borg CR-10 score, 0–10 |
| training_minutes | integer | Player-reported session duration |
| session_load | integer | rpe_score × training_minutes (sRPE load) |
| individual_session | boolean | True if player trained separately from group |
| submitted_at | timestamp | Server time of submission |
player_wellness
Pre-training wellness check-ins submitted via the Pre-Training tab.
| Column | Type | Notes |
|---|---|---|
| id | integer | Surrogate primary key |
| session_id | varchar(64) | Join key to sessions (no FK constraint — see below) |
| player_id | integer | FK → players.player_id |
| feeling | numeric(3,1) | Wellbeing score 1–5, nullable (player may skip) |
| sleep_hours | double | Hours slept, 0–24 |
| submitted_at | timestamp | Server time of submission |
Design decisions
No FK constraint on session_id
player_rpe.session_id and player_wellness.session_id are plain strings rather than foreign keys to sessions.session_id. This is intentional — sessions are created by coaching staff and may not exist at the time a player submits their data (late planning, retroactive session creation). Blocking player registrations due to a missing session record is not acceptable. The reporting app resolves unmatched session IDs and can flag them for follow-up.
Idempotent inserts
Both registration tables have a UNIQUE(player_id, session_id) constraint. A duplicate submission raises an IntegrityError in SQLAlchemy which database.py catches silently — the player sees a success message either way. This prevents double-counting in reporting without exposing database errors to players.
session_load computed in the app layer
session_load = rpe_score × training_minutes is calculated in database.py before the insert rather than as a PostgreSQL generated column. This keeps the SQLAlchemy model portable and avoids cross-database compatibility issues. A CHECK constraint enforces correctness at the database level.
Adding players
Players are added directly via SQL or a database client. There is no admin UI in the registration app — that is handled separately.
INSERT INTO players (player_id, player_first_name, player_last_name, team)
VALUES (7, 'Firstname', 'Lastname', 'U21');
Adding sessions
Sessions are added directly via SQL or a database client ahead of each training week.