Build a Conversational Inquiry about Schools using Generative AI

Introduction

In this lab, you learn to build a conversational inquiry about schools using Generative AI wherein a user can ask questions about a school in chat and the chat widget uses Generative AI to provide context sensitive answers. This lab makes use of the APEX GenAI dynamic action called the Show AI Assistant.

Note: The screenshots in this workshop are taken using Dark Mode in APEX 24.2

Estimated Time: 20 minutes

Objectives

In this lab, you will:

Create AI Configuration with RAG sources.
Build a conversational chatbot using Generative AI.

Task 1: Create the Chat Page

Task 2: Configure the Prompt Context using AI Configuration

Create a Page Item to store the selected School ID. In the Rendering Tree, under Components, right-click Content Body and select Create Page Item.
In the Property Editor, enter/select the following:
- Under Identification:
  - Name: P2_SCHOOL_ID
  - Type: Hidden
  Click Save.
Navigate to Shared Components and select AI Configurations under Generative AI.
Generative AI Configurations contain information such as System Prompt, Welcome Message, and Retrieval-Augmented Generation (RAG) Sources to enable richer interactions with Generative AI services. We create an AI Configuration to store the system prompt for our chat.

In the Generative AI Configurations page, click Create.
Enter/select the following:
- Name: Learn More AI
- Service: Application Default (If configured, you can also choose other GenAI services from the list)
- System Prompt:
```
If the question cannot be answered based on the above context, say "Information Not Found!".
```
- Welcome Message: Welcome! How may I help you?
Click Create.
Retrieval-Augmented Generation (RAG) Sources are used by the Generative AI Service to improve response quality by providing relevant information.

Now, let's add a RAG Source to the AI Configuration. Click Create RAG Source.
In the RAG source page, enter/select the following:
- Name: School Context
- Source > Type: SQL Query

You will make use of the APEX Assistant to generate the SQL Query for the RAG Source. In the SQL Query code editor, click APEX Assistant to open a drawer where you can chat with the AI Assistant. If a dialog box appears to accept the Terms and Conditions, click Accept.

Enter the following prompt in the chat box and click Send:

Help me create a query that returns only one column concatenating the following information for the HIGHSCHOOLS table. Provide an alias for the column name as prompt_context.

    Please post the description of the column and the value, for example:

    'Overview of the school : '|| OVERVIEW_PARAGRAPH ||chr(10) || chr(13)||


    Overview of the school,
    Language Courses,
    Advanced Placement Courses,
    Diversity in Admission Policy,
    extra curricular activities ,
    Public Schools Athletic League (PSAL) sports for boys,
    Public Schools Athletic League (PSAL) sports for girls,
    facilities,
    Academic opportunities,
    Attendance rate,
    Graduation rate

    filtering by the id of the school

Page Designer

The AI Assistant suggests a SQL Query. You can provide further prompts to refine the query. Once you are happy with the query, click Insert.

The SQL query is inserted into the Code Editor. Verify that the where condition in the query has ID = :P2_SCHOOL_ID. Click Validate. The SQL query should look like the following:

SELECT 'Overview of the school: ' || OVERVIEW_PARAGRAPH || chr(10) || chr(13) ||
   'Language Courses: ' || LANGUAGE_CLASSES || chr(10) || chr(13) ||
   'Advanced Placement Courses: ' || ADVANCED_PLACEMENT_COURSES || chr(10) || chr(13) ||
   'Diversity in Admission Policy: ' || DIADETAILS || chr(10) || chr(13) ||
   'Extra Curricular Activities: ' || EXTRACURRICULAR_ACTIVITIES || chr(10) || chr(13) ||
   'Public Schools Athletic League (PSAL) sports for boys: ' || PSAL_SPORTS_BOYS || chr(10) || chr(13) ||
   'Public Schools Athletic League (PSAL) sports for girls: ' || PSAL_SPORTS_GIRLS || chr(10) || chr(13) ||
   'Facilities: ' || ADDTL_INFO1 || chr(10) || chr(13) ||
   'Academic Opportunities: ' || ACADEMIC_OPPORTUNITIES || chr(10) || chr(13) ||
   'Attendance rate: ' || ATTENDANCE_RATE || chr(10) || chr(13) ||
   'Graduation rate: ' || GRADUATION_RATE as prompt_context
from HIGHSCHOOLS
where ID = :P2_SCHOOL_ID;