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 :

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

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

{
  "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,
      "status": {
        "name": "In Progress",
        "detail": "Loading holdings"
      }
    }
  },
  "jsonapi": {
    "version": "1.0"
  }
}

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

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

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()
• startLoading() 

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

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.