4. Database Schema (MySQL)

All tables live in the same MySQL database. Schemas are created and migrated automatically on startup by SchemaMigrator (in common/.../Database/SchemaMigrator.java). The migrator:

• Runs CREATE TABLE IF NOT EXISTS for every table.
• Compares INFORMATION_SCHEMA.COLUMNS definitions to the in-code spec; emits ADD / MODIFY / DROP as needed.
• Normalizes MySQL type strings so TINYINT(1)↔BOOLEAN, INT(11)↔INT, BIGINT(20)↔BIGINT, SMALLINT(5) UNSIGNED↔SMALLINT UNSIGNED, LONGTEXT DEFAULT '[]'↔JSON are treated as equal.
• For ENUM modifications, only widens if no existing data contains values outside the new enum set (otherwise skips to preserve data).
• All updates run via Database.runUpdate(...), which is async and retries on MySQL deadlock (SQLState == "40001") with a 1-second backoff.

4.1 appelSettings — global key/value

Column	Type	Notes
key	VARCHAR(255) NOT NULL	PK
value	TEXT NOT NULL

DAO: AppelSettingsDB.get(key), update(key, value).

4.2 playerData — per-player global state

Column	Type	Notes
Uuid	VARCHAR(36) NOT NULL	PK
Name	VARCHAR(16) NOT NULL	last known username
IP	VARCHAR(45)	nullable
LastSeen	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
IgnoredPlayers	JSON NOT NULL DEFAULT '[]'	List<UUID>
Reports	JSON NOT NULL DEFAULT '[]'	List<Report>
Notes	JSON NOT NULL DEFAULT '[]'	List<String>
Friends	JSON NOT NULL DEFAULT '[]'	List<UUID>
FriendNotificationsToggled	BOOLEAN NOT NULL DEFAULT TRUE
FriendRequestsToggled	BOOLEAN NOT NULL DEFAULT TRUE
LastMessaged	BLOB	Java-serialized MCPlayer
MessagesToggled	BOOLEAN NOT NULL DEFAULT TRUE
StaffToggled	BOOLEAN NOT NULL DEFAULT TRUE
StaffChatToggled	BOOLEAN NOT NULL DEFAULT FALSE
StaffModeToggled	BOOLEAN NOT NULL DEFAULT FALSE
StaffSpyToggled	BOOLEAN NOT NULL DEFAULT FALSE

DAO: PlayerDataDB.getPlayerData(uuid), getPlayerDataByName(name), updatePlayerData(atPlayerData). JSON columns deserialized via Database/TypeTokens.java (STRING_LIST, UUID_LIST, REPORT_LIST).

4.3 playerServerData — per-player, per-server state

Column	Type	Notes
Uuid	VARCHAR(36) NOT NULL	PK (composite)
Name	VARCHAR(16) NOT NULL
ServerUUID	VARCHAR(36) NOT NULL	PK (composite)
LastSeen	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
PaymentsToggled	BOOLEAN NOT NULL DEFAULT TRUE
Balance	DECIMAL(10, 2) NOT NULL
Frozen	BOOLEAN NOT NULL DEFAULT FALSE
StaffModeToggled	BOOLEAN NOT NULL DEFAULT FALSE
TeleportAutoAccept	BOOLEAN NOT NULL DEFAULT FALSE
TeleportToggle	BOOLEAN NOT NULL DEFAULT FALSE

DAO: PlayerServerDataDB.getTopBalances(serverUUID, limit) (orders by Balance DESC LIMIT n), getPlayerServerData(uuid, serverUUID), updatePlayerServerData(atp).

4.4 punishments — permanent punishments

ENUM values: 'BAN','MUTE','KICK','IP_BAN','IP_MUTE','IP_KICK'.

Column	Type	Notes
Id	VARCHAR(36) NOT NULL	PK
Type	ENUM(...)
PunisherName	VARCHAR(16) NOT NULL
PunisherUuid	VARCHAR(36) NOT NULL
PunishedName	VARCHAR(16) NOT NULL
PunishedUuid	VARCHAR(36) NOT NULL
PunishedIP	VARCHAR(45)
Reason	TEXT NOT NULL
Active	BOOLEAN
CreatedAt	TIMESTAMP NOT NULL

4.5 tempPunishments — temporary punishments

ENUM values: 'TEMP_BAN','TEMP_MUTE','TEMP_IP_BAN','TEMP_IP_MUTE'. Same columns as punishments plus:

• ExpiresAt TIMESTAMP NOT NULL
• Duration BIGINT NOT NULL

4.6 undonePunishments — undo log

ENUM values: 'UNBAN','UNMUTE','IP_UNBAN','IP_UNMUTE'. Same columns as punishments.

4.7 staffInventory

Column	Type	Notes
Server	VARCHAR(255) NOT NULL	PK
Uuid	VARCHAR(36) NOT NULL	PK
Inventory	LONGTEXT NOT NULL	Base64-encoded ItemStack[]

