Dylan Matthews

Case study · Data pipeline · private source · hosted schedule unverified

MTG Ingest

A versioned Postgres contract for Magic card data and competitive Commander tournament results.

HTTPX fetches Scryfall and EDHTop16 data. Python normalizes records and resolves Oracle IDs. psycopg and COPY upsert into Postgres; SQL verifies the corpus and publishes read-only mtg_v1 views.
Open the full-size architecture diagram.

The problem

Every Magic tool downstream needs card data, and competitive analysis also needs tournament decklists. Each tool tends to derive these itself, with its own unnoticed errors. This pipeline loads Scryfall card data and EDHTop16 tournament results into Postgres and publishes them through one read-only schema, so every consumer shares a single tested definition. It holds facts with their sources only: no recommendations, simulator conclusions or strategy judgments.

Contribution and AI assistance

The project work includes typed ingestion, schema migrations, consumer permissions, source provenance, rejection records and measured data corrections. Dylan owns the repository. Specific decisions and implementations are not individually attributed in the reviewed records, so this study presents the work and its verification without inventing manual authorship.

How AI contributed

The cloud-alignment plan (work items W1–W7, 2026-09-04) is written as instructions for a coding agent, and the state record describes work done in sessions. Other stages have no file-level attribution, and the tools used are not recorded.

  • Python
  • PostgreSQL 17
  • yoyo migrations
  • Docker
  • GitHub Actions

Two consequential decisions

Decision 1

The schema is the interface

Consumers connect as the mtg_consumer role and read only the mtg_v1 views. That role has no access to the physical tables in mtg_internal, so a query that reaches past the contract fails immediately. Views and grants are reapplied on every migration and compared against a saved schema snapshot in CI. The cost is maintaining a view layer and releasing any breaking change as a new schema version.

Decision 2

Fix data defects additively, and measure them

Paper availability used to come from one arbitrary printing per card, so 254 Reserved List cards and other staples looked digital-only. Migration 0006 derived it from all 117,625 printings instead. 1,062 cards gained paper availability and none lost it, so the fix could ship inside mtg_v1 rather than as a new version. The old value is kept but hidden, so the defect can still be reproduced.

Verification

  • Each query published in the consumer guide is an integration test run as mtg_consumer, and another test checks that the documented SQL matches the tested SQL.
  • EDHTop16 behavior was measured over 311 real decklists. Single-color decks arrive about 9.1 cards short on average, because the source sends no quantities, and an is_complete flag records this.
  • The verify-corpus command checks migrations, checksums, minimum corpus sizes and the consumer permission boundary on a rebuilt database. No run of it on a hosted database was verified for this review.

Outcome and progression

1,062 gained, 0 lost

Cards whose paper availability changed in migration 0006

Measured on 2026-09-03 against Scryfall's file of 117,625 printings. Paper gameplay cards rose from 32,509 to 33,571, and Reserved List cards wrongly marked as digital-only fell from 254 to 0.

2,613 of 2,613

Tournament decklist cards matched by Scryfall's card ID (oracle ID)

EDHTop16 import recorded in the state file in early September 2026, with zero name disagreements. This is a local sample, not a count over the full corpus.

Milestones

  1. 2026-09-01Stages 0–2 and the first live Scryfall run: schema contract, database roles and card loading.
  2. 2026-09-03Migration 0006 derived paper availability from all printings.
  3. 2026-09-04Stage 3 tournament corpus and code for running on a host recorded; scheduled nightly left disabled.

What I would measure next

Verify the current hosted schedule and database before changing either. Rebuild a separate disposable database, run verify-corpus, then collect a month of nightly success rates, durations and rejection counts alongside source-to-corpus freshness.

Explore the evidence

  1. Read the problem and the decision 'The schema is the interface' on this page.
  2. Open the public simulator README section 'Getting this to run' to see a consumer reading mtg_v1 as mtg_consumer.

Evidence and further reading