Deterministic Queries &
Stored Procedures for AI

🎯 The Core Problem

When AI systems generate SQL queries dynamically, organizations lose control over data access. The same question asked twice may generate different queries with different performance characteristics, and potentially different security implications.

This isn't just a performance issue. Regulatory frameworks like GDPR, HIPAA, and FedRAMP require documented, approved data access patterns. Ad-hoc SQL generation makes compliance nearly impossible to prove.

⚖️ Raw SQL vs. Stored Procedures

🔐 The Security Contract

Think of a stored procedure as a security contract between your database and AI systems. The contract says:

• These are the exact fields you can access
• These are the only parameters you can pass
• This is the maximum data you can retrieve
• Every call will be logged with full parameter visibility

The AI can call the procedure, but it can never see or modify the underlying query. It's like giving someone a vending machine instead of the keys to the warehouse.

🧩 Four AI-Safe Access Patterns

💻 Example: Secure Data Retrieval Procedure

Here's a stored procedure that acts as a security gatekeeper for sensitive business data:

CREATE PROCEDURE GetRecordSummaryData
    @RecordId INT
AS
BEGIN
    -- Only return approved/completed records
    -- Exclude sensitive fields (SSN, payment info, etc.)
    SELECT
        r.DisplayName,
        r.Category,
        r.Department,
        r.CreatedDate,
        r.StatusDescription,
        r.PublicNotes,
        r.SummaryMetrics
    FROM Records r
    WHERE r.Id = @RecordId
      AND r.Status = 'Approved'
    ORDER BY r.CreatedDate DESC
END

Notice what this procedure doesn't expose: sensitive PII, financial data, or draft records. The AI can generate summaries from this data, but it physically cannot access what the procedure doesn't return. This pattern applies to any domain: HR, finance, healthcare, customer data, and more.

📋 Compliance Benefits

🔀 Parameter Isolation: A Lesson from the Field

The fix was simple but illustrates an important principle: use DreamFactory's script to handle business parameters (like user identity) separately from what gets sent to the AI model.

• Your stored procedure handles the "who": user identity, permissions, data scoping
• The AI model only receives the "what": the actual data to process
• DreamFactory's script orchestrates both without mixing concerns

This separation makes your integration portable across AI providers. Switch from Ollama to GPT to Claude to Gemma; the stored procedure and business logic stay exactly the same.

🚀 Next Steps

• Read the full blog post for deeper technical details
• Explore Module 05 to learn about identity passthrough for AI agents
• Review your existing stored procedures. Are they AI-ready?