Development whiteboard

Database structuring

Auth microservice

Users

Key	Type	Description	Default
id*	PK UUID	User ID, UUID	uuid_generate_v4()
username*	varchar 255	Username
email*	varchar 255	Email address
phone	varchar 255	Phone number
country	varchar 2	2-character country code
language*	varchar 2	2-character language code	en
password*	text	bcrypt 12 rounds password
display_name	text	user-chosen display name	same as username
verified*	boolean	Email verified	false
activated*	boolean	Account active	true
login_at*	datetime	Last login date and time
created_at*	datetime		now()
updated_at*	datetime		now()
deleted_at	datetime

User tokens

Key	Type	Description	Default
id*	PK int 10		autoincrement
token*	text	Token
nonce	text	Number-used-once, for challenges
type*	enum (generic, activation, deactivation, password, login, gdpr, totp, public_key, recovery, refresh)	Token type
user_id*	FK UUID	Token owner
previous_id	FK int 10	Previous token ID of the same series. This is used to prevent refresh token reuse.
expires_at	datetime	Token expiry time
created_at*	datetime		now()

Bans

M2M join tables: ban-privilege.

Key	Type	Description	Default
id*	PK int 10		autoincrement
reason*	text	Reason for ban
ip	varchar 255	Banned IP
cidr	int 3	Banned IP netmask	32 - single address
user_id	FK UUID	Banned user ID. Not required if IP ban
admin_id*	FK UUID	User who created the ban ID
privilege_ban	boolean	This ban only affects certain privileges. Unless web is included, a notice will not be shown.	false
expires_at	datetime	Ban expiry date
created_at*	datetime		now()

Privilege

M2M join tables: role-privilege, user-privilege.

Key	Type	Description	Default
id*	PK int 10		autoincrement
privilege*	text	Privilege
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime

Role

Tree structure. M2M join tables: role-privilege, user-role.

Key	Type	Description	Default
id*	PK int 10		autoincrement
role*	text	Privilege
parent_id	FK int 10	Parent role ID
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime

Catalog microservice

This microservice contains the store, ownership and "physical" representation of the games.

Catalog categories

This is for searching content types more easily.

content category

Key	Type	Description	Default
id*	PK int 10		autoincrement
category*	text	Category
parent_id	FK int 10	Parent category ID

content category content type

Key	Type	Description	Default
id*	PK int 10		autoincrement
content_type*	enum	Content.type enum
category_id	FK int 10	Category ID

Content

Base object

Key	Type	Description	Default
id*	PK int 10	Content ID
name*	varchar 255	Item name
description*	text	Item description
user_id	FK UUID	Creator ID
parent_id	FK int 10	Parent content ID
restricted*	boolean	If true, asset has been restricted by a moderator.	false
onsale*	boolean	If true, asset is available for sale. If no prices defined, is free. Field has no effect on games unless prices are also defined.	false
published*	boolean	If false, only visible to the creator (and moderators)	false
comments_enabled*	boolean	Comments enabled	true
privacy*	enum (public, friends, unlisted, private)	Only applies to games(?) Visibility of content on the platform.	public
type*	enum	Content type. Generic: content Character items: character, hat, accessory, front, back, tool Creator items: mesh, texture, gameobject, sound, animation Comments: comment, status Games: game, world	content
open_source*	boolean	Content is open source - can be downloaded / reused	false
tradeable*	boolean	Marks this item as tradeable. Forbidden on: content, Creator items, Comments, Games	false
marketable*	boolean	If true, users can resell their owned copies of this content. Doesn't make much sense without a limited stock. Forbidden on: content, Creator items, Comments, Games	false
stock	int	Max sold item count. Forbidden on: content, Creator items, Comments, Games
license	text	Optional license text
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime
deleted_at	datetime

Content revision

Key	Type	Description	Default
id*	PK int 10	Revision ID
content_id*	FK int 10	Content ID
created_by*	FK UUID	User ID
updated_by	FK UUID
created_at	datetime
updated_at	datetime
deleted_at	datetime

Content relation

Key	Type	Description	Default
id*	PK int 10	Revision ID
first_content_id*	FK int 10	Content ID
second_content_id*	FK int 10	Second content ID. Second content is <relation> of the first.
relation*	enum	Relation type support - Second content supports first depend - Second content depends on first variant - Second content is variant of first alternative - Second content is alternative to first visual - Second content is a visualization of first related - Second content is related to first comment - Second content is a comment part - Second content is part of first
relation_name	string	More detailed name of the relation (thumbnail, icon, texture, character)
created_by*	FK UUID	User ID
updated_by	FK UUID
created_at	datetime
updated_at	datetime

Moderator action

