army_lists

Top-level army record for users, imports, TO flows, and guest armies

SQL Army System Transactional

army_lists

Purpose

Stores the main army header record. This table represents a full army list owned by a user and provides the parent row for all army units. It supports normal persistent armies, TO-managed armies, and ephemeral guest armies tied to a game.

Role in the system

users
 └── army_lists
      └── army_units

This table is the bridge between identity and gameplay. It is used by army management screens, import commit endpoints, TO entrant-army workflows, game setup, and reporting.

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`user_id`	bigint unsigned	No	Owning user
`name`	varchar(200)	No	Army name
`system`	varchar(100)	Yes	Game system text, usually `the-old-world`
`race_id`	int unsigned	Yes	Resolved race reference
`faction_id`	int unsigned	Yes	Resolved faction reference
`faction`	varchar(120)	Yes	Legacy faction text copy
`points_limit`	int	Yes	Army target points value
`scaling_factor`	decimal(6,2)	No	Scaling multiplier, default 1.00
`source_format`	varchar(50)	Yes	Import/manual source identifier
`source_text`	mediumtext	Yes	Original imported text or source payload
`created_at`	datetime(3)	No	Creation timestamp
`updated_at`	datetime(3)	No	Last update timestamp
`is_ephemeral`	tinyint(1)	No	Guest / temporary army flag
`ephemeral_game_id`	bigint unsigned	Yes	Game link for ephemeral armies
`ephemeral_side_code`	char(1)	Yes	Game side for ephemeral armies

Keys and indexes

PRIMARY KEY (id)
ix_army_lists_user (user_id)
idx_army_lists_race_id (race_id)
idx_army_lists_faction_id (faction_id)
idx_ephemeral (is_ephemeral, ephemeral_game_id, ephemeral_side_code)
idx_user_ephemeral (user_id, is_ephemeral)
idx_army_lists_ephemeral (user_id, is_ephemeral, ephemeral_game_id, ephemeral_side_code)

Foreign keys

fk_army_lists_user: army_lists.user_id → users.id ON DELETE CASCADE
fk_army_lists_race: army_lists.race_id → race.id
fk_army_lists_faction: army_lists.faction_id → factions.id

Relationships

Relationship	Type	Notes
`army_lists.user_id → users.id`	Many-to-one	Each army belongs to one user
`army_units.army_list_id → army_lists.id`	One-to-many	Army header owns its unit rows
`tournament_entrant_armies.army_list_id → army_lists.id`	One-to-many / linking	TO entrant linking layer references armies
`games.side army selections`	Referenced externally	Games point to army list ids during setup

Army types supported

Type	How identified	Typical usage
Persistent user army	`is_ephemeral = 0`	Normal My Armies flows
TO-managed event army	Normal row plus entrant linking	Entrant management / imports
Ephemeral guest army	`is_ephemeral = 1`	Unregistered opponent / temporary game setup

Used by

Armies create/get/list/update/delete endpoints
Import commit endpoints for OWB / NR / SIC
TO entrant army workflows
Game setup and guest army flows
Performance and reporting pages

Design notes

This table mixes reference links (race_id, faction_id) with user-entered/import metadata
faction is a legacy text copy kept for compatibility and display
source_text can become large because imported army text is stored here
Ephemeral support is built into the main table rather than split to a separate guest-army table

Risks

One table currently serves multiple conceptual army lifecycles
Ephemeral rows must be excluded carefully from normal user listings
Legacy faction text can drift conceptually from faction_id if update logic is inconsistent
Large source_text payloads may affect storage and backup size

Recommended future improvements

Document or enforce stronger lifecycle rules for ephemeral vs persistent armies
Consider whether TO-managed and ephemeral armies should remain in the same table long-term
Add clearer constraints around ephemeral_side_code values if needed
Consider normalised source/import metadata if imports expand further