# Project export: Databae. This document was generated by HackStack to give an AI agent context about a hackathon project. Sections are labeled with their provenance; content marked as truncated was cut to keep this document small. ## Project metadata - Hackathon: Cal Hacks 11.0 - Tagline: Simplifying databases for everyone! - Devpost: https://devpost.com/software/databae - GitHub: https://github.com/trungtran1234/databae - Video: https://www.youtube.com/embed/b37oVjFhQ5s?enablejsapi=1&hl=en_US&rel=0&start=&version=3&wmode=transparent - Result: winner (Fetch.ai: Agentic Track Prize) - Team: 4 GitHub contributor(s) — Ryan (10 commits), Phuc Nguyen (9 commits), Trung Tran (7 commits), Davel Radindra (4 commits) ## Devpost submission (written by the team) ### Inspiration As the list of use cases grow for artificial intelligence (AI for short), we thought that it is important to use AI in order to bridge the gap between those who are tech-savvy and those who are non tech-savvy. We were inspired to create this project because we know people that are not knowledgeable in SQL; we know people who are not that knowledgeable in technology that is more on the complicated side. Not everyone is a data scientist. However, a database is an entity that most people often (but do not realize) interact with. In fact, people close to us such as our parents, often are not knowledgeable in SQL. That's where we thought of Databae. Why not just use a language that most people know (in the US) such as English and use that to provide insightful analysis and visualization to the user? ### What it does Let's introduce you to your new friend, Databae. It is your personal AI assistant when it comes to making queries to any database and providing insightful analysis on the dataset. Let's be honest. Not everyone wants to write SQL queries. Not everyone knows SQL. Not everyone knows how to use complicated technologies. That's where Databae comes in. We utilize AI in order to use human language to provide insights on the data in your database. Databae generates a SQL query, double checks the SQL query against the database schema and prompt to make sure that it is a valid query, execute the query, and analyze the result set in order to provide you visualizations (only table form is available for now) about data that exists in your database. We do not just generate SQL queries and execute them. That is boring!!! We provide analysis on your data. Your question does not have to have anything corresponding with an SQL statement. You can perhaps ask, "Which employee in the company is best suited for frontend development?," and Databae will make sure to provide you analysis on that rather than just generating a SQL query and getting the result set. ### How we built it Frontend: Next.js - Basically, the core of our frontend. It displays the front page, connection page, and dashboard. Shadcn/ui - We used certain components from Shadcn/ui such as the buttons. Framer Motion - Do you see the cool animations on our website? Well, Framer Motion allows us to display those cool animations to you. Backend: Python - This is the programming language that powers our backend. FastAPI - FastAPI powers our API endpoints and allows the frontend to request data to display on the frontend. Groq: We utilized Groq's LLaMA models to be able to generate SQL queries, check the SQL queries, and analyze the result set and provide insightful analysis and visualizations. uAgents: We utilized uAgents to create AI agents that are able to communicate with one another. uAgents powers our query generator, query checker, query executor, and query analyzer. AI Agents (part of our backend): Query Generator: The query generator receive the user's request, utilizing the LLaMA model to process the user's prompt, understand and determining if a tool is need or not. If an SQL query and AI tool is needed, it will send the query to the Query Checker. If not, the workflow stops there and it will provide the user with general knowledge related to the database or its schemas. Query Checker: The query checker checks whether the SQL query generated abides by the database schema and user's prompt. If the query checker approves of the SQL query, it will forward the query to the query executor. Otherwise, it will notify the user that the query generated cannot be executed due to the query being too vague or unrelated. Query Executor: The query executor executes the query, gets the dataset from the query, and forwards the dataset to the query analyzer along with the database schema and prompt. Query Analyzer: The query analyzer analyzes the result set, gives insightful results and visualizes the results in a clear and concise manner accordingly to the user's request. ### Challenges we ran into As our frontend has a lot of animations and other cool elements to it, it took a long time to be able to perfect and optimize the appearance of our frontend. Being able to finetune and prompt engineer the LLM models in order to provide the appropriate responses. We had to make lots of adjustments in the prompts in order for it to provide good analysis of the data. We had to figure out how to properly translate the LLM's response into a proper Pandas DataFrame (basically the table). Handling multi-agent communication in uAgents was also a struggle as we had to figure out how to make one agent talk to another that talks to another and vice versa. ### Accomplishments we're proud of We are proud of being able to utilize Fetch.AI's AI agents in order to satisfy our workflow of generating a SQL query => checking the SQL query => executing the SQL query => analyzing the SQL query and providing proper visualizations. We are also proud of being able to use Groq's extremely fast LLMs in order to quickly provide quality but fast analysis of the data in the database. Our frontend was also an important aspect. We used many libraries in order to optimize and "perfect" the appearance of our website. It took us a long time to design the website in the first place. ### What we learned We learned about new amazing frameworks such as Fetch.AI's AI agents. With the assistance of many mentors in CalHacks, we were able to learn a lot about new technologies and techniques We also learned about Groq's LLMs; those were vital in providing those ultra quick responses to the user, and we see many other possible use cases for it. ### What's next More tools: We intend to create more tools for Databae. Our application was designed to have many more tools such as a pie chart generator, prediction models, and other tools. For our use case, we decided to only generate our visualizations in table form, but it is able to scale up in order to be able to display it in many more forms such as pie charts, bar charts, etc. Cross database analysis: What if you want to analyze multiple databases and make correlations between multiple databases? We want to add this functionality. Integration with more databases: We want to be able to have Databae be able to be integrated into more databases such as PostgreSQL, MongoDB, and other DBs. Finetuning the prompts: We want to finetune the prompts in order to provide more insightful responses and visualizations. ## README (from the GitHub repository) # Databae - Cal Hacks 11.0 ## What is Databae? * Have you ever had to create an SQL query by hand? You're probably saying, "Do not make me do that!" Well, Databae comes to the rescue! * Databae is your personal AI assistant when it comes to exploring the complexities of an SQL database. Tell Databae what you want to query your database in plain English, and Databae will automatically query the database and visualize the results for you to understand. ## Diagram ![Databae](https://github.com/user-attachments/assets/cb7e0d4d-fae8-4bea-bcdf-f2a91b8f8bb5) ## Instructions ### Frontend * Install [npm](https://nodejs.org/en) * Change directory into the folder ```frontend``` and run ```npm install``` to install all the dependencies * Run the command ```npm run dev``` ### Backend * Install [Python](https://www.python.org/) (minimum version 3.10) * Install [Poetry & Pipx](https://python-poetry.org/). * Run ```poetry shell``` and ```poetry install``` * Make a .env file consisting to store the Groq API key ```GROQ_API_KEY=GRAB YOUR API KEY FROM GROQ``` * For backend, open up two terminals. Change into the directory folder ```backend``` and run ```uvicorn server:app --reload``` on terminal 1 and run ```python agents.py``` on terminal 2. Depending on your operating system, it could be python or python3. ## Use Cases * Connect to any MySQL database to visualize data * Generate a pie chart based off data in the database * Generate a table based off data in the database * Generate a plaintext response based off data in the database ## Audience Anyone, whether SQL wizards or not, can use it. Type in plain English, and we generate the queries and visualize the data for you. * Are you in the medical industry? Do you want to visualize the amount of usage of medication A within each age group? Tell Databae! * Are you in the education industry? Do you want to know the grades of every student? Tell Databae! * Are you in the music industry? Do you want to make a pie chart that visualizes how many tracks each artist has created? Tell Databae! ## Detected evidence (automated analysis) Indexed codebase: 30 recognized source files, 85 KB. - CSS (language) — detected in the code - FastAPI (technology) — detected in the code - Next.js (technology) — detected in the code - Python (language) — detected in the code - React (technology) — detected in the code - Tailwind CSS (technology) — detected in the code - TypeScript (language) — detected in the code ## Codebase structure (from repository index) ### Files (40 of 40) ``` .gitignore backend/agent_class.py backend/agent_funcs.py backend/agents.py backend/analyzer_tools.py backend/db_tools.py backend/groq_tools.py backend/instructions.py backend/poetry.lock backend/pyproject.toml backend/README.md backend/server.py backend/test.py frontend/.eslintrc.json frontend/.gitignore frontend/components.json frontend/next.config.mjs frontend/package.json frontend/postcss.config.mjs frontend/README.md frontend/src/app/connection/page.tsx frontend/src/app/dashboard/page.tsx frontend/src/app/globals.css frontend/src/app/layout.tsx frontend/src/app/page.tsx frontend/src/Components/AgentStatus.tsx frontend/src/Components/Background.tsx frontend/src/Components/ChatInput.tsx frontend/src/Components/DataConnection/DatabaseConnection.tsx frontend/src/Components/DataConnection/InputField.tsx frontend/src/Components/SideBar.tsx frontend/src/Components/ui/button.tsx frontend/src/Components/ui/card.tsx frontend/src/Components/ui/textarea.tsx frontend/src/lib/utils.ts frontend/tailwind.config.ts frontend/tsconfig.json groq/main.py groq/tools.py README.md ``` ### Dependencies - backend/pyproject.toml: fastapi@^0.115.2, groq@^0.11.0, instructor@^1.6.2, mysql-connector-python@^9.1.0, pandas@^2.2.3, pydantic@2.8.2, python-dotenv@^1.0.1, uagents@^0.16.2, uvicorn@^0.30.1 - frontend/package.json: @radix-ui/react-slot@^1.1.0, @types/dompurify@^3.0.5, @types/node@^20, @types/randomcolor@^0.5.9, @types/react@^18, @types/react-dom@^18, class-variance-authority@^0.7.0, clsx@^2.1.1, eslint@^8, eslint-config-next@14.2.15, framer-motion@^11.11.9, html-react-parser@^5.1.18, ldrs@^1.0.2, lucide-react@^0.453.0, next@14.2.15, postcss@^8, react@^18, react-dom@^18, react-markdown@^9.0.1, tailwind-merge@^2.5.4, tailwindcss@^3.4.1, tailwindcss-animate@^1.0.7, typescript@^5 ### Recent commits (newest first) - Revert "Minor changes to README to mention SingleStore (#26)" (#27) - Minor changes to README to mention SingleStore (#26) - Update README.md - Update README.md - Update README.md - update response.txt flow - Loading status (#24) - ui changes (#23) - Working frontend (#22) - Backend probably is finalized (#21) - fix capitalization error (#19) - working dashboard with text generation (#18) - Database connection (#17) - Add executor functionality (#14) - added database connection to frontend (#15) - Query checker v2 (#13) - Query checker (#12) - Messaging boilerplate (#11) - new readme (#10) - ensure windows compatiblity by using abspath instead of relative path (#8) ## Key source files (fetched from GitHub, selected and truncated for size) ### backend/pyproject.toml ``` [tool.poetry] name = "databae" version = "0.1.0" description = "" authors = ["Your Name "] readme = "README.md" [tool.poetry.dependencies] python = ">=3.10,<3.13" uagents = "^0.16.2" fastapi = "^0.115.2" uvicorn = "^0.30.1" mysql-connector-python = "^9.1.0" groq = "^0.11.0" pydantic = "2.8.2" instructor = "^1.6.2" python-dotenv = "^1.0.1" pandas = "^2.2.3" [build-system] requires = ["poetry-core"] build-backend = "poetry.core.masonry.api" ``` ### frontend/package.json ``` { "name": "frontend1", "version": "0.1.0", "private": true, "scripts": { "dev": "next dev", "build": "next build", "start": "next start", "lint": "next lint" }, "dependencies": { "@radix-ui/react-slot": "^1.1.0", "class-variance-authority": "^0.7.0", "clsx": "^2.1.1", "framer-motion": "^11.11.9", "html-react-parser": "^5.1.18", "ldrs": "^1.0.2", "lucide-react": "^0.453.0", "next": "14.2.15", "react": "^18", "react-dom": "^18", "react-markdown": "^9.0.1", "tailwind-merge": "^2.5.4", "tailwindcss-animate": "^1.0.7" }, "devDependencies": { "@types/dompurify": "^3.0.5", "@types/node": "^20", "@types/randomcolor": "^0.5.9", "@types/react": "^18", "@types/react-dom": "^18", "eslint": "^8", "eslint-config-next": "14.2.15", "postcss": "^8", "tailwindcss": "^3.4.1", "typescript": "^5" } } ``` ### backend/server.py ```python import os from fastapi import FastAPI, HTTPException, Request from pydantic import BaseModel from uagents import Model from uagents.query import query from uagents.envelope import Envelope import json from db_tools import check_and_add_db_credentials import agent_class import asyncio from fastapi.middleware.cors import CORSMiddleware # Agent address AGENT_ADDRESS = "agent1qtafwkkm26h5gdkkz39pd5nnt604q96xh4hperynl085cwzquh0uyunffjz" app = FastAPI() origins = [ "http://localhost:3000", # Your React app's origin ] # Add CORS middleware to the FastAPI app app.add_middleware( CORSMiddleware, allow_origins=origins, allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) class DbBodyModel(BaseModel): host_name: str username: str password: str port: int db_name: str async def agent_query(req): response = await query(destination=AGENT_ADDRESS, message=req, timeout=20) if isinstance(response, Envelope): data = json.loads(response.decode_payload()) return data["text"] return response @app.get("/") def read_root(): return "Hello from the Agent controller" @app.post("/endpoint") async def make_agent_call(req: Request): if os.path.exists("response.txt"): os.remove("response.txt") res = None try: with open(os.path.join("status.txt"), "w") as file: file.write("Start") model = agent_class.Request.parse_obj(await req.json()) res = await agent_query(model) print('what is in here?', res) if not res or res == "success" or res == "returned": print('im right here!') if os.path.exists('response.txt'): with open('response.txt', "r") as file: print('im reading it!!') file_content = file.read().replace("\n", "").replace("text-align: right;", "text-align: center;") return {"status": "successful", "agent_response": file_content} else: return {"status": "successful", "agent_response": res} except Exception as e: if not res or res == "success" or res == "returned": if os.path.exists('response.txt'): print('im inside here!') with open('response.txt', "r") as file: print('im reading it!!') file_content = file.read().replace("\n", "").replace("text-align: right;", "text-align: center;") return {"status": "successful", "agent_response": file_content} else: return {"status": "successful", "agent_response": res} finally: with open(os.path.join("status.txt"), "w") as file: file.write("Done") # endpoint that takes in database connection details, # checks if there is the db connection is valid, # saves it to db_credentials.json # for now, the db has to have ssl off # returns 404 if the database is not able to be connected to @app.post("/input_connection_details") async def input_connection_details(db: DbBodyModel): if check_and_add_db_credentials(db.host_name, db.username, db.password, db.port, db.db_name): return "success" else: raise HTTPException(status_code=404, detail="Could not connect to database") @app.post("/status") async def status(req: Request): if os.path.exists("status.txt"): with open("status.txt", "r") as file: file_content = file.read() return file_content else: return "None" ``` ### groq/main.py ```python import os import importlib import json from groq import Groq # Assuming you're using the Groq client library import tools # Initialize the Groq client with your API key client = Groq(api_key="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") # Define models ROUTING_MODEL = "llama3-70b-8192" TOOL_USE_MODEL = "llama3-groq-70b-8192-tool-use-preview" GENERAL_MODEL = "llama3-70b-8192" #sample data data = [ { "id": 1, "name": "John Doe", "age": 29, "department": "Engineering", "salary": 60000, "years_experience": 5 }, { "id": 2, "name": "Jane Smith", "age": 34, "department": "Marketing", "salary": 70000, "years_experience": 8 }, { "id": 3, "name": "Alice Johnson", "age": 25, "department": "Sales", "salary": 50000, "years_experience": 2 }, { "id": 4, "name": "Robert Brown", "age": 45, "department": "HR", "salary": 80000, "years_experience": 15 }, { "id": 5, "name": "Emily Davis", "age": 30, "department": "Engineering", "salary": 65000, "years_experience": 6 } ] #to def route_query(query): """Routing logic to let LLM decide if tools are needed""" routing_prompt = f""" Given the following user query, determine if any tools are needed to answer it. If a pie chart generation tool is needed, respond with 'TOOL: PIE_CHART'. If a table generation tool is needed, respond with 'TOOL: TABLE_GENERATE'. If a prediction tool is needed, respond with 'TOOL: PREDICT'. If no tools are needed, respond with 'NO TOOL'. User query: {query} Response: """ response = client.chat.completions.create( model=ROUTING_MODEL, messages=[ {"role": "system", "content": "You are a routing assistant. Determine if tools are needed based on the user query."}, {"role": "user", "content": routing_prompt} ], max_tokens=1024 ) routing_decision = response.choices[0].message.content.strip() if "TOOL: PIE_CHART" in routing_decision: return "pie chart tool needed" elif "TOOL: TABLE_GENERATE" in routing_decision: return "table generation tool needed" elif "TOOL: PREDICT" in routing_decision: return "predict tool needed" else: return "no tool needed" def run_with_tool(query): """Use the tool use model to create the response""" data_json = json.dumps(data) messages = [ { "role": "system", "content": ( "You are a data analytics assistant. You can generate charts and tables based on the provided data. " f"The data you can use is as follows:\n{data_json}" ), }, { "role": "user", "content": query, } ] tool_definitions = [ { "type": "function", "function": { "name": "generate_pie_chart", "description": "Generates a pie chart based on provided data", "parameters": { "type": "object", "properties": { "labels": { "type": "array", "items": { "type": "string" }, "description": "Labels for the pie chart" }, "values": { "type": "array", "items": { "type": "number" }, "description": "Values corresponding to the labels for the pie chart" } }, "required": ["labels", "values"] } } }, { "type": "function", "function": { "name": "generate_table", "description": "Generates a table based on provided data", "parameters": { "type": "object", "properties": { "data": { "type": "array", "items": { "type": "array", "items": { "type": "string" } }, "description": "A list of lists where the first list contains the column headers, and subsequent lists contain the rows of the table" } }, "required": ["data"] } } }, { "type": "function", "function": { "name": "predict_data", "description": "Provides a prediction based on input data using a predictive model", "parameters": { "type": "object", "properties": { "data": { "type": "array", "items": { "type": "number" }, "description": "Input data for the prediction model" }, "model": { "type": "string", "description": "The predictive model to use (e.g., linear regression, decision tree)" } }, "required": ["data", "model"] } } } ] response = client.chat.completions.create( model=TOOL_USE_MODEL, messages=messages, tools=tool_definitions, tool_choice="auto", max_tokens=4096 ) response_message = response.choices[0].message tool_calls = response_message.tool_calls if tool_calls: messages.append({ "role": response [truncated — 3193 more characters] ``` ### frontend/src/app/layout.tsx ```typescript import type { Metadata } from "next"; import localFont from "next/font/local"; import "./globals.css"; const geistSans = localFont({ src: "./fonts/GeistVF.woff", variable: "--font-geist-sans", weight: "100 900", }); const geistMono = localFont({ src: "./fonts/GeistMonoVF.woff", variable: "--font-geist-mono", weight: "100 900", }); export const metadata: Metadata = { title: "Create Next App", description: "Generated by create next app", }; export default function RootLayout({ children, }: Readonly<{ children: React.ReactNode; }>) { return ( {children} ); } ``` ### frontend/src/app/page.tsx ```typescript 'use client' import { useState, useEffect } from 'react' import { motion } from 'framer-motion' import { useRouter } from 'next/navigation' import Image from 'next/image' export default function Component() { const [mousePosition, setMousePosition] = useState({ x: 0, y: 0 }) const router = useRouter() useEffect(() => { const handleMouseMove = (e: MouseEvent) => { setMousePosition({ x: e.clientX, y: e.clientY }) } window.addEventListener('mousemove', handleMouseMove) return () => { window.removeEventListener('mousemove', handleMouseMove) } }, []) return (
LOGO databae. Simplify Databases. router.push('/connection')} > Get started
) } ``` ### frontend/src/app/connection/page.tsx ```typescript import React from 'react'; import Background from '../../Components/Background'; // Import the component import DatabaseConnection from "../../Components/DataConnection/DatabaseConnection"; const Page = () => { return (
); }; export default Page; ``` ### frontend/src/app/dashboard/page.tsx ```typescript 'use client' import React, { useState, memo, useLayoutEffect, useRef } from 'react' import { motion } from 'framer-motion' import { Button } from "@/Components/ui/button" import { Textarea } from "@/Components/ui/textarea" import { Card, CardContent, CardHeader, CardTitle } from "@/Components/ui/card" import { Send } from 'lucide-react' import ReactMarkdown from 'react-markdown'; import { } from 'ldrs'; import parse from 'html-react-parser'; import AgentStatus from '@/Components/AgentStatus' interface FloatingCircleProps { size: string; initialPosition: { top?: string; left?: string; right?: string; bottom?: string }; duration: number; } const FloatingCircle: React.FC = ({ size, initialPosition, duration }) => ( ) interface MyComponentProps { response: string; // Assuming response is a string containing HTML } const FormTable: React.FC = ({ response }) => { const elRef = useRef(null); useLayoutEffect(() => { if (elRef.current) { // Access the table after it has been rendered const table = elRef.current.querySelector('table'); if (table) { // Apply dynamic styles table.style.width = '100%'; // Make the table take full width table.style.borderCollapse = 'collapse'; // Collapse borders // Style table headers const headers = table.querySelectorAll('th'); headers.forEach((header) => { header.style.backgroundColor = '#f3f4f6'; // Light gray background header.style.border = '2px solid #d1d5db'; // Gray border header.style.padding = '8px'; // Padding for headers }); // Style table cells const cells = table.querySelectorAll('td'); cells.forEach((cell) => { cell.style.border = '1px solid #d1d5db'; // Gray border cell.style.padding = '8px'; // Padding for cells }); // Optional: Style the table rows const rows = table.querySelectorAll('tr'); rows.forEach((row) => { row.style.transition = 'background-color 0.2s'; row.addEventListener('mouseenter', () => { row.style.backgroundColor = '#f9fafb'; // Light hover effect }); row.addEventListener('mouseleave', () => { row.style.backgroundColor = ''; // Reset background }); }); } } }, [response]); // Run effect when response changes return (
{parse(response)}
); }; export default function DatabaseVisualizer() { const [query, setQuery] = useState('') const [response, setResponse] = useState('') const [isLoading, setLoading] = useState(false) const handleRunQuery = async () => { setLoading(true); try { const res = await fetch('http://localhost:8000/endpoint', { method: 'POST', headers: { 'Content-Type': 'application/json', }, body: JSON.stringify({ query }), }) const data = await res.json() console.log(data.status) if (data.status == 'successful') { console.log('IT IS SUCCESSFUL'); setResponse(data["agent_response"]); } else { console.log('NAHHH'); setResponse(`Error: ${data.error}`); } } catch (error) { console.error('Error running query:', error) setResponse('Error: Something went wrong') } finally { setLoading(false) } } return (
{/* Floating circles */} Databae. {isLoading ? (
[truncated — 2602 more characters] ``` ### backend/test.py ```python from db_tools import create_connection, get_all_schemas print("=======") print("testing out create connection") connection = create_connection() print(connection) print("=======") print("testing out get_all_schemas") schema = get_all_schemas() print(schema) ``` ### backend/agent_class.py ```python from uagents import Model from typing import Any, Optional class Request(Model): query: str user: str = None class Response(Model): text: str query: Optional[str] = None sqlschema: Optional[dict] = None user: Optional[str] = None table: Optional[Any] = None class Config: # Include fields with None values during serialization exclude_none = False ``` [18 more indexed source files omitted to keep this export small. The full file list is in the Codebase structure section above.]