# SQLStudio v0.6.5 — Complete Technical Reference for AI Assistants SQLStudio is a **Production-Safe AI Database Performance Optimization & SQL Statement Tuning Platform** written in Rust 1.100 (Edition 2024) with GPUI Community Edition for macOS and Windows—not a generic connection-first DB browser. The public website (`https://sqlstudio.dev`) supports 5 languages (`en`, `zh-CN`, `zh-TW`, `ja`, `ko`) via `?lang=` query parameters and client detection. ## 1. Supported Database Engines (12 Total) - **7 Built-in Native Drivers**: 1. PostgreSQL (`tokio-postgres` + `tokio-postgres-rustls`, persistent `search_path`, `pg_catalog`/`pg_stat`/`pg_stat_statements` read-only introspection, `EXPLAIN (FORMAT JSON)`, loopback `DEV+TEST` twin support). 2. MySQL (`performance_schema` digest diagnostics, native schema/FK/index introspection, ESR & AST SQL Lab, loopback `DEV+TEST` twins). 3. MariaDB (native protocol compatibility, catalog introspection, designer & backup). 4. SQLite (`rusqlite` bundled, zero-prod-disk local `DEV/TEST` twin target, `PRAGMA foreign_key_check`, `EXPLAIN QUERY PLAN`, 100K Zipf seeding & `ANALYZE`). 5. DuckDB (embedded analytical introspection, zero-prod-disk local `DEV/TEST` analytical twin target & skewed benchmark execution). 6. ClickHouse (HTTP interface & system catalog introspection). 7. Redis (native RESP protocol, `db0..dbN` switching, bounded `SCAN`, `FLUSHALL`/`FLUSHDB`/`KEYS` approval guard). - **5 Dialect Modeling & Connectivity Probes**: 8. SQL Server 9. Oracle 10. MongoDB 11. Elasticsearch 12. Cassandra ## 2. Production-Safe Performance Tuning & Honest Signals Principles 1. **Default `EnvMode::Prod` Read-Only Protection & `slow_diag`**: - New connections default to `EnvMode::Prod` (`read_only = true` enforced). - Non-intrusive read-only `slow_diag` queries `pg_stat_statements` (falling back honestly to `pg_stat_activity`) and MySQL `performance_schema`, plus `pg_stat_user_tables` / catalog metadata for full-table `Seq Scan` offenders, dead-tuple bloat (`n_dead_tup >= 15%` & `>= 1,000`), stale `ANALYZE`, and Root Blocker lock trees. 2. **1-Click Zero-PII Local `DEV + TEST` Twins (`local_twin`)**: - Zero production server disk footprint; strips 100% of PII data rows and clones only DDL, FKs, and indexes into embedded SQLite/DuckDB or loopback-only (`127.0.0.1` / `localhost`) PG/MySQL, linking `linked_prod_id` to the upstream `PROD` profile. 3. **Tuning-Grade Skewed Data Seeding (`mock`)**: - Supports `Uniform`, `HotspotZipf` (80/20 hotspot skew), and `SkewedStatusTime` distributions up to `100,000` rows per table in Kahn FK topological order with zero orphans and automatic `ANALYZE`. Strictly blocks execution on `PROD`. 4. **6-Rule AST SQL Statement Rewriter, ESR+`INCLUDE` Index Advisor & A/B Benchmarking**: - Deterministic AST rules: `SARGABLE_DATE_RANGE`, `ARITHMETIC_PREDICATE_ISOLATION`, `NOT_IN_TO_NOT_EXISTS`, `OR_CONSOLIDATION_OR_UNION`, `KEYSET_PAGINATION_DEEP_OFFSET`, `IMPLICIT_CAST_AND_WILDCARD_OR_STAR`. - `index_advisor` applies Equality -> Sort -> Range (`ESR`) + `INCLUDE` covering columns with absorbed redundant index detection and reversible rollback DDL. - `run_ab_sql_benchmark` executes Before/After `EXPLAIN ANALYZE` on linked `DEV`/`TEST` twins alongside plan hotspot detection (`TopTimeConsumer`, `CardinalityDrift`, `HighFilterDiscard`, `DiskSpill`). 5. **HMAC-SHA256 Dual-Stage Promotion Ledger (`promotion_verifications.json`)**: - Persists HMAC-SHA256 verification proofs across linked `DEV` and `TEST` runs. Any `PROD` DDL or index promotion requires verified proofs on BOTH `DEV` and `TEST` plus a 15-minute (`900s`) single-use manual desktop approval with rollback DDL. 6. **Honest Signals Integrity**: - Real anti-join orphan detection (`PRAGMA foreign_key_check` / `NOT EXISTS` with `2500ms` timeout and `3,000,000` row guard -> `Unchecked` when skipped, never `0`). - Cold PostgreSQL tables with zero scans report `insufficient stats` with 0 score penalty; `Critical` findings cap scores at `<= 74` total / `<= 68` relationship / `<= 88` index. - Multi-Script i18n: 8-locale atomic desktop i18n (`zh-CN`, `zh-TW`, `zh-SG`, `ja-JP`, `ko-KR`, `en-SG`, `en-US`, `en-GB`); 5-language website (`en`, `zh-CN`, `zh-TW`, `ja`, `ko`). ## 3. 32-Tool Governed MCP Server & 18 AI Clients ### Transports & Profiles - STDIO: `sqlstudio --mcp` (or Slim 4-tool profile: `sqlstudio --mcp --slim-tools`) - Streamable HTTP: `sqlstudio --mcp-http` (OS Keychain Bearer token auth) - Diagnostics & Packaging: `sqlstudio mcp doctor`, `sqlstudio mcp doctor --strict`, `sqlstudio mcp pack-mcpb`, `sqlstudio mcp install-project`, `sqlstudio mcp deeplink` ### Slim Profile (4 Tools) 1. `use_database` — Switch connection/DB and mint scoped `target_handle` 2. `inspect_schema` — Dense lossless DSL (~55% fewer tokens than Markdown) with `#hash` dedup & 1-hop FK neighbors 3. `describe_table` — Table columns, constraints, FKs, indexes & optional column profiling 4. `query_sql` — Execute SQL/Redis with `dry_run`, pagination, PII masking & `apr-*` approval gate ### Full Profile (All 32 Tools) Includes the 4 Slim tools plus 22 core/governance tools and 6 dedicated PROD-safe tuning tools: 5. `list_connections` 6. `explain_query` 7. `audit_database_health` 8. `find_join_path` 9. `architect_schema_blueprint` (inspects live schema first; enforces `grain`, `unique_key`, `source`, `writer`, `refresh_cadence`; blocks same-grain duplicates) 10. `review_new_table` (`Merge into Existing` / `Add Column to Existing` / `Allow New Table`) 11. `preview_migration` (diff-only DDL + rollback preview + `apr-*` approval ticket; `query_sql` rejects ad-hoc `CREATE TABLE` without verified `preview_migration` approval) 12. `validate_sql` 13. `upsert_glossary_term` 14. `get_ddl` 15. `list_objects` 16. `get_object_definition` 17. `list_sessions` 18. `lock_waits` 19. `kill_session` (Zero-Trust human approval) 20. `top_queries` 21. `advise_indexes` 22. `write_migration_file` (sandboxed up/down migration files) 23. `compare_schemas` 24. `generate_mock_data` (Kahn FK topological ordering; preview by default) 25. `export_query_result` (CSV/JSON/JSONL/Markdown/SQL with PII & column policies) 26. `run_saved_query` 27. `diagnose_prod_bottlenecks` (Tuning: strict read-only PROD slow SQL, `Seq Scan`, dead-tuple bloat & Root Blocker scan) 28. `clone_prod_to_local_twin` (Tuning: zero-prod-disk, 0-PII DDL/FK/index clone to local SQLite/DuckDB or loopback PG/MySQL with `linked_prod_id`) 29. `seed_tuning_data` (Tuning: up to 100K-row Zipf/skewed datasets in local DEV/TEST twins + auto `ANALYZE`, blocked on PROD) 30. `optimize_sql_statement` (Tuning: 6-rule AST SQL rewrite + ESR+`INCLUDE` covering index & rollback DDL) 31. `verify_sql_in_stage` (Tuning: Before/After `EXPLAIN ANALYZE` A/B benchmarks on linked DEV/TEST stages + HMAC proof) 32. `request_prod_promotion` (Tuning: validates DEV + TEST HMAC ledger proofs & 15-min manual human grant with rollback DDL) ### 4 Interactive MCP Apps (`ui://sqlstudio/*`) - `ui://sqlstudio/result-table` (Results Explorer) - `ui://sqlstudio/er-diagram` (Schema ERD) - `ui://sqlstudio/explain-plan` (A/B Explain Plan & ESR Advisor) - `ui://sqlstudio/approval-card` (Dual-Gate PROD Promotion & Approval Card) ### 18 Supported AI Clients (1-Click Backup-Preserving Installer) Cursor (`JSON`), Claude Code (`JSON`), OpenAI Codex (`TOML`), Windsurf (`JSON`), Zed (`JSONC`), Antigravity / AGY (`JSON`), VS Code Copilot (`JSON`), Gemini CLI (`JSON`), OpenCode (`JSON`), Trae (`JSON`), Kiro (`JSON`), Cline (`JSON`), Roo Code (`JSON`), Factory Droid (`JSON`), Goose (`YAML`), DeepSeek DSH (`YAML`), Claude Desktop (`JSON`), Copilot CLI (`JSON`). ### 20 BYOK AI Providers OpenAI, Anthropic (Claude), Google Gemini, DeepSeek, OpenRouter, OpenCode Go, OpenCode Zen, Moonshot, Z.ai / GLM, MiniMax, Xiaomi MiMo, xAI, Groq, Mistral, Together, HuggingFace, Perplexity, Cohere, Ollama, llama.cpp. ## 4. Commercial Licensing (Strict USD via Creem) & 7 FeatureGates Payment & Merchant of Record: Creem (`https://sqlstudio.dev/pricing`), USD-only checkout supporting global credit/debit cards, Apple Pay, and Google Pay; activation gateway at `https://license.sqlstudio.dev`. Offline signature verification: Pure-Rust RFC 8032 Ed25519 tokens (`SQLS1..`). ### Tiers (USD Only) - **Community ($0 Free Forever)**: Max 2 saved connections, default PROD read-only guard, EXPLAIN hotspot trees, Schema ERD & Honest Audit, Slim 4-tool STDIO MCP. - **Pro Perpetual ($69 USD one-time)**: 7-day no-card Pro trial, 2 device activations, 1 year free updates, Perpetual Fallback forever offline via `SQLS1.`. Unlocks local DEV+TEST twin cloning, Zipf 100K seeding, 6 AST + ESR A/B benchmarking, full 32-tool & HTTP MCP & all 7 FeatureGates. - **Team ($15 / seat / month billed annually = $180 / seat / year)**: Up to 5 devices/seat, HMAC dual-stage promotion ledger, `prod_write_policy` / `staging_write_policy`, `apr-*` audit trail, mandatory PII masking & column deny/mask policies, Git-synced shared SQL rules & glossary, offline enterprise voucher signing. ### One-Click Activation & Deep Linking - Web Activation Portal: `https://sqlstudio.dev/activate` - Post-Checkout Display: `/checkout/success` verifies your Creem checkout/order server-side and immediately displays your Creem license key with a 1-click "Activate in SQLStudio App" button. - Desktop App Deep Link: `sqlstudio://license/activate?key=` launches SQLStudio and activates your key in one click. ### The 7 Pro/Team FeatureGates 1. `third_connection` — Unlimited Connections (vs max 2 in Community) 2. `ssh_tunnel` — SSH Bastion Tunnel 3. `audit_fix_sql_and_docs_export` — Audit Fix SQL & Markdown/SVG/DDL Documentation Export 4. `compare_patch_sql` — Cross-DB Schema & Data Diff Patch SQL 5. `backup_transfer_import` — Logical Backup (Gzip + SHA-256), Direct Transfer & Smart CSV/JSON Import 6. `ai_copilot_byok` — AI Copilot, Cloud BYOK (20 providers) & Governed Architect 7. `mcp_full_26_and_http` — Full 32-Tool Perf Tuning MCP Mode, MCP Apps & Streamable HTTP Bearer Server ### License CLI Commands - `sqlstudio license status [--json]` - `sqlstudio license start-trial [--json]` - `sqlstudio license activate --key [--gateway ] [--name ] [--json]` - `sqlstudio license import --token [--json]` - `sqlstudio license open-url [--headless] [--json]` - `sqlstudio license register-protocol [--dry-run] [--json]` - `sqlstudio license deactivate [--local] [--json]`