Pokémon TCG — Schema Evolution and Relational v2 Boundary

Provenance and status

docs/schema-evolution.md was introduced in project commit 05c4435 on 2026-07-27. It records two logical models: the physical database already created by rl/results_db.py, and a relational v2 proposal under review. The proposal is not a migration authorization. The populated current snapshot and its integrity findings live in pokemon_tcg_sqlite_schema_current; the implementation gap is summarized in pokemon_tcg_local_platform_status.

The document matters because ratings, tournaments and training claims are only reproducible when the identity and evidence that produced them are explicit. It is therefore a data-lineage design, not merely a table-renaming exercise.

Current physical model

The current schema already connects tournament summaries, matchups, matches, steps, options, events, board snapshots, field Pokémon, cards, decks and source-separated card/deck Elo. Its debt is structural:

agents and sources are partly represented as text or legacy fields rather than stable relational identities;
replay actions and enum values still include serialized or magic forms;
there is no complete zone_cards relation or card-movement history;
deck compositions do not yet have the full immutable family/revision model;
experiment, anamnese, training-run and dashboard-configuration records are incomplete relative to the target;
matchups.replay_html is legacy storage and is not part of the target.

These are design observations, not a claim that the current database contains no normalized evidence. The current database has real replay rows and a real idempotent receipt table, while the August audit also found unresolved FK violations. Both facts must remain visible.

Relational v2 topology

erDiagram
    DATA_SOURCES ||--o{ TOURNAMENTS : classifies
    TOURNAMENT_CONFIGS ||--o{ TOURNAMENTS : configures
    TOURNAMENTS ||--o{ MATCHUPS : contains
    MATCHUPS ||--o{ MATCHES : produces
    MATCHES ||--o{ MATCH_PARTICIPANTS : has
    AGENTS ||--o{ MATCH_PARTICIPANTS : participates
    DECK_REVISIONS ||--o{ MATCH_PARTICIPANTS : uses
    DECK_FAMILIES ||--o{ DECK_REVISIONS : versions
    DECK_REVISIONS ||--o{ DECK_REVISION_CARDS : contains
    CARDS ||--o{ DECK_REVISION_CARDS : included
    MATCHES ||--o{ MATCH_STEPS : has
    MATCH_STEPS ||--o{ STEP_OPTIONS : offers
    MATCH_STEPS ||--o{ STEP_ACTIONS : records
    MATCH_STEPS ||--o{ STEP_EVENTS : logs
    MATCH_STEPS ||--o{ BOARD_SNAPSHOTS : captures
    BOARD_SNAPSHOTS ||--o{ ZONE_CARDS : contains
    BOARD_SNAPSHOTS ||--o{ POKEMON_ON_FIELD : places
    EXPERIMENTS ||--o{ TRAINING_RUNS : may_have
    EXPERIMENTS ||--o{ EXPERIMENT_DECK_TESTS : evaluates
    DECK_REVISIONS ||--o{ DECK_RATINGS : aggregates
    CARDS ||--o{ CARD_RATINGS : aggregates

The proposed topology separates three kinds of records:

Stable identity: agents, models, cards, deck families, revisions and submissions.
Immutable evidence: tournament and match facts, replay steps, options, actions, events, snapshots and card movements.
Derived aggregates: source-scoped ratings, training metrics and report summaries that can be recomputed from the first two layers.

This separation is the schema-level counterpart of the project's provenance rule: a derived Elo row cannot become a substitute for the match evidence that produced it.

Design deltas by research question

Research question	v2 relation family	Why it is needed	Current boundary
Which artifact participated?	`models`, `model_revisions`, `submissions`	Distinguishes an editable name from an immutable artifact	Current submissions exist, but the complete model/revision lineage is incomplete.
Which deck was actually used?	`deck_families`, `deck_revisions`, `deck_revision_cards`	Preserves composition changes without rewriting old matches	Exact decks and fingerprints exist; full family/revision semantics remain target work.
What did a match contain?	`match_participants`, normalized replay relations, card movements	Makes each actor, seat, card serial and state transition inspectable	Current replay rows are useful but do not yet satisfy the complete zone/effect contract.
Why was a model changed?	`experiments`, observations, training runs and analyses	Joins hypothesis, config, checkpoint, evidence and retrospective interpretation	Narrative/manuscript records exist; normalized experiment lineage is not complete.
Which rating pool produced a number?	policies, epochs, source-separated rating tables and events	Prevents local, remote, deck and submission scales from being silently mixed	Current card/deck source separation exists; policy, epoch and append-only event families are incomplete.
Can a result be retried safely?	digests, natural keys and `operation_receipts`	Makes imports and administrative writes idempotent	Receipts are implemented for current flows; target coverage is broader.

Integrity rules

The target model requires every connection to enable foreign keys, every enum-like domain to use a reference table, and every immutable artifact to carry a content digest. Sequence keys and source-scoped uniqueness prevent duplicate child rows. Historical corrections create revisions or event rows instead of mutating prior evidence.

The replay rule is especially strict: if an official payload has a field not represented in the typed model, the next review must add a typed scalar or relation after fixture evidence. A generic JSON or HTML escape hatch is not an acceptable shortcut.

Review gate before migration

The source document leaves six questions open before a physical DDL change:

Which exact fields are present in the environment's replay JSON?
Which participant identity is available for local and remote matches?
How should deck families and immutable revisions behave in the builder?
At what grain are ratings updated, and which source boundaries apply?
Which relations are specific to each experiment type?
Which provider fields identify Kaggle teams, submissions and episodes?

Until these are answered by fixtures and current artifacts, the v2 diagram is an architectural proposal. The current database must not be silently deleted, reconstructed or “cleaned” to make it look like the diagram.

Relationship to the ETL and arena plans

Schema v2 is downstream of the zero-trust ETL sequence. ZIP contents, SQLite imports, Parquet manifests and declared metadata must first be reconciled, and orphan roots must be inventoried without deletion. The future arena boundary can then assign an arena_run, immutable configuration, selected submissions, matches and rating snapshots to the same relational evidence graph. See pokemon_tcg_data_oracle_etl, pokemon_tcg_aug14_data_etl_database_audit, pokemon_tcg_implementation_specification and pokemon_tcg_arena_future_architecture.

Source anchors

docs/schema-evolution.md, introduced at 05c4435.
docs/database_schema.md, current physical table map in the same design lineage.
rl/results_db.py at 20d7d0d, current implementation surface.
pokemon_tcg_sqlite_schema_current, read-only population and FK audit.
pokemon_tcg_physical_schema, target constraints and idempotency contract.

Cross-references

pokemon_tcg_local_platform_design — current synchronous platform and future application boundary.
pokemon_tcg_replay_data_model — normalized replay obligations.
pokemon_tcg_submissions_and_elo — submission and rating identity.
pokemon_tcg_experiments_and_anamnesis — temporal research records.
pokemon_tcg_current_state_reconciliation — implementation versus design reconciliation.