🌙 Verbis — Intelligent Database Assistant (VS Code Extension)

Publisher: pratham2511 · Version: 1.0.0 · License: MIT Repository: https://github.com/Pratham2511/Verbis

Eliminate the barrier between human intent and database insight by transforming natural language into precise, optimized, safe, and explainable database operations — with team collaboration and enterprise-grade privacy — entirely within the developer's workspace.

Verbis is an LLM-based intelligent database assistant delivered as a VS Code desktop extension. It generates, optimizes, validates, and executes SQL (and NoSQL) queries, explains results, monitors performance continuously, enforces enterprise-grade privacy, and learns from user behavior — all within the developer's existing workspace.

The default LLM provider is Google Gemini 2.5 Flash (free tier available — see Get a free API key). Groq and Ollama (local) are also supported, and API keys from any provider — Gemini, Claude, Kimi, OpenAI, and others — are accepted without format restrictions. Your API key is stored in the OS keychain via VS Code SecretStorage and is never written to any file on disk.

📦 Repository Layout

verbis/
├── package.json                  # VS Code extension manifest (marketplace-ready)
├── tsconfig.json
├── .vscodeignore                 # Excludes src/, webview/src/, python_backend/
├── .gitignore
├── CHANGELOG.md                  # [1.0.0] — 2026-08-16
├── LICENSE                       # MIT
├── README.md                     # ← this file
├── CONTRIBUTING.md
├── media/
│   ├── icon.png                  # 128×128 marketplace icon (add manually)
│   ├── icon.svg                  # source SVG
│   └── sidebar.svg               # 16×16 activity-bar icon
│
├── src/                          # Extension host (TypeScript, runs in Node.js)
│   ├── extension.ts              # Activation: first-run API key prompt + command registration
│   ├── BackendManager.ts         # Python subprocess lifecycle (start/stop/restart)
│   ├── panels/
│   │   ├── VerbisPanel.ts     # Main webview (chat + SQL + results)
│   │   ├── SchemaPanel.ts        # Schema explorer + ER diagram viewer
│   │   └── QueryTreePanel.ts     # ReactFlow DAG panel
│   ├── services/
│   │   ├── SecretsService.ts     # Gemini / Groq / DB-password storage (OS keychain)
│   │   ├── WorkspaceService.ts   # .qmind/ file I/O
│   │   └── BackendClient.ts      # HTTP client (forwards api_key + provider to backend)
│   └── types/index.ts            # Shared TypeScript interfaces + WebviewMessageType union
│
├── webview/                      # React app (Vite-bundled, runs in webview sandbox)
│   ├── package.json
│   ├── vite.config.ts            # CSP-safe (no eval, no inline scripts)
│   ├── tsconfig.json
│   ├── tailwind.config.js
│   ├── postcss.config.js
│   ├── index.html
│   └── src/
│       ├── main.tsx
│       ├── App.tsx               # Routes Chat / Schema / Tree / Glossary / Robustness / Connections
│       ├── index.css             # Tailwind + custom Verbis styles
│       ├── vscode.ts             # acquireVsCodeApi + postMessage helpers + useVsCode() hook
│       └── components/
│           ├── ChatPanel.tsx
│           ├── MessageBubble.tsx
│           ├── SQLCodeBlock.tsx        # CodeMirror 6 + annotation gutter
│           ├── ResultTable.tsx         # Paginated grid + cell annotations
│           ├── ConfidenceBar.tsx       # Novel Contribution [#5](https://github.com/Pratham2511/Verbis-Intelligent-Database-Assistant/issues/5)
│           ├── NarrativeCard.tsx       # Novel Contribution [#12](https://github.com/Pratham2511/Verbis-Intelligent-Database-Assistant/issues/12)
│           ├── QueryTreeView.tsx      # ReactFlow DAG (Novel [#14](https://github.com/Pratham2511/Verbis-Intelligent-Database-Assistant/issues/14))
│           ├── GlossaryEditor.tsx      # Novel Contribution [#10](https://github.com/Pratham2511/Verbis-Intelligent-Database-Assistant/issues/10)
│           ├── ConnectionForm.tsx
│           ├── ApiKeySettings.tsx      # ← Gemini + Groq API key entry UI
│           └── RobustnessReport.tsx    # Novel Contribution [#16](https://github.com/Pratham2511/Verbis-Intelligent-Database-Assistant/issues/16)
│
└── python_backend/               # Core intelligence engine (Python 3.11+, FastAPI)
    ├── main.py                   # FastAPI app, lifespan, binds 127.0.0.1:8765
    ├── requirements.txt           # >= constraints, all deps pinned
    ├── config.py                 # Pydantic BaseSettings
    ├── api/
    │   ├── dependencies.py       # DB pool, LLM, ChromaDB client wiring
    │   └── routes/
    │       ├── health.py
    │       ├── generate.py       # POST /api/generate (forwards api_key + provider to LLM)
    │       ├── execute.py        # POST /api/execute
    │       ├── schema.py         # GET  /api/schema
    │       ├── impact.py         # POST /api/schema/impact
    │       ├── robustness.py     # POST /api/robustness
    │       └── glossary.py       # GET/POST /api/glossary
    ├── models/
    │   └── requests.py           # GenerateRequest with provider + api_key fields
    ├── services/                 # 18 backend services
    │   ├── llm_service.py        # ← AsyncOpenAI: Gemini / Groq / Ollama
    │   ├── intent_service.py
    │   ├── ambiguity_service.py
    │   ├── privacy_shield.py
    │   ├── rag_service.py
    │   ├── sql_generator.py
    │   ├── nosql_generator.py
    │   ├── federated_service.py
    │   ├── validator_service.py
    │   ├── optimizer_service.py
    │   ├── confidence_service.py
    │   ├── execution_service.py
    │   ├── plan_explainer.py
    │   ├── perf_tracker.py
    │   ├── narrative_service.py
    │   ├── pii_service.py
    │   ├── schema_impact.py
    │   ├── glossary_service.py
    │   └── robustness_service.py
    └── core/
        ├── prompt_builder.py
        ├── memory_store.py
        ├── history_store.py
        └── db_pool.py

