webapp/WebApp.md

Rama main · réplica de lectura del repositorio original.

# Web App

A small Flask front-end for the local SQL agent (NLQ engine). Users log in,
ask natural-language questions (Persian supported), and see the generated SQL,
a plain-language interpretation, and the result rows — the full result set is
also exported to a CSV under `outputs/`.

## Structure

```
webapp/
├── app.py                # Flask entry point: routes (/login, /register, /logout, /) + create-user CLI
├── agent.py              # Bridge to the repo's NLQ engine (api.runner.run_query); exports rows to CSV
├── db.py                 # SQLite persistence: users + logs tables in app.db
├── requirements.txt      # flask, python-dotenv
├── .gitignore
├── .secret_key           # auto-generated session secret (created on first run)
├── app.db                # SQLite database (auto-created; users + query logs)
├── outputs/              # CSV exports of query results: output_YYYYMMDD_HHMMSS.csv
├── static/
│   └── style.css         # styles shared by all pages
├── templates/
│   ├── base.html         # layout used by every page
│   ├── index.html        # main page: ask a question, view SQL / interpretation / result table
│   ├── login.html        # login form
│   └── register.html     # admin-only account creation form
└── __pycache__/          # generated bytecode (git-ignored)
```

## How it works

- `agent.py` adds the repo root to `sys.path` and loads `.env` from there, so
  the web app runs the exact same generation → validation → execution pipeline
  as the CLI / API (`api.runner.run_query`).
- Every question and its outcome is logged to the `logs` table in `app.db`.
- Up to 100 result rows are rendered on the page; the full set goes to the CSV.
- Passwords are stored as salted hashes (werkzeug), never plain text.

## Prerequisites

- Python 3.13 (the app is invoked as `py -3.13`)
- The repo-root `.env` file (copy `.env.example`) with:
  - `OPENAI_BASE_URL` / `OPENAI_MODEL` (and `OPENAI_API_KEY` if the endpoint
    wants one) — any OpenAI-compatible endpoint serving the model (vLLM, LM
    Studio, Ollama's `/v1`)
  - `DB_CONNECTION_URL` and `DB_PASSWORD` — the SQL Server connection, or a
    `project_config/datasources.yaml` with one `DB_PASSWORD_*` per source when
    the deployment queries several databases (`docs/deployment-runbook.md`
    §16); the web app goes through the same pipeline, so it follows
    whichever the API uses
- The repo-root dependencies (`api` package) installed

## Running

From this folder (`webapp/`):

```powershell
# 1. Install the web app dependencies
pip install -r requirements.txt

# 2. Install the repo-root (NLQ engine) dependencies — once, from the repo root
pip install -r ..\requirements.lock

# 3. Create a user account (password is prompted if omitted)
py -3.13 app.py create-user alice
# or: py -3.13 app.py create-user alice s3cret

# 4. Start the dev server
py -3.13 app.py
```

Then open http://127.0.0.1:5000 and log in.

## Notes

- `ADMIN_USER` **must** be set (no default — a security-audit fix removed
  the literal username that used to ship here). Only that account can
  create new accounts via `/register`; the app refuses to start at all
  without it.
- The session secret comes from the `SECRET_KEY` env var if set; otherwise a
  random key is generated once and persisted in `.secret_key` (owner-only
  permissions on POSIX — see `_secret_key()`'s docstring).
- Sessions last 8 hours.
- Repeated failed `/login` attempts from the same IP are throttled: after
  roughly 30 consecutive failures within a 15-minute window, further
  attempts get `429` until a login from that IP succeeds, or the window
  rolls past with no further failures — this throttle expires on its own
  and does not lock an IP out indefinitely.
- Every POST form, `/login` included, carries a CSRF token and is
  rejected with `400` if it is missing or wrong — see `app.py`'s
  `_CSRF_EXEMPT_ENDPOINTS` comment for how a token is bound to a session
  before that session has ever authenticated.
- **The session cookie requires HTTPS** (`SESSION_COOKIE_SECURE=True`).
  Running the dev server above over plain `http://` still lets you load
  every page, but the browser will not send the cookie back, so
  `/login` will look like it silently does nothing — you get bounced
  back to the login page after what looked like a successful sign-in.
  Serve the app over HTTPS (a reverse proxy terminating TLS is the usual
  shape for this) to actually keep a session. This is intentionally not
  an environment-variable toggle: a flag that is easy to flip off is a
  flag that stays off past whatever local experiment prompted it — if
  you need it off for local HTTP-only development, comment out that one
  line in `create_app()` and remember why before you deploy.
- `app.db` is created automatically on first run (`db.init_db()`).
- Query result CSVs are written to `outputs/` (UTF-8 with BOM, so Excel opens
  Persian text correctly) with formula-injection defusing applied to every
  cell (`exporters/sanitize.py`).