Spaces:
Sleeping
Sleeping
Download app.py from SuhasGholkar/botnew: direct link, hf CLI and curl.
- Browser
- Download file 312 kB
-
https://huggingface.co/spaces/SuhasGholkar/botnew/resolve/main/app.py
- Command line
-
hf download hf://spaces/SuhasGholkar/botnew/app.py
-
curl -L -o app.py https://huggingface.co/spaces/SuhasGholkar/botnew/resolve/main/app.py
312 kB
from __future__ import annotations
import logging
import re as _re
# ---- BEGIN RAG Publisher SQL logging (idempotent, single-pre/post; chat_history only) ----
# --- RAG Publisher SQL logging (safe wrapper; no C-type monkey patching) ---
# === Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
# === Prompt & SQL Loggers ===
prompt_logger = logging.getLogger("prompt")
sql_logger = logging.getLogger("sql")
for _lg in (prompt_logger, sql_logger):
if not _lg.handlers:
_h = logging.StreamHandler()
_h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
_lg.addHandler(_h)
_lg.setLevel(logging.INFO)
# === End Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
import re as _re
from typing import Any as _Any
_REDACTIONS = [
(_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"),
(_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"),
(_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"),
]
def _redact(text: str) -> str:
if not text:
return text
out = text
for _rx, _repl in _REDACTIONS:
out = _rx.sub(_repl, out)
return out
def _truncate(text: str, max_chars: int = 4000) -> str:
if not text:
return text
return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>"
def _j(_obj: _Any) -> str:
try:
import json as _json
return _json.dumps(_obj, ensure_ascii=False, default=str)
except Exception:
return str(_obj)
def log_llm_inputs(event: str,
user_id: str | None,
chat_id: str | None,
model: str,
messages: list[dict],
context: str | None = None,
exemplars: list[dict] | None = None,
plan: dict | None = None,
extra: dict | None = None):
payload = {
"event": event,
"user_id": user_id,
"chat_id": chat_id,
"model": model,
"context": _truncate(_redact(context or ""), 3000),
"exemplars": exemplars or [],
"plan": plan or {},
"messages": [],
"extra": extra or {}
}
for m in messages or []:
payload["messages"].append({
"role": m.get("role"),
"name": m.get("name"),
"content": _truncate(_redact(str(m.get("content", ""))), 3000)
})
try:
prompt_logger.info(_j(payload))
except Exception as _e:
try:
prompt_logger.warning("log_llm_inputs failed: %s", _e)
except Exception:
pass
def log_secured_sql(chat_id: str | None,
user_id: str | None,
secured_sql: str | None,
sql_params: dict | list | None = None,
table_guard: dict | None = None):
if not secured_sql:
return
payload = {
"event": "secured_sql_ready",
"chat_id": chat_id,
"user_id": user_id,
"sql": _truncate(_redact(secured_sql), 4000),
"params": sql_params or {},
"guards": table_guard or {},
}
try:
sql_logger.info(_j(payload))
except Exception as _e:
try:
sql_logger.warning("log_secured_sql failed: %s", _e)
except Exception:
pass
def log_router_decision(chat_id: str | None,
user_id: str | None,
text: str,
label: str | None,
lang: str | None,
route: str):
try:
prompt_logger.info(_j({
"event": "router_decision",
"chat_id": chat_id,
"user_id": user_id,
"lang": lang,
"route": route,
"label": label,
"text": _truncate(_redact(text), 2000),
}))
except Exception as _e:
try:
prompt_logger.warning("log_router_decision failed: %s", _e)
except Exception:
pass
# === End Helpers ===
import re as _re
from typing import Any as _Any
_REDACTIONS = [
(_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"),
(_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"),
(_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"),
]
def _redact(text: str) -> str:
if not text:
return text
out = text
for _rx, _repl in _REDACTIONS:
out = _rx.sub(_repl, out)
return out
def _truncate(text: str, max_chars: int = 4000) -> str:
if not text:
return text
return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>"
def _j(_obj: _Any) -> str:
try:
import json as _json
return _json.dumps(_obj, ensure_ascii=False, default=str)
except Exception:
return str(_obj)
def log_llm_inputs(event: str,
user_id: str | None,
chat_id: str | None,
model: str,
messages: list[dict],
context: str | None = None,
exemplars: list[dict] | None = None,
plan: dict | None = None,
extra: dict | None = None):
payload = {
"event": event,
"user_id": user_id,
"chat_id": chat_id,
"model": model,
"context": _truncate(_redact(context or ""), 3000),
"exemplars": exemplars or [],
"plan": plan or {},
"messages": [],
"extra": extra or {}
}
for m in messages or []:
payload["messages"].append({
"role": m.get("role"),
"name": m.get("name"),
"content": _truncate(_redact(str(m.get("content", ""))), 3000)
})
try:
prompt_logger.info(_j(payload))
except Exception as _e:
try:
prompt_logger.warning("log_llm_inputs failed: %s", _e)
except Exception:
pass
def log_secured_sql(chat_id: str | None,
user_id: str | None,
secured_sql: str | None,
sql_params: dict | list | None = None,
table_guard: dict | None = None):
if not secured_sql:
return
payload = {
"event": "secured_sql_ready",
"chat_id": chat_id,
"user_id": user_id,
"sql": _truncate(_redact(secured_sql), 4000),
"params": sql_params or {},
"guards": table_guard or {},
}
try:
sql_logger.info(_j(payload))
except Exception as _e:
try:
sql_logger.warning("log_secured_sql failed: %s", _e)
except Exception:
pass
def log_router_decision(chat_id: str | None,
user_id: str | None,
text: str,
label: str | None,
lang: str | None,
route: str):
try:
prompt_logger.info(_j({
"event": "router_decision",
"chat_id": chat_id,
"user_id": user_id,
"lang": lang,
"route": route,
"label": label,
"text": _truncate(_redact(text), 2000),
}))
except Exception as _e:
try:
prompt_logger.warning("log_router_decision failed: %s", _e)
except Exception:
pass
# === End Helpers ===
prompt_logger = logging.getLogger("prompt")
sql_logger = logging.getLogger("sql")
for _lg in (prompt_logger, sql_logger):
if not _lg.handlers:
_h = logging.StreamHandler()
_h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
_lg.addHandler(_h)
_lg.setLevel(logging.INFO)
# === End Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
import re as _re
from typing import Any as _Any
_REDACTIONS = [
(_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"),
(_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"),
(_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"),
]
def _redact(text: str) -> str:
if not text:
return text
out = text
for _rx, _repl in _REDACTIONS:
out = _rx.sub(_repl, out)
return out
def _truncate(text: str, max_chars: int = 4000) -> str:
if not text:
return text
return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>"
def _j(_obj: _Any) -> str:
try:
import json as _json
return _json.dumps(_obj, ensure_ascii=False, default=str)
except Exception:
return str(_obj)
def log_llm_inputs(event: str,
user_id: str | None,
chat_id: str | None,
model: str,
messages: list[dict],
context: str | None = None,
exemplars: list[dict] | None = None,
plan: dict | None = None,
extra: dict | None = None):
payload = {
"event": event,
"user_id": user_id,
"chat_id": chat_id,
"model": model,
"context": _truncate(_redact(context or ""), 3000),
"exemplars": exemplars or [],
"plan": plan or {},
"messages": [],
"extra": extra or {}
}
for m in messages or []:
payload["messages"].append({
"role": m.get("role"),
"name": m.get("name"),
"content": _truncate(_redact(str(m.get("content", ""))), 3000)
})
try:
prompt_logger.info(_j(payload))
except Exception as _e:
try:
prompt_logger.warning("log_llm_inputs failed: %s", _e)
except Exception:
pass
def log_secured_sql(chat_id: str | None,
user_id: str | None,
secured_sql: str | None,
sql_params: dict | list | None = None,
table_guard: dict | None = None):
if not secured_sql:
return
payload = {
"event": "secured_sql_ready",
"chat_id": chat_id,
"user_id": user_id,
"sql": _truncate(_redact(secured_sql), 4000),
"params": sql_params or {},
"guards": table_guard or {},
}
try:
sql_logger.info(_j(payload))
except Exception as _e:
try:
sql_logger.warning("log_secured_sql failed: %s", _e)
except Exception:
pass
def log_router_decision(chat_id: str | None,
user_id: str | None,
text: str,
label: str | None,
lang: str | None,
route: str):
try:
prompt_logger.info(_j({
"event": "router_decision",
"chat_id": chat_id,
"user_id": user_id,
"lang": lang,
"route": route,
"label": label,
"text": _truncate(_redact(text), 2000),
}))
except Exception as _e:
try:
prompt_logger.warning("log_router_decision failed: %s", _e)
except Exception:
pass
# === End Helpers ===
def _install_ragpub_sql_logging() -> None:
"""
Wrap sqlite3.connect to return logging proxies that:
- Only log SELECTs that touch the chat_history table
- Log the *final fetched row count* (when fetchone/fetchmany/fetchall is called)
- Never alter underlying behavior or consume results early
This avoids trying to set attributes on immutable C types.
"""
import sqlite3 as _sqlite3
import re as _re
import functools as norm
log = logging.getLogger("olist")
# Already installed?
if getattr(_install_ragpub_sql_logging, "_installed", False):
return
# Save the original connect so we can delegate
_orig_connect = _sqlite3.connect
# Detect whether a SQL statement reads from chat_history
_chat_pat = _re.compile(r"\b(from|join)\s+chat_history\b", _re.IGNORECASE)
def _touches_chat_history(sql: str) -> bool:
try:
if not sql:
return False
# Only care about SELECT-like queries
s = sql.lstrip().lower()
if not (s.startswith("select") or s.startswith("with")):
return False
return _chat_pat.search(s) is not None
except Exception:
return False
class _LoggedCursor:
"""
Thin cursor proxy that logs row counts for SELECTs on chat_history.
We only log *after* the caller fetches so we don't consume results.
"""
def __init__(self, inner, logger):
self._c = inner
self._logger = logger
self._last_sql = None
self._touches = False
# Execute variants
def execute(self, sql, params=()):
self._last_sql = sql
self._touches = _touches_chat_history(sql)
return self.__class__(self._c.execute(sql, params), self._logger)
def executemany(self, sql, seq_of_params):
self._last_sql = sql
self._touches = _touches_chat_history(sql)
return self.__class__(self._c.executemany(sql, seq_of_params), self._logger)
def executescript(self, sql_script):
# Treat script as non-loggable; scripts are unusual for our paths
self._last_sql = sql_script
self._touches = False
return self._c.executescript(sql_script)
# Fetch methods (where we can safely know row counts)
def fetchall(self):
rows = self._c.fetchall()
if self._touches:
try:
self._logger.info("[ragpub] rows: %d", len(rows))
except Exception:
pass
return rows
def fetchone(self):
row = self._c.fetchone()
if self._touches:
try:
self._logger.info("[ragpub] rows: %d", 1 if row is not None else 0)
except Exception:
pass
return row
def fetchmany(self, size=None):
rows = self._c.fetchmany(size if size is not None else 0)
if self._touches:
try:
self._logger.info("[ragpub] rows: %d", len(rows))
except Exception:
pass
return rows
# Delegate any other attribute/method transparently
def __getattr__(self, name):
return getattr(self._c, name)
# Context manager passthrough
def __enter__(self):
self._c.__enter__()
return self
def __exit__(self, exc_type, exc, tb):
return self._c.__exit__(exc_type, exc, tb)
class _LoggedConnection:
"""
Connection proxy that wraps .execute()/.cursor() to attach logging cursors.
"""
def __init__(self, inner, logger):
self._conn = inner
self._logger = logger
# Connection-level convenience execute returns a cursor — wrap it.
def execute(self, sql, params=()):
if _touches_chat_history(sql):
try:
self._logger.info("[ragpub] read_sql: %s", sql)
except Exception:
pass
cur = self._conn.execute(sql, params)
return _LoggedCursor(cur, self._logger)
def executemany(self, sql, seq_of_params):
if _touches_chat_history(sql):
try:
self._logger.info("[ragpub] read_sql: %s", sql)
except Exception:
pass
cur = self._conn.executemany(sql, seq_of_params)
return _LoggedCursor(cur, self._logger)
def executescript(self, sql_script):
# Scripts: no special logging here
return self._conn.executescript(sql_script)
def cursor(self):
# Every regular cursor becomes a logging cursor
cur = self._conn.cursor()
return _LoggedCursor(cur, self._logger)
# Delegate everything else (commit, close, etc.)
def __getattr__(self, name):
return getattr(self._conn, name)
# Context manager passthrough
def __enter__(self):
self._conn.__enter__()
return self
def __exit__(self, exc_type, exc, tb):
return self._conn.__exit__(exc_type, exc, tb)
@_functools.wraps(_sqlite3.connect)
def _logged_connect(*args, **kwargs):
# Keep caller’s factory if they intentionally set one
if "factory" in kwargs and kwargs["factory"] is not None:
return _orig_connect(*args, **kwargs)
conn = _orig_connect(*args, **kwargs)
return _LoggedConnection(conn, log)
# Install wrapper once
_sqlite3.connect = _logged_connect
_install_ragpub_sql_logging._installed = True
def _rag_fetch_exemplars_for_query(
query_text: str,
k: int = 4,
min_relevance: float = 0.0,
) -> list[dict]:
"""
Retrieve top-k exemplar items (docs + metadata) for the given query from Chroma.
Returns a list of dicts: {"user_input","llm_plan","secured_sql","distance","doc","id"}.
"""
try:
import os as _os
import chromadb
from chromadb.utils import embedding_functions
# Embedder must match publisher (OpenAI or ONNX)
openai_key = _os.environ.get("OPENAI_API_KEY") or _os.environ.get("OPENAI_APIKEY")
ef = None
ef_dim = None
if openai_key:
embed_model = _os.environ.get("OPENAI_EMBED_MODEL", "text-embedding-3-small")
ef = embedding_functions.OpenAIEmbeddingFunction(api_key=openai_key, model_name=embed_model)
ef_dim = OPENAI_EMBED_DIM
else:
try:
from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2
ef = ONNXMiniLM_L6_V2()
ef_dim = ONNX_EMBED_DIM
except Exception:
ef = None
ef_dim = None
chroma_path = _os.environ.get("CHROMA_PATH", _os.environ.get("RAG_DIR", "/data/rag_store"))
coll_name = _os.environ.get("RAG_COLLECTION", "olist_sql_exemplars")
lg = logging.getLogger("olist")
lg.info("[rag-read] chroma client init | path=%s | collection=%s", chroma_path, coll_name)
client = chromadb.PersistentClient(path=chroma_path)
try:
coll = _get_or_migrate_chroma_collection(client, coll_name, ef, (ef_dim or OPENAI_EMBED_DIM), lg)
except Exception:
coll = client.get_or_create_collection(coll_name, embedding_function=ef)
if not (query_text or "").strip():
return []
res = coll.query(query_texts=[query_text], n_results=max(1, int(k)))
# Shape depends on Chroma version; handle defensively.
out = []
docs = (res.get("documents") or [[]])[0]
metas = (res.get("metadatas") or [[]])[0]
ids = (res.get("ids") or [[]])[0]
dists = (res.get("distances") or [[]])[0] or (res.get("embeddings") and [None] * len(docs)) or [None] * len(docs)
for i, doc in enumerate(docs):
md = metas[i] if i < len(metas) else {}
row = {
"id": ids[i] if i < len(ids) else None,
"doc": doc,
"distance": dists[i] if i < len(dists) else None,
"user_input": (md or {}).get("user_input"),
"llm_plan": (md or {}).get("llm_plan"),
"secured_sql": (md or {}).get("secured_sql"),
"tag": (md or {}).get("tag"),
"source": (md or {}).get("source"),
"chat_id": (md or {}).get("chat_id"),
"user_id": (md or {}).get("user_id"),
}
out.append(row)
# Optional relevance filter
if min_relevance is not None and out:
def _ok(x):
d = x.get("distance")
return True if d is None else (d <= (1.0 - min_relevance)) # adjust to your distance/score semantics
out = [x for x in out if _ok(x)]
# Log a compact sample
try:
samp = out[0] if out else None
logging.getLogger("olist").info("[rag-read] hits=%s | sample=%s", len(out), {
"id": samp and samp.get("id"),
"tag": samp and samp.get("tag"),
"distance": samp and samp.get("distance"),
"has_sql": bool(samp and (samp.get("secured_sql") or "").strip()),
})
except Exception:
pass
return out
except Exception as e:
logging.getLogger("olist").warning("[rag-read] failed: %s", e)
return []
def _rewrite_limit_and(sql: str) -> str:
s = (sql or "").strip()
# Fix pattern like: ... WHERE ... LIMIT 1 /*AND-misplaced*/ AND <cond>
m = _re.search(r"(?is)\bLIMIT\s+\d+\s+AND\s+(.+)$", s)
if m:
# Split into 'before limit' and trailing cond
parts = _re.split(r"(?is)\bLIMIT\s+\d+\s+AND\s+", s, maxsplit=1)
if len(parts) == 2:
before_limit = parts[0]
after_cond = parts[1]
# Extract the LIMIT value from original
mlim = _re.search(r"(?is)(.*)\bLIMIT\s+(\d+)", before_limit)
if mlim:
head = mlim.group(1)
limit_val = mlim.group(2)
fixed_head = _inject_where(head, after_cond)
return _cleanup_where_and(f"{fixed_head} LIMIT {limit_val}")
return sql
import pandas as pd # noqa: F401
import os
# --- begin: quiet Torch/Transformers imports ---
def _silence_torch_noise():
"""
Prepare a reusable stderr redirection context that doesn't close the buffer.
- Do NOT touch TORCH_LOGS (new PyTorch rejects values like 'off')
- Keep C++ logs quiet and avoid GPU probing
- Provide _TorchNoiseStderrShield to mute stderr during heavy imports
"""
try:
import os, io, contextlib
# Leave TORCH_LOGS unset; if present and invalid, remove it
if os.environ.get("TORCH_LOGS") == "off":
try: del os.environ["TORCH_LOGS"]
except Exception: pass
os.environ.setdefault("TORCH_CPP_LOG_LEVEL", "ERROR")
os.environ.setdefault("CUDA_VISIBLE_DEVICES", "")
class _StderrShield:
"""Redirects stderr to an in-memory buffer WITHOUT closing it on exit."""
def __init__(self):
import io, contextlib
self._buf = io.StringIO()
self._cm = contextlib.redirect_stderr(self._buf)
def __enter__(self):
return self._cm.__enter__()
def __exit__(self, exc_type, exc, tb):
# Restore original stderr but DO NOT close the buffer.
# Keeping it alive avoids 'I/O operation on closed file'
self._cm.__exit__(exc_type, exc, tb)
# Export the shield for use in other code paths
globals()["_TorchNoiseStderrShield"] = _StderrShield
except Exception:
pass
_silence_torch_noise()
# --- end: quiet Torch/Transformers imports ---
# Hard-coded collection for RAG Publisher (not shown to end users)
RAG_COLLECTION = os.getenv("RAG_COLLECTION", "olist_sql_exemplars") # keep env override if you want
RAG_PUBLISH_COLLECTION = RAG_COLLECTION # single source of truth for publisher
OPENAI_EMBED_MODEL = os.getenv("OPENAI_EMBED_MODEL", "text-embedding-3-small")
OPENAI_EMBED_DIM = 1536 # text-embedding-3-small & large are 1536
ONNX_EMBED_DIM = 384 # MiniLM-L6-v2
def _get_or_migrate_chroma_collection(client, base_name: str, ef, ef_dim: int, logger):
"""
Get a Chroma collection bound to the given embedding function.
If an existing collection on disk has a different embedding dimension,
transparently fall back to a dimension-suffixed collection name
(e.g., '<base>_d1536') to avoid InvalidArgumentError.
"""
name_try = base_name
try:
return client.get_or_create_collection(name_try, embedding_function=ef)
except Exception as e:
msg = str(e)
# If mismatch, Chroma typically throws InvalidArgumentError about dimensions.
if "dimension" in msg.lower() or "expecting embedding with dimension" in msg:
name_try = f"{base_name}_d{ef_dim}"
try:
lg = logger or logging.getLogger("olist")
lg.warning("Chroma dimension mismatch on '%s'. Using '%s' instead.", base_name, name_try)
except Exception:
pass
return client.get_or_create_collection(name_try, embedding_function=ef)
raise
# --- RAG Publisher SQL logging shim (back-compat, chat_history-only) ---
def _install_rag_sql_logging():
"""
Legacy shim that used to log all pd.read_sql* calls.
Now:
- If the new chat_history-only shim already wrapped pandas, this is a no-op.
- Otherwise, we wrap read_sql/read_sql_query but ONLY log when the SQL
actually touches the chat_history table.
"""
try:
import pandas as _pd
import logging as _lg
import time as _time
import re as _re
except Exception:
return
# If the new shim already wrapped pandas, bail out (avoid double wrapping).
if getattr(_pd, "_RAGPUB_SQL_WRAPPED_V3", False):
return
# If this legacy shim already wrapped once, don't wrap again.
if getattr(_pd, "_RAGPUB_SQL_WRAPPED_V2", False):
return
_olist_logger = _lg.getLogger("olist")
def _norm_sql(sql):
try:
s = str(sql)
except Exception:
return None
s = s.replace("\n", " ").replace("\r", " ")
s = _re.sub(r"\s+", " ", s).strip()
return s
def _touches_chat_history(sql_text: str) -> bool:
try:
return bool(sql_text) and ("chat_history" in sql_text.lower())
except Exception:
return False
def _wrap(fn_name: str):
real = getattr(_pd, fn_name, None)
if real is None or getattr(real, "_olist_rag_wrap", False):
return
def _wrapped(*args, **kwargs):
# Figure out the SQL string from args/kwargs
sql = kwargs.get("sql", args[0] if args else None)
sql_str = _norm_sql(sql)
do_log = _touches_chat_history(sql_str)
t0 = _time.perf_counter()
if do_log:
_olist_logger.info("[ragpub] read_sql: %s", sql_str)
df = real(*args, **kwargs)
if do_log:
try:
n = getattr(df, "shape", (None, None))[0]
except Exception:
n = None
_olist_logger.info("[ragpub] rows: %s", n)
return df
_wrapped._olist_rag_wrap = True
_wrapped.__wrapped__ = real
setattr(_pd, fn_name, _wrapped)
for _name in ("read_sql", "read_sql_query"):
_wrap(_name)
# mark legacy wrapper installed so we don't wrap again
_pd._RAGPUB_SQL_WRAPPED_V2 = True
# --- END legacy shim ---
# Install the shim as soon as pandas is available
try:
import pandas as _pd_check # noqa
if _pd_check is not None:
_install_rag_sql_logging()
except Exception:
pass
try:
import streamlit as st # noqa: F401
except Exception:
pass
# --- FIX v2: logger dedupe + run-once guards (26-Sep-2025) ---
import os, sys, logging
import os as _olist_os
import pathlib as _olist_pathlib
import logging as _olist_logging
from typing import Optional
import re
# === Auto-Chart Helpers (Admin-only) ===
def _infer_columns_for_chart(df: pd.DataFrame):
"""
Return a dict describing the best chart config, or None.
Heuristics:
- scatter: 2 numeric columns
- bar/pie: 1 categorical + 1 numeric (≤ 8 categories -> pie, else bar)
- stacked_bar: 2 categoricals + 1 numeric
"""
try:
if df is None or df.empty:
return None
df2 = df.copy()
num_cols = [c for c in df2.columns if pd.api.types.is_numeric_dtype(df2[c])]
cat_cols = [c for c in df2.columns if (not pd.api.types.is_numeric_dtype(df2[c])) and df2[c].nunique() > 0]
preferred_cats = [c for c in df2.columns if c.lower() in (
"category","categories","product_category","seller_id","product_id","state","city",
"payment_type","status","month","date","order_status","review_score"
)]
preferred_nums = [c for c in df2.columns if c.lower() in (
"count","cnt","total","sum","amount","revenue","value","score","quantity","qty","gmv"
)]
if len(num_cols) >= 2:
return {"kind":"scatter", "x": num_cols[0], "y": num_cols[1]}
if cat_cols and num_cols:
cat = preferred_cats[0] if preferred_cats and preferred_cats[0] in cat_cols else cat_cols[0]
num = preferred_nums[0] if preferred_nums and preferred_nums[0] in num_cols else num_cols[0]
top = (df2[[cat, num]]
.groupby(cat, dropna=False, as_index=False)[num].sum()
.sort_values(num, ascending=False))
ncat = top[cat].nunique()
if ncat <= 8 and (top[num] >= 0).all():
return {"kind":"pie", "cat": cat, "val": num, "data": top.head(8)}
else:
return {"kind":"bar", "x": cat, "y": num, "data": top.head(25)}
if len(cat_cols) >= 2 and num_cols:
cat1, cat2 = cat_cols[:2]
num = num_cols[0]
data = (df2[[cat1, cat2, num]]
.groupby([cat1, cat2], dropna=False, as_index=False)[num].sum())
return {"kind":"stacked_bar", "x": cat1, "hue": cat2, "y": num, "data": data}
return None
except Exception:
return None
def _render_chart_matplotlib(cfg: dict, df: pd.DataFrame):
import matplotlib.pyplot as plt
try:
kind = cfg.get("kind")
if kind == "scatter":
x, y = cfg["x"], cfg["y"]
fig, ax = plt.subplots()
ax.scatter(df[x], df[y])
ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} vs {x}")
st.pyplot(fig, use_container_width=True)
elif kind == "bar":
data = cfg.get("data", df)
x, y = cfg["x"], cfg["y"]
fig, ax = plt.subplots()
ax.bar(data[x].astype(str), data[y])
ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} by {x}")
ax.tick_params(axis='x', rotation=45)
st.pyplot(fig, use_container_width=True)
elif kind == "pie":
data = cfg.get("data", df)
cat, val = cfg["cat"], cfg["val"]
fig, ax = plt.subplots()
vals = data[val].clip(lower=0)
ax.pie(vals, labels=data[cat].astype(str), autopct=lambda p: f"{p:.0f}%")
ax.set_title(f"{val} share by {cat}")
st.pyplot(fig, use_container_width=True)
elif kind == "stacked_bar":
data = cfg.get("data", df)
x, hue, y = cfg["x"], cfg["hue"], cfg["y"]
pivot = data.pivot_table(index=x, columns=hue, values=y, aggfunc="sum", fill_value=0)
fig, ax = plt.subplots()
bottom = None
for col in pivot.columns:
vals = pivot[col].values
if bottom is None:
ax.bar(pivot.index.astype(str), vals, label=str(col))
bottom = vals
else:
ax.bar(pivot.index.astype(str), vals, bottom=bottom, label=str(col))
bottom = bottom + vals
ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} by {x} and {hue}")
ax.legend(loc="best", fontsize="small")
ax.tick_params(axis='x', rotation=45)
st.pyplot(fig, use_container_width=True)
else:
st.info("No suitable chart found.")
except Exception as e:
st.warning(f"Could not render chart: {e}")
def _call_render_df(df, key_prefix: str, page_size: int = 10):
"""
Indirectly call the global render_df_with_pagination to avoid any local binding issues.
Falls back to st.dataframe if the function isn't available yet in this scope.
"""
import sys
fn = getattr(sys.modules[__name__], "render_df_with_pagination", None)
try:
if callable(fn):
return fn(df, key_prefix, page_size)
except Exception:
pass
try:
st.dataframe(df)
except Exception:
pass
# Make matplotlib happy in containers (already helped in your logs)
os.environ.setdefault("MPLCONFIGDIR", "/tmp/mplconfig")
try:
os.makedirs(os.environ["MPLCONFIGDIR"], exist_ok=True)
except Exception:
pass
# Simple per-process once registry
__ONCE_FLAGS = set()
def run_once(key: str) -> bool:
"""Return True only the first time a given key is seen in this process."""
if key in __ONCE_FLAGS:
return False
__ONCE_FLAGS.add(key)
return True
# Idempotent, non-propagating logger setup with a marker on our handler
_FMT = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")
def _ensure_logger(name: Optional[str], level: int = logging.INFO) -> logging.Logger:
"""
Ensure a logger exists with one console handler and a unique marker so we don't
add handlers again on Streamlit reruns. If `name` is None, returns the root logger.
"""
lg = logging.getLogger(name) if name else logging.getLogger()
# Avoid duplicate handlers across reruns by looking for our marker
have_marker = any(getattr(h, "_olist_marker", False) for h in lg.handlers)
if not have_marker:
fmt = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")
ch = logging.StreamHandler()
ch.setLevel(level)
ch.setFormatter(fmt)
# mark this handler so we don't add another one on rerun
setattr(ch, "_olist_marker", True)
lg.addHandler(ch)
# If you also want a file handler in /data, uncomment:
# try:
# import os
# os.makedirs("/data", exist_ok=True)
# fh = logging.FileHandler("/data/app.log", encoding="utf-8")
# fh.setLevel(level)
# fh.setFormatter(fmt)
# setattr(fh, "_olist_marker", True)
# lg.addHandler(fh)
# except Exception as e:
# # fall back silently if not writable
# pass
# Don’t propagate to parents to avoid duplicate prints
lg.propagate = False
# Always enforce desired level (safe if already set)
lg.setLevel(level)
return lg
# Prepare our named loggers exactly once per process
if run_once("loggers_configured"):
_ensure_logger(None, logging.INFO) # root
olist_logger = _ensure_logger("olist", logging.DEBUG)
feedback_logger = _ensure_logger("feedback", logging.DEBUG)
# Only announce readiness once per process; guards prevent dupes on re-import
if run_once("olist_ready"):
olist_logger.info("olist logger ready (level=DEBUG)")
if run_once("feedback_ready"):
feedback_logger.info("feedback logger ready (level=DEBUG)")
# --- END FIX v2 ---
# Unify DB logging: both names point to the same "olist" logger
_olist_logger = logging.getLogger("olist")
# Ensure both symbols exist so older/newer code paths work
try:
db_logger
except NameError:
db_logger = _olist_logger
try:
_db_logger
except NameError:
_db_logger = _olist_logger
# Quiet down very chatty third‑party libs unless explicitly opted in
if _olist_os.environ.get("OLIST_VERBOSE_LIBS") not in ("1", "true", "True"):
for _name in (
"watchdog.observers.inotify_buffer",
"PIL.PngImagePlugin",
"matplotlib.font_manager",
"matplotlib.pyplot",
"httpcore.connection",
"httpcore.http11",
"openai._base_client",
):
_olist_logging.getLogger(_name).setLevel(_olist_logging.WARNING)
# --- END FIX ---
# Heuristic: 'my' + (order|orders|payment|payment type) should go to SQL, not small-talk
_MY_ACCOUNT_PAT = re.compile(r"\bmy\b.*\b(order|orders|payment|payment\s*type)\b|\b(order|orders|payment)\b.*\bmy\b", re.IGNORECASE)
def _is_my_account_query(text: str) -> bool:
try:
return bool(_MY_ACCOUNT_PAT.search(text or ''))
except Exception as e:
return False
def _clear_chat_input(_key: str = "user_query"):
try:
import streamlit as st
if _key in st.session_state:
st.session_state[_key] = ""
except Exception:
pass
# --- RAG config ---
RAG_BACKEND = os.getenv("RAG_BACKEND", "CHROMA").upper() # CHROMA | TFIDF
RAG_DIR = os.getenv("RAG_DIR", "/data/rag_store")
RAG_COLLECTION = os.getenv("RAG_COLLECTION", "olist_sql_exemplars")
OPENAI_EMBED_MODEL = os.getenv("OPENAI_EMBED_MODEL", "text-embedding-3-small")
EMBED_DIM = 1536
logging.info("[chroma] dir=%s exists=%s ls=%s", RAG_DIR, os.path.exists(RAG_DIR), os.listdir(RAG_DIR) if os.path.exists(RAG_DIR) else None)
# --- begin: cache hardening helper (safe to add) ---
def _ensure_writable_model_caches():
"""Force all typical model caches to /data/.cache and patch Chroma's ONNX path."""
import os
# put all caches under /data/.cache (writable in your container)
os.environ.setdefault("HOME", "/data")
os.environ.setdefault("XDG_CACHE_HOME", "/data/.cache")
os.environ.setdefault("HF_HOME", "/data/.cache")
os.environ.setdefault("HUGGINGFACE_HUB_CACHE", "/data/.cache")
os.environ.setdefault("TRANSFORMERS_CACHE", "/data/.cache")
os.environ.setdefault("SENTENCE_TRANSFORMERS_HOME", "/data/.cache")
try:
os.makedirs(os.environ["XDG_CACHE_HOME"], exist_ok=True)
except Exception:
pass
# patch Chroma default ONNX embedder to download into our cache dir
try:
from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2
onnx_cache = os.path.join(os.environ.get("XDG_CACHE_HOME", "/data/.cache"),
"chromadb", "onnx_models")
try:
os.makedirs(onnx_cache, exist_ok=True)
except Exception:
pass
try:
ONNXMiniLM_L6_V2.DOWNLOAD_PATH = onnx_cache
except Exception:
pass
except Exception:
# it's fine if this import fails here; OpenAI EF path won't use it
pass
# --- end: cache hardening helper ---
# app.py — Olist Analytics Assistant (LLM→SQL) with Indic (hi/mr) translation
# ---------------------------------------------------------------------
import os
import re
import time
import hashlib
import json
# ==== RAG imports (1212) ====
try:
_shield = globals().get("_TorchNoiseStderrShield")
if _shield:
with _shield():
import chromadb # may transitively import onnx/torch pieces
from chromadb.utils import embedding_functions as cef
else:
import chromadb
from chromadb.utils import embedding_functions as cef
except Exception:
chromadb = None
cef = None
import math
import bcrypt
import sqlite3
import pandas as pd
import yaml
import streamlit as st
import eda
from streamlit.runtime.scriptrunner import StopException
# ---- Ensure page config is set before any other Streamlit calls ----
try:
_pcfg = st.session_state.get("_page_config_set", False)
except Exception:
_pcfg = False
if not _pcfg:
try:
st.set_page_config(page_title="SmartAssist BOT", layout="wide", initial_sidebar_state="collapsed")
except Exception:
pass # ignore if already set
try:
st.session_state["_page_config_set"] = True
except Exception:
pass
# -------------------------------------------------------------------
# --- UI sanitizer for React-safe tables ---
def _ui_safe_cell(x):
# Convert dict/list/other objects to JSON or string so Streamlit/React won’t choke
import json
try:
if isinstance(x, (dict, list)):
return json.dumps(x, ensure_ascii=False)
return str(x)
except Exception:
return str(x)
# --- Safe display helper (prevents Streamlit 'function is not iterable' when a DB cursor sneaks in) ---
def _as_dataframe_safely(obj):
"""Return a pandas DataFrame from many possible SQL result shapes.
Handles: pandas.DataFrame, sqlite3.Cursor (attr or callable description), list[tuple].
Never raises; falls back to empty DataFrame.
"""
try:
import pandas as pd
# Already a DataFrame
if isinstance(obj, pd.DataFrame):
return obj
# sqlite3.Cursor (or lookalike): has .fetchall and .description
if hasattr(obj, "fetchall") and hasattr(obj, "description"):
try:
rows = obj.fetchall()
except Exception:
rows = []
desc = getattr(obj, "description", None)
try:
cols = [d[0] for d in (desc() if callable(desc) else (desc or []))]
except Exception:
cols = None
try:
return pd.DataFrame(rows, columns=cols)
except Exception:
return pd.DataFrame(rows)
# Plain Python sequence of rows
if isinstance(obj, (list, tuple)):
try:
import pandas as pd
return pd.DataFrame(list(obj))
except Exception:
pass
except Exception:
pass
# ultimate fallback
try:
import pandas as pd
return pd.DataFrame()
except Exception:
return obj
def _assistant_text_for_transcript(out: dict, default: str = "") -> str:
try:
if out.get("text"):
return out["text"]
plan = out.get("plan") or {}
if (SHOW_EXPLAIN_PLAN and plan.get("explanation_display")):
return str((SHOW_EXPLAIN_PLAN and plan.get("explanation_display")))
if (SHOW_EXPLAIN_PLAN and plan.get("explanation")):
return str((SHOW_EXPLAIN_PLAN and plan.get("explanation")))
qs = out.get("queries") or []
for q in qs:
if q.get("kind") == "non_sql":
df = q.get("df")
if df is not None:
try:
import pandas as pd
if isinstance(df, pd.DataFrame) and not df.empty and "message" in df.columns:
return str(df.iloc[0]["message"])
except Exception:
pass
return default
except Exception:
return default
def _begin_turn(user_text: str = ""):
try:
st.session_state['_transcript_done'] = False
st.session_state['last_query_text'] = user_text or st.session_state.get('last_query_text') or ''
except Exception:
pass
def _append_transcript_once(user_text: str, out: dict | None = None, explicit_text: str | None = None):
try:
if st.session_state.get('_transcript_done'):
return
asst = explicit_text if explicit_text is not None else _assistant_text_for_transcript(out or {}, default="")
st.session_state.setdefault('transcript', [])
st.session_state['transcript'].append({'user': user_text, 'assistant': asst, 'ts': time.time()})
st.session_state['_transcript_done'] = True
except Exception:
pass
import logging, sys
from datetime import datetime, timedelta
from contextlib import contextmanager
from collections import deque
from typing import Optional, Tuple, List, Dict, Any
import matplotlib.pyplot as plt
from mis import seller_scorecard_df, render_customer_360, render_seller_scorecards, ensure_mis_indexes
# -----------------------------
# Global Settings / Flags
# -----------------------------
DATA_DIR = "data"
SCHEMA_FILE = "olist_schema_registry.yaml"
DB_DIR = "/data" # persistent volume
DB_PATH = os.path.join(DB_DIR, "olist.sqlite")
def _schema_fingerprint(registry: dict) -> str:
try:
parts = []
for t in sorted(registry.keys()):
cols = ",".join(sorted(registry[t]["columns"].keys()))
parts.append(f"{t}({cols})")
return ";".join(parts)
except Exception:
return "unknown"
class RAGStore:
def __init__(self):
self.backend = RAG_BACKEND
self.chroma = None
self.col = None
# Lazy init
def _ensure(self):
if self.backend == "CHROMA":
if chromadb is None:
raise RuntimeError("chromadb not installed")
os.makedirs(RAG_DIR, exist_ok=True)
# Ensure model caches are writable to avoid '/.cache' permission errors
os.environ.setdefault('HOME', '/data')
os.environ.setdefault('XDG_CACHE_HOME', '/data/.cache')
os.environ.setdefault('HF_HOME', '/data/.cache')
os.environ.setdefault('HUGGINGFACE_HUB_CACHE', '/data/.cache')
os.environ.setdefault('TRANSFORMERS_CACHE', '/data/.cache')
os.environ.setdefault('SENTENCE_TRANSFORMERS_HOME', '/data/.cache')
try:
os.makedirs(os.environ['XDG_CACHE_HOME'], exist_ok=True)
except Exception:
pass
client = chromadb.PersistentClient(path=RAG_DIR)
# Use OpenAI embeddings if available, else default (may be slow)
ef = None
try:
if cef is not None and os.getenv("OPENAI_API_KEY"):
ef = cef.OpenAIEmbeddingFunction(api_key=os.getenv("OPENAI_API_KEY"), model_name=OPENAI_EMBED_MODEL)
except Exception:
ef = None
self.col = client.get_or_create_collection(RAG_COLLECTION, embedding_function=ef)
self.chroma = client
logging.info("[ex] count=%s", col.count())
res = col.get(
where={"tag": "exemplar"}, # or: where={"tags": {"$contains": "exemplar"}}
include=["metadatas", "documents", "ids"],
)
logging.info("[ex] tagged_exemplars ids=%s", res.get("ids", [])[:5])
else:
# Simple in-memory TFIDF fallback
from collections import defaultdict
self.docs = {} # id -> text
self.meta = {} # id -> dict
self._tf = defaultdict(dict)
def upsert_exemplar(self, ex_id: str, text: str, metadata: dict):
self._ensure()
if self.backend == "CHROMA":
self.col.upsert(ids=[ex_id], documents=[text], metadatas=[metadata])
else:
# naive store; search is linear cosine over TfidfVectorizer on demand
self.docs[ex_id] = text
self.meta[ex_id] = metadata
def query(self, text: str, k: int = 3, where: dict | None = None):
self._ensure()
if self.backend == "CHROMA":
where = where or {}
res = self.col.query(query_texts=[text], n_results=k, where=where)
out = []
for i in range(len(res.get("ids",[[]])[0])):
out.append({
"id": res["ids"][0][i],
"text": res["documents"][0][i],
"metadata": res["metadatas"][0][i]
})
return out
else:
# simple cosine via scikit-learn if available; else return best substring matches
try:
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
ids = list(self.docs.keys())
docs = [self.docs[i] for i in ids]
vec = TfidfVectorizer().fit_transform([text] + docs)
sims = cosine_similarity(vec[0:1], vec[1:]).ravel()
order = sims.argsort()[::-1][:k]
return [{"id": ids[i], "text": docs[i], "metadata": self.meta[ids[i]]} for i in order]
except Exception:
# fallback: naive substring rank
items = [(i, self.docs[i]) for i in self.docs]
items.sort(key=lambda p: p[1].count(text[:10]), reverse=True)
return [{"id": i, "text": t, "metadata": self.meta[i]} for i,t in items[:k]]
RAG = RAGStore()
def canonicalize_sql(sql: str) -> str:
try:
if sqlglot is None:
return sql.strip()
return sqlglot.transpile(sql, read="sqlite", write="sqlite")[0]
except Exception:
return sql.strip()
def exemplar_text(intent: str, question: str, schema_fp: str, plan: str, sql: str) -> str:
plan_short = (plan or "").strip()
if len(plan_short) > 400:
plan_short = plan_short[:400] + "…"
sql_canon = canonicalize_sql(sql or "")
return f"[INTENT]={intent}\nQUESTION={question}\nSCHEMA={schema_fp}\nPLAN={plan_short}\nSQL={sql_canon}"
LLM_CHOICE = os.getenv("LLM_CHOICE", "OPENAI")
REVENUE_USER_MODE = os.getenv("REVENUE_USER_MODE", "WARN")
SELLER_REVENUE_USER_MODE = os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK")
PRODUCT_REVENUE_USER_MODE = os.getenv("PRODUCT_REVENUE_USER_MODE", "BLOCK")
PAYMENTS_USER_MODE = os.getenv("PAYMENTS_USER_MODE", "SCOPE").upper()
# Feedback enforcement mode: OPTIONAL | SOFT_REQUIRED | HARD_REQUIRED
FEEDBACK_MODE = os.getenv("FEEDBACK_MODE", "SOFT_REQUIRED").upper()
DB_TIMEOUT = int(os.getenv("DB_TIMEOUT", "30"))
BUSY_TIMEOUT_MS = int(os.getenv("BUSY_TIMEOUT_MS", "5000"))
SQL_TIMEOUT = float(os.getenv("SQL_TIMEOUT", "30")) # default 30s
NON_SQL_ROUTER = os.getenv("NON_SQL_ROUTER", "ON").upper() == "ON"
# --- Tracing & timeouts ---
TRACE_LOGGING = os.getenv("TRACE_LOGGING", "1") == "1"
SQL_HARD_TIMEOUT_SEC = float(os.getenv("SQL_HARD_TIMEOUT_SEC", "15"))
SQL_VM_BUDGET = int(os.getenv("SQL_VM_BUDGET", "4000000"))
SQL_PROGRESS_NSTEPS = int(os.getenv("SQL_PROGRESS_NSTEPS", "1000"))
# --- Rate limiting (role-aware) ---
RATE_LIMIT_WINDOW_SEC = int(os.getenv("RATE_LIMIT_WINDOW_SEC", "60"))
RATE_LIMIT_MAX_REQUESTS_USER = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_USER", "8"))
RATE_LIMIT_MAX_REQUESTS_ADMIN = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_ADMIN", "30"))
# --- Visibility toggle for detailed sections ---
DISPLAY_DETAIL = os.getenv("DISPLAY_DETAIL", "YES").strip().upper()
SHOW_DETAILS = (DISPLAY_DETAIL != "NO") # True unless explicitly set to NO
# UI brand flag
USE_BRANDING = True
DEBUG_UI = os.getenv("DEBUG_UI", "0") == "1"
LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO").upper()
def setup_logging():
# Root logger -> stdout once
root = logging.getLogger()
if not root.handlers:
h = logging.StreamHandler(sys.stdout)
h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
root.addHandler(h)
root.setLevel(getattr(logging, LOG_LEVEL, logging.INFO))
# Quiet very chatty libs
logging.getLogger("httpx").setLevel(logging.WARNING)
logging.getLogger("urllib3").setLevel(logging.WARNING)
return root
root_logger = setup_logging()
def _ensure_logger(name: str, level=logging.INFO):
lg = logging.getLogger(name)
lg.setLevel(level)
# Attach its own handler to guarantee visibility (and avoid relying on propagation)
if not lg.handlers:
h = logging.StreamHandler(sys.stdout)
h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
lg.addHandler(h)
# When a named logger has its own handler, set propagate False to avoid duplicates
lg.propagate = False
return lg
logger = _ensure_logger("olist", getattr(logging, LOG_LEVEL, logging.INFO))
fb_logger = _ensure_logger("feedback", getattr(logging, LOG_LEVEL, logging.INFO))
# --- feedback instrumentation helper (added) ---
try:
_FB_DIAG_ENABLED # type: ignore
except NameError:
_FB_DIAG_ENABLED = True
def _log_feedback_event(phase: str, **fields):
try:
import json, logging
lg = globals().get("fb_logger") or logging.getLogger("feedback")
lg.info("[fb-diag] %s %s", phase, json.dumps(fields, ensure_ascii=False))
except Exception:
# never crash the app due to logging
pass
# --- end helper ---
logger.info("olist logger ready (level=%s)", LOG_LEVEL)
fb_logger.info("feedback logger ready (level=%s)", LOG_LEVEL)
def dbg(msg: str):
if DEBUG_UI:
st.sidebar.write(f"🧪 {msg}")
# ---- Hugging Face cache fix ----
HF_CACHE_BASE = os.getenv("HF_CACHE_BASE", "/data/hf-cache")
def _init_hf_cache():
import pathlib, time, os
base = pathlib.Path(HF_CACHE_BASE)
hub = base / "hub"
trsf = base / "transformers"
for d in (base, hub, trsf):
d.mkdir(parents=True, exist_ok=True)
# ensure transformers uses /data (not /.cache)
os.environ.setdefault("HF_HOME", str(base))
os.environ.setdefault("HF_HUB_CACHE", str(hub))
os.environ.setdefault("TRANSFORMERS_CACHE", str(trsf))
os.environ.setdefault("HF_HUB_DISABLE_SYMLINKS_WARNING", "1")
# Optional: clear stale lock files (>20 min old)
now = time.time()
for root, _, files in os.walk(str(base)):
for f in files:
if f.endswith(".lock"):
p = os.path.join(root, f)
try:
if now - os.path.getmtime(p) > 1200: # 20 min
os.remove(p)
except Exception:
pass
_init_hf_cache()
# -----------------------------
# Scoped UI helpers (safe & minimal)
# -----------------------------
def _inject_brand_css():
"""Loads assets/brand.css if present; otherwise falls back to embedded styles."""
import pathlib
css_path = pathlib.Path("assets/brand.css")
if css_path.exists():
st.markdown(f"<style>{css_path.read_text(encoding='utf-8')}</style>", unsafe_allow_html=True)
else:
st.markdown("""
<style>
.oa-banner{
border-radius:14px; padding:14px 16px; color:#fff;
background:linear-gradient(90deg,#2563eb,#4f8df6);
display:flex; align-items:center; gap:12px;
box-shadow:0 8px 24px rgba(37,99,235,.18); margin-bottom:12px;
}
.oa-logo{ width:36px;height:36px;border-radius:10px; background:rgba(255,255,255,.16); display:grid; place-items:center; font-weight:800; }
.oa-title{ font-weight:700; font-size:18px; line-height:1.1; margin:0; }
.oa-sub{ opacity:.95; font-size:13px; margin-top:2px; }
.oa-chips{ margin-left:auto; display:flex; gap:8px; align-items:center; flex-wrap:wrap; }
.oa-chip{ background:rgba(255,255,255,.16); padding:6px 10px; border-radius:999px; font-size:12px; backdrop-filter: blur(4px); }
.oa-user{ background:#fff; color:#0f172a; }
.oa-card{ background:#fff; border:1px solid #e7e9ee; border-radius:12px; padding:12px; box-shadow:0 6px 18px rgba(2,6,23,.05); }
.oa-card + .oa-card{ margin-top:12px; }
.oa-cardhead{ display:flex; align-items:center; gap:10px; margin-bottom:8px; }
.oa-ttl{ font-weight:700; font-size:15px; }
.oa-pill{ font-size:12px; border:1px solid #e7e9ee; background:#f3f6fb; border-radius:999px; padding:3px 8px; color:#334155; margin-left:auto; }
.oa-sql pre{ font-size:12.5px !important; border-radius:10px !important; }
.oa-note{ background:#eef5ff; border:1px solid #dbe8ff; color:#1e3a8a; border-radius:10px; padding:10px 12px; font-size:13px; }
</style>
""", unsafe_allow_html=True)
def ui_header(auth=None):
user_chip = ""
if auth:
who = auth.get("email","")
role = (auth.get("role") or "").title()
user_chip = f'<span class="oa-chip oa-user">{who} • {role}</span>'
st.markdown(f"""
<div class="oa-banner">
<div class="oa-logo">SA</div>
<div>
<div class="oa-title">SmartAssist</div>
<div class="oa-sub">AI powered BOT</div>
</div>
<div class="oa-chips">
<span class="oa-chip">Enterprise</span>
<span class="oa-chip">Secure</span>
{user_chip}
</div>
</div>
""", unsafe_allow_html=True)
def card_open(title=None, right=None):
st.markdown('<div class="oa-card">', unsafe_allow_html=True)
if title or right:
st.markdown('<div class="oa-cardhead">', unsafe_allow_html=True)
if title: st.markdown(f'<div class="oa-ttl">{title}</div>', unsafe_allow_html=True)
if right: st.markdown(f'<div class="oa-pill">{right}</div>', unsafe_allow_html=True)
st.markdown('</div>', unsafe_allow_html=True)
def card_close():
st.markdown('</div>', unsafe_allow_html=True)
if USE_BRANDING:
_inject_brand_css()
# -----------------------------
# Timing helper
# -----------------------------
@contextmanager
def timed_step(label: str, trace_id: str = ""):
t0 = time.perf_counter()
if TRACE_LOGGING:
logger.info("[trace=%s] ▶ %s: start", trace_id, label)
try:
yield
finally:
dt = time.perf_counter() - t0
if TRACE_LOGGING:
logger.info("[trace=%s] ◀ %s: %.3fs", trace_id, label, dt)
# -----------------------------
# -----------------------------
# Weekly Popular Queries (Top-K)
# -----------------------------
import hashlib
import datetime as dt
import re
from datetime import timezone
def _normalize_query_for_bucket(text: str) -> str:
if not text:
return ""
t = text.lower().strip()
t = re.sub(r"\d+", "<num>", t)
t = re.sub(r"[^a-z0-9 ]+", " ", t)
t = re.sub(r"\s+", " ", t).strip()
return t
def ensure_weekly_popular_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS weekly_popular_queries (
week_start_utc TEXT NOT NULL,
bucket_key TEXT NOT NULL,
display_text TEXT NOT NULL,
intent TEXT,
cnt INTEGER NOT NULL,
plan_blob TEXT,
PRIMARY KEY (week_start_utc, bucket_key)
);
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_wpq_week_cnt ON weekly_popular_queries(week_start_utc, cnt DESC);")
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_created_at ON chat_history(created_at);")
except Exception:
pass
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_role ON chat_history(role);")
except Exception:
pass
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_intent ON chat_history(intent);")
except Exception:
pass
conn.commit()
def _ist_week_start_anchor_utc(now: dt.datetime | None = None) -> str:
if now is None:
now = dt.datetime.now()
ist = dt.timezone(dt.timedelta(hours=5, minutes=30))
n_ist = now.astimezone(ist)
days_back = n_ist.weekday()
start_ist = (n_ist - dt.timedelta(days=days_back)).replace(hour=0, minute=0, second=0, microsecond=0)
start_utc = start_ist.astimezone(dt.timezone.utc)
return start_utc.replace(tzinfo=dt.timezone.utc).isoformat()
def _fetch_current_week_topk_live(conn: sqlite3.Connection, k: int = 5) -> list[dict]:
week_anchor = _ist_week_start_anchor_utc()
rows = conn.execute("""
SELECT user_input, intent, created_at
FROM chat_history
WHERE role='user' AND created_at >= ?
""", (week_anchor,)).fetchall()
buckets = {}
for user_input, intent, _ts in rows:
if not user_input:
continue
if intent and intent.lower() in {"greeting","thanks","help","smalltalk","complaint"}:
continue
norm = _normalize_query_for_bucket(user_input)
key = f"{(intent or 'other').strip()} \u241f {norm}"
d = buckets.get(key)
if d is None:
buckets[key] = {"bucket_key": key, "intent": intent or "other", "norm_q": norm, "display_text": user_input, "cnt": 1}
else:
d["cnt"] += 1
top = sorted(buckets.values(), key=lambda x: (-x["cnt"], x["intent"], x["norm_q"]))[:k]
for t in top:
t["week_start_utc"] = week_anchor
return top
def _materialize_weekly_topk(conn: sqlite3.Connection, k: int = 5) -> list[dict]:
ensure_weekly_popular_schema(conn)
top = _fetch_current_week_topk_live(conn, k=k)
if not top:
return []
week_anchor = top[0]["week_start_utc"]
for row in top:
conn.execute("""
INSERT INTO weekly_popular_queries (week_start_utc, bucket_key, display_text, intent, cnt, plan_blob)
VALUES (?, ?, ?, ?, ?, COALESCE(?, plan_blob))
ON CONFLICT(week_start_utc, bucket_key) DO UPDATE SET
display_text=excluded.display_text,
intent=excluded.intent,
cnt=excluded.cnt
""", (week_anchor, row["bucket_key"], row["display_text"], row["intent"], int(row["cnt"]), None))
conn.commit()
return top
def _load_weekly_topk(conn: sqlite3.Connection, k: int = 5) -> list[dict]:
ensure_weekly_popular_schema(conn)
week_anchor = _ist_week_start_anchor_utc()
cur = conn.execute("""
SELECT display_text, intent, cnt
FROM weekly_popular_queries
WHERE week_start_utc = ?
ORDER BY cnt DESC
LIMIT ?
""", (week_anchor, int(k)))
rows = [{"display_text": r[0], "intent": r[1], "cnt": int(r[2])} for r in cur.fetchall()]
return rows
def show_weekly_topk_chips(conn: sqlite3.Connection, k: int = 5, key_prefix: str = "wkpopular"):
try:
lst = _load_weekly_topk(conn, k=k)
if not lst:
lst = _fetch_current_week_topk_live(conn, k=k)
lst = [{"display_text": r["display_text"], "intent": r["intent"], "cnt": r["cnt"]} for r in lst]
except Exception as e:
lst = []
if isinstance(e, StopException):
raise # don't swallow st.stop()
logger.exception("Unhandled exception in <context>: %s", e)
if not lst:
return
with st.container():
st.markdown(
"""
<style>
.top-enq-title{font-size:1.15rem;font-weight:700;margin:0.25rem 0 0.5rem 0;}
</style>
""",
unsafe_allow_html=True
)
st.markdown('<div class="top-enq-title">Top Enquiries this week</div>', unsafe_allow_html=True)
num = len(lst)
cols = st.columns(num, gap="small")
for i, it in enumerate(lst):
label = f"{it['display_text']} ({it['cnt']})"
if cols[i].button(label, key=f"{key_prefix}_{i}", use_container_width=True):
st.session_state["q_enabled"] = it["display_text"]
# DB helpers / initialization
# -----------------------------
def get_conn():
conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False)
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};")
return conn
REQUIRED_TABLES = {
"orders", "order_items", "order_payments", "order_reviews",
"customers", "products", "sellers", "geolocation",
"product_category_name_translation",
}
def _apply_busy_timeout(conn):
try:
conn.execute(f"PRAGMA busy_timeout={int(SQL_TIMEOUT * 1000)}")
except Exception:
pass
return conn
def _list_tables(conn) -> set:
rows = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
return {r[0] for r in rows}
def _ensure_password_column(conn: sqlite3.Connection):
# add password_hash if missing
cols = {r[1] for r in conn.execute("PRAGMA table_info(users)").fetchall()}
if "password_hash" not in cols:
db_logger.info("[init] adding column users.password_hash")
conn.execute("ALTER TABLE USERS ADD COLUMN password_hash TEXT")
conn.commit()
def _create_user_if_absent(
conn: sqlite3.Connection,
*,
user_id: str,
email: str,
role: str,
password_plain: str,
customer_id: str
):
# does the user already exist?
row = conn.execute("SELECT user_id FROM users WHERE user_id = ? OR email = ? LIMIT 1", (user_id, email)).fetchone()
if row:
db_logger.info("[init] user exists: %s (%s) – not modifying", user_id, email)
return False
# hash the password; bcrypt is salted so the hash will differ per run, but check will pass
pw_hash = bcrypt.hashpw(password_plain.encode("utf-8"), bcrypt.gensalt(rounds=12)).decode("utf-8")
conn.execute(
"""
INSERT INTO users (user_id, email, role, customer_id, password_hash)
VALUES (?, ?, ?, ?, ?)
""",
(user_id, email, role, customer_id, pw_hash),
)
db_logger.info("[init] created %s user_id=%s email=%s", role, user_id, email)
return True
def seed_default_users(conn: sqlite3.Connection):
# Allow overrides via env (so you don't hardcode creds in repo)
admin_email = os.getenv("ADMIN_EMAIL", "admin@example.com")
admin_pass = os.getenv("ADMIN_PASSWORD", "admin123")
user_email = os.getenv("USER_EMAIL", "test@test.com")
user_pass = os.getenv("USER_PASSWORD", "Strongpass#123")
_create_user_if_absent(
conn,
user_id="admin1",
email=admin_email,
role="admin",
password_plain=admin_pass,
customer_id= ''
)
_create_user_if_absent(
conn,
user_id="U_user01",
email=user_email,
role="user",
password_plain=user_pass,
customer_id='06b8999e2fba1a1fbc88172c00ba8bc7'
)
conn.commit()
def ensure_chat_history_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS chat_history (
chat_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
role TEXT NOT NULL,
customer_id TEXT,
user_input TEXT NOT NULL,
llm_plan JSON,
secured_sql JSON,
sentiment TEXT,
intent TEXT,
llm_response_time REAL,
feedback INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
conn.commit()
def ensure_kv_store_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS kv_store (
k TEXT PRIMARY KEY,
v TEXT
);
""")
conn.commit()
def ensure_login_audit_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS login_audit (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
email TEXT,
success INTEGER NOT NULL,
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_created_at ON login_audit(created_at);")
conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_user ON login_audit(user_id);")
conn.commit()
def ensure_performance_indexes(conn: sqlite3.Connection):
stmts = [
"CREATE INDEX IF NOT EXISTS idx_orders_customer_time ON orders(customer_id, order_purchase_timestamp)",
"CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(order_status)",
"CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id)",
"CREATE INDEX IF NOT EXISTS idx_order_items_seller ON order_items(seller_id)",
"CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id)",
"CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)",
"CREATE INDEX IF NOT EXISTS idx_payments_type ON order_payments(payment_type)",
"CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)",
"CREATE INDEX IF NOT EXISTS idx_reviews_score ON order_reviews(review_score)",
"CREATE INDEX IF NOT EXISTS idx_products_category ON products(product_category_name)",
"CREATE INDEX IF NOT EXISTS idx_sellers_city ON sellers(seller_city)",
"CREATE INDEX IF NOT EXISTS idx_sellers_state ON sellers(seller_state)",
"CREATE INDEX IF NOT EXISTS idx_geo_zip ON geolocation(geolocation_zip_code_prefix)",
"CREATE INDEX IF NOT EXISTS idx_sessions_user ON user_sessions(user_id)",
"CREATE INDEX IF NOT EXISTS idx_chat_user_time ON chat_history(user_id, created_at)",
"CREATE INDEX IF NOT EXISTS idx_chat_feedback ON chat_history(feedback)",
"CREATE INDEX IF NOT EXISTS idx_rag_docs_coll ON rag_docs(collection)",
"CREATE INDEX IF NOT EXISTS idx_rag_chunks_doc ON rag_chunks(doc_id)",
"CREATE INDEX IF NOT EXISTS idx_rag_chunks_coll ON rag_chunks(collection)",
"CREATE INDEX IF NOT EXISTS idx_rag_log_coll_time ON rag_publish_log(collection, created_at)"
]
logger = globals().get("db_logger") or globals().get("_db_logger")
executed = 0
for s in stmts:
ok, note = _safe_exec(conn, s)
if not ok and logger:
logger.warning("[init] index failed sql=%s err=%s", s, note)
elif ok:
executed += 1
if executed:
conn.commit()
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_prod_cat_trans_name ON product_category_name_translation(product_category_name)")
conn.execute("CREATE INDEX IF NOT EXISTS idx_prod_cat_trans_en ON product_category_name_translation(product_category_name_english)")
except Exception:
pass
def ensure_performance_indexes_old (conn: sqlite3.Connection) -> list[str]:
stmts = [
"CREATE INDEX IF NOT EXISTS idx_orders_order_id ON orders(order_id);",
"CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);",
"CREATE INDEX IF NOT EXISTS idx_orders_customer_order ON orders(customer_id, order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_payments_order ON order_payments(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_reviews_order ON order_reviews(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id);",
"CREATE INDEX IF NOT EXISTS idx_products_product ON products(product_id);",
"CREATE INDEX IF NOT EXISTS idx_sellers_seller ON sellers(seller_id);",
"CREATE INDEX IF NOT EXISTS idx_login_audit_created ON login_audit(created_at);"
]
executed = []
for s in stmts:
conn.execute(s); executed.append(s)
conn.execute("ANALYZE;")
conn.commit()
return executed
def _col_exists(conn, table, col) -> bool:
cur = conn.execute(f"PRAGMA table_info({table})")
return any(r[1] == col for r in cur.fetchall())
def _chat_history_pk_name(conn) -> str | None:
"""Return the primary key column name for chat_history ('id' or 'chat_id')."""
cols = [r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()]
if "id" in cols:
return "id"
if "chat_id" in cols:
return "chat_id"
return None
def ensure_feedback_cols(conn):
# make sure chat_history exists
conn.execute("""
CREATE TABLE IF NOT EXISTS chat_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
role TEXT,
conversation_id TEXT,
input_text TEXT,
response_text TEXT,
sentiment TEXT,
intent TEXT,
secured_sql TEXT,
llm_plan TEXT,
llm_time REAL,
sql_time REAL,
created_at TEXT
)""")
def has(col: str) -> bool:
return any(r[1] == col for r in conn.execute("PRAGMA table_info(chat_history)").fetchall())
changed = False
if not has("feedback_value"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_value INTEGER"); changed = True
if not has("feedback_text"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_text TEXT"); changed = True
if not has("feedback_at"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_at TEXT"); changed = True
if changed:
conn.commit()
def has_feedback_for_conversation(get_conn, *, user_id: int, conversation_id: str) -> bool:
with get_conn() as conn:
cur = conn.execute(
"""
SELECT 1
FROM chat_history
WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL
LIMIT 1
""",
(user_id, conversation_id),
)
row = cur.fetchone()
fb_logger.debug("has_feedback_for_conversation user=%s cid=%s -> %s", user_id, conversation_id, bool(row))
return bool(row)
# at top
SCHEMA_VERSION = 1 # bump when you add migrations
_db_logger = logging.getLogger("olist")
def _table_exists(conn: sqlite3.Connection, name: str) -> bool:
"""Return True if a table exists in this SQLite connection."""
try:
cur = conn.execute(
"SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1",
(name,),
)
return cur.fetchone() is not None
except Exception as e:
try:
_db_logger.warning("[init] _table_exists(%s) failed: %s", name, e)
except Exception:
pass
return False
def _safe_exec(conn: sqlite3.Connection, sql: str) -> tuple[bool, str | None]:
"""
Execute a single SQL statement safely.
Returns (ok, note). Treats 'already exists' as ok (idempotent).
"""
try:
conn.execute(sql)
return True, None
except sqlite3.OperationalError as e:
msg = str(e)
if "already exists" in msg.lower():
return True, "exists"
return False, msg
except Exception as e:
return False, str(e)
# --- end helper block ---
def _ensure_translation_table(conn: sqlite3.Connection) -> None:
"""Create the optional Olist translation table if it doesn't exist."""
try:
conn.execute(
"CREATE TABLE IF NOT EXISTS product_category_name_translation ("
"product_category_name TEXT, "
"product_category_name_english TEXT)"
)
conn.commit()
except Exception:
# best-effort; never block startup on this
pass
def _set_pragmas(conn: sqlite3.Connection) -> None:
# All PRAGMAs must be literals (no ? bindings)
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute(f"PRAGMA busy_timeout={int(SQL_TIMEOUT*1000)};")
conn.execute("PRAGMA temp_store=MEMORY;")
def create_base_tables_if_missing(conn: sqlite3.Connection):
"""
Ensure ALL required base tables exist.
This version does not return early; it creates each table IF NOT EXISTS.
"""
db_logger = globals().get("db_logger") or logging.getLogger("olist")
if db_logger:
db_logger.info("[init] ensuring base tables exist")
conn.executescript("""
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS customers (
customer_id TEXT PRIMARY KEY,
customer_unique_id TEXT,
customer_zip_code_prefix INTEGER,
customer_city TEXT,
customer_state TEXT
);
CREATE TABLE IF NOT EXISTS orders (
order_id TEXT PRIMARY KEY,
customer_id TEXT NOT NULL,
order_status TEXT,
order_purchase_timestamp TEXT,
order_approved_at TEXT,
order_delivered_carrier_date TEXT,
order_delivered_customer_date TEXT,
order_estimated_delivery_date TEXT
);
CREATE TABLE IF NOT EXISTS order_items (
order_id TEXT NOT NULL,
order_item_id INTEGER NOT NULL,
product_id TEXT,
seller_id TEXT,
shipping_limit_date TEXT,
price REAL,
freight_value REAL,
PRIMARY KEY(order_id, order_item_id)
);
CREATE TABLE IF NOT EXISTS order_payments (
order_id TEXT NOT NULL,
payment_sequential INTEGER,
payment_type TEXT,
payment_installments INTEGER,
payment_value REAL
);
CREATE TABLE IF NOT EXISTS order_reviews (
review_id TEXT PRIMARY KEY,
order_id TEXT,
review_score INTEGER,
review_comment_title TEXT,
review_comment_message TEXT,
review_creation_date TEXT,
review_answer_timestamp TEXT
);
CREATE TABLE IF NOT EXISTS products (
product_id TEXT PRIMARY KEY,
product_category_name TEXT,
product_name_length REAL,
product_description_length REAL,
product_photos_qty REAL,
product_weight_g REAL,
product_length_cm REAL,
product_height_cm REAL,
product_width_cm REAL
);
CREATE TABLE IF NOT EXISTS sellers (
seller_id TEXT PRIMARY KEY,
seller_zip_code_prefix INTEGER,
seller_city TEXT,
seller_state TEXT
);
CREATE TABLE IF NOT EXISTS geolocation (
geolocation_zip_code_prefix INTEGER,
geolocation_lat REAL,
geolocation_lng REAL,
geolocation_city TEXT,
geolocation_state TEXT
);
CREATE TABLE IF NOT EXISTS users (
user_id TEXT PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
customer_id TEXT,
role TEXT CHECK(role IN ('user','admin')) NOT NULL
);
CREATE TABLE IF NOT EXISTS user_sessions (
session_id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
last_seen_at TEXT,
FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE TABLE IF NOT EXISTS chat_history (
chat_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
role TEXT NOT NULL,
user_input TEXT,
llm_response TEXT,
explanation_display TEXT,
sql_generated TEXT,
df_rowcount INTEGER,
llm_response_time REAL,
intent TEXT,
sentiment TEXT,
feedback INTEGER,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
customer_id TEXT,
FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE TABLE IF NOT EXISTS rag_docs (
doc_id TEXT PRIMARY KEY,
collection TEXT NOT NULL,
title TEXT,
source_uri TEXT,
meta_json TEXT,
created_by TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(created_by) REFERENCES users(user_id)
);
CREATE TABLE IF NOT EXISTS rag_chunks (
chunk_id TEXT PRIMARY KEY,
doc_id TEXT NOT NULL,
collection TEXT NOT NULL,
chunk_index INTEGER,
text TEXT,
embedding BLOB,
meta_json TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(doc_id) REFERENCES rag_docs(doc_id)
);
CREATE TABLE IF NOT EXISTS rag_publish_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
doc_id TEXT,
collection TEXT NOT NULL,
action TEXT,
actor_user_id TEXT,
msg TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS product_category_name_translation (
product_category_name TEXT,
product_category_name_english TEXT
);
""")
conn.commit()
def initialize_db(quiet: bool = True) -> dict:
#_ensure_translation_table(sqlite3.connect(DB_PATH))
os.makedirs(DB_DIR, exist_ok=True)
created = not os.path.exists(DB_PATH)
actions = []
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_set_pragmas(conn)
row = conn.execute("PRAGMA user_version;").fetchone()
prior_version = int(row[0]) if row and row[0] is not None else 0
create_base_tables_if_missing(conn)
_ensure_password_column(conn)
_ensure_translation_table(conn)
seed_default_users(conn)
ensure_chat_history_schema(conn)
ensure_login_audit_schema(conn)
ensure_performance_indexes(conn)
ensure_feedback_cols(conn)
if prior_version != SCHEMA_VERSION:
conn.execute(f"PRAGMA user_version={int(SCHEMA_VERSION)};")
actions.append(f"set_user_version:{SCHEMA_VERSION}")
conn.commit()
status = {
"created": created,
"prior_version": prior_version,
"new_version": SCHEMA_VERSION,
"actions": actions,
"db_path": DB_PATH,
}
if not quiet:
st.success(f"DB initialized (user_version {prior_version} → {SCHEMA_VERSION})")
return status
@st.cache_resource(show_spinner = False)
def bootstrap_db_once() -> dict:
return initialize_db(quiet = True)
# Call it after functions are defined:
_init_status = bootstrap_db_once()
# Log only once per browser session (not on every rerun)
if "init_logged" not in st.session_state:
logger.info("[init] bootstrap_db_once => %s", _init_status)
st.session_state.init_logged = True
def initialize_db_old (quiet: bool = True) -> bool:
"""
Create/upgrade DB if needed. Returns True if rebuild/migration happened.
Never writes to Streamlit UI; use logging instead.
"""
os.makedirs(DB_DIR, exist_ok=True)
rebuilt = False
# Fast existence & schema check
need_create = not os.path.exists(DB_PATH)
try:
with sqlite3.connect(DB_PATH) as c:
c.execute("PRAGMA journal_mode=WAL;")
c.execute("PRAGMA busy_timeout = 5000;")
# Store schema version in user_version
cur = c.execute("PRAGMA user_version;").fetchone()
current_ver = int(cur[0]) if cur and cur[0] is not None else 0
except Exception as e:
need_create = True
current_ver = 0
if need_create:
# your current CSV → tables loader goes here...
# ensure_chat_history_schema(c), ensure_login_audit_schema(c), etc.
with sqlite3.connect(DB_PATH) as c:
c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,))
rebuilt = True
logger.info("[init] fresh database created")
else:
# migrations if schema version is older
if current_ver < SCHEMA_VERSION:
with sqlite3.connect(DB_PATH) as c:
# run your ALTERs / ensures here
ensure_chat_history_schema(c)
ensure_login_audit_schema(c)
ensure_performance_indexes(c)
ensure_feedback_cols(c)
c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,))
c.commit()
rebuilt = True
logger.info("[init] migrated database from v%s to v%s", current_ver, SCHEMA_VERSION)
else:
# still ensure additive bits without noise
with sqlite3.connect(DB_PATH) as c:
ensure_chat_history_schema(c)
ensure_login_audit_schema(c)
ensure_performance_indexes(c)
ensure_feedback_cols(c)
c.commit()
return rebuilt
# --- Admin data reload helpers ---------------------------------------------
LOAD_ORDER = [
# parents → children (recommended load order)
"geolocation",
"customers",
"sellers",
"products",
"product_category_name_translation",
"orders",
"order_items",
"order_payments",
"order_reviews",
]
def get_table_csv_map() -> dict:
return {
"orders": f"{DATA_DIR}/olist_orders_dataset.csv",
"order_items": f"{DATA_DIR}/olist_order_items_dataset.csv",
"order_payments": f"{DATA_DIR}/olist_order_payments_dataset.csv",
"order_reviews": f"{DATA_DIR}/olist_order_reviews_dataset.csv",
"customers": f"{DATA_DIR}/olist_customers_dataset.csv",
"products": f"{DATA_DIR}/olist_products_dataset.csv",
"sellers": f"{DATA_DIR}/olist_sellers_dataset.csv",
"geolocation": f"{DATA_DIR}/olist_geolocation_dataset.csv",
"product_category_name_translation": f"{DATA_DIR}/product_category_name_translation.csv",
}
def _sorted_by_load_order(tables: list[str]) -> list[str]:
order_index = {t: i for i, t in enumerate(LOAD_ORDER)}
return sorted(tables, key=lambda t: order_index.get(t, 999))
def get_row_counts(conn: sqlite3.Connection, tables: list[str]) -> dict[str, int]:
out = {}
for t in tables:
try:
exists = conn.execute(
"SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1",
(t,),
).fetchone() is not None
if not exists:
out[t] = 0
continue
out[t] = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
except Exception as e:
out[t] = 0
try:
(globals().get('logger') or logging.getLogger('olist')).info("Row count skipped for %s: %s", t, e)
except Exception:
pass
return out
def repopulate_tables_from_csv(tables: list[str], logger=None) -> tuple[bool, str, dict]:
"""
DELETE FROM selected tables, then reload from CSVs.
Returns (ok, message, post_counts). Emits structured logs for each step.
"""
import time, os, sqlite3, pandas as pd, json, logging
lg = logger or (globals().get("_REPOP_LG") or logging.getLogger("olist"))
lg.debug("[reload] ENTER repopulate tables=%s", tables)
start = time.perf_counter()
if not tables:
lg.warning("[reload] No tables selected.")
return False, "No tables selected.", {}
csv_map = get_table_csv_map()
lg.info("[reload] csv_map = %s", json.dumps(csv_map, ensure_ascii=False))
# Partition into known/unknown
requested = list(dict.fromkeys(tables))
known = [t for t in requested if t in csv_map]
unknown = [t for t in requested if t not in csv_map]
if unknown:
lg.warning("[reload] skipping unknown tables (no CSV mapping): %s", unknown)
to_process = _sorted_by_load_order(known)
lg.info("[reload] tables (ordered) => %s", to_process)
timeout = globals().get("SQL_TIMEOUT", 60.0)
summary = {"tables": {}, "duration_sec": None, "errors": []}
try:
with sqlite3.connect(DB_PATH, timeout=timeout) as conn:
try:
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute("PRAGMA foreign_keys=OFF;")
lg.debug("[reload] PRAGMA set ok")
except Exception as e:
lg.warning("[reload] PRAGMA set failed: %s", e)
if not to_process:
summary["duration_sec"] = round(time.perf_counter() - start, 3)
lg.info("[reload] nothing to process (requested=%s, unknown=%s)", requested, unknown)
lg.info("[reload] summary: %s", json.dumps(summary, ensure_ascii=False))
return True, "Nothing to process.", {}
for t in to_process:
t_start = time.perf_counter()
entry = {"csv": None, "deleted_rows": None, "chunks": 0, "rows_appended": 0, "elapsed_sec": None}
summary["tables"][t] = entry
try:
path = csv_map[t]
entry["csv"] = path
if not os.path.exists(path):
msg = f"CSV not found: {path}"
lg.error("[reload] %s", msg)
summary["errors"].append({"table": t, "error": msg})
continue
# DELETE
try:
pre = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
except Exception:
pre = None
if pre is not None:
lg.info("[reload] %s: deleting old rows (pre=%s)", t, pre)
else:
lg.info("[reload] %s: deleting old rows (pre=unknown)", t)
conn.execute(f"DELETE FROM {t};")
try:
post_del = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
except Exception:
post_del = None
entry["deleted_rows"] = pre if pre is not None else "unknown"
if post_del not in (0, None):
lg.warning("[reload] %s: expected 0 after delete, got %s", t, post_del)
# LOAD
lg.info("[reload] %s: loading from %s", t, path)
chunk_idx = 0
total_appended = 0
for chunk in pd.read_csv(path, chunksize=50000):
chunk_idx += 1
entry["chunks"] = chunk_idx
# Products: fix Olist CSV typos
if t == "products":
try:
rename_map = {
"product_name_lenght": "product_name_length",
"product_description_lenght": "product_description_length",
}
cols_lower = {c.lower(): c for c in chunk.columns}
for bad, good in rename_map.items():
if bad in cols_lower:
real = cols_lower[bad]
chunk = chunk.rename(columns={real: good})
lg.debug("[reload] %s chunk#%d: rename shim applied", t, chunk_idx)
except Exception as e:
lg.warning("[reload] %s chunk#%d: rename shim failed: %s", t, chunk_idx, e)
# Translation CSV coercion
if t == "product_category_name_translation":
for col in ["product_category_name", "product_category_name_english"]:
if col in chunk.columns:
try:
chunk[col] = chunk[col].astype(str)
except Exception as e:
lg.warning("[reload] %s chunk#%d: cast %s->str failed: %s", t, chunk_idx, col, e)
try:
chunk.to_sql(t, conn, if_exists="append", index=False)
total_appended += len(chunk)
lg.debug(
"[reload] %s chunk#%d: appended rows=%d (total=%d) cols=%s",
t, chunk_idx, len(chunk), total_appended, list(chunk.columns)
)
except Exception as e:
lg.exception("[reload] %s chunk#%d: to_sql failed", t, chunk_idx)
summary["errors"].append({"table": t, "chunk": chunk_idx, "error": str(e)})
break
try:
conn.commit()
except Exception as e:
lg.exception("[reload] %s: commit failed", t)
summary["errors"].append({"table": t, "error": f"commit failed: {e}"})
try:
final_cnt = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
except Exception as e:
final_cnt = None
lg.warning("[reload] %s: COUNT(*) failed: %s", t, e)
entry["rows_appended"] = total_appended
entry["elapsed_sec"] = round(time.perf_counter() - t_start, 3)
lg.info("[reload] %s: done in %.3fs (appended=%s, final_count=%s)",
t, entry["elapsed_sec"], total_appended, final_cnt)
except Exception as e:
entry["elapsed_sec"] = round(time.perf_counter() - t_start, 3)
lg.exception("[reload] %s: failed at table-level", t)
summary["errors"].append({"table": t, "error": f"{e}"})
try:
conn.execute("PRAGMA foreign_keys=ON;")
except Exception as e:
lg.warning("[reload] foreign_keys=ON failed at end: %s", e)
try:
post_counts = get_row_counts(conn, to_process)
except Exception as e:
lg.warning("[reload] get_row_counts failed: %s", e)
post_counts = {}
summary["duration_sec"] = round(time.perf_counter() - start, 3)
lg.info("[reload] summary: %s", json.dumps(summary, ensure_ascii=False))
msg = "Repopulation completed."
if summary["errors"]:
msg = "Repopulation completed with warnings; check logs."
lg.info("[reload] EXIT ok msg=%s post_counts=%s", msg, post_counts)
return True, msg, post_counts
except Exception as e:
summary["duration_sec"] = round(time.perf_counter() - start, 3)
try:
lg.exception("[reload] fatal failure")
lg.info("[reload] summary at failure: %s", json.dumps(summary, ensure_ascii=False))
except Exception:
pass
lg.info("[reload] EXIT not-ok")
return False, f"Repopulation failed: {e}", {}
def load_schema_registry():
if not os.path.exists(SCHEMA_FILE):
raise FileNotFoundError(f"Missing schema file: {SCHEMA_FILE}")
with open(SCHEMA_FILE, "r") as f:
return yaml.safe_load(f)
# --- Feedback confirmation persistence helper ---
def _feedback_confirmed(chat_id: int) -> bool:
try:
import streamlit as st
return bool(st.session_state.get(f"FB_CONFIRMED_{chat_id}"))
except Exception:
return False
def _set_feedback_confirmed(chat_id: int, val: bool = True) -> None:
try:
import streamlit as st
st.session_state[f"FB_CONFIRMED_{chat_id}"] = val
except Exception:
pass
def render_feedback_controls(chat_id: int, key_ns: str | None = None, src_lang: str = "en"):
import streamlit as st
if not chat_id:
return
# --- unique namespace for widget keys (prevents DuplicateWidgetID) ---
try:
if key_ns is None:
st.session_state.setdefault("__fb_ns_counter__", 0)
st.session_state["__fb_ns_counter__"] += 1
key_ns = f"scope{st.session_state['__fb_ns_counter__']}"
except Exception:
if key_ns is None:
key_ns = "scope0"
# --- fetch any prior feedback (1 = up, 0 = down) ---
try:
existing = get_chat_feedback(chat_id)
except Exception:
existing = None # fail open: still allow user to click; we'll log fallback
# session flag to persist “submitted” state across reruns
submitted_flag = f"fb_submitted_{chat_id}"
already_submitted = bool(st.session_state.get(submitted_flag)) or (existing is not None)
st.caption("Was this response helpful?")
# show persistent confirmation (if we had previous feedback or this session submitted)
if existing is not None:
st.info("Feedback recorded: " + ("👍 Helpful" if existing == 1 else "👎 Not helpful"))
elif already_submitted:
st.success("Thanks for the feedback! Recorded.")
# If we already have feedback, don’t draw interactive controls again
if already_submitted:
return
# --- use a form so the click & side effects occur together cleanly ---
form_key = f"fb_form_{key_ns}_{chat_id}"
with st.form(form_key, clear_on_submit=True):
c1, c2 = st.columns(2)
with c1:
up_clicked = st.form_submit_button("👍 Yes", use_container_width=False)
with c2:
dn_clicked = st.form_submit_button("👎 No", use_container_width=False)
if up_clicked or dn_clicked:
val = 1 if up_clicked else 0
try:
# call whichever saver exists in your codebase
if "insert_chat_feedback" in globals():
_log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value)
insert_chat_feedback(chat_id, val)
elif "save_chat_feedback" in globals():
_log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value)
save_chat_feedback(chat_id, val)
elif "upsert_chat_feedback" in globals():
_log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value)
upsert_chat_feedback(chat_id, val)
else:
# fallback to logging so we don’t crash in dev
if "fb_logger" in globals():
fb_logger.info(f"[ui] feedback chat_id={chat_id} value={val}")
st.session_state[submitted_flag] = True
st.success("Thanks for the feedback! Recorded as " + ("👍 Helpful." if val == 1 else "👎 Not helpful."))
# Let Streamlit re-run naturally; no st.rerun() needed here.
except Exception as e:
st.error("Could not save feedback.")
try:
st.exception(e)
except Exception:
pass
def insert_chat_history(user_id, role, customer_id, user_input,
llm_plan, secured_sql, sentiment, intent, llm_time) -> int:
with sqlite3.connect(DB_PATH) as conn:
cur = conn.execute("""
INSERT INTO chat_history
(user_id, role, customer_id, user_input, llm_plan, secured_sql,
sentiment, intent, llm_response_time, feedback)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)
""", (
user_id, role, customer_id, user_input,
json.dumps(llm_plan), json.dumps(secured_sql),
sentiment, intent, llm_time
))
chat_id = cur.lastrowid
conn.commit()
return int(chat_id)
@st.cache_data(ttl=30, show_spinner=False)
def get_recent_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame:
with get_conn() as conn:
cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()}
# Detect PK and key columns
pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None)
created = "created_at" if "created_at" in cols else ( "ts" if "ts" in cols else None )
inp_col = "user_input" if "user_input" in cols else ( "input_text" if "input_text" in cols else None )
secured = "secured_sql" if "secured_sql" in cols else None
llm_plan = "llm_plan" if "llm_plan" in cols else None
feedback = "feedback_value" if "feedback_value" in cols else ( "feedback" if "feedback" in cols else None )
if not pk:
raise RuntimeError("chat_history has no id/chat_id column")
# Build SELECT list, substituting NULL for missing columns
select_parts = [f"{pk} AS chat_id"]
select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"]
select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"]
select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"]
select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"]
select_parts += [f"{feedback} AS feedback"] if feedback else ["NULL AS feedback"]
order_clause = f" ORDER BY datetime({created}) DESC" if created else ""
sql = f"""
SELECT {", ".join(select_parts)}
FROM chat_history
WHERE user_id = ?
{order_clause}
LIMIT ?
"""
return pd.read_sql_query(sql, conn, params=(user_id, limit))
@st.cache_data(ttl=30, show_spinner=False)
def get_unrated_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame:
with get_conn() as conn:
cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()}
pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None)
created = "created_at" if "created_at" in cols else None
inp_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else None)
secured = "secured_sql" if "secured_sql" in cols else None
llm_plan = "llm_plan" if "llm_plan" in cols else None
fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None)
if not pk:
raise RuntimeError("chat_history has no id/chat_id column")
select_parts = [f"{pk} AS chat_id"]
select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"]
select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"]
select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"]
select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"]
select_parts += [f"{fb_col} AS feedback"] if fb_col else ["NULL AS feedback"]
where_fb = f" AND {fb_col} IS NULL" if fb_col else ""
order_clause = f" ORDER BY datetime({created}) DESC" if created else ""
sql = f"""
SELECT {", ".join(select_parts)}
FROM chat_history
WHERE user_id = ? {where_fb}
{order_clause}
LIMIT ?
"""
return pd.read_sql_query(sql, conn, params=(user_id, limit))
@st.cache_data(ttl=30)
def get_recent_chats_admin(limit: int = 200, days: int | None = None, user_id: str | None = None) -> pd.DataFrame:
"""
Admin view: optionally filter by user_id; otherwise show all.
"""
sql = """
SELECT chat_id, user_id, role, customer_id, created_at,
intent, sentiment, feedback, user_input, llm_response_time
FROM chat_history
WHERE 1=1
"""
params = []
if user_id:
sql += " AND user_id = ?"
params.append(user_id)
if days is not None:
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
sql += " AND created_at >= ?"
params.append(since)
sql += " ORDER BY datetime(created_at) DESC LIMIT ?"
params.append(int(limit))
with get_conn() as conn:
df = pd.read_sql_query(sql, conn, params=params)
return df
import re
def detect_non_sql_intent(q: str):
_log_feedback_event('get_recent_chats_for_user.begin', locals=locals().copy())
try:
src = _detect_lang(q)
except Exception:
src = "en"
import streamlit as st
st.session_state["src_lang"] = src_lang # current turn
st.session_state["last_detected_lang"] = src_lang # optional alias
text_variants = []
q0 = (q or "").strip()
qlc = q0.lower()
text_variants.append(qlc)
# Translate non‑English to English for pattern matching
if src != "en":
try:
q_en = _tx_to_en(q0, src)
if q_en and isinstance(q_en, str):
text_variants.append(q_en.strip().lower())
except Exception as e:
logger.debug("detect_non_sql_intent: _tx_to_en failed: %s", e)
# Patterns
pat_greet = re.compile(r"\b(hi|hello|hey|greetings|namaste|namaskar|नमस्ते|नमस्कार|सलाम|\u0939\u093e\u0932\s*चाल|हालचाल|हाल चाल|आप कैसे हैं|क्या हाल है)\b", re.I)
pat_thanks = re.compile(r"\b(thanks|thank you|shukriya|धन्यवाद|शुक्रिया)\b", re.I)
pat_smalltalk = re.compile(r"\b(how\s+are\s+you|what\'?s\s+up|how\s+is\s+it\s+going|कैसे\s*हैं|क्या\s*हाल\s*है|हालचाल|हाल\s*चाल)\b", re.I)
pat_complaint = re.compile(r"\b(complain|not working|issue|problem|error|फिर\s*से\s*नहीं|काम\s*नहीं|समस्या|त्रुटि)\b", re.I)
for tv in text_variants:
if not tv:
continue
if pat_greet.search(tv):
logger.info("Matching label is : greeting")
_log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy())
return "greeting"
if pat_thanks.search(tv):
logger.info("Matching label is : thanks")
_log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy())
return "thanks"
if pat_smalltalk.search(tv):
logger.info("Matching label is : smalltalk")
_log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy())
return "smalltalk"
if pat_complaint.search(tv):
logger.info("Matching label is : complaint")
_log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy())
return "complaint"
_log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy())
return None
def classify_destructive_intent_with_llm(llm, query: str) -> bool:
# Normalize if the planner context was prefixed (case-insensitive)
q = (query or "")
m = re.search(r'question:\s*(.*)', q, flags=re.I | re.S) # grab everything after QUESTION:
if m:
q = m.group(1).strip()
sys_msg = (
"You are a strict binary classifier.\n"
"Return exactly one token: Destructive or Non-Destructive."
)
user_msg = (
"Classify the intent of this user query as either Destructive or Non-Destructive.\n"
f'Query: "{q}"\n'
"Answer with only the single word.\n"
"Examples of Destructive intent are:\n"
"- delete data\n"
"- delete my data\n"
"- erase data\n"
"- erase my data\n"
"- erase data from table\n"
"- drop data\n"
"- drop table\n"
"- modify data\n"
"- update data\n"
"- update table\n"
"- cancel my order\n"
"- void the order"
)
try:
out = llm.chat([
{"role": "system", "content": sys_msg},
{"role": "user", "content": user_msg},
])
# Be robust to different client return types
if isinstance(out, dict):
out = out.get("content") or out.get("text") or ""
label = (out or "").strip().splitlines()[-1].strip().lower().strip(" .:>")
# Tolerant parsing
if "non-destructive" in label or "non destructive" in label:
return False
if "destructive" in label and "non" not in label:
return True
except Exception as e:
if isinstance(e, StopException):
raise # don't swallow st.stop()
logger.exception("Unhandled exception in <context>: %s", e)
pass
# Fallback if the LLM path fails or is ambiguous
return _intent_destructive(query)
def _intent_destructive(text: str) -> bool:
logger.info("inside _intent_destructive -> Text received is: %s", text)
t = text.lower()
# If the string contains our planner context, keep only the user question
if 'question:' in t:
t = t.split('question:', 1)[1]
logger.info("inside _intent_destructive -> value of t is : %s", t)
t = re.sub(r'\s+', ' ', t).strip()
logger.info("inside _intent_destructive -> Final value of t is : %s", t)
logger.info("inside _intent_destructive -> returning value : %s", bool(_DESTRUCTIVE_PAT.search(t)))
return bool(_DESTRUCTIVE_PAT.search(t))
def respond_non_sql(label: str, src_lang: str | None = "en") -> str:
msgs = {
"greeting": ("Hi! 👋 I can answer questions about your Olist data. "
"Try: “list my last 10 orders” or “top categories by GMV”."),
"thanks": "You’re welcome! If you need examples, ask “help”.",
"smalltalk":"I’m here for analytics on the Olist dataset. "
"Ask me to list, aggregate, compare, or trend your data.",
"complaint":"Sorry to hear that. I’m an analytics bot and don’t have access to customer support systems. "
"I can help you check your order history, delivery dates, or reviews. For service issues, "
"please contact support.",
"help": ("I can generate SQL and answer questions like:\n"
"• last 10 orders\n• payments for my orders in July\n• sellers with highest GMV\n"
"• delivery time trend by month\n• top product categories for me\n"),
}
en = msgs.get(label, msgs["help"])
# If you have translation helpers in your code:
try:
if src_lang and src_lang != "en":
return _tx_from_en(en, src_lang) # your existing translator
except Exception:
pass
return en
def classify_sentiment_and_intent(llm, text: str):
CANONICAL_INTENTS = [
"list_sellers","list_products","my_orders","my_payments",
"seller_revenue","product_revenue","shipment_status",
"reviews_summary","top_categories","help_greeting","other"
]
examples = [
{"input":"pls list all sellers available on the platform","sentiment":"Neutral","intent":"list_sellers"},
{"input":"Need the list of sellers located in state SP","sentiment":"Neutral","intent":"list_sellers"},
{"input":"show all my payments in 2017","sentiment":"Neutral","intent":"my_payments"},
{"input":"track my last order delivery status","sentiment":"Neutral","intent":"shipment_status"},
{"input":"hello, what can you do?","sentiment":"Positive","intent":"help_greeting"},
]
sys = f"""You are a strict classifier.
Choose intent from this enum only:
{CANONICAL_INTENTS}
If a request lists sellers (even filtered by state/city), use "list_sellers".
Return ONLY JSON."""
out = llm.chat([
{"role":"system","content":sys},
{"role":"user","content":json.dumps({"task":"classify","text":text,"examples":examples})}
])
try:
parsed = json.loads(out)
except Exception:
return "Neutral","other"
sentiment = parsed.get("sentiment","Neutral")
intent = parsed.get("intent","other")
if sentiment not in ("Positive","Negative","Neutral"):
sentiment = "Neutral"
if intent not in CANONICAL_INTENTS:
intent = "other"
return sentiment, intent
# -----------------------------
# Auth helpers / Simple login
# -----------------------------
def hash_password(plain: str) -> str:
return bcrypt.hashpw(plain.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
def verify_password(plain: str, hashed: str) -> bool:
try:
return bcrypt.checkpw(plain.encode("utf-8"), hashed.encode("utf-8"))
except Exception:
return False
def get_user_by_email(email: str):
q = "SELECT user_id, email, password_hash, customer_id, role FROM users WHERE email = ?"
with sqlite3.connect(DB_PATH) as conn:
row = conn.execute(q, (email,)).fetchone()
if not row: return None
keys = ["user_id", "email", "password_hash", "customer_id", "role"]
return dict(zip(keys, row))
def create_user(email: str, plain_pw: str, customer_id: str, role: str = "user"):
hashed = hash_password(plain_pw)
user_id = f"U_{customer_id[:8]}" if customer_id else f"A_{int(time.time())}"
with sqlite3.connect(DB_PATH) as conn:
conn.execute("""
INSERT OR REPLACE INTO users (user_id, email, password_hash, customer_id, role)
VALUES (?, ?, ?, ?, ?)
""", (user_id, email, hashed, customer_id, role))
conn.commit()
def log_login_attempt(email: str | None, user_id: str | None, success: bool, reason: str | None):
try:
with sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) as conn:
conn.execute(
"INSERT INTO login_audit (user_id, email, success, reason) VALUES (?, ?, ?, ?)",
(user_id, (email or "").strip() or None, 1 if success else 0, reason or None)
)
conn.commit()
except Exception as e:
logger.warning("login audit write failed: %s", e)
if isinstance(e, StopException):
raise # don't swallow st.stop()
logger.exception("Unhandled exception in <context>: %s", e)
def simple_login_screen():
# Ensure captcha tuple exists
if "captcha_simple" not in st.session_state or not isinstance(st.session_state.captcha_simple, tuple) or len(st.session_state.captcha_simple) != 2:
try:
st.session_state.captcha_simple = _make_captcha()
except Exception:
import random
a, b = random.randint(1, 9), random.randint(1, 9)
st.session_state.captcha_simple = (a, b)
"""Simple login with inline CAPTCHA; styled by branding banner."""
import random
if "captcha_simple" not in st.session_state:
st.session_state.captcha_simple = (random.randint(10, 99), random.randint(1, 9))
a, b = st.session_state.captcha_simple
st.subheader("Sign in")
with st.form("login_simple", clear_on_submit=False):
email = st.text_input("Email", placeholder="you@company.com")
pwd = st.text_input("Password", type="password", placeholder="••••••••")
cap = st.text_input(f"Human check: {a} + {b} = ?")
submit = st.form_submit_button("Sign in", use_container_width=True)
if submit:
try:
if int((cap or "").strip()) != (a + b):
log_login_attempt(email, None, False, "captcha_failed")
st.error("CAPTCHA failed. Please try again.")
st.session_state.captcha_simple = None
return None
except Exception:
log_login_attempt(email, None, False, "captcha_invalid")
st.error("Please answer the CAPTCHA.")
return None
user = get_user_by_email((email or "").strip())
if not user:
log_login_attempt(email, None, False, "no_such_user")
st.error("No such user.")
return None
if not verify_password(pwd or "", user["password_hash"]):
log_login_attempt(email, user["user_id"], False, "wrong_password")
st.error("Incorrect password.")
return None
log_login_attempt(email, user["user_id"], True, "success")
st.success("Signed in successfully.")
return {
"user_id": user["user_id"],
"email": user["email"],
"role": user["role"],
"customer_id": user.get("customer_id"),
"remember": False,
"ts": time.time()
}
st.info("Enter your email/password above to sign in.")
return None
# -----------------------------
# LLM plan normalization / SQL extraction (provider-aware)
# -----------------------------
import json as _jsonmod, re as _re_mod
_SQL_FENCE_RE = _re_mod.compile(r"```(?:sql)?\s*(.*?)```", _re_mod.I | _re_mod.S)
_FIRST_SQL_RE = _re_mod.compile(r"(?is)\b(SELECT|WITH)\b.+?;")
def _normalize_sql_statement(stmt: str) -> str:
if not stmt:
return ""
s = stmt.strip()
if not s.endswith(";"):
s += ";"
return s
def _extract_sql_from_text(raw: str) -> str | None:
"""Extract first plausible SQL statement from raw text (fenced or free)."""
if not raw:
return None
s = raw.strip()
# fenced
blocks = _SQL_FENCE_RE.findall(s)
if blocks:
for b in blocks:
bb = (b or "").strip()
if _re_mod.match(r"(?is)^\s*(select|with)\b", bb):
return _normalize_sql_statement(bb)
# free-text first SQL-ish
m = _FIRST_SQL_RE.search(s)
if m:
semi = s.find(";", m.start())
stmt = s[m.start(): semi+1] if semi != -1 else s[m.start():].strip() + ";"
return _normalize_sql_statement(stmt)
return None
def _unwrap_sql_from_json(raw: str) -> str | None:
"""If raw is a JSON object with 'queries', return the first query string."""
try:
obj = _jsonmod.loads(raw)
except Exception:
return None
q = obj.get("queries")
if isinstance(q, list) and q and isinstance(q[0], str):
return _normalize_sql_statement(q[0])
if isinstance(q, str) and q.strip():
return _normalize_sql_statement(q)
return None
def _maybe_extract_sql(sql: str) -> str:
"""Accepts raw provider output or plain SQL and returns a clean SQL string.
Handles Anthropic-style JSON/text, fenced code, or plain SELECT/WITH.
"""
if not isinstance(sql, str):
return sql
s = sql.strip()
# JSON case
if s.startswith("{"):
unwrapped = _unwrap_sql_from_json(s)
if unwrapped:
return unwrapped
# Fenced / free text
if not _re_mod.match(r"(?is)^\s*(select|with|pragma|explain)\b", s):
extracted = _extract_sql_from_text(s)
if extracted:
return extracted
# As-is (ensure semicolon for selects)
return _normalize_sql_statement(s) if _re_mod.match(r"(?is)^\s*(select|with)\b", s) else s
# Provider-specific plan parsers (adapter pattern) — optional use where you build 'plan'
class Plan(dict):
pass
def parse_llm_openai(raw: str) -> Plan:
p = Plan(queries=[], explanation="", explanation_display="", raw=(raw or ""))
s = (raw or "").strip()
if s.startswith("{"):
try:
obj = _jsonmod.loads(s)
q = obj.get("queries") or []
p["queries"] = q if isinstance(q, list) else [q]
p["explanation"] = obj.get("explanation","")
p["explanation_display"] = obj.get("explanation_display", p["explanation"])
return p
except Exception as e:
if isinstance(e, StopException):
raise # don't swallow st.stop()
logger.exception("Unhandled exception in <context>: %s", e)
pass
# fallback via extractor
maybe = _maybe_extract_sql(s)
if maybe and _re_mod.match(r"(?is)^\s*(select|with)\b", maybe):
p["queries"] = [maybe]
p["explanation"] = s
p["explanation_display"] = p["explanation"]
return p
def parse_llm_anthropic(raw: str) -> Plan:
p = Plan(queries=[], explanation="", explanation_display="", raw=(raw or ""))
s = (raw or "").strip()
if s.startswith("{"):
u = _unwrap_sql_from_json(s)
if u:
p["queries"] = [u]
try:
obj = _jsonmod.loads(s)
p["explanation"] = obj.get("explanation","")
p["explanation_display"] = obj.get("explanation_display", p["explanation"])
except Exception:
p["explanation"] = s
p["explanation_display"] = s
return p
# fences / free text
u = _extract_sql_from_text(s)
if u:
p["queries"] = [u]
p["explanation"] = _SQL_FENCE_RE.sub("", s).strip() if _SQL_FENCE_RE.search(s) else s
p["explanation_display"] = p["explanation"]
return p
def parse_llm_plan(raw: str, provider: str | None = None) -> Plan:
prov = (provider or "").lower()
if "anthropic" in prov:
return parse_llm_anthropic(raw)
return parse_llm_openai(raw)
# -----------------------------
# SQL execution (timeouts)
# -----------------------------
def _looks_like_select(sql: str) -> bool:
s = "\n".join([ln for ln in sql.strip().splitlines() if not ln.strip().startswith("--")]).lstrip()
head = s[:7].upper()
return head.startswith("SELECT") or head.startswith("EXPLAIN") or head.startswith("PRAGMA")
class SqlTimeoutError(Exception):
pass
def run_sql_generic(sql_or_tuple):
# Accept "sql" or ("sql", params)
if isinstance(sql_or_tuple, (tuple, list)):
sql = sql_or_tuple[0]
params = sql_or_tuple[1] if len(sql_or_tuple) > 1 else None
else:
sql = sql_or_tuple
params = None
# --- Robust SQL extraction from provider output (JSON/fenced) ---
if isinstance(sql, str):
sql = _maybe_extract_sql(sql)
start = time.perf_counter()
is_select = _looks_like_select(sql)
conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False)
try:
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};")
vm_steps = 0
def _progress():
nonlocal vm_steps
vm_steps += SQL_PROGRESS_NSTEPS
if (time.perf_counter() - start) > SQL_HARD_TIMEOUT_SEC:
return 1
if vm_steps >= SQL_VM_BUDGET:
return 1
return 0
conn.set_progress_handler(_progress, SQL_PROGRESS_NSTEPS)
if is_select:
if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG):
logger.debug("[SQL] %s", sql)
df = pd.read_sql_query(sql, conn, params=params)
if TRACE_LOGGING:
logger.info("[SQL] select rows=%s (elapsed=%.3fs)", len(df), time.perf_counter() - start)
return df, None, "select"
else:
if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG):
logger.debug("[SQL] %s", sql)
if params:
cur = conn.execute(sql, params)
else:
cur = conn.execute(sql)
conn.commit()
rows = cur.rowcount if cur.rowcount != -1 else None
if TRACE_LOGGING:
logger.info("[SQL] exec rows=%s (elapsed=%.3fs)", rows, time.perf_counter() - start)
return None, rows, "exec"
except sqlite3.OperationalError as e:
if "interrupted" in str(e).lower():
raise SqlTimeoutError(
f"SQL timed out after ~{SQL_HARD_TIMEOUT_SEC}s or exceeded VM budget {SQL_VM_BUDGET}."
) from e
raise
finally:
try:
conn.set_progress_handler(None, 0)
except Exception:
pass
conn.close()
def run_sql_generic_old (sql_or_tuple):
"""
Execute SQL safely with timeouts & progress budget.
Accepts either:
- sql: str
- (sql, params): tuple[str, Sequence[Any]]
Returns: (df, rows, kind) where kind is "select" or "exec".
"""
# Unpack (sql, params) or just sql
if isinstance(sql_or_tuple, (tuple, list)):
sql = sql_or_tuple[0] if len(sql_or_tuple) > 0 else ""
params = sql_or_tuple[1] if len(sql_or_tuple) > 1 else None
else:
sql = sql_or_tuple
params = None
if not isinstance(sql, str):
raise TypeError("SQL must be a string or (sql, params) tuple.")
start = time.perf_counter()
is_select = _looks_like_select(sql)
conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False)
try:
# Pragmas tuned for WAL mode + decent performance
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};")
# Progress/timeout guard
vm_steps = 0
def _progress():
nonlocal vm_steps
vm_steps += SQL_PROGRESS_NSTEPS
# Hard time limit
if (time.perf_counter() - start) > SQL_HARD_TIMEOUT_SEC:
return 1
# VM steps budget
if vm_steps >= SQL_VM_BUDGET:
return 1
return 0
conn.set_progress_handler(_progress, SQL_PROGRESS_NSTEPS)
# Logging (trace)
if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG):
if params is not None:
logger.debug("[SQL] %s | params=%s", sql, params)
else:
logger.debug("[SQL] %s", sql)
if is_select:
# pandas will bind params if provided
if params is not None:
df = pd.read_sql_query(sql, conn, params=params)
else:
df = pd.read_sql_query(sql, conn)
if TRACE_LOGGING:
logger.info("[SQL] select rows=%s (elapsed=%.3fs)", len(df), time.perf_counter() - start)
return df, None, "select"
else:
# Exec with optional params
if params is not None:
cur = conn.execute(sql, params)
else:
cur = conn.execute(sql)
conn.commit()
rows = cur.rowcount if cur.rowcount != -1 else None
if TRACE_LOGGING:
logger.info("[SQL] exec rows=%s (elapsed=%.3fs)", rows, time.perf_counter() - start)
return None, rows, "exec"
except sqlite3.OperationalError as e:
# Convert SQLite interruption into our timeout error
if "interrupted" in str(e).lower():
raise SqlTimeoutError(
f"SQL timed out after ~{SQL_HARD_TIMEOUT_SEC}s or exceeded VM budget {SQL_VM_BUDGET}."
) from e
raise
except sqlite3.ProgrammingError as e:
# Helpful hint when bindings don't match
if "Incorrect number of bindings" in str(e):
if TRACE_LOGGING:
logger.error("[SQL] binding mismatch for: %s | params=%s", sql, params)
raise
finally:
try:
conn.set_progress_handler(None, 0)
except Exception:
pass
conn.close()
# ==== EDA helpers ================================================
from datetime import datetime
import math
import numpy as np
# Known keys for Olist tables (used for duplicate detection)
OLIST_KEYS = {
"orders": ["order_id"],
"order_items": ["order_id", "order_item_id"],
"order_payments": ["order_id", "payment_sequential"],
"order_reviews": ["review_id"],
"customers": ["customer_id"],
"products": ["product_id"],
"sellers": ["seller_id"],
"product_category_name_translation": ["product_category_name"],
# geolocation has no natural key; duplicates are expected
}
# Big-table guard rails
EDA_ROW_CAP_FOR_FULLROW_DUP = 200_000 # skip full-row dup on very large tables
EDA_SAMPLE_LIMIT_NUMERIC_HIST = 100_000 # cap numeric sample for histogram
EDA_TOPK_CATEGORICAL = 10 # default for top-K categories
def _list_tables(conn) -> list:
cur = conn.execute("""
SELECT name
FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%'
ORDER BY 1;
""")
return [r[0] for r in cur.fetchall()]
def _get_cols(conn, table: str) -> list:
cur = conn.execute(f"PRAGMA table_info({table})")
return [r[1] for r in cur.fetchall()]
def _get_declared_types(conn, table: str) -> dict:
cur = conn.execute(f"PRAGMA table_info({table})")
# returns: cid, name, type, notnull, dflt_value, pk
return {r[1]: (r[2] or "").upper() for r in cur.fetchall()}
def _affinity(declared: str, colname: str) -> str:
"""Rough SQLite affinity detection with a pragmatic nudge by column name."""
t = (declared or "").upper()
cn = (colname or "").lower()
# Numeric-ish
if any(k in t for k in ["INT", "REAL", "FLOA", "DOUB", "NUMERIC", "DEC"]):
return "num"
# Date-ish by declared type
if any(k in t for k in ["DATE", "TIME"]):
return "date"
# Heuristic by name
if any(k in cn for k in ["date", "timestamp", "time_"]):
return "date"
# Default text
return "text"
def _count_rows(conn, table: str) -> int:
return conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
def _count_nulls_per_col(conn, table: str, cols: list) -> dict:
expr = ", ".join([f"SUM(CASE WHEN {c} IS NULL THEN 1 ELSE 0 END) AS {c}" for c in cols])
row = conn.execute(f"SELECT {expr} FROM {table}").fetchone()
return {cols[i]: (row[i] or 0) for i in range(len(cols))}
def _count_empty_strings_text(conn, table: str, cols: list, affinities: dict) -> dict:
out = {}
for c in cols:
if affinities.get(c) == "text":
out[c] = conn.execute(f"SELECT COUNT(*) FROM {table} WHERE TRIM({c}) = ''").fetchone()[0]
else:
out[c] = 0
return out
def _count_distinct_per_col(conn, table: str, cols: list) -> dict:
out = {}
for c in cols:
try:
out[c] = conn.execute(f"SELECT COUNT(DISTINCT {c}) FROM {table}").fetchone()[0]
except Exception:
out[c] = None
return out
def _min_max_numeric(conn, table: str, col: str):
try:
mn, mx = conn.execute(
f"SELECT MIN(CAST({col} AS REAL)), MAX(CAST({col} AS REAL)) FROM {table} WHERE {col} IS NOT NULL"
).fetchone()
return mn, mx
except Exception:
return None, None
def _min_max_datetime(conn, table: str, col: str):
"""
Try SQLite datetime() conversion; if NULLs, fall back to MIN/MAX text.
Works for ISO-8601-ish timestamps in Olist.
"""
try:
mn, mx = conn.execute(
f"SELECT MIN(datetime({col})), MAX(datetime({col})) FROM {table} WHERE {col} IS NOT NULL"
).fetchone()
if mn is None and mx is None:
mn, mx = conn.execute(
f"SELECT MIN({col}), MAX({col}) FROM {table} WHERE {col} IS NOT NULL"
).fetchone()
return mn, mx
except Exception:
return None, None
def _count_duplicates_by_keys(conn, table: str, key_cols: list) -> int:
keys = ", ".join(key_cols)
sql = f"""
SELECT COALESCE(SUM(cnt - 1), 0) AS dup_count
FROM (
SELECT COUNT(*) AS cnt
FROM {table}
GROUP BY {keys}
HAVING COUNT(*) > 1
) t;
"""
return conn.execute(sql).fetchone()[0] or 0
def _count_fullrow_duplicates(conn, table: str, cols: list, total_rows: int) -> int | None:
if total_rows > EDA_ROW_CAP_FOR_FULLROW_DUP:
return None
parts = [f"IFNULL(CAST({c} AS TEXT),'∅')" for c in cols]
concat = "|| '¦' ||".join(parts)
sql = f"""
SELECT (COUNT(*) - COUNT(DISTINCT {concat})) AS dup_all
FROM {table};
"""
return conn.execute(sql).fetchone()[0] or 0
# ---------- Distribution helpers (computed on-demand in UI) ------------------
def _hist_numeric_sample(conn, table: str, col: str, sample_limit: int = EDA_SAMPLE_LIMIT_NUMERIC_HIST):
sql = f"""
SELECT CAST({col} AS REAL) AS v
FROM {table}
WHERE {col} IS NOT NULL
LIMIT ?
"""
vals = [r[0] for r in conn.execute(sql, (int(sample_limit),)).fetchall() if r[0] is not None]
return vals
def _topk_categorical(conn, table: str, col: str, k: int = EDA_TOPK_CATEGORICAL):
try:
rows = conn.execute(
f"SELECT {col} AS v, COUNT(*) AS cnt FROM {table} GROUP BY {col} ORDER BY cnt DESC LIMIT ?",
(int(k),)
).fetchall()
return rows
except Exception:
return []
def _timeseries_monthly(conn, table: str, col: str):
sql = f"""
SELECT strftime('%Y-%m', datetime({col})) AS ym, COUNT(*) AS cnt
FROM {table}
WHERE {col} IS NOT NULL
GROUP BY ym
ORDER BY ym
"""
rows = conn.execute(sql).fetchall()
return rows
CODEY_HINTS = ("id", "code", "zip", "prefix", "number", "cpf", "cnpj")
def _is_integer_series(s):
s = s.dropna()
if s.empty:
return False
# true integer or float with all whole numbers
return (np.issubdtype(s.dtype, np.integer) or
(np.issubdtype(s.dtype, np.floating) and np.all(np.isfinite(s)) and np.all(np.modf(s)[0] == 0.0)))
def is_code_like(series: pd.Series) -> bool:
"""Heuristic: numeric but looks like an ID/code."""
name = (series.name or "").lower()
s = series.dropna()
if s.empty:
return False
# integer-like and column name hints OR very high cardinality with wide numeric range
name_flag = any(h in name for h in CODEY_HINTS)
int_like = _is_integer_series(s)
# many unique values but mostly discrete
high_card = s.nunique() > min(200, int(len(s) * 0.2))
wide_range = (s.max() - s.min()) > 1_000 # tweak as needed
return int_like and (name_flag or (high_card and wide_range))
def pretty_bin_label(interval) -> str:
lo, hi = interval.left, interval.right
def fmt(x):
if abs(x) >= 1_000_000: return f"{x/1_000_000:.1f}M"
if abs(x) >= 1_000: return f"{x/1_000:.1f}k"
return f"{x:.0f}"
return f"{fmt(lo)}–{fmt(hi)}"
def plot_distribution(ax, series: pd.Series, max_bins: int = 10):
s = series.dropna()
ax.set_title(series.name, fontsize=11)
# 1) treat code-like numeric as categories
if np.issubdtype(s.dtype, np.number) and is_code_like(series):
counts = s.value_counts().head(20)
counts.sort_values(ascending=False).plot(kind="bar", ax=ax)
ax.set_ylabel("count")
ax.set_xlabel("top 20 values")
ax.tick_params(axis="x", rotation=45, labelsize=8)
return
# 2) real numeric: quantile bins
if np.issubdtype(s.dtype, np.number):
# drop duplicates='drop' to handle repeated quantiles
try:
cats = pd.qcut(s, q=min(max_bins, max(2, s.nunique())), duplicates="drop")
counts = cats.value_counts().sort_index()
# re-label bins nicely
labels = [pretty_bin_label(iv) for iv in counts.index]
ax.bar(range(len(counts)), counts.values)
ax.set_xticks(range(len(counts)))
ax.set_xticklabels(labels, rotation=60, ha="right", fontsize=8)
ax.set_ylabel("count")
except Exception:
# fallback simple hist if qcut fails
ax.hist(s, bins=min(max_bins, 20))
ax.tick_params(axis="x", rotation=45, labelsize=8)
ax.set_ylabel("count")
return
# 3) non-numeric: top categories
counts = s.astype(str).value_counts().head(20)
counts.sort_values(ascending=False).plot(kind="bar", ax=ax)
ax.set_ylabel("count")
ax.set_xlabel("top 20 categories")
ax.tick_params(axis="x", rotation=45, labelsize=8)
@st.cache_data(show_spinner=False)
def run_eda_snapshot(db_path: str, deep_freq: bool = False, topk: int = EDA_TOPK_CATEGORICAL) -> dict:
"""
Snapshot summary (lightweight): total rows, duplicate counts, per-column
nulls, empties (TEXT), distincts, and min/max for numeric/date columns.
Heavy plots are computed on-demand in the UI.
"""
_raw = os.getenv("EDA_EXCLUDE", "chat_history")
EDA_EXCLUDE_TABLES = {x.strip() for x in _raw.split(",") if x.strip()}
mtime = os.path.getmtime(db_path)
out = {"_meta": {"db_mtime": mtime, "generated_at": datetime.utcnow().isoformat()}}
with sqlite3.connect(db_path, timeout=SQL_TIMEOUT) as conn:
_apply_busy_timeout(conn)
all_tables = _list_tables(conn)
tables = [t for t in all_tables if t not in EDA_EXCLUDE_TABLES ]
for t in tables:
cols = _get_cols(conn, t)
if not cols:
continue
declared = _get_declared_types(conn, t)
affinities = {c: _affinity(declared.get(c, ""), c) for c in cols}
total = _count_rows(conn, t)
nulls = _count_nulls_per_col(conn, t, cols)
empties_text = _count_empty_strings_text(conn, t, cols, affinities)
# Distinct counts
try:
distincts = _count_distinct_per_col(conn, t, cols)
except Exception:
distincts = {c: None for c in cols}
# Duplicates
if t in OLIST_KEYS:
try:
dup_by_keys = _count_duplicates_by_keys(conn, t, OLIST_KEYS[t])
except Exception:
dup_by_keys = None
dup_full = None
else:
dup_by_keys = None
try:
dup_full = _count_fullrow_duplicates(conn, t, cols, total)
except Exception:
dup_full = None
# Min/Max (num & date)
col_rows = []
for c in cols:
item = {
"name": c,
"affinity": affinities.get(c),
"nulls": nulls.get(c, 0),
"empty_strings": empties_text.get(c, 0),
"distinct": distincts.get(c),
"min": None,
"max": None,
}
if affinities.get(c) == "num":
mn, mx = _min_max_numeric(conn, t, c)
item["min"], item["max"] = mn, mx
elif affinities.get(c) == "date":
mn, mx = _min_max_datetime(conn, t, c)
item["min"], item["max"] = mn, mx
col_rows.append(item)
out[t] = {
"total_rows": total,
"dup_by_keys": dup_by_keys,
"dup_fullrow": dup_full,
"columns": col_rows,
}
return out
def _eda_to_df(eda: dict, table: str) -> pd.DataFrame:
"""Format a single table's column stats to a neat DataFrame."""
cols = eda.get(table, {}).get("columns", [])
df = pd.DataFrame(cols)
if df.empty:
return df
tr = max(eda.get(table, {}).get("total_rows", 0), 1)
if "nulls" in df.columns:
df["null_pct"] = (df["nulls"] * 100.0 / tr).round(2)
if "empty_strings" in df.columns:
df["empty_pct"] = (df["empty_strings"] * 100.0 / tr).round(2)
# order columns nicely
want = ["name", "affinity", "nulls", "null_pct", "empty_strings", "empty_pct", "distinct", "min", "max"]
keep = [c for c in want if c in df.columns]
others = [c for c in df.columns if c not in keep]
return df[keep + others]
#----------------EDA Helper End -----------------
# -----------------------------
# LLM wrapper
# -----------------------------
def get_llm_client(choice: str):
choice = (choice or "OPENAI").upper()
if choice == "OPENAI":
from openai import OpenAI
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
class Wrapper:
def chat(self, messages):
try:
log_llm_inputs("pre_llm_invoke", (locals().get("auth") or {}).get("user_id") if "auth" in locals() else None, locals().get("chat_id"), str(locals().get("OPENAI_MODEL") or locals().get("ANTHROPIC_MODEL") or "model"), locals().get("messages") or [])
except Exception:
pass
resp = client.chat.completions.create(
model="gpt-4.1-mini",
messages=messages,
max_tokens=1000,
temperature=0.1,
)
return resp.choices[0].message.content.strip()
return Wrapper()
elif choice == "ANTHROPIC":
import anthropic
client = anthropic.Anthropic(api_key=os.getenv("ANTHROPIC_API_KEY"))
class Wrapper:
def chat(self, messages):
sys = "\n".join([m["content"] for m in messages if m["role"]=="system"])
usr = "\n".join([m["content"] for m in messages if m["role"]=="user"])
content = (sys + "\n\n" + usr).strip()
resp = client.messages.create(
model="claude-3-haiku-20240307",
max_tokens=1000,
temperature=0.1,
messages=[{"role":"user","content":content}],
)
return "".join([getattr(b, "text", "") for b in resp.content])
return Wrapper()
else:
raise ValueError(f"Unsupported LLM_CHOICE: {choice}")
def plan_sql(llm, user_text: str, schema_registry: dict):
import json as _json
schema_json = _json.dumps({t:list(schema_registry[t]["columns"].keys()) for t in schema_registry})
# === RAG retrieval (1212) — your original quick exemplars ===
try:
# simple heuristic to guess intent quickly using existing classifier if present
intent_guess = None
try:
intent_guess = detect_non_sql_intent(user_text) or None
except Exception:
intent_guess = None
schema_fp = _schema_fingerprint(schema_registry)
ex_query = "\n".join([
"[INTENT]={}".format(intent_guess or ""),
"QUESTION={}".format(user_text),
"SCHEMA={}".format(schema_fp),
])
exemplars_text_side = RAG.query(ex_query, k=3, where={"schema_fp": schema_fp} if RAG_BACKEND=="CHROMA" else None)
ex_blocks = []
for ex in exemplars_text_side:
ex_blocks.append(ex.get("text",""))
sep = "\n\n--- EXEMPLAR ---\n" # safe, single-line
ex_text = sep.join(ex_blocks) if ex_blocks else ""
except Exception:
ex_text = ""
# === Additional exemplar few-shots (prepended system/name=exemplar messages) ===
ex_msgs = []
try:
exemplars = []
try:
# this helper should exist in your codebase (or be added as discussed earlier)
exemplars = _rag_fetch_exemplars_for_query(user_text, k=4) or []
except Exception:
exemplars = []
for ex in exemplars:
uq = (ex.get("user_input") or "").strip()
plan = (ex.get("llm_plan") or "").strip()
sql = (ex.get("secured_sql") or "").strip()
if not (uq and sql):
# Fallback: try to parse from a published "doc" line format
doc = (ex.get("doc") or "")
try:
import re as _re
_uq = _re.search(r"(?mi)^Q:\s*(.+)$", doc)
_plan = _re.search(r"(?mi)^Plan:\s*(.+)$", doc)
_sql = _re.search(r"(?mi)^SQL:\s*(.+)$", doc)
uq = uq or (_uq and _uq.group(1).strip()) or ""
plan = plan or (_plan and _plan.group(1).strip()) or ""
sql = sql or (_sql and _sql.group(1).strip()) or ""
except Exception:
pass
if uq and sql:
ex_msgs.append({
"role": "system",
"name": "exemplar",
"content": f"EXEMPLAR\nQ: {uq}\nSQL: {sql}\n{('PLAN: ' + plan) if plan else ''}"
})
except Exception:
# swallow exemplar assembly errors
ex_msgs = []
sys_prompt = """You are a SQL planner for SQLite Olist dataset.
Guidelines:
- Use only valid tables/columns from SCHEMA.
- Prefer a safe LIMIT 50-100 unless user explicitly asks for all rows.
- Align date ranges with the user's wording.
- Outputs must be pure JSON with this shape:
{"queries": ["SQL1","SQL2?"], "explanation":"..."}
If EXEMPLARS are provided, learn patterns but generate fresh SQL that matches the new QUESTION.
"""
user_block = f"""USER QUESTION:
{user_text}
SCHEMA:
{schema_json}"""
if ex_text:
user_block += f"""
EXEMPLARS:
{ex_text}"""
# Build planner messages: system -> (prepended exemplars) -> user
planner_messages = [{"role": "system", "content": sys_prompt}]
if ex_msgs:
planner_messages.extend(ex_msgs)
planner_messages.append({"role": "user", "content": user_block})
# Telemetry: include trimmed exemplar summary in logs
try:
_ex_tele = []
for m in ex_msgs[:4]:
try:
lines = (m.get("content") or "").splitlines()
qline = next((ln for ln in lines if ln.strip().startswith("Q:")), "")
_ex_tele.append({"q": qline[2:].strip()[:120] if qline else "", "has_sql": any(ln.strip().startswith("SQL:") for ln in lines)})
except Exception:
_ex_tele.append({"q": "", "has_sql": False})
# pull session user/chat if available
session_user_id = None
session_chat_id = None
try:
import streamlit as st
_auth = st.session_state.get("auth") or {}
session_user_id = _auth.get("user_id")
session_chat_id = st.session_state.get("chat_id")
except Exception:
pass
try:
# safe call if your codebase has this logger
log_llm_inputs(
event="pre_llm_invoke",
user_id=session_user_id,
chat_id=session_chat_id,
model="model",
messages=planner_messages,
context="",
exemplars=_ex_tele,
plan={},
extra={}
)
except Exception:
pass
except Exception:
pass
out = llm.chat(planner_messages)
try:
return _json.loads(out)
except Exception:
return {"queries":[str(out).strip()],"explanation":"fallback"}
# -----------------------------
# Validator / Policy helpers
# -----------------------------
SQL_KEYWORDS = {
"WHERE","ON","USING","GROUP","ORDER","LIMIT","JOIN","LEFT","RIGHT","FULL","INNER","OUTER",
"CROSS","NATURAL","UNION","EXCEPT","INTERSECT","HAVING","OFFSET","FETCH","BY"
}
SENSITIVE_TABLES = {"orders", "order_items", "order_payments", "order_reviews"}
def _strip_trailing_semicolon(s: str) -> str:
return re.sub(r";\s*$", "", s.strip())
CLAUSE_BOUNDARY = re.compile(
r"\b(GROUP\s+BY|HAVING|WINDOW|ORDER\s+BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT)\b",
re.IGNORECASE,
)
def _inject_where(sql: str, condition: str) -> str:
s = _strip_trailing_semicolon(sql)
m_where = re.search(r"\bWHERE\b", s, flags=re.IGNORECASE)
if m_where:
after_where = s[m_where.end():]
m_bound = CLAUSE_BOUNDARY.search(after_where)
segment = after_where[: m_bound.start()] if m_bound else after_where
if segment.strip() == "":
return s[: m_where.end()] + f" {condition} " + after_where
else:
return s[: m_where.end()] + f" {condition} AND" + after_where
m_bound = CLAUSE_BOUNDARY.search(s)
if m_bound:
i = m_bound.start()
return s[:i] + f" WHERE {condition} " + s[i:]
else:
return s + f" WHERE {condition}"
def _top_level_alias(sql: str, table: str):
pat = re.compile(
rf"(FROM|JOIN)\s+{table}\b(?:\s+(?:AS\s+)?(?P<alias>[A-Za-z_][A-Za-z0-9_]*))?",
re.IGNORECASE
)
depth = 0
in_quote = None
i = 0
pos_set = set()
while i < len(sql):
ch = sql[i]
if in_quote:
if ch == in_quote: in_quote = None
else:
if ch in ("'", '"'): in_quote = ch
elif ch == "(": depth += 1
elif ch == ")" and depth > 0: depth -= 1
if depth == 0: pos_set.add(i)
i += 1
for m in pat.finditer(sql):
if m.start() in pos_set:
alias = m.group("alias")
if alias and alias.upper() not in SQL_KEYWORDS:
return alias, True
return table, True
return None, False
def _cleanup_where_and(sql: str) -> str:
s = re.sub(r"\bWHERE\s+AND\b", "WHERE", sql, flags=re.IGNORECASE)
s = re.sub(r"\bAND\s+AND\b", "AND", s, flags=re.IGNORECASE)
s = re.sub(r"\s{2,}", " ", s)
return s.strip()
def _remove_unscoped_notin_products(sql: str, product_ref: str) -> str:
pat = re.compile(
rf"(?:\b{re.escape(product_ref)}\.)?product_id\s+NOT\s+IN\s*\(\s*(?P<sub>.*?FROM\s+order_items\b.*?)\)",
re.IGNORECASE | re.DOTALL,
)
def repl(m):
sub = m.group("sub")
if re.search(r"\b(orders|customer_id)\b", sub, flags=re.IGNORECASE):
return m.group(0)
return ""
s = pat.sub(repl, sql)
return _cleanup_where_and(s)
def _find_table_alias(sql: str, table: str):
m = re.search(
rf"(?:FROM|JOIN)\s+{table}\b(?:\s+(?:AS\s+)?(?P<alias>[A-Za-z_][A-Za-z0-9_]*))?",
sql, flags=re.IGNORECASE
)
if not m:
return (None, False)
alias = m.group("alias")
if alias and alias.upper() not in SQL_KEYWORDS:
return (alias, True)
return (table, True)
def _mentions_seller(sql: str) -> bool:
return bool(
re.search(r"\bseller_id\b", sql, re.IGNORECASE) or
re.search(r"(?:FROM|JOIN)\s+sellers\b", sql, re.IGNORECASE) or
re.search(r"\bGROUP\s+BY\s+[^;]*seller_id\b", sql, re.IGNORECASE) or
re.search(r"\bORDER\s+BY\s+[^;]*seller_id\b", sql, re.IGNORECASE)
)
def _mentions_product(sql: str) -> bool:
return bool(
re.search(r"\bproduct_id\b", sql, re.IGNORECASE) or
re.search(r"(?:FROM|JOIN)\s+products\b", sql, re.IGNORECASE)
)
def _is_revenue_sql(sql: str) -> bool:
return bool(
re.search(r"\bSUM\s*\(\s*(oi\.)?(price|freight_value)\s*\)", sql, re.IGNORECASE) or
re.search(r"\bSUM\s*\(\s*(op\.)?payment_value\s*\)", sql, re.IGNORECASE) or
re.search(r"\b(revenue|gmv|sales|turnover)\b", sql, re.IGNORECASE)
)
def _is_seller_revenue_sql(sql: str) -> bool:
return _is_revenue_sql(sql) and _mentions_seller(sql)
def _is_product_revenue_sql(sql: str) -> bool:
return _is_revenue_sql(sql) and _mentions_product(sql)
# payments helpers
def _is_payments_sql(sql: str) -> bool:
return re.search(r"(?:FROM|JOIN)\s+order_payments\b", sql, re.IGNORECASE) is not None
def _has_customer_scope(sql: str) -> bool:
if re.search(r"\bcustomer_id\s*=\s*['\"][^'\"()]+['\"]", sql, re.IGNORECASE):
return True
if re.search(r"EXISTS\s*\(\s*SELECT\s+1\s+FROM\s+orders\s+\w+\s+WHERE\s+[^)]*\bcustomer_id\b", sql, re.IGNORECASE):
return True
return False
def _intent_platform_wide_payments(user_text: str) -> bool:
t = f" { (user_text or '').lower() } "
if "payment" not in t and "payments" not in t:
return False
user_markers = [" my ", " me ", " mine ", " by me ", " for me ", " my payments"]
if any(m in t for m in user_markers):
return False
platform_markers = [
"on the platform", "platform payments", "overall payments",
"entire platform", "whole platform", "across the platform",
"site-wide", "system-wide"
]
if any(m in t for m in platform_markers): return True
if re.search(r"\ball\b.*\bpayments?\b", t): return True
if re.search(r"\bevery\b.*\bpayments?\b", t): return True
if re.search(r"\btotal\b.*\bpayments?\b", t): return True
return False
# write & introspection guards
def _is_mutating_sql(sql: str) -> bool:
return re.search(
r"^\s*(UPDATE|DELETE|INSERT|REPLACE|MERGE|CREATE|ALTER|DROP|TRUNCATE|VACUUM)\b",
sql, re.IGNORECASE
) is not None
def _is_schema_introspection_sql(sql: str) -> bool:
if re.search(r"^\s*PRAGMA\b", sql, re.IGNORECASE): return True
if re.search(r"\bsqlite_master\b|\bsqlite_schema\b", sql, re.IGNORECASE): return True
if re.search(r"SELECT\s+name\s+FROM\s+sqlite_master", sql, re.IGNORECASE): return True
if re.search(r"SELECT\s+\*\s+FROM\s+sqlite_master", sql, re.IGNORECASE): return True
return False
def _intent_schema_introspection(user_text: str) -> bool:
t = (user_text or "").lower()
# Robust patterns (covers: "list of all tables", "share the list of tables", "show tables", etc.)
patterns = [
r"\blist(?:\s+of)?\s+(?:all\s+)?tables\b",
r"\bshare\s+(?:the\s+)?list\s+of\s+(?:all\s+)?tables\b",
r"\bgive\s+me\s+(?:the\s+)?list\s+of\s+(?:all\s+)?tables\b",
r"\bshow(?:\s+me)?\s+(?:the\s+)?(?:list\s+of\s+)?(?:all\s+)?tables\b",
r"\bwhat\s+(?:are\s+)?(?:the\s+)?tables\b",
r"\bdescribe\s+tables?\b",
r"\b(list|show)\s+(?:all\s+)?indexes\b",
r"\b(show|list)\s+schema\b",
r"\b(db|database)\s+schema\b",
r"\bschema\b",
]
for p in patterns:
if re.search(p, t):
return True
# Back-compat substring keys (kept so behavior elsewhere remains unchanged)
keys = [
"list all tables", "list tables", "show tables", "what tables",
"schema", "describe tables", "list indexes", "show indexes",
"list all indexes", "show schema", "db schema", "database schema"
]
return any(k in t for k in keys)
def _intent_dump_all_data(user_text: str) -> bool:
t = f" {(user_text or '').lower()} "
# Direct phrases
patterns = [
"list all data", "dump all data", "export all data",
"everything in database", "all database data", "full data dump",
"list data from all tables", "list data from each table",
"show data from all tables", "show all table data",
"dump data from all tables", "export data from all tables",
"all tables data", "data from all tables",
]
if any(p in t for p in patterns):
return True
# Heuristic: mentions "all/each/every table" + data-ish verbs/nouns
table_scope = (" all tables " in t) or (" each table " in t) or (" every table " in t)
data_words = any(w in t for w in [" data ", " rows ", " records ", " entries ", " select *", " list ", " show "])
return bool(table_scope and data_words)
def _plan_looks_like_dump_all_tables(plan: dict) -> bool:
try:
qs = plan.get("queries") or []
except Exception:
return False
uniq_tables = set()
count_selects = 0
for q in qs:
if not isinstance(q, str):
continue
if re.search(r"^\s*SELECT\b", q, re.IGNORECASE):
count_selects += 1
for t in _extract_tables_from_sql(q):
uniq_tables.add(t)
# Heuristic: selecting from several distinct base tables looks like a dump
return (count_selects >= 3 and len(uniq_tables) >= 3)
# pagination/limit helpers
def _has_limit_clause(sql: str) -> bool:
try:
return re.search(r"\bLIMIT\b\s+\d+", sql, flags=re.IGNORECASE) is not None
except Exception:
return False
def _ensure_limit(sql: str, n: int = 50) -> str:
s = _strip_trailing_semicolon(sql)
if not _looks_like_select(s): return s
if _has_limit_clause(s): return s
return f"{s} LIMIT {n}"
def _force_limit(sql: str, n: int = 10) -> str:
s = _strip_trailing_semicolon(sql)
if not _looks_like_select(s): return s
if _has_limit_clause(s):
s = re.sub(r"(?i)LIMIT\s+\d+", f"LIMIT {n}", s); return s
return f"{s} LIMIT {n}"
def _cap_limit(sql: str, cap: int = 50) -> str:
"""Ensure LIMIT exists and is <= cap; if existing LIMIT > cap, reduce to cap; if no LIMIT, add cap."""
s = _strip_trailing_semicolon(sql)
if not _looks_like_select(s):
return s
try:
m = re.search(r"(?i)\bLIMIT\s+(\d+)", s)
if m:
val = int(m.group(1))
if val <= cap:
return s # keep user's smaller limit
return re.sub(r"(?i)\bLIMIT\s+\d+", f"LIMIT {cap}", s)
else:
return f"{s} LIMIT {cap}"
except Exception:
return _ensure_limit(s, cap)
def _wants_all(user_text: str) -> bool:
return bool(re.search(r"\ball\b", (user_text or ""), flags=re.IGNORECASE))
# rate limit helpers
def _utc_now(): return datetime.utcnow()
def _utc_threshold_str(seconds: int) -> str:
return (_utc_now() - timedelta(seconds=seconds)).strftime("%Y-%m-%d %H:%M:%S")
def check_rate_limit(user_id: str, role: str) -> tuple[bool, str]:
max_req = RATE_LIMIT_MAX_REQUESTS_ADMIN if (role == "admin") else RATE_LIMIT_MAX_REQUESTS_USER
if max_req <= 0: return (False, "")
threshold = _utc_threshold_str(RATE_LIMIT_WINDOW_SEC)
try:
with get_conn() as conn:
row = conn.execute(
"SELECT COUNT(*) FROM chat_history WHERE user_id = ? AND created_at >= ?",
(user_id, threshold)
).fetchone()
count = int(row[0]) if row and row[0] is not None else 0
if count >= max_req:
msg = f"⏳ Rate limit hit: max {max_req} requests per {RATE_LIMIT_WINDOW_SEC}s. Please try again shortly."
return (True, msg)
return (False, f"{count}/{max_req} used in the last {RATE_LIMIT_WINDOW_SEC}s.")
except Exception as e:
logger.warning("Rate-limit check failed: %s", e)
return (False, "Rate-limit check unavailable.")
# block: rowcounts across all tables / each table
def _intent_rowcounts_all_tables(user_text: str) -> bool:
t = f" {(user_text or '').lower()} "
keywords = [
"count of records of all tables","count from each table","counts from each table",
"row count of each table","row counts of all tables","table wise record count",
"table-wise record count","table wise counts","table-wise counts",
"how many rows in each table","list all tables with row counts","show tables with row counts",
"rowcount of all tables","counts per table",
]
if any(k in t for k in keywords): return True
if (" count " in t or " counts " in t) and (" each table " in t or " all tables " in t): return True
return False
_rowcount_single_table_re = re.compile(
r"^\s*SELECT\s+COUNT\s*\(\s*\*\s*\)\s+FROM\s+([A-Za-z_][A-Za-z0-9_]*)\b",
re.IGNORECASE | re.DOTALL
)
def _is_single_table_rowcount_sql(sql: str) -> str | None:
s = _strip_trailing_semicolon(sql or "")
m = _rowcount_single_table_re.match(s)
if not m: return None
if re.search(r"\bGROUP\s+BY\b", s, flags=re.IGNORECASE): return None
return m.group(1)
def _plan_looks_like_multitable_rowcounts(plan: dict) -> bool:
try:
qs = plan.get("queries") or []
except Exception:
return False
tables = set()
for q in qs:
t = _is_single_table_rowcount_sql(q)
if t: tables.add(t.lower())
return len(tables) >= 2
def _is_user_count_query(sql: str) -> bool:
s = _strip_trailing_semicolon(sql or "")
return re.search(r"^\s*SELECT\s+COUNT\s*\(", s, re.IGNORECASE) is not None
# -----------------------------
# (Multilingual) Translation helpers — IndicTrans2 (lazy cached)
# -----------------------------
TRANSLATION_ENABLED = os.getenv("TRANSLATION_ENABLED", "1") == "1"
LANG_CODE_MAP = {"en": "eng_Latn", "hi": "hin_Deva", "mr": "mar_Deva"}
_LANG_TAGS = {"eng_Latn","hin_Deva","mar_Deva"}
try:
_LANG_TO_TAG # type: ignore
except NameError:
_LANG_TO_TAG = {
"en": "eng_Latn",
"hi": "hin_Deva",
"mr": "mar_Deva",
"bn": "ben_Beng",
"ta": "tam_Taml",
"te": "tel_Telu",
"gu": "guj_Gujr",
"pa": "pan_Guru",
"kn": "kan_Knda",
"ml": "mal_Mlym",
"or": "ory_Orya",
"as": "asm_Beng",
"ur": "urd_Arab",
"ne": "npi_Deva",
}
def _norm_guard_text(t: str) -> str:
"""Lowercase, strip punctuation to make intent regex robust."""
if not t:
return ""
t = t.lower()
t = re.sub(r"[^\w\s]", " ", t) # remove punctuation like leading dots
t = re.sub(r"\s+", " ", t).strip()
return t
# English patterns for “dump/list across all tables”
_ALL_TABLES_EN_PATTERNS = [
r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:all\s+)?(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?",
r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?",
r"(?:all|every)\s+tables?\s+(?:data|records|rows|entries)",
r"(?:across|from|in)\s+(?:all|every)\s+tables?",
r"(?:for|from)\s+each\s+table(?:\s+counts?| data| records)?", # “count from each table”, etc.
r"list\s+(?:all\s+)?tables?\s+data", # “list all tables data”
r"dump\s+(?:the\s+)?database", # generic dump
]
def is_forbidden_all_tables_post_tx(en_text: str) -> bool:
"""Run on the translated-to-English query BEFORE planning."""
t = _norm_guard_text(en_text)
return any(re.search(p, t) for p in _ALL_TABLES_EN_PATTERNS)
def _sample(s: str, n: int = 120) -> str:
if s is None: return ""
s = str(s)
return s if len(s) <= n else (s[:n] + "…")
def _strip_existing_tag(text: str) -> str:
m = re.match(r"^\s*>>([A-Za-z_]+)<<\s*", text or "")
if m and m.group(1) in _LANG_TAGS:
return (text or "")[m.end():]
return text or ""
def _ensure_tag_prefixed(text: str, src_tag: str) -> str:
"""
Ensure `text` begins with a valid IndicTrans language tag (e.g., "eng_Latn "),
not the older ">>eng_Latn<<" bracketed form. If already present, return as-is.
"""
try:
s = (text or "").lstrip()
# Already prefixed with eng_Latn / en_Latn / <xxx>_Latn pattern at the start?
if re.match(r"^[A-Za-z]{2,3}_[A-Za-z]{4}\s", s):
return s
# Also accept bracketed tags and normalize them away
m = re.match(r"^>>([A-Za-z]{2,3}_[A-Za-z]{4})<<\s*", s)
if m:
s = re.sub(r"^>>([A-Za-z]{2,3}_[A-Za-z]{4})<<\s*", "", s)
tag = (src_tag or "eng_Latn").strip()
if not re.match(r"^[A-Za-z]{2,3}_[A-Za-z]{4}$", tag):
tag = "eng_Latn"
return f"{tag} " + s
except Exception:
return f"{src_tag or 'eng_Latn'} " + (text or "")
def _norm_lang(lang: str | None) -> str:
try:
s = (lang or "").strip().lower().replace("-", "_")
return s
except Exception:
return "en"
def _indic_tag(lang: str, default: str = "eng_Latn") -> str:
"""Return IndicTrans language tag for a given language code.
- If `lang` already looks like a tag (contains '_'), return as-is.
- Else normalize and map via _LANG_TO_TAG.
"""
base = _norm_lang(lang)
if "_" in base:
return base
return _LANG_TO_TAG.get(base, default)
def _fix_tokenizer_lang(tok, src_tag: str, tgt_tag: str) -> None:
"""Set tokenizer src/tgt language attributes regardless of version quirks."""
try:
tok.src_lang = src_tag
tok.tgt_lang = tgt_tag
except Exception:
setattr(tok, "src_lang", src_tag)
setattr(tok, "tgt_lang", tgt_tag)
def _tx_normalize_target_code(lang: str) -> str | None:
"""Return an IndicTrans2 tag for the given short language code, or None."""
short = (lang or "en").split("-")[0].lower()
return _LANG_TO_TAG.get(short)
def _sanitize_lang_tag(val: str | None) -> str | None:
"""Strip accidental >>...<< and whitespace from a lang tag, return None if empty."""
if not val:
return val
v = str(val).strip()
if v.startswith(">>") and v.endswith("<<"):
v = v[2:-2].strip()
return v or None
def _ensure_tag_prefixed(text: str, tag: str) -> str:
"""Ensure text is prefixed with >>{tag}<< (required by IndicTrans2)."""
prefix = f">>{tag}<< "
t = (text or "").lstrip()
return t if t.startswith(prefix) else prefix + t
# ---- Translation cache / accessors (place near top-level globals) ----
import threading
_TX = None
_TX_lock = threading.Lock()
@st.cache_resource(show_spinner=False)
def _load_tx_models_cached():
# Reuse your existing loader; it returns {"ready": bool, ...} on success/failure
return _load_tx_models()
def _get_tx():
"""Return a ready translation bundle or a stub with ready=False."""
global _TX
if _TX and _TX.get("ready"):
return _TX
try:
with _TX_lock:
if _TX and _TX.get("ready"):
return _TX
_TX = _load_tx_models_cached()
return _TX or {"ready": False, "error": "no result from loader"}
except Exception as e:
logger.warning("[tx] _get_tx failed: %s", e, exc_info=True)
return {"ready": False, "error": str(e)}
def _tx_translate_en_to(text: str, tgt_lang: str) -> str:
"""Translate EN -> tgt_lang using the cached IndicTrans2 bundle."""
try:
info = _get_tx() # accessor around the cached _TX
if not info.get("ready"):
logger.warning("[tx] translate skipped: backend not ready (%s)", info.get("error"))
return text
tok = info.get("en_indic_tok")
mod = info.get("en_indic_mod")
if not tok or not mod:
logger.warning("[tx] translate skipped: missing tokenizer/model in bundle")
return text
src_tag = _indic_tag("en") # -> 'eng_Latn'
tgt_tag = _indic_tag(tgt_lang, "hin_Deva") # -> 'hin_Deva' for Hindi (default)
_fix_tokenizer_lang(tok, src_tag, tgt_tag) # set attributes best-effort
# *** CRITICAL *** Always prefix with the source tag so the tokenizer never
# tries to treat the first word as a tag. This fixes "Invalid source language tag: Pls".
tagged = f">>{src_tag}<< " + str(text)
logger.info("[tx] _tx_translate_en_to: tokenize start | src=%s tgt=%s", src_tag, tgt_tag)
# Do NOT pass src_lang/tgt_lang via kwargs (some builds ignore/override oddly);
# rely on the prefixed text + attributes we set above.
enc = tok(
[tagged],
return_tensors="pt",
padding=True,
truncation=True,
)
# Move to model device if available
try:
import torch
enc = {k: v.to(mod.device) for k, v in enc.items()}
infer_ctx = torch.inference_mode()
except Exception:
class _Null:
def __enter__(self): return None
def __exit__(self, *a): return False
infer_ctx = _Null()
# If tokenizer exposes get_lang_id, force BOS to target language
gen_kwargs = dict(max_new_tokens=256, num_beams=4, early_stopping=True)
try:
forced_bos = tok.get_lang_id(tgt_tag)
if forced_bos is not None:
gen_kwargs["forced_bos_token_id"] = forced_bos
except Exception:
pass
with infer_ctx:
out_ids = mod.generate(**enc, **gen_kwargs)
out = tok.batch_decode(out_ids, skip_special_tokens=True)
result = (out[0] if out else "").strip()
logger.info("[tx] _tx_translate_en_to: decode ok | len=%d", len(result))
return result or text
except Exception:
logger.exception("[tx] _tx_translate_en_to failed; returning original")
return text
def _ensure_tx_loaded() -> bool:
"""Ensure the translation models are in memory (_TX ready)."""
try:
global _TX
except NameError:
_TX = None # if not defined, create it
try:
if not _TX:
# If you have @st.cache_resource on _load_tx_models, this will still be cheap.
if "streamlit" in globals():
# Accessing via globals avoids import cycles in some layouts
pass
if not _TX or not isinstance(_TX, dict) or not _TX.get("ready"):
# Try to call your loader if it exists
loader = globals().get("_load_tx_models")
if callable(loader):
_TX = loader()
return bool(_TX and _TX.get("ready"))
except Exception:
return False
def _localize_msg(msg: str, tgt_lang: str) -> str:
"""Localize an English UI string to the target language (no-op if English)."""
try:
logger.info("[tx] _localize_msg: msg=%s | tgt_lang=%s", msg, tgt_lang)
if (tgt_lang or "en").lower().startswith("en"):
return msg
return _tx_translate_en_to(msg, tgt_lang)
except Exception:
logger.exception("[tx] _localize_msg failed; returning original")
return msg
def _safe_localize(msg: str, tgt_lang: str) -> str:
try:
return _localize_msg(msg, tgt_lang) or msg
except Exception:
return msg
# -----------------------------------------------------------------------
# Safe EN→target translation wrapper for UI strings (errors, notices, etc.)
def _tx_safe(text: str, tgt_lang: str | None) -> str:
"""
Best-effort translate an English message to `tgt_lang`.
Falls back to original English if translation is disabled or fails.
"""
logger.info("[tx] _tx_safe: text is =%s tgt_lang is =%s", text, tgt_lang)
if not text:
return ""
tgt = (tgt_lang or "").strip().lower()
# Pass-through for English/auto/blank
if tgt in ("", "en", "auto"):
return text
try:
# Respect feature flag if present (defaults to enabled)
if not globals().get("TRANSLATION_ENABLED", True):
return text
# Use your existing translator (should accept short codes like 'hi','mr')
out = _tx_from_en(text, tgt)
return text if out is None else str(out)
except Exception as e:
try:
logger.warning("[tx-safe] translate failed tgt=%s: %s", tgt, e)
except Exception:
pass
return text
def _get_lang_token_id(tok, tag: str) -> int | None:
"""
Resolve token id for a language tag; prefer >>tag<< then fall back to plain.
Return None if unresolved — we'll skip decoder forcing in that case.
"""
unk = getattr(tok, "unk_token_id", None)
for cand in (f">>{tag}<<", tag, f"<<{tag}>>"):
try:
tid = tok.convert_tokens_to_ids(cand)
if isinstance(tid, int) and tid != -1 and (unk is None or tid != unk):
logger.debug("[tx] lang token '%s' → id=%s", cand, tid)
return tid
except Exception:
pass
logger.warning("[tx] cannot resolve token id for '%s'; will skip decoder forcing", tag)
return None
# Ensure cache envs are set earlier as we discussed
# Translation loader (IndicTrans2) – safe, cached, and quiet
@st.cache_resource(show_spinner=False)
def _load_tx_models():
if not TRANSLATION_ENABLED:
return {"ready": False, "error": "Translation disabled"}
try:
# define nullcontext early
try:
from contextlib import nullcontext
except Exception:
class nullcontext:
def __enter__(self): return None
def __exit__(self, *a): return False
# reduce HF warnings
import warnings
warnings.simplefilter("ignore", category=UserWarning)
Shield = globals().get("_TorchNoiseStderrShield")
cm = Shield() if Shield else nullcontext()
with cm:
from transformers import AutoModelForSeq2SeqLM, AutoTokenizer
import torch
cache_dir = os.getenv("TRANSFORMERS_CACHE", "/data/hf-cache/transformers")
device = "cuda" if torch.cuda.is_available() else "cpu"
logger.info("[tx] loading IndicTrans2 (device=%s)", device)
with (Shield() if Shield else nullcontext()):
# Indic → English
indic_en_tok = AutoTokenizer.from_pretrained(
"ai4bharat/indictrans2-indic-en-dist-200M",
use_fast=False, trust_remote_code=True, cache_dir=cache_dir
)
indic_en_mod = AutoModelForSeq2SeqLM.from_pretrained(
"ai4bharat/indictrans2-indic-en-dist-200M",
trust_remote_code=True, cache_dir=cache_dir
).to(device)
# English → Indic
en_indic_tok = AutoTokenizer.from_pretrained(
"ai4bharat/indictrans2-en-indic-dist-200M",
use_fast=False, trust_remote_code=True, cache_dir=cache_dir
)
en_indic_mod = AutoModelForSeq2SeqLM.from_pretrained(
"ai4bharat/indictrans2-en-indic-dist-200M",
trust_remote_code=True, cache_dir=cache_dir
).to(device)
logger.info("[tx] IndicTrans2 models loaded (device=%s, trust_remote_code=True)", device)
return {
"ready": True, "device": device,
"indic_en_tok": indic_en_tok, "indic_en_mod": indic_en_mod,
"en_indic_tok": en_indic_tok, "en_indic_mod": en_indic_mod,
}
except Exception as e:
logger.error("[tx] load failed: %s", e, exc_info=True)
return {"ready": False, "error": str(e)}
# --- Language detection helpers (backend) ---
# Optional: langid
try:
import langid
_HAS_LANGID = True
try: langid.set_languages(['en','hi','mr'])
except Exception: pass
except Exception:
_HAS_LANGID = False
_DEVANAGARI_RANGE = ('\u0900', '\u097F')
_MAR_GLYPHS = set("ळऴऱय़ॲॅॉॐॠॡ")
_MAR_WORDS = {"आणि","माझ्या","यादी","कुठे","कधी","विक्री","ऑर्डर","दाखवा","एकूण"}
_HIN_WORDS = {"है","हूँ","सूची","कहाँ","कब","विक्रय","आदेश","दिखाइए","कुल"}
def _has_devanagari(text: str) -> bool:
return any(_DEVANAGARI_RANGE[0] <= ch <= _DEVANAGARI_RANGE[1] for ch in (text or ""))
def _score_words(text: str, lex: set[str]) -> int:
t = (text or "").lower()
return sum(1 for w in lex if w in t)
def _heuristic_hi_mr(text: str) -> str:
if not _has_devanagari(text):
return "en"
mar_score = sum(text.count(ch) for ch in _MAR_GLYPHS) + 2*_score_words(text,_MAR_WORDS)
hin_score = 2*_score_words(text,_HIN_WORDS)
if mar_score > hin_score: return "mr"
if hin_score > mar_score: return "hi"
return "hi"
def _detect_lang(text: str) -> str:
# 1) UI override
override = st.session_state.get("ui_lang_override")
if override in ("en","hi","mr"):
logger.debug("[lang] override=%s text=%s", override, _sample(text))
return override
# 2) langid
if _HAS_LANGID and (text or "").strip():
try:
lang, _ = langid.classify(text)
logger.debug("[lang] langid=%s text=%s", lang, _sample(text))
if lang in ("hi","mr"): return lang
if lang == "en" and not _has_devanagari(text): return "en"
except Exception as e:
logger.debug("[lang] langid error: %s", e)
# 3) heuristic
res = _heuristic_hi_mr(text)
logger.debug("[lang] heuristic=%s text=%s", res, _sample(text))
return res
def _tx_to_en_old(text: str, src_short: str) -> str:
"""Indic → English (prefix source tag + forced_bos to eng_Latn), with debug logs."""
if not text or not TRANSLATION_ENABLED or src_short == "en":
logger.debug("[tx→en] bypass (src=%s) text=%s", src_short, _sample(text))
return text
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx→en] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["indic_en_tok"]; mod = tx["indic_en_mod"]; device = tx["device"]
src_tag = LANG_CODE_MAP.get(src_short, "hin_Deva")
inp = _ensure_tag_prefixed(text, src_tag)
logger.debug("[tx→en] src_tag=%s device=%s inp=%s", src_tag, device, _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id("eng_Latn")
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx→en] ok translated=%s", _sample(result, 200))
return result
except AssertionError as e:
logger.error("[tx→en] tokenizer assertion: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
# hard fallback: try again with raw text (in case tag parsing misfired)
try:
raw = f">>{src_tag}<< " + (text or "").lstrip()
enc = tok([raw], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id("eng_Latn")
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx→en] retry translated=%s", _sample(result, 200))
return result
except Exception as e2:
logger.error("[tx→en] retry failed: %s", e2, exc_info=True)
return text
except Exception as e:
logger.error("[tx→en] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
return text
def _tx_from_en_old(text: str, tgt_short: str) -> str:
"""English → Indic (prefix eng_Latn + forced_bos to target), with debug logs."""
if not text or not TRANSLATION_ENABLED or tgt_short == "en":
logger.debug("[tx en→] bypass (tgt=%s) text=%s", tgt_short, _sample(text))
return text
logger.debug("_tx_from_en_old calling _load_tx_models")
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx en→] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["en_indic_tok"]; mod = tx["en_indic_mod"]; device = tx["device"]
tgt_tag = LANG_CODE_MAP.get(tgt_short, "hin_Deva")
inp = _ensure_tag_prefixed(text, "eng_Latn")
logger.debug("[tx en→] tgt_tag=%s device=%s inp=%s", tgt_tag, device, _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id(tgt_tag)
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx en→] ok translated=%s", _sample(result, 200))
return result
except Exception as e:
logger.error("[tx en→] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
return text
def _tx_to_en(text: str, src_short: str) -> str:
"""
Indic → English using TWO-PLAIN-TAGS '<src> <tgt> ' + text,
explicit decoder start, greedy decode, and use_cache=False.
"""
if not text or not TRANSLATION_ENABLED or src_short == "en":
logger.debug("[tx→en] bypass (src=%s) text=%s", src_short, _sample(text))
return text
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx→en] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["indic_en_tok"]; mod = tx["indic_en_mod"]; device = tx["device"]
mod.eval() # ensure eval mode
src_tag = LANG_CODE_MAP.get(src_short, "hin_Deva")
tgt_tag = "eng_Latn"
inp = f"{src_tag} {tgt_tag} " + (text or "").lstrip()
logger.debug("[tx→en] inp(two-tags)=%s", _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod.to(device)
tgt_id = _get_lang_token_id(tok, tgt_tag)
logger.debug("[tx→en] decoder_start_id=%s", tgt_id)
gen_kwargs = dict( # no beams, no sampling; avoid PKV path
do_sample=False,
num_beams=1,
max_new_tokens=192,
use_cache=False,
)
if tgt_id is not None:
decoder_input_ids = torch.tensor(
[[tgt_id]], device=enc["input_ids"].device, dtype=enc["input_ids"].dtype
)
gen_kwargs["decoder_input_ids"] = decoder_input_ids
with torch.no_grad():
out = mod.generate(
input_ids=enc["input_ids"],
attention_mask=enc.get("attention_mask"),
**gen_kwargs
)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx→en] OK → %s", _sample(result, 200))
return result
except Exception as e:
logger.error("[tx→en] failed: %s", e, exc_info=True)
return text
def _tx_from_en(text: str, tgt_short: str) -> str:
"""
English → Indic using TWO-PLAIN-TAGS 'eng_Latn <tgt> ' + text,
explicit decoder start, greedy decode, and use_cache=False.
"""
if not text or not TRANSLATION_ENABLED or tgt_short == "en":
logger.debug("[tx en→] bypass (tgt=%s) text=%s", tgt_short, _sample(text))
return text
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx en→] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["en_indic_tok"]; mod = tx["en_indic_mod"]; device = tx["device"]
mod.eval()
src_tag = "eng_Latn"
tgt_tag = LANG_CODE_MAP.get(tgt_short, "hin_Deva")
inp = f"{src_tag} {tgt_tag} " + (text or "").lstrip()
logger.debug("[tx en→] inp(two-tags)=%s", _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod.to(device)
tgt_id = _get_lang_token_id(tok, tgt_tag)
logger.debug("[tx en→] decoder_start_id=%s", tgt_id)
gen_kwargs = dict(
do_sample=False,
num_beams=1,
max_new_tokens=192,
use_cache=False,
)
if tgt_id is not None:
decoder_input_ids = torch.tensor(
[[tgt_id]], device=enc["input_ids"].device, dtype=enc["input_ids"].dtype
)
gen_kwargs["decoder_input_ids"] = decoder_input_ids
with torch.no_grad():
out = mod.generate(
input_ids=enc["input_ids"],
attention_mask=enc.get("attention_mask"),
**gen_kwargs
)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx en→] OK → %s", _sample(result, 200))
return result
except Exception as e:
logger.error("[tx en→] failed: %s", e, exc_info=True)
return text
# -----------------------------
# Core validation / scoping (continued)
# -----------------------------
def _extract_tables_from_sql(sql: str) -> list[str]:
if not isinstance(sql, str): return []
tables = []
for kw in ("FROM", "JOIN"):
for m in re.finditer(rf"\b{kw}\s+([A-Za-z_][A-Za-z0-9_+]*)", sql, flags=re.IGNORECASE):
tables.append(m.group(1).lower())
return tables
def _scope_sql_to_customer(sql: str, customer_id: str) -> str:
"""
Applies customer scope to orders/order_items/order_payments/order_reviews if referenced.
"""
s = _strip_trailing_semicolon(sql)
has_orders = re.search(r"(?:FROM|JOIN)\s+orders\b", s, re.IGNORECASE) is not None
has_oi = re.search(r"(?:FROM|JOIN)\s+order_items\b", s, re.IGNORECASE) is not None
has_op = re.search(r"(?:FROM|JOIN)\s+order_payments\b", s, re.IGNORECASE) is not None
has_or = re.search(r"(?:FROM|JOIN)\s+order_reviews\b", s, re.IGNORECASE) is not None
if not (has_orders or has_oi or has_op or has_or):
return s
# prefer alias if present
def ref_for(tbl):
alias, ok = _find_table_alias(s, tbl)
return alias if ok else tbl
o_ref, has_o_top = _top_level_alias(s, "orders")
oi_ref, has_oi_top = _top_level_alias(s, "order_items")
op_ref, has_op_top = _top_level_alias(s, "order_payments")
or_ref, has_or_top = _top_level_alias(s, "order_reviews")
# if orders table is present, scope directly
if has_o_top and o_ref:
cond = f"{o_ref}.customer_id = '{customer_id}'"
return _cleanup_where_and(_inject_where(s, cond))
# otherwise scope through EXISTS join back to orders
child_ref = None
if has_oi_top: child_ref = oi_ref
elif has_op_top: child_ref = op_ref
elif has_or_top: child_ref = or_ref
if child_ref:
if re.search(r"EXISTS\s*\(\s*SELECT\s+1\s+FROM\s+orders\s+\w+\s+WHERE", s, flags=re.IGNORECASE):
return s
cond = f"EXISTS (SELECT 1 FROM orders o WHERE o.order_id = {child_ref}.order_id AND o.customer_id = '{customer_id}')"
return _inject_where(s, cond)
return s
def _apply_policy(sql: str, role: str, user_text: str, customer_id: str | None) -> tuple[str | None, dict]:
"""
Validates, scopes, and enforces policy. Returns (secured_sql, meta_flags)
If blocked, secured_sql is None and meta_flags contain 'blocked_reason'/'policy_note'.
"""
s = (sql or "").strip()
flags = {}
# Hard blocks: write/DDL
if _is_mutating_sql(s):
flags["blocked_reason"] = "write_block"
s = _rewrite_limit_and(s)
return None, flags
# Hard block: schema introspection for normal users
if role == "user" and (_is_schema_introspection_sql(s) or _intent_schema_introspection(user_text)):
flags["blocked_reason"] = "introspection_user_block"
s = _rewrite_limit_and(s)
return None, flags
# Hard block: "dump all data" intents for normal users
if role == "user" and _intent_dump_all_data(user_text):
flags["blocked_reason"] = "dump_all_block"
s = _rewrite_limit_and(s)
return None, flags
# Hard block: row counts of ALL tables / EACH table
if _intent_rowcounts_all_tables(user_text):
flags["blocked_reason"] = "rowcounts_all_tables_block"
s = _rewrite_limit_and(s)
return None, flags
# Hard block: normal user cannot access 'customers' table directly
if role == "user":
tables = set(_extract_tables_from_sql(s))
if "customers" in tables:
flags["blocked_reason"] = "customers_user_block"
s = _rewrite_limit_and(s)
return None, flags
# Seller/Product revenue restrictions for normal user
if role == "user":
if SELLER_REVENUE_USER_MODE.upper() == "BLOCK" and _is_seller_revenue_sql(s):
flags["blocked_reason"] = "seller_revenue_user_block"; return None, flags
if PRODUCT_REVENUE_USER_MODE.upper() == "BLOCK" and _is_product_revenue_sql(s):
flags["blocked_reason"] = "product_revenue_user_block"; return None, flags
# Platform-wide payments: block or scope
if _is_payments_sql(s):
if role == "user" and PAYMENTS_USER_MODE == "BLOCK":
flags["blocked_reason"] = "platform_payments_user_block"; return None, flags
if role == "user" and PAYMENTS_USER_MODE == "SCOPE":
# scope to customer's payments even if query was platform-wide
s = _scope_sql_to_customer(s, customer_id)
# Row-count on any single table? For normal user, block.
if role == "user" and _is_user_count_query(s):
flags["blocked_reason"] = "count_user_block"; return None, flags
# Scope to current customer where applicable (orders/oi/op/or)
if customer_id and role == "user":
s = _scope_sql_to_customer(s, customer_id)
# Products/Sellers queries do not need customer scope (as per requirement)
s = _cleanup_where_and(s)
s = _rewrite_limit_and(s)
return s, flags
# Put this once near the top if not already present
_SCOPE_EXISTS_RE = re.compile(
r"""\b(?:o\.)?customer_id\s*=\s*(?:\?|['"][^'"]+['"])""",
re.IGNORECASE,
)
# --- add once near your other helpers (top-level is fine) ---
def _extract_queries_from_plan(plan_obj) -> list[str]:
"""
Normalize the plan["queries"] into a flat list of SQL strings.
Supports both list[str] and list[dict] with common keys.
"""
out: list[str] = []
if not isinstance(plan_obj, dict):
return out
raw = plan_obj.get("queries", [])
if not isinstance(raw, list):
return out
for item in raw:
if isinstance(item, str):
s = item.strip()
if s:
out.append(s)
elif isinstance(item, dict):
cand = (
item.get("secured")
or item.get("sql")
or item.get("original")
or item.get("text")
)
if isinstance(cand, str) and cand.strip():
out.append(cand.strip())
return out
def validate_sql(
sql: str,
role: str,
customer_id: Optional[str],
registry: dict,
user_text: str,
**_unused
) -> tuple[Optional[str], dict]:
# Work on a single string variable; don't overwrite later.
text = (sql or "").strip()
flags: dict = {}
params: list = []
# Scope normal users to their own orders if the query touches orders and isn't already scoped
touches_orders = bool(re.search(r'\b(from|join)\s+orders\b', text, re.I))
if role == "user" and touches_orders and not _SCOPE_EXISTS_RE.search(text):
# Choose alias safely
alias = "o" if re.search(r'\borders\s+o\b', text, re.I) else "orders"
text = _inject_where(text, f"{alias}.customer_id = ?")
if customer_id:
params.append(customer_id)
flags["scoped_orders"] = True
# Pagination guardrails
ut = (user_text or "")
wants_all = _wants_all(ut)
if _looks_like_select(text):
if role != "admin":
# enforce preview limit unless user explicitly asked for "all"
if not wants_all:
text = _cap_limit(text, 50)
else:
if not wants_all:
text = _cap_limit(text, 50)
# Optional product NOT IN clean-up (uses your existing helpers)
if _mentions_product(text):
alias_found, ok = _find_table_alias(text, "products")
if ok:
text = _remove_unscoped_notin_products(text, alias_found)
# Apply your higher-level policy last
secured, policy_flags = _apply_policy(text, role, ut, customer_id)
flags.update(policy_flags or {})
if secured is None:
return None, {"blocked": True, **policy_flags}
# Note if LIMIT present
if _has_limit_clause(secured):
policy_flags["limit_enforced"] = True
# Attach bound params so executor can use them without changing your return shape
if params:
flags["params"] = params
return secured, flags
# -----------------------------
# Planner + Runner
# -----------------------------
def handle_user_prompt(user_text: str, role: str, customer_id: str, registry: dict, source_lang: str = "en"):
"""
user_text is already English (translated if needed). source_lang is the user's original language ('en'|'hi'|'mr')
"""
trace_id = f"{int(time.time()*1000)}"
if TRACE_LOGGING:
logger.info("[trace=%s] user=%s role=%s text=%r lang=%s", trace_id, customer_id, role, user_text, source_lang)
# Early block: platform-wide payments
if role == "user" and PAYMENTS_USER_MODE == "BLOCK" and _intent_platform_wide_payments(user_text):
if TRACE_LOGGING:
logger.info("[trace=%s] Early BLOCK: platform-wide payments intent detected", trace_id)
plan = {"queries": [], "explanation": "Platform-wide payments are restricted to admins."}
# Translate explanation back if needed
plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang)
return {
"plan": plan,
"queries": [{"kind": "non_sql", "original": user_text, "secured": None,
"blocked_reason": "platform_payments_user_block",
"df": pd.DataFrame({"message": [
_tx_from_en("Platform-wide payments are restricted to admins. Try: 'Show my payments'.", source_lang)
]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: schema introspection ("list tables", "show schema", etc.) is admin-only
if role == "user" and _intent_schema_introspection(user_text):
if TRACE_LOGGING:
logger.info("[trace=%s] Early BLOCK: schema-introspection intent detected", trace_id)
plan = {"queries": [], "explanation": "Listing tables/indexes/schema is admin-only."}
plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang)
return {
"plan": plan,
"queries": [{
"original": user_text, "secured": None,
"blocked_reason": "introspection_user_block",
"df": pd.DataFrame({"message": [_tx_from_en(plan["explanation"], source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: row counts across all tables / each table
if _intent_rowcounts_all_tables(user_text):
msg = "Requests for row counts across all tables / each table are restricted and not supported."
plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)}
return {
"plan": plan,
"queries": [{
"original": user_text,
"secured": None,
"blocked_reason": "rowcounts_all_tables_block",
"df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: dump-all-data for normal users
if role == "user" and _intent_dump_all_data(user_text):
msg = "Requests to list/dump data across all tables are restricted."
plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)}
return {
"plan": plan,
"queries": [{
"original": user_text, "secured": None,
"blocked_reason": "dump_all_block",
"df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
llm = get_llm_client(LLM_CHOICE)
# Early block: destructive intents (delete/update/drop...) are not allowed
if classify_destructive_intent_with_llm(llm, user_text):
_msg = "I can’t delete or modify data. I’m a read‑only analytics assistant. Try: 'show my latest order'."
plan = {"queries": [], "explanation": _msg, "explanation_display": _tx_from_en(_msg, source_lang)}
return {
"plan": plan,
"queries": [{
"original": user_text,
"secured": None,
"blocked_reason": "destructive_request_block",
"df": pd.DataFrame({"message": [_tx_from_en(_msg, source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
wants_all = _wants_all(user_text)
with timed_step("LLM plan", trace_id):
t0 = time.perf_counter()
plan = plan_sql(llm, user_text, registry)
# NEW: extract and cap queries for execution
queries = _extract_queries_from_plan(plan)
llm_elapsed = time.perf_counter() - t0
# Prepare output structure
out_queries = []
for q in queries[:2]:
entry = {"original": q, "secured": None, "error": None, "df": None, "sql_time": None}
try:
secured, flags = validate_sql(q, role, customer_id, registry, user_text)
params = flags.get("params")
# If there’s a “?” in the SQL but no params surfaced (edge case), use customer_id defensively:
if params is None and "?" in (secured or "") and customer_id:
params = [customer_id]
# --- policy blocks / early exit ---
if secured is None:
entry.update(flags)
reason = flags.get("blocked_reason") or "blocked"
msg_map = {
"seller_revenue_user_block": "Seller revenue queries are restricted to admins.",
"product_revenue_user_block": "Product revenue queries are restricted to admins.",
"introspection_user_block": "Listing tables/indexes/schema is admin-only.",
"dump_all_block": "Requests to list/dump all database data are restricted.",
"rowcounts_all_tables_block": "Requests for row counts across all tables / each table are restricted.",
"count_user_block": "Row-count (COUNT) queries are restricted for normal users.",
"customers_user_block": "Access to the customers table is restricted for normal users.",
"platform_payments_user_block": "Platform-wide payments are restricted to admins.",
"blocked": "This request is restricted by policy.",
}
# Tailor message if this is a customers table block and user provided an order-id-like token
_suggest = None
if reason == "customers_user_block":
try:
import re as _re_hint
_m = _re_hint.search(r"\b[0-9a-f]{32}\b", (user_text or ""), _re_hint.IGNORECASE)
if _m:
_oid = _m.group(0)
_suggest = f"Did you want the status of an order? If yes, please type: pls provide details of my order {_oid}"
except Exception:
_suggest = None
_msg = _suggest or msg_map.get(reason, msg_map["blocked"])
entry["df"] = pd.DataFrame({"message": [_tx_safe(_msg, source_lang)]})
out_queries.append(entry)
continue
# --- allowed path ---
entry.update(flags)
entry["secured"] = secured
sql_for_exec = (secured, params) if params else secured
t1 = time.perf_counter()
df, rows, kind = run_sql_generic(sql_for_exec)
entry["sql_time"] = time.perf_counter() - t1
entry["df"] = df if kind == "select" else pd.DataFrame({"rows_affected": [rows]})
# optional warn note
revenue_mode = (os.getenv("REVENUE_USER_MODE") or os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK")).upper()
if role == "user" and _is_seller_revenue_sql(secured) and revenue_mode == "WARN":
entry["policy_note"] = _tx_safe(
"Note: revenue-style queries for sellers/products are limited for normal users.",
source_lang
)
except SqlTimeoutError as e:
entry["error"] = _tx_safe(str(e), source_lang)
except Exception as e:
entry["error"] = _tx_safe(str(e), source_lang)
out_queries.append(entry)
# plan explanation (safe)
expl = (SHOW_EXPLAIN_PLAN and plan.get("explanation"))
plan["explanation_display"] = _tx_safe(expl, source_lang) if isinstance(expl, str) and expl.strip() else None
return {"plan": plan, "queries": out_queries, "llm_time": llm_elapsed}
# -----------------------------
# Classification, history, feedback, MIS (unchanged logic)
# -----------------------------
@st.cache_data(ttl=30)
def load_chat_history(days: int | None = 30, only_user_id: str | None = None) -> pd.DataFrame:
# Start with the base SELECT (use triple quotes for multi-line string)
q = """
SELECT
chat_id,
created_at,
user_id,
role,
user_input,
intent,
sentiment,
llm_response_time,
secured_sql,
COALESCE(feedback_value, feedback) AS feedback
FROM chat_history
"""
conds, params = [], []
if days is not None:
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
conds.append("created_at >= ?")
params.append(since)
if only_user_id:
conds.append("user_id = ?")
params.append(only_user_id)
if conds:
q += " WHERE " + " AND ".join(conds)
q += " ORDER BY datetime(created_at) DESC LIMIT 50"
with get_conn() as conn:
df = pd.read_sql_query(q, conn, params=params)
return df
@st.cache_data(ttl=30, show_spinner=False)
def _feedback_stats_for_user(user_id: str | None, days: int | None):
"""
Returns a dict with helpful_count (feedback=1), not_helpful_count (feedback=0),
total_feedback, helpful_pct — filtered by user and timeframe.
"""
q = """
SELECT
COUNT(*) AS total_feedback,
SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) AS helpful_count,
SUM(CASE WHEN feedback = 0 THEN 1 ELSE 0 END) AS not_helpful_count,
ROUND(100.0 * SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS helpful_pct
FROM chat_history
WHERE (role = 'user' OR role IS NULL)
"""
params = []
if user_id:
q += " AND user_id = ?"
params.append(user_id)
if days is not None:
# inclusive lower bound by timestamp
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
q += " AND created_at >= ?"
params.append(since)
with get_conn() as conn:
row = pd.read_sql_query(q, conn, params=params).iloc[0].to_dict()
# normalize Nones
for k in ("total_feedback", "helpful_count", "not_helpful_count"):
row[k] = int(row.get(k) or 0)
row["helpful_pct"] = float(row.get("helpful_pct") or 0.0)
return row
@st.cache_data(show_spinner=False, ttl=30)
def get_unrated_conversations_for_user(user_id: int, limit: int = 100):
sql = """
WITH user_convos AS (
SELECT conversation_id, MAX(created_at) AS last_time
FROM chat_history
WHERE user_id = ? AND conversation_id IS NOT NULL
GROUP BY conversation_id
),
rated AS (
SELECT DISTINCT conversation_id
FROM chat_history
WHERE user_id = ? AND conversation_id IS NOT NULL AND feedback_value IS NOT NULL
)
SELECT uc.conversation_id, uc.last_time
FROM user_convos uc
WHERE uc.conversation_id NOT IN (SELECT conversation_id FROM rated)
ORDER BY uc.last_time DESC
LIMIT ?
"""
with get_conn() as conn:
df = pd.read_sql_query(sql, conn, params=[user_id, user_id, limit])
return df
def record_conversation_feedback(get_conn, *, user_id: int, conversation_id: str, value: int, text: str | None = None) -> int:
"""
Insert a single feedback row. Returns inserted row id.
value: +1 for thumbs up, -1 for thumbs down
"""
now = datetime.utcnow().isoformat(timespec="seconds")
fb_logger.info("record_conversation_feedback called user=%s cid=%s value=%s text=%s",
user_id, conversation_id, value, (text or ""))
with get_conn() as conn:
# Safety: if already rated, log & short-circuit
cur_check = conn.execute(
"""
SELECT id, feedback_value, feedback_at
FROM chat_history
WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL
ORDER BY feedback_at DESC
LIMIT 1
""",
(user_id, conversation_id),
)
existing = cur_check.fetchone()
if existing:
fb_logger.warning("Feedback already exists for user=%s cid=%s -> row=%s", user_id, conversation_id, existing)
return existing[0]
sql = """
INSERT INTO chat_history (
user_id, role, conversation_id,
input_text, response_text,
sentiment, intent,
feedback_value, feedback_text, feedback_at,
created_at
) VALUES (?,?,?,?,?,?,?,?,?,?,?)
"""
params = [
user_id, "user", conversation_id,
"[feedback]", None,
"neutral", "feedback",
value, text, now,
now,
]
fb_logger.debug("About to INSERT feedback: %s", params)
cur = conn.execute(sql, params)
conn.commit()
rowid = cur.lastrowid
fb_logger.info("Feedback INSERT committed rowid=%s", rowid)
# Verify readback
cur2 = conn.execute("SELECT conversation_id, feedback_value, feedback_at FROM chat_history WHERE id=?", (rowid,))
row2 = cur2.fetchone()
fb_logger.debug("Readback row: %s", row2)
return rowid
import sqlite3
from datetime import datetime
def _ensure_sql_string(x):
"""Coerce validator output into a SQL string."""
if x is None:
return ""
if isinstance(x, str):
return x
# common shapes: (sql, reason) or [sql, reason]
if isinstance(x, (tuple, list)) and x:
return _ensure_sql_string(x[0])
# dict shapes from some planners/validators
if isinstance(x, dict):
for k in ("secured", "sql", "text", "query", "original"):
v = x.get(k)
if isinstance(v, str):
return v
# last resort: stringify
return str(x)
def update_chat_feedback(chat_id: int, feedback_value: int) -> Tuple[bool, str]:
"""
Update feedback for the most recent chat (current session) regardless of owner.
Returns (ok, msg).
"""
fb_logger.info("update_chat_feedback -- start chat_id=%s value=%s", chat_id, feedback_value)
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"""
UPDATE chat_history
SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP
WHERE chat_id = ?
""",
(feedback_value, chat_id),
)
conn.commit()
if cur.rowcount == 0:
fb_logger.warning("update_chat_feedback -- no row updated chat_id=%s", chat_id)
return False, "Could not record feedback (chat not found)."
fb_logger.info("update_chat_feedback -- success chat_id=%s value=%s", chat_id, feedback_value)
return True, "Thanks for the feedback!"
except sqlite3.OperationalError as e:
fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e)
return False, "Database busy—please try again."
except Exception as e:
fb_logger.exception("unexpected error on feedback update chat_id=%s", chat_id)
return False, "Failed to record feedback."
def get_chat_feedback(chat_id: int) -> Optional[int]:
"""Return 1/0 if feedback exists, else None."""
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"SELECT feedback_value FROM chat_history WHERE chat_id = ?",
(chat_id,),
)
row = cur.fetchone()
if not row:
return None
return row[0] if row[0] is not None else None
except Exception as e:
fb_logger.error("get_chat_feedback failed chat_id=%s err=%s", chat_id, e)
return None
_log_feedback_event('rate_tab.submit.call', hint='update_chat_feedback_if_owner\\s*\\')
def update_chat_feedback_if_owner(chat_id: int, user_id: str, feedback_value: int) -> Tuple[bool, str]:
"""
Update feedback only if the given user owns the chat row.
Used in 'Rate Earlier Conversations'.
"""
fb_logger.info("update_chat_feedback_if_owner called chat_id=%s user_id=%s value=%s",
chat_id, user_id, feedback_value)
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"""
UPDATE chat_history
SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP
WHERE chat_id = ? AND user_id = ?
""",
(feedback_value, chat_id, user_id),
)
conn.commit()
if cur.rowcount == 0:
fb_logger.warning("update_chat_feedback_if_owner -- no row updated chat_id=%s user_id=%s",
chat_id, user_id)
return False, "You can only rate your own conversations."
fb_logger.info("update_chat_feedback_if_owner -- success chat_id=%s value=%s", chat_id, feedback_value)
return True, "Thanks for the feedback!"
except sqlite3.OperationalError as e:
fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e)
return False, "Database busy—please try again."
except Exception as e:
fb_logger.exception("unexpected error on owner feedback update chat_id=%s user_id=%s", chat_id, user_id)
return False, "Failed to record feedback."
def render_rate_earlier_conversation_tab(auth):
_log_feedback_event('update_chat_feedback_if_owner.begin', locals=locals().copy())
st.subheader("Rate Earlier Conversations")
fb_logger.info("Rate tab opened for user_id=%s", auth["user_id"])
unrated = get_unrated_conversations_for_user(auth["user_id"], limit=200)
fb_logger.info("Unrated conversations fetched: %d", len(unrated))
if unrated.empty:
st.success("No pending conversations to rate. 🙌")
_log_feedback_event('update_chat_feedback_if_owner.end', locals=locals().copy())
return
# Use the actual conversation_id as the select value; label just for display.
conv_ids = unrated["conversation_id"].tolist()
def _conv_label(cid: str) -> str:
with get_conn() as conn:
row = conn.execute(
"""
SELECT created_at, input_text
FROM chat_history
WHERE conversation_id = ? AND user_id = ?
ORDER BY created_at DESC
LIMIT 1
""",
(cid, auth["user_id"]),
).fetchone()
ts = row[0] if row else ""
prev = (row[1][:60] + "…") if row and row[1] else ""
_log_feedback_event('update_chat_feedback_if_owner.end', locals=locals().copy())
return f"#{cid} • {ts} • {prev}"
selected_conversation_id = st.selectbox(
"Pick a conversation to preview & rate:",
options=conv_ids,
format_func=_conv_label,
index=0,
key="select_conv_to_rate",
)
fb_logger.debug("Selected conversation_id=%s", selected_conversation_id)
if st.button("Preview Result", key="preview_conv"):
fb_logger.info("Preview clicked for cid=%s", selected_conversation_id)
with get_conn() as conn:
sel = pd.read_sql_query(
"""
SELECT *
FROM chat_history
WHERE conversation_id = ? AND user_id = ?
ORDER BY created_at DESC
LIMIT 10
""",
conn, params=[selected_conversation_id, auth["user_id"]]
)
st.markdown("**Recent Messages**")
if sel.empty:
st.info("No rows found to preview for this conversation.")
else:
for _, r in sel.iterrows():
who = r.get("role", "user")
txt = r.get("response_text") if who != "user" else r.get("input_text")
st.markdown(f"**{who.capitalize()}** — {r.get('created_at','')}")
st.code(str(txt or "").strip()[:2000], language="markdown")
# (Optional) add your SQL preview here as before
cols = st.columns([1,1,3])
with cols[0]:
up = st.button("👍 Thumbs Up", use_container_width=True, key="fb_up")
with cols[1]:
down = st.button("👎 Thumbs Down", use_container_width=True, key="fb_down")
with cols[2]:
fb_txt = st.text_input("Optional note", key="fb_note")
if up or down:
value = 1 if up else -1
fb_logger.info("Feedback button pressed: value=%s cid=%s note=%s", value, selected_conversation_id, fb_txt)
try:
rowid = record_conversation_feedback(
get_conn,
user_id=auth["user_id"],
conversation_id=selected_conversation_id,
value=value,
text=(fb_txt or None),
)
st.success(_safe_localize("Thanks! Your feedback has been recorded.", src_lang))
fb_logger.info("Feedback saved rowid=%s; clearing caches and rerunning", rowid)
# Clear caches so the just-rated convo disappears immediately
try:
get_unrated_conversations_for_user.clear()
fb_logger.debug("Cleared cache for get_unrated_conversations_for_user")
except Exception as e:
st.cache_data.clear()
fb_logger.warning("Fallback cache clear used: %s", e)
st.rerun()
except Exception as e:
fb_logger.exception("Feedback insert failed for cid=%s", selected_conversation_id)
st.error("Failed to save feedback. Please try again.")
def _kpi_number(label, value, help_text=None):
c1 = st.container(); c1.metric(label, value)
if help_text: c1.caption(help_text)
def _safe_pct(n, d): return (100.0 * n / d) if d else 0.0
def _json_obj(x):
"""Return Python object if x is JSON (or already a py obj); else None."""
if x is None:
return None
if isinstance(x, (dict, list)):
return x
if isinstance(x, str):
s = x.strip()
if not s:
return None
try:
return json.loads(s)
except Exception:
return None
return None
def _json_list(x):
"""
Normalize x into a list.
- If x is JSON list -> return it
- If x is JSON dict -> try common keys
- If x is plain string -> split on ; / newlines
- Else -> []
"""
obj = _json_obj(x)
if obj is None:
if isinstance(x, str):
parts = [p.strip() for p in re.split(r"[;\n]+", x) if p.strip()]
return parts
return []
if isinstance(obj, list):
return obj
if isinstance(obj, dict):
for k in ("secured_sql", "sql", "queries", "items"):
v = obj.get(k)
if isinstance(v, list):
return v
return []
def _json_plan_queries(x):
try:
obj = json.loads(x) if isinstance(x, str) else (x or {})
qs = obj.get("queries", [])
return qs if isinstance(qs, list) else []
except Exception:
return []
def _norm_sql(s: str) -> str:
if not isinstance(s, str): return ""
return re.sub(r"\s+", " ", s).strip().upper()
def _auto_repaired(row) -> bool:
plan_qs = _json_plan_queries(row.get("llm_plan"))
sec_qs = _json_list(row.get("secured_sql"))
if len(plan_qs) != len(sec_qs):
return True
for a, b in zip(plan_qs, sec_qs):
if _norm_sql(a) != _norm_sql(b):
return True
return False
def _pill(text: str, tone: str = "neutral"):
"""Render a small rounded pill; tone in {'positive','neutral','negative'}."""
palette = {
"positive": ("#065f46", "#d1fae5"), # dark text, light bg
"neutral": ("#374151", "#e5e7eb"),
"negative": ("#7f1d1d", "#fee2e2"),
}
fg, bg = palette.get(tone, palette["neutral"])
return f"""
<span style="
display:inline-block; padding:6px 10px; border-radius:999px;
font-weight:600; font-size:0.9rem;
color:{fg}; background:{bg}; border:1px solid rgba(0,0,0,0.05);
">{text}</span>
"""
def render_df_with_pagination(df: pd.DataFrame, key_prefix: str, page_size: int = 10):
total = len(df)
if total <= page_size:
st.dataframe(df, use_container_width=True)
st.caption(f"Showing 1–{total} of {total} rows.")
return
total_pages = math.ceil(total / page_size)
page = st.number_input(
"Page",
min_value=1, max_value=total_pages, value=1, step=1,
key=f"{key_prefix}_page"
)
start = (page - 1) * page_size
end = min(start + page_size, total)
st.dataframe(df.iloc[start:end], use_container_width=True)
st.caption(f"Showing rows {start+1}–{end} of {total} (page {page}/{total_pages}).")
def render_chat_output(out):
# BEGIN: hide explain plan unless explicitly enabled
try:
if not SHOW_EXPLAIN_PLAN:
_plan = None
try:
_plan = (out.get("plan") or {}) if isinstance(out, dict) else None
except Exception:
_plan = None
if isinstance(_plan, dict):
_plan["explanation_display"] = None
_plan["explanation"] = None
out["plan"] = _plan
if isinstance(out, dict):
if "explanation_display" in out:
out["explanation_display"] = None
if "explanation" in out:
out["explanation"] = None
except Exception:
pass
# END: hide explain plan
import streamlit as st
import pandas as pd
# Normalize objects
out = out or {}
plan = out.get("plan") or {}
queries = out.get("queries") or []
if out.get("src_lang"):
src_lang = out.get("src_lang")
else:
src_lang = "en"
# Prefer a simple chat bubble if 'text' is present
if out.get("text"):
st.chat_message("assistant").write(out["text"])
return # <-- IMPORTANT: stop here, do not render tables/cards
# Detect policy/guard conditions
blocked = bool(plan.get("blocked") or out.get("blocked") or any((q or {}).get("blocked_reason") for q in queries))
# Prefer explicit display message if present
display_msg = plan.get("explanation_display") or out.get("msg") or plan.get("explanation")
# If this is a hard block, show the guardrail and EXIT early
if blocked:
st.warning(display_msg or "This action is restricted.")
# Also render any specific per-card warnings, if present
for q in queries:
br = (q or {}).get("blocked_reason")
if br == "seller_revenue_user_block":
st.warning("🔒 Seller revenue queries are not available to normal users. Please contact an admin.")
if br == "product_revenue_user_block":
st.warning("🔒 Product revenue queries are not available to normal users. Please contact an admin.")
if br == "platform_payments_user_block":
st.warning("🔒 Platform-wide payments are restricted to admins.")
if br == "rowcounts_all_tables_block":
st.warning("🔒 Requests for row counts across all tables / each table are restricted.")
if br == "write_block":
st.warning("🔒 Write/DDL operations are disabled.")
if br == "introspection_user_block":
st.warning("🔒 Listing tables/indexes/schema is admin-only.")
if br == "dump_all_block":
st.warning("🔒 Requests to list/dump all database data are restricted.")
if br == "count_user_block":
st.warning("🔒 Row-count (COUNT) queries are restricted for normal users.")
if br == "customers_user_block":
st.warning("🔒 Access to the **customers** table is restricted for normal users.")
if br == "destructive_request_block":
st.warning("🔒 Destructive actions (delete/update/drop) are not allowed.")
if (q or {}).get("policy_note"):
st.info(q["policy_note"])
return
# Not blocked: optionally show any informative message, but DO NOT exit early
if display_msg:
# Demote to info so results still render below
st.info(display_msg)
# ---------------------------------------------------------------------
# ✅ Reconciled UI block (as requested): renders tables/queries/results
# ---------------------------------------------------------------------
_assistant_msg = "Pls find below the data for the input you provided"
tgt_lang = st.session_state.get("last_user_lang", "en")
logger.info("[tx] about to localize assistant bubble: tgt_lang=%s", tgt_lang)
with st.chat_message("assistant"):
st.write(_safe_localize(_assistant_msg, tgt_lang))
cols = st.columns(max(1, len(queries)))
for i, card in enumerate(queries):
card = card or {}
with cols[i]:
pill = "Limited to 50" if card.get("limit_enforced") else None
if SHOW_DETAILS:
card_open(f"Query {i+1}", pill)
sql_text = (card.get("secured") or card.get("original") or "").strip()
st.markdown('<div class="oa-sql">', unsafe_allow_html=True)
st.code(sql_text if sql_text else "-- no SQL generated --", language="sql")
st.markdown('</div>', unsafe_allow_html=True)
br = card.get("blocked_reason")
if br == "seller_revenue_user_block":
st.warning(_safe_localize("🔒 Seller revenue queries are not available to normal users. Please contact an admin.",tgt_lang ))
if br == "product_revenue_user_block":
st.warning(_safe_localize("🔒 Product revenue queries are not available to normal users. Please contact an admin.",tgt_lang ))
if br == "platform_payments_user_block":
st.warning(_safe_localize("🔒 Platform-wide payments are restricted to admins.",tgt_lang ))
if br == "rowcounts_all_tables_block":
st.warning(_safe_localize("🔒 Requests for row counts across all tables / each table are restricted.",tgt_lang ))
if br == "write_block":
st.warning(_safe_localize("🔒 Write/DDL operations are disabled.",tgt_lang ))
if br == "introspection_user_block":
st.warning(_safe_localize("🔒 Listing tables/indexes/schema is admin-only.",tgt_lang ))
if br == "dump_all_block":
st.warning(_safe_localize("🔒 Requests to list/dump all database data are restricted.",tgt_lang ))
if br == "count_user_block":
st.warning(_safe_localize("🔒 Row-count (COUNT) queries are restricted for normal users.",tgt_lang ))
if br == "customers_user_block":
st.warning(_safe_localize("🔒 Access to the **customers** table is restricted for normal users.",tgt_lang ))
if br == "destructive_request_block":
st.warning(_safe_localize("🔒 Destructive actions (delete/update/drop) are not allowed.",tgt_lang ))
if card.get("policy_note"):
st.info(_safe_localize(str(card["policy_note"]), tgt_lang))
if card.get("error"):
st.error(_safe_localize(str(card["error"]), tgt_lang))
else:
df_to_show = card.get("df", None)
if isinstance(df_to_show, pd.DataFrame):
if card.get("limit_enforced"):
st.markdown(
'<div class="oa-note">For performance, results are limited to <b>50</b> rows. '
'Include the word <b>“All”</b> in your question to bypass this cap (may be slower).</div>',
unsafe_allow_html=True
)
if df_to_show.empty:
st.info(_safe_localize("No rows returned.",tgt_lang ))
else:
_role = (st.session_state.get("auth") or {}).get("role", "").lower()
if _role == "admin":
cfg = _infer_columns_for_chart(df_to_show)
if cfg:
c_tbl, c_plt = st.columns(2, gap="large")
with c_tbl:
_call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10)
with c_plt:
_render_chart_matplotlib(cfg, df_to_show)
else:
_call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10)
else:
_call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10)
else:
st.info("No tabular result to display.")
def _apply_preview_limit(sql: str, limit: int = 10) -> str:
"""
Ensure the SQL has LIMIT <= limit for SELECT previews.
If non-SELECT, return as-is.
"""
s = (sql or "").strip().rstrip(";")
if not re.match(r"^\s*select\b", s, flags=re.I):
return s
m = re.search(r"\blimit\s+(\d+)\b", s, flags=re.I)
if m:
try:
current = int(m.group(1))
if current > limit:
s = re.sub(r"\blimit\s+\d+\b", f"LIMIT {limit}", s, flags=re.I)
except Exception:
s = f"{s} LIMIT {limit}"
else:
s = f"{s} LIMIT {limit}"
return s
# --- IST date helpers & audit DAO -------------------------------------------
from datetime import datetime, date
# --- Visibility of explain/plan in chat output ---
SHOW_EXPLAIN_PLAN = False # set True to show, default hides
def _ensure_repopulate_logger():
"""Dedicated logger for table repopulation with no duplicate handlers on rerun."""
lg = logging.getLogger("olist.repopulate")
if not getattr(lg, "_initialized", False):
lg.setLevel(logging.DEBUG)
fmt = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")
ch = logging.StreamHandler(stream=sys.stdout)
ch.setLevel(logging.DEBUG)
ch.setFormatter(fmt)
setattr(ch, "_olist_marker", True)
lg.addHandler(ch)
try:
os.makedirs("/data", exist_ok=True)
fh = logging.FileHandler("/data/repopulate.log", encoding="utf-8")
fh.setLevel(logging.DEBUG)
fh.setFormatter(fmt)
setattr(fh, "_olist_marker", True)
lg.addHandler(fh)
except Exception as e:
logging.getLogger("olist").warning("file logging disabled: %s", e)
lg.propagate = False
lg._initialized = True
return lg
def _ensure_translation_table(conn):
"""Create the optional Olist translation table if it doesn't exist."""
try:
logger.info("_ensure_translation_table starts")
conn.execute(
"CREATE TABLE IF NOT EXISTS product_category_name_translation ("
"product_category_name TEXT, "
"product_category_name_english TEXT"
")"
)
conn.commit()
logger.info("_ensure_translation_table Completed successfully")
except Exception:
# best-effort; never block startup
pass
try:
from zoneinfo import ZoneInfo
except ImportError:
from backports.zoneinfo import ZoneInfo # only if needed
IST = ZoneInfo("Asia/Kolkata")
def ist_today() -> date:
return datetime.now(IST).date()
def _date_yyyymmdd(d: date) -> str:
return d.strftime("%Y-%m-%d")
def count_login_audit_range(start_d: date, end_d: date) -> int:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_apply_busy_timeout(conn)
cur = conn.execute(
"""
SELECT COUNT(*)
FROM login_audit
WHERE date(created_at) BETWEEN ? AND ?
""",
(_date_yyyymmdd(start_d), _date_yyyymmdd(end_d)),
)
row = cur.fetchone()
return int(row[0] if row else 0)
def get_login_audit_range(start_d: date, end_d: date, offset: int = 0, limit: int = 10) -> pd.DataFrame:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_apply_busy_timeout(conn)
sql = """
SELECT
id AS audit_id,
created_at AS ts,
email,
user_id,
success,
reason
FROM login_audit
WHERE date(created_at) BETWEEN ? AND ?
ORDER BY datetime(created_at) DESC
LIMIT ? OFFSET ?;
"""
return pd.read_sql_query(
sql, conn,
params=(_date_yyyymmdd(start_d), _date_yyyymmdd(end_d), int(limit), int(offset))
)
# --- You already have: classify_sentiment_and_intent, insert_chat_history,
# render_feedback_controls, admin utils, etc. Keeping that logic intact.
# (Your existing functions are below, unchanged.)
# -----------------------------
# Startup
# -----------------------------
try:
dbg("Starting bootstrap_db_once()")
_init_status = bootstrap_db_once() # cached, runs once per process
dbg(f"DB init bootstrap status: {_init_status}")
schema_registry = load_schema_registry()
dbg("Schema loaded")
except Exception as e:
st.error("Startup failed.")
st.exception(e)
st.stop()
# -----------------------------
# Auth Gate
# -----------------------------
if "auth" not in st.session_state:
st.session_state["auth"] = None
if st.session_state.get("auth") is None:
if USE_BRANDING:
ui_header(None)
st.info("Login required")
auth_obj = simple_login_screen()
if auth_obj:
st.session_state["auth"] = auth_obj
st.rerun()
st.stop()
auth = st.session_state["auth"]
if USE_BRANDING:
ui_header(auth)
with st.sidebar:
st.success(f"Signed in as {auth['email']} ({auth['role']})")
if auth["role"] == "user":
st.caption(f"customer_id: {auth['customer_id']}")
if st.button("Sign out"):
st.session_state["auth"] = None
st.rerun()
def ensure_rag_published_schema(conn: sqlite3.Connection):
# Minimal ledger to prevent re-publishing the same chat row to RAG.
# Stores an arbitrary primary key string for the source row.
conn.execute("""
CREATE TABLE IF NOT EXISTS rag_published (
pub_key TEXT PRIMARY KEY,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
""")
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_rag_pub_created ON rag_published(created_at);")
except Exception:
pass
conn.commit()
def mark_published(pub_key: str):
if not pub_key:
return
try:
with sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) as _conn:
_conn.execute("INSERT OR IGNORE INTO rag_published (pub_key) VALUES (?)", (str(pub_key),))
_conn.commit()
except Exception as e:
logger.warning("mark_published failed for key=%s: %s", pub_key, e)
def publish_exemplar_from_row(row: dict, registry: dict, policy_version: str = "v1"):
try:
intent = row.get("intent") or "unknown"
question = row.get("user_input") or ""
plan_after = row.get("plan_after") or row.get("llm_plan") or ""
sql_after = row.get("sql_after") or row.get("secured_sql") or ""
sql_after = _ensure_sql_string(sql_after)
schema_fp = _schema_fingerprint(registry)
# make a simple, deterministic tables_used string
sql_lc = (sql_after or "").lower()
used = sorted([t for t in registry.keys() if t.lower() in sql_lc])
tables_used_str = ",".join(used) # <-- string, not list
ex_text = exemplar_text(
intent,
question,
schema_fp,
plan_after if isinstance(plan_after, str) else json.dumps(plan_after, ensure_ascii=False),
sql_after
)
ex_id = hashlib.sha256(
(intent + "|" + canonicalize_sql(sql_after) + "|" + schema_fp).encode("utf-8")
).hexdigest()[:24]
metadata = {
"tag": "exemplar",
"intent": intent, # str
"user_id": user_id,
"chat_id": chat_id,
"schema_fp": schema_fp, # str
"tables_used": tables_used_str, # <-- now str
"policy_version": policy_version, # str
"created_at": datetime.utcnow().isoformat(), # str
}
logger.info("[rag-publish] target dir=%s col=%s", registry.get("chroma_dir"), registry.get("chroma_collection"))
logger.info("[rag-publish] about to upsert ids=%s meta=%s", [ex_id], metadata)
RAG.upsert_exemplar(ex_id, ex_text, metadata)
logger.info("[rag-publish] upserted exemplar id=%s", ex_id)
return ex_id
except Exception as e:
logger.exception("Failed to publish exemplar: %s", e)
return None
def admin_rag_publisher(registry: dict):
#st.header("RAG Publisher (Admin)")
st.caption("Publish vetted exemplars into the retrieval store to improve future SQL generation.")
with st.expander("Build from recent positive chats", expanded=True):
try:
STOP_INTENTS = {"help_greeting", "thanks", "farewell", "smalltalk", "joke", "chitchat"}
# ---------- 1) Discover chat_history schema ----------
with get_conn() as conn:
cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn)
# Ensure rag_published exists
ensure_rag_published_schema(conn)
cols = set(c.lower() for c in cols_df["name"].astype(str))
# Core columns (fallbacks)
if "id" in cols:
id_col_expr = "id"
elif "chat_id" in cols:
id_col_expr = "chat_id"
else:
id_col_expr = "rowid"
id_select = f"CAST({id_col_expr} AS TEXT) AS pub_key"
ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"]
ts_col = next((c for c in ts_candidates if c.lower() in cols), None)
fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None)
if fb_col is None:
st.warning("No feedback column found in chat_history; cannot filter positives.")
st.stop()
# Only positives
fb_pred = f"{fb_col} = 1" if fb_col == "feedback_value" else f"LOWER({fb_col}) IN ('up','thumbs_up','positive','1','true','yes')"
# SQL column
sql_col = "secured_sql" if "secured_sql" in cols else ("sql" if "sql" in cols else None)
if sql_col is None:
st.warning("No SQL column (secured_sql/sql) found in chat_history.")
st.stop()
# Optional columns
user_id_col = "user_id" if "user_id" in cols else None
intent_col = "intent" if "intent" in cols else None
question_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else ("question" if "question" in cols else None))
# ---------- 2) Filters ----------
colA, colB, colC = st.columns([1,1,1])
with colA:
end_date = st.date_input("End date", datetime.utcnow().date())
with colB:
start_date = st.date_input("Start date", end_date - timedelta(days=30))
with colC:
max_rows = st.number_input("Max rows", min_value=10, max_value=1000, value=200, step=10)
intent_like = st.text_input("Filter by intent (optional, substring match)", "")
# ---------- 3) Build SELECT + WHERE (exclude empty SQL & chit-chat intents & already-published) ----------
select_cols = [id_select]
if ts_col: select_cols.append(ts_col)
if user_id_col: select_cols.append(user_id_col)
if intent_col: select_cols.append(intent_col)
if question_col: select_cols.append(question_col)
select_cols.append(sql_col)
select_list = ", ".join(select_cols)
where = [fb_pred]
# Exclude empty/[]/null SQL
where.append(f"TRIM(COALESCE({sql_col}, '')) <> ''")
where.append(f"LOWER(TRIM(COALESCE({sql_col}, ''))) NOT IN ('[]','null','none')")
params = []
if ts_col:
where.append(f"{ts_col} BETWEEN ? AND ?")
params.extend([f"{start_date} 00:00:00", f"{end_date} 23:59:59"])
if intent_col:
placeholders = ",".join(["?"] * len(STOP_INTENTS))
where.append(f"LOWER({intent_col}) NOT IN ({placeholders})")
params.extend([s.lower() for s in STOP_INTENTS])
if intent_like.strip() and intent_col:
where.append(f"{intent_col} LIKE ?")
params.append(f"%{intent_like.strip()}%")
where_sql = " AND ".join(where)
order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC"
base_sql = f"""
SELECT {select_list}
FROM chat_history ch
WHERE {where_sql}
AND NOT EXISTS (
SELECT 1 FROM rag_published rp
WHERE rp.pub_key = CAST({id_col_expr} AS TEXT)
)
{order_sql}
LIMIT ?
"""
params.append(int(max_rows))
with get_conn() as conn:
df = pd.read_sql_query(base_sql, conn, params=params)
# ---------- 4) Extra Python-side safety ----------
if not df.empty:
def _has_sql(x: str) -> bool:
s = str(x or "").strip().lower()
return len(s) > 0 and s not in {"[]", "null", "none"}
df = df[df[sql_col].map(_has_sql)]
if intent_col and intent_col in df.columns:
df = df[~df[intent_col].astype(str).str.lower().isin(STOP_INTENTS)]
if df.empty:
st.success("No eligible positive chats to publish (after filters and dedup).")
st.stop()
# ---------- 5) Selectable grid (max 50) ----------
preview_cols = []
for c in ["pub_key", ts_col, user_id_col,"chat_id", intent_col, question_col,"llm_plan", sql_col]:
if c and c in df.columns:
preview_cols.append(c)
view = df[preview_cols].copy()
def _ui_safe_cell_local(x):
import json
try:
if isinstance(x, (dict, list)):
return json.dumps(x, ensure_ascii=False)
return str(x)
except Exception:
return str(x)
view = view.map(_ui_safe_cell_local)
view.insert(0, "Select", False)
st.caption("Tick up to 50 rows to publish.")
edited = st.data_editor(
view.head(200),
use_container_width=True,
hide_index=True,
key="rag_pos_editor",
column_config={"Select": st.column_config.CheckboxColumn(required=False)},
)
selected_rows = edited[edited["Select"]]
sel_count = int(len(selected_rows))
st.caption(f"Selected: {sel_count} (max 50)")
disabled = sel_count == 0 or sel_count > 50
if st.button("Publish selected → RAG", key="btn_pub_selected", type="primary", disabled=disabled, use_container_width=True):
ok = 0
skipped = 0
logger.info("[rag-publish] CLICKED publish button")
logger.info("[rag-publish] edited shape=%s selected_rows=%s", edited.shape, int(edited.get("Select", pd.Series()).sum() if "Select" in edited else 0))
# Map selection back to the original df using pub_key
if "pub_key" not in view.columns:
st.error("Internal error: 'pub_key' not present in grid.")
st.stop()
selected_rows = edited[edited["Select"]]
selected_keys = set(selected_rows["pub_key"].astype(str).tolist())
logger.info("[rag-publish] selected_keys=%s", list(selected_keys)[:10])
if not selected_keys:
st.info("No rows selected.")
st.stop()
# Ensure df has pub_key as string for matching
df["_pub_key_str"] = df["pub_key"].astype(str)
publish_df = df[df["_pub_key_str"].isin(selected_keys)].copy()
for _, row in publish_df.iterrows():
# Resolve ids safely
chat_id_val = str(row.get("chat_id") or row.get("pub_key") or "")
user_id_val = str(row.get(user_id_col)) if (user_id_col and user_id_col in row) else ""
payload = {
"chat_id": chat_id_val,
"user_id": user_id_val,
"intent": row.get(intent_col, "") if intent_col else "",
"user_input": row.get(question_col, "") if question_col else "",
"llm_plan": row.get("llm_plan", ""),
"plan_after": row.get("llm_plan", ""),
"secured_sql": row.get(sql_col, ""),
"sql_after": row.get(sql_col, ""),
}
# Ensure plain string SQL
sql_after = payload.get("sql_after") or ""
if not isinstance(sql_after, str):
try:
import json as _json
sql_after = _json.dumps(sql_after, ensure_ascii=False)
except Exception:
sql_after = str(sql_after)
payload["sql_after"] = sql_after
try:
# If your publisher supports meta=..., prefer passing meta explicitly:
meta = {
"tag": "exemplar",
"source": "admin_rag_publisher/positive_chats",
"user_id": user_id_val,
"chat_id": chat_id_val,
"pub_key": str(row.get("pub_key") or ""),
"created_at": str(row.get(ts_col) or ""),
"intent": payload.get("intent", ""),
}
ex_id = publish_exemplar_from_row(payload, registry, meta=meta)
if ex_id:
ok += 1
client = registry.get("chroma_client") or get_chroma_client()
col = client.get_or_create_collection(registry.get("chroma_collection", "olist_exemplars"))
try:
logger.info("[rag-publish] collection count after upsert: %s", col.count())
except Exception as e:
logger.warning("[rag-publish] count() failed: %s", e)
mark_published(str(row.get("pub_key") or ""))
else:
skipped += 1
except Exception as e:
skipped += 1
logger.exception("publish error: %s", e)
st.success(f"Published {ok} exemplars to RAG. Skipped {skipped}.")
st.rerun()
except Exception as e:
st.warning(f"Render error in 'positive chats' panel: {e}")
with st.expander("Manual publish from a specific chat row", expanded=False):
try:
# Discover columns
with get_conn() as conn:
cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn)
cols = set(str(n).lower() for n in cols_df["name"].astype(str))
# Candidate lookup keys, in priority order
candidates = ["conversation_id", "conv_id", "session_id", "thread_id", "message_id", "turn_id", "id"]
avail_keys = [c for c in candidates if c in cols]
# rowid fallback if none of the above exist
use_rowid = False
if not avail_keys:
avail_keys = ["rowid"]
use_rowid = True
# Pick a key and input value
key_col = st.selectbox("Lookup key column", options=avail_keys, index=0)
key_val = st.text_input("Key value (exact match)")
# Timestamp / ordering column (optional)
ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"]
ts_col = next((c for c in ts_candidates if c.lower() in cols), None)
order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC"
if st.button("Fetch & Publish exemplar", key="btn_pub_one_row", disabled=not key_val):
# Build query safely
where_col = "rowid" if (use_rowid and key_col == "rowid") else key_col
select_list = "*"
base_sql = f"SELECT {select_list} FROM chat_history WHERE {where_col} = ? {order_sql} LIMIT 1"
with get_conn() as conn:
df_one = pd.read_sql_query(base_sql, conn, params=[key_val])
if df_one.empty:
st.warning("No row found for that key/value.")
else:
row = df_one.iloc[0].to_dict()
# Prepare payload for publisher (flexible keys)
payload = {
"intent": row.get("intent", ""),
"user_input": row.get("user_input", row.get("question", "")),
"llm_plan": row.get("llm_plan", ""),
"plan_after": row.get("llm_plan", ""),
"secured_sql": row.get("secured_sql", row.get("sql", "")),
"sql_after": row.get("secured_sql", row.get("sql", "")),
}
ex_id = publish_exemplar_from_row(payload, registry)
if ex_id:
st.success(f"Published exemplar {ex_id}")
else:
st.error("Failed to publish exemplar.")
except Exception as e:
st.warning(f"Manual publish error: {e}")
def render_rag_publisher():
import os
import json
import datetime
import sqlite3
import pandas as pd
try:
logging.getLogger("olist").info(
"[rag-publish] render start | DB=%s | CHROMA_PATH=%s | RAG_COLLECTION=%s",
os.environ.get("DB_PATH", "/data/olist.sqlite"),
os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store")),
os.environ.get("RAG_COLLECTION", "olist_sql_exemplars"),
)
except Exception:
pass
# Streamlit is optional at import time in some environments
try:
import streamlit as st
except Exception: # pragma: no cover
return
# Role gate
auth = st.session_state.get("auth") or {}
if (auth.get("role") or "").lower() != "admin":
st.warning("RAG Publisher is available to admins only.")
return
# DB open
db_path = os.environ.get("DB_PATH", "/data/olist.sqlite")
if not os.path.exists(db_path):
st.error(f"Database not found at {db_path}")
return
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
# mark-published table
try:
conn.execute(
"""CREATE TABLE IF NOT EXISTS rag_published (
chat_id INTEGER PRIMARY KEY,
pushed_at TEXT,
collection TEXT
)"""
)
conn.commit()
except Exception:
pass
# discover schema
try:
cols = [r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()]
except Exception as e:
st.error("Table `chat_history` not found or unreadable.")
try:
st.exception(e)
except Exception:
pass
conn.close()
return
pk = "chat_id" if "chat_id" in cols else ("id" if "id" in cols else None)
created = next((c for c in ["created_at", "timestamp", "ts", "time", "dt"] if c in cols), None)
has_fb = "feedback" in cols
has_fb_val = "feedback_value" in cols
role_col = next((c for c in ["user_role", "role"] if c in cols), None)
intent_col = next((c for c in ["intent", "intent_label", "non_sql_label", "label"] if c in cols), None)
inp_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else None)
plan_col = "llm_plan" if "llm_plan" in cols else None
sql_col = "secured_sql" if "secured_sql" in cols else None # metadata only
if not pk or not (has_fb or has_fb_val):
st.error("`chat_history` must have a primary key and at least one feedback column (feedback / feedback_value).")
conn.close()
return
# positive feedback predicate
if has_fb and has_fb_val:
POS_FEEDBACK = ["(COALESCE(feedback,0)=1 OR COALESCE(feedback_value,0)=1)"]
elif has_fb:
POS_FEEDBACK = ["(feedback = 1)"]
else:
POS_FEEDBACK = ["(feedback_value = 1)"]
# UI controls
today = datetime.date.today()
default_start = today - datetime.timedelta(days=7)
c1, c2, c3 = st.columns([1, 1, 1])
with c1:
start_date = st.date_input("From date", value=st.session_state.get("rag_from") or default_start, key="rag_from")
with c2:
end_date = st.date_input("To date", value=st.session_state.get("rag_to") or today, key="rag_to")
with c3:
max_fetch = st.number_input("Max rows", min_value=10, max_value=1000, value=200, step=10)
if start_date > end_date:
st.error("From date cannot be after To date.")
conn.close()
return
include_published = True # default behavior in your baseline
disable_intent_exclusion = False
default_excluded = {"greeting", "thanks", "smalltalk", "complaint", "others"}
# build WHERE
where = []
params = []
where += POS_FEEDBACK
if role_col:
where.append(f"({role_col} IS NULL OR LOWER({role_col})='user')")
if created:
where.append(f"{created} BETWEEN ? AND ?")
params += [f"{start_date} 00:00:00", f"{end_date} 23:59:59"]
if not include_published:
where.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)")
# intent (optional)
intent_params = []
intent_clause = None
if intent_col and not disable_intent_exclusion:
ex = sorted(default_excluded)
placeholders = ",".join(["?"] * len(ex))
intent_clause = f"COALESCE(LOWER({intent_col}), '') NOT IN ({placeholders})"
intent_params = [s.lower() for s in ex]
# SELECT list
select_cols = [f"{pk} AS chat_id"]
select_cols += [f"{created} AS created_at"] if created else ["NULL AS created_at"]
select_cols += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"]
select_cols += [f"{plan_col} AS llm_plan"] if plan_col else ["NULL AS llm_plan"]
select_cols += [f"{sql_col} AS secured_sql"] if sql_col else ["NULL AS secured_sql"]
order_by = f"ORDER BY {created} DESC" if created else f"ORDER BY {pk} DESC"
# small helper to debug counts
def _count(sql_where: str, args: list):
try:
return conn.execute(f"SELECT COUNT(1) FROM chat_history WHERE {sql_where}", args).fetchone()[0]
except Exception:
return None
base_pos = _count(" AND ".join(POS_FEEDBACK), [])
base_role = _count(" AND ".join(POS_FEEDBACK + ([f"({role_col} IS NULL OR LOWER({role_col})='user')"] if role_col else [])), [])
base_date = None
if created:
base_date = _count(
" AND ".join(
POS_FEEDBACK
+ ([f"({role_col} IS NULL OR LOWER({role_col})='user')"] if role_col else [])
+ [f"{created} BETWEEN ? AND ?"]
),
[f"{start_date} 00:00:00", f"{end_date} 23:59:59"],
)
base_published = None
if not include_published:
_w = POS_FEEDBACK[:]
if role_col:
_w.append(f"({role_col} IS NULL OR LOWER({role_col})='user')")
if created:
_w.append(f"{created} BETWEEN ? AND ?")
_w.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)")
_p = ([f"{start_date} 00:00:00", f"{end_date} 23:59:59"] if created else [])
base_published = _count(" AND ".join(_w), _p)
base_intent = None
if intent_col and not disable_intent_exclusion:
_w = POS_FEEDBACK[:]
if role_col:
_w.append(f"({role_col} IS NULL OR LOWER({role_col})='user')")
if created:
_w.append(f"{created} BETWEEN ? AND ?")
if not include_published:
_w.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)")
_w.append(f"COALESCE(LOWER({intent_col}), '') NOT IN ({','.join(['?']*len(default_excluded))})")
_p = []
if created:
_p += [f"{start_date} 00:00:00", f"{end_date} 23:59:59"]
_p += [s.lower() for s in sorted(default_excluded)]
base_intent = _count(" AND ".join(_w), _p)
final_where = where[:]
final_params = params[:]
if intent_clause and (base_intent is None or base_intent > 0):
final_where.append(intent_clause)
final_params += intent_params
sql = f"""
SELECT {", ".join(select_cols)}
FROM chat_history
WHERE {" AND ".join(final_where)}
{order_by}
LIMIT ?
"""
final_params += [int(max_fetch)]
# query rows
try:
rows = conn.execute(sql, final_params).fetchall()
except Exception as e:
st.error(f"Failed to query chat_history: {e}")
try:
st.exception(e)
except Exception:
pass
conn.close()
return
with st.expander("Debug: filter counts", expanded=False):
st.write(
{
"positive_only": base_pos,
"positive_plus_role": base_role,
"after_date": base_date,
"after_excluding_published": base_published,
"after_intent_exclusion": base_intent,
"final_rows": len(rows),
"excluded_intents": sorted(default_excluded)
if (intent_col and not disable_intent_exclusion)
else "N/A",
"intent_column_used": intent_col or "N/A",
}
)
def _plan_preview(raw) -> str:
try:
if not raw:
return ""
if isinstance(raw, (bytes, bytearray)):
raw = raw.decode("utf-8", "ignore")
obj = json.loads(raw) if isinstance(raw, str) else raw
for k in ("explanation_display", "explanation", "message", "text"):
v = obj.get(k)
if v:
s = str(v).strip()
return s if len(s) <= 300 else s[:300] + "…"
return json.dumps(obj, ensure_ascii=False)[:300] + "…"
except Exception:
s = str(raw or "")
return s if len(s) <= 300 else s[:300] + "…"
data = []
for r in rows:
data.append(
{
"select": False,
"chat_id": int(r["chat_id"]),
"created_at": r["created_at"],
"user_input": (r["user_input"] or "").strip() if "user_input" in r.keys() else "",
"llm_plan": _plan_preview(r["llm_plan"]) if "llm_plan" in r.keys() else "",
}
)
if not data:
st.info(
"No eligible positively-rated chats for the chosen filters. Use the debug box above to identify the blocking filter."
)
conn.close()
return
st.caption("Select up to 50 records to publish to RAG")
df = pd.DataFrame(data)
edited = st.data_editor(
df,
num_rows="fixed",
column_config={
"select": st.column_config.CheckboxColumn("Select", help="Pick rows to publish", default=False),
"chat_id": st.column_config.NumberColumn("chat_id", disabled=True),
"created_at": st.column_config.TextColumn("created_at", disabled=True),
"user_input": st.column_config.TextColumn("User input", width="large", disabled=True),
"llm_plan": st.column_config.TextColumn("LLM plan (preview)", width="large", disabled=True),
},
hide_index=True,
key="rag_grid_ui",
use_container_width=True,
)
selected_ids = [int(row.chat_id) for _, row in edited.iterrows() if bool(row.select)]
logging.getLogger("olist").info(
"[rag-publish] grid rows=%s | selected_ids(sample)=%s", len(df), selected_ids[:1]
)
st.write(f"Selected: **{len(selected_ids)}**")
if len(selected_ids) > 50:
st.error("You can select at most 50 records at a time.")
conn.close()
return
st.markdown("---")
coll_name = os.environ.get("RAG_COLLECTION", "olist_sql_exemplars")
coll = coll_name # keep a short alias used later
push = st.button("Publish selected to RAG")
if push:
lg = logging.getLogger("olist")
lg.info("[rag-publish] CLICKED | selected_total=%s", len(selected_ids))
if not selected_ids:
st.warning("Please select at least one row.")
conn.close()
return
if intent_col:
_intent_exclude = ("help_greeting", "thanks", "farewell", "smalltalk", "joke", "chitchat")
_intent_filter_sql = f"AND COALESCE(LOWER({intent_col}), '') NOT IN ({','.join(['?']*len(_intent_exclude))})"
else:
_intent_exclude = ()
_intent_filter_sql = ""
_plan_req = (plan_col or "NULL")
_sql_req = (sql_col or ("sql_generated" if "sql_generated" in cols else None))
_text_not_empty = f"AND TRIM({_plan_req}) <> ''" if plan_col else ""
_sql_not_empty = f"AND TRIM({_sql_req}) <> ''" if _sql_req else ""
qmarks = ",".join(["?"] * len(selected_ids))
sql2 = f"""
SELECT {pk} AS chat_id,
{inp_col if inp_col else "NULL"} AS user_input,
{plan_col if plan_col else "NULL"} AS llm_plan,
{sql_col if sql_col else "NULL"} AS secured_sql,
{role_col if role_col else "NULL"} AS role,
COALESCE(user_id, NULL) AS user_id
FROM chat_history
WHERE {pk} IN ({qmarks})
{_intent_filter_sql}
{_text_not_empty}
{_sql_not_empty}
{order_by}
"""
_params = list(selected_ids)
if intent_col:
_params += [s.lower() for s in _intent_exclude]
lg.info(
"[rag-publish] fetch_selected | ids=%s | intent_col=%s | plan_col=%s | sql_col=%s",
selected_ids,
intent_col,
plan_col,
sql_col,
)
try:
chosen = conn.execute(sql2, _params).fetchall()
except Exception as e:
st.error(f"Failed to fetch selected rows: {e}")
try:
st.exception(e)
except Exception:
pass
conn.close()
return
def _plan_text(raw) -> str:
try:
if not raw:
return ""
if isinstance(raw, (bytes, bytearray)):
raw = raw.decode("utf-8", "ignore")
obj = json.loads(raw) if isinstance(raw, str) else raw
for k in ("explanation_display", "explanation", "message", "text"):
v = obj.get(k)
if v:
return str(v).strip()
return json.dumps(obj, ensure_ascii=False)
except Exception:
return str(raw or "")
filtered = []
for r in chosen[:50]:
plan_txt = _plan_text(r["llm_plan"])
sql_txt = (r["secured_sql"] or "").strip()
if plan_txt.strip() and sql_txt.strip():
filtered.append(r)
lg.info("[rag-publish] eligible_to_publish=%s (from %s chosen)", len(filtered), len(chosen))
if not filtered:
st.warning("Nothing eligible to publish: either intent excluded or plan/SQL empty.")
conn.close()
return
# Build docs + metadata for upsert
docs, ids, metas = [], [], []
for r in filtered:
cid = int(r["chat_id"])
uq = (r["user_input"] or "").strip()
plan_txt = _plan_text(r["llm_plan"])
doc = f"Q: {uq}\nPlan: {plan_txt}\nSQL: {(r['secured_sql'] or '').strip()}"
docs.append(doc)
ids.append(f"chat_{cid}")
metas.append(
{
"tag": "exemplar",
"user_id": (r["user_id"] or None),
"chat_id": str(cid),
"source": "feedback_curated",
"secured_sql": (r["secured_sql"] or None),
"user_input": uq or None,
"llm_plan": plan_txt or None,
}
)
# RAG push
try:
import chromadb
from chromadb.utils import embedding_functions
# choose embedding function to match your reader path
openai_key = os.environ.get("OPENAI_API_KEY") or os.environ.get("OPENAI_APIKEY")
ef = None
ef_dim = None
if openai_key:
embed_model = os.environ.get("OPENAI_EMBED_MODEL", "text-embedding-3-small")
ef = embedding_functions.OpenAIEmbeddingFunction(api_key=openai_key, model_name=embed_model)
ef_dim = OPENAI_EMBED_DIM
else:
try:
from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2
ef = ONNXMiniLM_L6_V2()
ef_dim = ONNX_EMBED_DIM
except Exception:
ef = None
ef_dim = None
chroma_path = os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store"))
lg.info("[rag-publish] chroma client init | path=%s | collection=%s", chroma_path, coll)
client = chromadb.PersistentClient(path=chroma_path)
try:
rag = _get_or_migrate_chroma_collection(client, coll, ef, (ef_dim or OPENAI_EMBED_DIM), lg)
except Exception:
rag = client.get_or_create_collection(coll, embedding_function=ef)
# log an example meta row
if ids:
lg.info(
"[rag-publish] upsert begin | docs=%s | count_before=%s",
len(docs),
getattr(rag, "count", lambda: None)(),
)
lg.info("[rag-publish] sample id=%s | meta=%s", ids[0], {k: metas[0].get(k) for k in ("tag", "user_id", "chat_id", "source")})
if docs:
rag.upsert(documents=docs, ids=ids, metadatas=metas)
lg.info("[rag-publish] upsert done | pushed=%s | count_after=%s", len(docs), getattr(rag, "count", lambda: None)())
st.success(f"Pushed {len(docs)} item(s) to collection '{coll}'.")
else:
st.warning("Nothing to publish (no text available).")
except Exception as e:
st.error(f"RAG push failed: {e}")
try:
lg.exception("RAG push failed | coll=%s | chroma_path=%s", coll, os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store")))
except Exception:
pass
# Mark published
try:
conn.executemany(
"INSERT OR IGNORE INTO rag_published (chat_id, pushed_at, collection) VALUES (?, datetime('now'), ?)",
[(int(r["chat_id"]), coll) for r in filtered],
)
conn.commit()
lg.info("[rag-publish] marked published | ids=%s | coll=%s", [int(r["chat_id"]) for r in filtered], coll)
except Exception:
pass
# Aggregate audit
try:
conn.execute(
"""CREATE TABLE IF NOT EXISTS rag_push_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pushed_at TEXT,
count INTEGER,
collection TEXT
)"""
)
conn.execute(
"INSERT INTO rag_push_log(pushed_at, count, collection) VALUES (datetime('now'), ?, ?)",
(len(docs), coll),
)
conn.commit()
except Exception:
pass
conn.close()
# -----------------------------
# Tabs (role-aware)
# -----------------------------
# if auth["role"] == "admin":
# tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "🛠️ Admin Utils","RAG Publisher"])
# else:
# tab_chat, tab_mis, tab_rate, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "⭐ Rate Earlier Conversations","RAG Publisher"])
# ## ---- Tabs (Customer 360 / Chat / MIS / Rate/Admin) ----
if auth.get('role') == 'admin':
try:
tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "🛠️ Admin Utils","RAG Publisher"])
except Exception:
tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["Chat", "MIS", "Admin Utils","RAG Publisher"])
else:
try:
tab_c360, tab_chat, tab_mis, tab_rate = st.tabs(["👤 Customer 360", "💬 Chat", "📊 MIS", "⭐ Rate Earlier Conversations"])
except Exception:
tab_c360, tab_chat, tab_mis, tab_rate = st.tabs(["Customer 360", "Chat", "MIS", "Rate Earlier Conversations"])
# ---- Customer 360 ----
if auth and auth.get("role") == "user":
with tab_c360:
st.header("Customer 360")
st.info("Please use the Chat Tab for discussion with our Agent.")
# Compact height for charts if present
st.markdown("""<style>
div[data-testid=stVegaLiteChart] {height: 220px !important;}
div[data-testid=stPlotlyChart] {height: 220px !important;}
.css-1kyxreq, .css-1ht1j8u {font-size: 0.95rem !important;}
</style>""", unsafe_allow_html=True)
try:
if auth and auth.get("role") == "user":
try:
render_customer_360(auth.get("customer_id"), get_conn, db_path=None)
except Exception:
st.caption("Customer 360 is temporarily unavailable.")
except Exception:
st.caption("Customer 360 is temporarily unavailable.")
# ---- Chat ----
with tab_chat:
st.header("Chat with SmartAssist BOT")
st.session_state.setdefault('transcript', [])
# Clear Ask input on rerun if flagged
if st.session_state.pop('__clear_input__', False):
st.session_state['q_enabled'] = ''
st.session_state['q_disabled'] = ''
# (removed) Customer 360 preview inside Chat — shown only in Customer 360 tab now.
if "last_chat_out" not in st.session_state:
st.session_state.last_chat_out = None
st.session_state.last_sentiment = None
st.session_state.last_intent = None
st.session_state.last_query_text = None
st.session_state.last_chat_id = None
unrated = (
st.session_state.last_chat_id is not None
and get_chat_feedback(st.session_state.last_chat_id) is None
)
ask_disabled = False
blocked = False
bypass_main_logic = False
if FEEDBACK_MODE == "HARD_REQUIRED" and unrated:
st.warning("Please rate the previous answer (👍/👎) to continue.")
ask_disabled = True
if auth and auth.get("role") == "user":
try:
show_weekly_topk_chips(get_conn(), k=5)
except Exception:
pass
q = st.text_area("Ask a question", disabled=True, key="q_disabled")
else:
if FEEDBACK_MODE == "SOFT_REQUIRED" and unrated:
st.info("Quick rating helps improve results. Please rate the previous answer above. 👍/👎")
if auth and auth.get("role") == "user":
try:
show_weekly_topk_chips(get_conn(), k=5)
except Exception:
pass
q = st.text_area("Ask a question", key="q_enabled")
ask_clicked = st.button("Ask", disabled=ask_disabled)
# History toggle below input
st.session_state.setdefault('show_history', False)
show_hist = st.checkbox('Display Conversation History (This Session)', key='show_history')
# On non-click rerun, show latest conversation under input
if not ask_clicked and st.session_state.get('last_chat_out') is not None:
_last_user = st.session_state.get('last_query_text') or ''
if _last_user:
with st.chat_message('user'):
st.write(_last_user)
render_chat_output(st.session_state.last_chat_out)
render_feedback_controls(st.session_state.get('last_chat_id'), key_ns="clicked")
if ask_clicked:
st.session_state["__clear_input__"] = True
limited, rl_msg = check_rate_limit(auth["user_id"], auth["role"])
if limited:
st.warning(rl_msg)
else:
st.caption(f"Rate limit status: {rl_msg}")
# Detect & translate input to English (if Indic)
src_lang = _detect_lang(q) # 'en'|'hi'|'mr'
try:
st.session_state["last_user_lang"] = src_lang
except Exception:
pass
logger.info("[chat] _detect_lang src_lang=%s user_text=%s", src_lang, _sample(q, 200))
q_for_llm = _tx_to_en(q, src_lang)
st.session_state['last_chat_out'] = None
llm = get_llm_client(LLM_CHOICE)
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# NEW: Hard stop destructive requests BEFORE any other routing.
# This prevents the “please provide order/customer id” branch
# from catching delete/erase/cancel requests.
if classify_destructive_intent_with_llm(llm, q_for_llm):
logger.info("[chat] inside _intent_destructive condition ")
final_msg = (
"I can’t delete or modify your data. "
"I’m read-only and can only help you view or summarize your data. "
"If you’d like to examine your latest order or payments, I can show those."
)
logger.info("[chat] inside _intent_destructive - saving the data to chat history ")
# persist the bot message exactly as you already do
try:
insert_chat_history(
user_id=auth["user_id"],
role="assistant",
message=final_msg,
plan_json=None,
queries_json=None,
)
except Exception:
pass
out = {
"queries": [{
"kind": "non_sql",
"df": pd.DataFrame({"message": [final_msg]}),
"blocked_reason": "destructive_request_block",
}],
"plan": {
"explanation": final_msg,
"explanation_display": final_msg,
},
"llm_time": 0.0,
"sentiment": "Neutral",
"intent": "destructive_block",
"src_lang": st.session_state.get("src_lang", "en"),
}
bypass_main_logic = True
st.session_state['last_chat_out'] = out
st.session_state['last_query_text'] = q
st.session_state.last_sentiment = "Neutral"
st.session_state.last_intent = "Other"
st.session_state.last_chat_id = None
# Show it in the current run
render_chat_output(out)
render_feedback_controls(st.session_state.get("last_chat_id"), key_ns="curr_run")
# Keep transcript consistent with the SQL path
try:
st.session_state.setdefault("transcript", [])
st.session_state["transcript"].append({"user": q, "assistant": final_msg, "ts": time.time()})
except Exception:
pass
# Clear the input and trigger a rerun so the text area empties
st.session_state["__clear_input__"] = True
st.rerun()
# <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
# Provide logged-in customer context so LLM can resolve 'my' correctly
if auth.get('role') == 'user' and auth.get('customer_id'):
_cid = auth.get('customer_id')
context_hint = (
'CONTEXT:\n'
f'- Logged-in customer_id: {_cid}\n'
"- Treat 'my'/'me' as this customer_id.\n"
"- 'latest order' means the order with the maximum orders.order_purchase_timestamp for this customer.\n"
"- Prefer writing a single SQL SELECT that answers the question directly.\n"
"- For 'payment type for my latest order', join orders -> order_payments and select payment_type.\n"
)
q_for_llm = context_hint + '\nQUESTION:\n' + q_for_llm
logger.info("[chat] src_lang=%s user_text=%s", src_lang, _sample(q, 200))
logger.info("[chat] Value of NON_SQL_ROUTER=%s ", NON_SQL_ROUTER)
logger.info("[chat] Value of bypass_main_logic=%s", bypass_main_logic)
logger.info("[chat] Value of q is =%s", q)
logger.info("[chat] Value of _is_my_account_query(q)=%s", _is_my_account_query(q))
if NON_SQL_ROUTER and not _is_my_account_query(q) and not bypass_main_logic:
non_sql_label = detect_non_sql_intent(q)
try:
log_router_decision(locals().get("chat_id"), (locals().get("auth") or {}).get("user_id") if "auth" in locals() else None, locals().get("q_for_llm") or locals().get("user_text") or "", locals().get("non_sql_label"), locals().get("src_lang") or locals().get("lang") or None, "non-sql" if locals().get("non_sql_label") else "sql")
except Exception:
pass
logger.info("[chat] non_sql_label=%s", non_sql_label)
if non_sql_label:
# 1) sentiment/intent (kept for MIS)
try:
sentiment, intent = classify_sentiment_and_intent(get_llm_client(LLM_CHOICE), q)
except Exception:
sentiment, intent = ("neutral", non_sql_label)
# 2) Build an English base message, then translate safely if needed
base_msg_en = respond_non_sql(non_sql_label, src_lang="en")
final_msg = _tx_safe(base_msg_en, src_lang) # translate only once, safely
logger.info("[chat] non_sql_label -> base_msg_en=%s", base_msg_en)
if src_lang and src_lang != "en":
logger.info("[chat] non_sql_label -> final_msg(translated)=%s", final_msg)
# 3) Prepare minimal 'out' so the UI renders a friendly message table
out = {
"intent": ("neutral", non_sql_label),
"sentiment": sentiment,
"text": final_msg, # localized via _tx_safe
"text_en": base_msg_en,
"queries": [], # <-- nothing to render in grid
"plan": {},
"secured_sql": None,
"cards": []
}
# 4) Log chat to DB so feedback works
try:
chat_id = insert_chat_history(
user_id=auth["user_id"],
role=auth["role"],
customer_id=auth.get("customer_id"),
user_input=q, # original text (could be Indic)
llm_plan=out["plan"],
secured_sql=[], # no SQL for routed replies
sentiment=sentiment,
intent=intent,
llm_time=0.0,
)
st.session_state.last_chat_id = chat_id
except Exception as e:
logger.warning("failed to log routed chat: %s", e)
st.session_state.last_chat_id = None
# 5) Render and stop before the SQL path runs
st.session_state['last_chat_out'] = out
st.session_state['last_query_text'] = q
st.session_state.last_sentiment = sentiment
st.session_state.last_intent = intent
render_chat_output(out)
render_feedback_controls(st.session_state.get("last_chat_id"), key_ns="render")
# clear any previous SQL state that could leak into the UI
st.session_state["last_sql"] = None
st.session_state["last_result"] = None
st.session_state["last_plan"] = None
st.session_state["bypass_main_logic"] = True
logger.info("[chat] Set bypass_main_logic to True")
# Append to transcript and clear input on next run (non-SQL path)
try:
#_asst = (out.get('plan') or {}).get('explanation_display') or (out.get('plan') or {}).get('explanation')
st.session_state.setdefault('transcript', [])
st.session_state["transcript"].append({"user": q, "assistant": final_msg, "ts": time.time()})
#_append_transcript_once(q, out, explicit_text=_asst)
except Exception:
pass
st.session_state['__clear_input__'] = True
st.rerun()
logger.info("[chat] non_sql_label -> Post render_feedback_controls")
if is_forbidden_all_tables_post_tx(q_for_llm) and not bypass_main_logic:
blocked_en = "Requests to list or dump data across all tables are restricted."
# Optional: translate the message back for UI; if translation fails, we still show English
try:
blocked_msg = _tx_from_en(blocked_en, src_lang) if src_lang != "en" else blocked_en
except Exception:
blocked_msg = blocked_en
st.warning(blocked_msg)
blocked = True
logger.info("[guard] blocked all-tables request (post-translation) en='%s'", _norm_guard_text(q_for_llm))
# If you log chat_history here, mark it blocked; then exit before planner:
if not blocked and not bypass_main_logic:
logger.info(">> in not blocked and not bypass_main_logic condition <<")
with timed_step("Handle prompt", "meta"):
out = handle_user_prompt(q_for_llm, auth["role"], auth.get("customer_id"), schema_registry, source_lang=src_lang)
expl = out.get("plan", {}).get("explanation_display")
if expl:
logger.debug("[chat] explanation_display=%s", _sample(expl, 200))
with timed_step("Classify sentiment/intent", "meta"):
sentiment, intent = classify_sentiment_and_intent(get_llm_client(LLM_CHOICE), q_for_llm)
with timed_step("Insert chat_history", "meta"):
chat_id = insert_chat_history(
user_id=auth["user_id"],
role=auth["role"],
customer_id=auth.get("customer_id"),
user_input=q, # store original (may be Indic)
llm_plan=out["plan"],
secured_sql=[c["secured"] for c in out["queries"] if c.get("secured")],
sentiment=sentiment,
intent=intent,
llm_time=out["llm_time"]
)
out["sentiment"] = sentiment
out["intent"] = intent
st.session_state.last_chat_out = out
st.session_state.last_sentiment = sentiment
st.session_state.last_intent = intent
st.session_state.last_query_text = q
st.session_state.last_chat_id = chat_id
render_chat_output(out)
render_feedback_controls(chat_id, key_ns="notblock")
# Append to transcript and clear input on next run (SQL path)
try:
_asst = 'Pls find below the data you requested'
st.session_state.setdefault('transcript', [])
_append_transcript_once(q, out, explicit_text=_asst)
except Exception:
pass
st.session_state['__clear_input__'] = True
st.rerun()
else:
logger.info(">>in Else Loop<<")
if st.session_state.get("last_chat_out") is None:
st.info("Ask a question to see results.") # <-- no rendering here
# Ensure transcript has latest turn appended (once per turn)
_out = st.session_state.get('last_chat_out')
_last_user = st.session_state.get('last_query_text')
if _out is not None and _last_user:
_t = st.session_state.setdefault('transcript', [])
if (not _t) or (_t[-1].get('user') != _last_user):
try:
_asst = 'Pls find below the data you requested'
_t.append({'user': _last_user, 'assistant': _asst, 'ts': time.time()})
except Exception:
pass
# Render conversation history (earlier only) when toggled
if st.session_state.get('show_history') and st.session_state.get('transcript'):
st.subheader('Conversation history (earlier)')
_items = st.session_state['transcript'][:-1] if len(st.session_state['transcript']) > 1 else []
for _it in reversed(_items):
if _it.get('user'):
with st.chat_message('user'):
st.write(_it.get('user',''))
if _it.get('assistant'):
with st.chat_message('assistant'):
st.write(_it.get('assistant'))
# ---- MIS ----
with tab_mis:
st.header("📊 MIS Dashboard")
timeframe = st.selectbox("Timeframe", ["Last 7 days", "Last 30 days", "Last 90 days", "All"])
days_map = {"Last 7 days": 7, "Last 30 days": 30, "Last 90 days": 90, "All": None}
days = days_map[timeframe]
if auth["role"] == "admin":
df = load_chat_history(days=days)
st.subheader("Overview")
total = len(df)
unique_users = df["user_id"].nunique() if not df.empty else 0
avg_llm = float(df["llm_response_time"].mean()) if ("llm_response_time" in df and not df.empty) else 0.0
execable = sum(bool(_json_list(x)) for x in (df["secured_sql"] if "secured_sql" in df else []))
repaired = sum(_auto_repaired(row) for _, row in df.iterrows()) if total else 0
k1, k2, k3, k4 = st.columns(4)
with k1: _kpi_number("Total chats", total)
with k2: _kpi_number("Unique users", unique_users)
with k3: _kpi_number("Exec-able rate", f"{_safe_pct(execable, total):.1f}%", "Has secured SQL")
with k4: _kpi_number("Auto-repair rate", f"{_safe_pct(repaired, total):.1f}%", "Validator changed SQL")
st.caption(f"🤖 Avg LLM planning time: {avg_llm:.3f}s")
st.markdown("### Intent & Sentiment")
c1, c2 = st.columns(2)
with c1:
if "intent" in df and not df.empty:
st.bar_chart(df["intent"].value_counts().head(10))
else:
st.info("No intent data yet.")
with c2:
if "sentiment" in df and not df.empty:
st.bar_chart(df["sentiment"].value_counts())
else:
st.info("No sentiment data yet.")
st.markdown("### Top Tables & Joins")
table_counts = {}
if not df.empty and "secured_sql" in df:
for sqllist in df["secured_sql"]:
for sql in _json_list(sqllist):
for t in _extract_tables_from_sql(sql):
table_counts[t] = table_counts.get(t, 0) + 1
if table_counts:
top_tbl = pd.DataFrame(sorted(table_counts.items(), key=lambda x: x[1], reverse=True),
columns=["table", "mentions"]).head(15)
st.dataframe(top_tbl, use_container_width=True)
else:
st.info("No table usage yet.")
st.markdown("### Activity Over Time")
if not df.empty and "created_at" in df:
tmp = df.copy()
tmp["date"] = pd.to_datetime(tmp["created_at"], errors="coerce").dt.date
tmp = tmp.dropna(subset=["date"])
if not tmp.empty:
daily = tmp.groupby("date").size().reset_index(name="chats").set_index("date")
if not daily.empty:
st.line_chart(daily)
else:
st.info("No activity yet.")
else:
st.info("No activity yet.")
else:
st.info("No chat history yet.")
st.markdown("### Recent Chats")
show_cols = ["created_at", "email", "user_id", "role", "user_input", "intent", "sentiment", "llm_response_time", "feedback"]
try:
with get_conn() as conn:
users_df = pd.read_sql_query("SELECT user_id, email FROM users", conn)
df2 = df.merge(users_df, on="user_id", how="left")
except Exception:
df2 = df.copy()
for c in show_cols:
if c not in df2.columns: df2[c] = None
st.dataframe(df2[show_cols].head(200), use_container_width=True)
csv = df2.to_csv(index=False).encode("utf-8")
st.download_button("⬇️ Export CSV", csv, file_name="chat_history_export.csv", mime="text/csv")
else:
df = load_chat_history(days=days, only_user_id=auth["user_id"])
st.subheader("Your Summary")
total = len(df)
stats = _feedback_stats_for_user(auth["user_id"], days)
helpful = stats["helpful_count"]
avg_llm = float(df["llm_response_time"].mean()) if ("llm_response_time" in df and not df.empty) else 0.0
k1, k2, k3 = st.columns(3)
with k1: _kpi_number("Your chats", total)
with k2: _kpi_number("👍 Helpful", helpful)
with k3: _kpi_number("🤖 Avg plan time", f"{avg_llm:.3f}s")
st.markdown("### Your Top Intents")
if "intent" in df and not df.empty:
vc = df["intent"].value_counts()
if not vc.empty: st.bar_chart(vc.head(10))
else: st.info("No intent data yet.")
else:
st.info("No intent data yet.")
st.markdown("### Recent (last 50)")
show_cols = ["created_at", "user_input", "intent", "sentiment", "llm_response_time", "feedback"]
for c in show_cols:
if c not in df.columns: df[c] = None
st.dataframe(df[show_cols].head(50), use_container_width=True)
csv = df[show_cols].to_csv(index=False).encode("utf-8")
st.download_button("⬇️ Download your history (CSV)", csv, file_name="my_chat_history.csv", mime="text/csv")
# ---- Rate Earlier (user-only) ----
if auth["role"] != "admin":
with tab_rate:
st.header("⭐ Rate Earlier Conversations")
# Flash any success/error message from the previous submit
_flash = st.session_state.pop('rate_flash', None)
if _flash:
_kind, _msg = _flash
try:
(st.success if _kind=='success' else st.error)(_msg)
st.toast(_msg)
except Exception:
pass
fb_logger.info("[rate-tab] opened user_id=%s role=%s", auth["user_id"], auth["role"])
# 1) Load recent and keep only UNRATED (feedback is None/NaN)
my_recent = get_recent_chats_for_user(auth["user_id"], limit=100)
fb_logger.info("[rate-tab] fetched recent=%d", len(my_recent))
if "feedback" not in my_recent.columns:
fb_logger.error("[rate-tab] feedback column missing in my_recent columns=%s", list(my_recent.columns))
st.error("Internal error: feedback column missing.")
st.stop()
unrated = my_recent[my_recent["feedback"].isna()].copy()
fb_logger.info("[rate-tab] unrated=%d rated=%d", len(unrated), len(my_recent) - len(unrated))
if unrated.empty:
st.success("No conversations pending feedback. 🙌")
st.caption("Rated conversations are hidden from this list.")
st.stop()
unrated = unrated.reset_index(drop=True)
def _label(row):
text = (row["user_input"] or "").strip().replace("\n", " ")
if len(text) > 70:
text = text[:67] + "…"
ts = row.get("created_at", "")
fid = int(row["chat_id"])
return f"#{fid} • {ts} • {text}"
options = list(unrated.index)
labels = {i: _label(unrated.loc[i]) for i in options}
idx = st.selectbox(
"Pick a conversation to rate:",
options=options,
format_func=lambda i: labels[i],
key="rate_prev_select",
)
sel = unrated.loc[idx]
chat_id_prev = int(sel["chat_id"])
st.caption(f"Selected chat_id: {chat_id_prev}")
fb_logger.info("[rate-tab] selected chat_id=%s", chat_id_prev)
with st.expander("Show original question"):
st.write(sel["user_input"])
# ✅ Keep this INSIDE the tab block (indented)
preview_clicked = st.button("🔎 Preview result (validate + LIMIT 10 + execute)",
key=f"preview_{chat_id_prev}")
if preview_clicked:
fb_logger.info("[rate-tab] preview clicked chat_id=%s", chat_id_prev)
try:
fb_logger.info("[rate-tab] calling _json_list for chat_id=%s", chat_id_prev)
sqls = _json_list(sel.get("secured_sql"))
# Fallback to llm_plan if needed
if not sqls:
st.info("No SQL was recorded for this conversation.")
else:
if not SHOW_DETAILS:
st.subheader("Preview result (executed)")
else:
st.subheader("SQL Preview (executed)")
for i, raw_sql in enumerate(sqls[:2], start=1):
if SHOW_DETAILS:
st.markdown(f"**Query {i}**")
try:
fb_logger.debug("[rate-tab] validating q#%s", i)
user_text_for_guard = (sel.get("user_input") or "").strip()
validated = validate_sql(
raw_sql,
auth["role"],
auth.get("customer_id"),
schema_registry,
user_text_for_guard,
)
secured = _ensure_sql_string(validated)
try:
if not isinstance(validated, str):
fb_logger.debug("[rate-tab] validate_sql returned %s; coerced to string len=%d",
type(validated).__name__, len(secured))
except Exception:
pass
secured_limited = _apply_preview_limit(secured, limit=10)
fb_logger.debug("[rate-tab] executing q#%s", i)
df, rows, kind = run_sql_generic(secured_limited)
if SHOW_DETAILS:
st.code(secured_limited, language="sql")
if kind == "select":
try:
import pandas as pd
except Exception:
pass
df_to_show = df if 'pd' in globals() and isinstance(df, pd.DataFrame) else _as_dataframe_safely(df)
st.dataframe(df_to_show, use_container_width=True)
try:
st.caption(f"Returned {len(df_to_show)} row(s).")
except Exception:
pass
else:
st.info(f"Non-SELECT statement; rows affected: {rows}")
except SqlTimeoutError as te:
fb_logger.warning("[rate-tab] timeout q#%s chat_id=%s err=%s", i, chat_id_prev, te)
st.error(f"Timed out while executing Query {i}: {te}")
except Exception as e:
fb_logger.exception("[rate-tab] error executing q#%s chat_id=%s", i, chat_id_prev)
st.error(f"Error while executing Query {i}.")
st.exception(e)
except SqlTimeoutError as te:
fb_logger.warning("[rate-tab] preview timeout chat_id=%s err=%s", chat_id_prev, te)
st.error(f"Timed out while preparing preview: {te}")
except Exception as e:
fb_logger.exception("[rate-tab] preview failed chat_id=%s", chat_id_prev)
st.error("Preview failed due to an error.")
st.exception(e)
existing = get_chat_feedback(chat_id_prev)
fb_logger.info("[rate-tab] existing feedback for chat_id=%s -> %s", chat_id_prev, existing)
c1, c2 = st.columns(2)
with c1:
up = st.button("👍 Mark helpful", key=f"fb_prev_up_{chat_id_prev}", disabled=(existing is not None))
with c2:
dn = st.button("👎 Mark not helpful", key=f"fb_prev_dn_{chat_id_prev}", disabled=(existing is not None))
if existing is not None:
st.info("Feedback already recorded for this conversation."
+ (" (👍 Helpful)" if existing == 1 else " (👎 Not helpful)"))
else:
if up or dn:
value = 1 if up else 0
#_log_feedback_event('rate_tab.submit.call', chat_id=chat_id_prev, value=rating_value)
fb_logger.info("[rate-tab] submit feedback chat_id=%s value=%s", chat_id_prev, value)
ok, msg = update_chat_feedback_if_owner(chat_id_prev, auth["user_id"], value)
fb_logger.info("[rate-tab] update_chat_feedback_if_owner -> ok=%s msg=%s", ok, msg)
# Show message on next render and rerun to refresh lists
st.session_state['rate_flash'] = ('success' if ok else 'error', msg)
# Clear caches so the just-rated chat disappears immediately
try:
get_recent_chats_for_user.clear()
fb_logger.debug("[rate-tab] cache cleared for get_recent_chats_for_user")
except Exception:
try:
st.cache_data.clear()
fb_logger.debug("[rate-tab] global cache cleared")
except Exception:
pass
st.rerun()
# ---- Admin ----
if auth["role"] == "admin":
with tab_admin:
st.header("Admin Utilities")
tab_cu, tab_sc, tab_rd, tab_audit, tab_idx , tab_eda = st.tabs(
["👤 Create User", "🏅 Seller Scorecards", "Repopulate Tables from CSV", "🔐 Login Audit", "⚡ Create Recommended Indexes","📈 EDA"]
)
# --- Create User ---
with tab_cu:
st.subheader("Create User")
try:
with sqlite3.connect(DB_PATH) as conn:
custs = pd.read_sql_query(
"SELECT customer_id FROM customers LIMIT 200", conn
)["customer_id"].tolist()
chosen = st.selectbox("Customer ID", options=custs) if custs else None
email = st.text_input("Email", value="demo@example.com")
pw = st.text_input("Password", type="password", value="demo123")
role = st.selectbox("Role", ["user", "admin"], index=0)
if st.button("➕ Create user"):
if not email or not pw:
st.error("Email and password are required.")
elif role == "user" and not chosen:
st.error("Select a valid customer_id for normal users.")
else:
create_user(email, pw, chosen if role == "user" else None, role)
st.success(f"User created: {email} (role={role})")
except Exception as e:
st.error("Could not create user.")
st.exception(e)
with tab_sc:
# app.py (Admin → Seller Scorecard tab)
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
df = seller_scorecard_df(
conn=conn,
seller_id=st.text_input("Seller ID (optional)").strip() or None,
date_from=st.date_input("From", value=None) and str(st.session_state["From"]),
date_to=st.date_input("To", value=None) and str(st.session_state["To"]),
delivered_only=st.toggle("Delivered only", value=True),
limit=50
)
if df.empty:
st.warning("No data for the chosen filters. Try widening the date range or remove the seller filter.")
else:
st.dataframe(df, use_container_width=True)
with tab_rd:
csv_map = get_table_csv_map()
all_tables = sorted(csv_map.keys(), key=lambda t: LOAD_ORDER.index(t) if t in LOAD_ORDER else 999)
st.info(
"This will **DELETE** existing rows in the selected tables and reload data from the original CSVs. "
"Foreign-key checks are temporarily disabled during the load, and data is loaded in a safe parent→child order."
)
c1, c2 = st.columns([1, 3])
with c1:
select_all = st.checkbox("Select all tables", value=False)
with c2:
default_sel = all_tables if select_all else []
selected = st.multiselect("Pick tables to repopulate", options=all_tables, default=default_sel)
# Show current row counts for selected tables
if selected:
try:
with sqlite3.connect(DB_PATH) as conn:
before = get_row_counts(conn, selected)
df_counts = pd.DataFrame(
[{"table": t, "rows_before": before.get(t)} for t in selected]
)
st.dataframe(df_counts, use_container_width=True, hide_index=True)
except Exception as e:
st.warning(f"Could not fetch current counts: {e}")
# Confirmation gate
st.markdown("**Type `RELOAD` to confirm:**")
confirm = st.text_input("Confirmation", value="", placeholder="RELOAD")
# Optional: show the CSV mapping table for transparency
with st.expander("Show CSV file mapping"):
st.table(pd.DataFrame([{"table": t, "csv": csv_map[t]} for t in all_tables]))
# Run button
run = st.button("🚨 Repopulate selected tables", type="primary", disabled=not selected)
if run:
if (confirm or "").strip().upper() != "RELOAD":
st.error("Please type `RELOAD` to proceed.")
else:
st.info("Starting repopulation…")
ok, msg, post = repopulate_tables_from_csv(selected, logger)
if ok:
st.success(msg)
with sqlite3.connect(DB_PATH) as conn:
after = post or get_row_counts(conn, selected)
df_after = pd.DataFrame(
[{"table": t, "rows_after": after.get(t)} for t in selected]
)
st.dataframe(df_after, use_container_width=True, hide_index=True)
else:
st.error(msg)
# --- Login Audit ---
with tab_audit:
st.subheader("Login Audit (last 500)")
# Keep page state per filter set
if "audit_page" not in st.session_state:
st.session_state.audit_page = 1
if "audit_from" not in st.session_state:
st.session_state.audit_from = ist_today()
if "audit_to" not in st.session_state:
st.session_state.audit_to = ist_today()
c1, c2, c3 = st.columns([1,1,1], vertical_alignment="bottom")
with c1:
d_from = st.date_input("From date", value=st.session_state.audit_from, format="YYYY-MM-DD")
with c2:
d_to = st.date_input("To date", value=st.session_state.audit_to, format="YYYY-MM-DD")
with c3:
apply = st.button("Apply Filters", use_container_width=True)
# Normalize & guard
if d_to < d_from:
d_from, d_to = d_to, d_from # swap silently
# Reset pagination if filters changed or Apply clicked
if apply or (d_from != st.session_state.audit_from) or (d_to != st.session_state.audit_to):
st.session_state.audit_from = d_from
st.session_state.audit_to = d_to
st.session_state.audit_page = 1
# Enforce <= 5 days window
delta_days = (st.session_state.audit_to - st.session_state.audit_from).days
if delta_days > 5:
st.error("Please select a range of **5 days or fewer**.")
st.stop()
# Pagination calc
PAGE_SIZE = 10
page = st.session_state.audit_page
offset = (page - 1) * PAGE_SIZE
# Fetch counts & page
total = count_login_audit_range(st.session_state.audit_from, st.session_state.audit_to)
df_audit = get_login_audit_range(st.session_state.audit_from, st.session_state.audit_to, offset=offset, limit=PAGE_SIZE)
# Header line
left, right = st.columns([3,2])
with left:
st.caption(f"Showing {min(total, offset+1)}–{min(total, offset+len(df_audit))} of {total} "
f"for {st.session_state.audit_from} → {st.session_state.audit_to}")
with right:
# Pager controls
total_pages = max(1, (total + PAGE_SIZE - 1) // PAGE_SIZE)
prev_disabled = page <= 1
next_disabled = page >= total_pages
b1, _, b2 = st.columns([1,3,1])
with b1:
if st.button("← Prev", disabled=prev_disabled, key="audit_prev"):
st.session_state.audit_page = max(1, page - 1)
st.rerun()
with b2:
if st.button("Next →", disabled=next_disabled, key="audit_next"):
st.session_state.audit_page = min(total_pages, page + 1)
st.rerun()
st.caption(f"Page {page} / {total_pages}")
# Grid
if df_audit.empty:
st.info("No login events in the selected range.")
else:
# Optional: format columns for readability
if "success" in df_audit.columns:
df_audit["success"] = df_audit["success"].map({1: "✅ success", 0: "❌ failed"}).fillna(df_audit["success"])
st.dataframe(df_audit, use_container_width=True, hide_index=True)
# --- Index creation ---
with tab_idx:
st.subheader("Create Recommended Indexes")
st.write("These indexes speed up common Olist joins and scoping.")
if st.button("⚡ Create / Ensure Indexes"):
try:
with sqlite3.connect(DB_PATH) as conn:
stmts = ensure_performance_indexes(conn)
st.success("Indexes ensured.")
st.code("\n".join(stmts), language="sql")
except Exception as e:
st.error("Index creation failed.")
st.exception(e)
# --- Admin > EDA -------------------------------------------------------------
with tab_eda:
# --- EDA Mode ---
eda_mode = st.radio("EDA Mode", ["Summary", "Interactive"], index=0, horizontal=True, key="eda_mode_toggle")
if eda_mode == "Interactive":
eda.render_eda()
st.stop()
else:
eda.render_summary()
with tab_rag:
st.header("RAG Publisher")
try:
reg = schema_registry
except Exception as e:
logger.exception("Failed load schema registry : %s", e)
reg = load_schema_registry(os.path.join(DATA_DIR, SCHEMA_FILE))
render_rag_publisher()
def _debug_csv_map_snapshot():
try:
m = get_table_csv_map()
return json.dumps(m, ensure_ascii=False, indent=2)
except Exception as e:
return f"<error getting map: {e}>"
_REPOP_LG = _ensure_repopulate_logger()
# ---- RAG Publisher: narrow SQL logging to chat_history only (idempotent) ----
# ---- BEGIN RAG Publisher SQL logging (idempotent; chat_history-only) ----
def _install_ragpub_sql_logging():
"""
Install an idempotent sqlite3 execute() shim that logs ONLY statements which
touch the `chat_history` table. Also prints the COUNT value when a COUNT(*)
is detected so row-count probes are understandable in logs.
"""
import sqlite3 as _sqlite3
import time as _time
import re as _re
global _RAGPUB_SHIM_INSTALLED
try:
_RAGPUB_SHIM_INSTALLED
except NameError:
_RAGPUB_SHIM_INSTALLED = False
if _RAGPUB_SHIM_INSTALLED:
return
logger = _get_logger("olist")
def _touches_chat_history(sql_text: str) -> bool:
try:
s = (sql_text or "").lower()
except Exception:
return False
# be tolerant to quoting / aliasing
return bool(_re.search(r"\bchat_history\b", s))
_orig_execute = _sqlite3.Connection.execute
_orig_executescript = _sqlite3.Connection.executescript
def _wrap_execute(self, sql, params=()):
sql_str = sql if isinstance(sql, str) else str(sql)
if not _touches_chat_history(sql_str):
return _orig_execute(self, sql, params)
start = _time.perf_counter()
try:
cur = _orig_execute(self, sql, params)
rows = cur.fetchall() if cur.description else []
elapsed = _time.perf_counter() - start
cols = [d[0] for d in (cur.description or [])]
# If it's a COUNT probe, pull the scalar for clarity
sql_lc = (sql_str or "").lower()
count_note = ""
try:
if "count(" in sql_lc:
count_val = None
if rows:
first = rows[0]
try:
# mapping-like then positional
if hasattr(first, "keys"):
for k in ("n", "count", "count_1", "total", "cnt"):
if k in first.keys():
count_val = first[k]; break
if count_val is None and isinstance(first, (list, tuple)) and first:
count_val = first[0]
except Exception:
pass
if count_val is not None:
count_note = f" (COUNT={count_val})"
except Exception:
pass
logger.info("[ragpub] read_sql: %s", sql_str)
logger.info("[ragpub] result rows=%s cols=%s (%.3fs)%s",
len(rows), cols, elapsed, count_note)
# Return a lightweight cursor-like facade compatible with pandas.read_sql usage
# In practice, our code paths call sqlite3 directly for COUNT; others
# consume DataFrames already, so returning the raw rows is fine.
return rows
except Exception as e:
elapsed = _time.perf_counter() - start
logger.exception("[ragpub] SQL error after %.3fs: %s", elapsed, e)
raise
def _wrap_executescript(self, sql_script):
return _orig_executescript(self, sql_script)
_sqlite3.Connection.execute = _wrap_execute
_sqlite3.Connection.executescript = _wrap_executescript
_RAGPUB_SHIM_INSTALLED = True
# Install once (safe no-op on reruns)
try:
_install_ragpub_sql_logging()
except Exception:
pass
# ---- END RAG Publisher SQL logging ----
from __future__ import annotations
import logging
import re as _re
# ---- BEGIN RAG Publisher SQL logging (idempotent, single-pre/post; chat_history only) ----
# --- RAG Publisher SQL logging (safe wrapper; no C-type monkey patching) ---
# === Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
# === Prompt & SQL Loggers ===
prompt_logger = logging.getLogger("prompt")
sql_logger = logging.getLogger("sql")
for _lg in (prompt_logger, sql_logger):
if not _lg.handlers:
_h = logging.StreamHandler()
_h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
_lg.addHandler(_h)
_lg.setLevel(logging.INFO)
# === End Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
import re as _re
from typing import Any as _Any
_REDACTIONS = [
(_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"),
(_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"),
(_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"),
]
def _redact(text: str) -> str:
if not text:
return text
out = text
for _rx, _repl in _REDACTIONS:
out = _rx.sub(_repl, out)
return out
def _truncate(text: str, max_chars: int = 4000) -> str:
if not text:
return text
return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>"
def _j(_obj: _Any) -> str:
try:
import json as _json
return _json.dumps(_obj, ensure_ascii=False, default=str)
except Exception:
return str(_obj)
def log_llm_inputs(event: str,
user_id: str | None,
chat_id: str | None,
model: str,
messages: list[dict],
context: str | None = None,
exemplars: list[dict] | None = None,
plan: dict | None = None,
extra: dict | None = None):
payload = {
"event": event,
"user_id": user_id,
"chat_id": chat_id,
"model": model,
"context": _truncate(_redact(context or ""), 3000),
"exemplars": exemplars or [],
"plan": plan or {},
"messages": [],
"extra": extra or {}
}
for m in messages or []:
payload["messages"].append({
"role": m.get("role"),
"name": m.get("name"),
"content": _truncate(_redact(str(m.get("content", ""))), 3000)
})
try:
prompt_logger.info(_j(payload))
except Exception as _e:
try:
prompt_logger.warning("log_llm_inputs failed: %s", _e)
except Exception:
pass
def log_secured_sql(chat_id: str | None,
user_id: str | None,
secured_sql: str | None,
sql_params: dict | list | None = None,
table_guard: dict | None = None):
if not secured_sql:
return
payload = {
"event": "secured_sql_ready",
"chat_id": chat_id,
"user_id": user_id,
"sql": _truncate(_redact(secured_sql), 4000),
"params": sql_params or {},
"guards": table_guard or {},