llm_tracker

Automatically log OpenAI API usage (tokens, cost, latency) to MySQL. Drop-in wrapper that requires only a 1-line import change.

What It Does

Every call to client.chat.completions.create() logs:

• Tokens (prompt, completion, total)
• Cost (calculated from pricing table)
• Latency (milliseconds)
• Metadata (service name, endpoint, environment, user ID, request ID)

All logged to MySQL table ai_llm_usage_logs for cost analysis dashboards.

---

Quick Start (5 minutes)

1. Install

pip install llm-tracker

2. Configure

Copy .env.example to .env and fill in:

# Who you are
LLM_TRACKER_API_NAME=snapshot          # Your service name
LLM_TRACKER_USER_ID=your-user-id       # Your personal/team ID

# MySQL connection
LLM_TRACKER_DB_HOST=mysql.example.com
LLM_TRACKER_DB_PORT=3306
LLM_TRACKER_DB_USER=mysqladmin
LLM_TRACKER_DB_PASSWORD=password
LLM_TRACKER_DB_NAME=dev_db

# Optional
LLM_TRACKER_USE_SSL=1                  # SSL enabled (default: 1)
LLM_TRACKER_DEFAULT_ENV=beta           # test/beta/prod (default: test)

See .env.example for all variables with explanations.

3. Initialize Database

First time only:

python -c "from llm_tracker.db import init_db; init_db()"

This creates the ai_llm_usage_logs table.

4. Use Tracked Client

In your code, change only the import:

# Before
from openai import OpenAI
client = OpenAI(api_key="...", base_url="...")

# After
from llm_tracker import TrackedOpenAI
client = TrackedOpenAI(api_key="...", base_url="...")

# Everything else stays the same
response = client.chat.completions.create(
    model="gpt-4o",
    messages=[{"role": "user", "content": "hello"}]
)

5. (FastAPI only) Add Middleware

In your FastAPI app startup:

from fastapi import FastAPI
from llm_tracker.middleware import LLMContextMiddleware

app = FastAPI()
app.add_middleware(LLMContextMiddleware)

This automatically populates:

• api_name — your service name (from env)
• endpoint — the route path (e.g., /jobs/medium-brain)
• user_id — your ID (from env)
• request_id — unique per request (auto-generated)
• environment — from env var or X-Env header

---

Environment Variables

Required

Variable	Example	Description
LLM_TRACKER_API_NAME	snapshot	Your service/repo name
LLM_TRACKER_USER_ID	abc123def	Your personal/team ID (cost attribution)
LLM_TRACKER_DB_HOST	mysql.example.com	MySQL hostname
LLM_TRACKER_DB_USER	mysqladmin	MySQL username
LLM_TRACKER_DB_PASSWORD	password123	MySQL password
LLM_TRACKER_DB_NAME	dev_db	MySQL database name

Optional

Variable	Default	Description
LLM_TRACKER_DB_PORT	3306	MySQL port
LLM_TRACKER_USE_SSL	1	Enable SSL (0=off, 1=on)
LLM_TRACKER_SSL_CA	(system)	Path to CA certificate
LLM_TRACKER_DEFAULT_ENV	test	Environment label: test, beta, or prod
LLM_TRACKER_PRICING_JSON	(built-in)	Override pricing table as JSON

Special: Per-Request Environment

Send X-Env header to override environment for a single request:

curl -H "X-Env: test" http://localhost:8088/jobs/medium-brain

---

What Gets Logged

Table: ai_llm_usage_logs

Column	Example	Notes
id	a1b2c3d4-...	UUID (auto-generated)
created_at	2026-07-07 12:30:45	IST timestamp (auto)
api_name	snapshot	From LLM_TRACKER_API_NAME
endpoint	/jobs/medium-brain	HTTP route (FastAPI only)
deployment	gpt-4o	Model name
environment	beta	From LLM_TRACKER_DEFAULT_ENV
user_id	abc123def	From LLM_TRACKER_USER_ID
request_id	xyz789abc	Per-request UUID (FastAPI)
prompt_tokens	150	Input tokens
completion_tokens	50	Output tokens
total_tokens	200	Sum
cost_usd	0.0045	Calculated cost
latency_ms	1234	Round-trip time

Query Example

-- Total cost by endpoint (last 7 days)
SELECT endpoint, deployment, COUNT(*) as calls, SUM(cost_usd) as total_cost
FROM ai_llm_usage_logs
WHERE api_name = 'snapshot' AND created_at > NOW() - INTERVAL 7 DAY
GROUP BY endpoint, deployment
ORDER BY total_cost DESC;

---

For Manual Scripts (No FastAPI)

Load env vars and call flush() before exit:

from dotenv import load_dotenv
from llm_tracker import TrackedOpenAI
from llm_tracker.logger import flush

load_dotenv()

client = TrackedOpenAI(api_key="...")
response = client.chat.completions.create(
    model="gpt-4o",
    messages=[...]
)

flush()  # ensure background writes finish before script exits

---

Supported Models

Built-in pricing for:

• gpt-4o — $0.0025 input / $0.01 output per 1K tokens
• gpt-4o-mini — $0.00015 input / $0.0006 output per 1K tokens
• gpt-4.1 — $0.002 input / $0.008 output per 1K tokens

Unknown models log $0.00 cost. Override pricing with LLM_TRACKER_PRICING_JSON.

---

Azure OpenAI Support

TrackedOpenAI and TrackedAsyncOpenAI automatically detect Azure endpoints and switch to the appropriate Azure client when needed. Simply pass your Azure endpoint and api_version:

from llm_tracker import TrackedOpenAI

client = TrackedOpenAI(
    api_key="...",
    base_url="https://my-resource.openai.azure.com/",  # or azure_endpoint=...
    api_version="2024-05-01",  # required for Azure
)
response = client.chat.completions.create(model="gpt-4o", messages=[...])

Detection works by checking if the endpoint URL contains openai.azure.com or cognitiveservices.azure.com. You can also use the explicit TrackedAzureOpenAI / TrackedAsyncAzureOpenAI classes if you prefer:

from llm_tracker import TrackedAzureOpenAI

client = TrackedAzureOpenAI(
    api_key="...",
    azure_endpoint="https://my-resource.openai.azure.com/",
    api_version="2024-05-01",
)
response = client.chat.completions.create(model="gpt-4o", messages=[...])

---

Async Support

For async apps, use TrackedAsyncOpenAI / TrackedAsyncAzureOpenAI — same API, await the call:

from llm_tracker import TrackedAsyncOpenAI
from llm_tracker.logger import aflush

client = TrackedAsyncOpenAI(api_key="...")
response = await client.chat.completions.create(
    model="gpt-4o",
    messages=[{"role": "user", "content": "hello"}],
)

await aflush()  # async-friendly equivalent of flush()

Azure auto-detection works with async clients too:

from llm_tracker import TrackedAsyncOpenAI

client = TrackedAsyncOpenAI(
    api_key="...",
    base_url="https://my-resource.openai.azure.com/",
    api_version="2024-05-01",
)
response = await client.chat.completions.create(model="gpt-4o", messages=[...])

Known Limitations

• ❌ Streaming (stream=True) not supported
• ❌ Embeddings not tracked (by design — cheap)
• ✓ Sync and async OpenAI/AzureOpenAI clients supported

---

Support

• Issues: GitHub issues
• Docs: See .env.example and schema.sql
• Examples: example_usage.py