ABC - Missing Details Portal REST API Implementation

Challenge Objectives
For the purpose of this challenge, we need you to implement the REST API based on the architecture documents we provided in the forum.
 
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. Implement all the endpoints defined in the architecture using node.js
2. Keep the swagger documentation in sync with the code in case any changes are made during the challenge phase
3. Please pay special attention to the security requirements, including the OWASP guidelines that must be adhered to in the code implementation
4. Provide sql scripts to setup / clean / drop database tables, and separate sql or node.js script to populate test data for testing
5. Please note that in order to implement the /login endpoint, you also need to implement a separate mock sso login api (that’s not part of this suite of services) so our /login endpoint cann call that mock to decide if login is successful. The mock sso login can be simple and just have a list of configurable emails that can login. Only if the mock sso login returns login success should our /login endpoint proceed with the following logic:
   1. Check if the user is added into this app
   2. If not return login failure
   3. If yes generate a token for the user and save it securely in the database, return the user info to the api caller
6. As part of this challenge, the email sending job also needs to be implemented
7. Provide complete postman collection to test all the endpoints
8. Follow node.js best coding practices, and make sure all code is properly documented
9. Double check if all fields from “Fields Explained.xlsx” spreadsheet are implemented in the code.

 
Security requirements
Please pay attention to the following security requirements and make sure your code adheres to the guidelines:

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
5. DB username / password and / or DB connection string must be encrypted

 
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 API implementation. Please cross compare this with the architecture, and if you see any inconsistencies or have any questions, raise them in the forum for clarification / confirmation.
 
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

• Architecture Documents
• 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

• Full code that covers all requirements
• Swagger file that’s in-sync with the code
• Database scripts / test scripts / test files (bulk csv creation for example)
• Postman collection to test the API