mtg-league/CLAUDE.md

MTG league tracker

Small web app for a Magic: The Gathering league that runs its nights on melee.gg. Melee stays the source of truth for pairings and results; this app is a dashboard (leaderboard, session results, deck stats) fed by Melee exports. Hosted at home (Proxmox VM, Docker, Portainer stack deployed from Git), exposed through Caddy on a VPS over Tailscale.

Stack

Node + TypeScript run directly with tsx (no build step), Express 5, server-rendered EJS, Postgres via pg with plain parameterised SQL (no ORM). Tables are created on startup in src/db.ts. There is no migration tool yet: add ALTER TABLE ... IF NOT EXISTS statements to init or introduce a migration tool when the schema changes.

Layout

• src/server.ts routes, auth, security middleware, import handlers
• src/tables.ts pure parsers: Melee matches export, Melee standings export, generic CSV/JSON (no deps)
• src/standings.ts pure leaderboard + deck-stats logic
• src/db.ts pool, tx() helper, schema
• views/*.ejs five pages. Every link/form action is prefixed with <%= base %> (BASE_PATH support)
• test/ npm test (node:test) with anonymised fixtures. npm run check type-checks. test/e2e.sh [sampledata] runs end-to-end checks (imports, decks, hand import, league rename/delete) against scripts/local.sh up.

Rules and decisions

• Scoring: match win 3, draw 1, loss 0, bye = 2-0 win. Session score = sum of that night's match points. Leaderboard = sum of each player's best league.best_n session scores; tiebreak = total of all sessions, then Melee's tiebreakers (OMW%, GW%, OGW% over all the league's match rows; bye = 2-0 win, not an opponent; opponents floored at 1/3; verified identical to Melee on a real night), then name. Standings-only sessions add nothing to the tiebreakers.
• A session with match rows is scored from them; otherwise from imported standings (session_results). Standings imports have W-D-L but no opponents, so the leaderboard shows "–" W-D-L for players with such a session.
• Players are identified by real name (Melee Team.Players[0].Name, falling back to DisplayName, then Username), matched case-insensitively and created on first sight. Spelling differences create duplicate players.
• No top cut will ever be used, so the standings importer takes the last round of the export.
• Decks are free text, one per player per session (Melee exports contain no deck names), edited in one form and kept identical on the player's match rows and standings row; every import fills blanks from the decks already entered. known decks feed a datalist to avoid "Burn"/"burn" duplicates. Deck stats count whole-night results against the deck.
• 1-on-1 matches only. Unreported matches (HasResult false) are skipped.

Melee export shapes (observed)

• Standings export: array of rows, one snapshot per round (RoundNumber), each with Team.Players[], Points, MatchWins/MatchDraws/MatchLosses. Use the final round only.
• Matches export: array of matches with Competitors[] (sorted by SortOrder), GameWinsAndGameByes (always filled; GameWins is null for 0), GameDraws, HasResult, RoundNumber. One round per file; a bye is a single competitor.
• Melee only keeps organiser export data for ~60 days. The data API needs approval from contact@melee.gg, so it was deliberately not used; a future automation could use it (credentials must stay server side).
• Real exports contain personal data (Discord names, DCI numbers) and are gitignored. Use test/fixtures instead.

Security measures already in place

Parameterised SQL only; EJS escaping everywhere; admin-only routes behind admin middleware; strict CSP with a nonce (no inline handlers: use data-confirm); same-origin check on non-GET requests; login rate limit (10 failures / 15 min / IP); startup refuses weak SECRET_KEY (<32) or ADMIN_PASSWORD (<10); container runs as node; port bound to BIND_ADDR. Admin cookie is a stateless signed cookie (logout does not revoke it; rotate SECRET_KEY to invalidate). Leaderboard pages are public and show real names; protect with Caddy basic_auth if that is a concern.

Config (env)

DB_PASSWORD, SECRET_KEY, ADMIN_PASSWORD, COOKIE_SECURE, BIND_ADDR (Tailscale IP of the VM), HOST_PORT (default 8888), BASE_PATH (sub-path like /mtg, no trailing slash; Caddy must use handle_path so it strips the prefix).

Status

Verified locally (2026-10-28): npm run check, npm test, and the compose stack under podman (scripts/local.sh up) with real Melee exports: login, CSRF block, league/session creation, matches + standings import, decks, all pages, API. Hosted at https://derekc.xyz/mtgleague (BASE_PATH=/mtgleague); the user confirmed BASE_PATH and COOKIE_SECURE=true login work behind Caddy, and the public pages' security headers and prefixed links were checked through the proxy.

Ideas for automation

Scripted import of a Melee export directory; a CLI/API-token route for imports instead of the admin form; nightly pg_dump; GitOps redeploy in Portainer; per-round deck changes; merge/rename players; Melee API sync if access is ever granted.