Files
magik/scripts/build-cards.mjs
masi 14daed4c54 Add market prices to the card index and the deck total
Prices were already in the Scryfall bulk download, so this adds no
dependency and no new fetch: the work is deciding what a card's price
means, given that the index keeps one row per card and a card has many
printings.

It is the median of the ordinary printings. The kept printing underprices
about a third of the legal pool because it is chosen for art and identity
and is usually a cheap reprint; the maximum is worse, since premium and
bundled listings put a $870 Sol Ring in the data. The median lands where a
player recognises the number and ignores the outliers. It is a market
value, not a build cost.

Two filter decisions are deliberate and easy to undo by accident: Reserved
List printings are included, because that is where Commander's banned
Power 9 live and excluding them priced Ancestral Recall and Time Walk at
nothing, while memorabilia is excluded because a Collectors' Edition box is
not a single card. Banned cards fall back to their banned printings.

deckValue counts unpriced cards instead of treating them as free, so a
total is always labelled with what it left out.

Also implements scripts/verify-cards.mjs. package.json and the index skill
both documented `pnpm cards:verify` but the script did not exist; it now
runs in deploy and fails on a card priced at exactly zero, which is the
failure mode the missing-price guard exists to prevent.

Co-Authored-By: Claude Code <noreply@anthropic.com>
2026-10-02 13:17:09 -03:00

486 lines
18 KiB
JavaScript