🔑 Setting Your API Key

Verbis needs an LLM provider API key to generate SQL queries. The default provider is Google Gemini (free tier), and keys from any provider — Gemini, Claude, Kimi, OpenAI, Groq, and others — are accepted; no provider-specific key format is enforced. There are three ways to enter your key:

Option A — First-run welcome prompt

On the very first activation, Verbis shows an information message:

Welcome to Verbis! A free Gemini API key is needed to generate queries.

Click Set API Key to paste your key directly, or Get Free Key to open aistudio.google.com/app/apikey in your browser.

Option B — From the Command Palette

Run Verbis: Set Gemini API Key from the Command Palette (Ctrl+Shift+P / Cmd+Shift+P). You'll be asked which provider (gemini or groq) you're setting a key for, then prompted to paste it.

Any key format is accepted — paste the full key exactly as your provider shows it. The only check is a basic sanity check (non-empty, reasonable length); there is no prefix validation.

Option C — From the Webview Settings Panel

Open the Connections tab in the Verbis webview. The API Keys card at the top lets you paste any provider's key (Gemini, Claude, Kimi, OpenAI, Groq, …) into a password-masked input. The "Get free key ↗" links jump straight to the relevant provider's API-key page.

Removing the key

Run Verbis: Remove API Key from the Command Palette. A modal confirmation is required.

Where the key lives

• OS keychain only (via VS Code SecretStorage). It is never written to .qmind/, config.json, or any other file on disk.
• The Python backend receives the key per-request in the JSON body of POST /api/generate. It is not stored in any server-side environment variable or file.
• The active key is resolved from the verbis.llm.provider setting (see below): gemini returns the Gemini key, groq returns the Groq key.

✨ The 18 Novel Research Contributions

🏗️ Architecture Overview

