Development whiteboard

From Freeblox
Revision as of 18:12, 27 July 2023 by Evert (talk | contribs)
Jump to navigation Jump to search

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:
  1. sent
  2. opened
  3. rejected
  4. return_to_sender
  5. confirmed
  6. 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.