[$70] [Metadata Management] New project/product type does not work when we use a deleted key

[vikasrohit]

When we try to create a new project type or product category, and enter a key which used to exists in past but not is deleted, it throws validation error. We should be able to add a new project type or product category with a key of a deleted entity.

What do you think @maxceem about this behaviour?

[maxceem]

So this happens because we use soft-deleting (instead of deleting records we mark them as deletedBy <= userId). While at the same time key column is a primary key and should be unique.

1. The first approach is to remove primary key constrain from the key and add an auto-incremental id column as a primary key instead. The downside is that we would have to control uniqueness of the key using JS code manually.

2. Another approach is to introduce a new column detele_token and each time when we delete a record to fill it with a unique value. The same time we should change the primary key to the pair key + detele_token. Or we still can add an artificial id as a primary key, while making the pair key + detele_token - unique.
   This would lead to the fact, that we can add new records with the key which has been deleted and this would be controlled by the sequalzie/postgress. No need for the custom JS logic.
   The only thing we should make sure, that Posgress treats null values as the same. So pairs
   key == sameKey + detele_token == null and key == sameKey + detele_token == null are treated as equal pairs, but I guess it does.

[vikasrohit]

I think 1 is not the option to go as it requires more maintenance. For 2, I think we can make key + deletedAt + deletedBy as unique constraint instead of adding a new field.

On second thoughts, I realized that we should be good even with current state of application i.e. having key as unique constraint and don't let creation of a new record with key being duplicate of deleted or non deleted record. Only improvement, we can do is to make sure we show proper error message to the user something like There exists deleted/non-deleted entity with the same key and in case it is urgent for some one to create the new entity with exactly same key, we can either undelete the deleted record manually by SQL or update the key of the deleted record to be different e.g. append _deleted to the key.

[maxceem]

@vikasrohit there is already an error message which is almost like you proposed:

I would propose to show different error messages for the case of deleted and non-deleted to be more precise and avoid any confusion:

• Deleted project type already exists for key "key_value".
• Project type already exists for key "key_value". (we should fix undefined in current messages)

[vikasrohit]

I would like only single message @maxceem Project type already exists(may be deleted) for key "key_value".

[maxceem]

This issue has to be fixed in the Project Service repository https://github.com/topcoder-platform/tc-project-service

To verify this issue:

• deploy connect app and project service locally as per this guide https://github.com/topcoder-platform/tc-project-service#run-connect-app-with-project-service-locally
• import demo data https://github.com/topcoder-platform/tc-project-service#import-sample-metadata--projects
• As provided user doesn’t have permissions to create/updated/delete all the templates, you would have to remove permission checking in your local Project Service setup. For this purpose assign true to the projectAdmin in this line https://github.com/topcoder-platform/tc-project-service/blob/feature/milestone-pausing/src/permissions/index.js#L8

Provide 2 demo videos:

1. showing how we try to create projectType with deleted key
2. showing how we try to create productCategory with deleted key