prazy1208/text2sql

1# Text2SQL Project - PostgreSQL & Healthcare Schema Setup Guide2 3------------------------------------------------------------------------4 5## 1. PostgreSQL Installation (Windows)6 7### Step 1: Download PostgreSQL8 9Download from official website:10https://www.postgresql.org/download/windows/11 12Use the EnterpriseDB installer.13 14------------------------------------------------------------------------15 16### Step 2: Install PostgreSQL17 18During installation:19 20-   Keep default port: **5432**21-   Set a secure password for user **postgres**22-   Keep default installation directory23-   You may uncheck **Stack Builder** (not required)24 25Finish installation.26 27------------------------------------------------------------------------28 29## 2. Add Server in pgAdmin 430 31### Step 1: Open pgAdmin 432 33Enter master password.34 35### Step 2: Register Server36 37Right click: Servers → Register → Server38 39### General Tab40 41-   Name: `text2sql_dev`42 43### Connection Tab44 45-   Host: `localhost`46-   Port: `5432`47-   Maintenance DB: `postgres`48-   Username: `postgres`49-   Password: (your password)50-   Check "Save Password"51 52Click Save.53 54------------------------------------------------------------------------55 56## 3. Create Project Database57 58Right click: Databases → Create → Database59 60-   Database Name: `text2sql_db`61-   Owner: `postgres`62 63Click Save.64 65------------------------------------------------------------------------66 67## 4. Create Healthcare Schema68 69Open Query Tool on `text2sql_db` and run:70 71``` sql72CREATE SCHEMA healthcare_schema;73```74 75Refresh Schemas to confirm creation.76 77------------------------------------------------------------------------78 79## 5. Create Healthcare Tables80 81Run the following SQL in Query Tool:82 83``` sql84-- =========================85-- PATIENTS TABLE86-- =========================87CREATE TABLE healthcare_schema.patients (88    patient_id SERIAL PRIMARY KEY,89    first_name VARCHAR(100),90    last_name VARCHAR(100),91    date_of_birth DATE,92    gender VARCHAR(20),93    city VARCHAR(100),94    state VARCHAR(100),95    insurance_type VARCHAR(50),96    registration_date DATE97);98 99-- =========================100-- VISITS TABLE101-- =========================102CREATE TABLE healthcare_schema.visits (103    visit_id SERIAL PRIMARY KEY,104    patient_id INT REFERENCES healthcare_schema.patients(patient_id),105    admission_date DATE,106    discharge_date DATE,107    department VARCHAR(100),108    visit_type VARCHAR(50),109    total_cost NUMERIC(12,2)110);111 112-- =========================113-- DIAGNOSES TABLE114-- =========================115CREATE TABLE healthcare_schema.diagnoses (116    diagnosis_id SERIAL PRIMARY KEY,117    visit_id INT REFERENCES healthcare_schema.visits(visit_id),118    diagnosis_code VARCHAR(20),119    diagnosis_description VARCHAR(255),120    severity_level VARCHAR(20)121);122```123 124------------------------------------------------------------------------125 126## 6. Validate Tables127 128Run:129 130``` sql131SELECT table_name132FROM information_schema.tables133WHERE table_schema = 'healthcare_schema';134```135 136Expected Output: - patients - visits - diagnoses137 138------------------------------------------------------------------------139 140## 7. Create App Schema (Stage 1)141 142The **app_schema** holds application and session data for the Text2SQL pipeline (Stage 1): **sessions**, **intent_agent_output** (Intent Agent: rephrased question, keywords, business insights), and **table_agent_output** (Table Agent: `selected_tables` linked to `intent_agent_output.id`). Fresh installs use `scripts/create_app_schema.sql` (includes all three). Existing databases created before Table Agent: run `scripts/migration_add_table_agent_output.sql` once.143 144**Option A — Run SQL in pgAdmin**145 146Open Query Tool on **text2sql_db** and run the following (or run the file `scripts/create_app_schema.sql`):147 148```sql149-- App schema: sessions, intent_agent_output, table_agent_output (same as scripts/create_app_schema.sql)150CREATE SCHEMA IF NOT EXISTS app_schema;151 152CREATE TABLE IF NOT EXISTS app_schema.sessions (153    session_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),154    created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,155    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP156);157 158CREATE TABLE IF NOT EXISTS app_schema.intent_agent_output (159    id                 SERIAL PRIMARY KEY,160    session_id         UUID NOT NULL REFERENCES app_schema.sessions(session_id) ON DELETE CASCADE,161    use_case           VARCHAR(64) NOT NULL,162    user_input         TEXT NOT NULL,163    rephrased_question TEXT,164    keywords           TEXT[],165    business_insights  TEXT[],166    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP167);168 169CREATE INDEX IF NOT EXISTS idx_intent_agent_output_session_id170    ON app_schema.intent_agent_output(session_id);171CREATE INDEX IF NOT EXISTS idx_intent_agent_output_use_case172    ON app_schema.intent_agent_output(use_case);173 174CREATE TABLE IF NOT EXISTS app_schema.table_agent_output (175    id                 SERIAL PRIMARY KEY,176    intent_output_id   INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,177    selected_tables    TEXT[] NOT NULL DEFAULT '{}',178    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,179    UNIQUE (intent_output_id)180);181 182COMMENT ON SCHEMA app_schema IS 'Application/session data for Text2SQL Stage 1';183COMMENT ON TABLE app_schema.sessions IS 'One row per chat session';184COMMENT ON TABLE app_schema.intent_agent_output IS 'One row per user query; stores Intent Agent output in separate columns';185COMMENT ON TABLE app_schema.table_agent_output IS 'Table Agent: selected_tables for one intent_agent_output row';186```187 188**Option B — Run the Python script**189 190From the project root, with `.env` configured (e.g. `DATABASE_URL` or `DB_HOST`, `DB_PORT`, `DB_USER`, `DB_PASSWORD`, `DB_NAME`):191 192```bash193python scripts/run_create_app_schema.py194```195 196The script uses the same database settings as `build_vector_store.py` and creates `app_schema`, `sessions`, `intent_agent_output`, and `table_agent_output` in **text2sql_db**.197 198------------------------------------------------------------------------199 200## 7b. Legacy rollback: `system_schema.table_relationships` (optional)201 202If you previously created the **centralized** FK metadata table under `system_schema` (older project scripts that are no longer in the repo), remove it before adopting **per-domain** `table_relationships` tables in each business schema. Run once on **text2sql_db** as superuser or owner:203 204```sql205DROP TABLE IF EXISTS system_schema.table_relationships;206-- Optional: only if nothing else should live in system_schema207-- DROP SCHEMA IF EXISTS system_schema CASCADE;208```209 210If other objects still use `system_schema`, omit `DROP SCHEMA` and only drop the table.211 212------------------------------------------------------------------------213 214## 7c. Domain schemas — FK metadata (`table_relationships`)215 216**Runtime (`POST /query`):** FK edges are read from **`metadata_store/relationships_{schema}_metadata.json`** (`list_relationships_from_metadata`). Regenerate those files after changing the database: `python build_relationship_embeddings.py`. **Postgres `table_relationships` tables** are still required for `extract_and_load_relationships.py` and the embedding build; create/load them as below if rows are missing or stale.217 218After `healthcare_schema`, `retail_schema`, and `finance_schema` exist, add one **`table_relationships`** table per domain schema (referencing side is always in that domain; `target_schema` holds the referenced table’s schema for cross-schema FKs).219 220### One-command refresh (recommended after schema/table changes)221 222From project root, run:223 224```bash225python scripts/run_full_schema_refresh.py226```227 228This runs the full refresh pipeline in order:2291. Applies `scripts/create_domain_schemas.sql` (domain tables + comments + safe alter/constraint updates).2302. Ensures domain `table_relationships` tables exist.2313. Extracts FK relationships from `pg_catalog` and upserts into domain `table_relationships`.2324. Rebuilds relationship metadata JSON:233   - `metadata_store/relationships_healthcare_schema_metadata.json`234   - `metadata_store/relationships_retail_schema_metadata.json`235   - `metadata_store/relationships_finance_schema_metadata.json`2365. Rebuilds domain metadata + FAISS indexes:237   - `metadata_store/{schema}_metadata.json`238   - `metadata_store/{schema}_columns_metadata.json`239   - `faiss_indexes/{schema}.index`240   - `faiss_indexes/{schema}_columns.index`241 242### One-shot setup (recommended for new databases)243 244Run the full script in the **Supabase SQL Editor** (or pgAdmin against your DB): `scripts/complete_setup.sql`. It creates domain tables, `app_schema`, and **all three** `table_relationships` tables in one go. Safe to re-run: DDL uses `CREATE TABLE IF NOT EXISTS` / `CREATE INDEX IF NOT EXISTS`.245 246### If you already ran an older `complete_setup.sql` without FK tables247 248Apply only the FK block using either:249 250**Option A — SQL file**251 252Run `scripts/create_domain_schema_table_relationships.sql` in the SQL editor (same DDL as the FK section at the end of `complete_setup.sql`), or:253 254**Option B — Migration file**255 256Run `scripts/migration_add_domain_schema_table_relationships.sql` once (equivalent `CREATE TABLE IF NOT EXISTS`).257 258**Option C — Python**259 260From project root with `.env` pointing at the database:261 262```bash263python scripts/run_create_domain_schema_table_relationships.py264```265 266### Load rows from actual foreign keys267 268Empty `table_relationships` tables are not enough: populate them from `pg_catalog` so each row has `relationship_text` for the LLM.269 270```bash271python scripts/extract_and_load_relationships.py272```273 274Requires the domain business tables and FK constraints to exist (e.g. `retail_schema.orders` → `customers`, `products`). After loading, `POST /query` with `use_case: retail` will load `retail_schema.table_relationships`.275 276### Optional: embeddings JSON277 278Precompute vectors for each `relationship_text` and write `metadata_store/relationships_{schema}_metadata.json`:279 280```bash281python build_relationship_embeddings.py282```283 284------------------------------------------------------------------------285 286## Architecture Summary287 288Database: PostgreSQL\289Schema: healthcare_schema\290Tables: - patients - visits - diagnoses291 292This structure supports: - JOIN operations - Aggregations - Time-based293filtering - Cost analysis - Severity filtering - Multi-table reasoning294for Text-to-SQL system295 296------------------------------------------------------------------------297 298# Create Retail Schema299 300```sql301CREATE SCHEMA retail_schema;302```303 304# Create Retail Tables305Run the following SQL in Query Tool:306 307``` sql308-- =========================309-- CUSTOMERS TABLE310-- =========================311CREATE TABLE retail_schema.customers (312    customer_id SERIAL PRIMARY KEY,313    first_name VARCHAR(100),314    last_name VARCHAR(100),315    email VARCHAR(150),316    city VARCHAR(100),317    state VARCHAR(100),318    signup_date DATE319);320 321-- =========================322-- PRODUCTS TABLE323-- =========================324CREATE TABLE retail_schema.products (325    product_id SERIAL PRIMARY KEY,326    product_name VARCHAR(150),327    category VARCHAR(100),328    brand VARCHAR(100),329    price NUMERIC(10,2),330    launch_date DATE331);332 333-- =========================334-- ORDERS TABLE335-- =========================336CREATE TABLE retail_schema.orders (337    order_id SERIAL PRIMARY KEY,338    customer_id INT REFERENCES retail_schema.customers(customer_id),339    product_id INT REFERENCES retail_schema.products(product_id),340    order_date DATE,341    quantity INT,342    total_amount NUMERIC(12,2)343);344```345-----------------------------------------------346 347# Create Finance Schema348 349```sql350CREATE SCHEMA finance_schema;351```352 353# Create Finance Tables354Run the following SQL in Query Tool:355 356``` sql357-- =========================358-- ACCOUNTS TABLE359-- =========================360CREATE TABLE finance_schema.accounts (361    account_id SERIAL PRIMARY KEY,362    customer_name VARCHAR(150),363    account_type VARCHAR(50), -- savings, checking364    branch_city VARCHAR(100),365    opening_date DATE,366    current_balance NUMERIC(14,2)367);368 369-- =========================370-- TRANSACTIONS TABLE371-- =========================372CREATE TABLE finance_schema.transactions (373    transaction_id SERIAL PRIMARY KEY,374    account_id INT REFERENCES finance_schema.accounts(account_id),375    transaction_date DATE,376    transaction_type VARCHAR(50), -- debit, credit377    amount NUMERIC(14,2),378    description VARCHAR(255)379);380 381-- =========================382-- LOANS TABLE383-- =========================384CREATE TABLE finance_schema.loans (385    loan_id SERIAL PRIMARY KEY,386    account_id INT REFERENCES finance_schema.accounts(account_id),387    loan_type VARCHAR(100), -- home, auto, personal388    loan_amount NUMERIC(14,2),389    interest_rate NUMERIC(5,2),390    loan_start_date DATE,391    loan_end_date DATE392);393```394```sql395-- =========================================396-- SCHEMA DESCRIPTION397-- =========================================398COMMENT ON SCHEMA healthcare_schema IS399'Contains healthcare operational data including patient demographics, hospital visits, and medical diagnoses for analytical and reporting purposes.';400 401 402-- =========================================403-- PATIENTS TABLE DESCRIPTION404-- =========================================405COMMENT ON TABLE healthcare_schema.patients IS406'Stores demographic and registration details of patients receiving healthcare services.';407 408COMMENT ON COLUMN healthcare_schema.patients.patient_id IS409'Unique system-generated identifier assigned to each patient.';410 411COMMENT ON COLUMN healthcare_schema.patients.first_name IS412'Patient''s legal first name.';413 414COMMENT ON COLUMN healthcare_schema.patients.last_name IS415'Patient''s legal last name.';416 417COMMENT ON COLUMN healthcare_schema.patients.date_of_birth IS418'Date of birth of the patient used for age-based analysis and eligibility checks.';419 420COMMENT ON COLUMN healthcare_schema.patients.gender IS421'Self-reported gender of the patient.';422 423COMMENT ON COLUMN healthcare_schema.patients.city IS424'City of residence of the patient.';425 426COMMENT ON COLUMN healthcare_schema.patients.state IS427'State of residence of the patient.';428 429COMMENT ON COLUMN healthcare_schema.patients.insurance_type IS430'Type of insurance coverage used by the patient (e.g., private, government, uninsured).';431 432COMMENT ON COLUMN healthcare_schema.patients.registration_date IS433'Date when the patient was first registered in the healthcare system.';434 435 436-- =========================================437-- VISITS TABLE DESCRIPTION438-- =========================================439COMMENT ON TABLE healthcare_schema.visits IS440'Records individual hospital or clinic visits made by patients, including department, visit type, and associated costs.';441 442COMMENT ON COLUMN healthcare_schema.visits.visit_id IS443'Unique system-generated identifier for each patient visit.';444 445COMMENT ON COLUMN healthcare_schema.visits.patient_id IS446'Foreign key referencing the patient who attended the visit.';447 448COMMENT ON COLUMN healthcare_schema.visits.admission_date IS449'Date when the patient was admitted for the visit.';450 451COMMENT ON COLUMN healthcare_schema.visits.discharge_date IS452'Date when the patient was discharged from the visit.';453 454COMMENT ON COLUMN healthcare_schema.visits.department IS455'Medical department responsible for the visit (e.g., cardiology, emergency, oncology).';456 457COMMENT ON COLUMN healthcare_schema.visits.visit_type IS458'Classification of visit such as inpatient, outpatient, or emergency.';459 460COMMENT ON COLUMN healthcare_schema.visits.total_cost IS461'Total billed cost associated with the visit, including treatments and services.';462 463 464-- =========================================465-- DIAGNOSES TABLE DESCRIPTION466-- =========================================467COMMENT ON TABLE healthcare_schema.diagnoses IS468'Stores medical diagnoses assigned during patient visits, including severity and diagnostic codes.';469 470COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_id IS471'Unique system-generated identifier for each diagnosis record.';472 473COMMENT ON COLUMN healthcare_schema.diagnoses.visit_id IS474'Foreign key referencing the visit during which the diagnosis was made.';475 476COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_code IS477'Standardized medical diagnosis code (e.g., ICD code) representing the condition.';478 479COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_description IS480'Textual description of the diagnosed medical condition.';481 482COMMENT ON COLUMN healthcare_schema.diagnoses.severity_level IS483'Indicates the severity of the diagnosed condition (e.g., mild, moderate, severe).';484 485-- =========================================486-- SCHEMA DESCRIPTION487-- =========================================488COMMENT ON SCHEMA retail_schema IS489'Contains retail business data including customer profiles, product catalog information, and sales transaction records for analytics and reporting.';490 491 492-- =========================================493-- CUSTOMERS TABLE DESCRIPTION494-- =========================================495COMMENT ON TABLE retail_schema.customers IS496'Stores customer demographic and account information for individuals who purchase products.';497 498COMMENT ON COLUMN retail_schema.customers.customer_id IS499'Unique system-generated identifier assigned to each customer.';500 501COMMENT ON COLUMN retail_schema.customers.first_name IS502'Customer''s first name as provided during registration.';503 504COMMENT ON COLUMN retail_schema.customers.last_name IS505'Customer''s last name as provided during registration.';506 507COMMENT ON COLUMN retail_schema.customers.email IS508'Registered email address used for communication, promotions, and order notifications.';509 510COMMENT ON COLUMN retail_schema.customers.city IS511'City where the customer resides.';512 513COMMENT ON COLUMN retail_schema.customers.state IS514'State where the customer resides.';515 516COMMENT ON COLUMN retail_schema.customers.signup_date IS517'Date when the customer created their account in the retail system.';518 519 520-- =========================================521-- PRODUCTS TABLE DESCRIPTION522-- =========================================523COMMENT ON TABLE retail_schema.products IS524'Stores product catalog information including category, brand, pricing, and launch details.';525 526COMMENT ON COLUMN retail_schema.products.product_id IS527'Unique system-generated identifier assigned to each product.';528 529COMMENT ON COLUMN retail_schema.products.product_name IS530'Official name of the product available for sale.';531 532COMMENT ON COLUMN retail_schema.products.category IS533'Product category used for grouping similar items (e.g., electronics, apparel, home goods).';534 535COMMENT ON COLUMN retail_schema.products.brand IS536'Brand or manufacturer associated with the product.';537 538COMMENT ON COLUMN retail_schema.products.price IS539'Current selling price of the product per unit.';540 541COMMENT ON COLUMN retail_schema.products.launch_date IS542'Date when the product was first introduced to the market.';543 544 545-- =========================================546-- ORDERS TABLE DESCRIPTION547-- =========================================548COMMENT ON TABLE retail_schema.orders IS549'Records customer purchase transactions including product ordered, quantity, and total transaction value.';550 551COMMENT ON COLUMN retail_schema.orders.order_id IS552'Unique system-generated identifier for each customer order.';553 554COMMENT ON COLUMN retail_schema.orders.customer_id IS555'Foreign key referencing the customer who placed the order.';556 557COMMENT ON COLUMN retail_schema.orders.product_id IS558'Foreign key referencing the product that was purchased.';559 560COMMENT ON COLUMN retail_schema.orders.order_date IS561'Date when the order was placed by the customer.';562 563COMMENT ON COLUMN retail_schema.orders.quantity IS564'Number of units of the product purchased in the order.';565 566COMMENT ON COLUMN retail_schema.orders.total_amount IS567'Total monetary value of the order calculated as quantity multiplied by unit price.';568 569-- =========================================570-- SCHEMA DESCRIPTION571-- =========================================572COMMENT ON SCHEMA finance_schema IS573'Contains financial services data including customer accounts, banking transactions, and loan records for operational reporting and financial analytics.';574 575 576-- =========================================577-- ACCOUNTS TABLE DESCRIPTION578-- =========================================579COMMENT ON TABLE finance_schema.accounts IS580'Stores customer bank account information including account type, branch location, and current balance.';581 582COMMENT ON COLUMN finance_schema.accounts.account_id IS583'Unique system-generated identifier assigned to each bank account.';584 585COMMENT ON COLUMN finance_schema.accounts.customer_name IS586'Full name of the customer who owns the bank account.';587 588COMMENT ON COLUMN finance_schema.accounts.account_type IS589'Type of bank account such as savings or checking.';590 591COMMENT ON COLUMN finance_schema.accounts.branch_city IS592'City where the bank branch managing the account is located.';593 594COMMENT ON COLUMN finance_schema.accounts.opening_date IS595'Date when the bank account was opened.';596 597COMMENT ON COLUMN finance_schema.accounts.current_balance IS598'Current available balance in the account.';599 600 601-- =========================================602-- TRANSACTIONS TABLE DESCRIPTION603-- =========================================604COMMENT ON TABLE finance_schema.transactions IS605'Records financial transactions performed on customer accounts including debits and credits.';606 607COMMENT ON COLUMN finance_schema.transactions.transaction_id IS608'Unique system-generated identifier for each transaction.';609 610COMMENT ON COLUMN finance_schema.transactions.account_id IS611'Foreign key referencing the account on which the transaction occurred.';612 613COMMENT ON COLUMN finance_schema.transactions.transaction_date IS614'Date when the transaction was executed.';615 616COMMENT ON COLUMN finance_schema.transactions.transaction_type IS617'Type of transaction such as debit (money withdrawn) or credit (money deposited).';618 619COMMENT ON COLUMN finance_schema.transactions.amount IS620'Monetary value of the transaction.';621 622COMMENT ON COLUMN finance_schema.transactions.description IS623'Short textual explanation describing the purpose or nature of the transaction.';624 625 626-- =========================================627-- LOANS TABLE DESCRIPTION628-- =========================================629COMMENT ON TABLE finance_schema.loans IS630'Stores loan account information associated with customer accounts including loan type, principal amount, and repayment terms.';631 632COMMENT ON COLUMN finance_schema.loans.loan_id IS633'Unique system-generated identifier for each loan record.';634 635COMMENT ON COLUMN finance_schema.loans.account_id IS636'Foreign key referencing the account associated with the loan.';637 638COMMENT ON COLUMN finance_schema.loans.loan_type IS639'Category of loan such as home loan, auto loan, or personal loan.';640 641COMMENT ON COLUMN finance_schema.loans.loan_amount IS642'Total principal amount borrowed under the loan agreement.';643 644COMMENT ON COLUMN finance_schema.loans.interest_rate IS645'Annual interest rate applied to the loan expressed as a percentage.';646 647COMMENT ON COLUMN finance_schema.loans.loan_start_date IS648'Date when the loan repayment period began.';649 650COMMENT ON COLUMN finance_schema.loans.loan_end_date IS651'Date when the loan is scheduled to be fully repaid.';652 653 654```655 656# Business rules for retail schema657 658```sql659CREATE TABLE retail_schema.retail_business_rules (660    rule_id SERIAL PRIMARY KEY,661    concept_name VARCHAR(150) NOT NULL,662    description TEXT NOT NULL,663    insight TEXT,664    keywords TEXT[],665    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP666);667 668COMMENT ON TABLE retail_schema.retail_business_rules IS669'Stores domain-level business knowledge used by the Intent Agent to interpret user queries. 670The rules describe analytical business concepts related to the retail domain in natural language. 671They intentionally avoid references to database tables, columns, or SQL logic to prevent biasing 672downstream agents responsible for schema selection and SQL generation.';673 674COMMENT ON COLUMN retail_schema.retail_business_rules.rule_id IS675'Unique identifier for each business rule.';676 677COMMENT ON COLUMN retail_schema.retail_business_rules.concept_name IS678'High-level business concept represented by the rule, such as customer acquisition, product demand, or sales trends.';679 680COMMENT ON COLUMN retail_schema.retail_business_rules.description IS681'Neutral explanation of the business concept in natural language. 682This field defines the concept without referencing database schema, tables, columns, or SQL logic.';683 684COMMENT ON COLUMN retail_schema.retail_business_rules.insight IS685'Business reasoning that explains why the concept is important for analytical use cases.';686 687COMMENT ON COLUMN retail_schema.retail_business_rules.keywords IS688'List of keywords or phrases associated with the business concept. 689These keywords help semantic retrieval systems match user queries with relevant business rules.';690 691COMMENT ON COLUMN retail_schema.retail_business_rules.created_at IS692'Timestamp indicating when the business rule was created. Useful for rule governance and auditing.';693 694INSERT INTO retail_schema.retail_business_rules695(concept_name, description, insight, keywords)696VALUES697 698(699'Customer Acquisition',700'Customer acquisition refers to the process of gaining new customers who begin interacting with the business.',701'Tracking acquisition helps businesses understand growth and evaluate outreach or marketing efforts.',702ARRAY['new customers','customer growth','customer signup','customer acquisition']703),704 705(706'Customer Base Distribution',707'Customer base distribution describes how customers are spread across different geographic locations.',708'Understanding where customers are concentrated helps businesses identify strong markets and areas for expansion.',709ARRAY['customer location','customer regions','geographic distribution','customers by region']710),711 712(713'Customer Growth Trends',714'Customer growth trends represent how the number of customers changes over time.',715'Analyzing these trends helps businesses measure long-term growth and customer adoption.',716ARRAY['customer trends','customer increase','customer growth over time']717),718 719(720'Customer Engagement',721'Customer engagement reflects how actively customers interact with the business through purchasing behavior.',722'Understanding engagement levels helps identify loyal customers and overall customer activity.',723ARRAY['customer engagement','customer activity','active customers']724),725 726(727'Customer Purchase Behavior',728'Customer purchase behavior describes patterns in how customers buy products over time.',729'Studying purchasing patterns helps businesses understand customer preferences and buying habits.',730ARRAY['purchase behavior','customer buying patterns','shopping behavior']731),732 733(734'Product Catalog Overview',735'Product catalog analysis focuses on understanding the variety and organization of products offered by a business.',736'Reviewing the product catalog helps businesses maintain balanced offerings across categories and brands.',737ARRAY['product catalog','product list','available products']738),739 740(741'Product Category Analysis',742'Product categories group similar products together to simplify product organization and analysis.',743'Analyzing categories helps businesses understand demand patterns across different product types.',744ARRAY['product category','product categories','category analysis']745),746 747(748'Brand Representation',749'Brand representation reflects how different brands appear within the product assortment.',750'Understanding brand presence helps businesses evaluate brand diversity and popularity.',751ARRAY['brand presence','brand distribution','brand representation']752),753 754(755'Product Popularity',756'Product popularity reflects the level of interest customers show in particular products.',757'Popularity insights help businesses identify products that consistently attract customer attention.',758ARRAY['popular products','trending products','product interest']759),760 761(762'Product Introduction Trends',763'Product introduction trends describe how frequently new products are added to the catalog.',764'Monitoring product introductions helps businesses understand innovation and catalog expansion.',765ARRAY['new products','product launch','recent products']766),767 768(769'Sales Activity',770'Sales activity represents the overall purchasing interactions occurring between customers and the business.',771'Monitoring activity levels helps businesses evaluate demand and operational performance.',772ARRAY['sales activity','transactions','purchase activity']773),774 775(776'Order Volume',777'Order volume reflects the number of purchasing transactions occurring within a given timeframe.',778'Tracking order volume helps businesses understand changes in purchasing frequency.',779ARRAY['order volume','transaction count','number of purchases']780),781 782(783'Sales Trends',784'Sales trends describe how purchasing activity evolves across different time periods.',785'Trend analysis helps identify growth patterns and seasonal behavior.',786ARRAY['sales trends','purchase trends','transaction trends']787),788 789(790'Customer Purchasing Distribution',791'Customer purchasing distribution describes how purchasing activity varies among different customers.',792'Understanding purchasing distribution helps identify customers with higher engagement levels.',793ARRAY['customer spending','top customers','customer purchase activity']794),795 796(797'Product Demand Patterns',798'Product demand patterns describe how frequently different products attract customer purchases.',799'Understanding demand patterns helps businesses recognize products that drive consistent interest.',800ARRAY['product demand','high demand products','frequently purchased products']801),802 803(804'Product Category Demand',805'Category demand analysis evaluates how customer interest varies across different product categories.',806'This analysis helps businesses determine which types of products attract the most attention.',807ARRAY['category demand','popular categories','category interest']808),809 810(811'Brand Interest',812'Brand interest reflects the level of customer attention or engagement associated with particular brands.',813'Analyzing brand interest helps businesses understand which brands resonate most with customers.',814ARRAY['brand interest','popular brands','brand popularity']815),816 817(818'Customer Activity Over Time',819'Customer activity over time measures how purchasing engagement changes across different time periods.',820'Observing activity patterns helps identify periods of high or low customer interaction.',821ARRAY['customer activity trends','shopping activity','purchase timing']822);823 824```825 826# business rules for financial business rules827 828```sql829 830CREATE TABLE finance_schema.finance_business_rules (831    rule_id SERIAL PRIMARY KEY,832    concept_name VARCHAR(150) NOT NULL,833    description TEXT NOT NULL,834    insight TEXT,835    keywords TEXT[],836    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP837);838 839COMMENT ON TABLE finance_schema.finance_business_rules IS840'Stores domain-level financial business knowledge used by the Intent Agent to interpret user queries. 841These rules describe financial analytical concepts and insights in natural language. 842They intentionally avoid referencing database tables, columns, or SQL logic in order to prevent 843biasing downstream agents responsible for schema selection and SQL generation.';844 845COMMENT ON COLUMN finance_schema.finance_business_rules.rule_id IS846'Unique identifier assigned to each financial business rule.';847 848COMMENT ON COLUMN finance_schema.finance_business_rules.concept_name IS849'High-level financial concept or analytical theme represented by the rule, such as transaction activity, financial trends, or spending patterns.';850 851COMMENT ON COLUMN finance_schema.finance_business_rules.description IS852'Neutral natural-language explanation of the financial concept. 853This field describes the concept without referencing database schema elements, implementation details, or SQL logic.';854 855COMMENT ON COLUMN finance_schema.finance_business_rules.insight IS856'Business insight explaining why the financial concept is important for analysis and decision-making.';857 858COMMENT ON COLUMN finance_schema.finance_business_rules.keywords IS859'List of keywords or phrases associated with the financial concept. 860These keywords help semantic retrieval systems match user queries with relevant business rules.';861 862COMMENT ON COLUMN finance_schema.finance_business_rules.created_at IS863'Timestamp indicating when the financial business rule was created. 864This supports governance, auditing, and lifecycle management of rules.';865 866INSERT INTO finance_schema.finance_business_rules867(concept_name, description, insight, keywords)868VALUES869 870(871'Transaction Activity',872'Transaction activity represents the overall movement of financial operations occurring within a system.',873'Monitoring transaction activity helps organizations understand operational intensity and financial usage patterns.',874ARRAY['transactions','transaction activity','financial activity']875),876 877(878'Transaction Volume Trends',879'Transaction volume trends describe how the number of financial transactions changes over time.',880'Analyzing transaction volume helps identify periods of increased financial activity or operational demand.',881ARRAY['transaction trends','transaction volume','transaction increase']882),883 884(885'Account Activity',886'Account activity reflects how actively financial accounts are used for transactions or operations.',887'Understanding account activity helps identify frequently used accounts and engagement patterns.',888ARRAY['account activity','active accounts','account usage']889),890 891(892'Account Distribution',893'Account distribution describes how accounts are organized or spread across different categories or groups.',894'Analyzing distribution helps organizations understand structural patterns within the financial system.',895ARRAY['account distribution','account categories','account groups']896),897 898(899'Financial Flow Analysis',900'Financial flow analysis examines how value moves across the financial system through transactions.',901'Understanding flow patterns helps identify major channels of financial movement.',902ARRAY['financial flow','money movement','transaction flow']903),904 905(906'Spending Patterns',907'Spending patterns describe how financial resources are utilized across various activities.',908'Analyzing spending behavior helps organizations identify common expenditure trends.',909ARRAY['spending patterns','financial spending','expense behavior']910),911 912(913'Revenue Activity',914'Revenue activity represents financial inflows generated through operational or business activities.',915'Monitoring revenue activity helps organizations track financial performance over time.',916ARRAY['revenue activity','income generation','financial inflow']917),918 919(920'Expense Activity',921'Expense activity reflects financial outflows resulting from operational or business expenditures.',922'Tracking expense patterns helps organizations understand cost distribution and spending behavior.',923ARRAY['expenses','cost activity','financial outflow']924),925 926(927'Financial Balance Monitoring',928'Balance monitoring focuses on tracking the financial position of accounts or entities over time.',929'Understanding balance patterns helps identify financial stability and resource availability.',930ARRAY['balance monitoring','account balances','financial position']931),932 933(934'Customer Financial Behavior',935'Customer financial behavior reflects how customers interact with financial services or perform financial operations.',936'Studying financial behavior helps organizations understand customer engagement with financial systems.',937ARRAY['financial behavior','customer transactions','customer financial activity']938),939 940(941'Transaction Frequency',942'Transaction frequency measures how often financial operations occur within a defined period.',943'Monitoring frequency helps organizations detect changes in system usage patterns.',944ARRAY['transaction frequency','transaction rate','financial operations']945),946 947(948'Financial Activity Distribution',949'Financial activity distribution describes how financial operations are spread across different entities or accounts.',950'Understanding distribution helps identify concentration of financial activity.',951ARRAY['financial distribution','activity spread','transaction concentration']952),953 954(955'Financial Trends Over Time',956'Financial trends describe how financial activity evolves across different time periods.',957'Trend analysis helps organizations identify growth patterns and financial cycles.',958ARRAY['financial trends','transaction trends','activity trends']959),960 961(962'High Activity Entities',963'High activity entities represent accounts or participants that show elevated levels of financial operations.',964'Identifying high activity entities helps organizations monitor major contributors to financial activity.',965ARRAY['high activity accounts','active entities','frequent transactions']966),967 968(969'Financial Participation',970'Financial participation reflects how widely financial services or operations are used among participants.',971'Analyzing participation helps understand adoption and engagement levels.',972ARRAY['financial participation','system usage','participant activity']973),974 975(976'Financial Growth Patterns',977'Financial growth patterns describe increases or decreases in financial activity levels over time.',978'Understanding growth patterns helps organizations assess expansion or contraction in operations.',979ARRAY['financial growth','activity growth','transaction growth']980),981 982(983'Operational Financial Insights',984'Operational financial insights focus on understanding the behavior and structure of financial operations within a system.',985'These insights help organizations make informed decisions about financial management and operational efficiency.',986ARRAY['financial insights','operational finance','financial analytics']987);988 989```990 991# Business rules for Healthcare schema992 993```sql994 995-- ============================================996-- CREATE HEALTHCARE BUSINESS RULES TABLE997-- ============================================998 999CREATE TABLE healthcare_schema.healthcare_business_rules (1000    rule_id SERIAL PRIMARY KEY,1001    concept_name VARCHAR(150) NOT NULL,1002    description TEXT NOT NULL,1003    insight TEXT,1004    keywords TEXT[],1005    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP1006);1007 1008-- ============================================1009-- TABLE COMMENT1010-- ============================================1011 1012COMMENT ON TABLE healthcare_schema.healthcare_business_rules IS1013'Stores healthcare domain business knowledge used by the Intent Agent to interpret user queries. 1014The rules describe healthcare analytical concepts and insights in natural language. 1015They intentionally avoid referencing database tables, columns, or SQL logic to prevent 1016biasing downstream agents responsible for schema selection and SQL generation.';1017 1018-- ============================================1019-- COLUMN COMMENTS1020-- ============================================1021 1022COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.rule_id IS1023'Unique identifier assigned to each healthcare business rule.';1024 1025COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.concept_name IS1026'High-level healthcare concept or analytical theme represented by the rule, such as patient activity, treatment patterns, or healthcare service utilization.';1027 1028COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.description IS1029'Neutral natural-language explanation of the healthcare concept. 1030This field describes the concept without referencing database schema elements, implementation details, or SQL logic.';1031 1032COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.insight IS1033'Healthcare insight explaining why the concept is important for analysis, reporting, or operational understanding.';1034 1035COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.keywords IS1036'List of keywords or phrases associated with the healthcare concept. 1037These keywords help semantic retrieval systems match user queries with relevant business rules.';1038 1039COMMENT ON COLUMN healthcare_schema.healthcare_business_rules.created_at IS1040'Timestamp indicating when the healthcare business rule was created. 1041Useful for governance, auditing, and lifecycle management.';1042 1043-- ============================================1044-- INSERT HEALTHCARE BUSINESS RULES1045-- ============================================1046 1047INSERT INTO healthcare_schema.healthcare_business_rules1048(concept_name, description, insight, keywords)1049VALUES1050 1051(1052'Patient Registration Activity',1053'Patient registration activity reflects how new patients begin interacting with healthcare services.',1054'Monitoring registration patterns helps healthcare providers understand patient intake trends and service demand.',1055ARRAY['new patients','patient registration','patient intake','patient enrollment']1056),1057 1058(1059'Patient Demographic Distribution',1060'Patient demographic distribution describes how patients are represented across different demographic characteristics.',1061'Understanding demographic distribution helps healthcare organizations identify the populations they serve.',1062ARRAY['patient demographics','patient distribution','demographic analysis']1063),1064 1065(1066'Patient Visit Activity',1067'Patient visit activity represents how frequently patients interact with healthcare providers through appointments or consultations.',1068'Tracking visit activity helps healthcare providers understand service utilization patterns.',1069ARRAY['patient visits','appointments','consultations','visit frequency']1070),1071 1072(1073'Healthcare Service Utilization',1074'Healthcare service utilization reflects how healthcare services are used by patients across the organization.',1075'Analyzing service utilization helps identify which services are most frequently accessed.',1076ARRAY['service usage','healthcare utilization','medical services']1077),1078 1079(1080'Treatment Patterns',1081'Treatment patterns describe how different treatments or medical procedures are provided to patients.',1082'Understanding treatment patterns helps healthcare organizations evaluate care delivery practices.',1083ARRAY['treatment patterns','medical procedures','patient treatment']1084),1085 1086(1087'Patient Care Activity',1088'Patient care activity reflects the range of interactions between healthcare providers and patients during care delivery.',1089'Monitoring care activity helps organizations understand healthcare workload and service delivery levels.',1090ARRAY['patient care','care activity','medical care interactions']1091),1092 1093(1094'Patient Health Trends',1095'Patient health trends describe how patient-related health activities evolve over time.',1096'Tracking trends helps healthcare providers understand changes in care demand and treatment needs.',1097ARRAY['health trends','patient health patterns','health activity trends']1098),1099 1100(1101'Healthcare Provider Activity',1102'Healthcare provider activity reflects the level of engagement healthcare professionals have in delivering patient care.',1103'Analyzing provider activity helps healthcare organizations understand workload distribution and service capacity.',1104ARRAY['provider activity','medical staff activity','clinical workload']1105),1106 1107(1108'Appointment Patterns',1109'Appointment patterns describe how patients schedule and attend healthcare visits over time.',1110'Understanding appointment patterns helps healthcare providers optimize scheduling and service availability.',1111ARRAY['appointments','visit scheduling','appointment trends']1112),1113 1114(1115'Patient Engagement',1116'Patient engagement reflects how actively patients participate in healthcare services and interactions.',1117'High levels of engagement often indicate stronger patient involvement in care processes.',1118ARRAY['patient engagement','patient interaction','healthcare participation']1119),1120 1121(1122'Healthcare Activity Trends',1123'Healthcare activity trends describe how healthcare interactions and service usage change across different time periods.',1124'Trend analysis helps healthcare organizations anticipate demand and allocate resources effectively.',1125ARRAY['healthcare trends','medical activity trends','service demand trends']1126),1127 1128(1129'Patient Participation',1130'Patient participation reflects how widely healthcare services are utilized across the patient population.',1131'Analyzing participation helps healthcare providers understand overall service reach.',1132ARRAY['patient participation','service adoption','patient involvement']1133),1134 1135(1136'Clinical Activity Monitoring',1137'Clinical activity monitoring focuses on observing patterns in healthcare delivery and patient interactions.',1138'Monitoring clinical activity helps healthcare organizations maintain efficient service operations.',1139ARRAY['clinical activity','medical operations','care monitoring']1140),1141 1142(1143'Patient Service Demand',1144'Patient service demand reflects the level of need for healthcare services among patients.',1145'Understanding demand helps healthcare providers plan staffing, services, and resources.',1146ARRAY['service demand','healthcare demand','medical demand']1147),1148 1149(1150'Healthcare Interaction Distribution',1151'Healthcare interaction distribution describes how patient interactions are spread across different services or providers.',1152'Analyzing interaction distribution helps healthcare organizations understand how care is delivered.',1153ARRAY['healthcare interactions','patient interactions','service distribution']1154);1155 1156```