Topcoder Project Service - Workstreams

Introduce new data models and endpoints for handling work streams.

Project Background

Topcoder Project Service is the main backend service of Topcode Connect – client facing application of Topcoder.

Topcoder Project Service keep data of projects and let us manage work done inside these projects using phases. In this challenge, we are going to introduce a new model WorkStream which would combine phases (which we would call works) into work streams.

Technology Stack

• Node.js

• PostgreSQL

Code access

The work for this challenge has to be done in one repository:
- Project Service repo https://github.com/topcoder-platform/tc-project-service branch dev commit d206ad1ba570741a0513fdcae9a63384c4fae914 or later.

- Config for local setup is provided on the forum.

Individual requirements

1. WorkStream model

• Add WorkStream model:
  id bigint,
  name varchar(255),
  type varchar(45),
  status enum (draft, reviewed, active, paused, completed), // define in constants file
  projectId bigint,
  createdAt timestamp,
  updatedAt timestamp,
  createdBy bigint,
  updatedBy bigint,
  deletedAt timestamp,
  deletedBy bigint

• Implement next endpoints:
  - GET /projects/{projectId}/workstreams (permission: projectView)
  - GET /projects/{projectId}/workstreams/{id} (permission: projectView)
  - POST  /projects/{projectId}/workstreams (permission: projectAdmin)
  - PATCH  /projects/{projectId}/workstreams/{id} (permission: workManagementForTemplate, see below)
  - DELETE  /projects/{projectId}/workstreams/{id} (permission: projectAdmin)

2. WorkStreams to Phases relationship

We need to keep one-to-many relationship (via cross ref table) between WorkStream and Phase models. So that one work stream can have multiple phases. We don’t wanna use a foreign key to implement one-to-many relationship here as terminology is constantly changing, so we would like to keep these models separate by utilizing the join table.

• For this purpose, we can use sequalize’s belongsToMany method, something like WorkStream.belongsToMany(Phase, { through: 'PhaseWorkStream' })

• Implement next endpoints. Note that we are calling a phase as work in these endpoints.
  - GET /projects/{id}/workstreams/{id}/works (permission: projectView)
  - GET /projects/{id}/workstreams/{id}/works/{id} (permission: projectView)
  - POST /projects/{id}/workstreams/{id}/works (permission: workManagementForTemplate, see below)
  - PATCH /projects/{id}/workstreams/{id}/works/{id} (permission: workManagementForTemplate, see below)
  - DELETE /projects/{id}/workstreams/{id}/works/{id} (permission: workManagementForTemplate, see below)

3. Workitems endpoints

We already have one-to-many relationship between Phase and PhaseProduct models.

• We would like to utilize this relationship and implement the next endpoints to manage PhaseProducts of Phases inside WorkStreams.  In these endpoints, we are calling phase as work and phaseProdut as workItem.
  - GET /projects/{id}/workstreams/{id}/work{id}/workitems (permission: projectView)
  - GET /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (permission: projectView)
  - POST /projects/{id}/workstreams/{id}/work{id}/workitems (permission: workManagementForTemplate, see below)
  - PATCH /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (permission: workManagementForTemplate, see below)
  - DELETE /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (permission: workManagementForTemplate, see below)

4. workManagementForTemplate permission

We have several permission handlers which we define per policy. We would like to introduce a new handler workManagementForTemplate which would check permissions based on the project template used by the project.

• Create a new model WorkManagementPermissions:
  id bigint NOT NULL,
  policy varchar(255) NOT NULL, // e.g. 'workStream.edit', ‘workItem.create, ‘work.delete’ etc
  allowRule json NOT NULL,
  denyRule json,
  "projectTemplateId" bigint NOT NULL,
  "deletedAt" timestamp with time zone,
  "createdAt" timestamp with time zone,
  "updatedAt" timestamp with time zone,
  "deletedBy" bigint,
  "createdBy" integer NOT NULL,
  "updatedBy" integer NOT NULL,
  // constrains:
  PRIMARY KEY (id) autoinceremental
  UNIQUE(policy, projectTemplateId)

• allowRule and denyRule would have the next JSON format:
  {
   projectRoles: ['customer', 'manager'],
   topcoderRoles: ['Connect Admin', 'administrator']
  }
  Where projectRoles may contain any value of PROJECT_MEMBER_ROLE. And topcoderRoles may contain any value of USER_ROLE. Any of these arrays may be absent.

• The logic of workManagementForTemplate would be the following:
  - knowing projectId from the URL get the project object from DB
  - project object has templateId value
  - using the templateId (which is projectTemplateId) and policy ('workStream.edit', ‘workItem.create, ‘work.delete’ etc) find the WorkManagementPermissions record
  - if such record is not found - access is denied
  - if such record is found first we check if user match allowRule: either user has some projectRoles as member of the project or user has some topcoderRoles as Topcoder user
  - if user matches any rule in allowRule we check if there are denyRule which current user match. If user match allowRule, but not denyRule - access allowed.

General requirements

• All the endpoints should fetch from DB, no ElasticSearch indexing is needed for these models.

• For DELETE endpoints we should do soft-delete as we do in most other existent endpoints.

• Unit tests should be implemented for new endpoints and test the main workflows. Follow the existent test for reference. For the new endpoints, in particular, we should test cases where the user has permissions and doesn’t have permissions to access them.

• Also, we should write valuable tests to make sure the workManagementForTemplate handler correctly checks permissions. At least the next cases should be tested (and any other critical paths):
  - should deny access if there are no permission records
  - should deny access if user doesn’t match allowRule
  - should deny access if user match allowRule and match denyRule
  - should allow access if user match allowRule and doesn’t match denyRule
  - should allow access if user match projectRoles, but not topcoderRoles
  - should allow access if user match topcoderRoles, but not projectRoles

• Create corresponding SQL migration script in /migrations folder to support model changes in the DB.
  Validate that migration script is correct and submission works if we use migration SQL over the old DB structure.

• Follow the existent code standards and approaches.

• Update Swagger. Add new endpoints and create definitions where necessary.

• Update Postman file. Add new endpoints with valid data so they can be called for easy testing.

• Existent unit tests should pass.

• Lint should pass.

Verification

Note, we have a script to populate some demo data https://github.com/topcoder-platform/tc-project-service#import-sample-metadata--projects which could be helpful, as it populates projects and projectTemplates.
Provide a brief guide on how to test the submission. In particular, include steps we should follow to test workManagementForTemplate handler with examples of data to fill the WorkManagementPermissions model.

Final Submission Guidelines

• Git patch.

• Briefe verification guide.