Enterprise Data Agent

Live link: https://idda-frontend-g4f0vvgsc-harsh3456ds-projects.vercel.app/

A full-stack Natural Language to SQL (NL2SQL) application that lets users connect to enterprise databases, generate comprehensive data documentation, and query data using plain English — powered by Google Gemini AI.

Overview

Enterprise Data Agent bridges the gap between non-technical stakeholders and complex database systems. Users connect to any supported database, auto-generate a full data dictionary (schema, profiling, quality metrics), and then ask questions in natural language — the system translates them to SQL, executes safely, and returns results with explanations.

Supported Databases:

Database	Driver	Default Port
MySQL	PyMySQL	3306
PostgreSQL	psycopg2	5432
SQL Server	pyodbc	1433
Oracle	oracledb (via SQLAlchemy)	1521
SQLite	Built-in (SQLAlchemy)	N/A
Snowflake	snowflake-connector-python	443

Architecture

┌──────────────────────────────────────────────────────────┐
│                   React Frontend                         │
│           (Vite + Tailwind CSS + TypeScript)              │
│  ┌──────────┬───────────┬───────────┬──────────────────┐ │
│  │Connection│  Schema   │  Query    │  Documentation   │ │
│  │  Panel   │  Explorer │ Assistant │     Center       │ │
│  └──────────┴───────────┴───────────┴──────────────────┘ │
└──────────────────────┬───────────────────────────────────┘
                       │ HTTP (Axios)
                       ▼
┌──────────────────────────────────────────────────────────┐
│                 FastAPI Backend                           │
│  ┌──────────┬───────────┬───────────┬──────────────────┐ │
│  │ API      │ Chat      │ Metadata  │   Docs           │ │
│  │ Server   │ Handler   │ Extractor │   Builder        │ │
│  ├──────────┼───────────┼───────────┼──────────────────┤ │
│  │ SQL      │ LLM       │ Profiler  │   Masking        │ │
│  │ Guard    │ Client    │           │                  │ │
│  └──────────┴───────────┴───────────┴──────────────────┘ │
│                    Connectors Layer                       │
│  ┌────────┬────────┬───────┬────────┬───────┬──────────┐ │
│  │ MySQL  │Postgres│ MSSQL │ Oracle │SQLite │Snowflake │ │
│  └────────┴────────┴───────┴────────┴───────┴──────────┘ │
└──────────────────────────────────────────────────────────┘

Tech Stack:

Layer	Technology
Frontend	React 19, Vite 7, Tailwind CSS 4, TypeScript
Backend	Python 3.12+, FastAPI, SQLAlchemy 2.0, Pydantic
AI	Google Gemini (gemini-1.5-flash-latest)
Security	SQL Guard, PII masking, CORS lockdown

Features

