API Reference

API Reference 📡

Complete reference for the backend API endpoints.

Base URL

http://localhost:8000

Authentication

Currently no authentication required. See Deployment for adding authentication in production.

---

Endpoints

GET /

Description: Health check and service information

Response:

{
  "status": "running",
  "service": "Company AI Assistant API",
  "database": "localhost",
  "ollama": "http://localhost:11434"
}

Status Codes:

• 200 OK - Service is running

---

GET /health

Description: Check connectivity to database and Ollama

Response:

{
  "database": "connected",
  "ollama": "connected"
}

Possible Values:

• "connected" - Service is accessible
• "disconnected" - Service is not accessible

Status Codes:

• 200 OK - Always returns 200

---

POST /query

Description: Execute a natural language query

Request Body:

{
  "question": "Who are the highest paid employees?"
}

Parameters:

Field	Type	Required	Description
question	string	Yes	Natural language question

Response:

{
  "question": "Who are the highest paid employees?",
  "sql": "SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 5",
  "results": [
    {
      "first_name": "Alice",
      "last_name": "Williams",
      "salary": 110000
    }
  ],
  "answer": "The highest paid employees are Alice Williams ($110,000), Jane Smith ($95,000)...",
  "row_count": 5
}

Response Fields:

Field	Type	Description
question	string	Original question
sql	string	Generated SQL query
results	array	Query results as JSON objects
answer	string	Natural language answer
row_count	integer	Number of rows returned

Status Codes:

• 200 OK - Query executed successfully
• 400 Bad Request - Invalid query or unsafe SQL
• 500 Internal Server Error - Query execution failed

Error Response:

{
  "detail": "Unsafe query: Query contains dangerous keyword: DELETE"
}

---

GET /stats

Description: Get database statistics (table counts)

Response:

{
  "statistics": [
    {
      "table_name": "Employees",
      "count": 22
    },
    {
      "table_name": "Departments",
      "count": 6
    }
  ]
}

Status Codes:

• 200 OK - Statistics retrieved successfully
• 500 Internal Server Error - Database connection failed

---

GET /schema

Description: Get database schema information

Response:

{
  "schema": "DATABASE SCHEMA:\n\nTable: employees\n  - employee_id (integer)\n  - first_name (varchar)\n..."
}

Status Codes:

• 200 OK - Schema retrieved successfully

---

GET /employees

Description: Get all employees with job and department information

Response:

{
  "employees": [
    {
      "employee_id": 1,
      "first_name": "John",
      "last_name": "Doe",
      "email": "john.doe@company.com",
      "salary": 65000,
      "job_title": "IT Programmer",
      "dept_name": "Information Technology"
    }
  ]
}

Status Codes:

• 200 OK - Employees retrieved successfully

---

GET /departments

Description: Get all departments with statistics

Response:

{
  "departments": [
    {
      "dept_id": 1,
      "dept_name": "Information Technology",
      "location": "Edmonton",
      "budget": 150000,
      "employee_count": 3,
      "total_salaries": 253000,
      "avg_salary": 84333.33
    }
  ]
}

Status Codes:

• 200 OK - Departments retrieved successfully

---

Error Handling

All endpoints may return these error codes:

Code	Description
400	Bad Request - Invalid input
500	Internal Server Error - Server or database error

Error Response Format:

{
  "detail": "Error message describing what went wrong"
}

---

Rate Limiting

Currently no rate limiting. For production, consider adding rate limiting:

from slowapi import Limiter

limiter = Limiter(key_func=get_remote_address)
app.state.limiter = limiter

@app.post("/query")
@limiter.limit("10/minute")
async def execute_query():
    # ...

---

CORS

Allowed origins (development):

• http://localhost:3000
• http://localhost:5173

For production, update main.py:

app.add_middleware(
    CORSMiddleware,
    allow_origins=["https://your-frontend-domain.com"],
    # ...
)

---

Examples

Python

import requests

# Execute query
response = requests.post(
    "http://localhost:8000/query",
    json={"question": "Show me all employees in Finance"}
)
data = response.json()
print(data["answer"])

JavaScript

// Execute query
const response = await fetch('http://localhost:8000/query', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ 
    question: 'Show me all employees in Finance' 
  })
});
const data = await response.json();
console.log(data.answer);

cURL

# Execute query
curl -X POST http://localhost:8000/query \
  -H "Content-Type: application/json" \
  -d '{"question": "Show me all employees in Finance"}'

# Get stats
curl http://localhost:8000/stats

# Health check
curl http://localhost:8000/health

---

OpenAPI / Swagger

Interactive API documentation available at:

• http://localhost:8000/docs (Swagger UI)
• http://localhost:8000/redoc (ReDoc)

---

← Back to Home | Architecture →