README

Self-hosted natural language → SQL report engine. Scans your database and your codebase once, builds a semantic catalog (schema cards, a human-approved join graph, a real-cell-value dictionary, a library of proven question→SQL examples), then turns plain Turkish or English questions into safe, read-only SQL reports using cheap LLMs — local Qwen via Ollama, DeepSeek, or any OpenAI-compatible endpoint.

Runs as a small sidecar service with its own embeddable UI, so it can be attached to any host app (even a legacy Laravel 5.5 / PHP 7 project) with a single <iframe>.

Question ──► retrieve (relevant tables + exact values + similar examples)
        ──► cheap LLM generates one SELECT
        ──► guard (parser gate, allowlist, forced LIMIT + timeout)
        ──► read-only execution ──► table / chart / SQL always visible
        └── on SQL error: feed the error back, retry (max 2)
Confirmed answers are stored as approved examples — accuracy compounds with use.

Why it works on your schema when benchmarks look scary

Text-to-SQL benchmarks measure unseen schemas. This engine works on one known schema that it documents during the scan, seeds with every hand-written JOIN already in your codebase, and improves with every confirmed answer. No fine-tuning, no per-row embeddings, no external vector database — vectors live as BLOBs in the catalog schema and are searched in-process.

Requirements

PHP 8.2+ with pdo_mysql, curl, mbstring (the sidecar's PHP — the host app's PHP version is irrelevant)
A MySQL server for the catalog (a dedicated schema; it rides your existing backups)
Any OpenAI-compatible LLM endpoint (Ollama, vLLM/SGLang, OpenRouter, DeepSeek, OpenAI…)
Optional but recommended: an embedding model (e.g. bge-m3 on Ollama). Without one, a Turkish-aware token-overlap fallback keeps everything working.

Quickstart A — modern Laravel app (10+): true plug-and-play

composer require hemreduru/vizier
php artisan vizier:scan     # installs vz_* catalog tables + scans your DB and app/ code + LLM passes

Open /vizier while logged in — that's it. The service provider is auto-discovered; routes sit behind ['web', 'auth']. Configure the LLM with two env vars (VIZIER_LLM_URL, optionally VIZIER_LLM_MODEL), publish the full config with php artisan vendor:publish --tag=vizier-config.

Production hardening: point VIZIER_CONNECTION at a connection that uses a read-only DB user. Out of the box Vizier uses your default connection (with a fresh read-only session + SQL guard), which is fine for trying it out.

Quickstart B — any other stack (legacy Laravel, plain PHP, Node, …): sidecar

git clone https://github.com/hemreduru/vizier && cd vizier
composer install
bin/vizier setup            # interactive wizard: creates DB users + grants + config, runs the full scan
bin/vizier serve --port=8080

setup asks for admin MySQL credentials once (never stored), creates the catalog schema and the read-only vizier_ro user with SELECT grants on the schemas you pick, writes config.php with generated passwords, scans the database and (optionally) your codebase, auto-approves high-confidence joins, and prints a ready-to-open UI link. Non-interactive: pass --yes with --db-host= --admin-user= --admin-pass= --schemas= --llm-url= --code= flags.

After the scan (optional polish, both modes)

bin/vizier pair-examples --sql="SELECT class_name, definition_tr FROM registry.reports"
                                     # name harvested corpus examples from your report registry
bin/vizier edges                     # review remaining join candidates: --approve=ID / --reject=ID
bin/vizier note "Active students: spc.sp_is_active = 1"   # tribal knowledge, injected into every prompt

The codebase harvest is the warm start: every hand-written JOIN in your project is a proven relationship edge, and every report query becomes an approved example once pair-examples names it — the flywheel starts full, not empty.

Run

bin/vizier ask "yazılım ofisinde aktif çalışan personel listesi"   # CLI test
bin/vizier serve --port=8080                                       # UI + API

Embed in any host app:

<iframe src="https://reports.internal:8080/ui?t=<signed-token>"
        style="width:100%;height:85vh;border:0"></iframe>

The token is a tiny HMAC the host signs with the shared auth.secret — see examples/host-issue-token.php (PHP 5.6+ compatible, no dependencies). Dev shortcut: bin/vizier token --user=me.

HTTP API

Method	Path	Body	Notes
POST	`/api/ask`	`{"question": "..."}`	full pipeline; returns title/chart/sql/columns/rows
POST	`/api/feedback`	`{"run_id": 1, "good": true}`	`good` feeds the example flywheel
GET	`/api/admin/edges?status=pending`	—	admin token
POST	`/api/admin/edges`	`{"id": 1, "status": "approved"}`	admin token
GET/POST	`/api/admin/examples`	—	review corpus candidates
GET	`/api/health`	—	no auth

Token via ?t= or Authorization: Bearer.

Safety model (defense in depth)

Prompt rules (courtesy, not control)
phpmyadmin/sql-parser gate: exactly one statement, SELECT-only
Catalog allowlist — only scanned, enabled tables; system schemas always denied
Every query wrapped: forced LIMIT + MAX_EXECUTION_TIME hint
PHP-side row cap + read-only session
Least-privilege read-only DB user — the boundary that holds even if everything above fails
Full audit log (vz_runs): every question, SQL, outcome, user

The UI always shows the generated SQL and row count — no silent wrong answers.

Tests

composer test                      # logic + pipeline with fakes, no DB needed
VIZIER_TEST_DSN="mysql:host=...;dbname=vizier_test" \
VIZIER_TEST_USER=... VIZIER_TEST_PASS=... composer test   # + catalog store tier

Design notes

Catalog + vectors live in MySQL (BLOB + in-process cosine; ~10k vectors ≈ tens of ms). vector_store adapters (Qdrant) are the upgrade path, not a requirement.
One LLM config covers every provider via the OpenAI-compatible API; no SDK dependencies.
The schema excerpt sent to the model is explicitly labeled as a partial subset — this stops hallucinated tables.
Turkish questions are handled by keeping literals verbatim (value dictionary supplies exact cell strings) and folding/stemming on the retrieval side.

hemreduru / vizier

Maintainers

Package info

Statistics

Security