PlayGraph

My role

Solo project. I wrote the back end, the front end, the database migrations, the tests and the CI setup. PlayGraph account sign-in goes through Clerk and game data comes from Steam's Web API; everything else is mine. The full app isn't deployed yet, but the demo runs the real front end on sample data, so you can click around and write reviews.

The problem

The catalog and feed were unusably slow under load. With 3,000 games, 8,000 reviews and 200 simulated visitors, 95% of requests took up to 49 seconds. Every query was working out whether each title was a game or a piece of software by running string functions over every joined row, on every request.

How it works

• Stored each title's game-or-software classification in its own database column, filled in by a migration, so queries filter on a plain column instead of recomputing it every time.
• Cached public pages in Redis. Each cached area has a generation number in its keys, so bumping that number retires every old entry at once instead of hunting them down one by one.
• When several requests miss the cache for the same page at the same moment, they share one database query instead of each running their own.
• Steam syncs run as background jobs on arq, a Python job queue built on Redis, because achievements take one API call per game and a big library takes minutes. The page polls until the job finishes.
• Steam logins are verified with Steam on the server before a session is created. Sessions live in Redis so they can be revoked, and any request that changes data needs a CSRF token tied to the session plus a matching Origin header.
• Rate limits use a sliding window written as a Lua script that runs inside Redis, so nobody gets double their budget right at the edge of a window.
• A review freezes the playtime and achievement numbers from when it was posted. Editing the review later doesn't refresh them.

Trade-offs

• Storing the classification freezes the rules into the database. If I change how titles get classified, I need a new data migration, not just a code change.
• A cached public page can be slightly stale if an invalidation fails, so entries also expire on short timers as a backstop. Private library pages aren't cached at all.
• If Redis goes down, requests that need a session fail instead of skipping the session and rate-limit checks. I'd rather those requests fail than go through unchecked.

What I measured

Local development runs with Locust, a Python load-testing tool: SQLite, 3,000 games, 8,000 reviews, and 200 simulated visitors browsing public pages. p95 is the time 95% of requests finished within. The simulated visitors pause between requests, so these runs don't show maximum throughput, and I haven't measured signed-in traffic or a hosted server yet. Separately, CI runs 116 Python tests and 10 front-end tests, plus pip-audit and bandit security checks, on every push.