game_units

Instantiated unit rows for a specific game side

SQL Gameplay Critical Runtime

game_units

Purpose

Stores the per-game instantiated copy of army units. This is where gameplay state is tracked: destruction, below-25 status, side ownership, and event-linked scoring state.

Role in the system

army_units
   ↓ copied into
game_units
   ↑ referenced by
game_events

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`game_side_id`	bigint unsigned	No	Owning game side
`source_unit_id`	bigint unsigned	Yes	Original army_units source row
`name`	varchar(200)	No	Snapshot unit name
`unit_type`	varchar(100)	Yes	Snapshot unit type
`points`	int	No	Snapshot points
`quantity`	int	No	Snapshot quantity
`notes`	varchar(500)	Yes	Snapshot notes/options
`is_general`	tinyint(1)	No	General flag
`is_bsb`	tinyint(1)	No	BSB flag
`has_standard`	tinyint(1)	No	Banner/standard flag
`sort_order`	int	No	Display order
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`destroyed_at`	datetime(3)	Yes	Unit destroyed marker
`destroyed_in_event_id`	bigint unsigned	Yes	Destroying event reference (soft link)
`destroyed_by_side_code`	char(1)	Yes	Scoring side for destruction
`destroyed_in_player_turn_id`	bigint unsigned	Yes	Player turn when destroyed
`below_25_at`	datetime(3)	Yes	Below-25 marker
`below_25_in_event_id`	bigint unsigned	Yes	Event reference for below-25
`below_25_by_side_code`	char(1)	Yes	Scoring side for below-25
`below_25_in_player_turn_id`	bigint unsigned	Yes	Player turn for below-25
`master_unit_id`	int	No	Copied canonical master unit identifier, default -1

Keys and indexes

PRIMARY KEY (id)
KEY ix_game_units_side (game_side_id)
KEY ix_game_units_side_sort (game_side_id, sort_order, id)
KEY ix_game_units_source (source_unit_id)
idx_game_units_destroyed (destroyed_at)
idx_game_units_master_unit_id (master_unit_id)
idx_game_units_below25_at (below_25_at)
idx_game_units_below25_event (below_25_in_event_id)
idx_game_units_destroyed_at (destroyed_at)

Foreign keys

fk_game_units_side: game_units.game_side_id → game_sides.id ON DELETE CASCADE
fk_game_units_source: game_units.source_unit_id → army_units.id ON DELETE SET NULL

Relationships

Relationship	Type	Notes
`game_units.game_side_id → game_sides.id`	Many-to-one	Unit belongs to one game side
`game_units.source_unit_id → army_units.id`	Optional many-to-one	Original army unit snapshot source
`game_events.unit_id → game_units.id`	One-to-many	Events attach to game units

Gameplay state model

This table stores persistent in-game state rather than recalculating everything from events at read time. Key tracked states include:

Destroyed
Below 25%
Who scored the state
Which event and player turn caused the state

Used by

Game start/setup side flows that instantiate units
Event create/delete logic
Play-state calculation and UI
Game breakdown and standings context

Design notes

This is effectively a snapshot-plus-state table
State is denormalized intentionally for fast gameplay reads
Soft links such as destroyed_in_event_id are not enforced by FK constraints
master_unit_id is preserved for canonical reporting/debugging even though source_unit_id points to army_units

Risks

Denormalized state can drift if event creation/deletion logic is incorrect
Missing FKs on destroyed/below25 reference fields mean consistency relies on application logic
This table is central to scoring correctness

Recommended future improvements

Document state-transition rules explicitly
Consider whether some reference fields should have FKs if migration risk is acceptable
Add integrity diagnostics/admin tooling for state/event reconciliation