---
type: reference
title: "Pokémon TCG AI Battle — SQLite Schema (current, 2.0.0)"
description: "Live SQLite schema at model/results.db: day-partitioned Elo tables, source enum, first-class agents/decks/datasets/matches, current populated counts, and how the trainer/tournament read from it."
tags: [pokemon-tcg, sqlite, schema, elo, catalog, day-partitioned, source, datasets]
timestamp: "2026-08-15T16:31:30-03:00"
---

# Pokémon TCG AI Battle — SQLite Schema (current)

## August 15 read-only revalidation

The native `ResultsDB` API was queried again on August 15 without network
synchronization, imports, purges or schema writes. It reproduced the August
14 population and confirms that the database was not rebuilt as part of this
documentation pass:

| Table or metric | Value |
|---|---:|
| `matches` / `match_steps` | 139,783 / 3,065,706 |
| `match_participants` / `step_options` | 276,046 / 12,831,700 |
| `step_events` / `board_snapshots` | 15,318,730 / 6,028,402 |
| `pokemon_on_field` / `match_card_usage` | 19,067,881 / 6,106,603 |
| `agents` / `decks` | 1,117 / 948 |
| `days` / `datasets` | 30 / 30 |
| `agent_elo_daily` / `deck_elo_daily` | 28,745 / 10,742 |
| `tournaments` / `matchups` | 128 / 1,621 |
| direct source split | 138,023 remote / 1,760 local matches |
| `operation_receipts` | 138,151 |

The current local invariant-Elo probe reports deck `633` at 412.21 over 420
games and deck `251` at 577.39 over 420 games; both are marked MD10 complete.
The latest tournament ledger rows include stage-1 through stage-4 FP32 runs
at 13.81%–17.14% win rate, an earlier four-stage 12.38%–12.75% cohort, and
`first_sub` rows at 51.00% and 64.35%. These rows are observations of the
stored ledger, not a claim that all runs used one continuous database state.
See [[pokemon_tcg_aug14_data_etl_database_audit]],
[[pokemon_tcg_aug14_ablations_tournaments]] and
[[pokemon_tcg_current_state_reconciliation]] for cohort and reset-boundary
interpretation.

## August 14 live snapshot

The original populated table below is retained as the August 7 historical snapshot. A new read-only audit on August 14 found Schema 2.0.0 with 30 days and 30 datasets, 1,117 agents, 948 decks, 139,783 matches, 3,065,706 match steps, 28,745 daily agent-Elo rows, 10,742 daily deck-Elo rows, 138,151 operation receipts, 128 tournament summaries and 1,621 matchup summaries. The source split is 138,023 remote matches and 1,760 local matches.

The current Parquet catalog has 24,177,852 decision rows across 30 90-column files. All 30 `days` rows currently report `is_complete=0`; several declared match counts and source metadata do not match the physical ZIP and imported-match observations. A read-only foreign-key check reports 2,946,336 violations. No purge or rebuild was executed. Full evidence and the unresolved archive-count delta are recorded in [[pokemon_tcg_aug14_data_etl_database_audit]].

The `tournaments` table is persistent in the current snapshot, with IDs 1 through 128. The presence of the ledger does not establish whether underlying local replay rows were reset or selectively retained between tournament runs.

## Boundary

This page documents the schema **actually present** in `model/results.db` as of the dated snapshots above (`schema_metadata.schema_version = '2.0.0'`). The design-time target and integrity narrative live in [[pokemon_tcg_physical_schema]]; this page is the evidence-based snapshot of what is populated and how the trainer and tournament read from it.

The Antigravity brain artifact `schema_mermaid.md` is a useful visual map of
the table families and foreign-key topology. It is a structural handoff
artifact, described there as a legacy schema diagram, not a current population
or integrity report. The current counts, source split and FK result on this
page come from the native `ResultsDB` probe and the read-only audit. The master
handoff's phrase “100% disk parity” is therefore retained as a source claim,
not substituted for the observed 2,946,336 FK violations and unresolved
archive/database count delta.

Terminology (`day`, `source='remote'`, `datasets`, `aux_targets`, `is_self`) is defined in [[pokemon_tcg_glossary]].

## Tables and population at the August 7 snapshot

```text
schema_metadata.schema_version = 2.0.0
schema_metadata.created_at     = 2026-08-03 20:11:17

agents                    551
cards                   1,267
days                       22
decks                     552
deck_cards             11,025
matches                98,175
match_steps         2,341,014
submissions             3,179
datasets                   19       (one row per day-partitioned Parquet file)
agent_elo_daily         6,552       (agents × days × sources)
deck_elo_daily          4,349       (decks × days × sources)
card_elo_daily          5,159       (cards × days × sources)
data_sources                2       (remote, local)
deck_sources                7       (replay, arena, starter, submission, public_agent, builder, custom)
```

