Reading and Practice

Read about Node.js, Express, Postgres, and Sequelize.

Instructions

All homework in this class is to be done in a four- or five-person group. Do not “partition” the work: work together. Everyone is responsible for understanding the entire homework submission, so everyone should contribute to the entire assignment.

Please begin by creating a github repository for your team. It should be a private repository named <yourgithubname>/mini-spy.

Please make sure to add me as a committer. My github name is rtoal.

The Assignment

PLEASE NOTE: This assignment is a living document, meaning it is subject to frequent modification.

Changing requirements in the middle of a project is a fact of life. WE ARE SIMULATING REAL LIFE, PEOPLE! Students are responsible for keeping up with the changing and evolving requirements. Keep coming to class to stay abreast of the changes.

In this assignment, you will be building a REST web service using Node, Express, Postgres, and Sequelize. The following are your domain objects:

• A StaffMember has a first name, last name, user name, password hash, and roles, such as “Founder”, “Development Director”, “Director of Programs”, “Regional Coordinator”, “Senior Case Manager”, “Education and Employment Specialist”, “Outreach and Engagement Case Manager”, “Parenting Case Manager”, “Case Manager”, and “Arts and Healing Facilitator”.
• A Client has a first name, last name, nickname, birthdate, email, phone number, assigned case manager (who is also a User), and a case plan (which is just a text blob).
• A CaseNote is authored by a case manager (user) on a date, for a client. Its content is text, up to 2000 characters.
• A Program has a name (e.g., “Health and Wellness”, “Arts and Creativity”, “Legal”, “Advocacy”, “Education and Employment”, “Pregnancy and Parenting”, ...) and (optionally) a parent program. For example “Digital Arts Lab” is a subprogram of “Arts and Creativity”). A program has have one or more coordinators, who are staff members.
• Clients enroll in programs. We need to know the datetime the client enrolled.
• A program also has a set of activities. Each Activity has a name (e.g., “Medical Care”, “Dental Care”, “Medi-Cal Registration”, “HIV Testing”, “Mock Interviews”, “Life Skills”, ...), location, and the program it belongs to, of course.
• Activities are offered at certain time periods (with a start datetime and an end datetime). A client can sign up for these ActivityOfferings.
• ActivityOfferings can be rolled into DropInSessions. Clients can checkin to a dropin session, whether or not they have enrolled in any of the activity occurrences within the session. All we care about is the checkin datetime.

First (25 points), define Sequelize models (and migrations) for these domain objects. Run the migrations and check that your database has all the correct tables. Each table should have been build with an integer primary key (not null, autoincrement, etc.) Where there are many-to-many relations, ensure a join table is produced. You may have to iterate several times on building your database until you get it just right. This is okay, because that is sometimes the best way to learn.

Second (5 points), fill your database with sample data. We can generate this as a class. This can be done by writing either a script with SQL statements (not recommended) or a standalone JavaScript script that uses Sequelize to populate the tables (recommended).

Third (30 points), Practice your SQL! In a file called practice.sql, somewhere in your repository, give SQL queries for each of the following. (Please make sure to test! Malformed queries will not gain you any points.)

1. Get the first and last names of all staff members that are employment specialists.
2. Get the counts of staff managers for each role. List them in descending order by count.
3. Get all subprograms of the Arts and Creativity program.
4. Get all clients that enrolled in the Advocacy program during 2016.
5. Get all activity occurrences that began yesterday.
6. Get all programs that have an enrollee with an yahoo.com email address.
7. Get the text of most recent case note authored by Sherry Tseng.
8. Get all checkins to dropin sessions containing an activity occurrence for an activity in a program coordinated by Lisa Tanaka, sorted by date.
9. Get all activity occurrences with at least 25 enrollees, together with the number of enrollees and their location, sorted by number of enrollees descending.
10. List all activities sorted by their average number of enrollees per offering.

Fourth (60 points for the API, 30 points for the tests), build a REST API, using test-driven development if possible. The representation format must always be JSON; you can configure Express to handle all the serialization and deserialization and header management for you (we’ll do this in class). It is perfectly fine to use Basic Authentication only, but you can do OAuth if you prefer. (In either case, find a good library in NPM to do the hard work for you!) For tests, superagent is fine, but you can use any library you like.

Here are some endpoints to build:

POST staffmembers

Create a new staff member. Request body should be a JSON description of the staff member, without the id. Normally returns 201 or 401. Return 400 if the input is messed up in any way (bad JSON, id included, missing required information, extra fields, wrong type in fields, and so on).

GET staffmembers/{id}

Get the unique staff member with the given id. Normally returns 200, 401, or 404.

GET staffmembers[?role=&search=]

Get staff members. If the role parameter is present, restrict results to those staff members having the given role. If a search parameter is present, restrict results to those staff members whose first, last, or user name starts with (case insensitively) the parameter value. Normally returns 200 or 401. Not that a search returning zero results returns a 200 with an empty list.

PUT staffmembers/{id}

Similar to post, but replaces the staff member with the given id. If no staff member exists with the given id, respond with a 400.

More endpoint descriptions to follow.