Skip to content

System Architecture

Jump to bottom
Kshitij Joshi edited this page May 26, 2026 · 1 revision

System Architecture 🏗️

Deep dive into the architecture and design decisions.

High-Level Overview

┌─────────────────────────────────────────────────────────┐
│                     User's Browser                       │
│                   (React Frontend)                       │
│                  http://localhost:3000                   │
└──────────────────────┬──────────────────────────────────┘
                       │ HTTP/REST
                       │
┌──────────────────────▼──────────────────────────────────┐
│                   FastAPI Backend                        │
│                  http://localhost:8000                   │
│  ┌────────────┐  ┌────────────┐  ┌──────────────┐      │
│  │  Query     │  │  Ollama    │  │  Database    │      │
│  │  Handler   │──│  Client    │  │  Client      │      │
│  └────────────┘  └────────────┘  └──────────────┘      │
└────────┬─────────────┬──────────────────┬───────────────┘
         │             │                  │
         │             │                  │
┌────────▼─────┐ ┌────▼──────┐  ┌────────▼────────┐
│   Ollama     │ │  Ollama   │  │   PostgreSQL    │
│   Service    │ │  Models   │  │   + pgvector    │
│ (LLM Runtime)│ │  (Local)  │  │                 │
└──────────────┘ └───────────┘  └─────────────────┘

Component Details

Frontend (React + Vite)

Technology Stack:

  • React 18
  • Vite (build tool)
  • Modern CSS (no frameworks)
  • Fetch API for HTTP requests

Key Files:

  • App.jsx - Main component
  • App.css - Styling
  • main.jsx - Entry point

Responsibilities:

  1. User interface rendering
  2. User input handling
  3. API communication
  4. Result visualization
  5. Error handling

Design Patterns:

  • Functional components with hooks
  • State management with useState
  • Side effects with useEffect
  • Component composition

Backend (FastAPI + Python)

Technology Stack:

  • FastAPI (web framework)
  • Uvicorn (ASGI server)
  • psycopg3 (PostgreSQL driver)
  • Requests (HTTP client for Ollama)

Key Components:

1. API Layer (main.py)

  • FastAPI App: HTTP endpoint definitions
  • CORS Middleware: Cross-origin request handling
  • Pydantic Models: Request/response validation

2. Query Processing

User Question 
    ↓
Schema Context Retrieval
    ↓
LLM Prompt Construction
    ↓
Ollama API Call
    ↓
SQL Generation
    ↓
Safety Validation
    ↓
Query Execution
    ↓
Result Formatting
    ↓
Natural Language Answer

3. Safety Layer

  • Keyword blocking (DROP, DELETE, etc.)
  • Query type validation (SELECT only)
  • SQL injection prevention
  • Input sanitization

Database Layer (PostgreSQL + pgvector)

Schema:

employees ─┬─ departments
           ├─ jobs
           └─ managers (self-reference)

projects ─── departments

performance_reviews ─── employees

salary_history ─── employees

ai_query_history (logs)

employee_embeddings ─── employees (optional)
department_embeddings ─── departments (optional)

Indexes:

  • Primary keys (automatic)
  • Foreign keys (automatic)
  • Custom indexes on frequently queried columns
  • HNSW indexes for vector search (optional)

Connection Management:

  • Connection per request (simple)
  • Future: Connection pooling for production

AI Layer (Ollama)

Architecture:

┌──────────────────┐
│  Ollama Service  │
│  (HTTP Server)   │
└────────┬─────────┘
         │
    ┌────▼────┐
    │  Model  │
    │ Runtime │
    └────┬────┘
         │
    ┌────▼────┐
    │  Models │
    │ (Local) │
    └─────────┘

Models:

  • Llama 3.2 (2GB) - Fast, efficient
  • Mistral (4GB) - Good accuracy
  • Gemma4 (9.6GB) - High quality
  • Qwen3.6 (23GB) - Very capable

API Endpoints Used:

  • POST /api/generate - Text generation
  • GET /api/tags - List models
  • POST /api/embeddings - Generate embeddings (optional)

Data Flow

Query Execution Flow

1. User enters question in frontend
   ↓
2. Frontend sends POST /query request
   ↓
3. Backend receives request
   ↓
4. Get database schema
   ↓
5. Construct prompt with schema + question
   ↓
6. Send to Ollama for SQL generation
   ↓
7. Receive generated SQL
   ↓
8. Validate SQL (safety checks)
   ↓
9. Execute SQL on PostgreSQL
   ↓
10. Receive query results
    ↓
11. Format results with Ollama
    ↓
12. Return response to frontend
    ↓
13. Frontend displays results

Typical Response Time Breakdown

  • Frontend → Backend: ~10ms
  • Schema retrieval: ~50ms
  • Ollama SQL generation: 1-5s
  • Query execution: ~100ms
  • Ollama result formatting: 1-3s
  • Backend → Frontend: ~10ms

Total: 2-8 seconds (varies by model and query complexity)

Security Architecture

Defense in Depth

Layer 1: Frontend

  • Input sanitization
  • HTTPS (production)
  • CORS restrictions

Layer 2: Backend

  • Query validation
  • Keyword blocking
  • Rate limiting (production)
  • Authentication (production)

Layer 3: Database

  • Read-only user (recommended)
  • Connection encryption
  • Network isolation

Layer 4: Infrastructure

  • Firewall rules
  • SSH tunnels
  • VPN (production)

Scalability Considerations

Current Architecture (Single Server)

Limitations:

  • Single point of failure
  • Limited concurrent users (~10-20)
  • No load balancing

Suitable For:

  • Development
  • Small teams
  • Personal use

Scaling Options

Horizontal Scaling:

           ┌─── Backend 1 ───┐
           │                 │
Load ──────┼─── Backend 2 ───┼──── Database
Balancer   │                 │
           └─── Backend N ───┘

Vertical Scaling:

  • More RAM for PostgreSQL
  • Faster CPU for Ollama
  • SSD for better I/O

Caching:

  • Redis for query results
  • Schema caching
  • Ollama response caching

Performance Optimizations

Database

  1. Indexes

    CREATE INDEX idx_employees_dept ON employees(dept_id);
CREATE INDEX idx_employees_salary ON employees(salary DESC);

  2. Connection Pooling

    from psycopg_pool import ConnectionPool

pool = ConnectionPool(conninfo=DB_CONFIG)

  3. Query Caching

    from functools import lru_cache

@lru_cache(maxsize=100)
def get_schema_context():
    # ...

Backend

  1. Async Operations

    async def execute_query():
    # Non-blocking I/O

  2. Response Compression

    from fastapi.middleware.gzip import GZipMiddleware
app.add_middleware(GZipMiddleware)

Frontend

  1. Code Splitting
  2. Lazy Loading
  3. Asset Optimization

Monitoring & Observability

Logging

import logging

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)

Metrics

  • Query count
  • Response times
  • Error rates
  • Database connections
  • Ollama performance

Health Checks

@app.get("/health")
async def health():
    return {
        "database": check_db(),
        "ollama": check_ollama()
    }

Deployment Architectures

Development

Laptop
├── Frontend (npm run dev)
├── Backend (python main.py)
├── Ollama (local)
└── PostgreSQL (local or SSH tunnel)

Production (Cloud)

                    ┌─── Nginx ───┐
Internet ────────── │  (SSL/TLS)  │
                    └─────┬───────┘
                          │
                    ┌─────▼───────┐
                    │ Load Balance│
                    └─────┬───────┘
                          │
         ┌────────────────┼────────────────┐
         │                │                │
    ┌────▼────┐     ┌────▼────┐     ┌────▼────┐
    │Backend 1│     │Backend 2│     │Backend 3│
    └────┬────┘     └────┬────┘     └────┬────┘
         │                │                │
         └────────────────┼────────────────┘
                          │
                    ┌─────▼───────┐
                    │  PostgreSQL │
                    │  (Managed)  │
                    └─────────────┘

Technology Choices

Why FastAPI?

✅ Modern, fast, async support
✅ Automatic API documentation
✅ Type safety with Pydantic
✅ Easy to learn and use

Why React?

✅ Component-based architecture
✅ Large ecosystem
✅ Fast rendering
✅ Easy state management

Why Ollama?

✅ 100% local (privacy)
✅ No API costs
✅ Easy to use
✅ Multiple model support

Why PostgreSQL?

✅ Robust and reliable
✅ pgvector for embeddings
✅ Advanced query features
✅ Open source

Future Architecture Improvements

  • WebSocket support for streaming
  • GraphQL API option
  • Microservices architecture
  • Event-driven design
  • Kubernetes deployment
  • Multi-database support

← Back to Home | API Reference →

Clone this wiki locally