🧠 Cognitive Database Agent

AI-Powered Conversational Database System with Unforgeable Security

Query, analyze, and manage databases using natural language — without compromising security.

---

🚀 Overview

Cognitive Database Agent is a full-stack AI application that enables users to interact with a PostgreSQL database using plain English, while enforcing enterprise-grade security through Row-Level Security (RLS).

Unlike traditional AI database tools that rely on application-level permission checks, this system enforces access control directly at the database kernel level, making privilege escalation cryptographically and logically impossible.

The result is a secure, autonomous, explainable AI agent capable of querying, modifying, archiving, and visualizing data — all through conversation.

---

✨ Key Features

🗣️ Natural Language Database Interaction

• Ask questions in plain English — no SQL required
• Supports complex, multi-step operations
• Transparent reasoning using the ReAct agent pattern

🔐 Unforgeable Security (PostgreSQL RLS)

• Database-level Row-Level Security (not app-level)
• Prevents privilege escalation by design
• Role-aware execution: Admin, Manager, Viewer
• Zero trust in the AI agent — security is enforced by PostgreSQL itself

📊 Intelligent Data Visualization

• Automatic chart detection using 11 heuristic rules
• Supports Bar, Line, Area, Pie, and Table views
• AI explains why a chart was chosen (with confidence score)
• Manual override available for users

🤖 Autonomous Cognitive Agent

• Built with LangChain + Ollama (qwen2.5:7b)
• Uses 6 custom tools for safe DB interaction
• Multi-step planning: select → insert → delete → aggregate
• Role-aware error handling and messaging

📚 Retrieval-Augmented Generation (RAG)

• Learns database schema dynamically using embeddings
• No hardcoded schema or prompt engineering
• Scales to large and evolving databases

---

🏗️ System Architecture

Frontend (React + Vite)
│
│  Chat UI + Role Selector + Auto Visualizations
│
▼
Backend (FastAPI)
│
│  Cognitive Agent (LangChain ReAct)
│  ├── DB Tools (role-aware)
│  ├── RAG Retriever (pgvector)
│  └── Query Planner
│
▼
PostgreSQL (RLS Enforced)
│
│  sales_data, sales_archive, knowledge_docs
│  Roles: admin | manager | viewer
│  Security: Row-Level Security (Unbypassable)

---

🧩 Tech Stack

Frontend

• React 18 + Vite
• TypeScript
• Recharts
• Axios

Backend

• FastAPI
• LangChain (ReAct Agent)
• Ollama (qwen2.5:7b)
• Pydantic

Database

• PostgreSQL 14+
• Row-Level Security (RLS)
• pgvector

AI / ML

• Sentence Transformers (all-MiniLM-L6-v2)
• Retrieval-Augmented Generation (RAG)

---

🔐 Role-Based Access Control

Role	Permissions
Admin	Full access, all regions, all operations
Manager	Read/write access to own region only
Viewer	Read-only access, no mutations allowed

Security is enforced at the database level — not in the application code.

CREATE POLICY manager_select_own_region
ON sales_data
FOR SELECT
TO db_manager
USING (region = current_setting('app.current_region'));

---

📊 Example Queries

"Show total sales by region"
"Archive all 2021 sales from my region"
"Which quarter had the highest sales?"
"Show quarterly trends for 2023"

The agent:

1. Understands intent
2. Retrieves schema context (RAG)
3. Plans steps (ReAct)
4. Executes queries safely (RLS enforced)
5. Returns results + visualizations + explanation

---

🧪 Tested & Validated

• RLS privilege escalation attempts blocked
• SQL injection prevention
• Multi-step agent reasoning
• Visualization auto-detection
• Concurrent user sessions
• Large dataset handling (table fallback)

---

🚀 Getting Started

Prerequisites

• Node.js 18+
• Python 3.11+
• PostgreSQL 14+
• Ollama

Run Locally

# Backend
cd backend
python -m venv .venv
source .venv/bin/activate  # Windows: .venv\\Scripts\\activate
pip install -r requirements.txt
uvicorn backend.main:app --reload

# Frontend
cd frontend
npm install
npm run dev

• Frontend: http://localhost:5173
• API Docs: http://localhost:8000/docs

---

📁 Project Structure

backend/
 ├── agent/        # Cognitive agent + tools
 ├── api/          # FastAPI routes
 ├── db/           # PostgreSQL connection
 └── core/         # Config

frontend/
 ├── components/   # Visualization + UI
 ├── utils/        # Auto chart logic
 └── api/          # Backend client

database/
 ├── schema.sql    # Tables + RLS policies
 └── seed_data.sql

---

🎯 Why This Project Matters

• Demonstrates real-world AI system design
• Combines LLMs with security-critical databases
• Uses production-grade access control
• Focuses on explainability, safety, and UX
• Directly applicable to enterprise analytics platforms

---

🔮 Future Enhancements

• Scatter plots, heatmaps
• Query caching (Redis)
• Export to CSV / PDF
• Multi-database support
• Predictive analytics
• Voice interface

---

📜 License

MIT License

---

🙌 Author

Developed as a University Full-Stack AI Course Project Focused on AI agents, database security, and system design