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

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	varchar 2	Banned IP netmask	32
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
automatic*	boolean	Automatically assign to new users	false
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
automatic*	boolean	Automatically assign to new users	false
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.

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	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 Games: game, world	content
open_source*	boolean	Content is open source - can be downloaded / reused	false
license	text	Optional license text
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime

Content revision

Key	Type	Description	Default
id*	PK int 10	Revision ID
content_id*	FK int 10	Content ID
user_id*	FK UUID	User ID
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 (approve, reject, forward)	Moderator action
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.	true
rejection_reason	enum (tos, illegal, dmca, other)	Rejection reason
description	text	Additional information regarding moderator action
created_at	datetime
updated_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*	float	Rounded to two places (.00)	0.00
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	float	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.

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
notes	text	Moderator's notes
created_at	datetime
updated_at	datetime
resolved_at	datetime
M2M relation with moderator action

Game microservice

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)

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_host	text	API host
api_port	int	API port	443
country	varchar 255	Country of server
provision	boolean	If true, can be provisioned by session service	false
quarantine	boolean	If true, cannot be joined to. Set by moderators.	false
active	boolean	If false, server is not used for new sessions
server_info	json	Server-uploaded information about itself
ping	int	Last ping in milliseconds
created_at	datetime
updated_at	datetime
pinged_at	datetime	Server keepalive ping
M2M relation with games for multi-game support

Provisioned game server

Key	Type	Description	Default
id*	PK int 10	Relation ID
token*	text	Server token
game_id	FK int 10	Currently running game ID
socket_host	text	Socket host
socket_port	int	Socket port
api_host	text	API host
api_port	int	API port	443
country	varchar 255	Country of server
active	boolean	If false, server is not used for new sessions
server_info	json	Server-uploaded information about itself
ping	int	Last ping in milliseconds
created_by	FK UUID	User id
updated_by	FK UUID	User id
created_at	datetime
updated_at	datetime
pinged_at	datetime	Server keepalive ping

Session microservice

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