zavalivesite-demo

name

zavalivesite-demo

description

Build / prep / run / teardown lifecycle for the "SQL and AI for Developers in Action" (azuresqlcontainer) live-site demo — the Azure SQL preview container, one-command demo prep, idempotent container start, connectivity test, and teardown. Use when the user asks to prep/build/start/recreate/test/reset/teardown the live site (Zava On-Call Console) demo environment. NOT for incident triage — that is the live-site-sql skill.

ZavaLiveSite Demo — Build & Run Lifecycle Skill

Operational playbook for standing up, running, and tearing down the azuresqlcontainer demo. Owner: Bob Ward · SQL and AI for Developers in Action. This covers the environment lifecycle. For on-stage incident triage (mitigate/diagnose incident N) use the separate live-site-sql skill. For the presenter beats and the full prompt catalog see azuresqlcontainer/PRESENTER-RUNBOOK.md.

---

When to invoke

Trigger on any of:

• "prep the demo" / "prepare the demo environment"
• "build the demo" / "build from scratch"
• "start the SQL container" / "start the live site"
• "pull the latest image" / "recreate the container"
• "test SQL connectivity"
• "insert incident 5012" / "insert the incident"
• "run hybrid search for incident 5012" / "test the hybrid search"
• "reset for beat 2" / "reset the agent test state"
• "tear down / teardown the demo" / "remove the container"

Verified facts (this workspace: c:\sqlaiinaction)

Fact	Value
SQL container name	azsql-zavalivesite
Host port	14330 (→ container 1433)
Database	zavalivesitedb
Preview image	sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest
Preview registry auth	token docker login from a creds file (NOT az acr login)
Registry creds file	c:\temp\azuesqldeveloperpreviewcreds.txt (lines Username: / Password:; never committed)
sqlcmd	C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE
Engine reported	Edition SQL Azure, EngineEdition=5, ProductVersion 12.0.2000.8 (correct for the Azure SQL DB engine)
AI chat model	phi4-mini (3.8B) via Ollama+Caddy — CPU-only ~74s warm. Do NOT use phi4 (14B); it exceeds this demo's configured @timeout of 180s on CPU (the sp_invoke_external_rest_endpoint product max is 230s).
AI embed model	mxbai-embed-large (1024-dim)

Azure SQL Database engine specifics (EngineEdition 5) — do not relearn these

• All logins use sa. There is no sqladmin bootstrap — the engine won't create a sysadmin login the way box SQL Server does. sa is the server admin.
• 00_setup.sql must not run sp_configure / RECONFIGURE — the engine raises Msg 40510 'CONFIG not supported'. sp_invoke_external_rest_endpoint is on by default, so no server toggle is needed (unlike SQL Server 2025 box / MI).
• No GPU on this host — the chat model runs 100% CPU, which is why the model choice (phi4-mini, not phi4) matters.

Secret handling — non-negotiable

