zavacliniq

ZavaClinIQ – SQL Server 2025 + Foundry Local

Architecture

• SQL Server 2025 on localhost, Windows auth, database zavacliniq
• Foundry Local CLI 0.10.1+ (public preview) — one daemon serves ALL loaded models on a single pinned port (5273)
• Embeddings: qwen3-embedding-0.6b (1024 dims) — public catalog
• Chat: phi-4 (auto-selects GPU variant per machine) — public catalog
• Caddy HTTPS proxy: port 8444 → embeddings, port 8445 → chat, both → 127.0.0.1:5273 (build/Caddyfile)
• Provisioning: build/Setup-FoundryLocal.ps1 verifies/installs the CLI, pins the port, starts the daemon, downloads + loads both models, smoke-tests embeddings, installs + starts Caddy, HTTPS sanity-checks both Caddy ports, then imports Caddy's root CA into the Local Computer store and restarts SQL Server (one UAC prompt; skipped when already trusted, and skippable with -SkipCertTrust). build/Teardown-FoundryLocal.ps1 reverses the Foundry/Caddy side (stops Caddy, unloads models, stops daemon, clears cache, resets port) for a clean re-run. There is no separate database teardown — Deploy-ZavaClinIQ.ps1 step 01 drops + recreates zavacliniq on every run.
• sqlcmd: run the T-SQL scripts with sqlcmd -E -I (Windows auth; -I sets QUOTED_IDENTIFIER ON, which sqlcmd otherwise defaults OFF)

Flow

SQL Server 2025 → HTTPS → Caddy proxy → Foundry Local (localhost)

• Embeddings: AI_GENERATE_EMBEDDINGS(@prompt USE MODEL FoundryLocalEmbeddingModel) → 1024-dim vector
• Chat: sp_invoke_external_rest_endpoint → https://localhost:8445/v1/chat/completions
• Vector search: VECTOR_SEARCH() with DiskANN index (cosine metric)
• Care plan: vector search → prompt assembly → Foundry Local inference → JSON parse → ledger insert

Critical Facts

• Vector dimensions are 1024 (qwen3-embedding-0.6b)
• Requires Foundry Local CLI 0.10.1+ — both qwen3-embedding-0.6b and phi-4 are in the public catalog. Install with winget install Microsoft.FoundryLocal. In 0.10.x a single daemon serves every loaded model on one port (no per-service ports).
• sp_invoke_external_rest_endpoint max timeout is 230 seconds — prompts must stay concise
• Phi-4 model ID is variant-specific per machine — proc auto-detects via /v1/models endpoint
• foundry model load (NOT foundry model run) — run is interactive chat, load serves the REST API
• Daemon port is pinned via foundry config set port 5273; start with foundry server start (0.10.x; the old foundry service commands are gone)
• 00_prereqs.sql (enables external rest endpoint) and 01_database.sql (creates the database) run against master. All other scripts run against zavacliniq.
• Post-filter compensation: VECTOR_SEARCH() applies JOINs/WHERE after the ANN scan, so procs over-fetch 3x
• Care plan proc generates structured JSON: assessment, contraindications, medications (name/dose/route), follow-up
• Care plans are persisted to care.CarePlanLedger (append-only ledger) with full audit trail

Database Design

• Schemas: content (clinical documents + chunks + embeddings), care (care plans, ledger, patient vitals, patient-clinic links), org (clinics), ref (US states lookup)
• Ledger: care.CarePlanLedger uses LEDGER = ON (APPEND_ONLY = ON) for audit immutability
• ADR: ACCELERATED_DATABASE_RECOVERY = ON
• Optimized Locking: OPTIMIZED_LOCKING = ON

Stored Procedures

Proc	Purpose
content.SearchChunksByPrompt_Vector	Vector search for clinical evidence chunks (3x over-fetch)
care.usp_ai_agent_care_plan	RAG agent: vector search → prompt assembly → Foundry Local chat → JSON parse → ledger insert

Script Execution Order

All setup scripts are in the build/ subfolder (sqlserver2025/zavacliniq/build/).

One-shot deploy (preferred): run the build set in order, stopping on the first error:

c:\sqlaiinaction\sqlserver2025\zavacliniq\build\Deploy-ZavaClinIQ.ps1

The explicit per-script sequence below is what the deploy script runs — use it for manual/step-by-step execution:

$scriptDir = 'c:\sqlaiinaction\sqlserver2025\zavacliniq\build'
# -I sets QUOTED_IDENTIFIER ON (sqlcmd defaults it OFF) — required by the JSON
# CHECK constraints in 02_schema and CREATE VECTOR INDEX in 06.

# Prerequisites (run against master):
sqlcmd -S localhost -d master     -E -I -i "$scriptDir\00_prereqs.sql"
sqlcmd -S localhost -d master     -E -I -i "$scriptDir\01_database.sql"

