Development whiteboard

From Freeblox
Revision as of 08:01, 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
moderated* boolean If true, asset has been restricted by a moderator. false
type* enum Content type content
created_by FK UUID User id
updated_by FK UUID User id
created_at datetime
updated_at datetime
Object asset
Key Type Description Default
id* FK int 10 Content ID
asset_id* UUID ID of asset
type* enum (texture, texture3d,

mesh, animation, gameobject,

world, sound)

Asset ID this content references
Object price
Key Type Description Default
id* FK int 10 Content ID
price* float Rounded to two places (.00) 0.00
currency* enum (whole, denom) Two-currency system choice
Game object
Key Type Description Default
id* FK int 10 Content ID
privacy* enum (public, friends, unlisted, private) private
published* boolean false
Character object
Key Type Description Default
id* FK int 10 Content ID