How the School AI Analytics
Dashboard Works

1 What This Project Is

The School AI Analytics Dashboard is a self-contained web application that lets school administrators ask plain-English questions about their students, marks, fees, hostel and staff data and get instant, formatted answers. There is no SQL to write and no IT ticket to file — a principal simply types "How many students are yet to pay hostel fees?" and the system routes the request, reads the relevant database tables, asks a Large Language Model (Mistral) to analyse them, and returns conversational Markdown with charts and rankings.

It is built from three layers that all live in one folder:

2 The Files in the Repository

3 The Database

setup_db.py wipes any old school.db and rebuilds a fully relational schema, then seeds it with deterministic synthetic data so every install looks identical. The 13 core tables are:

The vector index (ChromaDB)

index.py reads db_schema_reference.txt, splits it on the word Table: into per-table chunks, and stores them in a persistent ChromaDB collection called school_context under vector_db/. This gives the system a semantic memory of the schema's structure that can scale into true retrieval-augmented generation later.

4 The AI Pipeline — What Happens When You Ask a Question

When you type a question in the AI Assistant tab, the front-end POSTs it to /api/chat. The backend then runs an agentic pipeline that mimics a human analyst:

The rate-managed Mistral client

Every call goes through ask_general_mistral(), which is deliberately defensive about the free-tier API limits:

• A mandatory 500 ms pre-pause before each request to avoid instant concurrent hits.
• On an HTTP 429 (rate limited), it waits and retries up to 4 times with exponential back-off (2.5s → 5s → 10s …).
• On any hard failure it returns an ERROR_SIGNAL sentinel, which the pipeline converts into the friendly "system is pacing requests, please retry" message instead of a crash.

5 File Upload & Indexing

The dashboard lets you upload a file and ask the AI about it. Uploads are not just attached to a single message — they are indexed into the same school.db so they become part of the analytics workspace. The handling depends on the file type:

A registry table, UploadedDocuments, tracks every upload (filename, type, linked table, extracted text, timestamp). On each chat request, gather_uploaded_context() pulls the most recent uploads (within a character budget) into the model's context. Deleting an upload also drops its generated data table.

6 The Front-End

index.html is a single self-contained page — no framework, no bundler. On load it fires four fetches (loadStats, loadTables, loadQuickQuestions, loadUploads) and renders three switchable panels:

• Overview — stat cards, a student leaderboard, and per-subject performance bars built from /api/stats.
• AI Assistant — the chat interface, quick-question chips, and the file-upload bar. Answers are rendered from Markdown via marked.js.
• Data Tables — a tabbed raw view of every table returned by /api/tables.
• How It Works — the link that opened this report.

7 API Reference

All routes are served under the base path /ai-azure-table-query.

8 How It Is Deployed

This instance runs as a managed background service behind nginx with SSL, rather than via the repo's start.py (which is geared toward local foreground development and binds port 8000):

Managing the service

# status / logs / restart
sudo systemctl status ai-school
sudo journalctl -u ai-school -f
sudo systemctl restart ai-school

Running it locally (development)

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt
echo "MISTRAL_API_KEY=your_key_here" > api.txt
python3 start.py   # builds DB + index, launches uvicorn

9 Using Claude (Anthropic) Instead of Mistral

The app currently calls Mistral, but because the pipeline is model-agnostic you can point it at Claude instead. First, an important billing distinction that trips a lot of people up:

Step 1 — Get an API key (separate from your subscription)

1. Go to console.claude.com → Billing and add a small amount of prepaid credit (a few dollars is plenty for this app).
2. Open API Keys → Create Key. It looks like sk-ant-api03-….
3. Treat it like a password — never commit it to a public repo.

Step 2 — Models & pricing

For a lightweight analytics Q&A like this, the token volume per question is small, so real-world cost is a few cents regardless of model.

Step 3 — Wire it into the app

Install the official SDK and store the key alongside the existing one:

# add to requirements.txt and install
pip install anthropic

# api.txt — add this line next to MISTRAL_API_KEY
ANTHROPIC_API_KEY=sk-ant-api03-...

Then add a Claude version of the LLM call in server.py and use it wherever ask_general_mistral() is called today:

import anthropic

client = anthropic.Anthropic(api_key=get_anthropic_key())

def ask_general_claude(messages_payload):
    # split the system prompt out of the message list
    system = ""
    msgs = []
    for m in messages_payload:
        if m["role"] == "system":
            system = m["content"]
        else:
            msgs.append(m)

    resp = client.messages.create(
        model="claude-opus-4-8",   # or claude-sonnet-4-6 / claude-haiku-4-5
        max_tokens=4096,
        system=system,
        messages=msgs,
    )
    return "".join(b.text for b in resp.content if b.type == "text")

Images: the upload feature's vision step (currently Pixtral) can also move to Claude — it reads images natively. Send the picture as an image content block:

messages=[{"role": "user", "content": [
    {"type": "image", "source": {
        "type": "base64", "media_type": "image/png", "data": image_b64}},
    {"type": "text", "text": "Extract all text and data from this image."}
]}]

10 Free AI API Options

If you'd rather not pay at all, many providers offer a genuinely free tier you can use in place of Mistral or Claude. Because this app already calls an OpenAI-style /chat/completions endpoint, switching to most of them means changing only three things: the URL, the model name, and the API key. The expanded list below is drawn from the community awesome-free-llm-apis directory.

Drop-in example — switching to Groq

Groq is OpenAI-compatible, so the change mirrors the existing ask_general_mistral() almost exactly — a new URL, model, and key:

# api.txt
GROQ_API_KEY=gsk_...

# server.py — same shape as ask_general_mistral(), three values changed
def ask_general_groq(messages_payload):
    api_key = get_key("GROQ_API_KEY")
    payload = {
        "model": "llama-3.3-70b-versatile",
        "messages": messages_payload,
        "temperature": 0.1,
    }
    headers = {"Authorization": f"Bearer {api_key}", "Content-Type": "application/json"}
    r = requests.post("https://api.groq.com/openai/v1/chat/completions",
                      headers=headers, json=payload, timeout=60)
    return r.json()["choices"][0]["message"]["content"]

The same function works for OpenRouter (https://openrouter.ai/api/v1/chat/completions), Cerebras (https://api.cerebras.ai/v1/chat/completions), and Gemini's OpenAI-compatible endpoint (https://generativelanguage.googleapis.com/v1beta/openai/chat/completions) — just swap the URL, model name, and key.

11 Media Generation in the Chat (Pollinations)

The AI Assistant doesn't just answer questions — it can also generate images, audio and video right inside the chat, powered by Pollinations AI. Just describe what you want in plain English:

• "generate an image of a happy school classroom" → an image appears in the reply
• "create audio that says: Welcome to our dashboard" → a playable audio clip (with transcript)
• "make a video of a cat walking in a garden" → an embedded video

How it routes

Every chat message first passes through detect_generation(), which scans for media keywords (image / picture / photo / logo / draw → image; audio / speech / voice / say / tts / song → audio; video / animation / clip / gif → video). If a media request is detected the message is sent to Pollinations instead of the analytics pipeline; otherwise it flows to the normal data-analysis path. Words like chart and graph are deliberately not triggers — those stay as text bar-charts in the analytics engine.

The backend (generate_media()) calls Pollinations with the POLLINATIONS_API_KEY from api.txt, decodes the result, writes it to the generated/ folder, and returns a URL the browser renders as an <img>, <audio> or <video> element inside the AI's reply.