Development whiteboard

Database structuring


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()


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()

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()

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

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

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
id*	FK int 10	Content ID
asset_id*	UUID	ID of asset
type*	enum `(texture, texture3d,` `mesh, animation, gameobject)`	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