Build a database-driven Slackbot

Objectives

• Build a chatbot using watsonx Assistant which interacts with a database backend
• Connect watsonx Assistant to Slack using an integration
• Create and deploy a Python database app to Code Engine
• Access a Db2 on Cloud database via a watsonx Assistant custom extension

1. The user interacts with IBM® watsonx™ Assistant, either through Slack or using a web chat client
2. The chatbot utilizes a custom extension with REST API deployed as Python app on Code Engine
3. The custom extension app retrieves data from and inserts data into a Db2 on Cloud database

Before you begin

This tutorial requires:

• IBM Cloud CLI,
  • Code Engine plugin,
• git to clone source code repository,
• jq to query JSON data.

To avoid the installation of these tools you can use the Cloud Shell from the IBM Cloud console.

You will find instructions to download and install these tools for your operating environment in the Getting started with tutorials guide.

Set up services and deploy backend

In this section, you are going to set up the needed services and deploy the backend app. All of this can be accomplished from the command line interface (CLI) in a terminal.

1. Clone the GitHub repository and navigate into the cloned directory:

   git clone https://github.com/IBM-Cloud/slack-chatbot-database-watson
   cd slack-chatbot-database-watson
   

2. If you are not logged in, use ibmcloud login or ibmcloud login --sso to log in interactively. Make sure to target the region and the resource group to work with. It is used to create the services and actions. You can list your available resource groups using ibmcloud resource groups.

   ibmcloud target -r <region> -g <resource group>
   

   Use ibmcloud target -g default to switch to the default resource group.

3. Create a Db2 on Cloud instance and name it eventDB. Adapt the region us-south to your region, e.g., eu-de.

   ibmcloud resource service-instance-create eventDB dashdb-for-transactions free us-south
   

   You can also use another than the free (lite) plan. The free plan is not available in all locations. Wait for the service to be provisioned. See the output from the above command for instructions. You can also run the next command to check the status:

   ibmcloud resource service-instance eventDB
   

4. To access the database service from the Code Engine app later on, it needs the proper authorization. Thus, you create service credentials and label them slackbotkey:

   ibmcloud resource service-key-create slackbotkey Manager --instance-name eventDB --output json > slackbotkey.json
   

   The slackbotkey.json file contains the service key also called service credentials. This clear text file contains the administrative password and connection strings for the eventDB service instance.

   The output is stored in the file slackbotkey.json and needed in this step. Run this command to extract the database connection URL:

   jq -r '.credentials.connection.db2 | "ibm_db_sa://" + (.authentication.username + ":" + .authentication.password + "@" + .hosts[0].hostname + ":" + (.hosts[0].port | tostring) + "/" + .database + "?Security=SSL;")' slackbotkey.json
   

   It will be referenced as $DB2_URI and can be stored in a shell variable if supported on your workstation or cloud shell and exported in case you are running the application locally.

    export DB2_URI=$(jq -r '.credentials.connection.db2 | "ibm_db_sa://" + (.authentication.username + ":" + .authentication.password + "@" + .hosts[0].hostname + ":" + (.hosts[0].port | tostring) + "/" + .database + "?Security=SSL;")' slackbotkey.json)
    echo $DB2_URI
   

   The output should be of the following format: ibm_db_sa://username:password@database-hostname:port/bludb?Security=SSL; and is in the shell variable DB2_URI.

5. Create an instance of the watsonx Assistant service. Use eventAssistant as name and the free Lite plan. Adapt us-south to your location.

   ibmcloud resource service-instance-create eventAssistant conversation free us-south
   

6. Deploy the pre-built container image with the custom extension as Code Engine app. First, create a project:

   ibmcloud ce project create --name slackbot
   

   Then select the project as the active one:

   ibmcloud ce project select --name slackbot
   

   Then, deploy the app naming it slackbot-backend. Replace the value for the API_TOKEN (MY_SECRET). Use your own text for MY_SECRET if you wish and make substitutions in all future steps. It is used to secure the calls to the REST API.

   ibmcloud ce app create --name slackbot-backend --image icr.io/solution-tutorials/tutorial-slack-chatbot-database:latest --min-scale 1 -e API_TOKEN=MY_SECRET -e DB2_URI="$DB2_URI" 
   

   Note the reported URI for the app. It is referenced in the next steps as $APP_URL. You can use a shell variable:

   APP_URL=$(ibmcloud ce app get -n slackbot-backend --output json | jq -r .status.url)
   echo $APP_URL
   

   Instead of deploying a pre-built container image you could also build and deploy your own image from the provided code. You can modify the create command to build from source: ibmcloud ce app create --name slackbot-backend --build-source . --min-scale 1 -e API_TOKEN=MY_SECRET -e DB2_URI="$DB2_URI" using the Dockerfile found in the repository. See the Code Engine documentation on deploying your app from local source code for additional details. Because you are not using the public container image, but your own, you also need to set up a registry secret.

