prazy1208/text2sql

1# Chat UI, sessions, and multi-chat API2 3The Stage 1 web UI is a single-page chat served by FastAPI from [`frontend/`](../frontend/). Conversations are **stored in Postgres**; the browser keeps only a **client id** (anonymous scope) and **last active session id**.4 5---6 7## Project layout (frontend + session-related backend)8 9| Path | Role |10|------|------|11| [`frontend/index.html`](../frontend/index.html) | Shell: header, **sidebar** (chat list + **New chat**), chat column, composer. |12| [`frontend/app.js`](../frontend/app.js) | Loads use cases, lists sessions, switches chats, calls `/query`; legacy chat HTML storage removed. |13| [`frontend/styles.css`](../frontend/styles.css) | Sidebar + main column layout. |14| [`backend/api/routes/query.py`](../backend/api/routes/query.py) | `GET /sessions`, `GET /sessions/{id}/messages`, `POST /session`, `POST /query`. |15| [`backend/api/db.py`](../backend/api/db.py) | `sessions`, `chat_messages`, list/filter helpers, title/use_case updates. |16| [`scripts/create_app_schema.sql`](../scripts/create_app_schema.sql) | Defines `app_schema.sessions` (incl. `title`, `client_id`, `use_case`) and `chat_messages`. |17 18---19 20## HTTP API (session + chat)21 22Base URL: same origin as the UI when using the bundled static server (`uvicorn backend.api.main:app`).23 24| Method | Path | Description |25|--------|------|-------------|26| `GET` | `/use-cases` | Domain dropdown values. |27| `POST` | `/session` | Create a new session. Optional **`client_id`** query param or **`X-Client-Id`** header (UUID). |28| `GET` | `/sessions?client_id=<uuid>&limit=...` | List sessions where **`client_id` equals that UUID** (strict — NULL `client_id` rows are omitted). **Only sessions with at least one `chat_messages` row** are returned. Backfill NULL `client_id` for your browser: [`BACKFILL_SESSIONS.md`](BACKFILL_SESSIONS.md). |29| `GET` | `/sessions/{session_id}/messages` | Full transcript (ordered). If the session has **`client_id`**, pass the same UUID via **`X-Client-Id`** or **`client_id`** query (required). |30| `GET` | `/sessions/{session_id}/pipeline-turns` | One object per **`intent_agent_output`** row, joined to table/column/few-shot/gen-sql outputs (same client guard as messages). The UI uses this with **`messages`** to rebuild rich assistant bubbles after switching chats. |31| `DELETE` | `/sessions/{session_id}?client_id=` | Deletes the session and cascaded app data. Requires **`X-Client-Id`** or **`client_id`** matching **`sessions.client_id`**; sessions with NULL `client_id` cannot be deleted via this route. |32| `POST` | `/query` | Pipeline request. Body includes `message`, `use_case`, optional `session_id`, `message_type`, `confirmation`, optional **`client_id`** (used when the server creates a new session). |33 34Session **title** is set **once** on the server from the first user message of type `new_query` or `intent_correction` (trimmed / length-capped); it is **not** overwritten later.35 36---37 38## Database (`app_schema`)39 40**`sessions`**41 42- `session_id` (UUID, PK)43- `created_at`, `updated_at`44- `title` (TEXT, nullable until first qualifying user message)45- `client_id` (UUID, nullable — anonymous scope for `/sessions` listing)46- `use_case` (VARCHAR(64), nullable — updated when queries run)47 48**`chat_messages`**49 50- `id`, `session_id`, `role`, `message_type`, `content`, `created_at`51 52Inserts bump `sessions.updated_at` (see `insert_chat_message` in `backend/api/db.py`).53 54---55 56## “New chat” behavior57 58**New chat** clears the composer area and clears the active session id until you **Send**. The first message runs `POST /query` **without** `session_id`; the API creates the session (with `client_id`) and sets the title from that question. That avoids creating many empty sessions that all showed as **New chat** in the sidebar.59 60## Browser `localStorage`61 62| Key | Purpose |63|-----|---------|64| `text2sql_client_id` | Generated once per browser profile; identifies rows for `GET /sessions`. |65| `text2sql_active_session` | Last selected `session_id` so reload returns to the same chat when it still exists. |66 67Removed on load if present (legacy): `text2sql_chat_history`, `text2sql_session_id`.68 69---70 71## Operational notes72 73- After **database reset** or pointing `.env` at a new Postgres instance, old session UUIDs are invalid. Clear site storage or open the app fresh; the UI creates or selects sessions via the API.74- For Supabase or hosted Postgres, apply [`scripts/create_app_schema.sql`](../scripts/create_app_schema.sql) (or your combined setup script) so `sessions` includes `title`, `client_id`, and `use_case`.75 76**Backfill** old `sessions.title` or `client_id`: [`BACKFILL_SESSIONS.md`](BACKFILL_SESSIONS.md).77 78See also: [`PROJECT_STRUCTURE.md`](PROJECT_STRUCTURE.md), [`SUPABASE_SETUP.md`](SUPABASE_SETUP.md).79