New data models for work management

[vikasrohit]

We need to introduce new data models and need to update existing ones to achieve the requirements/features needed for the new work management strategy. Though, we don't have final designs of all input and display requirements, we do have the feature details and we can determine common fields out of those details. So, we can start creating the model and API endpoints around it to be ahead of time and later update the end points when we have more detailed UI designs.

Technically, there is one new data model called WorkStream that is going to be introduced and it would have schema like:

id bigint,
name varchar(255),
type varchar(45) or enum (dev, qa, design, deploy),
status enum (draft, reviewed, active, paused, completed), // we might not need this in final version
projectId bigint,
createdAt timestamp,
updatedAt timestamp,
createdBy bigint,
updatedBy bigint,
deletedAt timestamp,
deletedBy bigint

New endpoints:

GET /projects/{projectId}/workstreams (fetch directly from database)
GET /projects/{projectId}/workstreams/{id} (fetch directly from database)
POST  /projects/{projectId}/workstreams (only admins should be able to directly create the workstreams)
PATCH  /projects/{projectId}/workstreams/{id} (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to update the details while for others we don't)
DELETE  /projects/{projectId}/workstreams/{id} (only admins should be able to directly deleted the workstreams)

We need to keep one to many relationship (via cross ref table) between WorkStream and Phase object so that one work stream can have multiple phases. Phase object would remain mostly as is. For old projects a phase won't be associated with any work stream, so it is not mandatory for a phase to have workstream associate with it. After we have this relationship, following new endpoints are needed:

GET /projects/{id}/workstreams/{id}/works (we are calling a phase as work in new terminology without changing it in our data model. fetch phases from the database directly.)
GET /projects/{id}/workstreams/{id}/works/{id} (fetch directly from database)
POST  /projects/{id}/workstreams/{id}/works (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to create work for a worksstream while for others we don't)
PATCH  /projects/{id}/workstreams/{id}/works/{id} (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to update the details of a work while for others we don't)
DELETE  /projects/{id}/workstreams/{id}/works/{id} (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to delete a work while for others we don't)

Note We would still keep our existing endpoint of /projects/{id}/phases and all its child routes as is. These endpoints would serve the response for old projects as is while for new projects, which have workstreams, these endpoints would allow us to interact with all work for the project e.g. GET /projects/{id}/phases would return all the phases in the project no matter which workstream they are associated with. There would be no mention of workstream or work in the request or response for new projects when accessed via this old endpoints. I don't see any usage of these endpoint as of now, but for completeness and backward compatibility, we need to ensure this behaviour.

Now, next change in data model is to now utilize the one to many relationship between a Phase and PhaseProduct to capture the new details about associating a phase/work with challenge/task. Our idea is to keep PhaseProduct as object to keep details about a challenge/task associated with a phase. I guess, until we have final UI requirements get started, we can use this model as is and keep any details (e.g. challenge id, type, etc in details field of PhaseProduct). We might need to update the schema though as soon as we get more requirements around challenges/tasks association.
However, we would need new endpoints for serving PhaseProduct for new projects as follows:

GET /projects/{id}/workstreams/{id}/work{id}/workitems (fetch directly from database)
GET /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (fetch directly from database)
POST /projects/{id}/workstreams/{id}/work{id}/workitems (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to create work items for a worksstream while for others we don't)
PATCH /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to update the details of a work item while for others we don't)
DELETE /projects/{id}/workstreams/{id}/work{id}/workitems/{id} (it requires complex logic of authorization depending on the project template, for some templates we allow customers to be able to delete a work item while for others we don't)

[maxceem]

@vikasrohit, in general, it looks clear.

Have a few questions to make sure I've got everything right.

1. We need to keep one to many relationship (via cross ref table) between WorkStream and Phase object

   Do you mean that we have to create an additional model to implement this relation? Or just add one more field to the Phase model: workstreamId which would refer to a WorkStream or can be null.

2. Regarding /projects/{id}/phases endpoints:

   I don't see any usage of these endpoint as of now, but for completeness and backward compatibility, we need to ensure this behaviour.

   As I understand we keep them as they are now. And they should ignore that some of the phases would be related to some WorkStreams after adding such relation.

3. Regarding PhaseProduct as I understand so far we don't make changes to the model. And we use the existent relation between Phase and PhaseProduct which is done via JSON in details at the moment.

[vikasrohit]

Do you mean that we have to create an additional model to implement this relation? Or just add one more field to the Phase model: workstreamId which would refer to a WorkStream or can be null.

I would not like to add any reference of workstream directly to the phase, rather I am thinking of using a join table to maintain this relationship which I guess is supported only via belongsToMany association in sequelize. I am still open for the way you suggested, if we see any issues with the way I proposed.

As I understand we keep them as they are now. And they should ignore that some of the phases would be related to some WorkStreams after adding such relation.

Yes, I think we don't need to make any change to the code for this endpoint. However, we might need to verify that this works after we introduce the models. 😬

And we use the existent relation between Phase and PhaseProduct which is done via JSON in details at the moment.

Yes, we are going to use existing relation but it is not maintained via details JSON, we already have phaseId field in phaseProduct model. details is just used to store the requirements of the specific phaseProduct which is rendered as the Specification tab of a phase.

[maxceem]

I would not like to add any reference of workstream directly to the phase, rather I am thinking of using a join table to maintain this relationship which I guess is supported only via belongsToMany association in sequelize. I am still open for the way you suggested, if we see any issues with the way I proposed.

Ok. So we will add one more model PhaseWorkStream:

-- phase_work_stream table

phaseId bigint NOT NULL,
workStreamId bigint NOT NULL,

And use sequalize feature belongsToMany:

Phase.belongsToMany(WorkStream, { through: PhaseWorkStream })

Yes, we are going to use existing relation but it is not maintained via details JSON, we already have phaseId field in phaseProduct model

Hmm, strange that we don't have phaseId in sequalize model of the PhaseProduct https://github.com/topcoder-platform/tc-project-service/blob/dev/src/models/phaseProduct.js#L3-L20 but we have it in SQL migration script https://github.com/topcoder-platform/tc-project-service/blob/dev/migrations/20180608_project_add_templateId_and_new_tables.sql#L89
I guess we should add it to the sequalize model too.

[vikasrohit]

Phase.belongsToMany(WorkStream, { through: PhaseWorkStream })

Do we need

WorkStream.belongsToMany(Phase, { through: PhaseWorkStream })

as well? or can we just specify only one of these (preferably the one I suggested above) to fulfill all the requirements of specifying the association?

I guess we should add it to the sequalize model too.

We don't need that as it added by association specified by https://github.com/topcoder-platform/tc-project-service/blob/dev/src/models/projectPhase.js#L42

[maxceem]

Do we need

WorkStream.belongsToMany(Phase, { through: PhaseWorkStream })
as well? or can we just specify only one of these (preferably the one I suggested above) to fulfill all the requirements of specifying the association?

Do you mean that we don't have to explicitly create the model PhaseWorkStream as we are not going to add any additional fields to the join table, and we can do like:

WorkStream.belongsToMany(Phase, { through: 'PhaseWorkStream' }) // 'PhaseWorkStream' is a string

And, create an SQL migration script for creating according table in the DB:

-- table
-- phase_work_stream
phaseId bigint NOT NULL,
workStreamId bigint NOT NULL,

Or did you mean some other way? As I don't see another way at the moment from your input.

[vikasrohit]

Yep, having relationship between Phase and WorkStream, without creating the model is also something I would like to have as Sequelize support that.

However, my intention is the last comment is about keeping the relationship between these two entities from WorkStream object instead of Phase i.e.
using WorkStream.belongsToMany(Phase, { through: 'PhaseWorkStream' }) instead of Phase.belongsToMany(WorkStream, { through: PhaseWorkStream }) because the later one says one phase belongs to many workstreams which actually not the case and we are just doing this for keeping the relationship in separate table while in the former one we are saying that a workstream belongs to multiple phases. Another question is that, do we need to specify relationship in both ways to fulfill the requirements of Sequelize to maintain many to many relationships.

[maxceem]

Hmm. So the concern is WorkStream.belongsToMany(Phase, { through: 'WorkStreamPhase' }) vs Phase.belongsToMany(WorkStream, { through: PhaseWorkStream }).

As per my understanding in our case, the next endpoint /projects/{id}/workstreams/{id} should return a list of Phase which belongs to one WorkStream. This is equivalent of Phase model to have workStreamId, which we would replace with a join table and belongsToMany.
It feels for me that this is equivalent to Phase.belongsToMany(WorkStream, { through: PhaseWorkStream }). Though I didn't find an example for such case, as the main purpose for belongsToMany is to handle N:M relations, while in our case we have 1:M relation which is usually handled by the foreign keys.

For our case, it feels that belongsTo/hasMany pair would be more suitable (see example), but it uses a foreign key instead of a join table.

Maybe I'm missing some point here, let me know if you got where is the confusion.

[vikasrohit]

As per my understanding in our case, the next endpoint /projects/{id}/workstreams/{id} should return a list of Phase which belongs to one WorkStream

I guess you meant /projects/{id}/workstreams/{id}/**works** and my point is that one workstream can have more than one phases but one phase would never belong to multiple workstreams.

[maxceem]

Thanks, @vikasrohit you are right. I was a little bit mixed up here.

The requirements are clear for me now. Just to get a wider perspective, what is the main intention to use a join table here instead of a foreign key workStreamId inside Phase model? Are there any future plans connected with these tables?

[vikasrohit]

I am just trying to keep the workstream logic separate from phases because I have seen the terminology changing very significantly over the time (e.g. now we are calling phase as work, which we had as naming convention in Connect v1 😬)

[maxceem]

@vikasrohit the last questions here before I lunch it:

1. There are few endpoints which require complex logic of authorization depending on the project template, for some templates we allow customers to be able to update the details while for others we don't.

   1. Do we have any details regarding this requirement? Or should we let all customers to perform these actions at the moment?
   2. This would be NOT allowed to copilots in any conditions right?

2. All the GET endpoints can be accessed by any authenticated user who is a member of a project + admin users, correct?

[vikasrohit]

1. We can not determine any specific logic for customer access. I mean, we need to be generic in terms of having capability of controlling access of work management based on user roles and project template. May be we can ask participants to improvise there.

2. I can not think case where your statement does not hold true, so we should be good for now.

[maxceem]

capability of controlling access of work management based on user roles and project template

if permissions only depend on user roles and template I think we can add a metadata field to the projectTemplate model. And use it to define the list of user roles which can edit works for this template, something like this:

projectTemplate = {
   metadata: {
      workManagementRoles: ['Copilot', 'Topcoder User']
   }
}

In the future we can define some additional data inmetadata for templates if we need.

Very general approach

There could be another more general approach. If we have any plans to use some custom access logic for other endpoints. Maybe we can think about some very general approach which would let use define permission for any endpoint using some rules. I guess this would require us to introduce some new model. This is just a vague idea.