Additional tables present but less operationally central to the training/tournament loop: `board_snapshots`, `match_card_usage`, `match_participants`, `match_steps`, `matchups`, `meta_features_daily`, `operation_receipts`, `pokemon_on_field`, `step_events`, `step_options`, `submission_decks`, `submissions`, `teams`, `tournaments`.

## Day-partitioning contract

Every Elo table is day-partitioned. The primary key is `(entity_id, day_id, source)`:

```text
agent_elo_daily(agent_id, day_id, source, elo, games_played, wins, losses, draws, computed_at)
deck_elo_daily(deck_id, day_id, source, elo, games_played, wins, losses, draws, computed_at)
card_elo_daily(card_id, day_id, source, elo, games_played, exposure, wins, losses, draws, computed_at)
```

`day_id` is a FK into the `days` table (one row per calendar date, with `is_complete`, `n_matches`, `source_zip`, `source_sha256`, `competition_day`). `source` is FK to `data_sources.code` (values `remote` or `local`).

Two backwards-compat views survive from schema 1:

```text
card_elo AS SELECT ... FROM card_elo_daily WHERE day_id = <latest>
deck_elo AS SELECT ... FROM deck_elo_daily WHERE day_id = <latest>
```

They return the most recent snapshot per (entity, source), keeping older callers alive without a rewrite.

## `data_sources` — the source enum

Only two source codes exist and both are populated:

| code | description |
|---|---|
| `remote` | Remote Kaggle replay observation |
| `local`  | Local synchronous arena observation |

Every Elo row commits to exactly one source. The trainer's `--top-elo` filter reads only `source='remote'` because that is what has coverage; `--sweep-source remote` in tournaments does the same. Local Elo requires that a local tournament has been played and imported, which currently is a low-signal population.

## `deck_sources` — deck provenance codes

Seven codes explaining where a deck row came from:

| code | description |
|---|---|
| `replay` | Exact composition observed in a remote replay |
| `arena` | Exact composition observed in the local arena |
| `starter` | Bundled starter deck |
| `submission` | Deck extracted from a submission artifact |
| `public_agent` | Deck distributed with a public agent |
| `builder` | Deck authored in the local dashboard |
| `custom` | User-authored deck |

The August 7 historical snapshot held 552 decks. The August 15 revalidation
holds 948 decks, still dominated by `replay` (imported from Kaggle replays)
with `submission`, `public_agent`, `starter` and `arena` filling in the fixed
roster. Deck counts are snapshot values and should be paired with the database
date when comparing tournament cohorts.

## `datasets` — the training-corpus catalog

```text
datasets(
    id, day_id, path, schema_version, rows, sha256, aux_targets, created_at
)
```

One row per day-partitioned Parquet. `path` is repo-relative. `schema_version = 3` (the Parquet writer schema; separate from the SQLite schema version). `aux_targets = 1` iff the writer emitted the four aux columns. Sample:

| date | rows | aux_targets | schema |
|---|---|---|---|
| 2026-08-01 | 813,124 | 1 | 3 |
| 2026-07-31 | 810,573 | 1 | 3 |
| 2026-07-30 | 812,765 | 1 | 3 |
| 2026-07-29 | 811,539 | 1 | 3 |
| 2026-07-28 | 813,079 | 1 | 3 |

The trainer resolves `--days` / `--last-n-days` / `--all-days` by looking up `datasets` joined against `days` and refuses to start if the resolved list is empty or if any resolved Parquet is missing on disk. Full Parquet layout in [[pokemon_tcg_parquet_dataset]].

## `agents` and `agent_elo_daily`

```text
agents(id, name, kaggle_username, team_id, submission_ref, is_self,
       first_seen_day, last_seen_day, created_at)
```

`is_self=1` marks agents whose name matches one of `bc_self_aliases` from the config (currently `["FitaLabs", "Alef Oliveira", "Fitalabs", "aleffita"]`). Used by the dataset builder to tag the `is_self` row column; not consumed as a model feature or loss target.

At the August 7 historical snapshot, `agent_elo_daily` had 6,552 rows across
551 agents and about 22 days. The August 15 revalidation has 28,745 rows,
1,117 agents and 30 days. In either snapshot, the trainer's `--top-elo N`
filter selects episodes whose both `player_name` and `opponent_name` belong to
the top-N agents on that day by `agent_elo_daily.elo WHERE source='remote'`.
See [[pokemon_tcg_top_elo_curriculum_filter]].

