hyperscaleai
Skill4 starsChanged 4 months agoMITView on GitHub
ZavaFin Act 3 "Add AI" demo — Azure SQL Hyperscale DiskANN vector search + in-database Phi-4 loan scoring, all in T-SQL. Use when the user wants to build/provision the demo (Provision-Hyperscale, Deploy-Base, build SQL 00–04), run the demo live (Run-LiveDemo.ps1 — e.g. "build demo", "run demo live", "run the hyperscale demo"), tear it down, or work anywhere under hyperscale/.
In this repository6 other files it gives its agents.
- demo-runbookSkill
- html-slide-to-pngSkill
- live-site-sqlSkill
- zavacliniqSkill
- zavahospitalSkill
- zavalivesite-demoSkill
The file
Files
HyperscaleAI — Azure SQL Hyperscale: vector search + AI loan scoring
ZavaFin's lending database (ZavaLendingDB, aka zavalending) runs on Azure SQL
Hyperscale. This demo adds two AI capabilities natively inside the database — no data
leaves the boundary:
- Narrative Search — semantic vector search over loan narratives (DiskANN, cosine).
- AI Loan Scoring — score a pending application against similar historical loans, with a Phi-4 risk narrative, written to an auditable ledger.
Everything lives under hyperscale/:
build/sql/basedeploy/— provision the Hyperscale DB + load base schema/seed (this makes the demo self-contained; originally the DB came from an "Act 1 migration")build/sql/— numbered AI deploy scripts (00–04)build/loan-scoring-agent/,build/APIM/— optional Part 2 (Foundry agent) + optional AI gatewaydemo/— on-stage runner (Run-LiveDemo.ps1) + teaching-ordered T-SQL (sql/)loan-platform-internal/— internal ops console (openindex.html)
Two common requests
- "build demo" / provision → stand up the DB and deploy all objects:
basedeploy→ build SQL00–04. See Build the demo below. - "run demo live" → execute the 4 live T-SQL steps in order via
demo/Run-LiveDemo.ps1. See Run the demo live below.
Architecture
- Azure SQL Hyperscale, server
zavafinsqlserver.database.windows.net, databasezavalending - Analytics named replica = a database
zavalending_NamedReplicaon the same server (optional; used for read isolation in demo step 2 — falls back to the primary) - Embeddings:
text-embedding-3-large(3072-dim, float16) viaCREATE EXTERNAL MODEL+AI_GENERATE_EMBEDDINGS - Chat/scoring:
Phi-4viasp_invoke_external_rest_endpoint(direct to Azure AI by default;@UseGateway=1routes through the optional APIM gateway) - Index: DiskANN
CREATE VECTOR INDEX(cosine) — V3 updateable (DML-friendly) - Audit:
LoanDecisionAuditledger table (LEDGER = ON (APPEND_ONLY = ON)) +HASHBYTES
Flow
NL prompt → AI_GENERATE_EMBEDDINGS → VECTOR_SEARCH (DiskANN, replica) → JSON of similar loans
→ sp_invoke_external_rest_endpoint (Phi-4) → risk narrative → INSERT decision + audit hashCritical facts (verified live 2026-07-18)
- Tooling: go-sqlcmd is REQUIRED. The classic ODBC
sqlcmddoes NOT support--authentication-method. Install go-sqlcmd:winget install sqlcmd(Microsoft.Sqlcmd →C:\Program Files\sqlcmd\sqlcmd.exe). The PowerShell runners auto-detect it (Resolve-GoSqlcmd). - Entra auth (default): use
--authentication-method=ActiveDirectoryDefaultand DO NOT also pass-G— they are mutually exclusive in go-sqlcmd. ActiveDirectoryDefault reuses youraz logintoken silently (you must be the server's Entra admin;Provision-Hyperscale.ps1sets that automatically). - No
-f 65001— that's ODBC-only. go-sqlcmd reads UTF-8 by default. Valid flags:-N -I -b -l 30 -i -S -d -U -P. sp_invoke_external_rest_endpoint@timeoutmax is 230s (product limit; default 30). The demo calls Phi-4 with a lower@timeout, so aMsg 31629 … exceeded the timeout valueon step 4 most often means the Phi-4 deployment capacity is too low (capacity1≈ 1K TPM throttles the multi-thousand-token scoring payload → 429 → retries → timeout). Deploy Phi-4 at--sku-capacity 50+ (GlobalStandard). A killed terminal or cold start can also cause it — re-run.- Placeholder secrets must be filled before build (
build/sql/01-embeddings-setup.sql): master-key password, credentialapi-key, and the Azure AI endpoint URL (also in04-loan-scoring.sql). They ship as<your-...>placeholders. - Requires an Azure AI resource with two deployments:
text-embedding-3-large+Phi-4. Nothing provisions this — it's a manual Azure prerequisite. - DiskANN needs ≥ 100 vectors — the seed narrates exactly
LoanId1–100 (zero headroom). - Pending demo application is
ApplicationId 1(Art Vandelay, SmallBusiness $150k) — seeded bybasedeploy, reset bydemo/sql/00-reset.sql. - Legacy
VECTOR_SEARCH(... TOP_N=…)is gone — the V3 index rejects it (Msg 42274); the demo usesTOP (N) WITH APPROXIMATEonly.
Build the demo (from nothing to demo-ready)
Base deploy scripts are in build/sql/basedeploy/.
Run az login first.
$bd = 'c:\sqlaiinaction\hyperscale\build\sql\basedeploy'
$srv = 'zavafinsqlserver.database.windows.net'
$adAuth = '--authentication-method=ActiveDirectoryDefault'
# 1. Provision the Hyperscale server + DB (idempotent; sets you as Entra admin; -CreateNamedReplica optional)
& "$bd\Provision-Hyperscale.ps1" -ResourceGroup zavafinrg -Server zavafinsqlserver -CreateNamedReplica
# 2. Base schema + seed (4 tables + ledger; 1,000 applicants/loans; Vandelay = ApplicationId 1)
& "$bd\Deploy-Base.ps1" -Server $srv -Database zavalending
# 3. Deploy the AI objects in order (go-sqlcmd; Entra by default). FILL the placeholder
# secrets in 01-embeddings-setup.sql first.
$sql = 'c:\sqlaiinaction\hyperscale\build\sql'
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\00-add-loan-narratives.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\01-embeddings-setup.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\02-vector-index.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\03-hybrid-search-procedure.sql"
sqlcmd -S $srv -d zavalending $adAuth -N -I -b -i "$sql\04-loan-scoring.sql"Build order: 00 narratives + full-text → 01 external model + embeddings → 02 DiskANN
index → 03 usp_HybridLoanSearch (Narrative Search) → 04 usp_ScoreLoanApplication +
LoanDecisionAudit (AI Loan Scoring).
Part 2 (optional) — expose
usp_ScoreLoanApplicationas an MCP tool via DAB + a Foundry agent:build/loan-scoring-agent/, driven perdemo/loan-scoring-agent/foundry-agent-setup.md.
Run the demo live
The demo/ folder executes already-deployed objects (it does NOT
build anything). Drive all four steps with the runner:
$demo = 'c:\sqlaiinaction\hyperscale\demo'
# Entra ID via your az login; step 2 on the named replica
& "$demo\Run-LiveDemo.ps1" -ReplicaDatabase zavalending_NamedReplica
# On-stage pacing (Enter before each step)
& "$demo\Run-LiveDemo.ps1" -ReplicaDatabase zavalending_NamedReplica -PauseThe 4 live steps (demo/sql/), in order:
| # | Script | Target | Shows |
| 1 | 00-reset.sql | primary | reset demo state (rows >100, ApplicationId 1 → Pending) |
| 2 | 03-vector-search-replica.sql | replica (or primary) | Narrative Search (ranked similar loans) |
| 3 | 04-dml-insert-search.sql | primary | insert 3 loans, instantly searchable (no rebuild) |
| 4 | 07-execute-scoring.sql | primary | AI Loan Scoring → Phi-4 → auditable decision |
Runner params: -Server (default zavafinsqlserver.database.windows.net), -Database
(default zavalending), -ReplicaDatabase (step 2 target; else primary), -ReplicaServer
(only if the replica is on a different server), -AuthMethod
{ActiveDirectoryDefault(default) | ActiveDirectoryInteractive | ActiveDirectoryDeviceCode
| SqlPassword}, -SqlUser/-SqlPassword (SqlPassword only), -Pause.
Expected step-4 result: ApplicationId 1 (Art Vandelay, SmallBusiness $150k) → Approved,
RiskScore ~15 (Low), 10 similar loans, model Phi-4, a few seconds.
Teardown
build/sql/basedeploy/Teardown-Hyperscale.ps1
— confirmation-gated. Default deletes the DB + named replica; -RemoveServer /
-RemoveResourceGroup widen scope; -Force skips the prompt.
& 'c:\sqlaiinaction\hyperscale\build\sql\basedeploy\Teardown-Hyperscale.ps1' -ResourceGroup zavafinrg -Server zavafinsqlserverTroubleshooting
Sqlcmd: '…': Unknown Optionon-f/--authentication-method→ you hit ODBC sqlcmd. Install go-sqlcmd (winget install sqlcmd); the runners auto-detect it.The -G and the --authentication-method options are mutually exclusive→ drop-G; keep only--authentication-method=….Msg 31629 … exceeded the timeout value(step 4) → Phi-4 deployment capacity too low (capacity 1 ≈ 1K TPM throttles). Raise it:az cognitiveservices account deployment create -g <rg> -n <account> --deployment-name Phi-4 --model-name Phi-4 --model-version 7 --model-format Microsoft --sku-name GlobalStandard --sku-capacity 50. A killed terminal / cold start can also cause it — re-run.- Login/auth failures → confirm
az loginand that you're the server's Entra admin (Provision-Hyperscale.ps1sets it; oraz sql server ad-admin create). Msg 42274 … does not support explicit TOP_N→ old legacy syntax on a V3 index; useTOP (N) WITH APPROXIMATE(the current procs already do).
Related
- Full narrative + folder map:
hyperscale/README.md - Build detail + prerequisites:
hyperscale/build/README.md - Base deploy + provisioning:
hyperscale/build/sql/basedeploy/README.md - Live demo steps:
hyperscale/demo/README.md
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.

