Development whiteboard

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