Canada Post, AI-Powered Data Insights Bot

March 27, 2024 (2mo ago)

This is Capstone project for Business Intelligence and System Infrastructure (2024) course at Algonquin College in Ottawa, Canada. The project was done in collaboration with Canada Post, where we were tasked with developing a web application that leverages Generative AI to assist in Business Intelligence. I was given the role to architect and spearhead the development of the application.

Github Repo ↗

Problem Statement

Canada Post produces a huge quantity of data every day. This information comprises details on mail amounts, delivery periods, customer responses and many more. Manual processing of this data can be time-consuming and prone to errors. For this reason, Canada post is seeking for ways through which the process of analyzing such data and drawing out useful recommendations can be automated.

Solution

Our proposal was to have a web application that uses Generative AI in Business Intelligence. The application enables Canada Post's managers and analysts to question the data and obtain immediate responses. To comprehend the questions, this application employs a Generative AI model and afterward uses SQL queries to pull out the needed information from the database. Moreover, it can display several graphs, charts, tables among other formats for better visualization of the findings obtained from the data.

Home Screen / Search UIInitial Report Generation

Architecture

The architecture of the web application is as follows:

  1. Frontend: The frontend of the web application is built using React.js. It provides a user-friendly interface for users to interact with the application.

  2. Backend: The backend of the web application is built using Node.js. It handles the communication between the frontend and the AI model. It receives the questions from the frontend, sends them to the AI model, and retrieves the answers to display to the users.

  3. Generative AI Model: The Generative AI model is built using OpenAI's GPT-3. It is trained on a large dataset of Canada Post's data and can generate SQL queries based on the questions asked by the users. The model is hosted on OpenAI's servers and is accessed via an API.

  4. Database: The database used is a PostgreSQL database that contains Canada Post's data. The database is on private cloud/local system and is accessed by the server from within the VPC to execute the SQL queries generated by the AI model.

  5. Caching & Autocomplete: We decided to use PostgreSQL's full-text search with weighted results, emphasizing 'prime' queries tailored to likely user questions and 'execution_count' for relevance. Every cache hit is one less call to openai and potential cost savings.

"query_annotation" text,
"execution_count" int4 DEFAULT 1,
"prime_query" bool DEFAULT false,
"search_vector" tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(query, '')), 'A') ||
    setweight(to_tsvector('english', coalesce("query_annotation", '')), 'B') ||
    setweight(to_tsvector('english', coalesce("execution_count"::text, '')), 'C') ||
    setweight(to_tsvector('english', CASE WHEN prime_query THEN 'true' ELSE 'false' END), 'D')
) STORED,

Features

Our core focus was on these elements since this is a government organization and data security is of utmost importance.

  1. Data Security: The application enforces data security by not storing any sensitive data in the prompt. The data stays within the private cloud and is accessed only by the server to execute the SQL queries.
  2. Performance: The application is optimized for performance and can provide insights in less than 1 second in most cases. The performance was tested on a server with 4 core arm64 CPU and 24GB RAM.
  3. Modularity: The application has a modular design that allows users to change the default model. Users can change the initialization, system prompt, prompt structure, and prompt calling to suit different models.
Click to view code snippet
interface AIModel {
    generateCompletion(prompt: string, context: string | undefined): Promise<any>;
}

enum Model {
    OPENAI = "OPENAI",
    ...
}

class AI {
    private models: { [key: string]: AIModel } = {};
    private API_KEY;

    constructor() {
        this.API_KEY = {
        OPENAI: "api_key_here",
        ...
        };
        const modelClasses = {
        [Model.OPENAI]: OpenAIModel,
        ...
        };

        Object.keys(Model).forEach(key => {
        const modelName = Model[key];
        this.models[modelName] = new modelClasses[modelName](this.API_KEY[modelName]);
        });
    }

    async generateCompletion(modelName: Model, prompt: string, context: string): Promise<any> {
        return this.models[modelName].generateCompletion(prompt, context);
    }
}

  1. Autocomplete: The application provides autocomplete suggestions to users as they type their questions. This helps users ask questions more efficiently and accurately.

  2. Cost-Effective: From the beginning, we tried to be cost-minded since a huge bill could come as a great surprise. During our entire testing phase, the cost was less than $0.5.

Notable Learnings:

Prompt Design

We designed the prompt at the very start of the project to get a baseline expectation of what the gen-ai can handle handled the following

  • Gave only json even without openai function calling being used.
  • Testable on public chat gpt web ui. Just copy paste the initial prompt and ask the queries starting with QUERY:.
  • Returns failure state, if given random query like QUERY: what is the capital of canada? which is not relevant to the dataset it will return a success: 0 with reason: ... all generated by model without entire data.

Data Security

  • We wanted to enforce data security from the get go, since we will be using openai and the data is sensitive. I made sure to not include any sensitive data in the prompt.
  • The data itself stays within private cloud only the prompt is sent expecting sql queries which are then used by the model.

Modularity

  • We have included a module system, sort of like plugin system which can be used to change the default model. This includes being able to change the initilization, system prompt, prompt structure, and the prompt calling itself since different models require different style/ways of interacting with them.

Performance

  • One of the major requirements was performance since tableu was already being used and was very slow to apply any type of filter to the dashboard realtime. Our app was able to provide the same data in less than 1 second most of the time if limit was set and almost always below 5 second.
  • This includes the time taken to call the openai api and get the response back by calling the sql queries provided to a database containing more than 600k rows.
  • The performance was tested on a Oracle Cloud server having 4 core arm64 cpu with 24gb ram. (Generally slower than x86 but still very good performance, like raspberry pi)

Thank you for reading!

If you have any questions or feedback, feel free to contact me here.