🧬 Eventhouse as a Vector Database - AI-Powered Semantic Search¶

// Create a table for document vectors
.create table DocumentVectors (
    document_id: string,
    document_text: string,
    document_type: string,
    source_system: string,
    tags: dynamic,
    created_date: datetime,
    embedding: dynamic
)

// Apply Vector16 encoding policy to the embedding column
.alter column DocumentVectors.embedding policy encoding
    type = 'Vector16'

// Verify encoding policy
.show table DocumentVectors policy encoding

// Create an external connection to Azure OpenAI
.create-or-alter function with (
    docstring = "Generate embeddings using Azure OpenAI",
    skipvalidation = "true"
) GenerateEmbedding(text: string) {
    let endpoint = 'https://your-aoai-resource.openai.azure.com';
    let deployment = 'text-embedding-3-small';
    let result = evaluate ai_embed_text(text, endpoint, deployment);
    result
}

// Check table row count and embedding dimensions
DocumentVectors
| take 1
| project document_id, document_type, EmbeddingDimensions = array_length(embedding)

// Verify Vector16 encoding is active
DocumentVectors
| take 5
| project document_id, document_type,
    EmbeddingLength = array_length(embedding),
    FirstDims = array_slice(embedding, 0, 4)

// Basic cosine similarity between two vectors
print similarity = series_cosine_similarity(
    dynamic([0.1, 0.2, 0.3, 0.4]),
    dynamic([0.1, 0.2, 0.3, 0.5])
)
// Output: ~0.9936

// Find the top 10 most similar documents to a query
let query_embedding = dynamic([0.012, -0.034, 0.089, ...]); // Pre-computed query vector
DocumentVectors
| extend similarity = series_cosine_similarity(embedding, query_embedding)
| top 10 by similarity desc
| project document_id, document_text, document_type, similarity, created_date

// Hybrid search: Vector similarity + document type + date range
let query_embedding = dynamic([0.012, -0.034, 0.089, ...]);
DocumentVectors
| where document_type == "SAR"
| where created_date >= ago(90d)
| extend similarity = series_cosine_similarity(embedding, query_embedding)
| top 10 by similarity desc
| project document_id, document_text, similarity, created_date

// Complex hybrid: Vector + multiple structured filters + tag matching
let query_embedding = dynamic([0.012, -0.034, 0.089, ...]);
let target_tags = dynamic(["structuring", "cash", "suspicious"]);
DocumentVectors
| where document_type in ("SAR", "CTR")
| where created_date between (datetime(2026-01-01) .. datetime(2026-03-31))
| where tags has_any (target_tags)
| extend similarity = series_cosine_similarity(embedding, query_embedding)
| where similarity > 0.75  // Minimum similarity threshold
| top 20 by similarity desc
| project
    document_id,
    document_text = substring(document_text, 0, 200),
    document_type,
    similarity = round(similarity, 4),
    matched_tags = set_intersect(tags, target_tags),
    created_date

// Compare query against documents grouped by type
let query_embedding = dynamic([0.012, -0.034, 0.089, ...]);
DocumentVectors
| extend similarity = series_cosine_similarity(embedding, query_embedding)
| summarize
    avg_similarity = avg(similarity),
    max_similarity = max(similarity),
    doc_count = count()
    by document_type
| order by max_similarity desc

// Generate an embedding for a query string
evaluate ai_embed_text(
    'Find all reports about cash structuring patterns',
    'https://your-aoai-resource.openai.azure.com',
    'text-embedding-3-small'
)

// End-to-end: Embed query → Search → Return results
let query_text = 'transactions that appear to be structuring to avoid CTR';
let query_result = evaluate ai_embed_text(
    query_text,
    'https://your-aoai-resource.openai.azure.com',
    'text-embedding-3-small'
);
let query_vector = toscalar(query_result | project embedding);
DocumentVectors
| extend similarity = series_cosine_similarity(embedding, query_vector)
| top 10 by similarity desc
| project document_id, document_type, similarity,
    preview = substring(document_text, 0, 300)

// Batch embed unprocessed documents
// Step 1: Identify documents missing embeddings
let unembedded = DocumentVectors
| where isnull(embedding) or array_length(embedding) == 0
| take 100; // Process 100 at a time
// Step 2: Generate embeddings (via notebook or pipeline)
// Step 3: Update with .set-or-append

// Basic chat completion
evaluate ai_chat_completion(
    'Summarize the following compliance filing in plain language.',
    'https://your-aoai-resource.openai.azure.com',
    'gpt-4o'
)

