Freeblox

Development whiteboard

Revision as of 10:50, 14 July 2023 by Evert (talk | contribs)

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, status
  • 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.

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

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

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
Retrieved from "https://wiki.freeblox.icynet.eu/index.php?title=Development_whiteboard&oldid=44"