7. Test the deployment by calling a REST API provided by the app to (re-)create the database schema and insert few sample records.

   curl -X 'POST' "$APP_URL/database/recreate" -H 'accept: application/json' -H 'API_TOKEN: MY_SECRET'
   

   The above request should return an error message that the confirmation is missing. Now try again with a query parameter:

   curl -X 'POST' "$APP_URL/database/recreate?confirmation=True" -H 'accept: application/json' -H 'API_TOKEN: MY_SECRET'
   

   The request should succeed and indicate that the database was recreated. Time for another test:

   curl -X 'GET' "$APP_URL/events" -H 'accept: application/json' -H 'API_TOKEN: MY_SECRET'
   

8. Note the Project ID it will be required in a future step.

   ibmcloud ce app get -n slackbot-backend --output json |  jq -r .metadata.namespace
   

Create an assistant

In this part of the tutorial you are going to work with the watsonx Assistant service. First, you create a new assistant. Then, you create the custom extension and add it to the assistant. Thereafter, you will create actions and test them using the web preview. Finally, you integrate the chatbot with Slack and perform more tests.

1. In the IBM Cloud Resource List open the overview of your services. Locate the instance of the watsonx Assistant service under the AI / Machine Learning section. Click on its entry to open the service details.

2. Click on Launch Watson Assistant to get to the watsonx Assistant Tool.

3. In the welcome dialog, create a new assistant by using slackbot as Assistant name, then click Next to start personalizing.

4. For the first question on deployment pick Web.

5. For the other questions answer for your role or with Other / Not sure at this time.

6. Click Next for the opportunity to customize the chat UI if desired.

7. Click Next and finalize by clicking Create.

   The new page includes a guided tour which you might want to complete if you are new to watsonx Assistant.

Add and configure a custom extension

Next, you are going to add and then configure a custom extension to watsonx Assistant and the newly created assistant.

1. In the dashboard on the lower left, click on Integrations, then on Build custom extension under Extensions.
2. In the multi-step dialog click Next, then enter events as Extension name and API for events database as Extension description. Click Next.
3. Select and upload the local file slackbot-openapi-spec.json, then click Next.
4. The last step lets you review the extension with included servers and operations. Once done click Finish.
5. Back on the Integrations page note the new events tile in the Extensions section. Click Add on that tile to configure the extension for the assistant.
6. The new dialog starts with a short overview. Click Next to get to the actual configuration. In the dropdown for Authentication type select API key auth and enter your chosen API key (MY_SECRET replacement).
7. For the Server variables use your deployment region, slackbot-backend as appname, and the Code Engine projectid of your app. Thereafter, the generated URL should match that of your Code Engine app. When done, click Next to get to the review page, then Finish and Close to get back to the Integrations page.

Create the first action

First, you are going to create an action to retrieve information about a single event identified by its name.

1. On the upper left, click on Actions and on the welcome page on Create action.

2. Click on the Start from scratch tile.

3. In the New action dialog, enter show me event details as example and click Save.

4. The next screen shows the step editor for the action with Step 1 open. In Assistant says type What is the event name?. Then, for Define customer response pick Free text as option. Leave And then as Continue to next step.

5. Click New step on the lower left to add Step 2. Leave the first parts (Assistant says, Define customer response) untouched, but under And then select Use an extension. In the dropdowns pick the events extension and its Event record by name operation. Thereafter, Parameters will show the possible inputs. By using the dropdown, assign for Set short_name to the value **Action step variables > 1. What is the event name?. It refers to the customer input from the previous step. Click on Apply to finish this step.

6. Add a New step. At the top change the selection so that Step 3 is taken with condition. Under Conditions and If select 2 Ran successfully. It refers to a result from using the extension in step 2.