flowchart TD
    subgraph VSCODE["VS Code Extension (UI Layer)"]
        CP["Chat Panel (NL Input + Narrative)"]
        QE["Query Editor (CodeMirror 6 + Annotations)"]
        SE["Schema / ER Diagram Viewer"]
        QT["Query Tree DAG (ReactFlow)"]
        AK["API Key Settings (Gemini / Groq)"]
    end

    subgraph EH["Extension Host (TypeScript)"]
        PM["postMessage Bridge"]
        SS["SecretStorage (OS keychain)"]
        BLM["Backend Lifecycle Manager"]
    end

    subgraph PY["Python Backend (FastAPI — localhost:8765)"]
        GEN["SQL / NoSQL Generator"]
        PP["Post-Processing Pipeline (Validate / Rank / Optimize / Score)"]
        EX["Execution & Intelligence Engine"]
    end

    subgraph EXT["External"]
        GEMINI["Gemini 2.5 Flash (default)"]
        GROQ["Groq (llama-3.3-70b-versatile)"]
        OLLAMA["Ollama Local (sqlcoder)"]
        DB["Databases (PostgreSQL / MySQL / SQLite / MongoDB)"]
    end

    CP & QE & SE & QT & AK --> PM
    PM <--> EH
    EH --> BLM
    BLM --> PY
    SS -->|api_key per request| PY
    PY --> GEMINI
    PY --> GROQ
    PY --> OLLAMA
    PY --> GEN --> PP --> EX
    EX --> DB

Key security property: the API key never touches disk. It is resolved from VS Code SecretStorage on every /api/generate call, attached to the request body in BackendClient.ts, and forwarded by routes/generate.py directly into llm_service.generate_sql(...).

🚀 Quick Start

Prerequisites

• Node.js 20+
• Python 3.11+
• VS Code 1.85+
• A free Gemini API key from aistudio.google.com/app/apikey (or an API key from any other supported provider)
• (optional) Ollama for fully-offline local LLM mode
• (optional) PostgreSQL / MySQL / MongoDB for live query execution

1. Install dependencies

git clone https://github.com/Pratham2511/Verbis.git
cd Verbis

# Extension host (TypeScript)
npm install

# Webview (React)
cd webview
npm install
cd ..

# Python backend
cd python_backend
pip install -r requirements.txt
cd ..

2. Build the webview

cd webview
npm run build          # produces webview/dist/
cd ..

3. Compile the TypeScript extension

npm run compile        # produces out/

4. Run in VS Code

• Open the Verbis/ folder in VS Code.
• Press F5 to launch an Extension Development Host with Verbis loaded.
• In the new window, open a workspace folder (any folder is fine — Verbis will create .qmind/ inside it).
• On first activation you'll see the welcome prompt asking for your API key (any provider's key format is accepted).
• Use the Command Palette (Ctrl+Shift+P / Cmd+Shift+P) and search for "Verbis".

5. Configure LLM provider (optional)

In VS Code Settings → Extensions → Verbis:

6. (Optional) Set up Ollama for local mode

# Install from https://ollama.com
ollama pull sqlcoder
ollama serve

Then set verbis.llm.provider to local in VS Code settings. No API key is required in local mode.

⌨️ Keyboard Shortcuts

🧭 Commands

🔌 Backend API Reference

All endpoints are bound to http://127.0.0.1:8765 (or first free port 8765–8775). Localhost-only — no remote connections possible (a defense-in-depth middleware refuses non-loopback requests).

Example: curl

# Health check
curl http://127.0.0.1:8765/api/health
# → {"status":"ok","version":"3.0.0"}

# Generate SQL — API key passed per request
curl -X POST http://127.0.0.1:8765/api/generate \
  -H 'Content-Type: application/json' \
  -d '{
    "nl_query": "Show me the top customers last quarter",
    "schema_context": "Table orders (customer_id int, order_total numeric, order_date date)",
    "dialect": "postgresql",
    "query_type": "sql",
    "provider": "gemini",
    "api_key": "AIzaSy...your-key..."
  }'
# → {"query":"SELECT customer_id, SUM(order_total) ...","confidence":0.9,"alternatives":[]}

🔒 Security Model

• Credentials never on disk. API keys and DB passwords live exclusively in VS Code SecretStorage (backed by the OS keychain). They never appear in config.json, memory.json, or any other file under .qmind/.
• API key passed per request. The Python backend receives the API key in the JSON body of /api/generate, uses it for a single LLM call, and discards it. The key is not stored in any environment variable, config file, or in-memory cache on the backend.
• Localhost only. The Python backend binds exclusively to 127.0.0.1. A defense-in-depth middleware refuses any non-loopback request.
• Read-only by default. INSERT / UPDATE / DELETE / DROP statements are rejected unless verbis.execution.readOnlyByDefault is set to false.
• Row limit + timeout. Default 500 rows returned (configurable up to 10,000 via verbis.query.rowLimit). 60-second query timeout.
• Privacy Shield. When verbis.privacy.enableShield is on, all schema names are tokenized (table_A, col_1) before the prompt is sent to the cloud LLM. The tokenization map is AES-256-GCM encrypted at .qmind/priv_map.enc.
• PII masking. Result columns are scanned via Microsoft Presidio (when available) + custom regex. Masking rules are configurable per project in .qmind/pii_rules.json. Every masked column is logged to .qmind/pii_audit.log for GDPR / CCPA / HIPAA compliance reporting.

