Lab 3

NYC has a roster of Health Inspectors that carry out restaurant inspections. In this Lab you will create an application to track Health Inspectors in the nyc_inspections schema via a RESTful API. Health Inspectors should log in to the application by providing a valid username and password. The application should then call web API methods to create, read, update, or delete Health Inspectors as described below. The API methods should not take action unless the user is properly authenticated.

You may work with one partner on this Lab. If you work with a partner, both partners should submit the same code and will receive the same grade. Provide the names of each partner in the comment section on Canvas.

Exercises

Write code for both the server side as well as the client side as follows:

Server side

Create an Employees table in your nyc_inspections schema to track the Health Inspectors, you pick the attributes, but they should be what you would expect an organization to track about its employees including: date of hire, salary, and whether the Inspector has admin privileges. You'll also want to store each Inspector's username and password.
Do NOT to store passwords in plain text! Instead hash and salt them (pepper if you'd like). I recommend using bcrypt. That package will automatically generate a salt for you and will combine it with the hash digest using bcrypt's hash function (e.g., you can store the hashed password and salt in one attribute in your table, see technique #2 in the link above) and can also determine if a plain-text password matches a stored hashed and salted digest using the compare function. The compare function extracts the salt from the stored digest, then salts and hashes the plain-text password, compares the result with the stored digest, and returns true if they match, false otherwise
Use Node.js on your local machine to create RESTful methods to create, read (both one Inspector given an ID or all Inspectors if no ID is provided), update, and delete Inspectors from the table using the proper HTTP verbs. You may use api.js from class as a scaffold. You will see however, that api.js does not have SQL code for POST, PUT, or DELETE verbs, you will need to flesh out those methods. There are many, many tutorials on the web about creating web APIs if you get stuck
Create a new database user that your API will use to access the database, give this user the minimal needed permissions necessary to carry out the API methods
Inspectors with admin privileges (indicated by the Admin attribute of your Employees table) can create, read, update, and delete any Inspectors in the Employees table, but an Inspector without admin rights can only read and update data about themselves
Provide a means for Inspectors to log in, allowing access to your RESTful API only after an Inspector has authenticated by providing a valid username and password. For simplicity, you may provide the username and password as parameters each time you make an API call. Your API methods should first confirm the username and password are valid before taking further action.

Client side

Create a client-side application program that allows users to create, read, update, or delete Health Inspectors in your Employees table (provided they have rights as described above) by calling your RESTful API methods
Write your client-side code in Python (you may use call_api.py from class as a scaffold for this portion of the Lab)
Your solution need not be aesthetically beautiful, but it should be functional
- You can use Python's GUI tools such as Tk Inter, or
- Your solution could be purely command line driven (e.g., press 1 to create a new Inspector, press 2 to Update an Inspector, ...).

Submission Instructions

Submit the following in a zip file:

SQL to create the Health Inspector table
Node.js code including package.json and your API code (do not provide the node_modules directory, I will use package.json to download any needed packages)
Your client-side Python application code to call the API methods.

Remember, if you work with a partner, each partner should submit the same solution and should note the names of each partner in the comments section on Canvas.

Extra Credit

Implement the client side in HTML/JavaScript using one of the user authentication/authorization methods available in Node.js such as JSON Web Tokens (JWT) or Passport with bcrypt and sessions.

Another option is to implement more sophisticated rights management. Instead of simply checking for the presence of admin rights, allow different levels of access (e.g., some users can see all other users, some users can only see data about themselves, perhaps only super-supervisors are able to delete Inspectors.