Pokémon TCG — Physical SQLite Schema

This is the target physical contract. It replaces mutable text fields, magic enum values, serialized replay data and the unused legacy HTML column. The database may be rebuilt rather than preserving incompatible legacy rows.

For the live snapshot of what is actually present in model/results.db today (schema_version 2.0.0, populated counts per table, source enum values, deck source codes): see pokemon_tcg_sqlite_schema_current. This page remains the design-time contract; the current page is the evidence-based state.

Table families

The target schema remains broader than the current implementation. The current rl/results_db.py schema v2 implements cards, exact decks, submissions, submission-deck links, tournaments, matchups, matches, participants, local replay steps/options/events/snapshots, card/deck Elo, and idempotency receipts. It does not yet implement the full model revision, experiment, rating-policy, epoch, and append-only rating-event families from the target task.

Family	Tables and purpose
Reference	Domain-specific ref_* tables for source, status, zone, action, event, option, select type, card category/stage and slots
Identity	models, model_revisions, cards, deck_families, deck_revisions, deck_revision_cards
Submissions	submissions, aliases and append-only lifecycle events
Experiments	experiments, explicit model/deck/submission links, experiment_observations, type-specific result tables
Training	training_configs, training_runs
Tournament	tournament_configs, tournaments, tournament_participants, matchups, matches
Replay	replay_imports, match_steps, step_options, step_actions, step_events, snapshots, zones, zone cards, field Pokémon, effects and card movements
Ratings	Policies, epochs, submission/model/deck/card ratings and append-only rating events
Provenance	System configuration, leaderboard snapshots, remote submissions/scores and operation receipts

Integrity contract

Every FK is enforced on every connection. Every enum is a domain table with a unique canonical code. Immutable artifacts use content digests and uniqueness constraints. Natural sequence keys prevent duplicate child rows. Historical evidence is append-only; corrections create new revisions or events.

Idempotency

Commands accept an idempotency key or derive one from immutable source identity. operation_receipts records the applied operation. Re-importing a replay, retrying a rating update, or repeating an administrative command returns the original result without a second mutation. All multi-row writes are atomic transactions.

Legacy removal

matchups.replay_html is unused legacy and is not part of the target. Current text agent fields, magic integers and serialized action/options/events are replaced by relational tables.

Related

pokemon_tcg_local_overhaul · pokemon_tcg_local_platform_status · pokemon_tcg_implementation_specification · pokemon_tcg_schema_evolution · pokemon_tcg_replay_data_model · pokemon_tcg_submissions_and_elo