Create a Custom App

Prerequisites:

The following shows the general steps/activities that will be carried out in setting up this integration. Note: It is advisable to use same E-Mail for signup across the 3 channels below. Step 1 -3 below are compulsory.

1. Sign up for PostgreSQL (Part of pre-requisites)

2. Sign up for iX Hello platform (Part of pre-requisites)

3. Create a custom app in iX Hello.

4. Publish the app

5. Test the Amazon Lex app.

Let's Create a Custom App:

1. Fill following details in the Add Custom window and click ‘Save’.

   1. Name: Postgres Assistant

   2. Custom Skill Type: Search

   3. Set this app as the Gen AI-App

   4. Click Save

Create Data Source:

In the top menu tabs, select “Data Sources” then “Create New Data Source”. Provide the following details:

DataSource 1: Gen AI

Name: GenAIPostgres-DS

Integration Configuration: Gen AI

Click: Save

DataSource 2: PostgreSQL

Name: PostgresDB-DS

Integration Configuration: PostgreSQLIntegration

Click: Save

Create Methods to fetch the data from Postgres

1. Next step is to create Methods. Methods allows you to fetch data from Postgres. The Postgres supported custom queries can be used to define the data to be fetched from the linked Postgres account.

2. iX Hello platform currently supports following Postgres methods:

   1. getallrecords

   2. RunSelectQuery

   3. Getitem

   4. addRecordWithoutPrimaryKey

   5. addRecordMethod

   6. updateRecordMethod

   7. DeleteRecord

The Sample Data uploaded in the client side - PostgreSQL Workbench

Method 1: getallrecordsMethod

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. getallrecordsMethod
Request Type	GetAllItems This is the method that you are using
Parameters	As explained in the previous table
Result Type	ListofRecords (Auto Selected) It indicates that the method can return one or more records
Record Limit	10
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Record Template	UserName:{{username}}, AppointmentTime:{{appointmnetTime}}, BookingID:{{bookingID}}, BookingStatus:{{bookingStatus}}, AppointmentDate:{{appointmentDate}}
Empty Result Template	How else can I help you?
Valid Result Template	{{Records}}

Sample Response:

Method 2: updateRecordMethod

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking
PrimaryKeyName	{{PrimaryKeyName}} - Column name of the primary key, enter the exact postgreSQL column name as stored in the table.
PrimaryKeyValue	{{PrimaryKeyValue}} - Actual Value for the primary key.
ItemData	Data in json format example : {"name":"John"} or {"Name":"John"} { "appointmentDate": "{{appointmentDate}}", "appointmenTime": "{{appointmenTime}}", "bookingStatus": "{{bookingStatus}}" }

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. updateRecordMethod
Request Type	PutItems This is the method that you are using
Parameters	As explained in the previous table
Result Type	Records (Auto Selected) It indicates that the method can return one or more records
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $ This selects all the records that is returned.
Empty Result Template	Sorry... No Data found
Valid Result Template	PrimaryKeyName:{{PrimaryKeyName}},PrimaryKeyValue:{{PrimaryKeyValue}}, Appointment Date:{{appointmentDate}}, Appointmen Time:{{appointmenTime}}, Booking Status:{{bookingStatus}}

Sample Response:

Method 3: DeleteRecordMethod

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking
PrimaryKeyName	{{PrimaryKeyName}} - Column name of the primary key, enter the exact postgreSQL column name as stored in the table.
PrimaryKeyValue	{{PrimaryKeyValue}} - Actual Value for the primary key.

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. DeleteRecordMethod
Request Type	DeleteItems This is the method that you are using
Parameters	As explained in the previous table
Result Type	Records (Auto Selected) It indicates that the method can return one or more records
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $ This selects all the records that is returned.
Empty Result Template	Sorry... No Record Deleted
Valid Result Template	This User is Deleted: PrimarykeyName:{{PrimaryKeyName}}, PrimaryKeyValue:{{PrimarykeyValue}}

Sample Response:

Method 4: GetitemMethod

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking
QueryParameters	{"userName": "John"}

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. GetitemMethod
Request Type	GetItems This is the method that you are using
Parameters	As explained in the previous table
Result Type	ListofRecords (Auto Selected) It indicates that the method can return one or more records
Record Limit	10
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Empty Result Template	How else can I help you?
Valid Result Template	UserName:{{username}}, AppointmentTime:{{appointmnetTime}}, BookingID:{{bookingID}}, BookingStatus:{{bookingStatus}}, AppointmentDate:{{appointmentDate}}