DAO: StaffInventoryDB.get(server, uuid), set(server, uuid, base64), delete(server, uuid).

4.8 servers

Column	Type	Notes
Name	VARCHAR(255) NOT NULL	PK
IP	VARCHAR(45) NOT NULL
Port	SMALLINT UNSIGNED NOT NULL

DAO: ServerDataDB.getServerData(port), updateServerData(name, ip, port).

4.9 friendRequests

Column	Type	Notes
senderUuid	VARCHAR(36) NOT NULL	PK
receiverUuid	VARCHAR(36) NOT NULL	PK
createdAt	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

DAO: FriendRequestDB.addFriendRequest (INSERT IGNORE), removeFriendRequest, getPendingRequests(receiver), getSentRequests(sender), hasFriendRequest, removeAllRequests, getAllFriendRequests.

4.10 homes

Column	Type	Notes
ServerUUID	VARCHAR(36) NOT NULL	PK
PlayerUUID	VARCHAR(36) NOT NULL	PK
Name	VARCHAR(64) NOT NULL	PK
World	VARCHAR(64) NOT NULL
X	DOUBLE NOT NULL
Y	DOUBLE NOT NULL
Z	DOUBLE NOT NULL
Yaw	FLOAT NOT NULL
Pitch	FLOAT NOT NULL

DAO: HomeDB.getHomes, getHome, setHome (REPLACE), deleteHome, renameHome.

4.11 HikariCP Pool Configuration

Database.setup builds a HikariDataSource with these tunings:

• prepStmtCache, useServerPrepStmts, useLocalSessionState, rewriteBatchedStatements
• cacheResultSetMetadata, cacheServerConfiguration, elideSetAutoCommits, cacheCallableStmts
• serverTimezone=UTC
• maxLifetime=1800000 ms (30 min), keepaliveTime=300000 ms (5 min), connectionTimeout=5000 ms (5 s), idleTimeout=600000 ms (10 min)
• maxPoolSize / minIdle from DatabaseConfig
• JDBC URL: jdbc:mysql://{host}:{port}/{name} with optional ?requireSSL=true&useSSL=true if DatabaseConfig.ssl is true.

4.12 DAOs — Method Index

DAO	Key methods
AppelSettingsDB	get(key), update(key, value)
PlayerDataDB	getPlayerData(uuid), getPlayerDataByName(name), updatePlayerData(atPlayerData)
PlayerServerDataDB	getTopBalances(serverUuid, limit), getPlayerServerData(uuid, serverUuid), updatePlayerServerData(atp)
PunishmentDB	searchPunishments(filters, types, class), getActivePermPunishments(mcPlayer), getActiveIPTempPunishments(mcPlayer, ip), deactivatePunishments(...), deactivateExpiredPunishments(), generatePunishmentID()
UndonePunishmentDB	getUndonePunishment(id), searchUndonePunishments(filters), updateUndonePunishment(p)
ServerDataDB	getServerData(port), updateServerData(name, ip, port)
StaffInventoryDB	get(server, uuid), set(server, uuid, base64), delete(server, uuid)
FriendRequestDB	addFriendRequest, removeFriendRequest, getPendingRequests, getSentRequests, hasFriendRequest, removeAllRequests, getAllFriendRequests
HomeDB	getHomes, getHome, setHome, deleteHome, renameHome

4.13 Punishment ID Generator

PunishmentDB.generatePunishmentID() returns a 6-digit zero-padded random string. Up to MAX_PUNISHMENT_ID_ATTEMPTS=10 retries on collision across all three punishment tables. If still colliding, falls back to the first 6 characters of UUID.randomUUID().toString(). Range: PUNISHMENT_ID_RANGE=1_000_000.

4.14 Query Filter System

common/.../Types/Query/ provides:

• QueryKey enum mapping to columns: PUNISHED_UUID("PunishedUuid"), PUNISHED_IP("PunishedIP"), ACTIVE("Active").
• QueryFilter (interface) with boolean isUseOr().
• SingleQueryFilter (@Data) — QueryKey key, Object value, boolean useOr.
• MultipleQueryFilter (@Data) — boolean useOr, List<SingleQueryFilter> filters. Varargs constructor.

PunishmentDB.searchPunishments(filters, types, class) unions punishments + tempPunishments + undonePunishments queries, casting each result to PermPunishment / TempPunishment / UndonePunishment based on the requested class. UUIDs are converted to strings before being bound.

4.15 ENUM Type Lists (from PunishmentType)

• permanentTypes() → 'BAN','MUTE','KICK','IP_BAN','IP_MUTE','IP_KICK'
• tempTypes() → 'TEMP_BAN','TEMP_MUTE','TEMP_IP_BAN','TEMP_IP_MUTE'
• Undone enum (hardcoded in createUndonePunishmentsTable): 'UNBAN','UNMUTE','IP_UNBAN','IP_UNMUTE'