Based on the understanding, Thrymr Team created a Technical Architecture & Database Schema for the User management. Image RemovedImage Removed

Version 1.0

Image Added

Description

Following points regarding JWT+Redis
JSON Web Tokens (JWT) :

We can control access to APIs you deploy to API gateways using JSON Web Tokens (JWTs).  When a client attempts to access an API, it must include a JWT. The resource validates the JWT with an authorization server using a corresponding public verification key. A token will be active until its set expiration date.
Even if the user has logged out on the front-end and local storage cleared, anyone having access to the token can access authenticated routes for that user until the token expires.

The solution is to save a blacklisted token on logout in a column of the user table and use it for validation, destroying the previous token when it expires.

Pros and cons

In-memory relies on main memory for computer data storage and is faster than database management systems that use disk-management systems.

Maintaining Redis is only for backend login engineering work not for any business functionality purpose.

Justification for Redis

1. Redis is an in-memory data structure store used as a database, cache, or message broker. You can use data structures like strings, hashes, lists, sets, sorted sets e.t.c

2. We have used JWT + Redis for many projects.
   We can use any other DB to maintain a blacklisted token but Redis is very fast as per our previous experiences.

...

SSO Architecture

...

SSO Architecture flow description:

1. Send User Credentials and Request for token

2. Verify Request URL

3. It connects to the Database and verifies the user credentials

4. Generate jwt token and return

5. Request for the resource (API) with jwt token in the header

6. Validate the token and Check the block list

7. Return the response

8. Save the token into a block list when the user logout
   

Database Schema V1.0

...

Database Schema V1.1

...

Table description

• user: Table contains information about all users who has access to the platform

• organization: This table stores information about all the organizations

• user_organization: This is a mapping table, It shows the user's organization/central. it's a many-to-one relation from the user table.

• policy: This table contains the policies attached by the organization.
  It's one-to-many relation from the organization table.

• role: Its a master table and it contains all user roles

• menu: It contains all pages in the application

• pages: it contain all subpages of a menu(One menu can have multiple submenus)

• role_permission: This table contains permissions, each role has a collection of page/menu-wise permissions. so that we have added menu and role references to this table.

• team: it contains team header level information

• team_memeber: it a mapping of team members and team.

• signup_info: This table contains the invitation URLs which has been sent for signup into the platform.

• org_theam: It contains the organization brand color codes which will use for front-end page design.

Table & Reference

// Creating tables

Table user as u {
id long [pk, increment] // auto-increment
first_name varchar
last_name varchar
email varchar [unique]
phone_number int [unique]
password varchar
profile_image_ref varchar //s3 References
role_id long
created_by long
updated_by long
}

Table role as r {
id long [pk]
name varchar
}

Ref: u.role_id > r.id

Table organization as org {
id long [pk]
name varchar
url varchar
logo_ref varchar
}
Table org_theam as theam {
id long [pk]
organization_id long [ref: > org.id]
brand_clr_code_1 varchar
brand_clr_code_2 varchar
}

Table policy as p {
id long [pk]
organization_id long [ref: > org.id]
policyterm varchar
policy_doc_ref varchar
}

table user_organization {
user_id long [ref: > http://u.id ]
organization_id long [ref: > org.id]
}

table menu as m {
id long [pk]
name varchar
action varchar
}

table page as pg {
id long [pk]
menu_id long [ref: > m.id]
name varchar
}

table role_permission {
role_id long [ref: > r.id]
page_id long [ref:> pg.id]
permission varchar
}

table signup_info {
id long [pk]
user_id long [ref: > http://u.id ]
email varchar
url varchar
expire_time timestamp
}

table team as t{
id long [pk]
admin_id long [ref: > http://u.id ]
type varchar
}

table team_member {
team_id long [ref: < t.id]
member_id long [ref: > http://u.id ]
}