cardboarddex: engineering a pokémon tcg market data platform
· 7 min read · case study, backend engineering, data engineering, fastapi, postgresql
CardboardDex prices Pokémon cards. It merges catalog and price data from a paid API with active eBay listings, so collectors can see what a card is worth, where it's trending and whether grading it pays off.
- Role: Solo: product, data pipeline, backend, frontend, infrastructure and deployment.
- Stack: Python, FastAPI, SQLAlchemy, PostgreSQL, Celery, Redis, TypeScript, Next.js, React, Tailwind CSS, Docker, AWS Lightsail, Cloudflare Tunnel/Workers/R2, GitHub Actions.
- Result: ~55,000 cards and 430K+ price observations on one 1 GB server. The catalog query went from 4.0–4.6 s (and timeouts) to 242–493 ms.
- When: 2026, live at cardboarddex.app.
1. The problem
Three data sources, none of which can be trusted on its own:
| Source | What it gives me | What goes wrong |
|---|---|---|
| Pokémon Price Tracker (PPT) | Catalog, market quotes, graded sales | 20,000 credits a day, then nothing |
| eBay Browse API | Active listings: asking prices | Proxies, bundles, wrong grades, look-alike cards |
| tcgapi.dev (retired) | The history I started with | Lost if the provider switch is done carelessly |
And all of it on one 1 GB Lightsail box:
┌──────────────── AWS Lightsail, 1 GB RAM ────────────────┐
│ PostgreSQL Redis (coordination) Redis (cache) │
│ FastAPI (capped at 160 MiB) Celery worker Beat │
└─────────────────────────────────────────────────────────┘
2. Approach
One rule shaped everything: page requests never call a provider.
BACKGROUND, on a schedule EVERY PAGE VIEW
───────────────────────── ───────────────
PPT API eBay API Browser
│ │ │
Redis quota Worker relay Next.js
(Lua script) │ │
└──────┬─────────┘ FastAPI (reads only)
v │
Celery workers │
validate · match · rank │
│ │
v v
PostgreSQL prices, history ─────> read
Redis analytics snapshots ──> read, or 503 + Retry-After: 60
Expensive analytics (market movers, trending, grading profit) are rebuilt by workers on a schedule. If a snapshot is missing, the API says "try again in a minute" instead of computing it inside the request.
Turning eBay listings into evidence. A search result containing "Charizard" isn't necessarily that Charizard. Every raw listing is saved, but it only becomes a price observation after it passes the matcher:
eBay search result ──> saved raw, always
│
v
identity: name · set · card number ──────> wrong card ✗
│
v
exclusions: proxy · lot · sealed · lang ──> "lot 50 cards" ✗
│
v
grade + condition ────────────────────────> "PSA 10?" ✗
│
v
asking-price observation ✓
│
v
median of ≤30 matched asks, last 30 days, outliers cut
Run in strict mode against Charizard, Base Set, 4/102:
| Listing title | Result |
|---|---|
Charizard 4/102 Base Set PSA 10 GEM MINT |
✓ PSA 10, confidence 0.95 |
Charizard 4/102 Base Set Holo NM |
✓ Near Mint, holo, confidence 0.90 |
Charizard PSA 10 proxy 4/102 Base Set |
✗ proxy |
Charizard Base Set 4/102 PSA 10? |
✗ uncertain grade |
Pokemon lot 50 cards Charizard Base Set 4/102 |
✗ bundle |
Charizard 4/102 Base Set Japanese |
✗ language |
Switching providers without rewriting history. Relabeling old tcgapi.dev rows as PPT would have faked where the data came from. Instead, each series records its own cutover:
time ─────────────────────────────────────────────────>
tcgapi.dev o───o────o───o───┤
PPT ├───o────o──────o───o
cutover, recorded per card,
printing, condition and metric
• stored rows keep their original provider and date
• gaps stay gaps: no invented observations
• ambiguous mappings stay separate series
Before going live, I restored a backup off the server and rebuilt 54,996 price projections with zero mismatches, with transitions seeded for 933 products.
3. What broke, and how I fixed it
The budget was gone by 4 a.m.
The live feed went quiet every day. The scheduler was spending all 20,000 PPT credits in the first four hours:
UTC 00 06 12 18 24
|-----|-----|-----|-----|
before ████···················· all 20,000 gone by 04:00
after ▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆▆·· paced through ~22:00
Fix: no request reaches PPT without first reserving its credits through one atomic Redis Lua script:
worker wants to call PPT
│
v
Lua: budget · lane · per-minute · cooldown · in-flight · graded · pacing
│ │
allowed: reserve credits blocked: log why, defer
│
v
call PPT ──> 429 cooldown
──> 401/403 pause provider 24 h
──> 200 reconcile real cost from response headers
Each lane gets a fixed share of the day:
rotation ██████████████████████████ 13,000 65.0%
priority █████████ 4,500 22.5%
discovery ███ 1,500 7.5%
graded █ 500 2.5%
retry █ 500 2.5%
1 block = 500 credits, 20,000 a day
A query running into its own deadline
The catalog page threw recurring 503s. Its default sort over ~55,000 cards ran right up against the 5 s SQL deadline:
0s 1s 2s 3s 4s 5s
|---------|---------|---------|---------|---------|
before ████████████████████████████████████████░░░░░░ 4.0–4.6 s
after ██░░░ 242–493 ms
SQL deadline at 5 s; 1 block = 0.1 s; ░ = range
Fix: I didn't raise the deadline or upgrade the server. I materialized the selected primary-price marker and built a partial PostgreSQL index for the default sort. A synthetic 55K-card benchmark confirmed it: the default sort went from 71 ms to 2 ms and the number sort from 215 ms to 38 ms, with identical results in all nine test cases.
An "index-only" scan that wasn't
The live-stats endpoint slowed to 5.66 s. The query plan said the database was reading only an index, but the buffers said otherwise:
live stats: 5.66 s
│
v
EXPLAIN (ANALYZE, BUFFERS)
│
v
Index Only Scan ... Heap Fetches: 59069 ← it's reading the table anyway
│
v
visibility map stale: autovacuum behind on an insert-heavy table
│
v
VACUUM (ANALYZE) ──> 0.99 s
│
v
keep it there: insert-triggered autovacuum,
stop recounting frozen legacy totals
before ████████████████████████████ 5.66 s 59,069 heap fetches
after █████ 0.99 s after VACUUM (ANALYZE)
1 block = 0.2 s
A monitor that cried wolf, and a pause that didn't lift
On a 1 GB box, a little iowait is normal. My alarm sat right inside the normal range:
iowait 0% 10% 20% 30%
|---------|---------|---------|
normal ▓▓▓▓ 9–12%, a normal day
old alarm ^ >10%: fires on a normal day
new alarm ^ >25% for 15 min
Memory and swap alarms stayed strict.
The opposite failure was quieter:
release ──> /health OK ──> deploy looks green
│
old ingestion.paused marker still set
│
v
workers idle, no new prices
Since then, worker health and data freshness are checked separately after every release, not inferred from the deploy.
4. Results
| Before | After | |
|---|---|---|
| Catalog query (production) | 4.0–4.6 s | 242–493 ms |
| Live-stats query | 5.66 s | 0.99 s |
| PPT credits | gone by 04:00 UTC | paced to ~22:00 UTC |
| History across the provider switch | at risk | 54,996 projections, 0 mismatches |
- ~55,000 cards (55,013 verified Oct. 23, 2026) and 430K+ price observations, all on the same 1 GB host.
- PPT has been the primary provider since the Oct. 19 release.
- 446 backend and 188 frontend tests passing (Oct. 26).
The last deploy covered here is Oct. 23 (b90ff28). The data-trust, SEO and UI work from Oct. 24–26 is written but not yet released.
5. Next steps
- Fix two known matcher bugs, the
TG/GGgallery-number regex and overly broad lot detection, with regression tests. - Fix the grading expected-value formula. A code comment describes a 5% raw-floor term that the code doesn't include.
- Trust forwarding headers only from known proxies, and make the admin comparison constant-time.
- Gate database migrations in the release script, and add the Cloudflare Cache Rule so the edge-cache headers actually take effect.
- Add frontend end-to-end tests.
What I learned
I set out to build a pricing site and spent most of my time on the data pipeline. The fixes that worked all came from measuring the actual failure: profiling the query instead of raising its timeout, counting heap fetches instead of blaming the CPU, reserving credits before spending them instead of reacting after they ran out. And a green deploy isn't the same as a working system.