games

Top-level gameplay record for setup, play, scoring, and finalisation

SQL Gameplay Critical Runtime

games

Purpose

Stores the main game header record. This table defines the overall identity, lifecycle, tournament context, ownership, and final scores of a game. Nearly every gameplay endpoint starts here.

Role in the system

users
 └── games
      ├── game_sides
      │    └── game_units
      ├── game_turns
      │    └── game_player_turns
      └── game_events

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`user_id`	bigint unsigned	No	Creator / owning user
`opponent_user_id`	bigint unsigned	Yes	Registered opponent when applicable
`tournament_id`	int unsigned	Yes	Optional tournament linkage
`tournament_round`	smallint unsigned	Yes	Round number within tournament context
`tournament_entrant_a_id`	bigint unsigned	Yes	Event-game entrant mapping
`tournament_entrant_b_id`	bigint unsigned	Yes	Event-game entrant mapping
`name`	varchar(200)	Yes	Optional game name
`game_system`	varchar(100)	Yes	System text
`scenario_name`	varchar(200)	Yes	Scenario label
`first_side_code`	char(1)	Yes	Side taking first turn
`status`	varchar(20)	No	Game lifecycle state, default `setup`
`started_at`	datetime(3)	Yes	Game start timestamp
`ended_at`	datetime(3)	Yes	Game end timestamp
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`archived_at`	datetime	Yes	Archive marker
`creator_side_code`	char(1)	No	Which side belongs to creator, default A
`final_score_a`	int	Yes	Final VP/score for side A
`final_score_b`	int	Yes	Final VP/score for side B
`finalised_tournament_round`	generated smallint unsigned	Yes	Generated only when status is `finalised`

Generated column behaviour

finalised_tournament_round is generated as:

CASE WHEN status = 'finalised' THEN tournament_round ELSE NULL END

This supports the uniqueness rule that prevents duplicate finalised rounds for the same user/tournament combination without blocking draft/setup records.

Keys and indexes

PRIMARY KEY (id)
UNIQUE KEY uq_games_user_finalised_tournament_round (user_id, tournament_id, finalised_tournament_round)
ix_games_user (user_id)
ix_games_status (user_id, status, started_at)
idx_games_opponent_user (opponent_user_id)
idx_games_user_archived (user_id, archived_at)
idx_games_user_updated (user_id, updated_at)
idx_games_scores (final_score_a, final_score_b)
idx_games_tournament_id (tournament_id)
idx_games_tournament_round (tournament_id, tournament_round)
idx_games_user_tournament_round (user_id, tournament_id, tournament_round)
idx_games_tournament_entrant_a (tournament_entrant_a_id)
idx_games_tournament_entrant_b (tournament_entrant_b_id)

Foreign keys

fk_games_user: games.user_id → users.id ON DELETE CASCADE
fk_games_tournament: games.tournament_id → tournaments.id ON DELETE SET NULL ON UPDATE CASCADE
fk_games_tournament_entrant_a: games.tournament_entrant_a_id → tournament_entrants.id
fk_games_tournament_entrant_b: games.tournament_entrant_b_id → tournament_entrants.id

Relationships

Relationship	Type	Notes
`game_sides.game_id → games.id`	One-to-many	Two side rows per game in practice
`game_turns.game_id → games.id`	One-to-many	Game-wide turn sequence
`game_player_turns.game_id → games.id`	One-to-many	Player-turn sequence
`game_events.game_id → games.id`	One-to-many	Scoring and state events

Lifecycle states

The schema does not enforce an enum at DB level, but application logic uses states such as:

setup
active
ended
finalised

Used by

Game create/get/list/update/archive endpoints
Game setup, play, end and finalise flows
Performance pages and standings
Tournament-linked event games

Design notes

Supports both non-event and tournament/event games in one table
Archiving is implemented as a timestamp rather than a boolean
Final scores are stored on the game header, while detailed scoring remains event-driven
The uniqueness rule for finalised tournament rounds is one of the most important business constraints in the schema

Risks

State transitions are mostly application-controlled rather than DB-enforced
Tournament linkage increases complexity, especially for round uniqueness
Changing tournament linkage after play starts can distort standings semantics

Recommended future improvements

Document lifecycle transition rules explicitly
Consider normalising or constraining status values at DB level
Document update restrictions for tournament-linked fields once a game begins