TSQL.APP RAG Implementation Guide

Overview

This guide demonstrates implementing Retrieval-Augmented Generation (RAG) within TSQL.APP framework using Card Actions and framework stored procedures.

Database Structure

-- Knowledge Base Table
CREATE TABLE knowledge_base (
    id INT IDENTITY(1,1) PRIMARY KEY,
    title NVARCHAR(255),
    content NVARCHAR(MAX),
    embedding VARBINARY(MAX),
    metadata NVARCHAR(MAX),
    created_date DATETIME DEFAULT GETDATE(),
    updated_date DATETIME,
    source_url NVARCHAR(500),
    category NVARCHAR(100)
)

-- Embeddings Cache Table
CREATE TABLE embedding_cache (
    id INT IDENTITY(1,1) PRIMARY KEY,
    text_hash VARBINARY(32),
    embedding VARBINARY(MAX),
    created_date DATETIME DEFAULT GETDATE()
)

-- Query Log Table
CREATE TABLE query_log (
    id INT IDENTITY(1,1) PRIMARY KEY,
    query_text NVARCHAR(MAX),
    retrieved_docs VARCHAR(MAX),
    response NVARCHAR(MAX),
    user_email NVARCHAR(255),
    query_date DATETIME DEFAULT GETDATE(),
    feedback_score INT
)

Card Actions

1. Document Ingestion Action

-- Action: Ingest Documents
EXEC sp_api_modal_text @text=N'Document Ingestion', @class='h2'

-- File upload control
DECLARE @file_content NVARCHAR(MAX)
EXEC sp_api_modal_file_upload 
    @name='document_upload',
    @label='Upload Document',
    @content=@file_content OUT

-- Process document
IF @file_content IS NOT NULL
BEGIN
    -- Generate embedding using external service
    DECLARE @embedding VARBINARY(MAX)
    EXEC sp_generate_embedding @file_content, @embedding OUT
    
    -- Store in knowledge base
    INSERT INTO knowledge_base (content, embedding)
    VALUES (@file_content, @embedding)
    
    EXEC sp_api_modal_text @text='Document processed successfully'
END

2. Query Processing Action

-- Action: RAG Query Processing
EXEC sp_api_modal_text @text=N'AI Assistant', @class='h2'

-- Get user query
DECLARE @query NVARCHAR(MAX)
EXEC sp_api_modal_input 
    @name='user_query',
    @label='Ask a question:',
    @value=@query OUT,
    @multiline=1

IF @query IS NOT NULL
BEGIN
    -- Generate query embedding
    DECLARE @query_embedding VARBINARY(MAX)
    EXEC sp_generate_embedding @query, @query_embedding OUT
    
    -- Retrieve relevant documents
    DECLARE @context TABLE (
        id INT,
        content NVARCHAR(MAX),
        similarity FLOAT
    )
    
    INSERT INTO @context
    EXEC sp_similarity_search 
        @embedding = @query_embedding,
        @top_k = 3
    
    -- Generate response using context
    DECLARE @response NVARCHAR(MAX)
    EXEC sp_generate_response 
        @query = @query,
        @context = @context,
        @response = @response OUT
    
    -- Log interaction
    INSERT INTO query_log (query_text, response, user_email)
    VALUES (@query, @response, @User)
    
    -- Display response
    EXEC sp_api_modal_text @text=@response
END

3. Administrative Dashboard Action

-- Action: RAG Admin Dashboard
EXEC sp_api_modal_text @text=N'RAG System Dashboard', @class='h2'

-- System statistics
SELECT 
    (SELECT COUNT(*) FROM knowledge_base) as total_documents,
    (SELECT COUNT(*) FROM query_log WHERE CAST(query_date AS DATE) = CAST(GETDATE() AS DATE)) as queries_today,
    (SELECT AVG(feedback_score) FROM query_log WHERE feedback_score IS NOT NULL) as avg_feedback
INTO #stats

EXEC sp_api_modal_table @tmptable='#stats'

-- Recent queries
SELECT TOP 10 
    query_text,
    response,
    user_email,
    query_date,
    feedback_score
INTO #recent_queries
FROM query_log
ORDER BY query_date DESC

EXEC sp_api_modal_text @text=N'Recent Queries', @class='h3'
EXEC sp_api_modal_table @tmptable='#recent_queries'

Helper Stored Procedures

