candidate

Description

A game or expansion on its way into the engine’s catalogue: created from BGG data, enriched with rules found on the web and interpreted by an LLM, reviewed by a human and then published to the engine. A published candidate can be approved again with new values; the pipeline then updates the engine’s game or expansion.

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
id	bigint	nextval(‘candidate_seq’::regclass)	false	public.candidate_reliability_check public.bgg_ranking public.raw_artifact
public_id	uuid	gen_random_uuid()	false
bgg_id	bigint		false
display_name	varchar(255)		false
description	text		true
min_players	integer		true
max_players	integer		true
playing_time	integer		true
min_age	integer		true
year_published	integer		true
image_url	varchar(500)		true
tags	text[]	‘{}’::text[]	false
is_expansion	boolean	false	false
base_game_bgg_id	bigint		true
score_type	varchar(16)		true
win_condition	varchar(16)		true
stage	varchar(32)	‘PENDING_RULES’::character varying	false
status	varchar(16)	‘PENDING’::character varying	false
reliability	varchar(16)	‘UNVERIFIED’::character varying	false
failure_reason	text		true
publish_request_id	uuid		true			Idempotency-Key of the pending publication to the engine, so a retry never creates a duplicate.
engine_game_id	uuid		true			Public id of the game in the engine (no FK: the engine owns it). For an expansion: its base game.
engine_expansion_id	uuid		true			Public id of the expansion in the engine, once published.
created_at	timestamp with time zone	now()	false
updated_at	timestamp with time zone		true
version	bigint		false

Constraints

Name	Type	Definition
chk_candidate_expansion_base_game	CHECK	CHECK (((NOT is_expansion) OR (base_game_bgg_id IS NOT NULL)))
chk_candidate_player_range	CHECK	CHECK ((((min_players IS NULL) OR (min_players >= 1)) AND ((max_players IS NULL) OR (min_players IS NULL) OR (max_players >= min_players))))
chk_candidate_reliability	CHECK	CHECK (((reliability)::text = ANY ((ARRAY[‘HIGH’::character varying, ‘MEDIUM’::character varying, ‘LOW’::character varying, ‘UNVERIFIED’::character varying])::text[])))
chk_candidate_score_type	CHECK	CHECK (((score_type IS NULL) OR ((score_type)::text = ANY ((ARRAY[‘SCORE’::character varying, ‘TIME’::character varying, ‘TURNS’::character varying, ‘WIN_LOSS’::character varying])::text[]))))
chk_candidate_stage	CHECK	CHECK (((stage)::text = ANY ((ARRAY[‘PENDING_RULES’::character varying, ‘RULES_COLLECTED’::character varying, ‘INTERPRETED’::character varying, ‘FAILED’::character varying])::text[])))
chk_candidate_status	CHECK	CHECK (((status)::text = ANY ((ARRAY[‘PENDING’::character varying, ‘APPROVED’::character varying, ‘PUBLISHED’::character varying, ‘REJECTED’::character varying])::text[])))
chk_candidate_win_condition	CHECK	CHECK (((score_type IS NULL) OR (((score_type)::text = ‘WIN_LOSS’::text) AND (win_condition IS NULL)) OR (((score_type)::text <> ‘WIN_LOSS’::text) AND ((win_condition IS NULL) OR ((win_condition)::text = ANY ((ARRAY[‘HIGHER_WINS’::character varying, ‘LOWER_WINS’::character varying])::text[]))))))
candidate_pkey	PRIMARY KEY	PRIMARY KEY (id)
uq_candidate_public_id	UNIQUE	UNIQUE (public_id)
uq_candidate_bgg_id	UNIQUE	UNIQUE (bgg_id)

Indexes

Name	Definition
candidate_pkey	CREATE UNIQUE INDEX candidate_pkey ON public.candidate USING btree (id)
uq_candidate_public_id	CREATE UNIQUE INDEX uq_candidate_public_id ON public.candidate USING btree (public_id)
uq_candidate_bgg_id	CREATE UNIQUE INDEX uq_candidate_bgg_id ON public.candidate USING btree (bgg_id)
ix_candidate_stage	CREATE INDEX ix_candidate_stage ON public.candidate USING btree (stage)
ix_candidate_status	CREATE INDEX ix_candidate_status ON public.candidate USING btree (status)
ix_candidate_base_game_bgg_id	CREATE INDEX ix_candidate_base_game_bgg_id ON public.candidate USING btree (base_game_bgg_id)

Relations

---

Generated by tbls