Development whiteboard: Difference between revisions

From Freeblox
Jump to navigation Jump to search
Line 307: Line 307:
|FK UUID
|FK UUID
|Creator ID
|Creator ID
|
|-
|parent_id
|FK int 10
|Parent content ID
|
|
|-
|-
Line 326: Line 331:
|type*
|type*
|enum
|enum
|Content type
|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
|content
|-
|-
Line 422: Line 433:
|
|
|
|
|-
| colspan="4" |M2M relation with bans
|}
|}
{| class="wikitable"
{| class="wikitable"
Line 633: Line 646:
Some items could be purchased for a certain amount of time only.
Some items could be purchased for a certain amount of time only.
|
|
|}
{| class="wikitable"
|+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
|
|
|}
{| class="wikitable"
|+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
|
|
|-
| colspan="4" |M2M relation with moderator action
|}
|}

Revision as of 09:08, 14 July 2023

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
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
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
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
M2M relation with bans
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.

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