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:

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": {
    "name": "Not Started",
  },
  "jsonapi": {
    "version": "1.0"
  }
}

{
  "status": {
    "name": "Started",
  },
  "attributes": {
    "started": "1999-12-31 14:59:59"
  },
  "jsonapi": {
    "version": "1.0"
  }
}

{
  "status": {
    "name": "In Progress",
    "detail" : "Populated to holdings"
  },
  "attributes": {
    "started": "1999-12-31 14:59:59"
  },
  "jsonapi": {
    "version": "1.0"
  }
}

{
  "status": {
    "name": "In Progress",
    "detail" : "Loading holdings"
  },
  "attributes": {
    "started": "1999-12-31 14:59:59",
    "totalCount": 1234
  },
  "jsonapi": {
    "version": "1.0"
  }
}

{
  "status": {
    "name": "Completed"
  },
  "attributes": {
    "started": "1999-12-31 14:59:59",
    "finished": "1999-12-31 16:30:47",
    "totalCount": 1234
  },
  "jsonapi": {
    "version": "1.0"
  }
}

{
  "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.

Current loading holdings flow:

Here is two options :

1 - retry whole process of loading holdings or

2 - partial retry of some stage.

 As for the first variant of loading holdings from the scratch - if, for instance, during the loading entries from RM API we have some problem, from my point of view, seems more reasonable to retry exactly loading stage instead of full process.

..to be continue

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. 

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.