ahmed mohammedandClaude Opus 4.7
8b42a0931c
kb/ is its own wiki root: 34 pages across 7 categories, index.md catalog, log.md, AGENTS.md schema layer, and raw/ holding copies of README, the spec, the 13-task plan, the design master and the SDD ledger. Every page cross-references 3 to 6 others and all 107 wikilinks resolve except two [["list"]] artefacts inside JSON snippets in the copied plan. Kept separate from the repository root on purpose: its generated graph lands in kb/.ua/ instead of overwriting the code graph. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
113 lines
6.4 KiB
Markdown
113 lines
6.4 KiB
Markdown
# SQL Server qAI
|
||
|
||
Paste a SQL Server ShowPlan XML (or drop a `.sqlplan`), get a report with several ranked remediation options and
|
||
rewritten queries. Two models, one job: a **generative analyst model** proposes the options and writes the report, and
|
||
**Jev** (TypeSafe System One) scores every option and decides which one to run first. Jev never writes SQL; it returns
|
||
typed scores, a choice, and confidences. The report renders as OpenUI elements, not a markdown blob.
|
||
|
||
## Pipeline
|
||
|
||
1. `lib/digest.ts` parses the XML in code: top operators, missing indexes, warnings, waits, estimate-vs-actual row
|
||
errors, tables, statement text, subtree cost, parallelism. Raw XML never reaches a model.
|
||
2. The analyst model receives the digest JSON and proposes 2–4 genuinely different options (index, rewrite, statistics,
|
||
parallelism, schema), each with ordered action steps and an optional rewrite.
|
||
3. Jev answers four questions per option (`bottleneck_fit`, `semantic_safety`, `ease` `score`; `root_cause` `noul`) and
|
||
one cross-option call (`first_to_run` `choice`, `anything_worth_running` `noul`).
|
||
4. `lib/jev.ts` combines them with visible weights (0.40 / 0.30 / 0.15 / 0.15 — four numbers in that file, the only
|
||
tuning knob) and prints a verdict. Jev's pick leads only when its confidence clears 0.5; otherwise the report says
|
||
the order came from composite scoring.
|
||
5. The analyst streams the final report as OpenUI Lang; `<Renderer>` paints it as it arrives.
|
||
|
||
Getting the XML: SSMS → **Ctrl+M** (include actual execution plan) → run the query → right-click the Execution Plan tab
|
||
→ *Save As .sqlplan*. No SSMS: prefix the batch with `SET STATISTICS XML ON;` and copy the XML result cell.
|
||
|
||
## Run it
|
||
|
||
This repo runs as a plain Node process. `Dockerfile` and `docker-compose.yml` are ready, but the image has **not
|
||
been built or run on this machine** (Docker is not installed here); treat it as untested until
|
||
`bash scripts/smoke.sh --docker` says otherwise. Local work is `npm run dev`.
|
||
|
||
```bash
|
||
npm install
|
||
cp .env.example .env.local # then fill in APP_SECRET and DATA_DIR
|
||
npm run dev # http://localhost:3000
|
||
bash scripts/smoke.sh # healthz, login page, signed-out redirect, analyze needs a session
|
||
```
|
||
|
||
`.env.local`:
|
||
|
||
| key | why |
|
||
| --- | --- |
|
||
| `APP_SECRET` | ≥16 chars. Encrypts API keys at rest (AES-256-GCM). Changing it makes stored keys unreadable. |
|
||
| `DATA_DIR` | where `qai.db` (SQLite, `node:sqlite`) lives. Default `./data`. Docker uses `/data`. |
|
||
| `COOKIE_SECURE` | set to `1` only when the app is served over https without a proxy that sets `X-Forwarded-Proto`. The session cookie is `Secure` when that header says `https`; forcing it on plain http makes Safari refuse to store it. |
|
||
|
||
Model keys are **not** config. Each user types them in the app (first-run guide or `/settings`), so a second user
|
||
brings their own endpoint and their own spend. Blank key field = keep the stored key.
|
||
|
||
- Analyst: any OpenAI-compatible base URL + key + model, plus optional extra JSON params.
|
||
A local endpoint such as `http://192.168.88.131:8888/v1` works; use key `EMPTY` when the server has no auth.
|
||
For a reasoning model (Qwen3 on vLLM) put `{"chat_template_kwargs":{"enable_thinking":false}}` in **Extra params**:
|
||
with thinking on, the model spends the whole context budget reasoning and the report arrives empty.
|
||
Both model calls default to `max_tokens: 2048`; the same field overrides it.
|
||
- Jev: a TypeSafe API key from `console.typesafe.ai/keys`, model usually `jev-latest`.
|
||
|
||
## Tests
|
||
|
||
```bash
|
||
npm test # node --test on tests/*.test.ts, no test framework installed
|
||
npm run build # production build + type check
|
||
npm run e2e # builds first, then Playwright against the production server
|
||
```
|
||
|
||
The unit suites run offline: the analyst and Jev are exercised through injected `fetch`, so no key and no network.
|
||
`npm run e2e` is the opposite — `pree2e` builds, then Playwright drives `npm run start` (the production server,
|
||
not `next dev`: cookie flags and hydration differ, and that difference once hid a sign-out bug) against the real
|
||
models. It reads `JEV_API_KEY` from `.env.local`. Expect it to be slow; that is the models, not the harness.
|
||
|
||
Projects: `chromium` (everything except the session spec, reusing one signed-in session), `webkit` (Safari —
|
||
session survival only, because it is the one engine that rejects a `Secure` cookie over `http://localhost`),
|
||
and `mobile` (Pixel 7, layout only, so no model run is paid for twice).
|
||
|
||
`tests/digest.test.ts` skips unless a genuine plan exists at `fixtures/real.sqlplan`. Drop one there from SSMS and the
|
||
skip becomes the real check on ShowPlan attribute spellings.
|
||
|
||
## Git remote (Gitea)
|
||
|
||
Origin points at the self-hosted Gitea:
|
||
|
||
```bash
|
||
git remote -v # http://192.168.88.39:3000/ahmed/sql-server-query-ai.git
|
||
git push origin feat/sql-server-qai
|
||
```
|
||
|
||
The remote URL deliberately contains **no password**. Git prompts for the username (`ahmed`) and password the first
|
||
time, or you can keep them out of prompts:
|
||
|
||
```bash
|
||
git config credential.helper osxkeychain # remember them locally
|
||
git push origin feat/sql-server-qai
|
||
```
|
||
|
||
Never commit the password into `.git/config`, a remote URL, or this README — a stored credential in the repo is
|
||
readable by whoever you hand the repo to. If you want it non-interactive in CI, create a Gitea token
|
||
(Settings → Applications) and use `http://192.168.88.39:3000/ahmed/sql-server-query-ai.git` with the token as password.
|
||
|
||
## Layout
|
||
|
||
```
|
||
lib/digest.ts XML → bounded digest (the only shape models see)
|
||
lib/analyst.ts propose 2–4 options; stream the final OpenUI report
|
||
lib/jev.ts questions, weights, ranking, verdict, fallback
|
||
lib/report-ui.tsx OpenUI component library — the same module builds the prompt and renders the answer
|
||
lib/db.ts node:sqlite: users, sessions, settings, analyses
|
||
lib/vault.ts AES-256-GCM seal/open/mask for credentials
|
||
lib/auth.ts scrypt passwords, hashed session tokens (DB stores the SHA-256, never the token)
|
||
app/api/analyze SSE pipeline: digesting → proposing → judging → writing
|
||
tests/ offline unit suites tests-e2e/ Playwright
|
||
docs/superpowers/ spec + the 13-task implementation plan this was built from
|
||
```
|
||
|
||
Design: dark terminal console, JetBrains Mono for data, IBM Plex Sans for prose, one phosphor accent.
|
||
Print stylesheet flips to paper white and drops the rail, buttons and chrome — that is the Export to PDF path.
|