prazy1208/text2sql

1# Table Agent (5a) — Implementation Plan2 3Saved in-repo for step-by-step execution. (Synced from Cursor plan `table_agent_5a`.)4 5## Step-by-step order (do in this sequence)6 7| Step | What | Main files |8|------|------|------------|9| **1** | Table metadata retrieval: load `metadata_store/{schema}_metadata.json`, optional FAISS top-10 when N>10; **expose table-level only** (schema, table name, description) to the Table Agent — columns stay in JSON for index/embeddings and for a later agent | `backend/services/table_metadata_retrieval.py` |10| **2** | Table Agent: LLM prompt, JSON `selected_tables`, validate against candidates | `backend/agents/table_agent.py` |11| **3** | DB: new `app_schema.table_agent_output` (FK `intent_output_id` → `intent_agent_output.id`); `insert_intent_output` returns `id`; `insert_table_agent_output(...)` | `scripts/create_app_schema.sql`, migration SQL, `backend/api/db.py` |12| **4** | API: extend `POST /query` — after Intent, run Table Agent; persist table row; add `selected_tables` to response | `backend/api/routes/query.py` |13| **5** | Frontend: show `selected_tables` | `frontend/index.html`, `frontend/app.js` |14| **6** | (Optional) Update `docs/PROJECT_STRUCTURE.md` / setup guide | docs — **done** (structure + §7 SQL snippet include `table_agent_output`) |15 16**Checkpoint:** After each step you can commit and test before moving on.17 18---19 20## Context (current repo)21 22- **Table metadata + FAISS** from `build_vector_store.py`: indexes embed full per-table text (including columns). **Table Agent** receives only `schema_name`, `table_name`, `table_description` (no columns in prompts/candidate dicts for now).23- **Today each domain has 3 tables** → “≤10 tables → all to LLM” path. Implement “>10 → FAISS top-10 then LLM” for future growth.24- **Intent output:** `backend/agents/intent_agent.py` → `rephrased_question`, `keywords`, `business_insights`.25- **API:** `backend/api/routes/query.py` — extend so Intent + Table Agent run in **one** `POST /query` response.26 27```mermaid28flowchart LR29  intent[Intent Agent]30  tableSvc[Table metadata FAISS optional]31  tableLLM[Table LLM]32  intent --> tableSvc33  tableSvc --> tableLLM34```35 36---37 38## Design39 40### Inputs41 42- `use_case`, `rephrased_question`, `keywords`43 44### Candidate selection (before LLM)45 461. `schema = USE_CASE_TO_SCHEMA[use_case]` (`backend/config.py`).472. Load metadata JSON for that schema.483. `N = len(metadata_list)`.494. **If N ≤ 10:** candidates = all tables (table-level text: schema, name, description — no columns).505. **If N > 10:** FAISS on `{schema}.index`, query = rephrased + keywords, top `k = min(10, N)`, map indices to metadata. Reuse embedding model `all-MiniLM-L6-v2` (shared cache with business-rules if practical).51 52### Table LLM53 54- Prompt includes rephrased question, keywords, numbered candidate table descriptions.55- Response: JSON only, e.g. `{ "selected_tables": ["schema.table_name", ...] }` — validate names against candidates.56- `chat_completion` from `backend/services/llm_client.py`.57 58### Output59 60- `run_table_agent(...) -> { "selected_tables": list[str], ... }`61 62### New modules63 64| Piece | Location |65|-------|----------|66| FAISS + metadata | `backend/services/table_metadata_retrieval.py` |67| Agent + prompt | `backend/agents/table_agent.py` |68 69---70 71## API and persistence72 731. **QueryResponse:** add `selected_tables: list[str]` (and error handling policy: partial success vs fail-all — choose when implementing).742. **Flow:** `run_intent` → `run_table_agent` with intent fields.753. **No new columns on `intent_agent_output`.** New table **`app_schema.table_agent_output`**:76   - `id` SERIAL PK77   - `intent_output_id` INT NOT NULL REFERENCES `app_schema.intent_agent_output(id)` ON DELETE CASCADE78   - `selected_tables` TEXT[]79   - `created_at` TIMESTAMPTZ DEFAULT now()804. **Insert:** `INSERT intent_agent_output ... RETURNING id` → `INSERT table_agent_output(intent_output_id, selected_tables)`.815. **Reads:** join `table_agent_output` to `intent_agent_output` on `intent_output_id`.82 83```mermaid84erDiagram85  intent_agent_output ||--o| table_agent_output : intent_output_id86  intent_agent_output {87    int id PK88    uuid session_id FK89  }90  table_agent_output {91    int id PK92    int intent_output_id FK93    text_array selected_tables94  }95```96 97---98 99## Frontend100 101- Display `selected_tables` in the intent output area.102 103## Testing104 105- POST /query with a retail question; expect plausible table names from metadata (e.g. `retail_schema.orders`).106 107## Optional108 109- Per-logger log level for `huggingface_hub` / `httpx` to reduce noise when loading embeddings.110