## `decks`, `deck_cards`, `deck_elo_daily`

```text
decks(id, fingerprint, name, source, archetype, card_count, created_at)
deck_cards(deck_id, card_id, quantity)
```

`fingerprint` is a SHA256 of the sorted card multiset — makes deduplication trivial. `card_count` is a `CHECK`-enforced positive integer; standard Pokémon TCG decks are 60.

At the August 7 snapshot, `deck_elo_daily` held 4,349 rows; the August 15
revalidation has 10,742. It remains the source of truth for `tournament.py
--sweep-source {remote,local}`. `_get_test_decks(db, source='remote')` walks
the top-N decks by Elo on the latest day where `deck_elo_daily.source ==
source`, resolves their composition via `deck_cards`, and returns 3 decks
(default + top 2 unique by 90%-similarity dedup) for the OUR-agent sweep. See
[[pokemon_tcg_tournament_system]].

## `cards`, `card_elo_daily`

```text
cards(id, name, category, stage, hp, energy_type, weakness, rule,
      metadata_complete)
```

`metadata_complete=1` when the row has been reconciled against
`EN_Card_Data.csv`. The current revalidation has 1,267 cards and 9,350
`card_elo_daily` rows. Card-level Elo is not currently a curriculum axis.

## `matches` and `match_steps`

At the August 7 snapshot, `matches` had 98,175 rows and `match_steps` had
2,341,014 rows. This population is retained for historical comparison. The
August 15 revalidation has 139,783 matches and 3,065,706 match steps, as listed
above and audited in [[pokemon_tcg_aug14_data_etl_database_audit]]. Each match
is (match_id, day_id, seat0_agent, seat1_agent, ...). Each step is (match_id,
step_num, player_idx, turn, select_type, select_context, n_options, action,
status, reward).

This population feeds the parquet writer, the daily Elo compute (`compute_agent_elo`, `compute_deck_elo`, `compute_card_elo`), and the dashboard.

## Reads by pipeline stage

- **Dataset builder** — writes to `days`, `datasets`, `agents`, `decks`, `deck_cards`, `matches`, `match_steps`.
- **Elo compute** — writes to `agent_elo_daily`, `deck_elo_daily`, `card_elo_daily` per source. Called with `compute_all(source='remote'|'local')`.
- **Trainer (`bc_train_mlx.py`)** — reads `datasets ⋈ days` to resolve training days; reads `agent_elo_daily WHERE source='remote'` when `--top-elo N` is set; reads `days.competition_day` for the `day_index_norm` scalar feature.
- **Tournament (`scripts/tournament.py`)** — reads `deck_elo_daily WHERE source=--sweep-source` for the sweep; writes match/step rows for each played game so local Elo builds up over time.
- **Dashboard** — reads everything; see [[pokemon_tcg_dashboard_and_tournament]].

## Provenance / anchor commits

- `033e64c` (2026-07-26) — fix: tournament identifies deck_ids and saves card_usage for local matches — established the write path from tournaments to `decks`, `deck_cards`, `match_card_usage`.
- `c695840` (2026-07-26) — fix: card Elo computation filters by source — enforced the `source='remote' | 'local'` split; ended remote/local mixing in Elo rows.
- `15bf88e` (2026-07-26) — fix: INITIAL_ELO changed from 1000 to 600 — the Elo tables now bootstrap at 600, matching the Kaggle ladder floor.
- `5beaac9` (2026-07-29) — rebuild results database from normalized replay evidence — schema 2.0.0 shape (day-partitioned Elo, source enum, first-class agents/decks/datasets) reached its live form.
- `b27d2ba` (2026-07-29) — stop tracking local results database — repo does not carry `model/results.db`; it is per-machine state.

Full timeline in [[pokemon_tcg_repository_timeline]].

## Cross-references

- [[pokemon_tcg_physical_schema]] — design-time schema, constraints, idempotency contract
- [[pokemon_tcg_glossary]] — `day`, `source='remote'`, `datasets`, `aux_targets` definitions
- [[pokemon_tcg_parquet_dataset]] — the Parquet files `datasets.path` points to
- [[pokemon_tcg_top_elo_curriculum_filter]] — how `--top-elo` reads `agent_elo_daily`
- [[pokemon_tcg_tournament_system]] — how `deck_elo_daily` powers the sweep
- [[pokemon_tcg_training_pipeline]] — the trainer's day resolution flow