Sample Response:

Method 5: addRecordWithoutPrimaryKey

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking
ItemData	{"userName":"{{userName}}","appointmentDate":"{{appointmentDate}}", "appointmenTime":"{{appointmenTime}}", "bookingStatus":"{{bookingStatus}}"}

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. addRecordWithoutPrimaryKey
Request Type	PostItemWithoutPrimaryKey This is the method that you are using
Parameters	As explained in the previous table
Result Type	Records (Auto Selected) It indicates that the method can return one or more records
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $ This selects all the records that is returned.
Empty Result Template	Sorry... No Data found
Valid Result Template	userName:{{userName}}, Appointment Date:{{appointmentDate}}, AppointmenTime:{{appointmenTime}}, Booking Status:{{bookingStatus}}

Sample Response:

Method 6: addRecordMethod

This method requires three parameters. Please review the information below.

Parameters:

TableName	public.AppBooking
PrimaryKeyName	{{PrimaryKeyName}} - Column name of the primary key, enter the exact postgreSQL column name as stored in the table.
PrimaryKeyValue	{{PrimaryKeyValue}} - Actual Value for the primary key.
ItemData	Data in json format example : {"name":"John"} or {"Name":"John"} { "appointmentDate": "{{appointmentDate}}", "appointmenTime": "{{appointmenTime}}", "bookingStatus": "{{bookingStatus}}" }

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. addRecordMethod
Request Type	PosttItems This is the method that you are using
Parameters	As explained in the previous table
Result Type	Records (Auto Selected) It indicates that the method can return one or more records
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $ This selects all the records that is returned.
Empty Result Template	Sorry... No Data found
Valid Result Template	PrimarykeyName:{{userName}}, PrimarykeyValue:{{PrimarykeyValue}},Appointment Date:{{appointmentDate}}, Appointmen Time:{{appointmenTime}}, Booking Status:{{bookingStatus}}

Sample Response:

Method 7: RunSelectQueryMethod

This method requires one parameter. Please review the information below.

Parameters:

SelectStatement	{{SelectStatement}}

Other than these parameters, provide following standard inputs:

Method Name	You can give any name. This name will be displayed in the methods list. RunSelectQueryMethod
Request Type	RunSelectQuery This is the method that you are using
Parameters	As explained in the previous table
Result Type	ListofRecords (Auto Selected) It indicates that the method can return one or more records
Record Limit	15
Record JsonPath	Each method returns data in Json format and to pick any specific information from the json, we need to specify the Record JsonPath so iX Hello platform will pick that data in consideration while displaying results. Example: $ This selects all the records that is returned.
Empty Result Template	Sorry... No Data found
Record Template	User Name:{{userName}}, Appointment Date:{{appointmentDate}}, Appointmen Time:{{appointmenTime}},BookingID : {{bookingId}}, Booking Status:{{bookingStatus}}
Valid Result Template

Sample Response:

Method 8: Gen AI

• Name: Chat Completion RAG

• Request Type: chatcompletion-rag

• Parameters

  • Model: Select your preferred Generative AI model (e.g., Azure gpt-4o).

  • System Message: Provide instructions for the bot’s behavior:

  • This is a sample prompt/system message, this can be configured as per the use case need.

I am a concise and focused assistant, providing accurate information based solely on the provided Zendesk integrated methods. I aim to deliver clear and relevant answers to your queries from the Zendesk integrated methods.

• UserQuestion: {{UserInput}}

• Streaming: False

• SessionTime: 10

• OrganizationID: {{Context.OrganizationID}}

• EmployeeID: {{Context.OrganizationID}}

Configure Method Output:

• Result Type: ListofRecords

• Record Limit: 10

• Configure Method Result:

  • Record JsonPath: $.choices[0].message

  • Valid Result Template: {{Records}}

  • Record Template: {{content}}

  • Empty Result Template: "Is there anything else I can assist you with?"

• Uncheck: Automatically prompt for next question

• Save & Test

  • Click Save & Test. This will initiate a test API call.

Sample Response

Create New Intent

1. Click Create New Intent.

2. Enter Intent Details as Follows:

Name	addRecordIntent
Intent Type	Search
Intent Action	Execute Method
Data Source	PostgresDB-DS
Data Source Method	addRecordMethod
Dialog Delegation Strategy	Fallback To Skill
Action	Save the intent configuration

Similarly Create the Intents for rest of the Methods created:

• getallrecords - getallrecordsIntent

• RunSelectQuery - RunSelectQueryIntent

• Getitem - GetitemIntent

• addRecordWithoutPrimaryKey - addRecordWithoutPrimaryKeyIntent

• addRecordMethod - addRecordMethodIntent

• updateRecordMethod - updateRecordMethodIntent

• DeleteRecord - DeleteRecordIntent

• GenAIZendeskAssist-DS - ChatIntent

Slot Creation

Slot Creation Steps for Generative AI and Postgres

Slot: addRecordIntent

Parameter 1: PrimaryKeyValue

Parameter 2 : appointmentDate

Parameter 3: appointmenTime

Parameter 4: bookingStatus

Name	PrimaryKeyValue	appointmentDate	appointmenTime	bookingStatus
Description	PrimaryKeyValue	appointmentDate	appointmenTime	bookingStatus
Slot Order (Sequence Number)	1	2	3	4
Reprompt Message	Please provide any Name	Please provide Appointment Date (yyyy-mm-dd)	Please provide Appointmen Time (hh:mm)	Please provide booking status
Type	Text	Date	Text	Text
Custom App	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce
Intent	addRecordIntent	addRecordIntent	addRecordIntent	addRecordIntent
Enable	Is Required	Is Required	Is Required	Is Required
Action	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration

Create rest of the slots as per the parameters suggested.

Utterance

1. Navigate to Utterances

2. In your Custom App, go to the Utterances tab.

Create New Utterance: Enter Intent Details as Follows:

Intent	addRecordIntent	AddRecord withoutprimarykey	ChatIntent	DeleteRecord	RunSelectQuery	updateRecordIntent
Value	add the record	add the record without primarykey	Ask a question	Delete the record	Run the sql query	update the record
Custom App	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce
Intent	addRecordIntent	getAllTicketsDetailsIntent	getClosedStatusTicketsIntent	getOpenStatusTicketsIntent	getPendingStatusTicketsIntent	UpdateTicketIntent
Action	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration

Create rest of the Utterances as per the Intents created.

Input Configuration

1. For Generative AI Intent

• Navigate to Input Configuration

• In your Custom App, go to the Input Configuration tab.

Name	ChatInput	UpdateRecordInput	Run the SQL Query	InsertRecordInput	DeleteRecordInput
Custom Skill	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce	Postgres Assistant_498bd99d-8109-4abe-91cf-f57d7ac4f9ce
Custom Intent	CreateNewTicketIntent	UpdateRecordIntent	RunSelectQueryIntent	addRecordIntent	DeleteRecordIntent
Description	ChatInput	UpdateRecordInput	Run the SQL Query	InsertRecordInput	DeleteRecordInput
Data Source	GenAIPostgres-DS	PostgresDB-DS	PostgresDB-DS	PostgresDB-DS	PostgresDB-DS
Data Source Method	chatCompletionMethod	UpdateRecordMethod	RunSelectQueryMethod	addRecordMethod	DeleteRecordMethod
Enable Gen AI Toggle Button	No	Yes	Yes	Yes	Yes
Action	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration

Create rest of the Inputs as per the Intents and Methods created.

Input Parameter Configuration

These parameters are created if and only if the method parameter exists to each methods created.

Input Parameter Creation for: UpdateRecordInput

Search Parameter Name	bookingStatus	PrimaryKeyName	PrimaryKeyValue	appointmentDate	appointmenTime
Data Type	Text	Text	Text	Date	Text
Description	bookingStatus	PrimaryKeyName	PrimaryKeyName	appointmentDate	appointmenTime
Method Parameter	bookingStatus	PrimaryKeyName	PrimaryKeyName	appointmentDate	appointmenTime
Action	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration	Save the intent configuration

Test the App

Click on the three dots under "Action." A dropdown menu will appear; select the "Test" option to test the application.

Click the "Test" option to access the chat widget. Here, you'll see the bot's name and a welcome message, allowing you to start your conversation with the bot.

Congratulations! You have successfully completed building a custom app using PostgreSQL.