WikifitaGitHub live67e8de5
pesquisa · kaggle/pokemon_tcg_sqlite_schema_current

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

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.

Baixar raw

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 metricValue
matches / match_steps139,783 / 3,065,706
match_participants / step_options276,046 / 12,831,700
step_events / board_snapshots15,318,730 / 6,028,402
pokemon_on_field / match_card_usage19,067,881 / 6,106,603
agents / decks1,117 / 948
days / datasets30 / 30
agent_elo_daily / deck_elo_daily28,745 / 10,742
tournaments / matchups128 / 1,621
direct source split138,023 remote / 1,760 local matches
operation_receipts138,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

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):

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:

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:

codedescription
remoteRemote Kaggle replay observation
localLocal 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:

codedescription
replayExact composition observed in a remote replay
arenaExact composition observed in the local arena
starterBundled starter deck
submissionDeck extracted from a submission artifact
public_agentDeck distributed with a public agent
builderDeck authored in the local dashboard
customUser-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

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:

daterowsaux_targetsschema
2026-08-01813,12413
2026-07-31810,57313
2026-07-30812,76513
2026-07-29811,53913
2026-07-28813,07913

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

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

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

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