// Full RAG pipeline in a single KQL query
// Step 1: Embed the user's question
let user_question = 'What structuring patterns were detected in Q1 2026?';
let query_result = evaluate ai_embed_text(
    user_question,
    'https://your-aoai-resource.openai.azure.com',
    'text-embedding-3-small'
);
let query_vector = toscalar(query_result | project embedding);
// Step 2: Retrieve top-K relevant documents
let context_docs = DocumentVectors
| where document_type == "SAR"
| where created_date >= datetime(2026-01-01)
| extend similarity = series_cosine_similarity(embedding, query_vector)
| top 5 by similarity desc
| project document_text;
// Step 3: Assemble context and generate response
let context = toscalar(
    context_docs
    | summarize context = strcat_array(make_list(document_text), '\n---\n')
);
let prompt = strcat(
    'Based on the following SAR filings, answer the question.\n\n',
    'Context:\n', context, '\n\n',
    'Question: ', user_question, '\n\n',
    'Provide a concise summary with specific patterns identified.'
);
evaluate ai_chat_completion(
    prompt,
    'https://your-aoai-resource.openai.azure.com',
    'gpt-4o'
)

// Chat completion with structured system prompt for compliance officer
let system_prompt = 'You are a compliance analysis assistant for a casino gaming '
    'operation. You analyze CTR and SAR filings to identify patterns. '
    'Always cite specific filing IDs. Flag any potential BSA violations.';
let user_message = 'Summarize unusual transaction patterns from the last 30 days.';
// ... (retrieve context via vector search) ...
evaluate ai_chat_completion(
    user_message,
    'https://your-aoai-resource.openai.azure.com',
    'gpt-4o',
    system_message = system_prompt
)

// Compliance document vector table
.create table ComplianceDocVectors (
    filing_id: string,
    filing_type: string,         // CTR, SAR, W-2G
    narrative_text: string,      // Full filing narrative
    player_id: string,
    property_id: string,
    transaction_amount: real,
    filing_date: datetime,
    tags: dynamic,               // ["structuring", "cash", "wire"]
    status: string,              // Pending, Filed, Flagged
    embedding: dynamic           // Vector16 encoded
)

// Apply Vector16 encoding
.alter column ComplianceDocVectors.embedding policy encoding type = 'Vector16'

// "Find filings similar to this structuring pattern"
let known_pattern = 'Multiple cash transactions of $9,500 each over three '
    'consecutive days at different cage windows. Patron used '
    'different player cards for each transaction.';
