Project Proposal
1. Introduction
QueryPilot X is a web-based AI business analytics assistant designed for decision makers who need information from an organizational database but do not regularly write SQL. It also generates recurring business reports and records important database changes so that managers can respond promptly.
1.1 Background Overview
Organizations store their sales, orders, inventory and customer transactions in relational databases. When a CEO or manager asks a new question - for example, “Why did revenue fall last month in Dhaka, particularly for electronics?” - an analyst may need to write SQL involving several tables, dates, filters and calculations. Conventional dashboards help with predefined metrics, but unexpected follow-up questions still require manual work.
AI-based natural-language-to-SQL systems can turn business questions into database queries. However, answering a question is only part of the daily workflow. Managers also need visual explanations, periodic summaries and visibility into direct database edits. QueryPilot X brings these tasks into one controlled application for a single demonstration business database.
1.2 Problem Statement
The problem has three connected parts. First, non-technical decision-makers cannot easily retrieve ad hoc information when a question needs complex SQL joins, aggregations or multiple conditions. Second, preparing weekly or monthly performance reviews manually takes time and can delay action. Third, edits made directly in a database may not be noticed immediately by managers, even when they affect important business values.
Our project aims to reduce these difficulties through AI-assisted querying and explanation, scheduled analysis and database-level audit logging. The first version will use one controlled PostgreSQL business database rather than attempt to connect securely to every company system. AI will help interpret questions and summarize evidence; exact figures and change records will come from database queries and audit logs.
1.3 Objectives
General Objective: Develop a secure, user-friendly system that allows business managers to ask questions in natural language, understand business performance and stay informed about important database changes.
AI Objective: Use a large language model (LLM) to convert business questions into safe, read-only SQL and to explain computed business metrics in clear, actionable language.
Specific objectives:
- Translate multi-condition business questions into SQL for an approved database schema.
- Present verified query results through tables and suitable line or bar charts.
- Generate scheduled reports with revenue comparisons, notable changes and evidence-based suggestions.
- Keep a row-level audit trail and email managers about qualifying direct database modifications.
- Provide role-based access and maintain visibility into the SQL, data and assumptions used.
1.4 Scope
In scope: A responsive web application for a sample sales/retail database; English-language questions; approved analytical SELECT queries; revenue, orders and product/category/region metrics; automatic chart selection; configurable daily or weekly reports; PDF/email summaries; and audit alerts for INSERT, UPDATE and DELETE events on selected tables.
Out of scope: AI-generated INSERT/UPDATE/DELETE statements, connecting multiple client databases, unrestricted internet research, custom LLM training, production-grade intrusion detection, proven causal analysis, and enterprise-wide ERP integration.
The AI will generate queries and explanations, but it will not change data, bypass access control or treat correlations as proof of the cause of a revenue drop. An administrator will configure the approved tables, metrics and notification recipients.
1.5 Stakeholders
| Stakeholder | Role and interest |
|---|---|
| CEO / business manager | Asks performance questions, schedules reports and receives change alerts. |
| Business analyst | Checks detailed results, filters, visualizations and AI explanations. |
| Database administrator | Configures access, trusted application accounts and audit triggers. |
| System administrator / project team | Maintains the web app, scheduler, AI integration and email service. |
| Organization / data owner | Sets rules for database access, retention and business data privacy. |
1.6 Proposed Solution
The solution has three coordinated workflows, each backed by a controlled PostgreSQL database:
Workflow A - Ask and analyze: The user types a business question. The backend supplies the LLM with only the approved schema and metric definitions. A SQL parser and permission checks validate the generated statement before a read-only database role executes it. The user receives the SQL, a data table, a chart and a concise interpretation.
Workflow B - Scheduled business intelligence: The manager chooses a daily or weekly schedule. A cron-style scheduler runs predefined KPI queries, compares current and previous periods, uses the LLM to draft a narrative based on those computed figures, and prepares a report showing revenue and order trends, the biggest category/region declines, possible recovery actions, and an email-ready PDF. Recommendations will be based on measured changes, not invented causes.
Workflow C - Change monitoring: Database triggers write before/after row values and database-account details to an audit table. When a monitored event is made using an account outside the trusted application role, the notification worker prepares an email with the changed fields and business impact warning. All monitored changes remain visible in the audit history.
Database auditing is implemented using PostgreSQL triggers, not periodic AI checks. Cron-style jobs are used for scheduled analytics and for retrying pending notification deliveries. The system identifies direct changes using separate database roles; this is an audit indicator, not a guarantee that every unauthorized act can be identified.
1.7 AI Features
Existing AI Features: Current natural-language BI and text-to-SQL tools can help users ask data questions without writing SQL. Their availability, security features and dashboard coverage vary by product. QueryPilot X is not claiming that text-to-SQL is a new invention; its project value is the integration of controlled querying, scheduled interpretation and database audit alerts in one small system.
Proposed AI features and the work they replace:
| Feature | Input → output | Manual work reduced |
|---|---|---|
| Natural language to SQL | Business question + approved schema → validated SELECT statement | Writing joins, filters and aggregate queries |
| Visual analysis guidance | Query result + metric type → suitable chart and short insight | Choosing a chart and explaining patterns |
| Report commentary | Computed KPIs and period changes → summary, risks and recommendations | Writing recurring business performance reviews |
AI Techniques: LLM API integration, schema-aware prompting (retrieval of relevant database metadata), structured output validation and constrained SQL generation. Business comparisons use deterministic SQL and Python calculations; recommendations are generated only after numerical evidence is supplied.
Why AI Is Essential: Without AI, the system could still store audit logs and show predefined reports, but it could not perform its main function: translating flexible, multi-condition business questions into SQL and turning computed findings into natural-language explanations.
2. System Requirements
The following requirements are limited to the four-week prototype and will be verified using a sample sales database.
| ID | Functional requirement (the system shall...) |
|---|---|
| FR-01 | Allow authorized manager, analyst and administrator accounts to log in and use role-appropriate pages. |
| FR-02 | Accept English natural-language questions with dates, product categories, regions and multiple conditions. |
| FR-03 | Generate a SQL SELECT query using only approved database tables and columns. |
| FR-04 | Reject unsafe or unauthorized SQL, enforce read-only execution, a result limit and a query timeout. |
| FR-05 | Show the generated SQL, query results and an error message when a question cannot be answered. |
| FR-06 | Provide line and bar charts for suitable time-series or category-based results. |
| FR-07 | Compare revenue or order metrics between selected periods and identify the largest contributing segments. |
| FR-08 | Allow managers to schedule daily or weekly analytical reports and manage notification recipients. |
| FR-09 | Generate a report containing key metrics, comparisons, chart(s), AI-written interpretation and practical recommendations. |
| FR-10 | Record the old/new values, table, primary key, operation, timestamp and database account for monitored database changes. |
| FR-11 | Queue an email alert for monitored changes made by accounts outside the trusted application database role. |
| FR-12 | Provide a searchable audit-history view and an email-delivery status for administrators. |
Non-functional requirements
| Area | Requirement / target |
|---|---|
| Security | The LLM has no database write permissions; SQL parsing, allowlists and PostgreSQL read-only credentials are enforced. |
| Reliability | Audit events are committed in the same database transaction as monitored row changes; email failures are recorded and retried. |
| Privacy | Only approved schema context and the minimum metrics needed for an explanation are sent to the LLM API. |
| Performance | For a 50,000-row demonstration dataset, common queries should return within 15 seconds under normal test conditions. |
| AI evaluation | Test at least 30 representative questions and report result-equivalence accuracy; target at least 80% correct results. |
| Usability | Display plain-English explanations, visible SQL and readable charts; use clear feedback for unsupported requests. |
3. Tools and Technologies
| Component | Planned technology | Purpose |
|---|---|---|
| Frontend | React, TypeScript, Tailwind CSS, Recharts | Question interface, filters, dashboard, charts and schedules. |
| Backend API | Python, FastAPI, Pydantic, SQLAlchemy | Authentication, business APIs, query controls and orchestration. |
| Database | PostgreSQL, PL/pgSQL triggers, Alembic | Business records, audit log, database roles and schema migrations. |
| AI integration | LLM API, schema-aware prompts, SQLGlot | Question-to-SQL generation, SQL syntax checks and grounded report drafting. |
| Job automation | APScheduler (cron triggers), background worker | Daily/weekly report jobs and queued email retries. |
| Reports / alerts | ReportLab, Python SMTP email client | Downloadable PDF reports and manager notifications. |
| Testing / collaboration | Pytest, Git, GitHub, Postman | API testing, query evaluation, version control and team integration. |
Implementation note: The prototype will use one PostgreSQL database with separate read-only analytics and application-write roles. A small seed dataset will simulate products, orders, order items, customers and regions, including unit prices and costs where needed for gross-margin metrics. The LLM provider may be chosen according to accessible API limits; model training is not required.
4. Project Timeline and Work Plan
The development period is one month, with three group members working in parallel. The milestones below prioritize a complete demonstrable system rather than enterprise-scale features.
| Week | Milestone / tasks | Expected output |
|---|---|---|
| Week 1 | Finalize data schema and user stories; set up repository, UI skeleton and API; seed demo database; establish database roles. | Schema, clickable UI, working API and populated test database. |
| Week 2 | Build text-to-SQL prompting, validation and read-only execution; connect tables and line/bar chart views. | End-to-end natural-language query demonstration. |
| Week 3 | Add daily/weekly scheduler and generated reports; implement audit triggers, audit dashboard and email-alert queue. | Scheduled report demonstration and direct-SQL change alert. |
| Week 4 | Integrate modules, harden access, test 30 AI questions, verify alerts, fix defects and prepare user guide and demo. | Test results, final prototype, presentation and submitted report. |
Team task distribution
| Member | Primary responsibility | Shared responsibility |
|---|---|---|
| Member 1 | PostgreSQL schema, database roles, audit triggers, backend APIs and access controls. | Integration, database test cases, final review. |
| Member 2 | React interface, user flows, data table, charts, schedule and audit-history screens. | Usability testing, figures, final demo. |
| Member 3 | Schema-aware LLM prompting, SQL validation, business analysis, scheduling, PDF and email jobs. | AI evaluation, documentation, integration. |
Milestone acceptance: A successful demo must (i) answer a multi-condition revenue question, (ii) create a chart and scheduled report, and (iii) email a before/after audit alert after a controlled direct SQL update.
5. Optional Sections
5.1 Existing System
In the usual manual workflow, an executive requests an answer from an analyst, the analyst writes SQL or updates a dashboard, and a report is assembled separately. Database administrators may inspect logs or receive generic monitoring alerts. Some business-intelligence products already offer natural-language queries, but this student prototype focuses on one consistent workflow connecting ad hoc answers, recurring management reports and row-level change notifications.
5.2 System Architecture
The frontend communicates only with the FastAPI backend. The backend handles authentication, metric definitions and approved schema selection. An AI service proposes SQL, while SQLGlot and application-side allowlists reject unsupported statements before execution by a read-only database role. Query results and aggregate metrics drive charts and the report engine. An independent scheduler executes reports, while PL/pgSQL triggers persist monitored database changes in an audit table. A background worker sends queued emails and records delivery status.
To detect likely direct database edits, the application uses a dedicated trusted write role distinct from the administrator or manual SQL account. Triggers record changes from all monitored writers; the alerting rule highlights writes by non-trusted roles. Highly privileged actors may disable logging, so the prototype does not claim tamper-proof auditing.
5.3 Ethical, Legal, and Social Considerations
- Data privacy: Use a synthetic or explicitly authorized dataset and avoid sending customer-level personal information to the AI provider.
- Access control: Restrict database roles and email recipients; do not expose sensitive old/new values beyond authorized recipients.
- Trustworthy explanations: Display calculation periods and figures; label business causes as hypotheses rather than verified causal explanations.
- Transparency: Keep generated SQL visible and retain audit records for troubleshooting and accountability.
- Data protection: Apply organizational policies and applicable data-protection rules before using real client records.
5.4 Future Work
After the course submission, possible improvements include support for more databases, Bangla questions, anomaly detection, forecasting and messaging-app alerts. These remain outside the one-month scope.