prazy1208/text2sql

1# Project structure2 3Layout for the Text2SQL backend and scripts. Keeps agents, services, and config organized as more agents are added (Table, Column, Few-Shot, Gen-SQL, SQL Validator).4 5## Root6 7```8Text2SQL project/9├── .env10├── docs/11│   ├── PROJECT_STRUCTURE.md      # this file12│   ├── CHAT_UI_AND_SESSIONS.md   # multi-chat UI, GET /sessions, client_id, localStorage13│   ├── MULTI_CHAT_SIDEBAR_PLAN.md # pointer: multi-chat feature status + link to CHAT_UI doc14│   ├── STAGE1_FINALIZED_PLAN.md15│   ├── TABLE_AGENT_5A_PLAN.md    # Table Agent implementation plan (5a)16│   ├── COLUMN_AGENT_5B_PLAN.md   # Column Agent implementation plan (5b)17│   ├── INSTALLATION_REQUIREMENTS.md18│   ├── SUPABASE_SETUP.md           # hosted Postgres: URI, password, schema, sessions19│   ├── RELATIONSHIPS_PIPELINE.md   # FK table_relationships pipeline (per-domain schema)20│   └── Text2SQL_PostgreSQL_Setup_Guide.md21├── requirements.txt22├── build_vector_store.py         # table/column metadata → FAISS + metadata_store23├── build_relationship_embeddings.py  # FK table_relationships → embeddings JSON (no FAISS)24├── build_few_shot_metadata_store.py  # system_schema.few_shot_examples → metadata_store JSON25├── build_business_rules_vector_store.py   # business rules → FAISS + business_rules_store26├── generate_data.py27├── faiss_indexes/                # FAISS indexes (schema + business_rules_*)28├── metadata_store/               # table/column metadata JSON (per schema)29├── business_rules_store/         # business-rules metadata JSON (per schema)30├── backend/                      # API and pipeline31├── scripts/                      # create_app_schema.sql, backfill_session_metadata.sql, run_*.py, domain FK DDL, migrations32└── frontend/                     # Chat UI: sidebar (multi-session), thread, composer; index.html, styles.css, app.js; served at / by FastAPI33```34 35## Backend36 37```38backend/39├── __init__.py40├── config.py                     # PROJECT_ROOT, get_engine(), DOMAIN_SCHEMAS, paths, RELATIONSHIP_METADATA_NAMES, USE_CASES41├── agents/                       # one module per agent42│   ├── __init__.py43│   ├── intent_agent.py           # Stage 1: rephrase + keywords + business_insights44│   ├── few_shot_agent.py         # LLM picks pattern ids (id + question + type); returns full rows for Gen-SQL45│   ├── table_agent.py            # Table selection: shortlist + LLM → selected_tables (schema.table); FK-aware46│   ├── column_agent.py           # Column selection: shortlist + LLM → selected_columns per table; FK-aware47│   ├── gen_sql_agent.py          # SQL synthesis from context + few-shot; optional relationship_text lines48│   └── ...49├── services/                     # shared services used by agents50│   ├── __init__.py51│   ├── business_rules_retrieval.py   # FAISS retrieval → list of insight strings52│   ├── llm_client.py                 # OpenAI / Gemini chat_completion53│   ├── table_metadata_retrieval.py   # table metadata + optional FAISS shortlist54│   ├── column_metadata_retrieval.py # column candidates for selected tables; threshold + column FAISS55│   ├── relationship_retrieval.py     # FK rows from metadata JSON (API); DB list helper for build scripts56│   ├── fewshot_retrieval.py          # few_shot catalog JSON (+ optional DB fallback)57│   ├── sql_validator.py              # rule-based checks on generated SQL (Gen-SQL pipeline)58│   └── ...59└── api/                          # FastAPI app and routes60    ├── __init__.py61    ├── main.py                   # App entry point; mounts routers (uvicorn backend.api.main:app)62    ├── db.py                     # sessions (title, client_id, use_case), chat_messages, intent/table/column outputs, session_memory63    └── routes/                   # One module per agent/flow64        ├── __init__.py65        ├── query.py              # GET /use-cases, GET /sessions, GET /sessions/{id}/messages, POST /session, POST /query (full pipeline incl. Gen-SQL + validation)66        └── ...67```68 69## Conventions70 71- **Agents** live under `backend/agents/`. Each agent has a single entry point (e.g. `run_intent(user_message, use_case)`) and uses **services** and **config**.72- **Services** are reusable pieces (retrieval, LLM client, DB helpers) used by one or more agents. No agent-specific logic.73- **Config** (`backend/config.py`) holds env, paths, and constants. No business logic.74- **API:** The app lives in `backend/api/main.py` (not at backend root). To add more agents: add a new router in `api/routes/<name>.py` and `app.include_router(...)` in `main.py`. Keep one route module per flow (query = Intent, later sql = Gen-SQL).75- Run the app from **project root**: `uvicorn backend.api.main:app --reload`.76 77## Reference78 79- Chat UI, session APIs, `localStorage` keys: `docs/CHAT_UI_AND_SESSIONS.md`80- Multi-chat feature pointer: `docs/MULTI_CHAT_SIDEBAR_PLAN.md`81- Backfill session title / client_id: `docs/BACKFILL_SESSIONS.md`82- Supabase (hosted DB) from scratch: `docs/SUPABASE_SETUP.md`83- Stage 1 scope and order: `docs/STAGE1_FINALIZED_PLAN.md`84- Table Agent (5a) — metadata shortlist, LLM selection, DB, API, UI: `docs/TABLE_AGENT_5A_PLAN.md`85- Column Agent (5b) — column metadata, threshold/FAISS, LLM, DB, API: `docs/COLUMN_AGENT_5B_PLAN.md`86- Full agent architecture (Phase 3): see plan referenced there (e.g. phase_3_agent_architecture in .cursor/plans).87- FK relationships (per-domain `table_relationships`, extract, embeddings JSON, retrieval): `docs/RELATIONSHIPS_PIPELINE.md`.88