Metadata-Version: 2.4
Name: db-chat-widget
Version: 0.2.0
Summary: Embeddable chatbot widget that answers natural-language questions about your database.
Project-URL: Homepage, https://github.com/armandkouassi/db-chat-widget
Project-URL: Issues, https://github.com/armandkouassi/db-chat-widget/issues
Author: Armand Kouassi
License-Expression: MIT
License-File: LICENSE
Keywords: chatbot,database,llm,nl2sql,sql,widget
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.9
Requires-Dist: anthropic>=0.34
Requires-Dist: groq>=0.11
Requires-Dist: httpx>=0.27
Requires-Dist: openai>=1.30
Requires-Dist: psycopg2-binary>=2.9
Requires-Dist: pymysql>=1.1
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: sqlglot>=23.0
Provides-Extra: dev
Requires-Dist: fastapi>=0.110; extra == 'dev'
Requires-Dist: httpx>=0.27; extra == 'dev'
Requires-Dist: jinja2>=3.1; extra == 'dev'
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pydantic>=2.0; extra == 'dev'
Requires-Dist: pytest-asyncio>=0.23; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.4; extra == 'dev'
Requires-Dist: uvicorn>=0.29; extra == 'dev'
Provides-Extra: server
Requires-Dist: fastapi>=0.110; extra == 'server'
Requires-Dist: jinja2>=3.1; extra == 'server'
Requires-Dist: pydantic>=2.0; extra == 'server'
Requires-Dist: uvicorn>=0.29; extra == 'server'
Description-Content-Type: text/markdown

# db-chat-widget

Embeddable chatbot widget that lets end users ask natural-language questions
about a SQL database and get back answers with the underlying query and data.

- **Any SQLAlchemy-compatible database**: PostgreSQL, MySQL, SQLite, etc.
- **Pluggable LLM backend**: Anthropic Claude, OpenAI, Groq, or a local Ollama model.
- **Read-only by default**: generated SQL is parsed and only `SELECT`
  statements are allowed unless you explicitly opt into write access.
- **Embeddable widget**: a small vanilla-JS chat component you can drop into
  any existing web page with a single `<script>` tag, backed by a FastAPI
  server.
- **Holds a real conversation**: remembers prior turns per visitor session
  (a server-side, cookie-backed store) — not just database questions,
  ordinary chit-chat too — and always replies in whatever language the
  visitor is writing in.

## Install

```bash
pip install "db-chat-widget[server]"
```

This includes every LLM provider (Anthropic, OpenAI, Groq, Ollama), every
database driver (PostgreSQL, MySQL), and the standalone FastAPI server + CLI
`serve` command used below. Pick which provider and database to use
afterwards, at configuration time, via `--llm-provider` / `Settings.llm_provider`
and `--db-url` / `Settings.db_url`.