📁 The .qmind/ Workspace

All Verbis state lives in a single .qmind/ folder at the workspace root. It is designed to be committed to version control alongside the codebase — this is the project's persistent "database intelligence" state.

.qmind/
├── config.json              # DB connections, LLM mode, PII rules (non-sensitive only)
├── schema_cache.json        # Cached schema introspection
├── memory.json              # Adaptive user preferences + disambiguation choices
├── perf_log.json            # Performance regression baseline data
├── pii_rules.json           # Configurable PII masking rules per column pattern
├── pii_audit.log            # Compliance audit trail for masked results
├── priv_map.enc             # AES-256-GCM encrypted schema tokenization map
├── history.json             # Full query execution history (genealogy)
├── query_tree.json          # Interactive branching DAG structure
├── annotations.json         # Collaborative line-level + cell-level annotations
├── er_diagram.svg           # Auto-generated ER diagram (versioned)
├── glossary.json            # Business glossary terms and SQL templates
├── chromadb/                # Local persistent ChromaDB vector index
└── sessions/
    ├── 2026-08-10.json      # Saved chat sessions by date
    └── 2026-08-15.json

Note: The .gitignore in this repo excludes .qmind/ by default because it may contain sensitive query results. If you want to share a sanitized version with your team, copy the .qmind/ structure into a separate examples/ directory and strip any sensitive data before committing.

🧪 Testing

# TypeScript unit tests (extension + webview)
npm test                          # vitest

# Python unit tests
cd python_backend
pytest -v                         # pytest + pytest-asyncio

# End-to-end (VS Code Extension Testing)
npm run test:e2e                  # @vscode/test-cli + @vscode/test-electron

Coverage targets:

• 100% coverage on validator_service.py, privacy_shield.py, pii_service.py.
• Benchmark runs on Spider 2.0 (1,000 queries), BIRD (1,500 queries), CoSQL (multi-turn).

📦 Publishing to the VS Code Marketplace

# 1. Make sure media/icon.png exists (128×128 PNG)
#    The .gitkeep file in media/ explains this — vsce package will fail
#    with a "File not found" error until icon.png is added.

# 2. Build everything
cd webview && npm run build && cd ..
npm run compile

# 3. Package
npx vsce package
# → produces verbis-db-assistant-1.0.0.vsix

# 4. Publish (requires a VS Code Marketplace PAT)
npx vsce publish

Before publishing, verify:

