The database layer is built on SQLAlchemy Core (not the ORM) and is defined entirely inDocumentation Index
Fetch the complete documentation index at: https://mintlify.com/invvd/mtg-cheaper-deck/llms.txt
Use this file to discover all available pages before exploring further.
db.py. No models or sessions are used — only a Table object backed by a MetaData instance and a lazily-created engine. The engine is chosen at runtime by reading DATABASE_URL from the environment, which means the same schema and queries work against both SQLite (the default) and PostgreSQL without any code changes.
The cards table
All Scryfall oracle card data is stored in a single table named cards. Each row represents one unique card name across all its printings — identified by Scryfall’s stable oracle_id.
Primary key. Scryfall’s oracle ID — unique per card name, not per individual printing. This ID stays stable even when new editions are released.
The Scryfall ID of the specific printing that Scryfall designated as the oracle representative for this card.
The card’s name. Not nullable. Used for display and for case-insensitive lookups via the
idx_cards_name_lower index.The card’s color identity as a JSON list of color symbols, e.g.
["U", "B"]. Stored as JSON for display; fast filtering is done through color_identity_mask instead.Bitmask encoding of
color_identity. Each color maps to a fixed bit: W=1, U=2, B=4, R=8, G=16. A bitwise AND between a card’s mask and a commander’s mask lets the query engine filter to cards whose color identity is a subset of the commander’s — far faster than parsing the JSON column row by row.The full printed type line, e.g.
"Legendary Creature — Vampire Wizard". Used for type-compatibility filtering when searching for candidates.Converted mana cost (also called mana value). Used to score mana-cost proximity between the original card and a suggested replacement.
List of evergreen keyword abilities as returned by Scryfall, e.g.
["Flying", "Deathtouch"]. Keyword overlap is the highest-weighted component of the similarity score.The card’s rules text. For double-faced cards, the oracle text of each face is joined with
\n//\n so both halves are searchable in a single field.Power value for creature cards. Stored as a string to accommodate non-numeric values such as
"*". Null for non-creatures.Toughness value for creature cards. Also stored as a string for the same reason. Null for non-creatures.
USD price taken from Scryfall’s daily bulk snapshot. Null when Scryfall does not have a price for the card. Updated on every
sync_scryfall.py run.Map of format name to legality string, e.g.
{"commander": "legal", "standard": "not_legal"}. Used in Phase 4 of candidate filtering to ensure suggestions are legal in the deck’s format.EDHREC popularity rank. Lower numbers indicate more popular cards. Null if the card has no EDHREC rank. Used as the fourth component of the similarity score.
Scryfall’s
released_at date for the oracle representative printing, stored as a string. Acts as a lightweight sync timestamp.Indexes
Three indexes are defined alongside the table to accelerate the most common query patterns:| Index | Column | Used by |
|---|---|---|
idx_cards_name_lower | lower(name) | matching.py — case-insensitive card name lookups |
idx_cards_price | price_usd | scoring.py — price range filtering for candidate search |
idx_cards_color_mask | color_identity_mask | scoring.py — bitmask color identity subset filtering |
Initializing the schema
Runningdb.py directly (or calling db.init_db() from any script) executes metadata.create_all() against the configured engine. For SQLite, it also ensures the data/ directory exists before attempting to create the file. The operation is idempotent — running it on an already-initialized database is safe.
Upsert strategy
During a sync, cards are written using anINSERT ... ON CONFLICT DO UPDATE statement built by sync_scryfall.py’s build_upsert_stmt() function. When a card already exists in the database, all columns except oracle_id are overwritten with the latest values from the Scryfall bulk file. The upsert statement is constructed using the dialect-specific insert from either sqlalchemy.dialects.sqlite or sqlalchemy.dialects.postgresql, but both expose the same .on_conflict_do_update() API, so no branching logic is needed beyond selecting the right import.