Vector Similarity Search

CREATE PROCEDURE sp_similarity_search
    @embedding VARBINARY(MAX),
    @top_k INT = 3
AS
BEGIN
    -- Implement cosine similarity search
    SELECT TOP (@top_k)
        id,
        content,
        dbo.cosine_similarity(embedding, @embedding) as similarity
    FROM knowledge_base
    ORDER BY dbo.cosine_similarity(embedding, @embedding) DESC
END

Embedding Generation

CREATE PROCEDURE sp_generate_embedding
    @text NVARCHAR(MAX),
    @embedding VARBINARY(MAX) OUTPUT
AS
BEGIN
    -- Check cache first
    DECLARE @text_hash VARBINARY(32) = HASHBYTES('SHA2_256', @text)
    
    SELECT @embedding = embedding
    FROM embedding_cache
    WHERE text_hash = @text_hash
    
    IF @embedding IS NULL
    BEGIN
        -- Call external embedding service
        -- Store result in cache
        INSERT INTO embedding_cache (text_hash, embedding)
        VALUES (@text_hash, @embedding)
    END
END

Context Variables

  • @User: Current user's email
  • @ID: Primary key of current record
  • @IDs: Selected record IDs
  • @CardName: Current Card name
  • @ActionName: Current Action name

Integration Points

External Services

  1. Embedding Generation Service
  2. LLM API Integration
  3. Document Processing Service

Configuration

CREATE TABLE rag_config (
    setting_name NVARCHAR(100) PRIMARY KEY,
    setting_value NVARCHAR(MAX),
    description NVARCHAR(500)
)

INSERT INTO rag_config VALUES
('embedding_service_url', 'https://api.example.com/embed', 'Embedding service endpoint'),
('llm_api_key', '****', 'API key for LLM service'),
('chunk_size', '512', 'Document chunk size in tokens')

Security Considerations

Access Control

CREATE TABLE rag_permissions (
    role_name NVARCHAR(50),
    permission_name NVARCHAR(50),
    granted_date DATETIME DEFAULT GETDATE(),
    granted_by NVARCHAR(255)
)

-- Example permissions
INSERT INTO rag_permissions (role_name, permission_name) VALUES
('Admin', 'MANAGE_KNOWLEDGE_BASE'),
('User', 'QUERY_RAG'),
('Manager', 'VIEW_ANALYTICS')

Data Privacy

  • Implement data masking for sensitive information
  • Log access to sensitive documents
  • Enable document-level permissions

Monitoring and Analytics

Performance Metrics

CREATE VIEW v_rag_metrics AS
SELECT
    CAST(query_date AS DATE) as date,
    COUNT(*) as query_count,
    AVG(DATEDIFF(ms, query_date, DATEADD(ms, 100, query_date))) as avg_response_time,
    AVG(CAST(feedback_score as FLOAT)) as avg_feedback
FROM query_log
GROUP BY CAST(query_date AS DATE)

Usage Analytics

CREATE VIEW v_user_analytics AS
SELECT
    user_email,
    COUNT(*) as total_queries,
    AVG(CAST(feedback_score as FLOAT)) as avg_feedback,
    MAX(query_date) as last_query_date
FROM query_log
GROUP BY user_email

Best Practices

  1. Document Preprocessing

    • Chunk documents appropriately
    • Maintain context windows
    • Extract metadata

  2. Query Optimization

    • Cache frequent queries
    • Implement semantic search
    • Use hybrid retrieval

  3. Response Generation

    • Include source citations
    • Handle edge cases
    • Implement feedback loop

Deployment Steps

  1. Database Setup
-- Create required tables
-- Configure security
-- Initialize settings

  1. External Services

    • Configure embedding service
    • Set up LLM integration
    • Enable monitoring

  2. Card Configuration

    • Create knowledge base Card
    • Set up query interface
    • Configure admin dashboard

Maintenance Tasks

Regular Maintenance

-- Cleanup old cache entries
DELETE FROM embedding_cache 
WHERE created_date < DATEADD(day, -30, GETDATE())

-- Update statistics
UPDATE STATISTICS knowledge_base
UPDATE STATISTICS embedding_cache

System Health Checks

-- Monitor system health
CREATE PROCEDURE sp_rag_health_check
AS
BEGIN
    -- Check embedding service
    -- Verify LLM integration
    -- Validate knowledge base integrity
END