Fraud Detection SQL

A modern end-to-end AML fraud detection project that converts natural language questions into SQL, executes them against PostgreSQL, and provides a React-based user interface for query history, bookmarks, and analytics.

Overview

This repository contains a fraud detection pipeline built with:

• FastAPI backend for NLP-to-SQL processing and fraud analytics
• React + Vite frontend for user queries, results display, and history management
• PostgreSQL-compatible SQL generation and execution
• Synthetic AML/fraud dataset generation for testing and demonstrations

The solution supports:

• natural language query input
• AI-assisted SQL generation fallback using a local LLaMA model via Ollama
• query history, bookmarking, and rerun support
• SAR generation and fraud pattern detection

Key Features

• Natural language to SQL conversion for AML and fraud queries
• FastAPI REST API with /query, /history, /bookmarks, and bookmark endpoints
• React UI for query input, results, history, and analytics
• Local model integration via Ollama for advanced query understanding
• Synthetic dataset generation scripts in backend/data_generation
• Documentation and UI screenshot in docs

Architecture

• backend/ - main API and processing logic
  • backend/api/ - FastAPI routes
  • backend/core/ - classifier, fraud rules, prompt building, pipeline and model caller
  • backend/services/ - history, query, SAR services
  • backend/database/ - SQL schema, seed data, database helpers
  • backend/data_generation/ - synthetic AML data generation scripts
• frontend/ - React + Vite app
• docs/ - project documentation and UI screenshot
• generated_data/ - generated CSV datasets

Tech Stack

• Python 3.11+ (recommended)
• FastAPI
• PostgreSQL / Psycopg2
• SQLAlchemy
• React 19 + Vite
• Material UI
• Ollama + LLaMA 3.1 8B for local model inference
• Groq API (remote model inference) as an alternative hosted LLM option

Setup

1. Install Python dependencies

From the project root:

python -m venv venv
venv\Scripts\activate
pip install -r requirements.txt

2. Install Node dependencies

cd frontend
npm install
cd ..

3. Prepare the database

The repository includes PostgreSQL schema and seed scripts in backend/database/. Run the SQL scripts in a PostgreSQL database before starting the backend.

Recommended steps (example):

1. Create a PostgreSQL database and user (example):

# create DB and user (run as postgres user)
psql -c "CREATE USER fraud_user WITH PASSWORD 'secure_password';"
psql -c "CREATE DATABASE fraud_db OWNER fraud_user;"

2. Configure environment variables for DB connection. Create a .env file at the project root with:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=fraud_db
DB_USER=fraud_user
DB_PASSWORD=secure_password

The backend reads these variables via backend/database/db_config.py.

3. Apply schema and seed data (two options):

• Using psql to run SQL files:

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f backend/database/schema.sql
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f backend/database/seed_data.sql

• Or use the provided Python loader which imports CSV files from generated_data/:

# activate Python venv first
venv\\Scripts\\activate
python backend/database/load_data.py

4. Verify the database:

psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT count(*) FROM transactions;"

Notes:

• Ensure generated_data/ contains the CSV files required by backend/database/load_data.py.
• If you change table or column names, update backend/database/load_data.py accordingly.

4. Start Ollama and/or configure Groq

The backend supports two options for LLM-based fallbacks:

• Local (recommended for offline use): Ollama with LLaMA 3.1 8B

ollama pull llama3.1:8b
ollama serve

• Remote (hosted): Groq API for managed model inference. To use Groq, set the GROQ_API_KEY environment variable and update backend/core/llm_caller.py to call the Groq endpoint instead of Ollama.

Example environment variable:

set GROQ_API_KEY=your_groq_api_key_here

Keep the Ollama server running when using the local option. If using Groq, ensure your API key is valid and network access is available.

Running the Project

Start the backend API

From the project root:

uvicorn backend.app:app --reload

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

Start the frontend

From the frontend folder:

npm run dev

Open the browser at the address shown by Vite, usually http://localhost:5173.

API Endpoints

• POST /query - execute a natural language AML query
• GET /history - retrieve query history
• GET /bookmarks - retrieve bookmarked queries
• POST /bookmark/{history_id} - bookmark a query
• DELETE /bookmark/{history_id} - remove a bookmark
• POST /history/{history_id}/rerun - rerun a saved query using stored SQL

Documentation

• docs/data_model.md — data model and schema documentation
• docs/images/ui-dashboard.png — application screenshot

Notes

• The UI image is located at docs/images/ui-dashboard.png and is referenced in this README.
• If you need to regenerate data, use the scripts in backend/data_generation/.
• For local ML query fallback, ensure Ollama is running and the LLaMA model is loaded, or configure Groq API by setting GROQ_API_KEY for remote inference.

Contact

For questions or enhancements, review the code in backend/core/ and frontend/src/ and open issues or discussions as needed.