prazy1208/text2sql

1# Supabase setup for Text2SQL (from scratch)2 3Use this when you want a **hosted Postgres** instead of local PostgreSQL. The app reads **`DATABASE_URL`** from `.env` (see `backend/config.py`).4 5---6 7## 1. Create a Supabase project8 91. Go to [https://supabase.com](https://supabase.com) and sign in.102. **New project** → choose organization, name, region, and **database password**.11 12This password is the **`postgres` user password** for the default database. Store it in a password manager.13 14---15 16## 2. Change or reset the database password later17 181. Open your project → **Project Settings** (gear) → **Database**.192. Under **Database password**, use **Reset database password** (or set a new one if your UI offers it).203. **Update `.env` immediately** with the new password in `DATABASE_URL` (or in `DB_PASSWORD` if you use split variables).21 22Any app or script using the old password will fail until you update `.env`.23 24**Password with special characters:** If the password contains `@`, `:`, `/`, `#`, etc., it must be **URL-encoded** inside the connection URI, or use the **Connection pooling** URI from the dashboard (Supabase often shows a copy-paste string that is already safe).25 26---27 28## 3. Connection string for this codebase29 301. In Supabase: **Project Settings** → **Database**.312. Find **Connection string** → **URI** (sometimes labeled “psql” or “SQLAlchemy”).323. It looks like:33 34   `postgresql://postgres.[ref]:[YOUR-PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres`  35   or direct:36 37   `postgresql://postgres:[YOUR-PASSWORD]@db.[project-ref].supabase.co:5432/postgres`38 394. Put it in **`.env`** as a **single line** (no spaces around `=`):40 41   ```env42   DATABASE_URL=postgresql://postgres:YOUR_PASSWORD@db.xxxxx.supabase.co:5432/postgres43   ```44 455. **SSL:** Supabase requires TLS. Either:46   - append **`?sslmode=require`** to the URL yourself, or  47   - rely on **`backend.config.get_engine()`**, which appends `sslmode=require` automatically when the host is `*.supabase.co` or `*.pooler.supabase.com`.48 496. **Prefer `DATABASE_URL`:** If `DATABASE_URL` is set, **`DB_*` variables are ignored**. Use one style only to avoid confusion.50 51---52 53## 4. Load the schema (tables the API expects)54 55The API uses **`app_schema`** (sessions, intent/table/column outputs) and domain schemas (`healthcare_schema`, etc.).56 571. Supabase → **SQL Editor** → **New query**.582. Paste the contents of **`scripts/complete_setup.sql`** from this repo and **Run**.59 60Confirm there are no errors. This creates schemas and tables the Stage 1 pipeline needs.61 62If you use optional features (e.g. few-shot examples in `system_schema`), run the matching scripts from `scripts/` and `docs/FEWSHOT_PIPELINE_IMPLEMENTATION.md` as needed.63 64---65 66## 5. Local `.env` checklist67 68- Copy **`.env.example`** to **`.env`** at the project root.69- Set **`DATABASE_URL`** (recommended for Supabase).70- Add at least one LLM key (**`GEMINI_API_KEY`** or **`OPENAI_API_KEY`**).71- Never commit **`.env`** (keep it gitignored).72 73---74 75## 6. Run the API76 77From the project root:78 79```bash80uvicorn backend.api.main:app --reload81```82 83If the DB URL is wrong, you will see errors on first request that touches the DB (e.g. creating a session).84 85---86 87## 7. Browser storage after switching databases88 89Chat **transcripts and session rows** live in Postgres (`app_schema.sessions`, `app_schema.chat_messages`). The UI only stores:90 91- `text2sql_client_id` — anonymous scope for listing “your” sessions (`GET /sessions`)92- `text2sql_active_session` — last opened session id (reload convenience)93 94If you **point the app to a new database** (new Supabase project or reset DB), old session UUIDs are invalid.95 96**Fix:** Clear site data for this origin (or remove those keys under DevTools → Application → Local Storage), then reload. The app will create or pick sessions via the API.97 98---99 100## 8. Optional: connection pooler vs direct101 102- **Direct** (`db.[ref].supabase.co:5432`): fine for the API and scripts.103- **Pooler** (port **6543**): useful for serverless/high concurrency; use the URI Supabase shows for your driver.104 105Use the exact string from the dashboard when possible.106 107---108 109## 9. Troubleshooting110 111| Symptom | What to check |112|--------|----------------|113| SSL / connection errors | `sslmode=require` on the URL (or use `get_engine()` as above). |114| Authentication failed | Wrong password in URI; reset in Supabase and update `.env`. |115| `Invalid or unknown session_id` | Stale session id vs current DB; clear site storage or pick another chat (see §7). |116| Table does not exist | Run `complete_setup.sql` (or migrations) on this Supabase project. |117 118For a full local Postgres walkthrough (pgAdmin, etc.), see **`Text2SQL_PostgreSQL_Setup_Guide.md`**.119