Account APIs and Schemas

Diagram examples

Schema examples

User Table

Column	Type	Description
user_id	integer	Primary key for the user
username	string	Unique username for the user
email	string	Unique email address for the user
password_hash	string	Hashed password for the user
created_at	datetime	Timestamp of when the user account was created
updated_at	datetime	Timestamp of when the user account was last updated

Authentication Token Table

Column	Type	Description
token_id	integer	Primary key for the authentication token
user_id	integer	Foreign key to the user table
token	string	Unique authentication token
created_at	datetime	Timestamp of when the authentication token was created
updated_at	datetime	Timestamp of when the authentication token was last updated

Password Reset Token Table

Column	Type	Description
token_id	integer	Primary key for the password reset token
user_id	integer	Foreign key to the user table
token	string	Unique password reset token
created_at	datetime	Timestamp of when the password reset token was created
updated_at	datetime	Timestamp of when the password reset token was last updated

Role Table

Column	Type	Description
role_id	integer	Primary key for the role
name	string	Name of the role
description	string	Description of the role
created_at	datetime	Timestamp of when the role was created
updated_at	datetime	Timestamp of when the role was last updated

User Role Table Column | Type | Description user_role_id | integer | Primary key for the user role user_id | integer | Foreign key to the user table role_id | integer | Foreign key to the role table created_at | datetime | Timestamp of when the user role was created updated_at | datetime | Timestamp of when the user role was last updated

Session Table

Column	Type	Description
session_id	integer	Primary key for the session
user_id	integer	Foreign key to the user table
token	string	Unique session token
expiration_time	datetime	Timestamp of when the session token will expire
created_at	datetime	Timestamp of when the session was created
updated_at	datetime	Timestamp of when the session was last updated

Login History

Column	Type	Description
login_id	integer	Primary key for the login
user_id	integer	Foreign key to the user table
login_time	datetime	Timestamp of when the user logged in
ip_address	string	IP address of the device used to log in
device_info	string	Information about the device used to log in
created_at	datetime	Timestamp of when the login record was created
updated_at	datetime	Timestamp of when the login record was last updated

Account/Login API Microservices

API Reference

• Accounts
  • https://docs.merge.dev/crm/accounts/
• Auth0
  • https://auth0.com/docs/api/authentication
• Okta APIs
  • https://developer.okta.com/docs/reference/core-okta-api/
• IdentityIQ API Reference
  • https://developer.sailpoint.com/iiq/api

Authentication Service examples

• User authentication and providing authentication tokens.
• User: id, username, email, password, salt
• Token: id, user_id, token, expiry_date
• User Signup:
  • A user signs up by providing their username, email, and password, which are then stored in the "User" data model after being encrypted with a salt.
• User Login:
  • When a user logs in, he API verifies their credentials by querying the "User" data model and checking if the provided password matches the encrypted password stored in the database. If the credentials are valid, the API generates an authentication token and stores it in the "Token" data model.
• Token Verification:
  • When a user makes a request to a protected resource, the API verifies the token by querying the "Token" data model and checking if the provided token is valid and has not expired. If the token is valid, the API returns the requested resource.
• User Logout:
  • When a user logs out, the API removes the corresponding token from the "Token" data model, effectively invalidating the token and ending the user's session.
• Note: The exact implementation of the API may vary based on the specific requirements and software stack of the project.

Authorization Service

• Verifying the authorization of a user to access specific resources.
• Role: id, name, description
• Permission: id, name, description
• UserRole: id, user_id, role_id
• User Role Assignment:
  • The API allows an administrator to assign roles to users, which are stored in the "UserRole" data model. A user may have multiple roles.
• Permission Assignment
  • The API allows an administrator to assign permissions to roles, which are stored in the "RolePermission" data model. A role may have multiple permissions.
• User Authorization:
  • When a user makes a request to a protected resource, the API verifies the user's authorization by checking the "UserRole" data model to see what roles the user has, and then checking the "RolePermission" data model to see what permissions the user has. If the user has the necessary permissions, the API returns the requested resource.
• Access Denied:
  • If the user does not have the necessary permissions, the API returns an "Access Denied" error, indicating that the user is not authorized to access the requested resource.

User Account Service

• Managing user accounts, including creating, updating, and retrieving account information.
• User: id, username, email, password, salt
• Profile: id, user_id, first_name, last_name, address
• User Signup:
  • A user signs up by providing their username, email, and password, which are then stored in the "User" data model after being encrypted with a salt.
• User Login:
  • When a user logs in the API verifies their credentials by querying the "User" data model and checking if the provided password matches the encrypted password stored in the database. If the credentials are valid, the API returns a success response and allows the user to access the requested resource.
• User Profile Management:
  • The API allows users to update their profile information, such as their name, address, and contact details, which are stored in the "Profile" data model.
• User Account Management:
  • The API allows users to update their account information, such as their password and email address, which are stored in the "User" data model.

Session Management Service

• managing user sessions and handling logouts.

• Session: id, user_id, start_time, end_time, ip_address

• User Login:

  • When a user logs in, the API generates a session token and stores it in the "Session" data model, along with the user's identifier and the time of creation.

• Session Validation:

  • When a user makes a request to a protected resource, the API verifies the user's session by checking if the provided session token exists in the"Session" data model, and if it has not expired. If the session is valid, the API returns the requested resource.

• Session Renewal:

  • The API allows users to refresh their session by generating a new session token and updating the "Session" data model.

• Session Termination:

  • When a user logs out or their session expires, the API removes the corresponding session token from the "Session" data model, effectively ending the user's session.

• Single Sign-On Service (OAuth PKCE example):

  • coordinating the SSO process and providing a single entry point for users to access multiple applications.
  • Application: id, name, redirect_uri
  • UserApplication: id, user_id, application_id
  • User Login: When a user logs in, the API generates a unique code verifier and sends a request to the authorization server using the OAuth 2.0 protocol and the PKCE (Proof Key for Code Exchange) extension. The authorization server returns an authorization code, which is then exchanged for an access token.
  • Token Validation: The API validates the access token by checking if it has been issued by a trusted authorization server and if it has not expired. If the token is valid, the API returns the requested resource.
  • Token Renewal: The API allows users to renew their access token by sending a request to the authorization server, which returns a new access token.
  • Token Revocation: The API allows users to revoke their access token by sending a request to the authorization server, which invalidates the token.

referencs:

• PKCE: https://auth0.com/docs/get-started/authentication-and-authorization-flow/call-your-api-using-the-authorization-code-flow-with-pkce
• Which OAuth 2.0 Flow Should I Use?
  • https://auth0.com/docs/get-started/authentication-and-authorization-flow/which-oauth-2-0-flow-should-i-use

Identity Provider Service

• providing user identity information to other services.
• User: id, username, email, password, salt
• User Management: The API allows users to create, update, and delete their profiles by updating the "User" data model.
• User Authentication: When a user logs in, the API verifies their credentials by querying the "User" data model and checking if the provided password matches the encrypted password stored in the database. If the credentials are valid, the API generates a token and returns it to the user.

Token Management Service

• managing and storing authentication tokens.
• Token: id, user_id, token, expiry_date
• Token Generation: The API generates tokens for users by encoding user-specific information into a compact and secure format, such as a JSON Web Token (JWT). The encoded information could include the user's identifier, roles, and any other relevant information.

Profile Service

• Profile: id, user_id, first_name, last_name, address, phone_number, date_of_birth

Additional Diagrams

Sequence Diagram of OAUTH PKCE

Diagram Flowchart