ABC - Missing Details Portal Architecture

Challenge Objectives
For the purpose of this challenge, we need the architecture to be defined for the Missing Details Portal which will be a web app using node.js and SQL Server 2012 for the backend, and ReactJS for the frontend.
 
Project Background

• This application will be used to manage and process all missing info queries for invoicing in a standardized manner. It will allow users to remove email, Skype or phone requests logging.
• We expect the application will help improve our way to provide reporting, tracking and managing queries.
• After this challenge, we’ll run multiple code challenges to implement the api and then integrate the frontend with the backend to build the final application.

 
Technology Stack

• Backend: Node.js v8
• Database: SQL Server 2012
• Frontend: ReactJS
• Deployment environment: Linux
• File Storage Service: local disk
• Email Service: SMTP

 
Challenge requirements
For the purpose of this challenge, you must complete the following:

1. Define the complete data model and REST endpoints (using JSON as payload) to support all features in the requirements and wireframes, please provide this in swagger format and include implementation notes for the endpoints
2. Provide a standard Application Design Specification (check here for a template) to describe the high level architecture
3. Provide overall architecture diagrams (both functional and technical), you can include this in the Application Design Specification. Functional diagrams should cover each functional components in a layered way / structured way , which would be implemented in this solution. Then, technical diagrams should cover the details to implement them.
4. Provide high level data flow diagram(s), you can include this in the Application Design Specification
5. Provide a complete mapping file from requirements and screens in the wireframes to the api endpoints
6. Please note that the application is supposed to use customer’s SSO for login, but since we don’t have access to that we’ll need to mock it for now:
   1. We need a standalone mock sso login module to be defined (that’s not part of the Missing Details Portal), we expect this module to return user’s role if login is successful, this module should have a list of configurable users who can log in.
   2. The Missing Details Portal needs to have its own login endpoint which will be called by the Missing Details Portal frontend. This endpoint will utilize the mock sso login module to perform login, and if that succeeds it will generate and save the user’s token in the database in a secure way. The frontend will need to include the token in all subsequent calls for authorization purposes.

 
Security requirements
The following requirements are critical so your submission must cover them with enough details:

1. Security architecture  (standard web security to be covered like OWASP 2013 etc.),  which need to be implemented as part of this solution. Please provide details on this so coders know what to follow to meet the standards. You can check this document for more details: https://www.owasp.org/images/f/f8/OWASP_Top_10_-_2013.pdf
2. Data security approach
3. Data storage security approach
4. User Access security & roles, responsibilities

 
Individual requirements
Below is the list of all features that are supposed to be implemented in the application, and hence need to be covered by the architecture. Please cross compare this with the wireframes, and if you see any inconsistencies or have any questions, raise them in the forum for clarification / confirmation.
 
Before you check the individual requirements below, please keep these notes in mind:

• The current contract admin dashboard is wrong, it will be updated to match Delivery User/Management User dashboard PLUS 2 additional buttons for single query creation and bulk query creation
• 3 new fields will be added to the table content (SDM comments, SDM attach and Rework reason)
• Expandable view is mentioned in multiple places below which is currently missing in the wireframes. There is nothing special but simply a way to expand a row to see more fields, these are the 10 fields that should be only visible in expanded view:
  • Billing Index
  • Billing Start Date
  • Billing End date
  • Value to be billed
  • Currency
  • Date & Time Closed
  • Requestor
  • SDM comments
  • SDM attach
  • Rework reason

 
01) Login (All Roles):

• Users will have to log into the portal using their company credentials (Emp ID + Virtual badge pin)
• Needs error scenario covered for wrong login
• Global login form, the user roles will be done by the back end (Delivery user or Contract Admin, or other type of users)

 
02) Delivery User Features:

• After successful login, user will see the dashboard screen and will be able to see all the missing info queries that were assigned on their names
• This user roles will be able to:
  • Sort/filter based on all the fields available in the header view
  • To expand the view and see additional query details
  • To download any attached file
  • To export data in .xls format
  • To reassign queries to a different Delivery User, if wrongly assigned by the CA
  • To add "Watchers" to any of the open queries
  • Select multiple queries and answer all of them at once; the comment and attachments for all answered queries will be common;

