Database Guide

Database Guide

All persistent state lives in a single MySQL 8+ database using the utf8mb4 character set: accounts, sessions, shiny collections, trainer profiles, raid lobbies, trust events, awards, store purchases, translation edits, and site settings. This page walks through the schema area by area, explains how the comment-block migration system works, and covers backups. Game data (Pokemon stats, raids, events) is not in the database; it is fetched from upstream sources and cached on disk, see Data-Sources.

Creating the database

schema.sql begins with CREATE DATABASE IF NOT EXISTS hailsdotgo (utf8mb4, utf8mb4_unicode_ci) and a USE statement, so a fresh setup is one command:

mysql -u your_db_user -p < schema.sql

Then run the migration blocks described below. Point the app at it with DB_HOST=localhost:3306, DB_NAME=hailsdotgo, and your credentials (see Configuration).

How migrations work

The base CREATE TABLE statements at the top of schema.sql are the original tables. Every schema change made since then is appended to the same file as a commented-out SQL block, each introduced by a -- Migration: header, in chronological order.

• Fresh install: apply the base tables, then every migration block in order (uncomment or paste them into the MySQL client). Blocks use IF NOT EXISTS and INSERT IGNORE where possible, but the ALTER TABLE statements are not idempotent, so keep track of where you are.
• Existing install: after pulling a new version, run only the blocks added since your last update, in order. The headers carry dates for recent ones.

migrate_tags.sql

A standalone convenience script that creates the tags and user_tags tables (the same definitions as the tag migration block in schema.sql) and seeds a Dev tag. It also contains a commented insert you can edit to assign that tag to your superadmin account.

Tables by area

Accounts, sessions, and invites

Table	Purpose
users	Accounts: bcrypt password hash, role enum (user, tester, moderator, admin), plus many migration-added columns: trainer profile fields (trainer name/code, avatar, pronouns, city/region/country and a location_display privacy enum, favorite Pokemon), moderation flags (disabled with disabled_reason, directory_hidden, raid_banned), permissions (api_access, translator), raid stats (raid_xp, last_raid_at, rater_weight, trust_score, special_rank), language preference, and activity timestamps
sessions	Server-side session tokens with expiry; rows cascade on user delete
invites	Invite codes generated by admins: expiry, optional granted role, multi-use support (max_uses, use_count)

Note that superadmin is not a role value in the database; it is matched by username against the SUPERADMIN_USER environment variable (see Accounts-and-Roles).

Moderation

Table	Purpose
user_strikes	Staff-issued strikes with reason and issuer

Shinies

Table	Purpose
user_shinies	One row per caught shiny per user: PoGoAPI name key, form (empty string for the default variant), method, caught date. Unique per (user, pokemon, form)

Site settings

site_settings is a simple key/value table that drives runtime toggles. Seeded keys and defaults:

Key	Default	Meaning
registration_open	0	Open self-registration (invites work regardless)
page_raids_enabled	1	Raids page on/off
page_dps_enabled	1	DPS calculator on/off
page_pvp_enabled	1	PvP IV ranker on/off
page_events_enabled	1	Events page on/off
page_trainers_enabled	1	Trainers page on/off
page_shinies_enabled	1	Personal shiny tracker on/off
section_trainer_directory_enabled	1	Directory section within Trainers
section_raid_finder_enabled	1	Raid Finder section within Trainers
store_enabled	0	Supporter store (seeded by the store migration)
awards_community_grants_enabled	0	Let non-staff users grant awards
awards_grant_min_trust	50	Trust score required to grant awards
raid_custom_lobby_min_trust	50	Trust score required for custom raid lobbies

page_shinydex_enabled is also honored by the code but is not seeded; a missing key counts as enabled. All page toggles are managed from the admin panel, see Operations.

Raid Finder v2 (current)

Introduced by the 2026-06-10 migration block; replaces the v1 browse-and-join board with per-boss matchmaking. See Raid-Finder.

Table	Purpose
raid_lobbies	Host lobbies: boss, tier, custom flag, member cap, state machine (open, full, raiding, reported, cancelled, expired), server-enforced deadlines
raid_lobby_members	Per-member state (matched, confirmed, timed_out, left, removed, requeued), confirm deadlines, attendance, host votes
raid_queue	One queue slot per user; millisecond enqueued_at is preserved on requeue so returned members re-enter at the front; stale heartbeats are evicted

Trust and awards

Table	Purpose
trust_events	Source of truth for trust: commends, dislikes, timeouts, early leaves, raid successes, staff adjustments, each with raw delta, weight, and applied delta. users.trust_score caches the sum. A NULL actor means a system event
awards	Award catalog (slug, name, icon, color); five community awards are seeded
award_grants	Individual grants; the same award from different granters stacks, but one granter cannot repeat it for the same recipient

See Trust-and-Awards.

Store and tags

Table	Purpose
store_items	Catalog (the migration seeds Supporter Pack and Priority Pass); price in cents, type (one_time, monthly, bimonthly), benefit key
purchases	PayPal orders with status lifecycle (pending, completed, refunded, cancelled) and expiry
custom_tag_requests	Supporter tag submissions with staff review status (including revision)
tag_color_changes	Rate-limit log for tag color changes
tags, user_tags	Colored label pills and their assignment to users

See Store.

Translations

Table	Purpose
translation_edits	Pending locale changes submitted by translators: language, key, old and new text, review status
locales	Runtime locale registry for translator-created languages; enabled controls the public language switcher. en is implicit and never stored; es, fr, de are seeded enabled

See Translator-Workspace.

Legacy Raid Finder v1 (kept for history)

raid_posts, raid_joins, and raid_ratings belong to the retired v1 post board. v2 stops writing to them but they are intentionally kept so history survives; the schema notes you can drop them manually once v2 has been stable for a release. raid_leave_log and raid_join_cooldowns are still actively used by v2.

Backups

The database is the only thing you cannot regenerate. A simple nightly dump covers it:

mysqldump -u your_db_user -p --single-transaction hailsdotgo > hailsdotgo-$(date +%F).sql

Also back up the locales/ overlay directory on the server (default LOCALES_DIR): it holds approved translation overrides that live outside both the binary and the database, plus their timestamped backups. Losing it silently reverts community translations to whatever is embedded in the binary.

Do not bother backing up:

• cache/ (CACHE_DIR): fetched game data and scraped event pages; regenerated automatically, with embedded snapshots covering a cold start
• The binary, templates, and static files: rebuilt from the repo on every deploy

See Operations for the wider day-2 picture.