UTM Database

Community compatibility reports for running PC games in UTM, served at db.getutm.app.

A single Go binary serves the public site and an internal API on a Unix socket, stores everything in one SQLite file, mirrors the IGDB PC game catalog, and keeps uploads and cover art on local disk. Cloudflare sits in front purely as a CDN.

How it works

• Catalog: every IGDB game released on PC is mirrored locally (igdb_games, with FTS5 search). The first sync walks the whole catalog (~hundreds of requests at 4 req/s); later runs fetch only games whose updated_at moved past the stored watermark. Cover art and storefront links are resolved when a game first receives a report, and the cover is copied to DATA_DIR/covers/.

• Active games: only games with at least one report appear on the site and can be starred. Submitting a report auto-stars the game for the author.

• Status: among the visible reports on the newest UTM version that has reports, each report counts one vote plus one per +1; the rating with the most votes wins and ties go to the most recent report. Users cannot +1 their own reports.

• UTM versions are a curated list managed through the internal API; the report form offers a dropdown.

• Reporting content: any signed-in user can report a report or comment to the maintainers with one click (not their own; content by a deleted account can be reported). Nothing public shows it, repeat clicks change nothing, and the flags disappear with the content or when a maintainer ignores them, after which the same user may report again. Rate limited like +1s.

• Reports carry a rating, the UTM version and guest OS (fixed once submitted; edits can change everything else), optional issue details, a CrossOver/Wine answer and private attachments. Authors can delete a report outright while it has no comments and is under 24 h old; after that they file a deletion request for a maintainer. Own comments can always be deleted.

• Auto-Triage: games the project owns on a storefront (each triage machine posts what it owns) or that are free show an "Auto-Triage available" badge; others get a "Donate a copy" button (/games/{id}/donate) offering gift accounts for the storefronts the game is sold on, or a demo link. The agent reports progress with a per-report triage status and may comment; /triage explains the pipeline. Bot comments may contain Markdown links; user content never renders links.

• Anti-spam (synchronous): honeypot field, link/HTML rejection with an editable error, per-user and per-IP hourly rate limits plus a minimum gap between any two content submissions by one user (RATE_CONTENT_INTERVAL, default 15 s; search, previews, stars and +1s are exempt), then Akismet. Bot comments from the triage agent bypass it.

• Attachments are private (uploader, operators and the triage agent only): other users cannot see them or that a report has any. They are type-checked by content, limited per file/report publicly and per user privately, and swept after ATTACHMENT_RETENTION_DAYS.

• Machine-readable twins: every page has a JSON twin at its address plus .json (/games.json, /games/{id}-{slug}.json, /games/{id}-{slug}/reports/{n}.json, /versions.json), and /reports.json lists the 200 most recently changed reports as a polling target for triage agents. /llms.txt states the rules for automated readers, then maps the documents, the vocabulary and the pages. Documents are identical for every requester, carry no attachments or addresses, are purged on the same writes as the pages, and may be held by the CDN for the full TTL. Agents are read-only; accounts are for people, and the report form refuses submissions faster than a person could fill it (CONTENT_FORM_MIN_AGE).

• Caching: public pages are rendered identically for everyone with Cache-Control: public, s-maxage=… and strong ETags and never set cookies. Per-user state (sign-in, stars, +1s, edit links) is hydrated by me.js from /api/me (no-store). Authenticated pages and all POSTs are private, no-store.

• Identity without addresses: email addresses are never stored. An account is identified by an Argon2id digest of the normalized address with a pepper derived from SECRET_KEY. A database dump cannot be reversed into addresses (no rainbow tables without the pepper; with it, each guess costs a full Argon2 run). Addresses never reach logs, Akismet, the spam log or the internal API, and the log mailer withholds the recipient. Consequences: no notifications are possible, a new address is a new account, and losing SECRET_KEY orphans every account, so back it up outside the server.

• Resource model: everything an anonymous visitor can reach is static or served from SQLite and cacheable by the CDN; search, previews, reports, uploads and attachments require sign-in. The one anonymous endpoint that can spend resources is POST /login, defended in layers: honeypot, address syntax, a signed single-use form token with a 2 s–1 h age window, a proof-of-work puzzle bound to that token (LOGIN_POW_BITS, solved in the browser), per-IP and per-address limits, then a site-wide send budget. Only what passes is queued; LOGIN_WORKERS workers do the Argon2 hash, an MX lookup (cached) and the SMTP send. The response is always the "enter your code" page, with the same request cookie, so the endpoint cannot be used to find out which addresses exist. The code is bound to that cookie, so it is useless to anyone who only reads the mailbox; wrong guesses are counted and five void it. Scanners that fetch links cannot consume anything. Memory bound: workers × 64 MiB.

