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
|
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
|
|
|