Data model

The database separates a logical card from a printing and an owned variant. Database numeric IDs are local to a deployment; upstream external IDs are stored separately.

erDiagram
    GAME ||--o{ CARD_SET : contains
    GAME ||--o{ CARD : contains
    CARD ||--o{ PRINTING : has
    CARD_SET ||--o{ PRINTING : includes
    CARD ||--o{ CARD_NAME : names
    PRINTING ||--o{ COLLECTION_ITEM : collected_as
    PRINTING ||--o{ PRICE_SNAPSHOT : priced_as
    USER ||--o{ COLLECTION_ITEM : owns
    COLLECTION_ITEM ||--o{ STOCK_TRANSACTION : records
    USER ||--o{ USER_IDENTITY : signs_in_with
    USER ||--o{ REFRESH_TOKEN : maintains

Loading

Catalog tables

Table	Purpose and important constraints
game	String game ID, display name, last catalog/price sync
card_set	Set metadata; unique (game_id, external_id); printed codes can collide
card	Logical card, canonical name, sort name, colors, JSONB attributes, freshness timestamps; unique game/external ID
printing	Card in a set, number, rarity, artist, finishes, languages, image URLs/paths, attributes; unique set/external ID
card_name	Localized names, optionally scoped to a printing; normalized generated search name and trigram indexes

A printed number does not universally identify a unique printing. Rarity variants can be separate printings; names and images can differ by language/edition.

Ownership and prices

Table	Purpose
collection_item	Unique user/printing/finish/condition/language tuple; quantity ≥0; unit manual price and notes
stock_transaction	User/item, signed delta, source, undone flag and timestamp
price_snapshot	Printing, finish, date and market source; currency and trend/low/average values

Snapshots are unique per (printing_id, finish, date, source). Prices are numeric decimals, not floating-point currency balances. There is no per-item acquisition-cost/transaction-currency model in this revision.

Stock history is not used to reconstruct historical chart quantities; see Prices and valuation.

Accounts and authentication

user stores username, optional local password hash, email, role, disabled state, authentication version, UI language, catalog preferences and ordered price sources. user_identity links issuer/subject to a user; issuer/subject is unique. refresh_token stores a hash, family ID, expiry and revocation/rotation timestamps.

Password changes and disabling users invalidate existing sessions through session-family and authentication-version checks.

Operational tables

Table	Purpose
sync_run	Job game/kind/status/trigger, process owner, heartbeat, cancellation, timestamps, JSON report/error
app_setting	Persisted application settings, including synchronization schedule
catalog_discrepancy	Source disagreements and administrator resolution
catalog_field_rule	Persistent game/source/field preference
api_usage	Per-source daily/monthly request counters
gap_check	Cached results for supplemental-source gap searches

The job table has partial unique indexes for a queued run and a running run per game/kind. These constraints do not imply arbitrary horizontal scaling has been validated.

Deletion and migration

Deleting a collection item cascades its stock history. Deleting a user cascades user-held inventory and related records. Catalog reset deliberately preserves referenced printings according to its service logic. Do not substitute manual SQL deletion for those behaviors.

Use Alembic migrations. Some revisions also relocate image files, so filesystem state and database state need coordinated recovery. Direct database edits bypass normal validation and are not the documented administration interface.

Implementation references

• backend/app/models/catalog.py
• backend/app/models/collection.py
• backend/app/models/user.py
• backend/app/models/sync.py