# Canada Post Conversational BI
> A four-person 2024 capstone I led: a BI system that turned plain-language questions into SQL-backed tables, charts, and follow-up analysis.
- URL: https://mandalsuraj.com/blog/canada-post-ai-assisted-bi
- Author: Suraj Mandal (https://mandalsuraj.com)
- Published: 2024-03-27
- Project date: 2024-05-09
- Tags: ai, datascience, openai, postgresql, react, nodejs, webdev, fullstack, college, bisi
- LinkedIn post: https://lnkd.in/p/gnQrrgWJ
This four-person **Business Intelligence and System Infrastructure** capstone started at Algonquin College in February 2024.
I led the team, split ownership across data, backend, React, and prompting, then brought it together around one goal: useful reports without writing SQL.
## What Canada Post BI Does
01 · Plain Language
Ask A Business Question
Use plain language and optional filters. The app turns the request into a report plan.
02 · SQL And Charts
Build A Live Report
The server runs the generated PostgreSQL query and returns a table with a bar or line chart.
03 · Follow-Up
Explore The Next Question
Each report can suggest the next question, so an analyst can move from a total to a cause or region.
04 · Query Cache
Reuse Proven Queries
Full-text search finds saved questions and popular reports before another model call is needed.
## Agentic BI Before OpenAI's Agent Products
This timeline keeps only milestones that changed the same workflow: plan a query, run data work, and return a usable report.
Mar 2023
[GPT-3.5 Turbo API](https://openai.com/index/introducing-chatgpt-and-whisper-apis/)
OpenAI released the low-cost chat model that later powered our report planner.
Jun 2023
[Function Calling](https://openai.com/index/function-calling-and-other-api-updates/)
OpenAI added JSON-schema function calls. Apps could turn natural language into structured tool or database requests.
Jul 2023
[Code Interpreter](https://help.openai.com/en/articles/6825453-chatgpt-release-notes)
ChatGPT Plus gained file analysis, Python execution, charts, math, and file editing.
Nov 2023
[Assistants API And GPTs](https://openai.com/index/new-models-and-developer-products-announced-at-devday/)
OpenAI combined instructions, retrieval, Code Interpreter, and function calling for purpose-built assistants.
Feb 2024
Canada Post BI
Our app used GPT-3.5 Turbo to plan SQL and charts, query PostgreSQL, stream a report, and suggest the next question.
May 2024
[Interactive Data Analysis](https://openai.com/index/improvements-to-data-analysis-in-chatgpt/)
ChatGPT added connected files, expandable tables, interactive charts, and chart downloads.
Jan 2026
[OpenAI Data Agent](https://openai.com/index/inside-our-in-house-data-agent/)
OpenAI showed its internal agent for company data, business context, analysis, and reliable insight.
The capstone was narrow and purpose-built. Its core loop was already clear: ask in plain language, choose the data work, and return a report ready to use.
## Visual Proof
## Report Flow
```mermaid
flowchart LR
accTitle: Canada Post BI Report Architecture
accDescr: A question moves from the React client through the Node.js server, a saved query or model plan, PostgreSQL, and a live report.
client["React Client
Question And Filters"] --> server["Express + Socket.IO
Search + Report Events"]
server --> plan["Cache Or Model
Reuse SQL Or Plan A Report"]
plan --> data["PostgreSQL
Run The Query"]
data --> result["Live Report
Rows + Chart + Follow-Up"]
```
The React app uses the Express API for filters, autocomplete, and frequent reports. It uses Socket.IO for report loading states and final report data.
The main OpenAI adapter used `gpt-3.5-turbo` with forced function calling and a low temperature. A local DeepSeek Coder adapter was also available.
PostgreSQL stores the master data and weighted full-text cache. The server executes the chosen query and sends rows and chart data back. The source data stays behind the server.
## Failure Volume Fell Faster Than Parcel Volume
The live chart uses separate zero-based scales, so each series keeps its real shape without a misleading second axis. Jul 24 carried the most work and failures.
| Day | Parcel Volume | Failure Volume |
|---|---:|---:|
| Jul 23 | 71,382 | 4,679 |
| Jul 24 | 519,040 | 39,283 |
| Jul 25 | 495,333 | 28,288 |
| Jul 26 | 458,266 | 19,668 |
| Jul 27 | 431,890 | 16,750 |
| Jul 28 | 294,387 | 7,440 |
| Jul 29 | 111,677 | 3,061 |
## Searchable Query Cache
Each saved question had a weighted search vector. Prime questions ranked first, annotations added context, and execution counts raised common questions.
```sql
"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,
```
This cache reduced repeat model calls and made common questions appear sooner.
### GPT-3.5 Turbo Planned The Report
The OpenAI call forced one `report_generator` function. The function returned data SQL, optional chart SQL, chart fields, and follow-up questions as structured JSON.
```ts
const data = await openai.chat.completions.create({
messages,
model: "gpt-3.5-turbo",
temperature: 0.08,
tools,
tool_choice: {
type: "function",
function: { name: "report_generator" },
},
});
```
### Socket.IO Sent Partial And Final Results
The server first marked both views as loading. It then sent table rows, chart data, chart fields, and follow-up questions in the final event.
```ts
socket.emit("message_loading", {
data_loading: true,
chart_loading: true,
});
const report_data = await pool.query(ai_result.sql_data.replace(";", ""));
socket.emit("message_to_client", {
success: true,
report_data: report_data.rows,
report_chart: {
chart_type: CHART_TYPE[ai_result.report_chart.chart_type],
xField: ai_result.report_chart.xField,
yField: ai_result.report_chart.yField,
chart_data: report_chart.rows,
},
suggestions: ai_result.suggestions,
});
```
The capstone ran against a controlled sample database. A production version should validate generated SQL and use a read-only PostgreSQL role before execution.
## Results
- The app returned limited reports in less than one second in most tests.
- Larger tests stayed below five seconds in most cases.
- The test database held more than 600,000 rows.
- The test server had four ARM64 CPU cores and 24 GB of memory.
- Model API use cost less than US$0.50 during the test phase.
The model layer was replaceable. This kept provider-specific setup outside the report flow.