If you only need the `ChatEngine` as a library — e.g. from
[django-db-chat-widget](https://github.com/krak225/django-db-chat-widget),
which brings its own web layer — `pip install db-chat-widget` (without
`[server]`) skips FastAPI/uvicorn/Jinja2 entirely.

## Quickstart

```bash
export DB_CHAT_LLM_API_KEY=gsk_...   # your Groq API key

db-chat-widget serve \
  --db-url "postgresql://user:pass@localhost/mydb" \
  --llm-provider groq \
  --port 8000
```

(`--llm-provider anthropic` / `openai` work the same way, with the matching API key.)

Open http://127.0.0.1:8000 for a demo chat page, or embed the widget in your
own site.

### Popup mode (floating chat bubble)

Drops a chat bubble launcher into the bottom-right corner of the page — the
most common way to bolt this onto an existing template with zero markup
changes. Clicking it opens/closes the chat panel.

```html
<link rel="stylesheet" href="http://127.0.0.1:8000/static/widget.css" />
<script
  src="http://127.0.0.1:8000/static/widget.js"
  data-mode="bubble"
  data-api-url="http://127.0.0.1:8000/api/chat"
  data-title="Ask about our data"
></script>
```

Use `data-position="bottom-left"` to anchor it to the bottom-left instead.

### Inline mode

Embeds the chat panel directly into an element already on the page, instead
of floating:

```html
<link rel="stylesheet" href="http://127.0.0.1:8000/static/widget.css" />
<div id="my-db-chat"></div>
<script
  src="http://127.0.0.1:8000/static/widget.js"
  data-target="#my-db-chat"
  data-api-url="http://127.0.0.1:8000/api/chat"
  data-title="Ask about our data"
></script>
```

### Programmatic API

Both modes are also available from JavaScript, e.g. after dynamically
injecting the script, or to control the popup (`.open()` / `.close()`):

```html
<script src="http://127.0.0.1:8000/static/widget.js"></script>
<script>
  const chat = DBChatWidget.init({
    mode: "bubble", // or "inline" (requires `target`)
    apiUrl: "http://127.0.0.1:8000/api/chat",
    title: "Ask about our data",
    position: "bottom-right", // or "bottom-left"
  });
  // chat.open(); chat.close(); chat.ask("How many orders today?");
</script>
```

See [`examples/embed_example.html`](examples/embed_example.html) and
[`examples/demo_app.py`](examples/demo_app.py) for a runnable, self-contained
example with a seeded SQLite database.

## Using it as a library

```python
from db_chat_widget import ChatEngine, Settings

settings = Settings(
    db_url="sqlite:///mydb.sqlite",
    llm_provider="groq",
    llm_api_key="gsk_...",
    read_only=True,          # default: only SELECT statements are allowed
    max_rows=200,             # cap on rows returned per query
    allowed_tables=None,      # optionally restrict which tables can be queried
    history_max_turns=10,     # conversation turns kept for context; 0 disables memory
)

engine = ChatEngine(settings)
result = engine.ask("How many orders were placed last month?")

print(result.answer)
print(result.sql)
print(result.rows)

# Continue the conversation by passing back prior turns yourself — ChatEngine
# holds no state between calls (the FastAPI server and django-db-chat-widget
# do this for you automatically via a session):
history = [
    {"role": "user", "content": "How many orders were placed last month?"},
    {"role": "assistant", "content": result.answer},
]
follow_up = engine.ask("And the month before that?", history=history)
```

To mount just the FastAPI app (e.g. behind your own ASGI server or reverse
proxy):

```python
from db_chat_widget.config import Settings
from db_chat_widget.server.app import create_app

app = create_app(Settings(db_url="...", llm_provider="openai", llm_api_key="..."))
```

## How it works

1. The database schema (tables, columns, types, foreign keys) is introspected
   via SQLAlchemy and sent to the configured LLM as context, along with the
   last `history_max_turns` turns of the conversation for continuity.
2. The LLM classifies the message into one of three replies, always in the
   visitor's own language: a SQL `SELECT` statement (a data question), a free
   conversational reply prefixed with `CHAT:` (a greeting, small talk, or any
   general, non-data question — answered directly, no query is run), or
   `CANNOT_ANSWER` (a data question the schema can't answer).
3. For SQL replies, the query is parsed with `sqlglot` and rejected if it
   contains anything other than a single read-only `SELECT` (no
   `INSERT`/`UPDATE`/`DELETE`/`DROP`/multiple statements/disallowed tables).
4. The query is executed with a row limit, and the LLM is asked to phrase a
   short, natural-language answer from the actual result rows — again in the
   visitor's language.

## Default encoding

MySQL/MariaDB servers commonly default their client connection to `latin1`,
which mangles accented characters (e.g. French text) unless the client asks
for UTF-8 explicitly. When your `db_url` is a MySQL URL without a `charset`
query parameter, `db-chat-widget` automatically connects with
`charset=utf8mb4`:

```
mysql+pymysql://user:pass@localhost:3306/mydb
# connects as:
mysql+pymysql://user:pass@localhost:3306/mydb?charset=utf8mb4
```

If your URL already specifies a `charset` (e.g. `?charset=latin1`), it is left
untouched. Other database backends (PostgreSQL, SQLite) already default to
UTF-8 and are not affected.

## Safety notes

- `read_only=True` (the default) is enforced at the SQL-parsing level, not
  just by prompting the model — treat it as the actual security boundary.
- Use `allowed_tables` to scope the chatbot to specific tables (e.g. exclude
  a `users`/`secrets` table containing sensitive columns).
- The demo server enables permissive CORS (`*`) for convenience; pass
  `cors_origins=[...]` (or `--cors-origin` on the CLI) to restrict this in
  production.
- Run the database user backing `db_url` with least-privilege, read-only
  grants where possible, as defense in depth.
- The FastAPI server keeps conversation history in an in-memory store keyed
  by an opaque, `httponly` session cookie — it isn't shared across visitors,
  but it also doesn't survive a server restart or scale across multiple
  worker processes. For production multi-worker deployments, run a single
  worker or swap in your own shared store (e.g. Redis) ahead of `ChatEngine.ask`.

## Development

```bash
pip install -e ".[dev]"
pytest
ruff check .
```

## License

MIT
