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

OpenAI released the low-cost chat model that later powered our report planner.

Jun 2023

Function Calling

OpenAI added JSON-schema function calls. Apps could turn natural language into structured tool or database requests.

Jul 2023

Code Interpreter

ChatGPT Plus gained file analysis, Python execution, charts, math, and file editing.

Nov 2023

Assistants API And GPTs

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

ChatGPT added connected files, expandable tables, interactive charts, and chart downloads.

Jan 2026

OpenAI 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

Business question search and saved report suggestions

Work centre failure report with chart and ranked table

Query refinement controls and report preview

## 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.