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

claude-bot commented

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

Collaborator

Motivation/Context

The closest existing structured surface for NPC/creature/PC data is
LoreEntry.sidebar_fields — a JSONB list of {label, value, visibility, …} rows where
value is a string capped at 300 chars (LoreSidebarFieldInput,
webapp/backend/app/routers/campaigns.py:179-202). That's fine for loose infobox rows
but too weak to represent a real stat block: no ints, no lists, no nesting, and typed
vs. free-form rows would collide in one list. This issue introduces a versioned,
declarative stat schema per game system and a place to store validated structured stats
against it, so later work (the stat-block editor, issue 5; the #130 upgrade, issue 6;
the conversion wizard, issue 7; Foundry's npc_to_actor, issue 8) has a real target
instead of parsing markdown or overloading sidebar_fields.

Three storage options were weighed in the investigation report (§3.2): a typed variant
of sidebar_fields (too weak — no typing/nesting, collides with free-form rows), a
dedicated stat_blocks table (cleanest relational integrity but duplicates the
versioning/draft-rail/export story that lore_entries already has), and a JSONB stats
envelope column on lore_entries (matches house style — sidebar_fields and
timeline_events are already JSONB-on-the-entry, models/lore_entry.py:219-230 — and
rides along with version snapshots, export/import, and the draft rail for free).
Recommendation: the JSONB envelope (Option C), with the dedicated-table approach
documented as the escalation path if cross-entry stat queries become a real need.

Approach

New table game_system_schemas:

id             UUID PK
system_id      UUID NOT NULL FK game_systems(id) ON DELETE CASCADE
version        INTEGER NOT NULL          -- UNIQUE (system_id, version)
status         TEXT NOT NULL DEFAULT 'active'   -- 'draft' | 'active' | 'deprecated'
                                                 -- plain TEXT + CHECK, not a PG enum
definition     JSONB NOT NULL
created_at     TIMESTAMPTZ

definition is a constrained, JSON-Schema-flavoured document, one section per
applicable LoreType (npc, creature, player_character): field groups, field keys,
labels, types (int, str, text, enum, list[str], list[text], bool for v1),
and constraints (min/max/choices). Schema definitions live as JSON files in the
repo (webapp/backend/app/game_systems/dnd5e/schema_v1.json, pf2e/schema_v1.json,
covering npc/creature kinds only for v1) and are loaded/seeded into the table by
migration — versioned in git, reviewable in PRs. Published versions are immutable;
changes mean a new version row. A stat envelope records the version it was written
against and is never silently migrated forward.

stats envelope columns:

lore_entries
  + stats JSONB NULL   -- {"system": "dnd5e", "schema_version": 1, "values": {...},
                        --  "visibility": "gm"}
lore_entry_versions
  + stats JSONB NULL   -- snapshotted like sidebar_fields already is

The envelope stores the system key (not a UUID) so it's stable across exports and
survives a campaign unlinking/relinking (§4.4 of the report). Default visibility is
GM-only, matching the #130 decision on generated stat content. No DB-level FK from
stats.system to the registry — validated on write in the service layer instead.

Validator service — webapp/backend/app/services/game_system_service.py: validates
a values payload against a (system, version) pair (~150 lines; use the jsonschema
library if preferred over hand-rolled checks, no other new dependency needed). Wire
validation into the lore entry CRUD endpoints so a stats write is rejected if it
doesn't match the current schema for the entry's linked system.

Migration (raw-SQL seed, same house rules as #133): CREATE TABLE game_system_schemas with status as TEXT + CHECK (no sa.Enum in
op.create_table), seed dnd5e v1 and pf2e v1 rows loaded from the in-repo JSON files,
then ALTER TABLE lore_entries ADD COLUMN stats JSONB NULL and ALTER TABLE lore_entry_versions ADD COLUMN stats JSONB NULL.

Dependencies

#133 (GameSystem registry + campaign linkage) — game_system_schemas.system_id FKs
to game_systems, and the campaign needs a linked system for its lore entries' stats
to validate against.

Out of scope

The stat-block editor UI and infobox projection — issue 5.
Wiring generate_statblock (the #130 upgrade) to emit schema-valid values — issue 6.
The opt-in conversion wizard that backfills stats from existing free-form data —
issue 7.
Any rules engine, derived-value computation, or combat math — explicitly not part of
this design (validation only, per the report's "NOT rules" principle).
Custom/user-authored schemas — the schema table design accommodates them later
(created_by on game_systems) but authoring tooling is out of scope here.

Acceptance criteria

game_system_schemas table exists, seeded with dnd5e v1 and pf2e v1 definitions
covering npc and creature entity kinds, loaded from versioned JSON files in the
repo.
lore_entries.stats and lore_entry_versions.stats are nullable JSONB columns;
existing entries are unaffected (stats IS NULL).
A validator service rejects a stats.values payload that doesn't conform to the
referenced (system, schema_version) definition (wrong type, out-of-range, unknown
enum choice) and accepts a conforming one.
Writing/updating an entry's stats through the lore endpoints validates server-side;
reading returns the envelope as-is including its self-described system and
schema_version.
LoreEntryVersion snapshots capture stats alongside sidebar_fields on every
approval/edit that changes it.
Default stats.visibility is GM-only unless explicitly set otherwise.
Alembic downgrade drops the new table and columns without touching existing data.

## Motivation/Context The closest existing structured surface for NPC/creature/PC data is `LoreEntry.sidebar_fields` — a JSONB list of `{label, value, visibility, …}` rows where `value` is a string capped at 300 chars (`LoreSidebarFieldInput`, `webapp/backend/app/routers/campaigns.py:179-202`). That's fine for loose infobox rows but too weak to represent a real stat block: no ints, no lists, no nesting, and typed vs. free-form rows would collide in one list. This issue introduces a versioned, declarative stat schema per game system and a place to store validated structured stats against it, so later work (the stat-block editor, issue 5; the #130 upgrade, issue 6; the conversion wizard, issue 7; Foundry's `npc_to_actor`, issue 8) has a real target instead of parsing markdown or overloading `sidebar_fields`. Three storage options were weighed in the investigation report (§3.2): a typed variant of `sidebar_fields` (too weak — no typing/nesting, collides with free-form rows), a dedicated `stat_blocks` table (cleanest relational integrity but duplicates the versioning/draft-rail/export story that `lore_entries` already has), and a JSONB `stats` envelope column on `lore_entries` (matches house style — `sidebar_fields` and `timeline_events` are already JSONB-on-the-entry, `models/lore_entry.py:219-230` — and rides along with version snapshots, export/import, and the draft rail for free). **Recommendation: the JSONB envelope (Option C)**, with the dedicated-table approach documented as the escalation path if cross-entry stat queries become a real need. ## Approach **New table `game_system_schemas`:** ``` id UUID PK system_id UUID NOT NULL FK game_systems(id) ON DELETE CASCADE version INTEGER NOT NULL -- UNIQUE (system_id, version) status TEXT NOT NULL DEFAULT 'active' -- 'draft' | 'active' | 'deprecated' -- plain TEXT + CHECK, not a PG enum definition JSONB NOT NULL created_at TIMESTAMPTZ ``` `definition` is a constrained, JSON-Schema-flavoured document, one section per applicable `LoreType` (`npc`, `creature`, `player_character`): field groups, field keys, labels, types (`int`, `str`, `text`, `enum`, `list[str]`, `list[text]`, `bool` for v1), and constraints (`min`/`max`/`choices`). Schema definitions live as JSON files in the repo (`webapp/backend/app/game_systems/dnd5e/schema_v1.json`, `pf2e/schema_v1.json`, covering `npc`/`creature` kinds only for v1) and are loaded/seeded into the table by migration — versioned in git, reviewable in PRs. **Published versions are immutable**; changes mean a new version row. A stat envelope records the version it was written against and is never silently migrated forward. **`stats` envelope columns:** ``` lore_entries + stats JSONB NULL -- {"system": "dnd5e", "schema_version": 1, "values": {...}, -- "visibility": "gm"} lore_entry_versions + stats JSONB NULL -- snapshotted like sidebar_fields already is ``` The envelope stores the system **key** (not a UUID) so it's stable across exports and survives a campaign unlinking/relinking (§4.4 of the report). Default visibility is GM-only, matching the #130 decision on generated stat content. No DB-level FK from `stats.system` to the registry — validated on write in the service layer instead. **Validator service** — `webapp/backend/app/services/game_system_service.py`: validates a `values` payload against a `(system, version)` pair (~150 lines; use the `jsonschema` library if preferred over hand-rolled checks, no other new dependency needed). Wire validation into the lore entry CRUD endpoints so a `stats` write is rejected if it doesn't match the current schema for the entry's linked system. **Migration** (raw-SQL seed, same house rules as #133): `CREATE TABLE game_system_schemas` with `status` as `TEXT` + `CHECK` (no `sa.Enum` in `op.create_table`), seed dnd5e v1 and pf2e v1 rows loaded from the in-repo JSON files, then `ALTER TABLE lore_entries ADD COLUMN stats JSONB NULL` and `ALTER TABLE lore_entry_versions ADD COLUMN stats JSONB NULL`. ## Dependencies - #133 (GameSystem registry + campaign linkage) — `game_system_schemas.system_id` FKs to `game_systems`, and the campaign needs a linked system for its lore entries' stats to validate against. ## Out of scope - The stat-block editor UI and infobox projection — issue 5. - Wiring `generate_statblock` (the #130 upgrade) to emit schema-valid `values` — issue 6. - The opt-in conversion wizard that backfills `stats` from existing free-form data — issue 7. - Any rules engine, derived-value computation, or combat math — explicitly not part of this design (validation only, per the report's "NOT rules" principle). - Custom/user-authored schemas — the schema table design accommodates them later (`created_by` on `game_systems`) but authoring tooling is out of scope here. ## Acceptance criteria - `game_system_schemas` table exists, seeded with dnd5e v1 and pf2e v1 definitions covering `npc` and `creature` entity kinds, loaded from versioned JSON files in the repo. - `lore_entries.stats` and `lore_entry_versions.stats` are nullable JSONB columns; existing entries are unaffected (`stats IS NULL`). - A validator service rejects a `stats.values` payload that doesn't conform to the referenced `(system, schema_version)` definition (wrong type, out-of-range, unknown enum choice) and accepts a conforming one. - Writing/updating an entry's `stats` through the lore endpoints validates server-side; reading returns the envelope as-is including its self-described `system` and `schema_version`. - `LoreEntryVersion` snapshots capture `stats` alongside `sidebar_fields` on every approval/edit that changes it. - Default `stats.visibility` is GM-only unless explicitly set otherwise. - Alembic downgrade drops the new table and columns without touching existing data.

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

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

claude-bot added the

backend

enhancement

labels

Rows
Columns

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

Motivation/Context

Approach

Dependencies

Out of scope

Acceptance criteria

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