• If the user is a back-up for another user, they will have the option to select "See all queries" and filter based on the assigned to / requestor name, and then they will be able to reassign the open queries to themselves in order to be able to respond or receive responses for those one.
• When clicking on the "See all queries" button, all the queries with "New" and "Open" status will be visible on my screen, even if they are assigned to another person. Only the "SDM" field will be editable and I will have the possibility to re-assign the query on my name by changing the email address in the "SDM" field. Then all the fields will become editable and I will be able to respond them
• The tool should allow single or multiple selection for reassignment
• The assignment will be done by a Contract Admin using the online standardized form for raising queries to ADMs/SDMs/Client Managers or a desktop automation which will upload the requests directly into the portal DB
• The portal will do a lookup in the DB and display only those queries that have the User's email address filled in the "SDM" field
• Expandable view, This option (button) will have to allow users to expand the user view and see all the details/fields that will be available for each and every query.
• Responding single query, When clicking on the Query ID hyperlink, a pop-up screen will show up; all the details of that query will be displayed, as described in the BRD. Any text added in the Comments field will be stored in the DB on the "SDM comments" field. Any file attached by the SDM User will be stored in the DB in the "SDM attachments" field. Once clicking on the "Submit" button, the portal will update the field "Status" in the DB, from Open to Closed
• Rejecting single query, The SDM user will be able to add the response comments in a comments field. When clicking on the "Reject" button, the list of "Rejection reasons" will pop-up and the user will have to select one in order to be able to close the query. The comments and the rejection reason will have to be updated in the "SDM comments" and "Rejection reason" fields, directly in the DB. Closure timestamp will also be captured in the "Closed time & date" field and the "Status" field will be changed from Open to Rejected.
• Multiple query closure, When clicking on "Respond multiple queries" button, a selection box will be made available for each and every open query. When selecting multiple lines and clicking on "Respond" the responding pop-up screen will show-up, displaying the list of selected queries. The "Comments", "Attachments" and "Rejection reason" (where applicable) will be common for all selected queries and will be stored in the DB accordingly. When clicking on "Submit"/"Reject", the "Status" field value will have to be updated in the DB for all the selected queries.
• When clicking on Download, the portal will record the fields from the user view (it might be expanded or collapsed view) and allow the user to save a .csv / .xls offline copy. If data is filtered on the user screen, the .csv file will contain only the filtered data.
• As a user, depending on my access privileges, clicking on the "Download Report" button will prompt a raw data file in .xls format for the selection that I am currently viewing.
• As a Business user, I need to be able to view all the opened queries, with their due-dates, and generate a report out of it
• As a Business user, I need to be able to view all aged queries by country, owner, team, query type
• Need the ability to upload attachments when responding in pop-up screen.
• Receive notifications for new/pending queries

 
03) Management User Features:

• View all open/closed/rejected queries
• Re-assign open queries to other users
• Extract reports

 
04) Contract Admin Features:

• The portal will do a lookup in the DB and display only those queries that have the User's email address filled in the "Requestor" field
• Add New Query, the Contract Admin (CA) users will have a button for creating new individual queries, which will open a pop-up screen with an online form, containing the fields marked with Green in the BRD document embedded file. The Submit button on the form will automatically update a new entry in the DB and will record timestamps
• Expandable view, This option (button) will have to allow users to expand the user view and see all the details/fields that will be available for each and every query.
• When clicking on Download, the portal will record the fields from the user view (it might be expanded or collapsed view) and allow the user to save a .csv / .xls offline copy. If data is filtered on the user screen, the .csv file will contain only the filtered data.
• As a user, depending on my access privileges, clicking on the "Download Report" button will prompt a raw data file in .xls format for the selection that I am currently viewing.
• Single query creation, as a contract admin, I will have the ability to log into the portal, access an online request form and update all the fields related to my missing information query (you can find the list of fields marked in green below in the “Data Fields” embedded file). If any additional information will be needed, I will have the ability to attach documents.
• Bulk query creation: contract admins will upload a csv file exported from other tools to this portal to bulk create queries, the csv template should be defined in this challenge based on the DB fields (in the BRD we have detailed which fields should be calculated or filled by the user). Once done, the server will send a completion report, to ensure that all my missing info queries have been created.
• Bulk query creation fallouts, If there's going to be any error during the bulk query creation process, the portal will have to send an email to the requestor, referencing the items that were not created.
• Need the ability to upload attachments as user input method.
• Need the ability to upload attachments when creating queries.
• Needs to be able to see all submitted queries
• Need to be able to view all the queries that have his / her email ID in the requestor field. The contract admin has to be able to reassign the open queries to a different SDM, and also be able to change the Requestor ID so that a colleague who’s backing that person up can see his / her open queries and receive responses.
• Receive notifications for closed/rejected queries
• Extract reports

 
05) Super User Features:

• E-mail notifications, Set Delivery/Management User email notification, The portal will have scheduled batch jobs (the frequency has to be defined; assumption: twice per day) which will lookup in the DB and retrieve all the queries with "New" and "Open" status assigned to each SDM user. Then will send an email to each user, providing the number of "New" and "Open" queries for each "Query Type". Please note the emails should be template based.
• E-mail notifications, Set Contract Admin user email notification, The portal will have scheduled batch jobs (the frequency has to be defined; assumption: twice per day) which will lookup in the DB and retrieve all the queries with "Closed", "Rejected" and "Open" status. Then will send an email to a generic email address, providing the number of "Closed", "Rejected" and "Open" queries for each "Requestor"
• Edit/update SLA table / KPI settings
• Manage Users
• Ignore the Roles page, it shouldn’t be there, the permissions for each role should be static.

 
Documentation
Register to download the following documents we provided in the forum

• Wireframes
• Requirements Spreadsheet:
  • Required modules.xlsx
  • Missing Info Portal BRD_05062018.docx (this also contains an embedded data fields spreadsheet which should help with database design)

Final Submission Guidelines

• Application Design Specification (with the mentioned diagrams and security details included)
• Swagger file covering all API endpoints and data models
• CSV template for bulk query creation
• Database schema for all tables
• Full mapping from requirements and screens to api endpoints