zavahospital
Skill4 starsChanged 4 months agoMITView on GitHub
ZavaHospital – AI-powered inpatient clinical assistant using SQL Server 2025 vector search + NVIDIA NIM on AKS (GPU). Use when: deploying zavahospital build scripts, building encounter/doctor-note embeddings, running vector search or clinical recommendation procs, presenting the on-stage demo, or working in sqlserver2025/zavahospital/.
- Reads credentials
1 other project uses a version of this, 1 of them edited.
Adopted by
Most recently taken up by
- microsoft/bobsqledited, first seen Nov 8, 2026
Other versions1 project uses a version of this.
Word for word · 0
None found.
Edited · 1
- microsoft/bobsql★ 169% same
Wrote their own version · 0
None found.
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository6 other files it gives its agents.
- demo-runbookSkill
- html-slide-to-pngSkill
- hyperscaleaiSkill
- live-site-sqlSkill
- zavacliniqSkill
- zavalivesite-demoSkill
The file
We split this skill into the rules it gives an agent
2 rules. About 0 are common, 0 are distinctive, and 2 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈0
Distinctive · estimate
This author's own choices. Worth a look.
2
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 85
All deploy scripts are in build/sql/.
- Very specific: line 117
Verify NIM endpoints respond: .\test-embedding.ps1, .\test-chat.ps1 in build/nvidianim/
≈ “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
ZavaHospital – SQL Server 2025 + NVIDIA NIM on AKS
Single skill covering build (deploy the database) and demo (present on stage).
Everything lives under sqlserver2025/zavahospital/:
build/sql/— numbered T-SQL deploy scripts (00_prereqs.sql…14_verify.sql)build/nvidianim/— NIM-on-AKS deployment (must stand up before any SQL) — see itsREADME.mddemo/— teaching-ordered walkthrough scripts +Run-LiveDemo.ps1resources/— architecture slides + UI demo HTML/JS/CSS
Architecture
- SQL Server 2025 on localhost, Windows auth, database
zavahospital - NVIDIA NIM embeddings:
nvidia/nv-embedqa-e5-v5-query(1024 dims, T4 GPU on AKS) - NVIDIA NIM chat:
meta/llama-3.2-3b-instruct(T4 GPU on AKS) - AKS cluster:
aks-nvidianiminrg-nvidianim-westus2, 2x NC4as_T4_v3 GPU nodes + 2x DS2_v2 system nodes (two system nodes are required so the NGINX ingress controller fits alongside the platform add-ons — one 2-vCPU node leaves the controller Pending) - TLS: Self-signed cert (CN=nim-aks.local) via AKS Web App Routing (managed NGINX ingress)
- Hostname:
nim-aks.local→<aks-ingress-ip>via Windows hosts file - Content-Type fix: NGINX annotation
proxy_set_header Content-Type "application/json"
Flow
SQL Server 2025 → HTTPS → AKS Ingress (nim-aks.local) → NIM pods- Embeddings:
AI_GENERATE_EMBEDDINGS(@prompt USE MODEL NIMEmbeddingModel)→ 1024-dim vector - Chat:
sp_invoke_external_rest_endpoint→https://nim-aks.local/v1/chat/completions - Vector search:
VECTOR_SEARCH()with DiskANN index (cosine metric) - Recommendation: vector search → gather vitals/symptoms/allergies/orders → NIM chat → structured JSON
Critical Facts
- PREVIEW_FEATURES = ON is REQUIRED —
01_database.sqlsetsALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;, mandatory forCREATE VECTOR INDEXandVECTOR_SEARCH()in SQL Server 2025 - Embedding model name MUST use
-querysuffix:nvidia/nv-embedqa-e5-v5-query. The-querysuffix bakes ininput_type=queryserver-side —AI_GENERATE_EMBEDDINGScannot sendinput_type. - Vector dimensions are 1024 (not 1536)
sp_invoke_external_rest_endpoint@timeoutmax is 230 seconds (product limit; default 30) — this demo calls NIM with a shorter@timeout, so keep prompts concise- NIM rejects
application/json;charset=utf-8— the NGINX annotation strips the charset suffix - Chat model (llama-3.2-3b) is small — system prompt MUST include grounding facts for quality answers
00_prereqs.sql(enablesexternal rest endpoint) and01_database.sql(creates the database) run against master. All other scripts run against zavahospital.- Scripts
07_genembeddings.sqland11_embeddingtable.sqlcall NIM per row — slower than the rest - Post-filter compensation:
VECTOR_SEARCH()applies JOINs/WHERE after the ANN scan, so procs over-fetch 3x
Database Design
- Schemas:
ref(reference),core(patients/encounters),clinical(vitals/notes/orders/alerts),sec(RLS) - Ledger:
clinical.DoctorNotesusesLEDGER = ON (APPEND_ONLY = ON)for audit immutability - RLS: Row-Level Security predicates scope data by building/ward via
SESSION_CONTEXT - ADR:
ACCELERATED_DATABASE_RECOVERY = ON - Optimized Locking:
OPTIMIZED_LOCKING = ON - Data compression:
clinical.VitalsSnapshotsusesDATA_COMPRESSION = ROW
Stored Procedures
| Proc | Purpose |
clinical.usp_GetCurrentPatientVitals | Latest vitals per patient (open encounters) |
clinical.usp_GetPatientSymptoms | Symptoms for patient/encounter |
clinical.usp_GetDoctorNotes | Doctor notes with filtering |
clinical.usp_CreateOrder | Create clinical order |
clinical.usp_findsimilarcases | Vector search for similar encounters (3x over-fetch) |
clinical.usp_clinical_recommendation | RAG: vector search + NIM chat → structured JSON recommendation |
clinical.usp_search_doctor_notes | Vector search on individual doctor notes |
Embedding Tables
| Table | Granularity | Source |
clinical.EncounterVectors | One vector per encounter | Composite narrative (reason + symptoms + orders + vitals + notes + ward) |
clinical.DoctorNotesEmbeddings | One vector per doctor note | Individual NoteText from clinical.DoctorNotes |
NIM on AKS (stand up FIRST, before any SQL)
Full deployment scripts + detail live in build/nvidianim/ (see that folder's README.md). One-shot: Setup-NimOnAks.ps1 -Subscription <guid> ($env:NGC_API_KEY set) chains steps 1-4, waiting on kubectl rollout status for both pods before switching hostname; -Test verifies endpoints, -SkipClusterCreate reuses a cluster. It runs no SQL. Individual steps:
create-aks-cluster.ps1— AKS + GPU node pool (2x NC4as_T4_v3), NVIDIA device plugin, kubectldeploy-nim.ps1— NIM chat + embedding containers + ingress (requires$env:NGC_API_KEY)switch-hostname.ps1 -NewHostname nim-aks.local— TLS cert, K8s secrets, ingress, hosts file, Windows Trusted Root (infra only — runs no SQL; the build scripts own the model URL)test-embedding.ps1,test-chat.ps1— verify both endpoints respond
- Additional demo machine (AKS already exists): do NOT re-run
Setup-NimOnAks.ps1/switch-hostname.ps1(they regenerate the TLS cert and break the first machine). Attach withJoin-NimClient.ps1 -Subscription <guid> -RestartSql— auto-detects the ingress IP and live-pulls the served cert from the endpoint (no saved state / cert file); add-IngressIP <ip>if this machine has no kubectl/az context. Only sets hosts entry + Trusted Root cert + SQL restart, no cluster changes. Add-Testto verify both endpoints overhttps://nim-aks.local(no kubectl needed). Undo on that machine withRemove-NimClient.ps1. - Permissions to run Join (colleague): local Administrator on their machine is mandatory (hosts/cert/SQL-service via UAC); no SQL permission for Join itself. Azure RBAC needed only for
-Subscriptioningress-IP auto-detect: cluster uses local K8s accounts (no Entra/AAD), so the single role is Azure Kubernetes Service Cluster User Role at the cluster scope — grant viaGrant-NimClientAccess.ps1 -PrincipalId <upn-or-oid> -Subscription <guid>. Skip all Azure access by passing-IngressIP <ip>(then only local admin + outbound TCP 443). - NGC API key required (
$env:NGC_API_KEY); images fromnvcr.io/nim/ - Llama 3.1 8B OOMs on T4 (16GB); Llama 3.2 3B fits but needs grounded system prompt
- Same containers + K8s manifests deploy on Azure Local for the edge story (demo uses AKS in Azure to avoid standing up an Azure Local machine)
- New clusters self-patch host-kernel CVEs via
--node-os-upgrade-channel NodeImage(set increate-aks-cluster.ps1) - Teardown:
cleanup.ps1deletes the cluster/resource group;Remove-NimClient.ps1reverses the client join per machine (both needed for a from-scratch test) - From-scratch client test (same machine, no cluster teardown): run from
build/nvidianim, each script self-elevates (UAC). If kubectl isn't on PATH first run$env:PATH += ";$HOME\.azure-kubectl;$HOME\.azure-kubelogin"(or pass-IngressIP <ip>). Then:.\Remove-NimClient.ps1 -RestartSql→.\Join-NimClient.ps1 -RestartSql -Test. A passing-Test(embeddings 1024-dim + chat overhttps://nim-aks.local) proves hosts mapping + freshly imported cert are trusted.
Script Execution Order (build)
All deploy scripts are in build/sql/. Run in order, stopping on the first error:
$scriptDir = 'c:\sqlaiinaction\sqlserver2025\zavahospital\build\sql'
# -I sets QUOTED_IDENTIFIER ON (sqlcmd defaults OFF) — required for CREATE VECTOR INDEX.
# Against master (server config + create database + db-scoped config):
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 zavahospital (all remaining):
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\02_schema.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\03_seeding.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\04_procs.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\05_ai_model.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\06_embedding_table.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\07_genembeddings.sql" # calls NIM
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\08_create_vector_index.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\09_find_similar_cases.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\10_clinical_recommendation.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\11_embeddingtable.sql" # calls NIM
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\12_create_notes_vector_index.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\13_search_doctor_notes.sql"
sqlcmd -S localhost -d zavahospital -E -I -i "$scriptDir\14_verify.sql" # read-only gate14_verify.sql is the final read-only gate — it asserts every object/seed set and RAISERRORs (sev 16) so -b stops on failure. The live "call the proc" demos live in demo/03 and demo/05.
Prerequisites (before running SQL)
- NIM on AKS deployed and pods running (see
build/nvidianim/README.md) - Hosts file:
<aks-ingress-ip> nim-aks.local(get the IP fromkubectl get ingress -n nim) - NIM TLS cert imported into Local Machine Trusted Root CAs
- SQL Server restarted after cert import
sp_invoke_external_rest_endpointenabled (sp_configure)- Verify NIM endpoints respond:
.\test-embedding.ps1,.\test-chat.ps1inbuild/nvidianim/
Demo Walkthrough (on-stage)
The demo/ folder is a teaching-ordered view of what build/ already deployed — not a second deployment. Presentation assets are in resources/.
- SSMS: set the database dropdown to
zavahospitalbefore running any demo script — the demo scripts carry noUSE zavahospital;(context comes from the dropdown, or-d zavahospitalviaRun-LiveDemo.ps1/sqlcmd).
resources/architecture-slide.html/.png— opening slide (AMD / HPE sponsor variants alongside)demo/01_clinical_recommendation_building_blocks.sql— narrate building blocks — do not executedemo/02_clinical_recommendation_proc.sql— walk RAG proc body — do not execute (CREATE OR ALTERcopy ofbuild/sql/10)demo/03_call_clinical_recommendation.sql— EXECUTE LIVE — RAG recommendation via NIM chatdemo/04_doctor_notes_vector_search.sql— narrate note embedding/search — do not execute (copy ofbuild/sql/11-13)demo/05_call_search_doctor_notes.sql— EXECUTE LIVE — vector search over doctor notesdemo/ssms_copilot_prompt.md— closer via SSMS GitHub Copilot
- All procs/indexes are created by
build/, notdemo/.demo/01/04are narration;demo/02is aCREATE OR ALTERteaching copy ofbuild/sql/10_clinical_recommendation.sqlthat would OVERWRITE the deployed proc. Onlydemo/03anddemo/05execute — both pureEXEC. - Auto-run the live scripts:
demo/Run-LiveDemo.ps1runs onlydemo/03+demo/05in order (-Pausefor stage pacing). - Regenerate
architecture-slide.pngwith the html-slide-to-png skill (Edge classic headless).
UI Demos (optional visual closers)
Self-contained static HTML in resources/ — open in a browser, no server/DB. They fake live data client-side:
resources/digital-chart.html— bedside digital patient chartresources/clinical-search-app.html— clinical similarity search UI
Troubleshooting
AI_GENERATE_EMBEDDINGSTLS error → importbuild\nvidianim\k8s\tls.certo Trusted Root CAs, restart SQL- Vector search returns fewer rows than expected → increase
@SearchTopN(procs already over-fetch 3x) nim-aks.localunreachable → check hosts file, AKS ingress IP, pods running;build\nvidianim\switch-hostname.ps1- EXTERNAL MODEL fails → use
API_FORMAT = 'OpenAI', URL must end in/v1/embeddings - NIM pod OOM → use llama-3.2-3b (not 8B) for chat on T4
- Chat hallucinations → add grounding facts to system prompt (3B model needs explicit context)
03_seeding.sql"already seeded" → script has guard clause; drop+recreate via02_schema.sqlfirst- Scripts 08/12 slow → these call NIM per row; normal for initial population
Demo Narrative
Edge AI pattern: hospital runs SQL Server 2025 + NIM on Azure Local in their data center. Patient data never leaves the building — zero cloud API calls. Same code, same containers, same T-SQL works in cloud AKS or on-premises Azure Local.
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.

