agentleFS
MicrosoftKnown publishermicrosoft/sqlaiinaction/.github/skills/zavahospital/SKILL.md

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

  • Other versions1 project uses a version of this.

    Word for word · 0

    None found.

    Edited · 1

    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.

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
  1. Very specific:

    All deploy scripts are in build/sql/.

    line 85
  2. Very specific:

    Verify NIM endpoints respond: .\test-embedding.ps1, .\test-chat.ps1 in build/nvidianim/

    line 117

≈ “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.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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 its README.md
  • demo/ — teaching-ordered walkthrough scripts + Run-LiveDemo.ps1
  • resources/ — 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-nvidianim in rg-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.sql sets ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;, mandatory for CREATE VECTOR INDEX and VECTOR_SEARCH() in SQL Server 2025
  • Embedding model name MUST use -query suffix: nvidia/nv-embedqa-e5-v5-query. The -query suffix bakes in input_type=query server-side — AI_GENERATE_EMBEDDINGS cannot send input_type.
  • Vector dimensions are 1024 (not 1536)
  • sp_invoke_external_rest_endpoint @timeout max 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 (enables external rest endpoint) and 01_database.sql (creates the database) run against master. All other scripts run against zavahospital.
  • Scripts 07_genembeddings.sql and 11_embeddingtable.sql call 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.DoctorNotes uses LEDGER = 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.VitalsSnapshots uses DATA_COMPRESSION = ROW

Stored Procedures

ProcPurpose
clinical.usp_GetCurrentPatientVitalsLatest vitals per patient (open encounters)
clinical.usp_GetPatientSymptomsSymptoms for patient/encounter
clinical.usp_GetDoctorNotesDoctor notes with filtering
clinical.usp_CreateOrderCreate clinical order
clinical.usp_findsimilarcasesVector search for similar encounters (3x over-fetch)
clinical.usp_clinical_recommendationRAG: vector search + NIM chat → structured JSON recommendation
clinical.usp_search_doctor_notesVector search on individual doctor notes

Embedding Tables

TableGranularitySource
clinical.EncounterVectorsOne vector per encounterComposite narrative (reason + symptoms + orders + vitals + notes + ward)
clinical.DoctorNotesEmbeddingsOne vector per doctor noteIndividual 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:

  1. create-aks-cluster.ps1 — AKS + GPU node pool (2x NC4as_T4_v3), NVIDIA device plugin, kubectl
  2. deploy-nim.ps1 — NIM chat + embedding containers + ingress (requires $env:NGC_API_KEY)
  3. 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)
  4. 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 with Join-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 -Test to verify both endpoints over https://nim-aks.local (no kubectl needed). Undo on that machine with Remove-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 -Subscription ingress-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 via Grant-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 from nvcr.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 in create-aks-cluster.ps1)
  • Teardown: cleanup.ps1 deletes the cluster/resource group; Remove-NimClient.ps1 reverses 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 over https://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 gate

14_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)

  1. NIM on AKS deployed and pods running (see build/nvidianim/README.md)
  2. Hosts file: <aks-ingress-ip> nim-aks.local (get the IP from kubectl get ingress -n nim)
  3. NIM TLS cert imported into Local Machine Trusted Root CAs
  4. SQL Server restarted after cert import
  5. sp_invoke_external_rest_endpoint enabled (sp_configure)
  6. Verify NIM endpoints respond: .\test-embedding.ps1, .\test-chat.ps1 in build/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 zavahospital before running any demo script — the demo scripts carry no USE zavahospital; (context comes from the dropdown, or -d zavahospital via Run-LiveDemo.ps1/sqlcmd).
  1. resources/architecture-slide.html/.png — opening slide (AMD / HPE sponsor variants alongside)
  2. demo/01_clinical_recommendation_building_blocks.sql — narrate building blocks — do not execute
  3. demo/02_clinical_recommendation_proc.sql — walk RAG proc body — do not execute (CREATE OR ALTER copy of build/sql/10)
  4. demo/03_call_clinical_recommendation.sql — EXECUTE LIVE — RAG recommendation via NIM chat
  5. demo/04_doctor_notes_vector_search.sql — narrate note embedding/search — do not execute (copy of build/sql/11-13)
  6. demo/05_call_search_doctor_notes.sql — EXECUTE LIVE — vector search over doctor notes
  7. demo/ssms_copilot_prompt.md — closer via SSMS GitHub Copilot
  • All procs/indexes are created by build/, not demo/. demo/01/04 are narration; demo/02 is a CREATE OR ALTER teaching copy of build/sql/10_clinical_recommendation.sql that would OVERWRITE the deployed proc. Only demo/03 and demo/05 execute — both pure EXEC.
  • Auto-run the live scripts: demo/Run-LiveDemo.ps1 runs only demo/03 + demo/05 in order (-Pause for stage pacing).
  • Regenerate architecture-slide.png with 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 chart
  • resources/clinical-search-app.html — clinical similarity search UI

Troubleshooting

  • AI_GENERATE_EMBEDDINGS TLS error → import build\nvidianim\k8s\tls.cer to Trusted Root CAs, restart SQL
  • Vector search returns fewer rows than expected → increase @SearchTopN (procs already over-fetch 3x)
  • nim-aks.local unreachable → 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 via 02_schema.sql first
  • 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.

Download now (ZIP)

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
What did you use it for? e.g. Reviewing pull requests in a Rails app

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.