game_events

Event-driven scoring and gameplay fact table

SQL Gameplay Critical Runtime

game_events

Purpose

Stores gameplay and scoring events as an append-style ledger. This is the central event table used to calculate scores, reconstruct game history, attribute actions to sides and turns, and drive the play-state UI.

Columns

Column	Type	Null	Notes
`id`	bigint unsigned	No	Primary key
`game_id`	bigint unsigned	No	Parent game
`turn_id`	bigint unsigned	Yes	Game turn reference
`player_turn_id`	bigint unsigned	Yes	Player turn reference
`side_code`	char(1)	No	Scoring side
`target_side_code`	char(1)	Yes	Opposing/target side where relevant
`event_type`	varchar(50)	No	Event classifier
`points_delta`	int	No	Points awarded/changed by event
`unit_id`	bigint unsigned	Yes	Related game unit where applicable
`meta`	longtext JSON	Yes	Flexible JSON payload
`client_event_id`	varchar(64)	Yes	Idempotency token from client
`created_at`	datetime(3)	No	Creation timestamp

Keys and indexes

PRIMARY KEY (id)
UNIQUE KEY uq_client_event (game_id, client_event_id)
UNIQUE KEY ux_game_client_event (game_id, client_event_id)
KEY ix_events_game (game_id, id)
KEY ix_events_turn (turn_id, id)
KEY ix_events_side (game_id, side_code, id)
KEY ix_events_type (game_id, event_type, id)
KEY ix_events_unit (unit_id, id)
KEY idx_game_events_player_turn (player_turn_id)

Foreign keys

fk_events_game: game_events.game_id → games.id ON DELETE CASCADE
fk_events_turn: game_events.turn_id → game_turns.id ON DELETE SET NULL
fk_events_unit: game_events.unit_id → game_units.id ON DELETE SET NULL

Note: player_turn_id is indexed but currently has no explicit FK constraint.

Event model

This is the main scoring ledger. Each row represents one meaningful scoring or state event, such as destruction, below-25, objective control, or end-of-game scoring.

The exact allowed event_type values are controlled by application logic rather than DB constraints.

Idempotency support

Client-originated event creation uses client_event_id to prevent duplicates. Uniqueness is scoped by game, which is why the unique key includes game_id.

Used by

Events/create and events/delete endpoints
Play-state projection
Game breakdown views
Performance aggregation by event type

Design notes

This table is the main gameplay fact ledger
meta provides flexible payloads without constant schema changes
Detailed score/state projection is partly event-driven and partly denormalized into game_units
Turn/player-turn linkage allows event attribution at multiple time granularities

Risks

Incorrect event insertion/deletion has immediate scoring impact
JSON meta is flexible but less strongly structured
No FK on player_turn_id means some integrity relies on application logic

Recommended future improvements

Consider explicit FK on player_turn_id if safe to add
Document event-type taxonomy centrally
Add reconciliation tooling between events and unit state