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

schema.sql is the complete current schema, it already reflects every migration, so nothing else is needed for a fresh install. Point the app at it with DB_HOST=localhost:3306, DB_NAME=hailsdotgo, and your credentials (see Configuration).

How migrations work

There are two migration files:

• schema.sql: the full current schema for fresh installs. Run this once on a blank database; do not also run migrate.sql on the same fresh install (the ALTER TABLE statements would fail against tables that already have those columns).
• migrate.sql: numbered sections for upgrading an existing install. Each section is labelled (e.g., -- 25. Translator applications).

Recommended: use the migrate tool. Rather than tracking sections by hand, let cmd/migrate apply the right ones for you. It reads migrate.sql, records applied sections in a schema_migrations table, and runs only what is pending, tolerating sections you already applied manually:

go run ./cmd/migrate -from v0.1.4a   # first run: baseline at your installed version, then apply the rest
go run ./cmd/migrate                 # every upgrade after that
go run ./cmd/migrate -status         # show applied vs pending

It reads the same DB_HOST/DB_USER/DB_PASS/DB_NAME env vars as the app, refuses to run on a fresh database (use schema.sql for those), and refuses an untracked database until you pass -from <version> so it never blasts every section at a partway-upgraded install. Fresh installs created from schema.sql already have a fully-recorded schema_migrations table. The -from flag accepts any recognized baseline version; v0.1.6a is now among them (use the version you actually installed from).

Manual fallback. You can still apply sections by hand: the ALTER TABLE statements are not idempotent, so run only the sections added since your last update, in order. The section headers carry dates where available.

Migration history

Section	Date	Description
1-22	(pre-2026)	Accounts, sessions, invites, shinies, site settings, store, tags, Raid Finder v2, trust events, awards
23	2026	locales table for translator-created languages
24	2026-06-11	host_unfulfilled event type added to trust_events.event_type enum
25	2026-06-11	translator_applications table
26	2026-06-18	shinies_hidden TINYINT(1) column on users
27	2026-06-22	user_friends and user_blocks tables
28	2026-06-22	feedback_options table + 10 seed phrases
29	2026-06-22	user_feedback table
30	2026-06-22	user_pokemon_box and mobile_device_tokens tables (mobile companion app)
31	2026-06-23	costume and event_tag columns on user_shinies; unique index rebuilt to include both
32	2026-06-23	trainer_level column on users
33	2026-06-23	Dropped unique constraint on user_shinies (replaced by a non-unique index)
34	2026-06-23	Renamed user_friends to user_follows
35	2026-06-25	confirm_warned_30s flag on raid_lobby_members
36	2026-06-25	min_grant_rank TINYINT UNSIGNED column on awards
37	2026-06-26	sprite_locks table: per-sprite rank access control (slug PK, min_rank, updated_at)
38	2026-06-26	evolved_at DATETIME NULL column on user_shinies
39	2026-06-28	Bug report system: bug_reports, bug_report_messages, bug_report_participants, bug_report_labels (7 seeded built-ins), bug_report_label_map
40	2026-06-28	Bug report triage: priority, assignee_id, rating, rating_comment, rated_at columns on bug_reports; bug_report_macros table
41	2026-06-28	Player reports: type, reported_user_id, reason columns on bug_reports (shared ticket tables)
42	2026-07-03	Transactional email: email_verified_at column on users (existing accounts grandfathered as verified); email_tokens table
43	2026-07-05	Regional form support in shiny collection: region VARCHAR(16) NOT NULL DEFAULT '' column on user_shinies, positioned after form. Kept separate from form so combinations like a Shadow Alolan catch stay representable

A schema_migrations table (section PK, name, applied_at) tracks which sections have run; the migrate tool maintains it, and schema.sql seeds it for fresh installs.

Sections 27-29 should be applied together; they have no inter-dependencies beyond the users table. Section 26 may produce a harmless "Duplicate column name" error if you already applied it; the rest will complete cleanly.

migrate_tags.sql

A standalone convenience script that creates the tags and user_tags tables (the same definitions as the tag migration block in migrate.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), pending_role (for invite-granted staff roles awaiting confirmation), plus many migration-added columns: trainer profile fields (trainer name/code, avatar, pronouns, city/region/country and a location_display privacy enum, favorite Pokemon), privacy flags (shinies_hidden), 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, email_verified_at (soft email verification), 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)
email_tokens	Email confirmation and password reset tokens: SHA-256 hash of the raw token (never the raw value), purpose enum (verify, reset), expiry, and single-use used_at; rows cascade on user delete

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 default variant), region (regional form: alolan, galarian, hisuian, paldean, or empty), costume, event_tag, method, caught_at. region is stored separately from form so combinations like a Shadow Alolan catch stay representable. evolved_at is set when the user evolves the entry through the app (changes pokemon_id to the evolved form). Unique per (user, pokemon, form) via non-unique index (migration 33)

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_iv_enabled	1	IV Calculator 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

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; confirm_warned_30s flag tracks whether the 30-second push warning has fired
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
translator_applications	Translator programme applications: target languages with proficiency levels, motivation text, optional experience and country, review status (pending, reviewing, accepted, rejected)
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.

Social and feedback

Table	Purpose
user_follows	Directional follow relationships: (user_id, friend_id) primary key; CASCADE on user delete; user_id sees raid notifications for friend_id. Renamed from user_friends in migration 34
user_blocks	Block relationships: (user_id, blocked_id) primary key; blocking is enforced in the social API handlers and removes the follow in both directions
feedback_options	Staff-curated list of feedback phrases with sentiment (positive, neutral, negative), sort_order, and enabled flag; seeded with 10 Pokemon-themed phrases in migration 28
user_feedback	One review per author--target pair; UNIQUE on (author_id, target_id); updated via ON DUPLICATE KEY; CASCADE on user and option delete

Bug and player reports

The bug report system (Bug Reports) and player reports (Player Reports) share these tables; a type column on bug_reports distinguishes bug from player.

Table	Purpose
bug_reports	One row per report: type (bug/player), reporter_id, reported_user_id (player reports), subject, reason (player reports), status (open/pending/resolved/closed), priority, assignee_id, CSAT rating/rating_comment/rated_at, and last_activity_at. reporter_email/anon_token are reserved for future anonymous reporting
bug_report_messages	Threaded messages: body, visibility (public/internal staff-only notes), is_system flag; CASCADE on report delete
bug_report_participants	Who is on a report: role (reporter/collaborator/staff), last_seen_at (drives unread), added_by
bug_report_labels	Labels: name, color, builtin flag; seeded with 7 built-ins (Bug, Crash, UI, Feature Request, Question, Duplicate, Wontfix)
bug_report_label_map	Many-to-many report-to-label join
bug_report_macros	Staff canned responses: title, body, created_by

Trainer avatar locks

Table	Purpose
sprite_locks	Per-sprite access control: slug (PK) maps to min_rank (0=unlocked, 1=Trusted+, 2=Content Creator+, 4=Tester+, 5=Moderator+, 100=Admin only). Professor sprites (label starts with "Prof.") are hard-locked to rank 1 in application code regardless of this table's contents

Mobile companion app

Table	Purpose
user_pokemon_box	Per-user Pokémon storage: Pokémon name, form, CP, level, IV values (nullable for ambiguous spreads), full iv_candidates JSON blob, optional caught date and 160-char note. Capped at 3000 rows per user by the application layer
mobile_device_tokens	FCM (Android) and APNs (iOS) push token registry: platform, push_token (unique), optional device_name. Replaced on re-register via UPSERT so one token is always current per device

See IV Calculator for how the box is used and API Reference for the mobile API endpoints.

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.