SQL Agent Studio 🚀

SQL Agent Studio is an "Agent-First" IDE fork of VS Code OSS, custom-engineered to modernize SQL Server database management, schema visualization, and unit testing. Moving beyond simple sidebar chat boxes, SQL Agent Studio places an autonomous AI agent at the absolute center of the database development experience through a unified visual workspace and robust Model Context Protocol (MCP) server integration.

---

🗺️ High-Level System Architecture

SQL Agent Studio utilizes a Decoupled Agent-Host Architecture. The user interface communicates with custom extensions embedded inside VS Code OSS, which spin up internal MCP servers to expose schema discovery, semantic search, and unit testing capabilities directly to local LLMs (Ollama / Phi3) or cloud APIs (Gemini 3 Flash & Pro).

 ┌────────────────────────────────────────────────────────┐
 │                   VS Code OSS Shell                    │
 │  ┌──────────────────────────────────────────────────┐  │
 │  │                Mission Control UI                │  │
 │  └────────────────────────┬─────────────────────────┘  │
 └───▲───────────────────────│────────────────────────▲───┘
     │                       │                        │
     │ SSE Event Streams     │ Tool Calls & JSON-RPC  │ MCP Tools
     │                       ▼                        │
 ┌───┴────────────────────────────────────────────────┴───┐
 │                  Agent Orchestrator                    │
 │                (Gemini / Ollama / Phi3)                │
 └───────────────────────────┬────────────────────────────┘
                             │
            ┌────────────────┼────────────────┐
            ▼                ▼                ▼
    ┌───────────────┐┌───────────────┐┌───────────────┐
    │  SQL Context  ││   SQL Rely    ││ SQL Visualize │
    │  (Metadata)   ││   (Testing)   ││   (Canvas)    │
    └───────────────┘└───────────────┘└───────────────┘

---

🔌 Custom Extensions & Added Features

SQL Agent Studio adds four custom VS Code extensions to the default VS Code OSS distribution, each enhancing a specific aspect of agent-driven SQL development.

1. SQL Agent Studio: Mission Control (sql-agent-studio)

The central orchestrator that exposes the visual "Mission Control" canvas inside the IDE.

• Reasoning Graph (React Flow / D3): A live node-based visualization mapping the Agent's thought process (Intent Node ➔ Context Node ➔ Plan Node ➔ Execution Node ➔ Validation Node).
• 5-Step Implementation Plans: Every write operation is wrapped in a strict plan structure requiring:
  1. Technical/Business Summary
  2. Prerequisites & Permissions
  3. T-SQL Execution Script
  4. Automated Validation Queries
  5. Rollback Blocks
• State Machine & Clarification Interlock: Pauses execution and displays a multi-choice clarification card in Mission Control if intent certainty drops below 90%.
• Hardened Prompting: Enforces strict tool-calling behaviors and bans unauthorized manual transaction blocks (e.g., ghost BEGIN/COMMIT TRANSACTION loops).

2. SQL Context (sql-context)

Handles AI-powered database documentation, metadata retrieval, and business context editing.

• Interactive Database Encyclopedia: A visual, collapsible hierarchy outlining databases, tables, views, procedures, and columns.
• Surgical Per-Column AI Generation: Enables users to select individual columns and generate descriptive metadata with AI.
• Batch Document Database: An automated loop that documents whole tables or schemas. Features a Batch Review UI allowing the user to select AI providers, review generated text, and cancel or pause.
• DB-Level Persistence: Synchronizes documentation directly with the database's MS_Description extended properties.
• Semantic Object Search: Operates an active indexer allowing the Agent to search for database objects based on natural language intent (e.g., searching for "financial transactions" instead of knowing specific table names).

3. SQL Rely (sql-rely)

A native database unit testing suite integrated directly with the VS Code Testing UI and exposed to the AI Agent.

• tSQLt Framework Integration: Auto-installs tSQLt schemas, creates test classes, and scaffolds test procedures.
• SQLCop Guardrails: Integrates SQLCop tests to analyze schemas for performance, security leaks, and syntax vulnerabilities.
• Database-Aware Test Running: Exposes a hardened, connection-aware API allowing tests to execute against specific selected databases rather than relying strictly on the active text editor.
• JSON-Piped Output: Test results are formatted and returned as structured JSON payloads, allowing the AI Agent to process failures and success logs with precision.

4. SQL Visualize (sql-visualize / mssql-visual-builder)

A webview canvas allowing developers to visually map tables, join conditions, and build queries.

