--- type: reference title: "Pokémon TCG — Schema Evolution and Relational v2 Boundary" description: "Design lineage from the populated SQLite schema to the proposed relational v2, including identity, replay, experiment and rating relationships that still require review." tags: [pokemon-tcg, schema, sqlite, relational, data-model, replay, ratings, provenance] timestamp: "2026-08-15T17:35:00-03:00" --- # 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 ```mermaid 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: 1. **Stable identity:** agents, models, cards, deck families, revisions and submissions. 2. **Immutable evidence:** tournament and match facts, replay steps, options, actions, events, snapshots and card movements. 3. **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: 1. Which exact fields are present in the environment's replay JSON? 2. Which participant identity is available for local and remote matches? 3. How should deck families and immutable revisions behave in the builder? 4. At what grain are ratings updated, and which source boundaries apply? 5. Which relations are specific to each experiment type? 6. 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.