prazy1208/text2sql

1# FK relationships pipeline2 3Foreign-key metadata for the Table and Column agents lives in **one table per domain schema**: `healthcare_schema.table_relationships`, `retail_schema.table_relationships`, and `finance_schema.table_relationships`. There is no central `system_schema` table for this feature.4 5## Flow6 71. **DDL** — Create the three tables (same shape in each schema). See `scripts/create_domain_schema_table_relationships.sql` or `python scripts/run_create_domain_schema_table_relationships.py`. For databases that predate this feature, use `scripts/migration_add_domain_schema_table_relationships.sql` once.82. **Load** — `python scripts/extract_and_load_relationships.py` reads FKs from `pg_catalog` and upserts into `{source_schema}.table_relationships` (only schemas listed in `DOMAIN_SCHEMAS` in `backend/config.py`).93. **Embeddings + agent metadata** — `python build_relationship_embeddings.py` writes `metadata_store/relationships_{schema}_metadata.json` (SentenceTransformer `all-MiniLM-L6-v2`). **`POST /query` loads FK rows from these JSON files** via `list_relationships_from_metadata` (embeddings stripped at read time). The database tables are still the source of truth for extract/build scripts.104. **API** — `POST /query` resolves `use_case` → `schema_name`, loads rows from metadata JSON, and passes them into the table, column, and Gen-SQL agents.11 12If you ever used the old centralized `system_schema.table_relationships`, drop it using the rollback snippet in `Text2SQL_PostgreSQL_Setup_Guide.md` §7b before relying on per-domain tables.13 14## Key files15 16| Purpose | Location |17| --- | --- |18| Domain list | `DOMAIN_SCHEMAS`, `RELATIONSHIP_METADATA_NAMES` in `backend/config.py` |19| DDL + migration | `scripts/create_domain_schema_table_relationships.sql`, `scripts/migration_add_domain_schema_table_relationships.sql` |20| Extract + upsert | `scripts/extract_and_load_relationships.py` |21| Embedding build | `build_relationship_embeddings.py` |22| Runtime load (API) | `list_relationships_from_metadata` in `backend/services/relationship_retrieval.py` |23| HTTP wiring | `backend/api/routes/query.py` |24| Prompts | `backend/agents/table_agent.py`, `backend/agents/column_agent.py` |25 26## Environment27 28Scripts and `get_engine()` use the same variables as the rest of the project: `DATABASE_URL` or `DB_HOST`, `DB_PORT`, `DB_USER`, `DB_PASSWORD`, `DB_NAME` (default database name `text2sql_db` in backend config).29 30## Operations31 32- **Order:** Domain schemas and business tables must exist before creating `table_relationships`. Then extract/load; embeddings are optional and can be run after load.33- **Logging:** If the metadata JSON file is missing or unreadable, `query.py` logs a warning and continues with an empty relationship list so the pipeline still responds.34