• Dependency Explorer: A React Flow interactive view mapping active schemas and schema dependencies.
• Ghost Joins (Inferred Relationships): Uses column-naming heuristics (e.g., matching column suffixes like ID and Code with identical types) to infer and display suggested joins. Includes a check toggle to hide or show suggestions.
• Auto-Layout Engine: Supports both hierarchical (Directed Acyclic Graph layout ignoring ghost joins to avoid clutter) and circular layouts.
• Type-Safe Joins: Validates data types upon dragging visual connections, rejecting invalid joins with direct warning messages.
• Workspace Persistence: Allows saving and loading full canvas designs as .sqlviz files (Ctrl+Shift+S and Ctrl+O).

---

🛠️ Exporter MCP Tools

To allow the autonomous AI Agent to work seamlessly, both sql-context and sql-rely expose standard Model Context Protocol (MCP) tool APIs:

Database & Context Tools (sql-context-server)

• get_database_context: Reads global business rules from .sqlcontext.md.
• update_database_context: Rewrites or appends business guidelines.
• get_object_documentation: Retrieves database descriptions/metadata.
• get_schema: Obtains DDL structure for tables, views, and procedures.
• apply_documentation: Commits documentation back to MS_Description.
• search_objects: Searches database objects via semantic matching.

Database Testing Tools (sql-rely-mcp)

• run_sql_tests: Runs all unit tests.
• run_sql_test: Executes a specific test procedure.
• list_sql_tests: Lists all unit test classes and test cases.
• create_test_class: Generates a new tSQLt test class (schema).
• create_sql_test: Generates a template for a new tSQLt test.
• deploy_sql_test: Deploys a tSQLt test procedure to the database.
• install_sqlcop: Installs SQLCop test rules.

---

🔒 Safety & Guardrails

• PII Scrubbing: RegEx-based local filters automatically mask emails and phone numbers in query results before they leave the environment for AI processing.
• Transactional Sandbox: All write actions proposed by the Agent default to a BEGIN TRANSACTION block, enabling risk-free simulation and direct rollback functionality.
• Impact Guardrail: Before executing destructive UPDATE or DELETE statements, the Agent is forced to execute a SELECT COUNT(*) pre-flight check to inform the user of the scope of modification.

---

🏗️ Developer Setup Guide

Follow this guide to clone, compile, and run the SQL Agent Studio codebase.

1. Prerequisites

Ensure you have the following installed on your machine:

• Node.js (v20+ recommended) & npm (included with Node.js)
• SQL Server Instance (Local SQL Express, Developer Edition, or Azure SQL database)
• Ollama (for local AI inference with Phi3 or similar LLMs)
• Git (for code collaboration)

2. Clone the Repository

Clone the repository and enter the workspace:

git clone https://github.com/ZEUSXXIV/SQL-Agent-Studio.git
cd SQL-Agent-Studio

3. Install Host Dependencies

Install dependencies for the custom VS Code OSS IDE distribution:

cd vscode-oss
npm install

4. Compile Custom Extensions

SQL Agent Studio features four custom extensions under vscode-oss/extensions/. Each must be compiled for the editor to run properly:

• SQL Agent Studio (Mission Control):

  cd vscode-oss/extensions/sql-agent-studio
  npm install
  npm run compile

• SQL Context (Metadata & Search):

  cd vscode-oss/extensions/sql-context
  npm install
  npm run compile

• SQL Rely (Database Testing):

  cd vscode-oss/extensions/sql-rely
  npm install
  npm run compile

• SQL Visualize (Dependency Explorer):

  cd vscode-oss/extensions/sql-visualize
  npm install
  npm run compile

Optional: Alternatively, you can use npm run watch in any of these extension folders to enable hot-recompiling during active development.

5. Start the Standalone UI (Optional)

To run or test the standalone React user interface components:

cd agent-ui
npm install
npm run dev

6. Run the Editor

Start the compiled custom editor from the vscode-oss folder:

• Windows (Command Prompt / PowerShell):

  cd vscode-oss
  .\scripts\code.bat

• macOS / Linux:

  cd vscode-oss
  ./scripts/code.sh

7. Configure AI Models & Databases

1. Ollama Integration: Ensure Ollama is running and download the default local model:

   ollama run phi3

2. Database Schema Setup:
   • Open the newly launched editor and open a .sql file to activate database connections.
   • Click Disconnected in the status bar (or use Ctrl+Shift+P ➔ MS SQL: Connect) to connect to your SQL Server instance.
   • Click the Test Explorer beaker icon (⚗️) in the left Activity Bar.
   • Run the command palette command SQL Rely: Install tSQLt Framework followed by SQL Rely: Install SQLCop Tests to deploy testing frameworks directly to your target database.