Data Pipeline — Technical Overview

A lightweight, three-layer pipeline: ingestion scripts pull from public APIs, a relational store normalizes the data, and a thin API layer feeds the frontend.

Four distinct sources, each with a different update cadence and data type.

Idempotent upserts with composite unique keys — re-running the script never creates duplicates.

import httpx, pandas as pd
from datetime import date

def fetch_openrouter_pricing():
    """Pull current prices from OpenRouter's public API."""
    resp = httpx.get("https://openrouter.ai/api/v1/models", timeout=30)
    resp.raise_for_status()
    models = resp.json()["data"]
    rows = []
    for m in models:
        pricing = m.get("pricing", {})
        rows.append({
            "model_name": m["id"],
            "provider": m.get("author", "unknown"),
            "observation_date": date.today(),
            "input_price_per_m_tokens":  float(pricing.get("prompt", 0)) * 1e6,
            "output_price_per_m_tokens": float(pricing.get("completion", 0)) * 1e6,
            "source": "openrouter",
        })
    return pd.DataFrame(rows)

def upsert_pricing(df: pd.DataFrame, conn):
    """Idempotent upsert — UNIQUE(model_name, observation_date, source)."""
    df.to_sql("llm_pricing_history", conn, if_exists="append", index=False,
              method="multi", chunksize=50)
    # Conflict resolution: ON CONFLICT DO UPDATE handled at DB level

Five PostgreSQL tables, normalized for time-series queries and fast filtering by model/tier/date.

Thin FastAPI endpoints — each is a single SQL query wrapped in JSON. No business logic here; all computation lives in the frontend or in dedicated Python modules.

@app.get("/api/free/llm/models")
def llm_models():
    """Return all model specs, ordered by tier then name."""
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        SELECT model_name, tier, provider,
               input_price_per_m_tokens, output_price_per_m_tokens,
               context_window, max_output_tokens,
               supports_reasoning, reasoning_token_multiplier,
               is_open_weights, hw_requirement
        FROM llm_model_specs
        ORDER BY tier, model_name
    """)
    cols = ["model_name", "tier", "provider", "input_price", "output_price",
            "context_window", "max_output_tokens", "supports_reasoning",
            "reasoning_token_multiplier", "is_open_weights", "hw_requirement"]
    rows = [dict(zip(cols, r)) for r in cur.fetchall()]
    conn.close()
    return {"ok": True, "models": rows}

Vanilla JavaScript with Chart.js 4.x. No build step, no framework — each section is a standalone module that fetches from the API and renders into its container.

// llm-core.js — translation engine + model registry
var llmLang = localStorage.getItem("seb-lang") || "en";
function llmT(key) {
    return llmTranslations[llmLang][key] || key;
}

// llm-render.js — chart rendering (example: pricing history)
function renderPricingChart(history) {
    const ctx = document.getElementById("pricing-chart").getContext("2d");
    new Chart(ctx, {
        type: "line",
        data: {
            labels: history.map(r => r.date),
            datasets: history.map(r => ({
                label: r.model_name,
                data: [r.input_price, r.output_price],
                tension: 0
            }))
        },
        options: { scales: { x: { type: "time" } } }
    });
}

What happens between the raw source and the warehouse.