Data structure

Entities (tables)

This entity defines authentication factors entity information structure:

Column	Type	M/O	Purpose
id	uuid	M
user_id	uuid	M	User FK
type	varchar	M
factor	varchar	O	Factor value
is_active	boolean	M
inserted_at	timestamp	M
updateed_at	timestamp	M

This entity enrich a few attributes:

Store counters parameters for 2FA in `users.priv_settings` as structure :
{
"login_error_counter": 0,
"otp_error_counter": 0
}
Information about blocked directly in entity `users`.
Column Type Purpose
is_blocked boolean
block_reason varchar (255), NULL

This entity defines OTP (one tome passwords) entity information structure:

Column	Type	M/O	Purpose
id	uuid	M
key	varchar	M	Сomposite key: <token.id> + <separator> + <Value of factor>
code	varchar	M	Value of OTP
status	varchar	M	Status (Dictionary: OTP_STATUS)
code_expired_at	timestamp	M	Timestamp which OTP expired (now() + param from config)
attempts_count	integer	O	Count of trying OTP authorization process
updateed_at	timestamp	M

We need minimal 4 state for user & 2FA:

DISABLED - Disable 2FA for this user (via NHS Admin)
BLOCKED - User blocked after N-unsuccessful attempts via authentication process.
RESET - NHS Admin reset value of factor or factor type. At first login user must fill value of factor.
ACTIVE - normal status: 2FA enable

Propose don't use statuses model, and use logic calculating for state 2FA user:

Logic status for 2FA & user	Purpose
RESET	User don't blocked (`is_blocked`=false) & exist 1 active item in `authentication_factors` with empty value of factor . Examples for type = SMS: ( factor = NULL or factor = "" )
ACTIVE	User don't blocked (`is_blocked`=false) & exist 1 active item in `authentication_factors` with fill value of factor. Examples for type = SMS: ( factor= "+380677778899" )
DISABLED	User don't blocked (`is_blocked`=false) & not exist any active items in `authentication_factors` (SELECT * FROM authentication_factors AS 2FA WHERE 2FA.user_id = $.user_id AND 2FA.is_active = 1 -- return 0 rows)
BLOCKED	User have `is_blocked`=true (SELECT * FROM users AS U WHERE U.id = $.user_id AND U.is_blocked = true --- return 1 rows)

Dictionary: `OTP_STATUS`

Value	Purpose
NEW	Initial status for newest OTP item. Ready to use in OTP verification process.
VERIFIED	Status for successful OTP verification process
UNVERIFIED	Status for unsuccessful OTP verification process
EXPIRED	Expired after OTP lifetime.
CANCELED	Status for manual admin action.

Transitions

From	Transition	Result
	Created new OTP	status = NEW
status = NEW	Succseful OTP verify	status = VERIFIED
status = NEW	Unsuccseful OTP verify afterexcess of count [param: OTP_ERROR_MAX]	status = UNVERIFIED
status = NEW	[AUTO] Termination process after end of life-time OTP [param: OTP_LIFETIME]	status = EXPIRED
status = NEW	All OTP by `key` in status=`NEW` before creating new OTP	status = CANCELED