Spike: MODKBEKBJ-255 - Harden process to load holdings

MODKBEKBJ-255 - Getting issue details... STATUS

The main goal of the issue is to define additional steps to harden the process of loading holdings entities from RM API.

Here are the points highlighted in the issue:

1. Create a separate endpoint that gives us the status of the progress of the process.
2. Add retry mechanism in case something fails - at least 3 retries
3. What happens if someone tries to filter resources by tags while the table is being populated/has no entries - we end up making multiple requests to RM API - which can be avoided if we add an updated_at column to our holdings table. Based on when the entry was last updated, we decide whether we want to insert/update the entry or leave it as is - this eliminates the need to truncate the entire holdings table and re-populate increasing performance.
4. What happens if RM API is down? Approach outlined in 3. helps us still retain holdings data albeit stale

Spike results:


1. Create a separate endpoint that gives us the status of the progress of the process.

Here is the proposed definition of the endpoint:

"methods": ["GET"],
"pathPattern": "/loadHoldings/status",
"permissionsRequired": ["kb-ebsco.holdings.load.status.get"]

The status enum includes the following values :

Status NameDescription ExampleFiles
Not Started

before the first start of loading holdings. The response will not provide any details.


{
  "data": {
    "type": "status",
    "attributes": {
      "status": {
        "name": "Not Started"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}
Started

Backend service received the signal to start the process but not yet called RM API.

The additional attributes can be provided but not required

  • started - the time of receiving the signal to start loading
{
  "data": {
    "type": "status",
    "attributes": {
      "started": "1999-12-31 14:59:59",
      "status": {
        "name": "Started"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}
In Progress

the loading is in progress. The additional attributes can be provided but not required:

  • detail - the details of the process. Currently includes these values
    • Populating staging area - populates holdings data to a staging area
    • Loading holdings - getting holdings from staging 
  • started - the time of receiving the signal to start loading
  • totalCount - the total amount of holdings 
  • importedCount - the amount of holdings that have been loaded up until this point
{
  "data": {
    "type": "status",
    "attributes": {
      "started": "1999-12-31 14:59:59",
      "status": {
        "name": "In Progress",
        "detail": "Populating staging area"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}
{
  "data": {
    "type": "status",
    "attributes": {
      "started": "1999-12-31 14:59:59",
      "totalCount": 1234,
 	  "importedCount" : 1000
      "status": {
        "name": "In Progress",
        "detail": "Loading holdings"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}
Completed

loading is finished and holdings saved in a database. The additional attributes can be provided but not required:

  • started - the time of receiving the signal to start loading
  • finished - the finished loading and saving holdings
  • totalCount - the total amount of holdings  
{
  "data": {
    "type": "status",
    "attributes": {
      "started": "1999-12-31 14:59:59",
      "finished": "1999-12-31 16:30:47",
      "totalCount": 1234,
      "status": {
        "name": "Completed"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}
Failedsome request failed. The additional attributes can be provided but not required:
  • errors - the list of errors. includes the following values
    • title - the error message title
    • detail - the error message detail
    • source - the error message source
{
  "data": {
	"type" : "status",
    "attributes": {
      "status": {
        "name": "Failed"
      },
      "errors": [
        {
          "title": "Invalid APIKEY",
          "detail": "Kb api credentials are invalid"
        }
      ]
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}

The endpoint will return 200 Ok status or 500 if service is down.

2. Add retry mechanism in case something fails - at least 3 retries

Current loading holdings flow:

For the retry mechanism there are two options:

1 - retry whole process of loading holdings 

2 - partial retry of some stage.

 The first variant of loading holdings from the scratch has some disadvantages, for instance, if we have a situation when holdings population to the staging area was successful but have difficulties for loading entities from the server. In this case more effective from the time perspective is to restart just loading part instead of the whole process.

The proposed partial retry mechanism will be implemented in the following methods of the HoldingsService:

  • createSnapshot() - If any of the requests to RM API related to creating snapshot are completed with error then creation of snapshot will be retried after configured delay. 
  • startLoading()  - If loading a page for holdings is failed with error then loading of this page is retried. If page fails to be loaded multiple times then it is skipped and LoadServiceFacade starts loading next page.

The PoC is available to try on branch MODKBEKBJ-255-harden-loading-holdings

3. What happens if someone tries to filter resources by tags while the table is being populated/has no entries - we end up making multiple requests to RM API - which can be avoided if we add an updated_at column to our holdings table. Based on when the entry was last updated, we decide whether we want to insert/update the entry or leave it as is - this eliminates the need to truncate the entire holdings table and re-populate increasing performance.

Update an existing holdings table definition to add an additional column updated_at to have a timestamp of the date, that indicates when the table was last modified like following or more optimized way by using the time of the transaction (transaction_timestamp)

ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;

and update the SQL statement INSERT_OR_UPDATE_HOLDINGS_STATEMENT, currently used for holdings table, to update holdings entry instead of ignoring.  In this case, we need to care about entities which have been deleted from RM API and still present in our holdings table. 

4. What happens if RM API is down? Approach outlined in 3. helps us still retain holdings data albeit stale. 

The status endpoint will return the "Failed" status which indicates that something went wrong during loading. The user is able to run a one-time job to load holdings and for the search, we will have holdings already stored in the database which we are able to operate.