API to check if a unit has been created

[franck-boullier]

As part of #3 we also need an endpoint so that the 3rd party can check if the unit has been correctly created:

How it should work:

• 3rd party calls an endpoint
• 3rd party specifies the record it wants to retrieve
• 3rd party specifies the information it wants to retrieve for this record
• End point returns the information

Background information:

The information is available in the UNTE Database in the table unte_api_add_unit. From this table, it is possible to retrieve the following information

• mefe_unit_id: The MEFE id of the unit
• uneet_created_datetime: the timestamp when the unit was created in Unee-T
• is_api_success:
  • 1 if the unit was created correctly
  • 0 if the there was an error while creating the unit.
  • NULL we have no information (this is the initial state until we receive a reply from the downstream systems)
• api_error_message: the error message if applicable.

The different ways to retrieve the information:

Option 1:

In this option the 3rd party only needs the information from it's internal source of truth

User Input:

The user sends the following information as part of the payload:

• MANDATORY organizationKey varchar(255): The UNTE API key for the 3rd party. This key MUST exist in the table unte_api_keys or else there will be an error.
• MANDATORY externalId varchar(255): the Unique ID of the unit in the source system: the system where the user information is coming from. This cannot be empty. (see #3)
• OPTIONAL externalSystem varchar(100): This is the name of the source of truth for unit information from the 3rd party.
• OPTIONAL tableInExternalSystem varchar(100): This is the table where the unit information are stored in the the source of truth for unit information from the 3rd party.
• OPTIONAL infoNeeded: this could be one of the following values:
  • All - this will be the default value if nothing is specified.
  • mefeUnitId
  • createdDatetime
  • created
  • errorMessage

Output:

Based on the value specified in infoNeeded

All or [unspecified]:

return all the information:

• mefeUnitId: the mefe unit id of the unit
• createdDatetime: The timestamp (Z) when the unit was created in Unee-T
• status: This can either be
  • 1: success
  • 0: error
  • NULL: we have no information about the status of the request, request is still pending.
• errorMessage: The error message (if applicable)

mefeUnitId:

return the information:

• mefeUnitId: the mefe unit id of the unit

createdDatetime:

return the information:

• createdDatetime: The timestamp (Z) when the unit was created in Unee-T

status:

return the information:

• status: This can either be
  • 1: success
  • 0: error
  • NULL: we have no information about the status of the request, request is still pending.

errorMessage:

return the information:

• errorMessage: The error message (if applicable)

Option 2:

In this option the 3rd party only need to have stored the requestId that was generated when the unit creation was request (see #3)

User Input:

The user sends the following information as part of the payload:

• MANDATORY organizationKey varchar(255): The UNTE API key for the 3rd party. This key MUST exist in the table unte_api_keys or else there will be an error.
• MANDATORY requestId varchar(255): the Unique ID of the request that was sent when the 3rd party requested the unit to be created (see #3)
• OPTIONAL infoNeeded: this could be one of the following values:
  • All - this will be the default value if nothing is specified
  • mefeUnitId
  • createdDatetime
  • created
  • errorMessage

Output:

Based on the value specified in infoNeeded

All or [unspecified]:

return all the information:

• mefeUnitId: the mefe unit id of the unit
• createdDatetime: The timestamp (Z) when the unit was created in Unee-T
• status: This can either be
  • 1: success
  • 0: error
  • NULL: we have no information about the status of the request, request is still pending.
• errorMessage: The error message (if applicable)

mefeUnitId:

return the information:

• mefeUnitId: the mefe unit id of the unit

createdDatetime:

return the information:

• createdDatetime: The timestamp (Z) when the unit was created in Unee-T

status:

return the information:

• status: This can either be
  • 1: success
  • 0: error
  • NULL: we have no information about the status of the request, request is still pending.

errorMessage:

return the information:

• errorMessage: The error message (if applicable)

How to get the information from the UNTE DB:

This is done by passing some SQL variables and calling a specific procedure

The variables:

@organization_key: This can NOT be NULL @request_id:

• This MUST be NULL if @external_id is NOT NULL (this means we are in Option 1)
• This can NOT be NULL IF @external_id is NULL (this means we are in Option 2) @external_id: This can NOT be NULL
• This can NOT be NULL if @request_id is NULL (this means we are in Option 1)
• This MUST be NULL IF @request_id is NOT NULL (this means we are in Option 2) @external_system: This MUST be NULL
• IF this is NOT specified by the 3rd party
• IF @request_id is NOT NULL (this means we are in Option 2) @table_in_external_system: This MUST be NULL
• IF this is NOT specified by the 3rd party
• IF @request_id is NOT NULL (this means we are in Option 2) @info_needed: This MUST be NULL if this is NOT specified by the 3rd party

The SQL syntax:

SET @organization_key = [organizationKey];
SET @request_id = [requestId];
SET @external_id = [externalId];
SET @external_system = [externalSystem OR NULL];
SET @table_in_external_system = [tableInExternalSystem OR NULL];
SET @info_needed = [infoNeeded OR NULL];
CALL `proc_unte_api_check_unit_creation` ;

[kaihendry]

Is the mandatory organizationKey really necessary? We could look it up with the unguessable external_id/request_id. We already use a bearer token to authenticate the unit create API call. Or is organizationKey meant to authenticate the calls?

Since usually the -H "Authorization: Bearer ...secret.." is used for API authentication. But if it's not the case with these APIs I'll update.

[kaihendry]

Do you have an example of the output of

  `CALL `proc_unte_api_check_unit_creation` ;`?

I hoped I could just lookup the row with external_id/request_id and return it verbatim. I don't quite understand why we need to call a routine to check the status of the job.

Also I'm confused as to the structure of the response. Ideally it maps as closely to the existing unte_api_add_unit table structure. i.e.

• api_error_message and not errorMessage
• is_api_success and not status
• syst_created_datetime and not createdDatetime

Otherwise another layer of mappings need to be written.

[franck-boullier]

is organizationKey meant to authenticate the calls?

That is correct: organizationKey is meant to authenticate the calls and make sure that the user calling the API is allowed to make the call.

[kaihendry]

Let me know when proc_unte_api_check_unit_creation is ready to go so I can resume this work.