let pattern_embedding = toscalar(
    evaluate ai_embed_text(
        known_pattern,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
ComplianceDocVectors
| where filing_type == "SAR"
| where filing_date >= ago(180d)
| extend similarity = series_cosine_similarity(embedding, pattern_embedding)
| where similarity > 0.80
| top 20 by similarity desc
| project
    filing_id,
    player_id,
    transaction_amount,
    similarity = round(similarity, 4),
    narrative_preview = substring(narrative_text, 0, 250),
    filing_date,
    status

// Step 1: Officer describes suspicious activity in natural language
let officer_query = 'patron making multiple sub-threshold cash deposits '
    'that individually fall below CTR reporting but aggregate above $10,000 '
    'within a single gaming day';

// Step 2: Embed and search
let q_vec = toscalar(
    evaluate ai_embed_text(
        officer_query,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);

// Step 3: Find similar filings with compliance context
ComplianceDocVectors
| extend similarity = series_cosine_similarity(embedding, q_vec)
| where similarity > 0.75
| top 15 by similarity desc
| join kind=leftouter (
    ComplianceDocVectors
    | summarize filing_count = count() by player_id
) on player_id
| project
    filing_id,
    filing_type,
    player_id,
    player_total_filings = filing_count,
    transaction_amount,
    similarity = round(similarity, 4),
    narrative_preview = substring(narrative_text, 0, 200),
    filing_date
| extend risk_flag = iff(player_total_filings > 3, "⚠️ Repeat Filer", "")

// Find CTR filings with unusual narrative patterns
let unusual_pattern = 'large cash transaction immediately followed by chip purchase '
    'and cashout at different cage window';
let q_vec = toscalar(
    evaluate ai_embed_text(
        unusual_pattern,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
ComplianceDocVectors
| where filing_type == "CTR"
| where transaction_amount >= 10000
| extend similarity = series_cosine_similarity(embedding, q_vec)
| where similarity > 0.70
| summarize
    filings = count(),
    total_amount = sum(transaction_amount),
    avg_similarity = avg(similarity),
    date_range = strcat(format_datetime(min(filing_date), 'yyyy-MM-dd'),
                        ' to ', format_datetime(max(filing_date), 'yyyy-MM-dd'))
    by player_id
| where filings > 1
| order by total_amount desc

// Federal policy document vector table
.create table FederalPolicyVectors (
    chunk_id: string,
    agency: string,              // USDA, EPA, NOAA, SBA, DOI
    document_title: string,
    section_title: string,
    chunk_text: string,          // 500-token policy chunk
    cfr_reference: string,       // e.g., "7 CFR Part 1410"
    effective_date: datetime,
    policy_category: string,     // regulation, guidance, memo, directive
    tags: dynamic,
    embedding: dynamic           // Vector16 encoded
)

.alter column FederalPolicyVectors.embedding policy encoding type = 'Vector16'

// Search USDA policies related to crop insurance requirements
let query = 'What are the eligibility requirements for federal crop insurance '
    'under the Federal Crop Insurance Act?';
let q_vec = toscalar(
    evaluate ai_embed_text(
        query,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
FederalPolicyVectors
| where agency == "USDA"
| extend similarity = series_cosine_similarity(embedding, q_vec)
| top 5 by similarity desc
| project
    document_title,
    section_title,
    cfr_reference,
    similarity = round(similarity, 4),
    chunk_preview = substring(chunk_text, 0, 300),
    effective_date

// Search EPA regulations about toxic release reporting thresholds
let query = 'What are the TRI reporting thresholds for lead and mercury releases?';
let q_vec = toscalar(
    evaluate ai_embed_text(
        query,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
FederalPolicyVectors
| where agency == "EPA"
| where tags has_any (dynamic(["TRI", "reporting", "threshold"]))
| extend similarity = series_cosine_similarity(embedding, q_vec)
| top 5 by similarity desc
| project document_title, section_title, cfr_reference,
    similarity = round(similarity, 4),
    chunk_text, effective_date

// Find related policies across agencies for environmental impact
let query = 'environmental impact assessment requirements for federal projects';
let q_vec = toscalar(
    evaluate ai_embed_text(
        query,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
FederalPolicyVectors
| extend similarity = series_cosine_similarity(embedding, q_vec)
| where similarity > 0.70
| top 3 by similarity desc  // Best match per agency
| project
    agency,
    document_title,
    cfr_reference,
    similarity = round(similarity, 4),
    policy_category,
    chunk_preview = substring(chunk_text, 0, 250)
| order by agency asc, similarity desc

// Complete RAG: Analyst asks a policy question, gets a grounded answer
let analyst_question = 'What are the requirements for environmental impact '
    'statements under NEPA for DOI land management projects?';
let q_vec = toscalar(
    evaluate ai_embed_text(
        analyst_question,
        'https://your-aoai-resource.openai.azure.com',
        'text-embedding-3-small'
    ) | project embedding
);
let context = toscalar(
    FederalPolicyVectors
    | where agency in ("DOI", "EPA")
    | extend similarity = series_cosine_similarity(embedding, q_vec)
    | top 5 by similarity desc
    | summarize ctx = strcat_array(
        make_list(strcat('[', cfr_reference, '] ', chunk_text)), '\n---\n')
);
let prompt = strcat(
    'You are a federal policy analyst. Answer the question based ONLY on the ',
    'provided policy excerpts. Cite specific CFR references.\n\n',
    'Policy Context:\n', context, '\n\n',
    'Question: ', analyst_question
);
evaluate ai_chat_completion(
    prompt,
    'https://your-aoai-resource.openai.azure.com',
    'gpt-4o'
)

// Partition by document type for targeted searches
// This allows the engine to skip irrelevant partitions
ComplianceDocVectors
| where document_type == "SAR"  // Engine skips CTR and W-2G partitions
| where filing_date >= ago(90d)  // Further reduces scan scope
| extend similarity = series_cosine_similarity(embedding, query_vector)
| top 10 by similarity desc

// ✅ Good: Filter first, then compute similarity
DocumentVectors
| where agency == "EPA"                    // Filters to ~20% of data
| where created_date >= ago(365d)          // Filters to ~30% of that
| extend similarity = series_cosine_similarity(embedding, query_vector)
| top 10 by similarity desc

// ❌ Bad: Compute similarity on all rows, then filter
DocumentVectors
| extend similarity = series_cosine_similarity(embedding, query_vector)
| where agency == "EPA"
| where created_date >= ago(365d)
| top 10 by similarity desc

// Configure hot cache for vector tables
.alter table ComplianceDocVectors policy caching
    hot = 90d  // Keep 90 days in hot cache for fast similarity search

// Enable query results caching
set query_results_cache_max_age = time(10m);
ComplianceDocVectors
| extend similarity = series_cosine_similarity(embedding, query_vector)
| top 10 by similarity desc