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