Usage Consolidation: Folio - Cost-per-use integration: Authentification and configuration design

Owned by Pavlo Smahin
Last updated: Sep 17, 2020
21 min read

MODKBEKBJ-490 - Getting issue details... STATUS

Participants:

RoleNameApproval
Solution Architect

Java Lead

Product Owner

Goals

  1. Design configuration, storing credentials, and authentication approach
  2. Support multiple libraries in a single tenant

Design

Introduction

The main point of this design document is to describe an approach of how mod-kb-ebsco should store all data that is needed to use Cost-per-use Controller API. 

APIGEE is used as a proxy for Cost-per-use Controller API requests. Requests to APIGEE are required to have these headers: 

HeaderDescription
custkeyEncrypted Customer ID
AuthorizationBearer token

Bearer token can be obtained by Auth Service. This can be done by making a request: POST https://apis.ebsco.com/oauth-proxy/token with x-www-form-urlencoded body: 

KeyValue
grant_typeclient_credentials
client_idXXXX
client_secretYYYY

client_id and client_secret are common for FOLIO.

The response from Auth service:

{
    "access_token": "ZZZZ",
    "token_type": "Bearer",
    "expires_in": 3600,
    "scope": "openid"
}

The token is valid until it expires.

Database design

  1. client_id and client_secret for Auth Service are stored in the usage_consolidation_credentials table
  2. Encrypted Customer ID and settings parameters like Start Fiscal Month and Currency are stored in the usage_consolidation_settings table
  3. To support multiple libraries in a single tenant usage_consolidation_settings maps to kb_credentials table (one KB credentials can have only one Usage Consolidation settings)
  4. The full list of currencies are stored in the currencies table

 Full list of currencies
