epic(mcp): Querya Desktop as a read-only MCP server #1133

Goal

Querya Desktop works as an MCP server: any MCP client (Claude Desktop / Code, Cursor, VS Code, Gemini CLI, Continue, ...) and any model behind it can read the schema of the user's connections and run read-only SQL through Querya. No chat inside Querya, no provider adapters.

Decisions (v1)

• Databases: PostgreSQL, MySQL, SQLite. Mongo, Redis and extension drivers are v2.
• Read-only. Writes (propose_write with confirmation in the Querya window) are v2.
• The server lives in the running app: it reuses configured connections, SSH tunnels and keyring secrets. The model only sees connection id, name, type, environment and database, never credentials.
• Transport: the client starts querya-mcp (stdio). The shim forwards bytes to the app over 127.0.0.1:<random port>; port and token are in a 0600 file in the app data dir. If the app is not running, the shim returns a clear error ("Start Querya Desktop").

Architecture

MCP client ──stdio──▶ querya-mcp (AOT, byte pipe) ──127.0.0.1 + token──▶ Querya Desktop
                                                                         └─ McpServer (dart_mcp)
                                                                            └─ McpQueryService
                                                                               └─ Postgres/Mysql/Sqlite services (read-only sessions)

v1 tools

list_connections, list_tables, describe_table, sample_rows, run_query, explain_query; resource schema://<connection>.

Security: three layers

1. Classifier: only SELECT / WITH without DML / EXPLAIN / SHOW (isMutatingSqlStatement, DestructiveSqlDetector.splitStatements), one statement per call.
2. Read-only session in the database: Postgres PgSessionMode.readOnly (default_transaction_read_only), MySQL SET SESSION TRANSACTION READ ONLY, SQLite PRAGMA query_only=ON.
3. Policy: MCP access is opt-in per connection (off by default), statement timeout, row limit, cell truncation, audit of every call.

Query results are data, not instructions (prompt injection): they are returned as structured content, never mixed into tool descriptions.

Children

Order: #1134 → #1135 → #1136, with #1138 alongside #1134/#1135 and #1137 last.

• feat(mcp): McpQueryService, read-only SQL core for MCP tools #1134 McpQueryService (read-only SQL core)
• feat(mcp): MCP server in the app and the querya-mcp stdio shim #1135 MCP server in the app + querya-mcp stdio shim
• feat(mcp): Settings page, per-connection access and activity log #1136 Settings page, per-connection access, activity log
• test(mcp): security suite for the MCP server #1138 Security test suite
• docs(mcp): user guide, security model and Obsidian notes #1137 Docs and Obsidian notes

v2 (not in this epic)

propose_write with confirmation in the Querya window, Streamable HTTP transport, MongoDB / Redis tools, extension drivers (ClickHouse).