Key	Type	Description	Default
id*	PK int 10	Moderation ID
content_id*	FK int 10	Content ID
revision_id*	FK int 10	Revision ID
action*	enum (pending, approve, reject, forward)	Moderator action	pending
user_id	FK UUID	Moderator user ID
penalty*	boolean	Moderator penalized the content author. For example, banned for illegal content.	false
asset_delete*	boolean	Moderator ordered the deletion of related assets from the system.	false
rejection_reason	enum (tos, illegal, dmca, other)	Rejection reason
description	text	Additional information regarding moderator action
created_at	datetime
updated_at	datetime
decided_at	datetime
M2M relation with bans

Object asset

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
revision_id*	FK int 10	Revision ID
asset_id*	UUID	ID of asset
type*	enum (image, texture, texture3d, mesh, animation, gameobject, world, character, sound)	Type of asset
type_name	varchar 255	Sub-type (preview, icon, etc)
index*	int 10	Asset index	0

Object price

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
price*	integer	No decimals	0
currency*	enum (whole, denom)	Two-currency system choice
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime

Content ownership relation

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
source*	enum (author, purchase, trade, gift)	Obtained by action	author
user_id*	FK UUID	User ID
previous_ownership_id	FK int 10	Previous ownership ID. This is used by the system to track the ownership of items and trace trades. The previous ownership entity must have its ended_at date set.
purchase_price	integer	Purchase price
purchase_currency	enum (whole, denom)	Purchase price currency
serial	int 10	Serial number of item
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime
ended_at	datetime	Ownership end date and time. If this is not null, the related user does not own this item anymore.
expires_at	datetime	Ownership expiry date and time. Some items could be purchased for a certain amount of time only.

Trade

Key	Type	Description	Default
id*	PK int 10	Relation ID
user_id*	FK UUID	User ID
recipient_id*	FK UUID	User ID
status*	enum	Status: sent opened rejected return_to_sender confirmed cancelled	sent
description	text	Trade description
created_at	datetime
updated_at	datetime
M2M join tables: user-content-ownership, recipient-content-ownership

Favorite

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
user_id*	FK UUID	User ID
created_at	datetime
updated_at	datetime

Content vote

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
user_id*	FK UUID	User ID
vote*	enum (up, down)	Up or down vote
created_at	datetime
updated_at	datetime

Content report

Key	Type	Description	Default
id*	PK int 10	Relation ID
content_id*	FK int 10	Content ID
user_id*	FK UUID	User ID
moderator_id	FK UUID	Moderator ID
reason*	text	Report reason
description*	text	Report description
status*	enum (open, closed, invalid, resolved)	Moderator's resolution	open
revision_id	FK int	Reported revision
notes	text	Moderator's notes
created_at	datetime
updated_at	datetime
resolved_at	datetime
M2M relation with moderator action

Game microservice

Game feature flags.

Game

Key	Type	Description	Default
id*	FK int 10	Game ID, same as the Content ID
hosted*	boolean	If true, only uses self-hosted servers	false
max_players*	int	Max player count	16

Game supports

Key	Type	Description	Default
id*	PK int 10	Relation ID
game_id*	FK int 10	Game ID
flag*	enum	Support flag: character individual character features chat
enabled*	boolean	Flag is enabled	true
content_id	FK int 10	Supported content ID (character)

Server microservice

Manages servers.

Provisioning hosts can be used to spawn new servers or remove inactive ones depending on the load. The web platform microservice only keeps track of servers, it does not itself shut down or create new servers. Static "always-on" servers can be configured manually by administrators.

The session service will request a server for a game from the server service. Servers which contain friends of the requested user and have free player slots will be preferred. If no servers are available, provision hosts are contacted and a new server is requested. Provisioning hosts usually create multiple servers at once, provided they have the slots, to alleviate bigger player influxes.

Server provisioning host

Key	Type	Description	Default
id*	PK int 10	Relation ID
token*	text	Server host token
api_url*	text	API URL
official*	boolean		true
active*	boolean		true
pinged_at	datetime	Last ping time
capacity*	int	Max capacity (number of server slots)
slots*	int	Server slots remaining
updated_by	FK UUID	Admin user ID
updated_at	datetime

Game servers can be created by administrators or by the system via provisioning hosts to service the entire site (official boolean flag) or by users to service their own games.

If the "provisioned" boolean flag is set, then the server service will decide which game will be loaded in a given server. For user-hosted servers, the games loaded will only be the ones configured by the user.

Hosted game server