• Never route the SA password through the model, vscode_askQuestions, or a command argument. Have the user type it at a masked Read-Host prompt directly in the terminal.
• The SA password supplied to any deploy/prep step must match the password the running container was created with, or sa login fails.
• Non-secret values (image, container name, port) are known — pass them as parameters; only the SA password is interactive. Everything connects as sa; there is no separate sqladmin login (the Azure SQL Database engine doesn't support creating one the way box SQL Server does).

Agent-harness gotchas (why the "simple" way fails for the agent)

The presenter's real flow is trivial: set 3 env vars once, run the script. But across agent tool calls:

1. CWD resets between calls → always use absolute script paths, never .\.
2. Env vars don't persist across calls → don't rely on $env:SQLAI_* set in a prior call; pass values inline in the same command.
3. Output capture truncates → Tee-Object the run to a log file and read the log with read_file instead of trusting inline output.

Canonical commands (absolute paths; run exactly these)

Prep the demo — the one-command presenter flow (Build → deploy-prestage → Reset-ForBeat2 → Warmup-Ai → Verify-Build → Start-LiveSite). Prompts for the two passwords, then runs non-interactively and logs everything:

$sa=Read-Host 'SA password (match container)' -AsSecureString
& 'C:\sqlaiinaction\azuresqlcontainer\local\build\Prepare-DemoEnvironment.ps1' `
    -SqlImage 'sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest' `
    -SaCredential $sa `
    *>&1 | Tee-Object -FilePath 'C:\sqlaiinaction\azuresqlcontainer\local\build\prep-run.log'

Then read ...\build\prep-run.log to confirm each step. Add -SkipStart to stop before launching the AppHost.

Build from scratch — 12-step idempotent bootstrap:

& 'C:\sqlaiinaction\azuresqlcontainer\local\build\Build.ps1'

Start the container — idempotent: running → no-op, stopped → start, missing → create:

& 'C:\sqlaiinaction\azuresqlcontainer\local\build\container\Start-AzureSqlContainer.ps1'

• -Pull → remove container + local image, re-pull fresh, run (get an updated image).
• -Recreate (-Force) → remove container, rebuild from the local image (e.g. move ports).
• Creating prompts for the SA password (masked, in-terminal) unless $env:SQLAI_SA_PASSWORD is set.

Test connectivity — clean version/edition line (uses $env:SQLAI_SA_PASSWORD):

& 'C:\sqlaiinaction\azuresqlcontainer\local\diagnostics\Test-AzureSqlConnection.ps1'

Or a one-shot check without the script:

& sqlcmd -S localhost,14330 -U sa -P "$env:SQLAI_SA_PASSWORD" -C -b -h -1 -W -Q "SELECT CONVERT(varchar(30),SERVERPROPERTY('ProductVersion')) + ' | ' + CONVERT(varchar(40),SERVERPROPERTY('Edition'));"

Insert incident 5012 — runs the five-feature INSERT for Beat 2. If the password environment variable is unavailable, prompts for it securely in the terminal:

& 'C:\sqlaiinaction\azuresqlcontainer\local\demo\Insert-Incident.ps1'

Run hybrid search for incident 5012 — executes the Beat 3 hybrid-search query and prints its result set. The actual execution plan remains a manual SSMS step:

& 'C:\sqlaiinaction\azuresqlcontainer\local\demo\Run-HybridSearch.ps1'

Remove just the SQL container (clean redeploy):

& 'C:\sqlaiinaction\azuresqlcontainer\local\build\container\Remove-AzureSqlContainer.ps1'

-RemoveImage also drops the image; -PruneVolumes prunes volumes.

Full teardown (AppHost + dab-* + SQL container):

& 'C:\sqlaiinaction\azuresqlcontainer\local\build\Teardown-LiveSite.ps1'

App + reset helpers (run from local\):

• Start / Stop / Open app: demo\Start-LiveSite.ps1, demo\Stop-LiveSite.ps1, demo\Open-LiveSite.ps1
• Rewind to pre-Beat-1: demo\Reset-ForBeat2.ps1
• One-command reset + re-insert 5012: demo\Reset-AgentTestState.ps1
• Warm the models: demo\Warmup-Ai.ps1

Flags cheat-sheet — Start-AzureSqlContainer

Goal	Flag
Everyday start (leave a running container alone)	(none)
Pick up a newer preview image	-Pull (remove container + image → re-pull → run)
Rebuild from the local image (move port / reset)	-Recreate (alias -Force)

After prep — verify

Read prep-run.log for the step banners, then confirm end state:

• docker ps shows azsql-zavalivesite up and (if started) dab-* containers.
• Test-AzureSqlConnection.ps1 returns the Azure SQL version line.
• zavalivesitedb exists with a seeded IncidentArchive corpus.

Now run the demo (what to do next)

Prep ends with the app running. To present:

1. Open the console — browser to http://localhost:8080 (or say open the live site → Open-LiveSite.ps1). Incident #5012 shows its empty/placeholder state.
2. Walk the beats — the six-beat flow (schema → INSERT → hybrid search → agent mitigation → cloud) and the exact prompt/action per beat are in PRESENTER-RUNBOOK.md. Most beats are manual VS Code / MSSQL actions.
3. Beat 4 (the AI moment) — in Copilot Chat run Mitigate incident 5012 @live-site-sql. That hands off to the live-site-sql agent/skill (triage protocol), NOT this skill. Then refresh the browser to see the mitigation card populate.
4. Between rehearsals — reset the agent test state (Reset-AgentTestState.ps1) rewinds + re-inserts #5012 in one command. Or reset for beat 2 for the incident rewind only.
5. Quick agent-path smoke test (optional) — Test-AgentPath.ps1 -WithReset.

When done: tear down the demo (Teardown-LiveSite.ps1).