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