Development whiteboard

From Freeblox
Revision as of 08:36, 14 July 2023 by Evert (talk | contribs) (→‎Content)
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()

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

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
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
type* enum Content type content
opensource* boolean Content is open source - can be downloaded / reused false
multiown* boolean Users can own multiple instances of this item false
license text Optional license text
created_by FK UUID User id
updated_by 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
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
Object asset
Key Type Description Default
id* PK int 10 Relation ID
content_id* FK int 10 Content 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
Game object
Key Type Description Default
id* FK int 10 Content ID
privacy* enum (public, friends, unlisted, private) private
Character object
Key Type Description Default
id* FK int 10 Content ID
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.