prazy1208/text2sql

1-- App schema for Stage 1: sessions and intent_agent_output2-- Run against text2sql_db (e.g. in pgAdmin Query Tool or via run_create_app_schema.py)3 4-- Schema5CREATE SCHEMA IF NOT EXISTS app_schema;6 7-- Sessions: one row per conversation session8CREATE TABLE IF NOT EXISTS app_schema.sessions (9    session_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),10    created_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,11    updated_at   TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,12    title          TEXT,13    client_id      UUID,14    use_case       VARCHAR(64)15);16 17-- Idempotent upgrades for databases created before title/client_id/use_case existed18ALTER TABLE app_schema.sessions ADD COLUMN IF NOT EXISTS title TEXT;19ALTER TABLE app_schema.sessions ADD COLUMN IF NOT EXISTS client_id UUID;20ALTER TABLE app_schema.sessions ADD COLUMN IF NOT EXISTS use_case VARCHAR(64);21 22CREATE INDEX IF NOT EXISTS idx_sessions_client_id_updated_at23    ON app_schema.sessions (client_id, updated_at DESC);24 25-- Intent agent output: one row per user request; each Intent output in separate columns26CREATE TABLE IF NOT EXISTS app_schema.intent_agent_output (27    id                 SERIAL PRIMARY KEY,28    session_id         UUID NOT NULL REFERENCES app_schema.sessions(session_id) ON DELETE CASCADE,29    use_case           VARCHAR(64) NOT NULL,30    user_input         TEXT NOT NULL,31    rephrased_question TEXT,32    keywords           TEXT[],33    business_insights  TEXT[],34    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP35);36 37-- Optional: index for listing outputs by session38CREATE INDEX IF NOT EXISTS idx_intent_agent_output_session_id39    ON app_schema.intent_agent_output(session_id);40 41-- Optional: index for filtering by use_case42CREATE INDEX IF NOT EXISTS idx_intent_agent_output_use_case43    ON app_schema.intent_agent_output(use_case);44 45-- Table agent output: at most one row per intent row (FK intent_agent_output.id)46CREATE TABLE IF NOT EXISTS app_schema.table_agent_output (47    id                 SERIAL PRIMARY KEY,48    intent_output_id   INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,49    selected_tables    TEXT[] NOT NULL DEFAULT '{}',50    created_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,51    UNIQUE (intent_output_id)52);53 54COMMENT ON SCHEMA app_schema IS 'Application/session data for Text2SQL Stage 1';55COMMENT ON TABLE app_schema.sessions IS 'One row per chat session';56COMMENT ON TABLE app_schema.intent_agent_output IS 'One row per user query; stores Intent Agent output in separate columns';57COMMENT ON TABLE app_schema.table_agent_output IS 'Table Agent: selected_tables for one intent_agent_output row';58 59-- Few-shot agent output: at most one row per intent_agent_output row60CREATE TABLE IF NOT EXISTS app_schema.few_shot_agent_output (61    id                  SERIAL PRIMARY KEY,62    intent_output_id    INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,63    few_shot_examples   JSONB NOT NULL DEFAULT '[]'::jsonb,64    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,65    UNIQUE (intent_output_id)66);67 68COMMENT ON TABLE app_schema.few_shot_agent_output IS 'Few-Shot Agent: selected examples for one intent_agent_output row';69 70-- Column agent output: at most one row per table_agent_output row71CREATE TABLE IF NOT EXISTS app_schema.column_agent_output (72    id                      SERIAL PRIMARY KEY,73    table_agent_output_id   INT NOT NULL REFERENCES app_schema.table_agent_output(id) ON DELETE CASCADE,74    selected_columns        JSONB NOT NULL DEFAULT '{}'::jsonb,75    created_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,76    UNIQUE (table_agent_output_id)77);78 79COMMENT ON TABLE app_schema.column_agent_output IS 'Column Agent: selected_columns (per table) for one table_agent_output row';80 81-- ---------------------------------------------------------------------------82-- Chat persistence (for chat-style UX + intent confirmation flow)83-- ---------------------------------------------------------------------------84 85-- One row per displayed chat message (user/assistant/system), per session.86CREATE TABLE IF NOT EXISTS app_schema.chat_messages (87    id            BIGSERIAL PRIMARY KEY,88    session_id    UUID NOT NULL REFERENCES app_schema.sessions(session_id) ON DELETE CASCADE,89    role          VARCHAR(16) NOT NULL,  -- 'user' | 'assistant' | 'system'90    message_type  VARCHAR(32) NOT NULL DEFAULT 'message', -- 'new_query' | 'intent_confirmation' | 'intent_correction' | etc.91    content       TEXT NOT NULL,92    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP93);94 95CREATE INDEX IF NOT EXISTS idx_chat_messages_session_id_created_at96    ON app_schema.chat_messages(session_id, created_at);97 98COMMENT ON TABLE app_schema.chat_messages IS 'Session chat transcript (user/assistant/system messages)';99 100-- Records intent confidence + confirmation status for a given intent output.101CREATE TABLE IF NOT EXISTS app_schema.intent_review (102    id                     BIGSERIAL PRIMARY KEY,103    intent_output_id       INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,104    confidence_score       INT NOT NULL, -- 0..100105    confirmation_required  BOOLEAN NOT NULL DEFAULT FALSE,106    confirmation_status    VARCHAR(16) NOT NULL DEFAULT 'pending', -- 'pending' | 'confirmed' | 'rejected' | 'superseded'107    reviewed_at            TIMESTAMP WITH TIME ZONE,108    created_at             TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,109    UNIQUE (intent_output_id)110);111 112CREATE INDEX IF NOT EXISTS idx_intent_review_status113    ON app_schema.intent_review(confirmation_status);114 115COMMENT ON TABLE app_schema.intent_review IS 'Intent confidence + user confirmation status for one intent_agent_output row';116 117-- Stores rolling summary for long conversations to keep prompts token-safe.118CREATE TABLE IF NOT EXISTS app_schema.session_memory (119    session_id                  UUID PRIMARY KEY REFERENCES app_schema.sessions(session_id) ON DELETE CASCADE,120    summary_json                JSONB NOT NULL DEFAULT '{}'::jsonb,121    last_summarized_message_id  BIGINT,122    updated_at                  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP123);124 125COMMENT ON TABLE app_schema.session_memory IS 'Per-session structured summary for context-window + long chat memory';126 127-- Gen-SQL agent output: at most one row per intent_agent_output row128CREATE TABLE IF NOT EXISTS app_schema.gen_sql_agent_output (129    id                         SERIAL PRIMARY KEY,130    intent_output_id           INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,131    generated_sql              TEXT NOT NULL DEFAULT '',132    reasoning_summary          TEXT,133    validation_passed          BOOLEAN NOT NULL DEFAULT FALSE,134    validation_error_codes     TEXT NOT NULL DEFAULT '',135    validation_error_message   TEXT NOT NULL DEFAULT '',136    blocked_keywords           TEXT NOT NULL DEFAULT '',137    is_single_statement        BOOLEAN NOT NULL DEFAULT FALSE,138    is_select_only             BOOLEAN NOT NULL DEFAULT FALSE,139    created_at                 TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,140    UNIQUE (intent_output_id)141);142 143COMMENT ON TABLE app_schema.gen_sql_agent_output IS 'Gen-SQL Agent: generated SQL and rule-based validation columns per intent_agent_output row';144