ConversationalDB: AI-Powered SQL & API Agent

BUET CSE FEST HACKATHON 2026 – Preliminary Round

ConversationalDB is an intelligent backend API that translates natural language questions into executable actions. It dynamically decides whether to query a PostgreSQL database (Sales, HR, Payroll) or fetch data from external APIs (Currency, Location) using Google’s Gemini LLM.

---

Features

• Natural Language → SQL Converts English questions into complex SQL queries (JOINs, aggregations, filtering).

• External API Integration Seamlessly handles non-database queries (e.g., currency conversion, geolocation) via predefined tools.

• Hybrid Querying Solves queries that require both database results and external API data (e.g., “Total revenue in EUR”).

• Time Travel Mode All temporal logic is strictly evaluated as if the current date is 2024-12-31.

• Standardized Output Responses follow a strict JSON format (scalar, record, table) for automated verification.

---

🛠️ Tech Stack

• Language: TypeScript / Node.js

• Framework: Express.js

• AI Model: Google Gemini 2.5 Flash

• Database: PostgreSQL

• Libraries & Tools:

  • pg (PostgreSQL client)
  • google-generative-ai SDK

---

⚙️ Setup & Installation

1. Prerequisites

• Node.js v18+
• PostgreSQL v14+
• Python 3.x (for running the checker)

---

2. Database Setup

You can set up the database using Docker or a local PostgreSQL installation.

Option A: Docker (Recommended)

# From the project root
docker-compose up -d

This will start a PostgreSQL container and automatically load the schema and seed data from:

data/generated_data.sql

Option B: Local PostgreSQL

1. Create a database:

CREATE DATABASE bcf_hackathon;

2. Import the schema and data:

psql -U your_user -d bcf_hackathon -f data/generated_data.sql

---

3. Backend Setup

Navigate to the backend directory and install dependencies:

cd backend
npm install

---

4. Environment Variables

Create a .env file in the backend/ directory based on .env.example:

cp .env.example .env

Configure the environment variables:

PORT=8080
GEMINI_API_KEY=your_actual_gemini_api_key_here
DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=postgres
DB_NAME=bcf_hackathon
DB_PORT=5432

---

▶ Running the Application

Start the Backend Server

# Inside the backend directory
npm start

The server will start at:

http://localhost:8080

Health Check

Visit:

http://localhost:8080/health

Expected response:

{
  "status": "ok",
  "database": "connected"
}

---

🧪 Testing (Running the Checker)

The project includes a Python-based checker to validate your API against official test cases.

1. Install Python Dependencies

pip install requests

2. Run the Checker

Ensure the backend is running on port 8080, then execute:

# From the project root
python checker/checker.py -b http://localhost:8080

Note: If you are using updated or local test cases, run checker/checker_local.py instead (if provided).

---

Project Structure

📦 bcf-2026-hackathon-preliminary
├── 📂 backend
│   ├── 📂 src
│   │   ├── agent.ts        # Core logic: LLM interaction & tool routing
│   │   ├── db.ts           # PostgreSQL connection
│   │   ├── index.ts        # Express server entry point
│   │   ├── tools.ts        # External API tool implementations
│   │   └── ...
│   ├── .env                # Environment variables (not committed)
│   └── package.json
├── 📂 checker
│   ├── checker.py          # Official verification script
│   └── test_cases.json     # Questions and expected answers
├── 📂 data
│   └── generated_data.sql  # Database schema and seed data
└── docker-compose.yml      # Docker setup for PostgreSQL

---

API Reference

POST /query

Main endpoint to ask natural language questions.

Request

{
  "question": "Which customer paid the most?",
  "llm": "gemini-2.5-flash"
}

Response

{
  "question": "Which customer paid the most?",
  "llm": "gemini-2.5-flash",
  "result_type": "record",
  "columns": ["customer_name"],
  "rows": [["Acme Inc"]],
  "meta": {
    "row_count": 1,
    "source": "database"
  }
}

---

Troubleshooting

• Database Connection Failed Verify .env credentials and ensure the PostgreSQL container is running:

  docker ps

• Gemini API Errors Ensure GEMINI_API_KEY is valid and active.

• Checker: Connection Refused Make sure the backend is running and the correct base URL is passed:

  -b http://localhost:8080

---

Notes

• All date/time-based queries are evaluated as if the current date is 2024-12-31.
• Output formats are strict to ensure compatibility with the automated checker.

---

Good luck in BUET CSE FEST Hackathon 2026 🚀