• [ ] package.json has publisher, icon, categories, keywords, repository, bugs, homepage
• [ ] package.json does NOT have "private": true
• [ ] CHANGELOG.md exists in repo root
• [ ] .vscodeignore excludes webview/src/** but NOT webview/dist/**, media/**, or out/**
• [ ] media/icon.png (128×128 PNG) exists
• [ ] requirements.txt includes openai>=1.0.0 and other backend deps
• [ ] npm run compile succeeds with zero errors

📊 Performance SLAs

🛠️ Tech Stack

Why CodeMirror 6 instead of Monaco?

Monaco requires web workers and eval(), both of which are blocked by the VS Code webview Content Security Policy (CSP). CodeMirror 6 is CSP-safe, supports SQL syntax highlighting, and integrates cleanly with the annotation gutter required by Component 12 (Collaborative Annotations).

Why Gemini as the default provider?

1. Free tier is generous — the Gemini 2.5 Flash model has a substantial free quota that covers most developer workflows.
2. OpenAI-compatible SDK — Gemini's generativelanguage.googleapis.com/v1beta/openai/ endpoint accepts the standard OpenAI Python client, so adding it required no new dependencies beyond openai>=1.0.0.
3. Fast — Flash-tier latency is well within the < 3 s SQL-generation SLA for simple queries.

If you prefer Groq or local Ollama, just flip verbis.llm.provider in VS Code settings — no code changes required.

🎯 Example Interaction Flows

Flow 1 — Ambiguous Query with Memory Resolution

Session 1:
User:       "Show me the top customers last quarter"
System:     [Ambiguity Detector: 'top' and 'last quarter' are ambiguous]
Assistant:  "Two quick questions to generate your query:
             What does 'top' mean?
             [A] Highest total spend  [B] Most purchases by count  [C] Most recent orders
             What is 'last quarter'?
             [D] Previous calendar quarter  [E] Last 90 rolling days"
User:       Selects A + D
System:     [Writes to memory.json:
              "top customers" → highest_total_spend,
              "last quarter"  → previous_calendar_quarter]
            [Multi-path generation: Path 2 (CoT) ranks highest]
System:     Confidence: 94%
            SELECT customer_id, SUM(order_total) AS total_spend
            FROM orders
            WHERE order_date BETWEEN '2026-04-01' AND '2026-06-30'
            GROUP BY customer_id
            ORDER BY total_spend DESC
            LIMIT 10;

Session 2 (next day):
User:       "Top customers last quarter again"
System:     [Disambiguation memory hit — resolves automatically, no questions asked]
            [Same SQL pattern generated in < 2 seconds]

Flow 2 — Privacy Shield + PII Masking

User:       Connects to HR database (employees, salaries, SSNs) — Cloud Mode active
System:     [Privacy Shield activates]
            Real schema:  employees.salary, employees.ssn, departments.budget
            Sent to Gemini:  table_A.col_1, table_A.col_2, table_B.col_3

User:       "Average salary by department for 2026"
LLM sees:   "Average col_1 grouped by col_3 for 2026"
LLM output: SELECT AVG(col_1), col_3 FROM table_A JOIN table_B ON ...
System:     [De-tokenizes — user sees real names:]
            SELECT AVG(salary), dept_name FROM employees JOIN departments ...

            [Query executes — result contains SSN column]
            [PII Masker: SSN column → XXX-XX-XXXX in all displayed rows]
            [Audit log entry: ssn masked via rule SSN_PATTERN at 2026-08-15T10:23:44Z]

📜 License

MIT. See LICENSE for details.

📚 Research Grounding

This work is grounded in a post-analysis of 40+ papers (2024–2026) on NL2SQL, RAG, query optimization, schema evolution, privacy, and database tooling, including:

• IBM RLAIF NL2SQL, BIRD Benchmark, MAC-SQL, CHESS, PET-SQL, SchemaRAG
• AmbiSQL, Spider 2.0, DBCopilot, OpenSearch-SQL, DIN-SQL, CHASE-SQL
• EvoSchema (VLDB 2025), LLM-PM (ICDE 2025)
• "NL2SQL is a Solved Problem… Not!" (CIDR 2024)
• arXiv 2505.23838 (cross-lingual NL2SQL), arXiv 2508.15276 (AmbiSQL)

The 18 novel contributions are documented in the project specification (LLM_DB_Assistant_Ultimate_Prompt_v3-1.md).

🤝 Contributing

To contribute:

1. Fork the repo at https://github.com/Pratham2511/Verbis
2. Create a feature branch (git checkout -b feat/my-feature)
3. Commit your changes following the existing commit-message style
4. Open a Pull Request against main

For major changes, please open an issue first to discuss what you'd like to change.

🔮 Future Research Directions

• Multi-language NL support (Hindi, Arabic, French → SQL) — closing the cross-lingual NL2SQL gap (arXiv 2505.23838).
• RLHF Continuous Improvement — use thumbs up/down signals and annotation flags to fine-tune the local Ollama model incrementally.
• Automated Test Query Generation — from the schema, generate representative test queries automatically and self-evaluate accuracy without user input.
• NoSQL-to-SQL Migration Assistant — NL-guided schema mapping from MongoDB collections to normalized relational schemas.
• Voice-to-SQL Interface — spoken NL input for accessibility and hands-free workflows.
• Proactive Insight Surfacing — scheduled scans of the database with proactively suggested queries based on historical usage patterns and detected data anomalies.

📝 Changelog

See CHANGELOG.md for the full version history.

Document Revision History: