Project UBahn | Replace QLDB with PostgreSQL

This is a reposted contest. The submission duration is deliberately kept to 18 hours only to allow members that could not submit or faced upload issues with the earlier contest to submit once again. 

Context

Project Context

Ubahn is an employee management system to determine employees that are no longer working on active projects and to understand their qualifications and expertise for suitability in other projects

 

Challenge Context

Replace our existing database (QLDB) with PostgreSQL

 

Expected Outcome

Multiple outcomes expected:

• We no longer will use QLDB as our database. The current execution will remain unchanged even after replacing the database.

• On deletion of records, cascade deletion is expected as outlined in the requirements

 

Challenge Details

Technology Stack

• Nodejs version 12

• Elasticsearch version 7.7

 

Development Assets

Single code base involved here

• You can find the API code base here. Ensure that you are using the “develop” branch.

There's another code base called the elasticsearch processor. It is NOT part of this contest. But it is mentioned below to help with the understanding.
 

Individual requirement

Some background about the current code base (the api code base):

• It uses Amazon QLDB has it’s primary database

• Data is first written into QLDB, after which it is passed on to Topcoder’s Bus API. This API publishes the data into Kafka, which is then picked up by another code base that write the data into Elasticsearch

• All read operations are first performed by the API on Elasticsearch, before it falls back to the QLDB database.

• The API only reads data from elasticsearch. Whereas the other code base (not relevant to this contest) writes data into elasticsearch.

• The API writes data into QLDB and reads data from it only when the read operation in elasticsearch fails.

With this background, we are asking you to carry out the following changes:

Requirement 1: Replace the QLDB database with a PostgreSQL one (Major Requirement)

• We would like to use PostgreSQL as our database instead of QLDB

• Replace all occurrences of code that works with QLDB with ones that will interface with PostgreSQL instead.

• Existing behaviour will continue to work as before - namely, all read requests will still happen through Elasticsearch with a fallback to PostgreSQL and all updates will only happen in PostgreSQL with the updates passed on to the bus api.

• You are expected to update the data insertion and deletion scripts, to work with PostgreSQL (minor requirement)

• You are also expected to provide migration scripts for the database (minor requirement)

 

Requirement 2: Behaviour when deleting records (Major Requirement)

• After using PostgreSQL, we would like you to handle deletion of records better

• Check out this ticket that describes clearly the expected behaviour for when any record is deleted. It references the record by their model names.

• We would like you to take the following approach, when implementing the cascade deletion:

  • Since deletion of user or organization will result in deletion of other dependent records, you need to carry this out in a non blocking manner. The api will respond immediately (after the checks have passed) and the status for the deletion will be a 204 (No content) with no response body, while the api, in the background,  collects all the records that need to be deleted, deletes them from the PostgreSQL database and then passes on this information to the bus api (for the elasticsearch processor to respond by deleting the records from elasticsearch).

  • When passing the records to the bus api, for deletion, only in this scenario (that is, when we are deleting a user or organization and its dependent records), pause (for a configurable number of seconds) between each record that is deleted. For example, if, deleting a user, will result in deletion of 20 dependent records, after passing details of the first record to delete, wait for 1 second, and then pass the next record to delete. This is to ensure that the processor, that will respond to these events, has time to process it.

 

Requirement 3: Provide data migration script (Minor Requirement)

• Provide a script through npm scripts that can migrate the data from our QLDB instance to the PostgreSQL instance.

 

Requirement 4: Remove wrapper service methods (Minor Requirement)

• The current code base has a wrapper used by every model to handle CRUD operations.

• While the wrapper encapsulates common code, it is very difficult to introduce a middleware for a specific model to apply any business logic.

• Thus, remove this method. Instead, define the methods under each module’s service independently. For some, you could end up writing the same code multiple times, and that is ok.

• This will also allow you to write the logic necessary when deleting a record without having to figure out how to modify the wrapper, since each module will now have its own version of the code.

 

Important Notes

• Use standard for your lint tool

• Use the async await pattern

 

Deployment guide and validation document

Update the deployment guide as needed.

 

Scorecard Aid

Judging Criteria

• We should be able to use the solution with very less downtime to our existing instances.

• To the end users of the api, there should be no difference. Only the database used has changed, but the behaviour will continue to be as before.

• When we delete records, depending on the model, multiple other records could be deleted, or we may told to delete dependent records first or only that record gets deleted

Final Submission Guidelines

You need to submit a zip file containing the git patch of the changes made, which will be applied to the “develop” branch. This is the recommended approach. However, if you face issues generating the patch, you can upload the entire code base too in the zip file. As much as possible, please upload ONLY the patch files.