AI-Powered Database Interaction Layer

This repository details a serverless backend built upon FastAPI. Its primary function is to translate user queries into executable SQL against a designated Postgres database using large language models from OpenAI. The output is meticulously structured JSON, optimized for seamless integration into interactive chatbot UIs. The infrastructure supports flexible deployment pathways, namely AWS Lambda (managed via AWS SAM) or conventional local/Docker setups.

Core Capabilities

A performant FastAPI RESTful service exposing a singular /ask resource.
Employing OpenAI's GPT series for the dual tasks of SQL generation and result summarization.
Secure connection tooling targeting a managed Postgres system (e.g., Supabase).
Outputting strictly defined JSON schemas suitable for immediate frontend visualization.
Cross-Origin Resource Sharing (CORS) pre-configured for front-end consumption.
Infrastructure-as-Code readiness for AWS Lambda provisioning via SAM templates.
Detailed logging mechanisms designed for CloudWatch observability during Lambda execution.

Directory Layout

├── main.py # Core FastAPI application logic and Lambda entry point ├── requirements.txt # Python package dependency manifest ├── template.yaml # AWS Serverless Application Model specification ├── samconfig.toml # Configuration file for SAM tooling ├── Dockerfile # Instructions for container image construction ├── .gitignore # Version control exclusion list └── .env # Sensitive configuration parameters (excluded from VCS)

Initialization Procedure

Phase 1: Repository Checkout

sh git clone cd mcp-chat-3

Phase 2: Virtual Environment and Dependency Installation

sh python -m venv .venv source .venv/bin/activate # Linux/macOS | .venv\Scripts\activate for Windows pip install -r requirements.txt

Phase 3: Setting Environment Secrets

Establish a configuration file named .env (ensure it is listed in .gitignore):

OPENAI_API_KEY=your-openai-key SUPABASE_DB_NAME=your-db SUPABASE_DB_USER=your-user SUPABASE_DB_PASSWORD=your-password SUPABASE_DB_HOST=your-host SUPABASE_DB_PORT=your-port

Local Operation Modes

Via Uvicorn Web Server

sh uvicorn main:app --reload --port 8080

Within a Docker Container

sh docker build -t mcp-chat-backend . docker run -p 8080:8080 --env-file .env mcp-chat-backend

Serverless Deployment to AWS (SAM)

Prerequisite: Install the AWS SAM CLI.
Execution sequence: sh sam build sam deploy --guided
Environment variables must be provisioned either within template.yaml or configured post-deployment via the AWS Management Console.
The accessible API endpoint will be reported upon successful deployment (e.g., https://xxxxxx.execute-api.region.amazonaws.com/Prod/ask).

Interacting with the API

Endpoint: POST /ask

Payload Format: { "question": "Submit your natural language inquiry here" }
Expected Return: A structured JSON object optimized for display in a conversational interface, for example:

{ "messages": [ { "type": "text", "content": "Result for Sample 588 indicates a resistance value of 1.2 ohms.", "entity": { "entity_type": "sample", "id": "588" } }, { "type": "list", "items": ["Observation A", "Observation B"] } ] }

Detailed schema specification is available within the source code of main.py.

Runtime Environment Variables

OPENAI_API_KEY: Authentication token for the OpenAI service.
SUPABASE_DB_NAME, SUPABASE_DB_USER, SUPABASE_DB_PASSWORD, SUPABASE_DB_HOST, SUPABASE_DB_PORT: Credentials necessary for securing database access.

Operational Context

All operational output is directed to standard output (which translates to CloudWatch logs when running on Lambda).
CORS policy permits access from any origin by default for ease of development.
The system relies on the consuming client application to correctly interpret and render the complex structured data format returned.