zavacliniq
Skill4 starsChanged 4 months agoMITView on GitHub
ZavaClinIQ – AI-powered outpatient clinical assistant using SQL Server 2025 vector search + Foundry Local (phi-4, qwen3-embedding). Use when: deploying zavacliniq scripts, building clinical evidence embeddings, running vector search procs, generating AI care plans via RAG, working in sqlserver2025/zavacliniq/.
In this repository6 other files it gives its agents.
- demo-runbookSkill
- html-slide-to-pngSkill
- hyperscaleaiSkill
- live-site-sqlSkill
- zavahospitalSkill
- zavalivesite-demoSkill
The file
We split this skill into the rules it gives an agent
4 rules. About 0 are common, 1 is distinctive, and 3 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈1
Distinctive · estimate
This author's own choices. Worth a look.
3
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 15
sqlcmd: run the T-SQL scripts with sqlcmd -E -I (Windows auth.
- Very specific: line 54
All setup scripts are in the build/ subfolder (sqlserver2025/zavacliniq/build/).
- Distinctive: line 56
One-shot deploy (preferred): run the build set in order, stopping on the first error:
- Very specific: line 122
Run build/Setup-FoundryLocal.ps1 — provisions the Foundry daemon + both models, starts Caddy on 8444/8445, HTTPS sanity-checks both ports, …
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
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.ps1verifies/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.ps1reverses 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.ps1step 01 drops + recreateszavacliniqon every run. - sqlcmd: run the T-SQL scripts with
sqlcmd -E -I(Windows auth;-IsetsQUOTED_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.6bandphi-4are in the public catalog. Install withwinget 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_endpointmax timeout is 230 seconds — prompts must stay concise- Phi-4 model ID is variant-specific per machine — proc auto-detects via
/v1/modelsendpoint foundry model load(NOTfoundry model run) —runis interactive chat,loadserves the REST API- Daemon port is pinned via
foundry config set port 5273; start withfoundry server start(0.10.x; the oldfoundry servicecommands are gone) 00_prereqs.sql(enablesexternal rest endpoint) and01_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.CarePlanLedgerusesLEDGER = 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.ps1The 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).
resources/architecture-slide.png(or.htmlfull-screen) — frame: SQL 2025 + Foundry Local, one edge device, no clouddemo/01_clinical_evidence_building_blocks.sql— building blocks (EXTERNAL MODEL, VECTOR(1024), AI_GENERATE_EMBEDDINGS, VECTOR INDEX) — walk throughdemo/02_security_and_prereqs.sql— four trust layers (sp_configure, HTTPS/TLS, cert, least-priv) — walk throughdemo/03_vector_search_proc.sql— vector search proc — walk throughdemo/04_call_vector_search.sql— EXECUTE LIVE — clinical question → ranked evidence (~400ms)demo/05_ai_agent_care_plan_proc.sql— RAG agent proc — walk throughdemo/06_call_ai_agent_care_plan.sql— EXECUTE LIVE — Art Vandelay (hypertensive emergency) → care plan + ledger (~60-85s)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)
- 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). sp_invoke_external_rest_endpointenabled — handled by00_prereqs.sql.
Troubleshooting
AI_GENERATE_EMBEDDINGSTLS error → re-runbuild/Setup-FoundryLocal.ps1(auto-imports Caddy's root CA + restarts SQL), or import%APPDATA%\Caddy\pki\authorities\local\root.crtviacertlm.mscand restart SQL04_ai_model.sqlfails 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-runSetup-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 port5273(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 runinstead ofload→runis interactive chat; useload
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.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