Key	Type	Description	Default
id*	PK int 10	Relation ID
token*	text	Server token
user_id*	FK UUID	Owner user ID
game_id	FK int 10	Currently hosted game ID
socket_host*	text	Socket host
socket_port*	int	Socket port
api_url*	text	HTTP(S) API base URL
country	varchar 255	Country of server
provisioned*	boolean	If true, current game can be chosen by server service	false
quarantine*	boolean	If true, cannot be joined to. Set by moderators.	false
official*	boolean	Official provisioned server	false
active*	boolean	If false, server is not used for new sessions	true
server_info	json	Server-uploaded information about itself
player_count	int	Player count from last ping
ping	int	Last ping in milliseconds
provisioned_by	FK int	Provisioning host ID, if applicable
created_at	datetime
updated_at	datetime
pinged_at	datetime	Server keepalive ping
M2M relation with games for multi-game support

Host bans differ from user (IP) bans. It is possible to ban a server or network for being used to provide hosting without banning users.

Host ban

Key	Type	Description	Default
id*	PK int 10	Relation ID
ip*	varchar 255	Server IP
cidr*	int 3	Server IP netmask	32
admin_id*	FK UUID	Admin user ID
user_id	FK UUID	Banned user ID
reason*	text	Reason for network ban
created_at	datetime
updated_at	datetime
expires_at	datetime	If null, does not expire

Session microservice

Manages sessions.

Session

Created when player presses Play. Server gets token on connection. Server validates token against service and receives character.

Key	Type	Description	Default
id*	PK int 10	Relation ID
token*	text	Session token
user_id*	FK UUID	Owner user ID
character_id*	FK int 10	Character ID
game_id*	FK int 10	Played game ID
server_id*	FK int 10	Server ID
server_type*	enum (host, provision)	Server type	provision
created_at*	datetime
joined_at	datetime	Set when the server validates the token
pinged_at	datetime	Session keepalive ping

Player microservice

Character

Key	Type	Description	Default
id*	PK int 10
user_id*	FK UUID	User ID
content_id*	FK int 10	Character content type ID
name	text	Character name
created_at	datetime
updated_at	datetime

Character properties

Key	Type	Description	Default
id*	PK int 10
character_id	FK int 10	Character ID
property*	varchar 255	Character Property
value*	text	Property Value
value_type*	enum	Property Value type
related_content_id	FK int 10	Content ID
created_at	datetime
updated_at	datetime

Character worn item

Key	Type	Description	Default
id*	PK int 10
character_id	FK int 10	Character ID
content_id	FK int 10	Content ID
created_at	datetime
updated_at	datetime

Friends

Key	Type	Description	Default
id*	PK int 10
user_id*	FK UUID	User ID (Requester)
target_id*	FK UUID	Second user ID
pending*	boolean	Is pending request	true
created_at	datetime
updated_at	datetime

Follow

Key	Type	Description	Default
id*	PK int 10
user_id*	FK UUID	User ID (Follower)
target_id*	FK UUID	Second user ID (Followee)
created_at	datetime
updated_at	datetime

Block

Key	Type	Description	Default
id*	PK int 10
user_id*	FK UUID	User ID (Blocker)
target_id*	FK UUID	Second user ID (Blockee)
created_at	datetime
updated_at	datetime

Bank microservice

Currencies

Definition

Key	Type	Description	Default
id*	PK int 10
type*	enum (whole, denom)	Currency type
name*	varchar 255	Currency name
created_at	datetime
updated_at	datetime

Conversion

Key	Type	Description	Default
id*	PK int 10
currency_source_id*	FK int 10
currency_destination_id*	FK int 10
ratio*	int	Conversion ratio
volume	int
created_at	datetime
updated_at	datetime
valid_from*	datetime
valid_to	datetime

Account balance

Key	Type	Description	Default
id*	PK int 10
currency_id*	FK int 10
user_id*	FK UUID	User ID
amount	int 10	Amount
created_at	datetime
updated_at	datetime

Transaction

Key	Type	Description	Default
id*	PK int 10
currency_id*	FK int 10
user_id*	FK UUID	User ID
recipient_id*	FK UUID	User ID
previous_transaction_id	FK int 10	Previous transaction ID, usually for rollbacks/refunds
description	text	Transaction description
amount	int 10	Amount
confirmed*	boolean		false
automated*	boolean	Automatic transaction initiated by platform	false
created_at	datetime
updated_at	datetime

Assets microservice

Manages assets, forwards them from S3-compatible buckets.

Key	Type	Description	Default
id*	PK UUID
user_id	FK UUID	Created by user
asset_tag	text	Asset tag
source_uri	text	Data source URI
source	text	Data source
originalname	text	Original name
mimetype	varchar 255	Mimetype
filesize	int	File size in bytes
upload_ip	text	Uploader IP address
public	boolean	If false, requires valid token issued by platform to access	false
created_at	datetime
updated_at	datetime
deleted_at	datetime

Render microservice

Render microservice does not have any databases. Its only job is to render 3D assets to 2D thumbnails.

Character accessories are rendered onto supported character models and then uploaded as thumbnail image assets.

GameObjects are also rendered here.

This microservice also does transcoding and reformatting - formatting variable formats into commonly used ones.