Files
magik/server/db.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

268 lines
9.0 KiB
JavaScript

/**
* Read-only card index access.
*
* Everything the browser needs to browse and filter Commander-legal cards is
* answered from `data/cards.db`. The database is built by
* `scripts/build-cards.mjs` and is never written to at runtime.
*/
import { DatabaseSync } from 'node:sqlite'
import { existsSync } from 'node:fs'
import { resolve, dirname } from 'node:path'
import { fileURLToPath } from 'node:url'
const ROOT = resolve(dirname(fileURLToPath(import.meta.url)), '..')
const DB_PATH = process.env.CARDS_DB ?? resolve(ROOT, 'data/cards.db')
let db = null
export function cards() {
if (!db) {
if (!existsSync(DB_PATH)) {
throw new Error(
`card index missing at ${DB_PATH} — run \`pnpm cards:build\` to create it`,
)
}
db = new DatabaseSync(DB_PATH, { readOnly: true })
}
return db
}
const CARD_COLUMNS = `
id, name, mana_cost, cmc, 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
`
/** Shapes a database row into the JSON the client consumes. */
function hydrate(row) {
if (!row) return null
return {
id: row.id,
name: row.name,
manaCost: row.mana_cost,
cmc: row.cmc,
typeLine: row.type_line,
oracleText: row.oracle_text,
colorIdentity: row.color_identity,
colors: row.colors,
rarity: row.rarity,
power: row.power,
toughness: row.toughness,
loyalty: row.loyalty,
faces: row.faces ? JSON.parse(row.faces) : undefined,
releasedAt: row.released_at,
artist: row.artist,
setName: row.set_name,
setCode: row.set_code,
bannedInCommander: row.commander_legality === 'banned',
commanderEligible: row.commander_eligible === 1,
isPartner: row.is_partner === 1,
isBasicLand: row.is_basic_land === 1,
legalFormats: JSON.parse(row.legal_formats),
image: {
small: row.image_small,
normal: row.image_normal,
large: row.image_large,
png: row.image_png,
artCrop: row.image_art_crop,
boardCrop: row.image_board_crop,
},
// Null when the card has no market price in the index. Callers must render
// that as "no price", never as $0.00.
priceUsd: row.price_usd,
}
}
const SORTS = {
name: 'name COLLATE NOCASE ASC',
cmc: 'cmc ASC, name COLLATE NOCASE ASC',
'-cmc': 'cmc DESC, name COLLATE NOCASE ASC',
'-name': 'name COLLATE NOCASE DESC',
released: 'released_at DESC, name COLLATE NOCASE ASC',
'-released': 'released_at ASC, name COLLATE NOCASE ASC',
rarity: `CASE rarity
WHEN 'mythic' THEN 0 WHEN 'rare' THEN 1 WHEN 'uncommon' THEN 2 ELSE 3 END,
name COLLATE NOCASE ASC`,
}
const RARITIES = new Set(['common', 'uncommon', 'rare', 'mythic'])
const SUPER_TYPES = new Set(['creature', 'planeswalker', 'artifact', 'enchantment', 'land', 'battle'])
const TYPES = new Set(['instant', 'sorcery', 'adventure', 'tribal', 'conspiracy', 'phenomenon', 'plane', 'scheme', 'vanguard', 'conspiracy'])
const COLORS = new Set(['W', 'U', 'B', 'R', 'G'])
const LEGACIES = new Set(['legal', 'banned', 'not_legal'])
/** Escapes a user string for use inside a LIKE pattern. */
function escapeLike(text) {
return text.replace(/[\\%_]/g, (c) => `\\${c}`)
}
function likePattern(text) {
return `%${escapeLike(text)}%`
}
/**
* Translates validated query parameters into a WHERE clause.
*
* Every value reaching SQL goes through this function, so parameter binding
* stays the only place user data meets the statement.
*/
function buildFilters(q) {
const where = []
const params = []
// An exact name beats a name prefix, which beats a substring or rules-text
// hit, so typing "sol" puts Sol Ring first rather than the alphabetically
// earlier Sol Grail or the "Absolutes" a substring search would surface.
let rank = null
let rankParams = []
const text = (q.get('q') ?? '').trim()
if (text !== '') {
where.push(`(
name LIKE ? ESCAPE '\\' COLLATE NOCASE
OR name LIKE ? ESCAPE '\\' COLLATE NOCASE
OR oracle_text LIKE ? ESCAPE '\\' COLLATE NOCASE
)`)
params.push(`${escapeLike(text)}%`, likePattern(text), likePattern(text))
rank = `CASE WHEN name = ? COLLATE NOCASE THEN 0
WHEN name LIKE ? ESCAPE '\\' COLLATE NOCASE THEN 1
WHEN name LIKE ? ESCAPE '\\' COLLATE NOCASE THEN 2
ELSE 3 END`
rankParams = [text, `${escapeLike(text)}%`, likePattern(text)]
}
const colors = (q.get('colors') ?? '').toUpperCase().split('').filter((c) => COLORS.has(c))
if (colors.length > 0) {
// A card is a match only if it uses no colour outside the selection:
// that is the colour-identity containment a Commander deck needs.
for (const c of 'WUBRG') {
if (colors.includes(c)) continue
where.push(`(color_identity = '' OR instr(color_identity, ?) = 0)`)
params.push(c)
}
}
const supertypes = (q.get('type') ?? '').toLowerCase().split('+').filter(Boolean)
for (const t of supertypes) {
if (SUPER_TYPES.has(t)) {
where.push(`instr(lower(type_line), ?) > 0`)
params.push(t)
}
}
const types = (q.get('subtype') ?? '').toLowerCase().split('+').filter(Boolean)
for (const t of types) {
if (TYPES.has(t)) {
where.push(`instr(lower(type_line), ?) > 0`)
params.push(t)
}
}
const min = Number.parseInt(q.get('cmcMin') ?? '', 10)
if (Number.isFinite(min)) {
where.push('cmc >= ?')
params.push(min)
}
const max = Number.parseInt(q.get('cmcMax') ?? '', 10)
if (Number.isFinite(max)) {
where.push('cmc <= ?')
params.push(max)
}
const rarities = (q.get('rarity') ?? '').toLowerCase().split('+').filter((r) => RARITIES.has(r))
for (const r of rarities) {
where.push('rarity = ?')
params.push(r)
}
const identity = (q.get('identity') ?? '').toUpperCase().split('').filter((c) => COLORS.has(c))
if (identity.length > 0) {
for (const c of 'WUBRG') {
if (identity.includes(c)) continue
where.push(`(color_identity = '' OR instr(color_identity, ?) = 0)`)
params.push(c)
}
}
if (q.get('commander') === '1') where.push('commander_eligible = 1')
if (q.get('partners') === '1') where.push('is_partner = 1')
if (q.get('basic') === '1') where.push('is_basic_land = 1')
// Commander's ban list is small but non-empty, and those cards are
// otherwise indistinguishable in a browser. Default to the legal pool;
// callers opt into `legality=banned` when they want to explain one.
const legality = (q.get('legality') ?? 'legal').toLowerCase()
if (LEGACIES.has(legality)) {
where.push('commander_legality = ?')
params.push(legality)
}
return { sql: where.length ? `WHERE ${where.join(' AND ')}` : '', params, rank, rankParams }
}
export function searchCards(q) {
const { sql: where, params, rank, rankParams } = buildFilters(q)
const limit = Math.min(Math.max(Number.parseInt(q.get('limit') ?? '60', 10) || 60, 1), 200)
const page = Math.max(Number.parseInt(q.get('page') ?? '1', 10) || 1, 1)
const sort = SORTS[q.get('sort') ?? ''] ?? SORTS.name
// Relevance is a prefix on the ordering, not a replacement for it: an
// explicit sort choice still decides the order within each rank.
const order = rank === null ? sort : `${rank}, ${sort}`
const total = cards().prepare(`SELECT count(*) AS n FROM cards ${where}`).get(...params).n
const rows = cards()
.prepare(`SELECT ${CARD_COLUMNS} FROM cards ${where} ORDER BY ${order} LIMIT ? OFFSET ?`)
.all(...params, ...rankParams, limit, (page - 1) * limit)
return { total, page, limit, pages: Math.ceil(total / limit), cards: rows.map(hydrate) }
}
export function getCard(id) {
return hydrate(
cards()
.prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE id = ?`)
.get(id) ?? null,
)
}
export function getCardsByIds(ids) {
if (ids.length === 0) return []
const placeholders = ids.map(() => '?').join(',')
const rows = cards()
.prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE id IN (${placeholders})`)
.all(...ids)
const byId = new Map(rows.map((r) => [r.id, hydrate(r)]))
return ids.map((id) => byId.get(id)).filter(Boolean)
}
/**
* Resolves exact card names to records.
*
* Imported decklists and share links carry names, not ids; this turns them
* into real cards in one round trip instead of one search per card.
*/
export function getCardsByNames(names) {
const unique = [...new Set(names.filter((n) => typeof n === 'string' && n !== ''))]
if (unique.length === 0) return []
if (unique.length > 400) unique.length = 400
const placeholders = unique.map(() => '?').join(',')
const rows = cards()
.prepare(`SELECT ${CARD_COLUMNS} FROM cards WHERE name IN (${placeholders})`)
.all(...unique)
const byName = new Map(rows.map((r) => [r.name, hydrate(r)]))
return {
cards: unique.map((name) => byName.get(name)).filter(Boolean),
missing: unique.filter((name) => !byName.has(name)),
}
}
export function cardCount() {
return cards().prepare('SELECT count(*) AS n FROM cards').get().n
}