DB Analytics + Query Platform (FastAPI)

Project Structure

• app/main.py: FastAPI app initialization and router registration
• app/core: config and security helpers
• app/db: DB engine/session and init
• app/models: SQLAlchemy ORM models
• app/schemas: Pydantic request/response schemas
• app/api/routes: endpoint routes
• app/services: business logic services
• app/mcp: MCP integration stubs
• alembic: migration configuration

Requirements

• Python 3.10+
• SQLite

Install dependencies:

pip install -r requirements.txt

Local Run

uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

Open docs at http://localhost:8000/docs.

Endpoints

• POST /api/auth/register: create user
• POST /api/auth/token: login and retrieve JWT
• GET /api/auth/me: get current user
• POST /api/connections/test: validate DB connection
• POST /api/connections: save connection (admin only)
• POST /api/queries/execute: execute SQL query
• GET /api/reports/users/total: return user total
• GET /api/reports/access-logs
• GET /api/reports/objects/summary
• GET /api/metrics/performance
• GET /api/metrics/top-objects
• GET /api/metrics/frequency
• POST /api/mcp/run-query
• GET /api/mcp/schema

Sample Flow

1. Register admin

curl -X POST "http://localhost:8000/api/auth/register" -H "Content-Type: application/json" -d '{"username":"admin","email":"admin@example.com","password":"password","role":"admin"}'

2. Get token

curl -X POST "http://localhost:8000/api/auth/token" -H "Content-Type: application/x-www-form-urlencoded" -d "username=admin&password=password"

3. Use token for protected endpoints

curl -H "Authorization: Bearer <TOKEN>" "http://localhost:8000/api/reports/users/total"

Expected Register Response

Successful registration returns:

HTTP/1.1 200 OK
{
  "id": 1,
  "username": "admin",
  "email": "admin@example.com",
  "role": "admin",
  "is_active": true,
  "created_at": "2026-03-25T12:34:56.789000"
}

If the user exists:

HTTP/1.1 400 Bad Request
{
  "detail": "User already exists"
}

Alembic migrations

alembic revision --autogenerate -m "init"
alembic upgrade head