pawnbase

A chess.com → Postgres ingester that stores your live-mode (rapid / blitz / bullet) games as one normalized row per game, so you can slice your chess history with plain SQL — in Grafana, psql, or anything that speaks Postgres.

pawnbase polls the chess.com public API on its own slow schedule and UPSERTs each finished game into a games table. A chess game is an event (immutable, fully known at completion), and questions like "players from which countries have I beaten?", "what's my win-rate per opening?", or "which weekday do I play most?" are slice-and-dice queries over those events.

Note on provenance: this project was built with assistance from AI. The design, code, tests, and documentation were produced in collaboration with an AI coding assistant and reviewed before release.

What it captures

One row per game, per configured player, with the columns below (see internal/store/schema.sql):

Column	Notes
username, game_url, end_time	Natural key is (username, game_url); a game between two configured players is stored once per point of view.
time_class, time_control, rated, color, result	result is win/loss/draw from the player's perspective.
player_rating	The player's rating for that game — a true per-game rating curve.
opponent, opponent_rating, opponent_country	opponent_country only when enrichment is enabled.
eco	ECO opening code from the PGN header.
accuracy	The player's own accuracy % — only when a Game Review exists.
move_count, duration_seconds, avg_move_seconds	Derived from the PGN; NULL when unknown.

Nullable columns use NULL to mean unknown, never a real zero. Re-polling is idempotent (ON CONFLICT DO UPDATE); values that arrive later — a Game Review's accuracy, an opponent country, an opening after a fixed PGN parse — fill in without ever overwriting a stored value with NULL.

Scope

Only the three live time classes (rapid, blitz, bullet) are ingested. Daily / Chess960, Puzzle Rush, Tactics, and FIDE are out of scope. Per-move classifications (brilliant/blunder/…) are a premium Game Review feature not exposed by any API; only aggregate accuracy % is stored.

Installation

Prebuilt container image (GHCR)

docker pull ghcr.io/mocdaniel/pawnbase:latest

Multi-arch images (amd64 and arm64) are published to the GitHub Container Registry on every tagged release.

From source

go build ./cmd/pawnbase

Prebuilt binaries for Linux, macOS, and Windows (amd64/arm64) are attached to each GitHub release.

Configuration

pawnbase is configured with a YAML file (default config.yaml, override with --config). Copy config.example.yaml and edit it — every key is optional except users and the database connection:

database_url: "" # Postgres DSN; prefer the DATABASE_URL env var (see below)
poll_interval: 1h # how often to poll chess.com (keep well above a few minutes)
request_timeout: 30s # per-request HTTP timeout
user_agent: "" # optional contact User-Agent (see below)
history_window_months: 3 # months of archives to backfill each poll (1-60)
enrich_opponent_country: false # opt-in opponent-country lookups (cached)
users:
  - erik
  - hikaru

Database connection. Because the DSN carries a password, prefer the DATABASE_URL environment variable — it overrides database_url when set — and keep it out of committed config files. Each poll backfills the last history_window_months of archives; rows outside the window are never deleted, so your history accumulates over time.

User-Agent. Optional. If unset, pawnbase sends a generic, non-identifying pawnbase User-Agent. chess.com's API documentation strongly requests a contact (email, chess.com username, or URL), e.g. "pawnbase (mail@example.com)", so they can reach you before blocking abusive traffic. Leaving it unset trades that safety net for anonymity.

Running

Docker Compose (recommended)

A ready-to-use stack with Postgres is provided in docker-compose.yml:

cp config.example.yaml config.yaml   # then edit users (and optionally user_agent)
docker compose up -d

Binary

export DATABASE_URL="postgres://user:pw@localhost:5432/pawnbase?sslmode=disable"
./pawnbase --config config.yaml

The schema is created (and idempotently re-applied) on startup — no separate migration step.

Endpoints

Path	Description
/healthz	Liveness probe: 200 ok, or 503 if the database is unreachable.
/	Small landing page.

Querying

Point a Grafana PostgreSQL datasource at the database, or use psql. Some queries you might want to run:

-- Countries you've beaten (needs enrich_opponent_country: true)
SELECT opponent_country, count(*)
FROM games
WHERE username = 'erik' AND result = 'win' AND opponent_country IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC;

-- Rating development per live mode (true per-game curve)
SELECT end_time, time_class, player_rating
FROM games WHERE username = 'erik' ORDER BY end_time;

-- Win-rate and average accuracy per opening
SELECT eco,
       avg((result = 'win')::int)::numeric(4,3) AS win_rate,
       avg(accuracy)                            AS avg_accuracy,
       count(*)                                 AS games
FROM games WHERE username = 'erik' AND eco IS NOT NULL
GROUP BY eco ORDER BY games DESC;

-- Games by weekday (0 = Sunday), in UTC
SELECT extract(dow FROM end_time) AS weekday, count(*)
FROM games WHERE username = 'erik' GROUP BY 1 ORDER BY 1;

end_time is stored in UTC; wrap it in AT TIME ZONE at query time for local-time buckets.

Development

go test ./...          # run the test suite (store integration tests skip without a DB)
go vet ./...
golangci-lint run      # lint (config in .golangci.yml)

The store integration tests run only when TEST_DATABASE_URL points at a Postgres instance:

docker run --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:17
TEST_DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable" \
  go test ./internal/store/

CI runs lint and the full test suite (against a Postgres service container) on every push and pull request. Pushing a v* tag publishes a multi-arch image to GHCR and a GitHub release with cross-compiled binaries via GoReleaser.

License

MIT.