#!/usr/bin/env node
/**
* Build the Commander-legal card index.
*
* Downloads the Scryfall "Default Cards" bulk JSONL (one object per distinct
* printed card), keeps every card whose `legalities.commander` is `legal`,
* and writes `data/cards.db` — a SQLite file the API serves read-only.
*
* Why bulk at build time rather than the live API:
* - the API forbids browser CORS and rate-limits to ~10 req/s, which a
* faceted card browser cannot live within;
* - Commander legality, colour identity and ban status are all static
* between releases, so there is nothing to re-fetch at runtime.
*
* The Scryfall bulk catalogue is the source of truth for the download URL;
* filenames are versioned and change on every regeneration, so they are
* never hardcoded here.
*
* node scripts/build-cards.mjs [--out data/cards.db] [--all]
*
* --all keep non-legal cards too (diagnostics and format-browsing)
*/
import { createReadStream, createWriteStream, existsSync } from 'node:fs'
import { mkdir, rm, rename, stat } from 'node:fs/promises'
import { createGunzip } from 'node:zlib'
import { Readable } from 'node:stream'
import { pipeline } from 'node:stream/promises'
import { DatabaseSync } from 'node:sqlite'
import { dirname, resolve } from 'node:path'
import { fileURLToPath } from 'node:url'
const ROOT = resolve(dirname(fileURLToPath(import.meta.url)), '..')
const UA = 'magik-commander-builder/1.0 (https://magic.marcospaulo.dev.br)'
const BULK_CATALOG = 'https://api.scryfall.com/bulk-data'
const BULK_TYPE = 'default_cards'
const args = process.argv.slice(2)
const outPath = resolve(ROOT, valueOf('--out') ?? 'data/cards.db')
const keepAll = args.includes('--all')
function valueOf(flag) {
const i = args.indexOf(flag)
return i === -1 ? undefined : args[i + 1]
}
const SCHEMA = `
PRAGMA journal_mode = OFF;
PRAGMA synchronous = OFF;
CREATE TABLE cards (
id TEXT PRIMARY KEY, -- Scryfall id of the chosen printing
oracle_id TEXT NOT NULL, -- stable across printings
name TEXT NOT NULL,
mana_cost TEXT NOT NULL DEFAULT '',
cmc INTEGER NOT NULL DEFAULT 0,
mv_cap INTEGER NOT NULL DEFAULT 0, -- cmc bucket for the 7+ histogram
type_line TEXT NOT NULL DEFAULT '',
oracle_text TEXT NOT NULL DEFAULT '',
color_identity TEXT NOT NULL DEFAULT '',
colors TEXT NOT NULL DEFAULT '',
rarity TEXT NOT NULL DEFAULT '',
power TEXT,
toughness TEXT,
loyalty TEXT,
layout TEXT NOT NULL DEFAULT '',
faces TEXT, -- JSON array for multi-face cards
released_at TEXT NOT NULL DEFAULT '',
artist TEXT NOT NULL DEFAULT '',
set_name TEXT NOT NULL DEFAULT '',
set_code TEXT NOT NULL DEFAULT '',
commander_legality TEXT NOT NULL, -- legal | banned | not_legal
commander_eligible INTEGER NOT NULL DEFAULT 0,
is_partner INTEGER NOT NULL DEFAULT 0,
is_basic_land INTEGER NOT NULL DEFAULT 0,
legal_formats TEXT NOT NULL DEFAULT '', -- JSON map, formats where legal
image_small TEXT, image_normal TEXT, image_large TEXT,
image_png TEXT, image_art_crop TEXT, image_board_crop TEXT,
price_usd REAL -- median market price, see MARKET PRICE below
);
CREATE INDEX idx_cards_name ON cards(name);
CREATE INDEX idx_cards_commander ON cards(commander_eligible);
CREATE INDEX idx_cards_cmc ON cards(cmc);
CREATE INDEX idx_cards_rarity ON cards(rarity);
CREATE INDEX idx_cards_identity ON cards(color_identity);
`
/**
* SQLite has no `LIKE` index, so a search over ~30k rows is a table scan of
* a single narrow table — a few milliseconds. FTS5 is not used: its
* tokenisation hurts name-prefix matching ("sol" must find "Sol Ring"), which
* is the dominant query in a card browser.
*/
async function main() {
const { url, size: expectedSize } = await resolveBulkUrl()
console.log(`bulk: ${url}`)
await mkdir(dirname(outPath), { recursive: true })
const tmpPath = `${outPath}.tmp`
await rm(tmpPath, { force: true })
const db = new DatabaseSync(tmpPath)
db.exec(SCHEMA)
const insert = db.prepare(`
INSERT INTO cards (
id, oracle_id, name, mana_cost, cmc, mv_cap, type_line, oracle_text,
color_identity, colors, rarity, power, toughness, loyalty, layout, faces,
released_at, artist, set_name, set_code, commander_legality,
commander_eligible, is_partner, is_basic_land, legal_formats,
image_small, image_normal, image_large, image_png, image_art_crop, image_board_crop,
price_usd
) VALUES (
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
)
`)
db.exec('BEGIN')
// The bulk file holds one object per card *printing*, and digital-only
// reprints (Alchemy, Vintage Masters) are marked banned in paper Commander.
// Indexing those verbatim would show Ancestral Recall eleven times, all
// "banned", which is a printing-level fact masquerading as a card-level
// one. So printings are collapsed to one row per oracle card, keeping the
// best paper-legal one.
const best = new Map()
const prices = new Map()
const bannedPrices = new Map()
let seen = 0
for await (const card of readBulk(url, expectedSize)) {
seen++
const legality = card.legalities?.commander
if (legality === undefined) continue
if (legality === 'legal' || legality === 'banned') {
const usd = samplePrice(card)
if (usd !== null) {
const oracleId = card.oracle_id ?? card.id
const target = legality === 'legal' ? prices : bannedPrices
let bucket = target.get(oracleId)
if (!bucket) target.set(oracleId, (bucket = []))
bucket.push(usd)
}
}
// Reversible and split cards publish bare faces as their own objects with
// no type line. The front face is indexed under the full card, so these
// would only ever add empty duplicates.
if ((card.type_line ?? '') === '') continue
// Banned cards are kept deliberately: the validator has to be able to
// name the offending card, and the commander picker has to be able to
// explain why a legendary card is unavailable.
if (!keepAll && legality !== 'legal' && legality !== 'banned') continue
const oracleId = card.oracle_id ?? card.id
const candidate = { row: project(card, legality), rank: rankPrinting(card, legality) }
const current = best.get(oracleId)
if (!current || compareRank(candidate.rank, current.rank) > 0) best.set(oracleId, candidate)
if (seen % 20000 === 0) console.log(` ${seen} read · ${best.size} distinct cards`)
}
let kept = 0
for (const [oracleId, { row }] of best) {
// A banned card has no Commander-legal printing, so the legal sample is
// empty and it falls back to its banned printings. The card still has a
// market price, and the index keeps banned cards precisely so the validator
// can name them — pricing them as unknown would be a gap, not a truth.
const price = medianPrice(prices.get(oracleId)) ?? medianPrice(bannedPrices.get(oracleId))
insert.run(...row, price)
kept++
}
db.exec('COMMIT')
db.exec('VACUUM')
db.exec('ANALYZE')
db.close()
await rename(tmpPath, outPath)
const { size } = await stat(outPath)
console.log(`done: ${kept} distinct cards from ${seen} printings -> ${outPath} (${(size / 1e6).toFixed(1)} MB)`)
}
const SET_TYPE_RANK = {
core: 9,
commander: 8,
draft_innovation: 7,
promo: 6,
masters: 5,
expansion: 4,
starter: 3,
limited: 2,
token: 0,
}
/**
* MARKET PRICE
*
* A card has many printings at very different prices, but the index keeps one
* row per card, so a price has to be aggregated rather than copied off the
* kept printing. Neither obvious choice is correct:
*
* - the kept printing is chosen for identity and art (core set, English,
* recent), which is usually a cheap reprint; across the Commander-legal
* pool it underprices about a third of cards
* - the maximum is worse: premium and bundled listings put a $870 Sol Ring
* and a $920 Counterspell in the data, and Limited Edition Alpha forests
* price a basic land at $750
*
* The median of the ordinary printings lands where a player would recognise
* the number — Forest $0.32, Sol Ring $2.10, Mystical Tutor $18.30 — and is
* unmoved by those outliers, which is why it is the median and not the mean.
* It is a market value, not a build cost: the cheapest printing is usually
* well below it, and that is a different question this index does not answer.
*
* Prices are a property of the card, not of the app, so the runtime never
* fetches them; like every other fact here they are a build artifact and go
* stale together with the rest of the index.
*/
/**
* Whether a printing's price is a fair sample of the market.
*
* Excluded: promo and digital-only printings, which are not the card anyone is
* buying; token printings, which are not cards; non-English printings, whose
* USD price tracks a different regional market; and *memorabilia*, which is
* sealed product — a Collectors' Edition box priced as if it were a single
* card is the single largest source of nonsense in this data.
*
* Reserved List printings are deliberately included. It is tempting to exclude
* them as "not openly available", but that is exactly where Commander's banned
* Power 9 live: dropping them left Ancestral Recall, Time Walk and Mox Jet with
* no price at all, when they are among the most actively traded cards in the
* game. They are excluded from reprinting, not from the market.
*/
export function isMarketPrinting(card) {
return (
!card.promo &&
!card.digital &&
card.set_type !== 'token' &&
card.set_type !== 'memorabilia' &&
card.lang === 'en' &&
card.nonfoil !== false
)
}
/**
* A printing's USD price, or null when it contributes nothing to the sample.
*
* The missing-price guard is on the raw value, not the converted one, and that
* distinction is the whole function: Scryfall spells a missing price as null,
* `Number(null)` is 0, and 0 is finite. Converting first and testing the result
* lets a priceless printing into the median as a free card, which is how
* Ancestral Recall, Time Walk and Mox Jet all came out at $0.00 — every one of
* their priced printings surrounded by unpriced ones. A card priced at zero is
* worse than a card priced at nothing.
*/
export function samplePrice(card) {
const raw = card.prices?.usd
if (raw == null || raw === '') return null
const usd = Number(raw)
if (!Number.isFinite(usd)) return null
return isMarketPrinting(card) ? usd : null
}
/** Median of a price list, to the cent. Null when nothing was sampled. */
export function medianPrice(values) {
if (!values?.length) return null
const sorted = [...values].sort((a, b) => a - b)
const mid = sorted.length >> 1
const value = sorted.length % 2 ? sorted[mid] : (sorted[mid - 1] + sorted[mid]) / 2
return Math.round(value * 100) / 100
}
/**
* Orders printings of the same card so the most useful one wins.
*
* Paper legality comes first: Commander is played with paper cards, so a
* printing Scryfall marks banned there is not a ban on the card. Within the
* legal group, prefer a core-set printing in English, then the most recent
* release for its best available art.
*/
function rankPrinting(card, legality) {
return [
legality === 'legal' ? 1 : 0,
card.set_code ? 1 : 0,
card.lang === 'en' ? 1 : 0,
SET_TYPE_RANK[card.set_type] ?? 1,
card.released_at ?? '',
]
}
function compareRank(a, b) {
for (let i = 0; i < a.length; i++) {
if (a[i] !== b[i]) return a[i] > b[i] ? 1 : -1
}
return 0
}
async function resolveBulkUrl() {
const res = await fetch(BULK_CATALOG, { headers: { 'User-Agent': UA, Accept: 'application/json' } })
if (!res.ok) throw new Error(`bulk catalogue ${res.status}`)
const { data } = await res.json()
const entry = data.find((e) => e.type === BULK_TYPE)
if (!entry?.jsonl_download_uri) {
throw new Error(`no "${BULK_TYPE}" entry with a download uri in the bulk catalogue`)
}
return { url: entry.jsonl_download_uri, size: entry.compressed_size }
}
/**
* Yields one parsed card object at a time from the bulk download.
*
* The archive is cached under `data/.cache/` because it is versioned per
* Scryfall release: rebuilding the index after a schema tweak should not
* re-fetch 200 MB.
*
* The cache is size-checked against the bulk catalogue on every use. A
* download interrupted part-way through still un-gzips, still yields valid
* JSON lines for everything before the break, and still produces a plausible
* looking index — one that is silently missing every card Scryfall listed
* after the cut. That happened once here and cost an afternoon: the only
* defence is to compare the byte count against the size Scryfall published.
*
* Lines are split by hand rather than with `readline` so that a transport or
* gunzip error propagates out of the `for await` instead of being swallowed
* by the reader's event emitter.
*/
async function* readBulk(url, expectedSize) {
const cachePath = resolve(ROOT, 'data/.cache', url.split('/').pop())
await mkdir(dirname(cachePath), { recursive: true })
const incomplete = (actual) =>
`${cachePath} is ${actual} bytes, Scryfall says the file is ${expectedSize}; it is a partial download`
let size = existsSync(cachePath) ? (await stat(cachePath)).size : 0
if (size !== 0 && size !== expectedSize) {
await rm(cachePath, { force: true })
size = 0
}
if (size === 0) {
console.log('downloading…')
const res = await fetch(url, { headers: { 'User-Agent': UA } })
if (!res.ok || !res.body) throw new Error(`bulk download ${res.status} ${url}`)
const partial = `${cachePath}.part`
await pipeline(Readable.fromWeb(res.body), createWriteStream(partial))
const { size: written } = await stat(partial)
if (written !== expectedSize) {
await rm(partial, { force: true })
throw new Error(incomplete(written))
}
await rename(partial, cachePath)
} else {
console.log(`using cached ${cachePath}`)
}
const gunzip = createReadStream(cachePath).pipe(createGunzip())
let rest = ''
for await (const chunk of gunzip) {
rest += chunk
let nl
while ((nl = rest.indexOf('\n')) !== -1) {
if (rest.slice(0, nl) !== '') yield JSON.parse(rest.slice(0, nl))
rest = rest.slice(nl + 1)
}
}
if (rest.trim() !== '') yield JSON.parse(rest)
}
/**
* Commander eligibility, per CR 903.3:
* (a) a legendary creature
* (b) a legendary Vehicle
* (c) a legendary Spacecraft with printed power/toughness
* 903.3a — any legendary card whose text says it can be your commander
*
* 903.3a is the awkward one: Scryfall and MTGJSON both strip that sentence
* from the card text they publish, so it cannot be read card by card from any
* accessible dataset. Wizards' own search indexes only the "can be your
* commander" wording and misses the rest, and its Partner filter omits every
* Partner planeswalker.
*
* The two classes that the stripped sentence would otherwise decide are
* therefore taken from the type line, which is correct for all of them:
*
* - Legendary Planeswalkers. Every one printed since the 2017 change that
* made planeswalkers legal commanders carries the ability, and the
* pre-2017 ones were reprinted with it.
* - Legendary Vehicles and Spacecraft, which 903.3b/c admit by type.
*
* What neither type line nor any dataset can supply is the handful of
* legendary artifacts, enchantments, lands and sorceries that lead a deck
* only because of 903.3a. That is a short, closed list, kept in
* DESIGNATED_COMMANDERS below. Add to it when a new set introduces one.
*/
const DESIGNATED_COMMANDERS = new Set([
'Agatha\'s Soul Cauldron',
'Gerrard\'s Hourglass Pendant',
'Instrument of the Bards',
'Lithoform Engine',
'Nyx Lotus',
'Ozolith, the Shattered Spire',
'The Book of Exalted Deeds',
'The Cauldron of Eternity',
'The Earth Crystal',
'The Mightstone and Weakstone',
"Urza's Sylex",
])
/** A Background is a legendary creature but can never lead a deck alone. */
function isCommanderEligible(card) {
const typeLine = card.type_line ?? ''
if (!/legendary/i.test(typeLine)) return false
if (/\bbackground\b/i.test(typeLine)) return false
if (/creature/i.test(typeLine) || /vehicle/i.test(typeLine) || /\bplaneswalker\b/i.test(typeLine)) return true
if (/\bspacecraft\b/i.test(typeLine)) return card.power != null && card.toughness != null
return (
DESIGNATED_COMMANDERS.has((card.name ?? '').split(' // ')[0]) ||
/can be your commander/i.test(card.oracle_text ?? '')
)
}
function project(card, commanderLegality) {
const typeLine = card.type_line ?? ''
const oracleText = card.oracle_text ?? ''
const name = (card.name ?? '').split(' // ')[0]
const formats = []
for (const [format, status] of Object.entries(card.legalities ?? {})) {
if (status === 'legal') formats.push(format)
}
const cmc = Number.isFinite(card.cmc) ? card.cmc : 0
// Scryfall models both of these as arrays; a colourless card has an empty
// one, which must not be bound to a TEXT column.
const identity = (card.color_identity ?? []).join('')
const colors = (card.colors ?? []).join('')
const commanderEligible = isCommanderEligible(card)
return [
card.id,
card.oracle_id ?? card.id,
name,
card.mana_cost ?? '',
cmc,
Math.min(cmc, 7),
typeLine,
oracleText,
identity,
colors,
card.rarity ?? '',
card.power ?? null,
card.toughness ?? null,
card.loyalty ?? null,
card.layout ?? '',
card.faces ? JSON.stringify(card.faces) : null,
card.released_at ?? '',
card.artist ?? '',
card.set_name ?? '',
card.set_code ?? '',
commanderLegality,
commanderEligible ? 1 : 0,
/\bpartner\b/i.test(oracleText) ? 1 : 0,
/^basic (snow )?land\b/i.test(typeLine.trim()) ? 1 : 0,
JSON.stringify(formats),
card.image_uris?.small ?? null,
card.image_uris?.normal ?? null,
card.image_uris?.large ?? null,
card.image_uris?.png ?? null,
card.image_uris?.art_crop ?? null,
card.image_uris?.board_crop ?? null,
]
}
// Only build when run as a script. The price helpers above are imported by the
// test suite, which must not kick off a 75 MB download as a side effect.
if (import.meta.url === `file://${process.argv[1]}`) {
main().catch((err) => {
console.error(err)
process.exitCode = 1
})
}