---
type: reference
title: "Pokémon TCG — Physical SQLite Schema"
description: "Target relational schema, integrity rules and idempotent write contract for the local overhaul."
tags: [pokemon-tcg, schema, sqlite, relational, foreign-keys, idempotency]
timestamp: "2026-07-27T23:00:00-03:00"
---

# 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]]