6.3 Update Copilot With Semantic Ranking

The next step is to update your API to use the semantic ranking capability. For this, you will update how it finds and retrieves SOW chunks, which are the blocks of content extracted from each SOW uploaded into the application.

Review the function

Following the function calling pattern used by your LangChain agent to retrieve data from the database, you will use a Python function to execute semantic ranking queries from your copilot. Within the src/api/app/functions/chat_functions.py file, the find_sow_chunks_with_semantic_ranking function has been provided for executing queries using the semantic_reranking UDF you added to your database in the previous step. Open it now in Visual Studio Code and explore the code with the function. You can also expand the section below to see the code inline.

Find SOW Chunks with Semantic Ranking code

async def find_sow_chunks_with_semantic_ranking(self, user_query: str, vendor_id: int = None, sow_id: int = None, max_results: int = 3):
        """
        Retrieves content chunks similar to the user query for the specified SOW.
        """

        # Get the embeddings for the user query
        query_embeddings = await self.__create_query_embeddings(user_query)

        # Create a vector search query
        cte_query = f"SELECT content FROM sow_chunks"
        cte_query += f" WHERE sow_id = {sow_id}" if sow_id is not None else f" WHERE vendor_id = {vendor_id}" if vendor_id is not None else ""
        cte_query += f" ORDER BY embedding <=> '{query_embeddings}'"
        cte_query += f" LIMIT 10"

        # Create the semantic ranker query
        query = f"""
        WITH vector_results AS (
            {cte_query}
        )
        SELECT content, relevance
        FROM semantic_reranking('{user_query}',  ARRAY(SELECT content from vector_results))
        ORDER BY relevance DESC
        LIMIT {max_results};
        """

        rows = await self.__execute_query(f'{query};')
        return [dict(row) for row in rows]

1. Generate embeddings (line 18): Azure OpenAI generates embeddings representing the user query.

2. Create vector search query (lines 21-24): The UDF expects vector search results as input, so a vector search query is created. The query selects the content field from the sow_chunks table and orders them by semantic similarity.

3. Create a semantic ranker query (lines 27-35): The results of the vector search query are required to call the semantic_reranking UDF.

   • Create CTE (lines 28-30): A common table expression (CTE) executes the vector search query and extracts the content values and relevancy scores from that query.
   • Execute the semantic_reranking UDF (lines 31-33): Using the results of the CTE, the results are reranked using the semantic_reranking UDF.
   • Limit results (line 34): The number of results is limited to ensure the more relevant records are sent to the LLM.

4. Return the results (lines 37-38): The query results are extracted and returned to the LLM.

Implement semantic ranker

To use the semantic ranking functionality instead of the vector search to retrieve SOW chunks, you must replace the function assigned to your LangChain agent's tools collection. You will replace the find_sow_chunks tool with the find_sow_chunks_with_semantic_ranking function.

1. In the VS Code Explorer, navigate to the src/api/app/routers folder and open the completions.py file.

2. Within the tools array, locate the following line:

   StructuredTool.from_function(coroutine=cf.find_sow_chunks),
   

3. Replace that line with the following:

   Insert the code to use the semantic ranking function!

   StructuredTool.from_function(coroutine=cf.find_sow_chunks_with_semantic_ranking),
   

4. Your new tools array should look like this:

   # Define tools for the agent to retrieve data from the database
   tools = [
       # Hybrid search functions
       StructuredTool.from_function(coroutine=cf.find_invoice_line_items),
       StructuredTool.from_function(coroutine=cf.find_invoice_validation_results),
       StructuredTool.from_function(coroutine=cf.find_milestone_deliverables),
       StructuredTool.from_function(coroutine=cf.find_sow_chunks_with_semantic_ranking),
       StructuredTool.from_function(coroutine=cf.find_sow_validation_results),
       # Get invoice data functions
       StructuredTool.from_function(coroutine=cf.get_invoice_id),
       StructuredTool.from_function(coroutine=cf.get_invoice_line_items),
       StructuredTool.from_function(coroutine=cf.get_invoice_validation_results),
       StructuredTool.from_function(coroutine=cf.get_invoices),
       # Get SOW data functions
       StructuredTool.from_function(coroutine=cf.get_sow_chunks),
       StructuredTool.from_function(coroutine=cf.get_sow_id),
       StructuredTool.from_function(coroutine=cf.get_sow_milestones),
       StructuredTool.from_function(coroutine=cf.get_milestone_deliverables),
       StructuredTool.from_function(coroutine=cf.get_sow_validation_results),
       StructuredTool.from_function(coroutine=cf.get_sows),
       # Get vendor data functions
       StructuredTool.from_function(coroutine=cf.get_vendors)
   ]
   

5. Save the completions.py file.

Test with VS Code

As you have done previously, you will test your updates using Visual Studio Code.

Start the API

Follow the steps below to start a debug session for the API in VS Code.

1. In Visual Studio Code Run and Debug panel, select the API Debugger option for your OS from the debug configurations dropdown list.

   

2. Select the Start Debugging button (or press F5 on your keyboard).

   

3. Wait for the API application to start completely, indicated by an Application startup complete. message in the terminal output.

   

Start the Portal

With the API running, you can start a second debug session in VS Code for the Portal project.

1. Return to the Run and Debug panel in Visual Studio Code and select the Portal Debugger option from the debug configurations dropdown list.

   

2. Select the Start Debugging button (or press F5 on your keyboard).

   

3. This should launch the Woodgrove Bank Contract Management Portal in a new browser window (http://localhost:3000/).

4. On the Dashboard page, enter the following message into the chat and send it:

   Paste the following prompt into the copilot chat box!

   Show me SOWs pertaining to cost management and optimization.
   

5. Observe the results provided using your UDF and semantic ranking.

Congratulations! You just learned how to leverage the semantic ranking capabilities in Azure Database for PostgreSQL!