Challenge Overview
Welcome to Quartz Energy - Offset Operator Frac Schedule REST API Code Challenge
Offset Operator Frac Schedule is an application that helps operators so they can easily log on to and share upcoming frac information.
For this challenge we need your help to building backend data model and NodeJS REST API for Offset Operator Frac Schedule application by analyze provide storyboard screens that just completed on our previous Topcoder Design Challenge.
On challenge specification we analyzed the storyboard design screens and wrote down the routes that we think that will be proper for this system. As part of this challenge, you are highly encouraged to review the storyboard workflow/content and suggest any improvements. You also need create best practice table/collection on each section on MS SQL Server database.
REST API Technology Requirement
We will use following technology stack in this challenge :
- NodeJS
- MS SQL Server
- Stand-alone SMTP Email Server
Swagger Challenge Result
https://drive.google.com/open?id=1NZ75bX9W6A7MXoM-IG_BjwdaSw7NWmer
REST API Requirements
Generals
- We will use NodeJS to build the REST API.
- We might host our MS SQL Server database on AWS OR let us know any better suggestions. Make sure your submission configuration can support that.
- Always refer to provided Swagger file when build the REST API
- Create Database schema for each table/collections based data/fields displayed on provided storyboard screens
- You’re encouraged to improve/suggest proper Database based on existing
- It is preferred if you provide scripts and steps to deploy the REST API application in Heroku
- You also might need host the MS SQL Server on the cloud for testing.
- You will build REST API for these following pages, here is list of endpoints to implement, you can suggest changes but you need to post in forums for confirmation and discussion:
1). Login
- Create the following endpoints to create, update, delete, retrieve, and retrieve all user collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847868
Route: POST /login
Parameters :
- username
- password
Logics:
- Create some sample data for users
- Validate username/password and login the user.
- If the authorization are valid, then use JWT to generate token, with configurable expiration date, then return generated authorization token in response.
2). Learn More
- Create the following endpoints to create, update, retrieve learn more collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847870
Route: GET /learnmore
Parameters :
- n/a
Logics:
- Pull data for title, description & image
- No need authentication
3). Dashboard - Map View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847869
Route: GET /dashboard
Parameters :
- refer to filtering information below
Logics:
- Endpoints require valid authorization header
- Display Schedule summary like on top left of screenshot
- Need use clusterer marker on map, you can see some numbers on dot represents total operator.
- When zoom in the map need display scattered dots on map.
- If there’s 1 operator need display the details information like this: https://marvelapp.com/cb7ggce/screen/40847872 & https://marvelapp.com/cb7ggce/screen/40847871
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
User information
- Based on screenshot we need store user information that will be displayed on the bottom left of left sidebar
- Click the Logout button need logout the user
Search
- This is out of challenge scope.
4). Dashboard - List View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847875
Route: As possible need re-use same endpoint like on /dashboard but we need able to sort and set pagination for this list view
Parameters :
- refer to filtering information below
- refer to pagination information below
- Need able to sort the result based on all table columns
Logics:
- Endpoints require valid authorization header
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Pagination
We need to add pagination when user scrolling the page, these endpoint should have the following parameters :
- offset (default 0)
- limit (default 15)
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
5). Dashboard - Chart View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847878
Route: As possible need re-use same endpoint like on /dashboard but we need able to filtering for this chart view
Parameters :
- refer to filtering information below
Logics:
- Endpoints require valid authorization header
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
6) Upload Data
- Create the following endpoints to create, update, delete, retrieve, retrieve all upload data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847879
- Route: POST /upload
Parameters:
- Need able to detect Single Upload or Batch Upload
Single Upload
- companyname (required)
- wellname (required)
- fracstartdate (required)
- fracsenddate (required)
Batch upload Upload
- document (required)
Logics:
- Need able to capture both Single Upload and Batch Upload everytime user submitted the form
- As part of this challenge you need create SMTP Email Server that will send email message after successfully upload data
- Might need call another routes for auto suggest well name, refer details below.
- Suggest best solution to store the document file in MS SQL Server
- Response should display the created data.
Auto suggest Well Name
Screenshot: https://marvelapp.com/cb7ggce/screen/40847882 & https://marvelapp.com/cb7ggce/screen/40847881
- Route: GET /well
Parameters:
- wellname
Response:
- surfacelat (required)
- surfacelong (required)
- bottomholelat (required)
- bottomholelong (required)
- targetinformationtvd (required)
7). Company Page
- Create the following endpoints to create, update, delete, retrieve, and retrieve all company collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847886
Retrieve All company and contacts list
Route: GET /companies
Parameters:
- n/a
Response:
- companyid
- companyname
- lastsubmission
- contactlist:
-- contactid
-- contactorder
-- contactname
-- contactrole
-- contactemail
-- contactephone
-- contactlocation
-- contactemail
Add new company
Route: POST /companies
Parameters:
- companyname
- lastsubmission
Response:
- Response should be created company
Edit company
Route: PUT /companies/{id}/contact/
Parameters:
- companyname
Response:
- Response should be updated company
8). Contact
- Create the following endpoints to create, update, delete, retrieve, and retrieve all contacts collections.
Add Contact
Screenshot: https://marvelapp.com/cb7ggce/screen/40847864
Route: POST /companies/{id}/contact/
Parameters:
- companyid
- contactorder
- fullname
- email
- phone
- city
- state
Logic:
- Suggest best solution to store the picture file in MS SQL Server
- Response should be created contact and company information.
Edit Contact
Screenshot: https://marvelapp.com/cb7ggce/screen/40847864
Route: POST /companies/{id}/contact/
Parameters:
- companyid
- contactorder
- fullname
- email
- phone
- city
- state
Logic:
- Suggest best solution to store the picture file in MS SQL Server
- Response should be created contact and company information.
9). Upcoming Schedule
- Create the following endpoints to create, update, delete, retrieve, and retrieve all upcoming collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847866
Route: GET /upcoming
Parameters :
- refer to filtering information below
- refer to pagination information below
- Need able to sort the result based on all table columns
Logics:
- Endpoints require valid authorization header
- Need grouping per month
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Pagination
We need to add pagination when user scrolling the page, these endpoint should have the following parameters :
- offset (default 0)
- limit (default 15)
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Validation
Perform the following validation in each API endpoint :
Path parameters :
- They usually represents the ID of existing objects, a validation should be done that the entity with ID exists, otherwise return error.
- Required input parameters in create endpoints must be present. For update endpoints we can pass only the fields that should be updated.
- Required input parameters should be validated against their expected type.
- Optional input parameters should be validated against their expected type if provided.
- Foreign keys must be validated.
- Validation between callbacks must be performed.
Errors should be in json format with three fields :
- code : http status code
- status : success/failed
- message : reason of failure
Request and Response
- GET endpoints parameters will accept parameters as query strings, in key/value format.
- Create/Update endpoint parameters will accept input as JSON format.
- Exclude file upload endpoints which cannot work with application/json content type.
- Response should be in json format always.
Unit Testing
All of your code should be test covered. You must cover at least +90% of the code.
You need to test all cases and in test case assertion you need to assert accuracy of all fields.
Hosting
- It is preferred if you provide scripts and steps to deploy the application in Heroku
Documentation
- Provide a detailed README documentation for how to setup and configure the application.
Configurations
- You are expected to use environment variables to store sensitive information and environment-specific configurations.
Scripts
- Provide scripts for all tables/collection
- Provide script to insert default users.
- Provide script to insert sample data
Postman Client
- Create postman environment and collection file that will listing all calls and sample data.
- Group each routes on Postman collection into separate folder
- Provide description for endpoints
- Get Started with Postman : http://www.getpostman.com/
- All source code that implement the challenge requirements.
- README in markup language
- Verification document contains steps to verify your solution.
- Scripts input file.
Offset Operator Frac Schedule is an application that helps operators so they can easily log on to and share upcoming frac information.
For this challenge we need your help to building backend data model and NodeJS REST API for Offset Operator Frac Schedule application by analyze provide storyboard screens that just completed on our previous Topcoder Design Challenge.
On challenge specification we analyzed the storyboard design screens and wrote down the routes that we think that will be proper for this system. As part of this challenge, you are highly encouraged to review the storyboard workflow/content and suggest any improvements. You also need create best practice table/collection on each section on MS SQL Server database.
REST API Technology Requirement
We will use following technology stack in this challenge :
- NodeJS
- MS SQL Server
- Stand-alone SMTP Email Server
Swagger Challenge Result
https://drive.google.com/open?id=1NZ75bX9W6A7MXoM-IG_BjwdaSw7NWmer
REST API Requirements
Generals
- We will use NodeJS to build the REST API.
- We might host our MS SQL Server database on AWS OR let us know any better suggestions. Make sure your submission configuration can support that.
- Always refer to provided Swagger file when build the REST API
- Create Database schema for each table/collections based data/fields displayed on provided storyboard screens
- You’re encouraged to improve/suggest proper Database based on existing
- It is preferred if you provide scripts and steps to deploy the REST API application in Heroku
- You also might need host the MS SQL Server on the cloud for testing.
- You will build REST API for these following pages, here is list of endpoints to implement, you can suggest changes but you need to post in forums for confirmation and discussion:
1). Login
- Create the following endpoints to create, update, delete, retrieve, and retrieve all user collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847868
Route: POST /login
Parameters :
- username
- password
Logics:
- Create some sample data for users
- Validate username/password and login the user.
- If the authorization are valid, then use JWT to generate token, with configurable expiration date, then return generated authorization token in response.
2). Learn More
- Create the following endpoints to create, update, retrieve learn more collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847870
Route: GET /learnmore
Parameters :
- n/a
Logics:
- Pull data for title, description & image
- No need authentication
3). Dashboard - Map View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847869
Route: GET /dashboard
Parameters :
- refer to filtering information below
Logics:
- Endpoints require valid authorization header
- Display Schedule summary like on top left of screenshot
- Need use clusterer marker on map, you can see some numbers on dot represents total operator.
- When zoom in the map need display scattered dots on map.
- If there’s 1 operator need display the details information like this: https://marvelapp.com/cb7ggce/screen/40847872 & https://marvelapp.com/cb7ggce/screen/40847871
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
User information
- Based on screenshot we need store user information that will be displayed on the bottom left of left sidebar
- Click the Logout button need logout the user
Search
- This is out of challenge scope.
4). Dashboard - List View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847875
Route: As possible need re-use same endpoint like on /dashboard but we need able to sort and set pagination for this list view
Parameters :
- refer to filtering information below
- refer to pagination information below
- Need able to sort the result based on all table columns
Logics:
- Endpoints require valid authorization header
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Pagination
We need to add pagination when user scrolling the page, these endpoint should have the following parameters :
- offset (default 0)
- limit (default 15)
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
5). Dashboard - Chart View
- Create the following endpoints to create, update, delete, retrieve, retrieve all data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847878
Route: As possible need re-use same endpoint like on /dashboard but we need able to filtering for this chart view
Parameters :
- refer to filtering information below
Logics:
- Endpoints require valid authorization header
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Region (default all)
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
6) Upload Data
- Create the following endpoints to create, update, delete, retrieve, retrieve all upload data.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847879
- Route: POST /upload
Parameters:
- Need able to detect Single Upload or Batch Upload
Single Upload
- companyname (required)
- wellname (required)
- fracstartdate (required)
- fracsenddate (required)
Batch upload Upload
- document (required)
Logics:
- Need able to capture both Single Upload and Batch Upload everytime user submitted the form
- As part of this challenge you need create SMTP Email Server that will send email message after successfully upload data
- Might need call another routes for auto suggest well name, refer details below.
- Suggest best solution to store the document file in MS SQL Server
- Response should display the created data.
Auto suggest Well Name
Screenshot: https://marvelapp.com/cb7ggce/screen/40847882 & https://marvelapp.com/cb7ggce/screen/40847881
- Route: GET /well
Parameters:
- wellname
Response:
- surfacelat (required)
- surfacelong (required)
- bottomholelat (required)
- bottomholelong (required)
- targetinformationtvd (required)
7). Company Page
- Create the following endpoints to create, update, delete, retrieve, and retrieve all company collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847886
Retrieve All company and contacts list
Route: GET /companies
Parameters:
- n/a
Response:
- companyid
- companyname
- lastsubmission
- contactlist:
-- contactid
-- contactorder
-- contactname
-- contactrole
-- contactemail
-- contactephone
-- contactlocation
-- contactemail
Add new company
Route: POST /companies
Parameters:
- companyname
- lastsubmission
Response:
- Response should be created company
Edit company
Route: PUT /companies/{id}/contact/
Parameters:
- companyname
Response:
- Response should be updated company
8). Contact
- Create the following endpoints to create, update, delete, retrieve, and retrieve all contacts collections.
Add Contact
Screenshot: https://marvelapp.com/cb7ggce/screen/40847864
Route: POST /companies/{id}/contact/
Parameters:
- companyid
- contactorder
- fullname
- phone
- city
- state
Logic:
- Suggest best solution to store the picture file in MS SQL Server
- Response should be created contact and company information.
Edit Contact
Screenshot: https://marvelapp.com/cb7ggce/screen/40847864
Route: POST /companies/{id}/contact/
Parameters:
- companyid
- contactorder
- fullname
- phone
- city
- state
Logic:
- Suggest best solution to store the picture file in MS SQL Server
- Response should be created contact and company information.
9). Upcoming Schedule
- Create the following endpoints to create, update, delete, retrieve, and retrieve all upcoming collections.
Screenshot: https://marvelapp.com/cb7ggce/screen/40847866
Route: GET /upcoming
Parameters :
- refer to filtering information below
- refer to pagination information below
- Need able to sort the result based on all table columns
Logics:
- Endpoints require valid authorization header
- Need grouping per month
- Display all fields like on screenshot
Filtering
Screenshot: https://marvelapp.com/cb7ggce/screen/40847874
- Operator (default all)
- Date Range (default 30)
- Custom Range
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Pagination
We need to add pagination when user scrolling the page, these endpoint should have the following parameters :
- offset (default 0)
- limit (default 15)
The parameters should be returned in the response with the total count of matching records as well, It is preferred if the default values are configurable.
Validation
Perform the following validation in each API endpoint :
Path parameters :
- They usually represents the ID of existing objects, a validation should be done that the entity with ID exists, otherwise return error.
- Required input parameters in create endpoints must be present. For update endpoints we can pass only the fields that should be updated.
- Required input parameters should be validated against their expected type.
- Optional input parameters should be validated against their expected type if provided.
- Foreign keys must be validated.
- Validation between callbacks must be performed.
Errors should be in json format with three fields :
- code : http status code
- status : success/failed
- message : reason of failure
Request and Response
- GET endpoints parameters will accept parameters as query strings, in key/value format.
- Create/Update endpoint parameters will accept input as JSON format.
- Exclude file upload endpoints which cannot work with application/json content type.
- Response should be in json format always.
Unit Testing
All of your code should be test covered. You must cover at least +90% of the code.
You need to test all cases and in test case assertion you need to assert accuracy of all fields.
Hosting
- It is preferred if you provide scripts and steps to deploy the application in Heroku
Documentation
- Provide a detailed README documentation for how to setup and configure the application.
Configurations
- You are expected to use environment variables to store sensitive information and environment-specific configurations.
Scripts
- Provide scripts for all tables/collection
- Provide script to insert default users.
- Provide script to insert sample data
Postman Client
- Create postman environment and collection file that will listing all calls and sample data.
- Group each routes on Postman collection into separate folder
- Provide description for endpoints
- Get Started with Postman : http://www.getpostman.com/
Final Submission Guidelines
Deliverables- All source code that implement the challenge requirements.
- README in markup language
- Verification document contains steps to verify your solution.
- Scripts input file.