Running Streamlit + Gemini as a Production BI Dashboard — Auth, Cost, Caching, Rate Limits, Observability

Understanding Gemini API cost with real numbers

This is where the article earns its keep. The first thing that bites you in production is "the Gemini bill creeps up and you cannot tell whose queries caused it."

As of May 2026, the relevant model tiers for BI workloads, with prices I measured on my own usage:

• Gemini 2.5 Flash — about ¥45 per 1M input tokens, ¥190 per 1M output. Schema summaries and single-shot analysis queries are fine here. Averaging 1,200 tokens per call, that's roughly ¥0.4 per call.
• Gemini 2.5 Pro — about 8x the Flash cost. Worth it only for long-context summarization or multi-step reasoning where the quality gap is visible. ¥3–¥4 per call.
• Gemini 3.x Pro family — reserve for "derive strategy from data" depth. ¥10–¥15 per call, so trigger it carefully.

My operating rule: default is always Flash. Pro tiers fire only when the user explicitly clicks a "Deep analysis" button. Switching to this dropped my monthly Gemini bill from ¥4,000 to ¥600.

# model_router.py
from google import genai
 
client = genai.Client(api_key=st.secrets["GEMINI_API_KEY"])
 
MODEL_TIERS = {
    "fast": "gemini-2.5-flash",
    "deep": "gemini-2.5-pro",
    "research": "gemini-3.0-pro-latest",
}
 
def route(prompt: str, tier: str = "fast", schema=None):
    model = MODEL_TIERS[tier]
    cfg = {"response_mime_type": "application/json"} if schema else {}
    if schema:
        cfg["response_schema"] = schema
    return client.models.generate_content(
        model=model, contents=prompt, config=cfg
    )

Keeping tiers as string keys lets you expose them as st.radio in the sidebar. Default the radio to Flash; only let the user opt into Pro when they actually need it. That single UX choice keeps billing predictable.

Result caching for analysis queries

Cutting the "uploaded the same CSV twice, paid twice" line. I run a three-layer cache.

Layer 1: in-process memory cache via @st.cache_data. Standard Streamlit. Same-session repeat queries hit this. A 1-hour TTL is my sweet spot.

@st.cache_data(ttl=3600, show_spinner="Querying Gemini…")
def analyze_schema(schema_summary: str, model_tier: str) -> dict:
    resp = route(
        prompt=f"Extract 3 key trends from this schema: {schema_summary}",
        tier=model_tier,
        schema=ANALYSIS_SCHEMA,
    )
    return resp.parsed

Layer 2: SHA-256-keyed persistent cache. Survives process restarts. Key on the SHA-256 of (schema_summary, question, tier), store the result in Cloudflare KV or SQLite. SQLite is plenty at indie scale. Critical detail: hash the schema summary, never raw CSV cells. Raw CSV contains PII, and a hash-collision-based lookup would leak across users.

import hashlib, sqlite3, json
 
def cache_key(schema_summary: str, question: str, tier: str) -> str:
    payload = f"{tier}|{schema_summary}|{question}".encode()
    return hashlib.sha256(payload).hexdigest()
 
def get_or_run(schema_summary: str, question: str, tier: str):
    key = cache_key(schema_summary, question, tier)
    conn = sqlite3.connect("cache.db")
    row = conn.execute(
        "SELECT result FROM cache WHERE key=? AND ts > strftime('%s','now')-86400",
        (key,),
    ).fetchone()
    if row:
        return json.loads(row[0]), True  # hit
    result = route(prompt=question + "\n\n" + schema_summary, tier=tier).text
    conn.execute(
        "INSERT OR REPLACE INTO cache(key, ts, result) VALUES(?, strftime('%s','now'), ?)",
        (key, json.dumps(result)),
    )
    conn.commit()
    return result, False  # miss

Layer 3: Gemini API context caching. Only when the schema summary is large (over 5,000 tokens) and you ask many questions against the same schema. Using cachedContents brings input token billing down to roughly a quarter. BI dashboards where "the same morning CSV gets queried daily" benefit most.

In my own BI the realized hit rate is 42%. That number reflects the fact that I look at the same Lacrima and Mystery KPIs every morning — a more general-purpose BI will land lower, but 20–30% is a reasonable expectation.

Concurrency control and per-user rate limits

Gemini's free tier sits at 15 RPM, paid plans commonly land at 60 or 360. Once multiple users share a BI, even normal click rates start triggering 429.

Two-stage defense.

Stage 1: global exponential backoff against project quota. tenacity is non-optional. Without it the Streamlit UI freezes the moment Gemini stutters.

from tenacity import retry, stop_after_attempt, wait_exponential, retry_if_exception_type
 
@retry(
    stop=stop_after_attempt(4),
    wait=wait_exponential(multiplier=1.5, min=2, max=20),
    retry=retry_if_exception_type(Exception),
    reraise=True,
)
def safe_generate(**kwargs):
    return client.models.generate_content(**kwargs)

Stage 2: per-user token bucket. Prevents one user mashing buttons from blowing the project quota and 429-ing everyone else. Ten requests per minute per user, kept in SQLite.