# Against zavacliniq (all remaining):
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\02_schema.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\03_seeddata.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\04_ai_model.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\05_generate_embeddings.sql"           # calls Foundry Local
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\06_create_vector_index.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\07_create_proc_for_vector_search.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\08_ai_agent_care_plan.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\09_add_clinics.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$scriptDir\10_verify.sql"

Demo Scripts (run live after deployment)

The two live demo scripts are in the demo/ subfolder:

$demoDir = 'c:\sqlaiinaction\sqlserver2025\zavacliniq\demo'
sqlcmd -S localhost -d zavacliniq -E -I -i "$demoDir\04_call_vector_search.sql"
sqlcmd -S localhost -d zavacliniq -E -I -i "$demoDir\06_call_ai_agent_care_plan.sql"          # full RAG pipeline (~60-85s)

Demo Walkthrough (on-stage)

Consolidated teaching-order scripts live in sqlserver2025/zavacliniq/demo/; presentation assets in sqlserver2025/zavacliniq/resources/. Only two demo scripts are executed live; the rest are do not execute (they recreate already-deployed objects).

1. resources/architecture-slide.png (or .html full-screen) — frame: SQL 2025 + Foundry Local, one edge device, no cloud
2. demo/01_clinical_evidence_building_blocks.sql — building blocks (EXTERNAL MODEL, VECTOR(1024), AI_GENERATE_EMBEDDINGS, VECTOR INDEX) — walk through
3. demo/02_security_and_prereqs.sql — four trust layers (sp_configure, HTTPS/TLS, cert, least-priv) — walk through
4. demo/03_vector_search_proc.sql — vector search proc — walk through
5. demo/04_call_vector_search.sql — EXECUTE LIVE — clinical question → ranked evidence (~400ms)
6. demo/05_ai_agent_care_plan_proc.sql — RAG agent proc — walk through
7. demo/06_call_ai_agent_care_plan.sql — EXECUTE LIVE — Art Vandelay (hypertensive emergency) → care plan + ledger (~60-85s)
8. demo/ssms_copilot_prompt.md (optional) · resources/tablet-demo.html (optional visual closer)

All procs are created by build/, not demo/. demo/03 and demo/05 are read-only teaching copies of build/07 (content.SearchChunksByPrompt_Vector) and build/09 (care.usp_ai_agent_care_plan) — same object names via CREATE OR ALTER, so running them would overwrite the deployed procs with reformatted/condensed variants, not create duplicates. Only demo/04 and demo/06 execute; demo/01/02 are narration-only.

Auto-run the live scripts: demo/Run-LiveDemo.ps1 runs only demo/04 + demo/06 in order (skips the narration/teaching copies), with per-script timing; -Pause waits for Enter before each for stage pacing.

Regenerate architecture-slide.png with the html-slide-to-png skill (Edge classic headless, --window-size=1280,720 --force-device-scale-factor=2).

Seed Data

• 15 clinical documents, 38 text chunks (SOPs, symptom playbooks, escalation policies, allergy cross-reference)
• 25 clinic locations, 5 patients with vitals and care plans (added by 09_add_clinics.sql)

Prerequisites (before running scripts)

1. Run build/Setup-FoundryLocal.ps1 — provisions the Foundry daemon + both models, starts Caddy on 8444/8445, HTTPS sanity-checks both ports, and imports Caddy's root CA + restarts SQL Server (one UAC prompt; skipped when already trusted, or with -SkipCertTrust).
2. sp_invoke_external_rest_endpoint enabled — handled by 00_prereqs.sql.

Troubleshooting

• AI_GENERATE_EMBEDDINGS TLS error → re-run build/Setup-FoundryLocal.ps1 (auto-imports Caddy's root CA + restarts SQL), or import %APPDATA%\Caddy\pki\authorities\local\root.crt via certlm.msc and restart SQL
• 04_ai_model.sql fails its embedding sanity check → the Foundry daemon (127.0.0.1:5273) or Caddy (https://localhost:8444) is down, or the cert isn't trusted — re-run Setup-FoundryLocal.ps1
• Care plan times out (>230s) → reduce chunk preview length, lower max_tokens
• Phi-4 not found → run foundry model load phi-4, verify the daemon on port 5273 (foundry server status)
• Vector search returns fewer rows than expected → proc already over-fetches 3x
• Embedding generation fails → check Caddy running + both models loaded (foundry model load)
• foundry model run instead of load → run is interactive chat; use load

Demo Narrative

Outpatient AI assistant: doctor enters symptoms on a clinic tablet, SQL Server 2025 searches clinical evidence via vector search, assembles a grounded prompt, calls a local SLM (phi-4), and generates a structured care plan — all on the laptop, no cloud, no API keys.