[
    {
        "code": "AED",
        "description": "United Arab Emirates Dirham"
    },
    {
        "code": "AFN",
        "description": "Afghan Afghani"
    },
    {
        "code": "ALL",
        "description": "Albanian Lek"
    },
    {
        "code": "AMD",
        "description": "Armenian Dram"
    },
    {
        "code": "ANG",
        "description": "Netherlands Antillean Guilder"
    },
    {
        "code": "AOA",
        "description": "Angolan Kwanza"
    },
    {
        "code": "ARS",
        "description": "Argentine Peso"
    },
    {
        "code": "AUD",
        "description": "Australian Dollar"
    },
    {
        "code": "AWG",
        "description": "Aruban Florin"
    },
    {
        "code": "AZN",
        "description": "Azerbaijani Manat"
    },
    {
        "code": "BAM",
        "description": "Bosnia-Herzegovina Convertible Mark"
    },
    {
        "code": "BBD",
        "description": "Barbadian Dollar"
    },
    {
        "code": "BDT",
        "description": "Bangladeshi Taka"
    },
    {
        "code": "BGN",
        "description": "Bulgarian Lev"
    },
    {
        "code": "BHD",
        "description": "Bahraini Dinar"
    },
    {
        "code": "BIF",
        "description": "Burundian Franc"
    },
    {
        "code": "BMD",
        "description": "Bermudan Dollar"
    },
    {
        "code": "BND",
        "description": "Brunei Dollar"
    },
    {
        "code": "BOB",
        "description": "Bolivian Boliviano"
    },
    {
        "code": "BRL",
        "description": "Brazilian Real"
    },
    {
        "code": "BSD",
        "description": "Bahamian Dollar"
    },
    {
        "code": "BTC",
        "description": "Bitcoin"
    },
    {
        "code": "BTN",
        "description": "Bhutanese Ngultrum"
    },
    {
        "code": "BWP",
        "description": "Botswanan Pula"
    },
    {
        "code": "BYN",
        "description": "Belarusian Ruble"
    },
    {
        "code": "BYR",
        "description": "Belarusian Ruble (pre-2016)"
    },
    {
        "code": "BZD",
        "description": "Belize Dollar"
    },
    {
        "code": "CAD",
        "description": "Canadian Dollar"
    },
    {
        "code": "CDF",
        "description": "Congolese Franc"
    },
    {
        "code": "CHF",
        "description": "Swiss Franc"
    },
    {
        "code": "CLF",
        "description": "Chilean Unit of Account (UF)"
    },
    {
        "code": "CLP",
        "description": "Chilean Peso"
    },
    {
        "code": "CNH",
        "description": "Chinese Yuan (Offshore)"
    },
    {
        "code": "CNY",
        "description": "Chinese Yuan"
    },
    {
        "code": "COP",
        "description": "Colombian Peso"
    },
    {
        "code": "CRC",
        "description": "Costa Rican Colón"
    },
    {
        "code": "CUC",
        "description": "Cuban Convertible Peso"
    },
    {
        "code": "CUP",
        "description": "Cuban Peso"
    },
    {
        "code": "CVE",
        "description": "Cape Verdean Escudo"
    },
    {
        "code": "CZK",
        "description": "Czech Republic Koruna"
    },
    {
        "code": "DJF",
        "description": "Djiboutian Franc"
    },
    {
        "code": "DKK",
        "description": "Danish Krone"
    },
    {
        "code": "DOP",
        "description": "Dominican Peso"
    },
    {
        "code": "DZD",
        "description": "Algerian Dinar"
    },
    {
        "code": "EEK",
        "description": "Estonian Kroon"
    },
    {
        "code": "EGP",
        "description": "Egyptian Pound"
    },
    {
        "code": "ERN",
        "description": "Eritrean Nakfa"
    },
    {
        "code": "ETB",
        "description": "Ethiopian Birr"
    },
    {
        "code": "EUR",
        "description": "Euro"
    },
    {
        "code": "FJD",
        "description": "Fijian Dollar"
    },
    {
        "code": "FKP",
        "description": "Falkland Islands Pound"
    },
    {
        "code": "GBP",
        "description": "British Pound Sterling"
    },
    {
        "code": "GEL",
        "description": "Georgian Lari"
    },
    {
        "code": "GGP",
        "description": "Guernsey Pound"
    },
    {
        "code": "GHS",
        "description": "Ghanaian Cedi"
    },
    {
        "code": "GIP",
        "description": "Gibraltar Pound"
    },
    {
        "code": "GMD",
        "description": "Gambian Dalasi"
    },
    {
        "code": "GNF",
        "description": "Guinean Franc"
    },
    {
        "code": "GTQ",
        "description": "Guatemalan Quetzal"
    },
    {
        "code": "GYD",
        "description": "Guyanaese Dollar"
    },
    {
        "code": "HKD",
        "description": "Hong Kong Dollar"
    },
    {
        "code": "HNL",
        "description": "Honduran Lempira"
    },
    {
        "code": "HRK",
        "description": "Croatian Kuna"
    },
    {
        "code": "HTG",
        "description": "Haitian Gourde"
    },
    {
        "code": "HUF",
        "description": "Hungarian Forint"
    },
    {
        "code": "IDR",
        "description": "Indonesian Rupiah"
    },
    {
        "code": "IEP",
        "description": "Irish Punt"
    },
    {
        "code": "ILS",
        "description": "Israeli New Sheqel"
    },
    {
        "code": "IMP",
        "description": "Manx pound"
    },
    {
        "code": "INR",
        "description": "Indian Rupee"
    },
    {
        "code": "IQD",
        "description": "Iraqi Dinar"
    },
    {
        "code": "IRR",
        "description": "Iranian Rial"
    },
    {
        "code": "ISK",
        "description": "Icelandic Króna"
    },
    {
        "code": "JEP",
        "description": "Jersey Pound"
    },
    {
        "code": "JMD",
        "description": "Jamaican Dollar"
    },
    {
        "code": "JOD",
        "description": "Jordanian Dinar"
    },
    {
        "code": "JPY",
        "description": "Japanese Yen"
    },
    {
        "code": "KES",
        "description": "Kenyan Shilling"
    },
    {
        "code": "KGS",
        "description": "Kyrgystani Som"
    },
    {
        "code": "KHR",
        "description": "Cambodian Riel"
    },
    {
        "code": "KMF",
        "description": "Comorian Franc"
    },
    {
        "code": "KPW",
        "description": "North Korean Won"
    },
    {
        "code": "KRW",
        "description": "South Korean Won"
    },
    {
        "code": "KWD",
        "description": "Kuwaiti Dinar"
    },
    {
        "code": "KYD",
        "description": "Cayman Islands Dollar"
    },
    {
        "code": "KZT",
        "description": "Kazakhstani Tenge"
    },
    {
        "code": "LAK",
        "description": "Laotian Kip"
    },
    {
        "code": "LBP",
        "description": "Lebanese Pound"
    },
    {
        "code": "LKR",
        "description": "Sri Lankan Rupee"
    },
    {
        "code": "LRD",
        "description": "Liberian Dollar"
    },
    {
        "code": "LSL",
        "description": "Lesotho Loti"
    },
    {
        "code": "LTL",
        "description": "Lithuanian Litas"
    },
    {
        "code": "LVL",
        "description": "Latvian Lats"
    },
    {
        "code": "LYD",
        "description": "Libyan Dinar"
    },
    {
        "code": "MAD",
        "description": "Moroccan Dirham"
    },
    {
        "code": "MDL",
        "description": "Moldovan Leu"
    },
    {
        "code": "MGA",
        "description": "Malagasy Ariary"
    },
    {
        "code": "MKD",
        "description": "Macedonian Denar"
    },
    {
        "code": "MMK",
        "description": "Myanma Kyat"
    },
    {
        "code": "MNT",
        "description": "Mongolian Tugrik"
    },
    {
        "code": "MOP",
        "description": "Macanese Pataca"
    },
    {
        "code": "MRO",
        "description": "Mauritanian Ouguiya (pre-2018)"
    },
    {
        "code": "MRU",
        "description": "Mauritanian Ouguiya"
    },
    {
        "code": "MTL",
        "description": "Maltese Lira"
    },
    {
        "code": "MUR",
        "description": "Mauritian Rupee"
    },
    {
        "code": "MVR",
        "description": "Maldivian Rufiyaa"
    },
    {
        "code": "MWK",
        "description": "Malawian Kwacha"
    },
    {
        "code": "MXN",
        "description": "Mexican Peso"
    },
    {
        "code": "MYR",
        "description": "Malaysian Ringgit"
    },
    {
        "code": "MZN",
        "description": "Mozambican Metical"
    },
    {
        "code": "NAD",
        "description": "Namibian Dollar"
    },
    {
        "code": "NGN",
        "description": "Nigerian Naira"
    },
    {
        "code": "NIO",
        "description": "Nicaraguan Córdoba"
    },
    {
        "code": "NOK",
        "description": "Norwegian Krone"
    },
    {
        "code": "NPR",
        "description": "Nepalese Rupee"
    },
    {
        "code": "NZD",
        "description": "New Zealand Dollar"
    },
    {
        "code": "OMR",
        "description": "Omani Rial"
    },
    {
        "code": "PAB",
        "description": "Panamanian Balboa"
    },
    {
        "code": "PEN",
        "description": "Peruvian Nuevo Sol"
    },
    {
        "code": "PGK",
        "description": "Papua New Guinean Kina"
    },
    {
        "code": "PHP",
        "description": "Philippine Peso"
    },
    {
        "code": "PKR",
        "description": "Pakistani Rupee"
    },
    {
        "code": "PLN",
        "description": "Polish Zloty"
    },
    {
        "code": "PYG",
        "description": "Paraguayan Guarani"
    },
    {
        "code": "QAR",
        "description": "Qatari Rial"
    },
    {
        "code": "RON",
        "description": "Romanian Leu"
    },
    {
        "code": "RSD",
        "description": "Serbian Dinar"
    },
    {
        "code": "RUB",
        "description": "Russian Ruble"
    },
    {
        "code": "RWF",
        "description": "Rwandan Franc"
    },
    {
        "code": "SAR",
        "description": "Saudi Riyal"
    },
    {
        "code": "SBD",
        "description": "Solomon Islands Dollar"
    },
    {
        "code": "SCR",
        "description": "Seychellois Rupee"
    },
    {
        "code": "SDG",
        "description": "Sudanese Pound"
    },
    {
        "code": "SEK",
        "description": "Swedish Krona"
    },
    {
        "code": "SGD",
        "description": "Singapore Dollar"
    },
    {
        "code": "SHP",
        "description": "Saint Helena Pound"
    },
    {
        "code": "SLL",
        "description": "Sierra Leonean Leone"
    },
    {
        "code": "SOS",
        "description": "Somali Shilling"
    },
    {
        "code": "SRD",
        "description": "Surinamese Dollar"
    },
    {
        "code": "SSP",
        "description": "South Sudanese Pound"
    },
    {
        "code": "STD",
        "description": "São Tomé and Príncipe Dobra (pre-2018)"
    },
    {
        "code": "STN",
        "description": "São Tomé and Príncipe Dobra"
    },
    {
        "code": "SVC",
        "description": "Salvadoran Colón"
    },
    {
        "code": "SYP",
        "description": "Syrian Pound"
    },
    {
        "code": "SZL",
        "description": "Swazi Lilangeni"
    },
    {
        "code": "THB",
        "description": "Thai Baht"
    },
    {
        "code": "TJS",
        "description": "Tajikistani Somoni"
    },
    {
        "code": "TMT",
        "description": "Turkmenistani Manat"
    },
    {
        "code": "TND",
        "description": "Tunisian Dinar"
    },
    {
        "code": "TOP",
        "description": "Tongan Pa'anga"
    },
    {
        "code": "TRY",
        "description": "Turkish Lira"
    },
    {
        "code": "TTD",
        "description": "Trinidad and Tobago Dollar"
    },
    {
        "code": "TWD",
        "description": "New Taiwan Dollar"
    },
    {
        "code": "TZS",
        "description": "Tanzanian Shilling"
    },
    {
        "code": "UAH",
        "description": "Ukrainian Hryvnia"
    },
    {
        "code": "UGX",
        "description": "Ugandan Shilling"
    },
    {
        "code": "USD",
        "description": "United States Dollar"
    },
    {
        "code": "UYU",
        "description": "Uruguayan Peso"
    },
    {
        "code": "UZS",
        "description": "Uzbekistan Som"
    },
    {
        "code": "VEF",
        "description": "Venezuelan Bolívar"
    },
    {
        "code": "VND",
        "description": "Vietnamese Dong"
    },
    {
        "code": "VUV",
        "description": "Vanuatu Vatu"
    },
    {
        "code": "WST",
        "description": "Samoan Tala"
    },
    {
        "code": "XAF",
        "description": "CFA Franc BEAC"
    },
    {
        "code": "XAG",
        "description": "Silver Ounce"
    },
    {
        "code": "XAU",
        "description": "Gold Ounce"
    },
    {
        "code": "XCD",
        "description": "East Caribbean Dollar"
    },
    {
        "code": "XDR",
        "description": "Special Drawing Rights"
    },
    {
        "code": "XOF",
        "description": "CFA Franc BCEAO"
    },
    {
        "code": "XPD",
        "description": "Palladium Ounce"
    },
    {
        "code": "XPF",
        "description": "CFP Franc"
    },
    {
        "code": "XPT",
        "description": "Platinum Ounce"
    },
    {
        "code": "YER",
        "description": "Yemeni Rial"
    },
    {
        "code": "ZAR",
        "description": "South African Rand"
    },
    {
        "code": "ZMK",
        "description": "Zambian Kwacha (pre-2013)"
    },
    {
        "code": "ZMW",
        "description": "Zambian Kwacha"
    },
    {
        "code": "ZWD",
        "description": "Zimbabwean Dollar (pre-2009)"
    },
    {
        "code": "ZWL",
        "description": "Zimbabwean Dollar"
    }
]
 SQL script
CREATE TABLE "kb_credentials" (
  "id" UUID PRIMARY KEY,
  "name" VARCHAR,
  "customer_id" VARCHAR,
  "api_key" VARCHAR,
  "url" VARCHAR,
  "created_date" TIMESTAMP,
  "created_by_user_id" UUID,
  "created_by_user_name" VARCHAR,
  "updated_date" TIMESTAMP,
  "updated_by_user_id" UUID,
  "updated_by_user_name" VARCHAR
);

CREATE TABLE "usage_consolidation_settings" (
  "id" UUID PRIMARY KEY,
  "kb_credentials_id" UUID,
  "customer_key" VARCHAR,
  "fiscal_month" INT,
  "currency" VARCHAR
);

CREATE TABLE "usage_consolidation_credentials" (
  "client_id" VARCHAR,
  "client_secret" VARCHAR
);

CREATE TABLE "currencies" (
  "code" VARCHAR PRIMARY KEY,
  "description" VARCHAR
);

ALTER TABLE "usage_consolidation_settings" ADD FOREIGN KEY ("kb_credentials_id") REFERENCES "kb_credentials" ("id");

ALTER TABLE "usage_consolidation_settings" ADD FOREIGN KEY ("currency") REFERENCES "currencies" ("code");

Cost-per-use settings manage design

client_id and client_secret manage

The usage_consolidation_credentials table should be managed manualy.

customer key and settings manage

Endpoints to enable and manage Usage Consolidation settings:
MethodEndpointDescriptionNotes
GET/eholdings/currenciesRetrieve all currencies that can be used for Usage ConsolidationList of currencies uploades to database while mod is starting
GET/eholdings/kb-credentials/<id>/ucRetrieve Usage Consolidation settings related to KB Credentials


POST/eholdings/kb-credentials/<id>/ucEnable Usage Consolidation related to KB Credentials

customer_key - required field

start_month - optional, default JAN

currency - optional, default USD

platform- optional, default ALL


Need to verify customer_key before save by making test request to APIGEE (if 401 - than customer_key is invalid)
PATCH/eholdings/kb-credentials/<id>/ucUpdate Usage Consolidation settings related to KB CredentialsUpdates only specified fields

Cost-per-use authentification design

  1. Check If customer key is exist
  2. Token stores in mod-kb cache
  3. If token is expired or not exist in cache:
    1. get client_id and client_secret from the database
    2. make request to Auth Service
    3. get token and save it to cache 
  4. Retrieve settings from the database
  5. Construct and send request to APIGEE with token and customer key in headers

Implementation stories

  1. Implement endpoint GET /eholdings/currencies
  2. Implement endpoint POST /eholdings/kb-credentials/<id>/uc
  3. Implement endpoint GET /eholdings/kb-credentials/<id>/uc
  4. Implement endpoint PATCH /eholdings/kb-credentials/<id>/uc
  5. Implement CostPerUseAuthentificationService