[Game Systems] GameSystem registry + campaign linkage (backend)

claude-bot commented

2026-07-15 22:00:25 +00:00

Collaborator

Motivation/Context

Today "game system" is a single nullable free-text string on Campaign
(webapp/backend/app/models/campaign.py:35) whose only job is to be interpolated into
LLM prompts and Discord embed footers. There is no registry of systems, no structured
stat model, and no rules awareness anywhere in the codebase. Real-world values are
unnormalised free text — "D&D 5e", "Pathfinder", "PF2e", "5e", "Call of Cthulhu"
all appear interchangeably in fixtures (webapp/backend/tests/test_campaigns.py:47,122,
webapp/frontend/src/pages/Dashboard.test.jsx:37-38, scripts/seed_dev.py:74).

This issue is the foundational step of the Game-Aware Systems pillar (see the
2026-07-15 investigation report, docs/.internal/game-aware-systems-investigation-2026-07-15.md):
a first-class GameSystem registry that later prompt integration, stat schemas, the
stat-block editor, the conversion wizard, and the Foundry adapter registry (#24) all key
off. Everything else in the pillar depends on this landing first.

Approach

New table game_systems:

id                UUID PK
key               TEXT UNIQUE NOT NULL   -- "dnd5e", "pf2e", "generic"; doubles as the
                                          -- Foundry adapter registry key (#24)
name              TEXT NOT NULL          -- "Dungeons & Dragons 5th Edition"
short_name        TEXT NOT NULL          -- "D&D 5e" (embeds, card subtitles)
publisher         TEXT NULL
aliases           JSONB NOT NULL DEFAULT '[]'   -- ["5e","dnd 5e","d&d 5e","dnd5e"]
prompt_hint       TEXT NULL              -- 1-3 sentences of LLM context
foundry_system_id TEXT NULL              -- Foundry's system id, usually == key
is_builtin        BOOLEAN NOT NULL DEFAULT true
is_active         BOOLEAN NOT NULL DEFAULT true
created_by        UUID NULL FK users.id  -- future: user-defined custom systems
created_at        TIMESTAMPTZ

Keep the builtin set at exactly dnd5e, pf2e, generic for phase 1; generic carries
no stat schema and exists so a campaign can be "system-linked" for Foundry passthrough
(#10) without claiming a schema. Custom systems (is_builtin=false) are explicitly out
of scope for phase 1 but shape the table design.

Campaign reference (coexistence contract):

campaigns
  + game_system_id UUID NULL FK game_systems(id) ON DELETE SET NULL
    game_system    TEXT NULL      -- UNCHANGED (models/campaign.py:35)

Free text remains the default: game_system_id IS NULL behaves exactly as today.
One resolution helper used everywhere: effective_system_name = registry.short_name if game_system_id else campaign.game_system. The raw text column is never overwritten
when a system is linked (preserved for rollback/flavour, e.g. "D&D 5e (homebrewed)").
CampaignSummary/CampaignResponse (schemas/campaign.py:139,151) keep game_system: str | null populated via the resolution helper, and add
game_system_ref: {id, key, name, short_name} | null.
Bot endpoints (routers/bot.py:226/271, :411/446) keep sending the resolved plain
string — zero bot-side changes required; bot/questboard_bot/api_client.py:59,72
stays untouched.
CampaignCreate/CampaignUpdate (schemas/campaign.py:45-118) gain
game_system_id: UUID | None; validation rejects unknown/inactive ids.

Migration (one Alembic revision, following house rules):

CREATE TABLE game_systems … — plain columns, no sa.Enum in op.create_table
(per the project's enum house rule, illustrated by
webapp/backend/alembic/versions/p6q7r8s9t0u1_expand_lore_types.py:38-75).
Raw-SQL INSERT seed rows for dnd5e, pf2e, generic with fixed UUIDs so
dev/prod/test agree.
ALTER TABLE campaigns ADD COLUMN game_system_id UUID NULL REFERENCES game_systems(id) ON DELETE SET NULL.

No backfill in this migration — matching is a UI nudge, not automatic (see issue 2).
Downgrade drops only the additive column/table; no existing data is touched or lost.

Dependencies

None — this is the foundational issue for the pillar.

Out of scope

Prompt integration (issue 3), stat schemas/storage (issue 4), the stat-block editor
(issue 5), the conversion wizard (issue 7), and Foundry alignment (issue 8) — all
build on this but are separate issues.
Custom/user-defined game systems (is_builtin=false authoring flow).
Any UI (selector, nudge banner) — that's issue 2.
Automatic backfill/linking of existing free-text values — deliberately not done here;
free text is too dirty to convert silently.

Acceptance criteria

game_systems table exists with dnd5e, pf2e, generic seeded via raw-SQL insert
in the migration, fixed UUIDs.
campaigns.game_system_id is a nullable FK to game_systems(id) with ON DELETE SET NULL; existing campaigns.game_system column and its behaviour are unchanged.
A single resolution helper computes the effective display string; used by campaign
schemas, exports, and (later) prompts — no duplicate resolution logic.
CampaignSummary/CampaignResponse expose both game_system (resolved string,
backward compatible) and the new game_system_ref.
CampaignCreate/CampaignUpdate accept game_system_id, validated against active
registry rows.
Bot payloads (routers/bot.py:226,411) and bot/questboard_bot/api_client.py:59,72
require no changes — verified by existing bot contract tests passing unmodified.
Existing campaigns (game_system_id = NULL) show zero behaviour change in tests.
Alembic downgrade cleanly drops the new column and table.

## Motivation/Context Today "game system" is a single nullable free-text string on `Campaign` (`webapp/backend/app/models/campaign.py:35`) whose only job is to be interpolated into LLM prompts and Discord embed footers. There is no registry of systems, no structured stat model, and no rules awareness anywhere in the codebase. Real-world values are unnormalised free text — `"D&D 5e"`, `"Pathfinder"`, `"PF2e"`, `"5e"`, `"Call of Cthulhu"` all appear interchangeably in fixtures (`webapp/backend/tests/test_campaigns.py:47,122`, `webapp/frontend/src/pages/Dashboard.test.jsx:37-38`, `scripts/seed_dev.py:74`). This issue is the foundational step of the Game-Aware Systems pillar (see the 2026-07-15 investigation report, `docs/.internal/game-aware-systems-investigation-2026-07-15.md`): a first-class `GameSystem` registry that later prompt integration, stat schemas, the stat-block editor, the conversion wizard, and the Foundry adapter registry (#24) all key off. Everything else in the pillar depends on this landing first. ## Approach **New table `game_systems`:** ``` id UUID PK key TEXT UNIQUE NOT NULL -- "dnd5e", "pf2e", "generic"; doubles as the -- Foundry adapter registry key (#24) name TEXT NOT NULL -- "Dungeons & Dragons 5th Edition" short_name TEXT NOT NULL -- "D&D 5e" (embeds, card subtitles) publisher TEXT NULL aliases JSONB NOT NULL DEFAULT '[]' -- ["5e","dnd 5e","d&d 5e","dnd5e"] prompt_hint TEXT NULL -- 1-3 sentences of LLM context foundry_system_id TEXT NULL -- Foundry's system id, usually == key is_builtin BOOLEAN NOT NULL DEFAULT true is_active BOOLEAN NOT NULL DEFAULT true created_by UUID NULL FK users.id -- future: user-defined custom systems created_at TIMESTAMPTZ ``` Keep the builtin set at exactly `dnd5e`, `pf2e`, `generic` for phase 1; `generic` carries no stat schema and exists so a campaign can be "system-linked" for Foundry passthrough (#10) without claiming a schema. Custom systems (`is_builtin=false`) are explicitly out of scope for phase 1 but shape the table design. **Campaign reference (coexistence contract):** ``` campaigns + game_system_id UUID NULL FK game_systems(id) ON DELETE SET NULL game_system TEXT NULL -- UNCHANGED (models/campaign.py:35) ``` - Free text remains the default: `game_system_id IS NULL` behaves exactly as today. - One resolution helper used everywhere: `effective_system_name = registry.short_name if game_system_id else campaign.game_system`. The raw text column is never overwritten when a system is linked (preserved for rollback/flavour, e.g. "D&D 5e (homebrewed)"). - `CampaignSummary`/`CampaignResponse` (`schemas/campaign.py:139,151`) keep `game_system: str | null` populated via the resolution helper, and *add* `game_system_ref: {id, key, name, short_name} | null`. - Bot endpoints (`routers/bot.py:226/271`, `:411/446`) keep sending the resolved plain string — **zero bot-side changes required**; `bot/questboard_bot/api_client.py:59,72` stays untouched. - `CampaignCreate`/`CampaignUpdate` (`schemas/campaign.py:45-118`) gain `game_system_id: UUID | None`; validation rejects unknown/inactive ids. **Migration (one Alembic revision, following house rules):** 1. `CREATE TABLE game_systems …` — plain columns, no `sa.Enum` in `op.create_table` (per the project's enum house rule, illustrated by `webapp/backend/alembic/versions/p6q7r8s9t0u1_expand_lore_types.py:38-75`). 2. Raw-SQL `INSERT` seed rows for `dnd5e`, `pf2e`, `generic` with **fixed UUIDs** so dev/prod/test agree. 3. `ALTER TABLE campaigns ADD COLUMN game_system_id UUID NULL REFERENCES game_systems(id) ON DELETE SET NULL`. No backfill in this migration — matching is a UI nudge, not automatic (see issue 2). Downgrade drops only the additive column/table; no existing data is touched or lost. ## Dependencies None — this is the foundational issue for the pillar. ## Out of scope - Prompt integration (issue 3), stat schemas/storage (issue 4), the stat-block editor (issue 5), the conversion wizard (issue 7), and Foundry alignment (issue 8) — all build on this but are separate issues. - Custom/user-defined game systems (`is_builtin=false` authoring flow). - Any UI (selector, nudge banner) — that's issue 2. - Automatic backfill/linking of existing free-text values — deliberately not done here; free text is too dirty to convert silently. ## Acceptance criteria - `game_systems` table exists with `dnd5e`, `pf2e`, `generic` seeded via raw-SQL insert in the migration, fixed UUIDs. - `campaigns.game_system_id` is a nullable FK to `game_systems(id)` with `ON DELETE SET NULL`; existing `campaigns.game_system` column and its behaviour are unchanged. - A single resolution helper computes the effective display string; used by campaign schemas, exports, and (later) prompts — no duplicate resolution logic. - `CampaignSummary`/`CampaignResponse` expose both `game_system` (resolved string, backward compatible) and the new `game_system_ref`. - `CampaignCreate`/`CampaignUpdate` accept `game_system_id`, validated against active registry rows. - Bot payloads (`routers/bot.py:226,411`) and `bot/questboard_bot/api_client.py:59,72` require no changes — verified by existing bot contract tests passing unmodified. - Existing campaigns (`game_system_id = NULL`) show zero behaviour change in tests. - Alembic downgrade cleanly drops the new column and table.

claude-bot added this to the v3.9.0 - Game-Aware Systems milestone

2026-07-15 22:00:27 +00:00

claude-bot added the

backend

enhancement

labels

2026-07-15 22:00:28 +00:00

claude-bot referenced this issue

2026-07-15 22:00:46 +00:00

[Game Systems] System selector UI + free-text coexistence + suggested-match nudge (frontend) #135

claude-bot referenced this issue

2026-07-15 22:01:06 +00:00

[Game Systems] System-aware LLM prompt context #137

claude-bot referenced this issue

2026-07-15 22:01:35 +00:00

[Game Systems] Versioned stat schemas + stats storage + validation service #139

claude-bot referenced this issue

2026-07-15 22:02:48 +00:00

[Game Systems] Opt-in conversion wizard: free-form to system with LLM-assisted stat backfill #145

claude-bot referenced this issue

2026-07-15 22:03:27 +00:00

[Game Systems] Foundry alignment: one system identity + structured npc_to_actor #151

claude-bot commented

2026-07-18 15:48:27 +00:00

Author

Collaborator

Picking this up as the foundational issue of the v3.9.0 Game-Aware Systems pillar.

Working on branch feat/133-gamesystem-registry-backend, PR to target the integration branch feat/v3.9-game-aware-systems (per-issue PRs onto the integration branch, then one merge to main).

Recon notes grounding the implementation against current code (issue line numbers were stale after #130/#117):

Current migration head is a3b4c5d6e7f8; the new migration chains off it.
Campaign PK is UUID; game_system is free-text Text (schema-capped at 100). The free-text column stays untouched when a system is linked (rollback/flavour), per the coexistence contract.
game_system reaches the bot in exactly two routers/bot.py response models (SessionTimeslotsResponse, NextSessionResponse), both sending the plain string — they'll send the resolved effective name via the new helper, no shape change, no BOT_CONTRACT_VERSION bump (stays 1).
Registry resolution + validation lands in a new services/game_system_service.py (which #137/#139 also build on), plus a GET /api/game-systems list endpoint for #135's selector.

Scope held exactly to this issue: registry table + seed (dnd5e/pf2e/generic, fixed UUIDs), campaigns.game_system_id FK (ON DELETE SET NULL), resolution helper, game_system_ref on campaign responses, game_system_id on create/update with validation. No UI, no prompt integration, no stat schemas (those are #135/#137/#139).

Picking this up as the foundational issue of the v3.9.0 Game-Aware Systems pillar. Working on branch `feat/133-gamesystem-registry-backend`, PR to target the integration branch `feat/v3.9-game-aware-systems` (per-issue PRs onto the integration branch, then one merge to `main`). Recon notes grounding the implementation against current code (issue line numbers were stale after #130/#117): - Current migration head is `a3b4c5d6e7f8`; the new migration chains off it. - `Campaign` PK is UUID; `game_system` is free-text `Text` (schema-capped at 100). The free-text column stays untouched when a system is linked (rollback/flavour), per the coexistence contract. - `game_system` reaches the bot in exactly two `routers/bot.py` response models (`SessionTimeslotsResponse`, `NextSessionResponse`), both sending the plain string — they'll send the *resolved* effective name via the new helper, no shape change, **no `BOT_CONTRACT_VERSION` bump** (stays 1). - Registry resolution + validation lands in a new `services/game_system_service.py` (which #137/#139 also build on), plus a `GET /api/game-systems` list endpoint for #135's selector. Scope held exactly to this issue: registry table + seed (dnd5e/pf2e/generic, fixed UUIDs), `campaigns.game_system_id` FK (`ON DELETE SET NULL`), resolution helper, `game_system_ref` on campaign responses, `game_system_id` on create/update with validation. No UI, no prompt integration, no stat schemas (those are #135/#137/#139).

rbrooks referenced this issue from a commit

2026-07-18 16:26:31 +00:00

feat(backend): GameSystem registry + campaign linkage (#133)

claude-bot referenced this issue from a pull request that will close it,

2026-07-18 16:26:53 +00:00

feat(backend): GameSystem registry + campaign linkage (#133) #205

claude-bot closed this issue

2026-07-18 16:28:57 +00:00

claude-bot referenced this issue from a commit

2026-07-18 16:28:58 +00:00

Merge PR #205: GameSystem registry + campaign linkage (#133)

claude-bot commented

2026-07-18 16:29:47 +00:00

Author

Collaborator

Done and verified — merged into the integration branch feat/v3.9-game-aware-systems via PR #205.

Verification (Docker, pinned Python 3.12):

Full backend suite: 594 passed, no regressions. The CampaignResponse builder refactor touches every campaign endpoint — all green.
New/updated tests cover: linkage on create, resolved response + game_system_ref, invalid/inactive id → 400, free-text campaign unchanged, unlink restores free text, both bot endpoints resolving correctly.
Migration b5c6d7e8f9a0 against a real Postgres 16: upgrade head → downgrade -1 → upgrade head all clean; dnd5e/pf2e/generic seeded; campaigns.game_system_id confirmed as uuid FK ON DELETE SET NULL.

Notes for the dependent issues (#135/#137/#139):

Resolution + validation live in services/game_system_service.py: resolve_effective_system_name, build_game_system_ref, validate_game_system_id, get_active_system, list_active_systems, ensure_builtin_systems.
Registry list endpoint: GET /api/game-systems (authenticated).
Builtin systems use fixed UUIDs mirrored in both the migration and game_system_service.BUILTIN_SYSTEMS; ensure_builtin_systems self-heals for the create_all test harness (tests don't run migrations — relevant for #139, which also seeds).
game_system reaches the bot resolved; BOT_CONTRACT_VERSION stays 1.

Closing; ships to main with the v3.9.0 release (integration branch → main).

Done and verified — merged into the integration branch `feat/v3.9-game-aware-systems` via PR #205. **Verification (Docker, pinned Python 3.12):** - Full backend suite: **594 passed**, no regressions. The `CampaignResponse` builder refactor touches every campaign endpoint — all green. - New/updated tests cover: linkage on create, resolved response + `game_system_ref`, invalid/inactive id → 400, free-text campaign unchanged, unlink restores free text, both bot endpoints resolving correctly. - Migration `b5c6d7e8f9a0` against a real Postgres 16: `upgrade head` → `downgrade -1` → `upgrade head` all clean; dnd5e/pf2e/generic seeded; `campaigns.game_system_id` confirmed as `uuid` FK `ON DELETE SET NULL`. **Notes for the dependent issues (#135/#137/#139):** - Resolution + validation live in `services/game_system_service.py`: `resolve_effective_system_name`, `build_game_system_ref`, `validate_game_system_id`, `get_active_system`, `list_active_systems`, `ensure_builtin_systems`. - Registry list endpoint: `GET /api/game-systems` (authenticated). - Builtin systems use fixed UUIDs mirrored in both the migration and `game_system_service.BUILTIN_SYSTEMS`; `ensure_builtin_systems` self-heals for the `create_all` test harness (tests don't run migrations — relevant for #139, which also seeds). - `game_system` reaches the bot resolved; `BOT_CONTRACT_VERSION` stays 1. Closing; ships to `main` with the v3.9.0 release (integration branch → main).

rbrooks referenced this issue from a commit

2026-07-18 16:57:24 +00:00

feat(backend): versioned stat schemas + stats storage + validation (#139)

claude-bot referenced this issue

2026-07-18 16:57:46 +00:00

feat(backend): versioned stat schemas + stats storage + validation (#139) #206

rbrooks referenced this issue from a commit

2026-07-18 17:03:23 +00:00

feat(frontend): game system selector UI + free-text coexistence + suggested-match nudge (#135)

claude-bot referenced this issue

2026-07-18 17:03:37 +00:00

feat(frontend): game system selector UI + free-text coexistence + suggested-match nudge (#135) #207