prazy1208/text2sql

1-- Per-domain FK metadata: one table_relationships per business schema.2-- Requires healthcare_schema, retail_schema, finance_schema to exist (see Text2SQL_PostgreSQL_Setup_Guide.md).3-- Run against text2sql_db (pgAdmin or: python scripts/run_create_domain_schema_table_relationships.py)4 5-- healthcare_schema6CREATE TABLE IF NOT EXISTS healthcare_schema.table_relationships (7    id                  SERIAL PRIMARY KEY,8    source_table        TEXT NOT NULL,9    source_column       TEXT NOT NULL,10    target_schema       TEXT NOT NULL,11    target_table        TEXT NOT NULL,12    target_column       TEXT NOT NULL,13    relationship_text   TEXT NOT NULL,14    constraint_name     VARCHAR(256),15    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,16    CONSTRAINT uq_healthcare_table_relationships_edge17        UNIQUE (source_table, source_column, target_schema, target_table, target_column)18);19 20CREATE INDEX IF NOT EXISTS idx_healthcare_table_relationships_source21    ON healthcare_schema.table_relationships (source_table);22 23CREATE INDEX IF NOT EXISTS idx_healthcare_table_relationships_source_col24    ON healthcare_schema.table_relationships (source_table, source_column);25 26COMMENT ON TABLE healthcare_schema.table_relationships IS 'Foreign keys with referencing tables in healthcare_schema; target_schema for referenced side';27COMMENT ON COLUMN healthcare_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';28COMMENT ON COLUMN healthcare_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';29 30-- retail_schema31CREATE TABLE IF NOT EXISTS retail_schema.table_relationships (32    id                  SERIAL PRIMARY KEY,33    source_table        TEXT NOT NULL,34    source_column       TEXT NOT NULL,35    target_schema       TEXT NOT NULL,36    target_table        TEXT NOT NULL,37    target_column       TEXT NOT NULL,38    relationship_text   TEXT NOT NULL,39    constraint_name     VARCHAR(256),40    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,41    CONSTRAINT uq_retail_table_relationships_edge42        UNIQUE (source_table, source_column, target_schema, target_table, target_column)43);44 45CREATE INDEX IF NOT EXISTS idx_retail_table_relationships_source46    ON retail_schema.table_relationships (source_table);47 48CREATE INDEX IF NOT EXISTS idx_retail_table_relationships_source_col49    ON retail_schema.table_relationships (source_table, source_column);50 51COMMENT ON TABLE retail_schema.table_relationships IS 'Foreign keys with referencing tables in retail_schema; target_schema for referenced side';52COMMENT ON COLUMN retail_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';53COMMENT ON COLUMN retail_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';54 55-- finance_schema56CREATE TABLE IF NOT EXISTS finance_schema.table_relationships (57    id                  SERIAL PRIMARY KEY,58    source_table        TEXT NOT NULL,59    source_column       TEXT NOT NULL,60    target_schema       TEXT NOT NULL,61    target_table        TEXT NOT NULL,62    target_column       TEXT NOT NULL,63    relationship_text   TEXT NOT NULL,64    constraint_name     VARCHAR(256),65    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,66    CONSTRAINT uq_finance_table_relationships_edge67        UNIQUE (source_table, source_column, target_schema, target_table, target_column)68);69 70CREATE INDEX IF NOT EXISTS idx_finance_table_relationships_source71    ON finance_schema.table_relationships (source_table);72 73CREATE INDEX IF NOT EXISTS idx_finance_table_relationships_source_col74    ON finance_schema.table_relationships (source_table, source_column);75 76COMMENT ON TABLE finance_schema.table_relationships IS 'Foreign keys with referencing tables in finance_schema; target_schema for referenced side';77COMMENT ON COLUMN finance_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';78COMMENT ON COLUMN finance_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';79