Definition

Diagram

MySQL

CREATE TABLE IF NOT EXISTS `applications` (
  `id` bigint(20) unsigned NOT NULL,
  `application_key` varchar(127) NOT NULL,
  `application_name` varchar(127) NOT NULL,
  `application_description` varchar(255),
  `client_id` varchar(64) NOT NULL,
  `client_secret` varchar(127) NOT NULL,
  `redirect_uri` varchar(255) NOT NULL,
  `status` int(10) NOT NULL COMMENT 'lt 0 deleted, 0 pendding, 1 valid',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  KEY `idx_applications_client_id` (`client_id`),
  UNIQUE KEY `udx_applications_application_key` (`application_key`)
);
CREATE TABLE IF NOT EXISTS `roles` (
  `id` bigint(20) unsigned NOT NULL,
  `role_name` varchar(127) NOT NULL,
  `role_description` varchar(255),
  `status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) unsigned NOT NULL,
  `ref` bigint(20) unsigned NOT NULL COMMENT 'ref to userID who invited this user',
  `out_key` varchar(255) COMMENT 'outer system''s key',
  `first_name` varchar(127) NOT NULL,
  `last_name` varchar(127) NOT NULL,
  `avatar_url` varchar(255),
  `photo_url` varchar(255),
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(255),
  `status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `application_role` (
  `application_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  `right` bigint(20) NOT NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`application_id`, `role_id`),
  CONSTRAINT `fk_application_role_application_id` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`),
  CONSTRAINT `fk_application_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
);
CREATE TABLE IF NOT EXISTS `application_user` (
  `application_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `right` bigint(20) NOT NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`application_id`, `user_id`),
  CONSTRAINT `fk_application_user_application_id` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`),
  CONSTRAINT `fk_application_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);
CREATE TABLE IF NOT EXISTS `emails` (
  `id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `email_address` varchar(127) NOT NULL,
  `email_verified_at` timestamp NULL,
  `status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_emails_email_address` (`email_address`),
  CONSTRAINT `fk_emails_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);
CREATE TABLE IF NOT EXISTS `phones` (
  `id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `phone_number` varchar(16) NOT NULL,
  `phone_verified_at` timestamp NULL,
  `status` int(10) NOT NULL COMMENT 'lt 0 deleted, 0 pendding, 1 valid',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_phones_phone_number` (`phone_number`),
  CONSTRAINT `fk_phones_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);
CREATE TABLE IF NOT EXISTS `role_user` (
  `role_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`role_id`, `user_id`),
  KEY `idx_role_user_role_id_user_id` (`role_id`, `user_id`),
  CONSTRAINT `fk_role_user_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `fk_role_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);
CREATE TABLE IF NOT EXISTS `tokens` (
  `id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `access_token` varchar(255) NOT NULL,
  `refresh_token` varchar(255) NOT NULL,
  `scope` varchar(255) NOT NULL,
  `code` varchar(32) NOT NULL COMMENT 'use in oauth2 code',
  `code_challenge` varchar(64) COMMENT 'oauth2 PKCE',
  `token_type` varchar(255) NOT NULL COMMENT 'Bearer',
  `expire_at` timestamp NULL,
  `client` varchar(255) NOT NULL,
  `version` varchar(255) NOT NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_tokens_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

Application

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
ApplicationKey	string	application_key	varchar(127)	true
ApplicationName	string	application_name	varchar(127)	true
ApplicationDescription	*string	application_description	varchar(255)	false
ClientID	string	client_id	varchar(64)	true
ClientSecret	string	client_secret	varchar(127)	true
RedirectUri	string	redirect_uri	varchar(255)	true
Status	int	status	int(10)	true	lt 0 deleted, 0 pendding, 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Role

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
RoleName	string	role_name	varchar(127)	true
RoleDescription	*string	role_description	varchar(255)	false
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

User

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
Ref	uint64	ref	bigint(20) unsigned	true	ref to userID who invited this user
OutKey	*string	out_key	varchar(255)	false	outer system's key
FirstName	string	first_name	varchar(127)	true
LastName	string	last_name	varchar(127)	true
AvatarUrl	*string	avatar_url	varchar(255)	false
PhotoUrl	*string	photo_url	varchar(255)	false
Password	string	password	varchar(255)	true
RememberToken	*string	remember_token	varchar(255)	false
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

ApplicationRole

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ApplicationID	uint64	application_id	bigint(20) unsigned	true
RoleID	uint64	role_id	bigint(20) unsigned	true
Right	int64	right	bigint(20)	true
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

ApplicationUser

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ApplicationID	uint64	application_id	bigint(20) unsigned	true
UserID	uint64	user_id	bigint(20) unsigned	true
Right	int64	right	bigint(20)	true
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Email

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
UserID	uint64	user_id	bigint(20) unsigned	true
EmailAddress	string	email_address	varchar(127)	true
EmailVerifiedAt	*time.Time	email_verified_at	timestamp	false
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Phone

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
UserID	uint64	user_id	bigint(20) unsigned	true
PhoneNumber	string	phone_number	varchar(16)	true
PhoneVerifiedAt	*time.Time	phone_verified_at	timestamp	false
Status	int	status	int(10)	true	lt 0 deleted, 0 pendding, 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

RoleUser

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
RoleID	uint64	role_id	bigint(20) unsigned	true
UserID	uint64	user_id	bigint(20) unsigned	true

Token

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
UserID	uint64	user_id	bigint(20) unsigned	true
AccessToken	string	access_token	varchar(255)	true
RefreshToken	string	refresh_token	varchar(255)	true
Scope	string	scope	varchar(255)	true
Code	string	code	varchar(32)	true	use in oauth2 code
CodeChallenge	*string	code_challenge	varchar(64)	false	oauth2 PKCE
TokenType	string	token_type	varchar(255)	true	Bearer
ExpireAt	*time.Time	expire_at	timestamp	false
Client	string	client	varchar(255)	true
Version	string	version	varchar(255)	true
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false