# 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.