Connect Local AI to Enterprise Databases

-- Stored Procedure: GetRecordSummaryData
-- Purpose: Retrieve data for AI summarization
-- Security: Returns only approved fields, user-scoped

CREATE PROCEDURE [dbo].[GetRecordSummaryData]
    @RecordId INT,
    @RequestingUser NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    -- Verify user has access to this record
    IF NOT EXISTS (
        SELECT 1 FROM Records r
        INNER JOIN UserAccess ua ON r.DepartmentId = ua.DepartmentId
        WHERE r.Id = @RecordId
        AND ua.Username = @RequestingUser
    )
    BEGIN
        RAISERROR('Access denied', 16, 1);
        RETURN;
    END

    -- Return only the fields needed for AI summary
    SELECT
        r.Id,
        r.Title,
        r.Description,
        r.Category,
        r.Status,
        r.CreatedDate,
        r.LastModified,
        -- Aggregate related data
        (
            SELECT COUNT(*)
            FROM Comments c
            WHERE c.RecordId = r.Id
        ) AS CommentCount,
        -- Include recent activity summary
        (
            SELECT TOP 5
                a.ActionType + ': ' + a.Description
            FROM ActivityLog a
            WHERE a.RecordId = r.Id
            ORDER BY a.Timestamp DESC
            FOR JSON PATH
        ) AS RecentActivity
    FROM
        Records r
    WHERE
        r.Id = @RecordId
        AND r.Status != 'Draft'  -- Don't expose drafts
END

{
  "model": "llama3.1:14b",
  "messages": [
    {
      "role": "user",
      "content": "Say 'Hello from DreamFactory' and nothing else."
    }
  ],
  "max_tokens": 50
}

# AISummary Scripted Service
# Orchestrates data retrieval and AI summarization

import json
import urllib.request
import urllib.error

# ─── CONFIGURATION ──────────────────────────────────
BASE_URL = "http://localhost"  # Internal DF URL
API_KEY = event.request.headers.get("X-DreamFactory-Api-Key", "")
AI_MODEL = "llama3.1:14b"  # Or "gpt-4o-mini" for OpenAI

# ─── HELPER FUNCTION ────────────────────────────────
def internal_request(method, url, payload=None, timeout=300):
    """Make internal DreamFactory API call"""
    headers = {
        "X-DreamFactory-Api-Key": API_KEY,
        "Content-Type": "application/json"
    }

    data = json.dumps(payload).encode('utf-8') if payload else None
    req = urllib.request.Request(url, data=data, headers=headers, method=method)

    try:
        with urllib.request.urlopen(req, timeout=timeout) as response:
            return json.loads(response.read().decode('utf-8'))
    except urllib.error.HTTPError as e:
        return {"error": f"HTTP {e.code}: {e.reason}"}
    except Exception as e:
        return {"error": str(e)}

# ─── MAIN LOGIC ─────────────────────────────────────

# 1. Extract request parameters
record_id = event.request.parameters.get("record_id")
requesting_user = event.request.parameters.get("user", "anonymous")

if not record_id:
    raise ValueError("Missing required parameter: record_id")

# 2. Call stored procedure to get data
proc_url = f"{BASE_URL}/api/v2/MainDatabase/_proc/GetRecordSummaryData"
proc_payload = {
    "params": [
        {"name": "RecordId", "value": record_id},
        {"name": "RequestingUser", "value": requesting_user}
    ]
}

proc_result = internal_request("POST", proc_url, proc_payload, timeout=30)

if "error" in proc_result:
    raise Exception(f"Database error: {proc_result['error']}")

if not proc_result or not proc_result.get("resource"):
    raise ValueError("No data found for the specified record")

record_data = proc_result["resource"][0]

# 3. Format prompt for AI
system_prompt = """You are a professional analyst. Summarize the provided data
concisely and highlight key insights. Use bullet points for clarity.
Keep the summary under 200 words."""

user_prompt = f"""Please summarize the following record:

Title: {record_data.get('Title', 'N/A')}
Category: {record_data.get('Category', 'N/A')}
Status: {record_data.get('Status', 'N/A')}
Created: {record_data.get('CreatedDate', 'N/A')}
Description: {record_data.get('Description', 'N/A')}
Comment Count: {record_data.get('CommentCount', 0)}
Recent Activity: {record_data.get('RecentActivity', 'None')}

Provide a concise summary with key insights."""

# 4. Call AI model
ai_url = f"{BASE_URL}/api/v2/LocalAI/chat/completions"
ai_payload = {
    "model": AI_MODEL,
    "messages": [
        {"role": "system", "content": system_prompt},
        {"role": "user", "content": user_prompt}
    ],
    "max_tokens": 500,
    "temperature": 0.3  # Lower = more deterministic
}

ai_result = internal_request("POST", ai_url, ai_payload, timeout=300)

if "error" in ai_result:
    raise Exception(f"AI error: {ai_result['error']}")

# 5. Extract and return summary
summary = ai_result.get("choices", [{}])[0].get("message", {}).get("content", "")

return {
    "record_id": record_id,
    "title": record_data.get("Title"),
    "summary": summary.strip(),
    "model_used": AI_MODEL,
    "generated_at": __import__("datetime").datetime.utcnow().isoformat() + "Z"
}

{
  "record_id": "123",
  "title": "Q4 Sales Report",
  "summary": "**Key Insights:**\n• Record is currently Active with 12 comments\n• Created in Q4 2024, last modified recently\n• High engagement indicated by comment volume\n• Recent activity shows review and approval workflow\n\n**Recommendation:** This record appears to be progressing through standard approval process with healthy stakeholder engagement.",
  "model_used": "llama3.1:14b",
  "generated_at": "2026-02-05T14:32:18Z"
}

curl -X GET \
  "https://your-dreamfactory.com/api/v2/AISummary?record_id=123&user=testuser" \
  -H "X-DreamFactory-Api-Key: YOUR_API_KEY" \
  -H "Accept: application/json"