Technical:Back-end Server

Technical documentation for the back-end server. The back-end server is used by the website (https://trot.land/) for creating and managing user accounts and by game servers to persist player data.

Database schema

Authentication and access control

users

Key	Type	Nullable / Default	Description
id	uuid (PK)	NOT NULL
username	varchar	NOT NULL
password	text	NOT NULL
email	text	NOT NULL
activated	boolean	NOT NULL, false
created_at	timestamptz
updated_at	timestamptz

user_tokens

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
user_id	users.id (FK)	NOT NULL
type	enum login activate reset deactivate otp	NOT NULL	One time token for logging in via website Token to activate the account Token to reset account password Token to confirm account deletion Time-based one time password (TOTP) secret key
token	text	NOT NULL
expires_at	timestamptz
created_at	timestamptz

Each moderation action is recorded into the database, even if it was a one-off action.

moderations

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
type	enum user_ban ip_ban mute server_kick server_ban	NOT NULL	User is banned from logging in IP address is banned from logging in or creating accounts User is banned from using chat (timed out). If server_id is set, only applies to that server User was kicked from a server. server_id shows which server this action was taken in User is banned from a specific server, indicated by server_id
description	text	NOT NULL
notes	text		Private notes for moderators
server_id	servers.id (FK)		Server related to this moderation action
actor_id	users.id (FK)		Moderator/Admin who created this event
user_id	users.id (FK)		Moderated user
report_id	player_reports.id (FK)		ID of the player report associated with this moderation action, if applicable
ip_address	inet		Host or network
expires_at	timestamptz
created_at	timestamptz

The role system is used to differentiate moderators and administrators from regular players.

roles

Key	Type	Nullable / Default	Description
key	varchar (PK)	NOT NULL
name	varchar	NOT NULL
default	boolean
server_id	servers.id (FK)		If set, role only applies to specific server
many-to-many relation with users

Grants are the actual actions that each role has the permission to do.

grants

Key	Type	Nullable / Default	Description
key	varchar (PK)	NOT NULL
name	varchar	NOT NULL
many-to-many relation with roles

Servers and access

Used for the server list and for server status information for the back-end to reference.

servers

Key	Type	Nullable / Default	Description
id	uuid (PK)	NOT NULL
secret	text	NOT NULL	Secret key used by game servers to contact the back-end
address	text	NOT NULL	Connection address complete with the port
name	text	NOT NULL	Public name for the server
rules	text	NOT NULL	Individual server rules
listed	boolean	true	Certain servers may be delisted
region	varchar(2)	NOT NULL	ISO 3166-1 alpha-2 code
last_ping	timestamptz	NOT NULL	Last time the server contacted the back-end
last_players	integer	NOT NULL	Last reported player count

Session tokens are assigned before connecting to a server from (a launcher? or the website? via REST API) or by the server itself if authenticated directly by password. In the latter case, the player will not see the session token and it is only used for the character lock. Session tokens are valid only for one session or one connection to a server. The same character must not be used in multiple servers at once or in the same server multiple times.

session_tokens

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
server_id	servers.id (FK)	NOT NULL
character_id	characters.id (FK)		Set value indicates “character lock”
user_id	users.id (FK)	NOT NULL
token	text	NOT NULL
expires_at	timestamptz
created_at	timestamptz

Player persistence

All in-game data must be persisted to the database so that players will keep their characters and game progress between all of the servers.

characters

Key	Type	Nullable / Default	Description
id	uuid (PK)	NOT NULL
user_id	users.id (FK)	NOT NULL
name	varchar	NOT NULL	Display name of the character, set by player
title	varchar		In-game character title, only assignable via admin APIs
inventory	jsonb	NOT NULL	Serialized in-game inventory
stats	jsonb	NOT NULL	Serialized in-game statistics
journal	jsonb		Placeholder
last_pos	text	NOT NULL	Stringified vector3f (third dimension is a placeholder)
created_at	timestamptz
updated_at	timestamptz
last_login	timestamptz

Since player statistics and inventory is on a per-character basis, creating multiple characters may not be desirable. For that reason, multiple "outfits" will be implemented instead. The outfits table contains the actual character customization data. Each character will have at least one outfit entry.

outfits

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
character_id	characters.id (FK)	NOT NULL
name	varchar	NOT NULL, "Default"	Set by player
data	bytea	NOT NULL	Serialized character customization data
active	boolean	true	Currently used outfit for character

Some people do not get along. Some people get along really well. This table covers both of those cases.

relationships

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
type	enum block friend	NOT NULL
user_id	users.id (FK)	NOT NULL	User who blocked User who sent friend request
target_id	users.id (FK)	NOT NULL	Blocked user Friended user
target_name	varchar		Last known character name at the time of blocking
accepted	boolean		Always true for "block" Target player has to accept for the relationship to be valid
seen_at	timestamptz		Used for displaying friend request only once
created_at	timestamptz

parties

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
created_at	timestamptz
user_id	users.id (FK)	NOT NULL	Created by user, does not have to be current leader

party_members

Key	Type	Nullable / Default	Description
id	serial (PK)	NOT NULL
party_id	parties.id (FK)	NOT NULL
invited_by_id	users.id (FK)	NOT NULL
user_id	users.id (FK)	NOT NULL
accepted	boolean	false	User has to accept for the membership to be valid
leader	boolean	false	First user in party always gets true
created_at	timestamptz

Other

player_reports

Key	Type	Nullable / Default	Description
id	uuid (PK)	NOT NULL
actor_id	users.id (FK)	NOT NULL	User who reported
user_id	users.id (FK)	NOT NULL	User being reported
character_id	characters.id (FK)		Character of reported user at the time of reporting
character_name	varchar		Name of the character of the reported user at the time of reporting
user_ip	inet		IP address of the reported user at the time of reporting
reason	text	NOT NULL	Reason for reporting
notes	text		Private notes for moderators
resolved_at	timestamptz		Time report was resolved
resolved_by_id	users.id (FK)		Moderator who resolved the report
chat_log	text		Recent chat log recorded by the game server for context
created_at	timestamptz