Auto-Documentation — Connects to a database and generates a full data dictionary with column types, nullability, primary/foreign keys, sample values, and quality metrics.
Natural Language Queries — Ask questions in plain English; the system generates, validates, and executes SQL safely.
SQL Guard — Every generated query passes through multi-layer validation: blocks DDL/DML/admin commands, rejects multi-statement queries, enforces row limits.
Auto-Correction — If a generated SQL query fails, the system retries with error context (configurable retries).
PII Masking — Automatically detects and masks sensitive columns (email, phone, SSN, etc.) in query results.
Data Profiling — Computes row counts, null percentages, unique counts, min/max values, and top-N frequency distributions.
Sleek Dark Mode UI — Black/dark grey backgrounds with Electric Blue (#007BFF) accents, fully responsive.
Versioned Outputs — Documentation snapshots are timestamped and stored with a latest/ symlink.

Prerequisites

Requirement	Version	Notes
Python	3.12+	3.14 tested and supported
Node.js	18+	For frontend build
npm	9+	Comes with Node.js
Git	Any	For cloning
Gemini API Key	—	From Google AI Studio

Database-specific drivers (installed automatically via requirements.txt):

MySQL: PyMySQL
PostgreSQL: psycopg2-binary
SQL Server: pyodbc
Oracle: oracledb
Snowflake: snowflake-connector-python

Project Structure

enterprise_data_agent/
├── api_server.py                # FastAPI application entry point
├── package.json                 # Backend npm scripts (optional)
├── app/
│   ├── main.py                  # CLI entry + connector factory
│   ├── config/
│   │   └── settings.py          # Pydantic Settings (env/CLI config)
│   ├── ai/
│   │   ├── llm_client.py        # Gemini API wrapper with retry
│   │   └── prompts.py           # System/user prompt templates
│   ├── chat/
│   │   └── handler.py           # NL2SQL pipeline orchestrator
│   ├── connectors/
│   │   ├── base.py              # DatabaseConnector ABC
│   │   ├── mysql.py             # MySQL connector
│   │   ├── postgres.py          # PostgreSQL connector
│   │   ├── sqlserver.py         # SQL Server connector
│   │   ├── oracle.py            # Oracle connector (SQLAlchemy)
│   │   ├── sqlite.py            # SQLite connector
│   │   └── snowflake.py         # Snowflake connector
│   ├── metadata/
│   │   └── extractor.py         # Schema introspection via SQLAlchemy
│   ├── profiling/
│   │   └── profiler.py          # Statistical data profiling
│   ├── docs/
│   │   ├── builder.py           # Documentation assembly
│   │   └── markdown_gen.py      # Markdown report generator
│   ├── security/
│   │   ├── sql_guard.py         # SQL validation & safety checks
│   │   └── masking.py           # PII detection & masking
│   ├── storage/
│   │   └── vector_store.py      # Vector store integration
│   └── utils/
│       └── logger.py            # Loguru configuration
├── frontend/
│   ├── src/
│   │   ├── App.tsx              # Main layout
│   │   ├── main.tsx             # React entry
│   │   ├── types.ts             # TypeScript interfaces
│   │   └── components/
│   │       ├── Header.tsx
│   │       ├── ConnectionPanel.tsx
│   │       ├── TableExplorer.tsx
│   │       ├── SchemaGrid.tsx
│   │       ├── QueryAssistant.tsx
│   │       └── DocumentationCenter.tsx
│   ├── index.html
│   ├── package.json
│   ├── vite.config.ts
│   └── tailwind.config.js
├── tests/
│   └── test_db_connectivity.py  # DB connectivity & SQL Guard tests
└── outputs/                     # Generated documentation (git-ignored)

Setup & Installation

1. Clone the Repository

git clone https://github.com/Harsh3456D/Intelligent-Data-Dictionary-Agent.git
cd HackFest

2. Backend Setup

# Create virtual environment
python -m venv venv

# Activate (Windows)
venv\Scripts\activate

# Activate (macOS/Linux)
source venv/bin/activate

# Install Python dependencies
pip install -r requirements.txt

Note: If you encounter version conflicts with great_expectations or grpcio-status, those have already been resolved in the updated requirements.txt.

3. Frontend Setup

cd enterprise_data_agent/frontend

# Install Node dependencies
npm install

cd ../..

4. Environment Configuration

Create a .env file in the enterprise_data_agent/ directory:

cp enterprise_data_agent/.env.example enterprise_data_agent/.env

Or create it manually — see the Configuration section below.

Configuration

All settings are loaded from environment variables or a .env file located at enterprise_data_agent/.env.

Required Settings

Variable	Description	Example
`GEMINI_API_KEY`	Google Gemini API key (required)	`AIzaSy...`

Database Settings

Variable	Description	Default
`DB_TYPE`	Database backend type	`mysql`
`DB_HOST`	Database hostname	—
`DB_PORT`	Database port	Auto-detected
`DB_NAME`	Database name (or SQLite path)	—
`DB_USER`	Database username	—
`DB_PASSWORD`	Database password	—
`DB_SCHEMA`	Schema name	`public`

Oracle-Specific

Variable	Description	Default
`ORACLE_SERVICE_NAME`	Oracle service name	—

Snowflake-Specific

Variable	Description	Default
`SNOWFLAKE_ACCOUNT`	Snowflake account	—
`SNOWFLAKE_WAREHOUSE`	Snowflake warehouse	—
`SNOWFLAKE_ROLE`	Snowflake role	—

Safety & Limits

Variable	Description	Default
`GEMINI_MODEL`	Gemini model for NL2SQL	`gemini-1.5-flash-latest`
`QUERY_ROW_LIMIT`	Max rows returned per query	`100`
`STATEMENT_TIMEOUT_MS`	Query timeout in milliseconds	`30000`
`MAX_AUTOCORRECT_RETRIES`	Retries on SQL generation failure	`2`
`LOG_LEVEL`	Logging level	`INFO`

Example `.env` File

# AI
GEMINI_API_KEY=AIzaSyXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
GEMINI_MODEL=gemini-1.5-flash-latest

# Database
DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=mydb
DB_USER=root
DB_PASSWORD=secret
DB_SCHEMA=public

# Safety
QUERY_ROW_LIMIT=100
STATEMENT_TIMEOUT_MS=30000
MAX_AUTOCORRECT_RETRIES=2
LOG_LEVEL=INFO

Running the Application

Start the Backend

cd enterprise_data_agent

# Development (auto-reload)
uvicorn api_server:app --reload --host 0.0.0.0 --port 8000

# Production
uvicorn api_server:app --host 0.0.0.0 --port 8000 --workers 4

The API will be available at http://localhost:8000.

Start the Frontend

In a separate terminal:

cd enterprise_data_agent/frontend
npm run dev

The UI will be available at http://localhost:5173.

Using the Application

Open http://localhost:5173 in your browser.
In the Connection Panel, enter your database credentials and click Connect & Generate Docs.
The system will introspect the schema, profile all tables, and generate documentation.
Use the Table Explorer to browse tables and view column details.
Use the Query Assistant to ask questions in plain English (e.g., "Show me the top 10 customers by revenue").
View the generated Documentation in the Documentation Center.

Connecting to Databases

MySQL

DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=my_database
DB_USER=root
DB_PASSWORD=my_password

PostgreSQL

DB_TYPE=postgresql
DB_HOST=localhost
DB_PORT=5432
DB_NAME=my_database
DB_USER=postgres
DB_PASSWORD=my_password
DB_SCHEMA=public

SQL Server

DB_TYPE=mssql
DB_HOST=localhost
DB_PORT=1433
DB_NAME=my_database
DB_USER=sa
DB_PASSWORD=my_password
DB_SCHEMA=dbo

Oracle

DB_TYPE=oracle
DB_HOST=localhost
DB_PORT=1521
DB_USER=system
DB_PASSWORD=my_password
ORACLE_SERVICE_NAME=XEPDB1

SQLite

DB_TYPE=sqlite
DB_NAME=/path/to/database.db

No host, port, or credentials needed. The DB_NAME field is the file path to the SQLite database.

Snowflake

DB_TYPE=snowflake
DB_NAME=my_database
DB_USER=my_user
DB_PASSWORD=my_password
DB_SCHEMA=PUBLIC
SNOWFLAKE_ACCOUNT=abc12345.us-east-1
SNOWFLAKE_WAREHOUSE=COMPUTE_WH
SNOWFLAKE_ROLE=SYSADMIN

API Reference

`GET /`

Health check.

Response:

{ "status": "running" }

`POST /generate-docs`

Connect to a database and generate full documentation.

Request Body:

{
  "db_type": "mysql",
  "host": "localhost",
  "port": 3306,
  "database": "my_database",
  "username": "root",
  "password": "secret",
  "schema": "public"
}

Response:

{
  "status": "Documentation generated",
  "tables": ["users", "orders", "products"]
}

`POST /query`

Execute a natural language query against the connected database.

Request Body:

{
  "question": "Show me the top 5 customers by total spend"
}

Response:

{
  "sql": "SELECT customer_name, SUM(amount) AS total FROM orders GROUP BY customer_name ORDER BY total DESC LIMIT 5",
  "rows": [...],
  "row_count": 5,
  "explanation": "This query aggregates order amounts by customer..."
}

`GET /docs/latest`

Retrieve the most recently generated documentation.

Response: Full documentation JSON object with schema metadata, profiling results, and quality metrics.

Security

The application implements multiple security layers:

Layer	Protection
SQL Guard	Blocks DDL (DROP, ALTER, CREATE), DML (INSERT, UPDATE, DELETE), admin commands (GRANT, REVOKE), transaction control, and multi-statement queries
Row Limits	Enforces configurable row caps via LIMIT/TOP/FETCH FIRST/ROWNUM
PII Masking	Auto-detects and masks sensitive columns (email, phone, SSN, credit card)
CORS Lockdown	Restricted to `localhost:5173` and `localhost:3000` only
Error Sanitization	Internal errors are logged but never exposed to API responses
Read-Only Mode	Only SELECT and WITH queries are permitted
Password Encoding	Special characters in passwords are URL-encoded to prevent injection
Parameterized Queries	Timeout settings use parameterized queries (not string interpolation)

Testing

Run All Tests

cd enterprise_data_agent

# Activate virtual environment first
python tests/test_db_connectivity.py

Test Suites

Suite	Tests	Description
`TestSQLiteConnector`	8	Connect/disconnect, SELECT, filter, aggregate, JOIN, row limit, dialect, error handling
`TestSQLGuard`	10	Safe SELECT, blocks DDL/DML, multi-statement, comment-hidden attacks, LIMIT append, Oracle ROWNUM
`TestOracleConnector`	2	Connect + query, dialect (skips if no Oracle server)

Oracle Test Setup

To run Oracle tests, set these environment variables:

set ORACLE_USER=system
set ORACLE_PASSWORD=my_password
set ORACLE_HOST=localhost
set ORACLE_PORT=1521
set ORACLE_SERVICE=XEPDB1

Deployment Guide

Option 1: Local Development

This is the quickest way to get started:

# Terminal 1 — Backend
cd enterprise_data_agent
uvicorn api_server:app --reload --port 8000

# Terminal 2 — Frontend
cd enterprise_data_agent/frontend
npm run dev

Option 2: Production with Docker

Dockerfile (Backend)

FROM python:3.12-slim

WORKDIR /app

# Install system dependencies for database drivers
RUN apt-get update && apt-get install -y \
    gcc \
    libpq-dev \
    freetds-dev \
    && rm -rf /var/lib/apt/lists/*

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY enterprise_data_agent/ ./enterprise_data_agent/

WORKDIR /app/enterprise_data_agent

EXPOSE 8000

CMD ["uvicorn", "api_server:app", "--host", "0.0.0.0", "--port", "8000", "--workers", "4"]

Dockerfile (Frontend)

FROM node:20-alpine AS build

WORKDIR /app
COPY enterprise_data_agent/frontend/package*.json ./
RUN npm ci
COPY enterprise_data_agent/frontend/ ./
RUN npm run build

FROM nginx:alpine
COPY --from=build /app/dist /usr/share/nginx/html
COPY nginx.conf /etc/nginx/conf.d/default.conf
EXPOSE 80

docker-compose.yml

version: "3.9"

services:
  backend:
    build:
      context: .
      dockerfile: Dockerfile.backend
    ports:
      - "8000:8000"
    env_file:
      - enterprise_data_agent/.env
    restart: unless-stopped

  frontend:
    build:
      context: .
      dockerfile: Dockerfile.frontend
    ports:
      - "80:80"
    depends_on:
      - backend
    restart: unless-stopped

nginx.conf

server {
    listen 80;
    root /usr/share/nginx/html;
    index index.html;

    location / {
        try_files $uri $uri/ /index.html;
    }

    location /api/ {
        proxy_pass http://backend:8000/;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }
}

Deploy

docker-compose up -d --build

The application will be available at http://localhost (frontend) with API proxied through /api/.

Option 3: Cloud Deployment (AWS/GCP/Azure)

AWS (ECS + Fargate)

Build & push images to Amazon ECR:

aws ecr get-login-password | docker login --username AWS --password-stdin <account>.dkr.ecr.<region>.amazonaws.com
docker build -t enterprise-data-agent-backend -f Dockerfile.backend .
docker tag enterprise-data-agent-backend:latest <account>.dkr.ecr.<region>.amazonaws.com/enterprise-data-agent-backend:latest
docker push <account>.dkr.ecr.<region>.amazonaws.com/enterprise-data-agent-backend:latest

Create ECS Task Definition with the backend and frontend containers.
Set environment variables (GEMINI_API_KEY, DB_*) via AWS Secrets Manager or ECS task environment.
Create an ALB (Application Load Balancer) to route traffic.

GCP (Cloud Run)

# Backend
gcloud run deploy enterprise-data-agent \
  --source . \
  --port 8000 \
  --set-env-vars "GEMINI_API_KEY=..." \
  --allow-unauthenticated

Azure (App Service)

az webapp create --name enterprise-data-agent \
  --resource-group mygroup \
  --plan myplan \
  --runtime "PYTHON:3.12"

az webapp config appsettings set \
  --name enterprise-data-agent \
  --settings GEMINI_API_KEY=...

Production Checklist

Set GEMINI_API_KEY via secrets manager (not plain text .env)
Configure CORS origins in api_server.py for your production domain
Enable HTTPS/TLS termination at the load balancer or reverse proxy
Set LOG_LEVEL=WARNING for production
Use connection pooling for high-traffic scenarios (already configured in connectors)
Set appropriate QUERY_ROW_LIMIT and STATEMENT_TIMEOUT_MS for your workload
Run database connectivity tests before going live
Configure firewall rules to restrict database access to the application server only

Troubleshooting

Problem	Solution
`GEMINI_API_KEY is missing`	Create a `.env` file in `enterprise_data_agent/` with your API key
`ModuleNotFoundError`	Activate the virtual environment: `venv\Scripts\activate` (Windows) or `source venv/bin/activate` (Linux/Mac)
`Connection refused` on DB	Verify `DB_HOST`, `DB_PORT`, and that the database server is running
CORS errors in browser	Ensure the frontend is running on `localhost:5173` (or update `_ALLOWED_ORIGINS` in `api_server.py`)
Oracle `ORA-12541: TNS:no listener`	Verify Oracle listener is running and `ORACLE_SERVICE_NAME` is correct
SQLite `database is locked`	Ensure only one process is accessing the SQLite file at a time
`grpcio` version conflicts	Run `pip install --force-reinstall grpcio==1.78.0 grpcio-status==1.78.0`
Frontend won't build	Run `npm install` in `enterprise_data_agent/frontend/` to install dependencies
Query blocked by SQL Guard	Only `SELECT` and `WITH` queries are allowed. DDL/DML/admin commands are rejected by design.

License

This project was built for HackFest 2026.

Name		Name	Last commit message	Last commit date
Latest commit History 23 Commits
Data		Data
Database		Database
New Refactors		New Refactors
api		api
enterprise_data_agent		enterprise_data_agent
frontend		frontend
logs		logs
node_modules		node_modules
outputs		outputs
.dockerignore		.dockerignore
.gitignore		.gitignore
DO THIS.txt		DO THIS.txt
Dockerfile.backend		Dockerfile.backend
Dockerfile.frontend		Dockerfile.frontend
Hackfest2.txt		Hackfest2.txt
LICENSE		LICENSE
README.md		README.md
docker-compose.yml		docker-compose.yml
nginx.conf		nginx.conf
requirements-full.txt		requirements-full.txt
requirements.txt		requirements.txt

Folders and files

Latest commit

History

Repository files navigation

Enterprise Data Agent

Live link: https://idda-frontend-g4f0vvgsc-harsh3456ds-projects.vercel.app/

Table of Contents

Overview

Architecture

Features

Prerequisites

Project Structure

Setup & Installation

1. Clone the Repository

2. Backend Setup

3. Frontend Setup

4. Environment Configuration

Configuration

Required Settings

Database Settings

Oracle-Specific

Snowflake-Specific

Safety & Limits

Example .env File

Running the Application

Start the Backend

Start the Frontend

Using the Application

Connecting to Databases

MySQL

PostgreSQL

SQL Server

Oracle

SQLite

Snowflake

API Reference

GET /

POST /generate-docs

POST /query

GET /docs/latest

Security

Testing

Run All Tests

Test Suites

Oracle Test Setup

Deployment Guide

Option 1: Local Development

Option 2: Production with Docker

Dockerfile (Backend)

Dockerfile (Frontend)

docker-compose.yml

nginx.conf

Deploy

Option 3: Cloud Deployment (AWS/GCP/Azure)

AWS (ECS + Fargate)

GCP (Cloud Run)

Azure (App Service)

Production Checklist

Troubleshooting

License

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

Example `.env` File

`GET /`

`POST /generate-docs`

`POST /query`

`GET /docs/latest`