7. Under Assistants says, you can compose the answer with the event details by referring to the output fields of the API call to the deployed app. Use I got these event details: followed by the Enter key to get to the next line. The editor supports Markdown format. Thus, use the - key to create a bulleted list. Add a list item with Name:, then click on the Insert a variable icon. From the dropdown select 2 body.shortname. Use the Enter key again to get to a new line with a list item. Add Location: with 2 body.location from the variables dropdown. Repeat for Begins, Ends, and Contact. Once done, set And then to End the action.

8. To handle errors in the extension, create another step with a condition. Now let the step react to 2 Ran successfully being false. Let the Assistant say Sorry, there was a problem and then end the action again.

   For the sake of simplicity, not all errors and conditions like empty results are handled.

9. Click on the Save icon on the upper right, then the X next to it to close the step editor.

10. On the bottom right select Preview to test the dialog.

11. In the chat, type show me event details and submit. The bot should respond What is the event name?. Now type Think and submit. Because the backend app uses a wildcard search, it should find the sample event with the name Think 2022 and return the details (see screen capture below).

You can click Inspect to see details on how the custom extension is utilized. You can check input and output variables and how the API function of the extension is invoked.

Action to gather data and insert a new record

Similar to retrieving a record it is possible to gather input about an event and add that data via the custom extension.

1. Switch to Actions and Created by you. Click New action.

2. Click on the Start from scratch tile.

3. In the New action dialog, enter add new event and Save.

4. Use How do you want to name the event? for Assistant says. The customer responds with Free text again.

5. Add a New step and have the Assistant say Where is the event?. Again, the customer may respond with Free text.

6. Add another step and ask When does the event begin?, but use Date for Define customer response.

7. Repeat the same but with When does the event end?.

8. Last, in a new step, ask What is the URL (web address) for the event? and let the customer respond with Free text.

9. Now, to confirm the input, create a new step. Use Is this correct? and as bulleted list show the gathered data. Add an item for Name, Location, Begin, End, and Contact and use Action variables relating to the input steps for the data (see screen capture below). Use Confirmation as customer response type.

   

10. Add a new step with the condition the previous confirmation being No. The Assistant could say Ok, let's start over. Set And then to Re-ask previous step(s) and select all previous steps.

11. In a new step, which should be step 8, react to the confirmation being Yes. Under And then select Use an extension. Configure the events extension with Insert a new event record as operation. Match the parameters to the Action variables for the steps, for example, shortname to 1. How do you want to name the event?

12. Create a new step with a condition 8 Ran successfully being true. Let the Assistant say A new record with ID VARIABLE was created. For VARIABLE select 8 body.eid from events (Step 8). End the action under And then.

13. Create a new step with the condition for 8 Ran successfully being false. Use something like It seems there was a problem creating the new event record for the Assistant to say and end the action under And then. Save and close the action with the icons in the upper right.

14. Save the action and then test the new action by clicking on Preview on the left and using the webchat. Type add new event and submit. When prompted by the bot, enter my conference as name, home office as location, pick dates for begin and end, and use http://localhost as URL. Thereafter, confirm that the data is correct.

When creating a chatbot, you may want to publish a chatbot. It is the controlled release of a version which allows rolling back changes and to continue with development without impacting the chatbot interacting with real customers.

Integrate with Slack

Now, you will integrate the chatbot with Slack.

1. On the lower left, click on Integrations.
2. In the integrations overview, in the section Channels, locate Slack and click Add.
3. Follow the step by step instructions to integrate the Draft environment of your chatbot with Slack. More information about it is available in the topic Integrating with Slack.
4. Once done, open up your Slack workspace. Begin a direct chat with the bot and say show me event details. Then, similar to above, answer with Think when prompted for an event name.

Remove resources

To clean up the resources for this tutorial, go to the IBM Cloud Resource List. Locate the service instances of watsonx Assistant and Db2 on Cloud and delete them. Similarly, locate the Code Engine project and delete it.

Depending on the resource it might not be deleted immediately, but retained (by default for 7 days). You can reclaim the resource by deleting it permanently or restore it within the retention period. See this document on how to use resource reclamation.

Related content

Here are additional resources on the topics covered in this tutorial.

• Build Great Chatbots, Fast
• Tips and Tricks for Using the IBM Cloud CLI