• Visitor IP: CF-Connecting-IP (or the first X-Forwarded-For entry) is used only when the peer is inside TRUSTED_PROXY_CIDRS; otherwise the socket address is used. Everything works with or without a CDN in front.

• Your data: the account page offers a ZIP export (account, reports, comments, +1s, stars, requests, attachment files), deletion of all attachments, and deletion of the whole account (everything the user could delete themselves is deleted; reports past the direct-deletion window are kept, flagged anonymized and shown as "Deleted user", with attachments removed; the user row stays with deleted_at set and its email identity, so the internal API can still trace those reports to the account and a ban on it blocks the same address from returning; affected games are recomputed). Exports are the only way to get attachments back and are deliberately throttled (EXPORT_INTERVAL, default one per day; attachments younger than EXPORT_MIN_ATTACHMENT_AGE, default 48 h, are withheld) so the site cannot double as a file-transfer service. Attachments are never exposed by URL.

Running locally

Requires Go 1.27+.

cp .env.example .env            # fill in IGDB_CLIENT_ID / IGDB_CLIENT_SECRET; SECRET_KEY may stay empty locally
make build
MAIL_MODE=log DATA_DIR=./data BASE_URL=http://localhost:8080 ./gamesdb sync --full --max-pages 5   # small catalog slice
MAIL_MODE=log DATA_DIR=./data BASE_URL=http://localhost:8080 ./gamesdb seed                        # optional demo users, reports, statuses
MAIL_MODE=log DATA_DIR=./data BASE_URL=http://localhost:8080 ./gamesdb serve

With MAIL_MODE=log, sign-in codes are printed to stdout instead of being emailed (the recipient is withheld). In production, mail goes out either directly from the server (MAIL_MODE=direct) or through an SMTP relay (MAIL_MODE=smtp); both are described in deploy/README.md. Without SECRET_KEY the server uses a built-in development secret and warns; set a real one before any data you care about, since changing it later orphans every account. While editing templates, CSS or JS, start with DEV_RELOAD=1 (or make dev) and the files are re-read from the source tree on every request, so a save plus a browser reload is enough; the production binary embeds them and needs a rebuild. Add at least one UTM version before submitting reports:

curl -X POST localhost:8081/internal/utm-versions -d '{"version":"4.6.5","released_at":"2025-06-01"}'

Then open http://localhost:8080, sign in, and submit a report. Run go test ./... for the test suite (includes an end-to-end test of the public site).

Deployment

See deploy/README.md: Docker Compose on a Linux VPS, the bare-metal macOS setup behind Cloudflare (launchd, TLS, pf allowlist), and the Cloudflare zone configuration.

Internal API

Plain JSON over HTTP on a Unix socket (INTERNAL_LISTEN_ADDR, an absolute path; file permissions decide who may use it, and from another machine it is reached through an SSH tunnel, ssh -L 8081:/path/to/internal.sock user@host) or, in development, a loopback TCP address. No authentication of its own: the socket's permissions, or the network boundary, are the protection. Requests that carry browser headers (Origin, Sec-Fetch-Site) or a non-loopback Host are refused, so a web page open on the operator's machine cannot reach the API through the SSH tunnel. All routes are under /internal/.

Method	Path	Purpose
GET/POST	/internal/utm-versions	List / add {version, prerelease?, released_at?} (version is x.y.z)
DELETE	/internal/utm-versions/{id}	Remove an unused version
GET	/internal/work?since=&limit=	One call for a triage or moderation machine: visible reports created or changed since the cursor (ascending, with comments; next_since is the cursor for the next call and is inclusive at second resolution, so de-duplicate on id and updated_at; reports_more says a full page was returned), plus the open queues in full: donation requests, deletion requests, flagged content, unreviewed spam-log entries, and counts; triage_leases (the lease row of each listed report that has one, by id) and stale_triage (reports in progress whose lease expired: their machine stopped renewing). Poll it about once a minute
GET	/internal/backup	Streams an uncompressed tar of everything needed to rebuild the site elsewhere: env (settings, SECRET_KEY), tls/, dkim/, db/gamesdb.sqlite (every user and maintainer table, consistent snapshot, catalog tables empty), uploads/, plus manifest.json. The IGDB mirror and covers are left out and re-fetched after a restore. One at a time; restore with deploy/macos/restore.sh
GET	/internal/reports?since=&game=&visibility=&limit=	Reports (any visibility) with configuration and attachment metadata, oldest-updated first. anonymized marks reports whose author deleted their account; user_id still identifies that account
GET	/internal/reports/{id}	One report with all comments and the game
GET	/internal/attachments/{id}	Download an attachment
POST	/internal/reports/{id}/comments	Bot comment {body_md} (labelled "Auto-Triage"; Markdown links are rendered, unlike user comments)
POST	/internal/reports/{id}/visibility, /internal/comments/{id}/visibility	{visibility: visible|hidden|removed}
POST	/internal/reports/{id}/triage	{status: pending|in_progress|needs_clarification|fix_proposed|wont_fix|cannot_reproduce} (empty clears); shown as a badge on the report. GET /internal/reports?triage_status=none lists untriaged reports. A machine naming itself in X-Triage-Holder must hold the lease to set in_progress (409 otherwise); any other status ends its lease
GET/POST/DELETE	/internal/reports/{id}/lease	The triage lease (see below): POST {holder?, ttl_seconds?} takes or renews it (default 1200 s, 60 s–6 h) → 200 {lease}, or 409 {error, lease} when another machine holds it; DELETE ?holder= releases it
GET	/internal/deletion-requests?status=open	Authors' requests to remove reports that have comments or are older than 24 h; POST …/{id}/status {status: done|rejected} ("done" removes the report)
GET	/internal/triage/requests?status=open	User gift/demo offers; POST …/{id}/status {status} (409 when it is no longer open)
GET	/internal/triage/owned	The owned-games list
POST	/internal/triage/owned	Add entries or update their free flag, keeping every other entry: [{"storefront":"steam","storefront_id":"220","free":false}, {"igdb_game_id":1234}]. Each triage machine adds what it owns; an igdb_game_id entry marks any catalog game available, storefront link or not
DELETE	/internal/triage/owned/{storefront}/{storefront_id}	Remove one entry (/internal/triage/owned/steam/220; itch ids keep their slash)
GET	/internal/users/{id}	One user (id, display name, deleted_at; never an address) with activity: reports and comments (all and still visible), their flagged items and the flags on them, flags they made, spam log entries, +1s
POST	/internal/users/{id}/ban, …/unban	Moderation; banning a deleted user blocks the same address from returning
GET	/internal/flags?limit=	Content users have reported (kind, target, flag count, author, excerpt, path), most recently flagged first. Removing the content with the visibility endpoints deletes its flags
POST	/internal/flags/{report|comment}/{id}/ignore	Delete the flags on one item without telling anyone; the reporters may flag it again
GET	/internal/spam-log?pending=&limit=; POST …/{id}/feedback {feedback: spam|ham}	Review rejections (pending=1: only those without feedback yet), train Akismet
POST	/internal/sync {full?, max_pages?, wait?}; GET /internal/sync/status	IGDB sync
POST	/internal/games/{id}/refresh	Re-resolve a game's IGDB details and cover
POST	/internal/maintenance/sweep-attachments	Run the retention sweeper now
GET	/internal/healthz	Liveness

Several triage machines. A machine names itself in the X-Triage-Holder header. Before it triages a report it takes the report's lease, and it renews the lease while the work is alive; other machines skip a report with an active lease. Bot comments and triage status on a report with an active lease are refused (409) for every other caller, so a machine that lost its lease (it was offline past the expiry and another took over) cannot write over the new holder. A machine that names itself must hold the report's lease to set in_progress (409 otherwise), so a report in progress whose lease expired is one whose machine died: the feed lists it in stale_triage for another machine to take over. One put in progress without naming a machine (by hand) has no lease and is left alone. Moderation and gift work may be done by several machines at once: a write that finishes something already finished (a resolved deletion request or offer, spam feedback already given, flags already ignored) answers 409 and changes nothing.

Owned-games entries are matched to catalog games through the storefront ids parsed from IGDB website links (Steam app id, GOG/Epic slug, itch user/game), or directly by igdb_game_id. Entries that cannot be matched yet are kept and retried when a game becomes active.

Layout

cmd/gamesdb          entrypoint: serve | sync | seed | migrate | mail-setup | mail-test
internal/config      env configuration (.env is loaded if present)
internal/db          SQLite open (WAL, single writer pool) + embedded migrations
internal/igdb        IGDB client (rate limited), catalog sync, detail resolution
internal/store       typed queries and transactional domain operations
internal/status      version ordering and status rule (pure, tested)
internal/auth        email identities (Argon2id), sign-in codes, sessions, sign-in queue and proof of work
internal/mail        direct delivery with DKIM, SMTP relay, or log mailer
internal/antispam    link check, Akismet, rate limiter
internal/markdown    goldmark renderer: links disabled for users, allowed for the bot; no images or raw HTML
internal/upload      attachment validation, storage and download
internal/cdn         cache headers, ETag, optional purge hook
internal/web         public site: handlers, templates, static assets
internal/internalapi internal JSON API on a Unix socket (refuses browser requests)
deploy/              deployment notes, launchd/pf files for the macOS host, Docker files for a VPS

License

Apache License 2.0; see LICENSE.