def user_rate_check(email: str, limit_per_min: int = 10) -> bool:
    conn = sqlite3.connect("rate.db")
    cutoff = int(time.time()) - 60
    conn.execute("DELETE FROM hits WHERE ts < ?", (cutoff,))
    count = conn.execute("SELECT COUNT(*) FROM hits WHERE email=?", (email,)).fetchone()[0]
    if count >= limit_per_min:
        return False
    conn.execute("INSERT INTO hits(email, ts) VALUES(?, ?)", (email, int(time.time())))
    conn.commit()
    return True

Since adding both stages I cannot recall the last 429-driven error screen on my BI.

Prompt injection for BI workloads

If you let Gemini generate SQL or Plotly code, the moment a user smuggles "; DROP TABLE ..." into a CSV cell, injection becomes real. Three layers of defense in my stack.

First, never let Gemini return free-form SQL. Force structured output with response_schema like {"chart_type": "bar"|"line", "x_column": str, "y_column": str}.

Second, sanitize CSV cell values before passing them in — drop anything over 200 chars or containing control characters from the sample rows you send to Gemini.

Third, parse any generated SQL or code through a whitelist-only AST. I use sqlglot and reject anything that isn't a SELECT against allowed tables.

import sqlglot
from sqlglot import exp
 
ALLOWED_TABLES = {"orders", "users", "events"}
 
def validate_sql(sql: str) -> bool:
    try:
        tree = sqlglot.parse_one(sql)
    except Exception:
        return False
    if not isinstance(tree, exp.Select):
        return False
    tables = {t.name for t in tree.find_all(exp.Table)}
    return tables.issubset(ALLOWED_TABLES)

Observability: knowing who asked what, costing what

Without this layer, you find out about a bad week from the next invoice. At minimum, log three things.

• Request log: user email, tier, input tokens, output tokens, cache hit/miss, latency.
• Cost calc: convert tokens to JPY at call time; aggregate per user, per day, per month.
• Alerts: notify Slack or Discord when any single user crosses ¥500 in a day.

SQLite plus a Streamlit admin page is enough at indie scale. I expose /admin gated to my email only.

def log_call(email, tier, in_tok, out_tok, cached, latency_ms):
    cost_jpy = (in_tok / 1_000_000 * PRICE_IN[tier] +
                out_tok / 1_000_000 * PRICE_OUT[tier])
    conn = sqlite3.connect("obs.db")
    conn.execute(
        """INSERT INTO calls(email, tier, in_tok, out_tok, cached, latency_ms, cost_jpy, ts)
           VALUES(?,?,?,?,?,?,?,?)""",
        (email, tier, in_tok, out_tok, int(cached), latency_ms, cost_jpy, int(time.time())),
    )
    conn.commit()
    if cost_jpy and daily_cost(email) > 500:
        notify_slack(f"⚠️ daily cost for {email} crossed ¥{daily_cost(email):.0f}")

One week after enabling logging I caught a bug where I had defaulted to Pro by accident. That month alone would have been an extra ¥3,000.

Deploy target comparison: Cloud Run vs Streamlit Community Cloud vs Cloudflare Tunnel

My conclusions from running all three:

• Streamlit Community Cloud — free, fastest deploy, but no custom domain and a cold start on every wake. Best for personal experiments and sub-5-user internal use.
• Cloud Run — ¥300–¥1,500 a month, custom domain, integrates cleanly with Cloudflare Access. Auto-scales. My pick for production BI.
• Cloudflare Tunnel + your own box — exposes an existing VPS or home Mac mini. Cheapest learning curve, availability is on you. Good for validation.

I keep two stacks: a production BI on Cloud Run + Cloudflare Access, and a personal validation BI on Cloudflare Tunnel + Mac mini.

The exact stack I run, and the gotchas I hit

Sharing the BI I currently run as a one-person operation while keeping the AdMob and App Store side of things alive:

• Auth: Cloudflare Access with Google OTP, restricted to my Google account.
• Hosting: Cloud Run, min instances=1, max=2.
• Models: Gemini 2.5 Flash default, 2.5 Pro behind a "Deep" toggle.
• Cache: @st.cache_data + SQLite, TTLs 1h and 24h.
• Rate limiting: tenacity exponential backoff + per-user 10 RPM.
• Observability: SQLite logging + Discord ping when any user exceeds ¥500/day.

Three gotchas to flag. First, min instances=0 saves a few hundred yen a month but every first morning request waits 8 seconds — I bumped it to 1. Second, passing a raw DataFrame into @st.cache_data hashes so slowly that caching is slower than not caching; pass the schema-summary string instead. Third, deeply nested response_schema definitions sometimes return 400 — I keep schemas at most two levels deep.

Next action

The single best next move after reading this is to write one sentence describing your current Streamlit + Gemini app's auth layer. If you're still sharing a bare Streamlit URL inside your team, install streamlit-authenticator first (30 minutes), then layer 2 through 5 on top in order. That sequence is the lowest-risk path I've found.