airbyte_ops_mcp.mcp.context_store_ops
MCP tools for MotherDuck query diagnostics.
Provides tools for querying MotherDuck QUERY_HISTORY, RECENT_QUERIES,
and active server connections. Requires org admin credentials via the
MOTHERDUCK_ADMIN_TOKEN environment variable.
MCP reference
MCP primitives registered by the context_store_ops module of the airbyte-internal-ops server: 2 tool(s), 0 prompt(s), 0 resource(s).
Tools (2)
query_motherduck_active_connections
Hints: read-only · idempotent
List active MotherDuck server connections.
Calls md_active_server_connections() to show which ducklings currently
have active transactions. Useful for debugging connection pool exhaustion,
orphaned sessions, or verifying expected service accounts are live.
Use the client_connection_id from results to filter the query history
tool via filters.query_connection_id for correlated diagnostics.
The client_query field (currently running SQL) receives the same text
treatment as the query history tool: truncation, string constant redaction,
metadata extraction, hashing, and subtype detection. database_name and
source_id (the owning Sonar source database and its parsed source UUID)
are also derived from the raw client_query, so a connection can be mapped
to a source even when include_query_text omits the text.
Parameters:
| Name | Type | Required | Default | Description |
|---|---|---|---|---|
filters |
object |
yes | — | Structured connection filters. |
include_query_text |
boolean | object |
no | true |
Controls client_query text inclusion. False omits entirely. True applies default treatment (1000 char limit, redact string constants). Pass a QueryTextTreatment object for fine-grained control. |
Show input JSON schema
{
"additionalProperties": false,
"properties": {
"filters": {
"description": "Structured connection filters.",
"properties": {
"user_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Substring match on client_user_agent. md_active_server_connections() does not expose a dedicated user column; service account names are typically embedded in the user agent string."
},
"session_name_contains": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Substring match on client_user_agent. md_active_server_connections() does not expose a session_name column; this filter proxies through client_user_agent."
},
"session_name_pattern": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Regex on client_user_agent. DuckDB RE2 syntax. md_active_server_connections() does not expose a session_name column; this filter proxies through client_user_agent."
},
"min_age_minutes": {
"anyOf": [
{
"minimum": 0,
"type": "number"
},
{
"type": "null"
}
],
"default": null,
"description": "Minimum connection age in minutes. Filters on server_transaction_elapsed_time to find long-running connections (e.g. 60 for connections open 1+ hour)."
}
},
"type": "object"
},
"include_query_text": {
"anyOf": [
{
"type": "boolean"
},
{
"description": "Controls how QUERY_TEXT is processed before returning to the caller.",
"properties": {
"char_limit": {
"default": 1000,
"description": "Truncate QUERY_TEXT to this many characters.",
"exclusiveMinimum": 0,
"type": "integer"
},
"redact_string_constants": {
"default": true,
"description": "Replace string literals in QUERY_TEXT with `?` placeholders.",
"type": "boolean"
}
},
"type": "object"
}
],
"default": true,
"description": "Controls client_query text inclusion. False omits entirely. True applies default treatment (1000 char limit, redact string constants). Pass a QueryTextTreatment object for fine-grained control."
}
},
"required": [
"filters"
],
"type": "object"
}
Show output JSON schema
{
"description": "Result of querying active MotherDuck server connections.",
"properties": {
"total_connections": {
"description": "Total number of active connections matching filters.",
"type": "integer"
},
"connections": {
"description": "Active connection records.",
"items": {
"description": "A single active MotherDuck server connection.",
"properties": {
"client_connection_id": {
"description": "Unique client connection UUID.",
"type": "string"
},
"client_duckdb_id": {
"description": "Unique client DuckDB instance UUID.",
"type": "string"
},
"client_user_agent": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "User agent string of the client."
},
"client_duckdb_version": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Client DuckDB version."
},
"server_transaction_stage": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Current server transaction stage."
},
"server_transaction_elapsed_time": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "How long in the current stage."
},
"client_query": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Currently running SQL text (processed per QueryTextTreatment), or None if omitted."
},
"client_query_hash": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "SHA-256 hex of normalized client_query (whitespace-trimmed, metadata stripped)."
},
"client_query_metadata": {
"anyOf": [
{
"additionalProperties": true,
"type": "object"
},
{
"type": "null"
}
],
"description": "Parsed JSON from leading /* {...} */ comment in client_query, if present."
},
"client_query_subtype": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Regex-derived statement classification of client_query (SELECT/INSERT/COPY/.../UNKNOWN)."
},
"server_query_elapsed_time": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "How long the query has been running."
},
"server_query_progress_pct": {
"anyOf": [
{
"type": "number"
},
{
"type": "null"
}
],
"description": "Query progress percent (from 0.0 to 100.0)."
},
"database_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "MotherDuck database (= Sonar source schema) the running query targets, derived from the `iceberg_scan('s3://...')` path in the raw client_query text. `None` when no such path is present."
},
"source_id": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Airbyte source UUID parsed deterministically from `database_name` (underscores mapped back to hyphens). `None` when absent or the trailing segment is not a canonical UUID (fails closed)."
}
},
"required": [
"client_connection_id",
"client_duckdb_id",
"client_user_agent",
"client_duckdb_version",
"server_transaction_stage",
"server_transaction_elapsed_time",
"client_query",
"client_query_hash",
"client_query_metadata",
"client_query_subtype",
"server_query_elapsed_time",
"server_query_progress_pct"
],
"type": "object"
},
"type": "array"
}
},
"required": [
"total_connections",
"connections"
],
"type": "object"
}
query_motherduck_queries
Hints: read-only · idempotent
Query MotherDuck query execution data for diagnostics.
Supports two modes via the realtime toggle:
realtime=false(default): Queries QUERY_HISTORY -- historical data with slight delay. Best for performance analysis, error pattern mining, cold start investigation.realtime=true: Queries RECENT_QUERIES -- currently running + just-completed queries not yet in QUERY_HISTORY. Best for live debugging, detecting stuck queries.
The response includes query_hash (SHA-256 of normalized SQL for deduplication),
query_metadata (parsed from leading /* {...} */ comments), and query_subtype
(regex-derived statement classification like select/insert/copy).
It also includes database_name (the MotherDuck database = Sonar source
schema the query ran against, extracted from the iceberg_scan('s3://...')
path in the raw query text) and source_id (the Airbyte source UUID parsed
from database_name). database_name is None only when the query has no
such path; source_id is None when database_name is absent or its
trailing segment is not a canonical UUID (so database_name may be present
while source_id is None). Together they let a failed or slow query be
traced back to its owning Sonar source.
Parameters:
| Name | Type | Required | Default | Description |
|---|---|---|---|---|
filters |
object |
yes | — | Structured query filters. |
realtime |
boolean |
no | false |
If true, query RECENT_QUERIES (active + just-completed). If false, query QUERY_HISTORY (historical, slight delay). |
limit |
integer |
no | 1000 |
Number of rows to return. |
include_query_text |
boolean | object |
no | true |
Controls QUERY_TEXT inclusion. False omits entirely. True applies default treatment (1000 char limit, redact string constants). Pass a QueryTextTreatment object for fine-grained control. |
Show input JSON schema
{
"additionalProperties": false,
"properties": {
"filters": {
"description": "Structured query filters.",
"properties": {
"user_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Service account filter (e.g. 'sonar_org_a1b2c3d4')."
},
"min_start_time": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "ISO8601 lower bound on START_TIME. In historical mode (QUERY_HISTORY) this defaults to the last 24h when omitted; in realtime mode (RECENT_QUERIES) no default lower bound is applied."
},
"max_start_time": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "ISO8601 upper bound on START_TIME. When omitted, no upper bound is applied (results extend through the most recent queries)."
},
"error_only": {
"default": false,
"description": "If true, return only queries with a non-null ERROR_MESSAGE.",
"type": "boolean"
},
"min_execution_seconds": {
"anyOf": [
{
"minimum": 0,
"type": "number"
},
{
"type": "null"
}
],
"default": null,
"description": "Minimum execution_time threshold (seconds)."
},
"min_total_elapsed_seconds": {
"anyOf": [
{
"minimum": 0,
"type": "number"
},
{
"type": "null"
}
],
"default": null,
"description": "Minimum total_elapsed_time threshold (seconds)."
},
"query_text_contains": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Case-insensitive substring match on QUERY_TEXT."
},
"query_text_pattern": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Regex pattern on QUERY_TEXT. DuckDB RE2 syntax."
},
"instance_type": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Filter by duckling size (Pulse/Standard/Jumbo/Mega/Giga)."
},
"query_connection_id": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Filter by CONNECTION_ID UUID (bridge from active_connections tool)."
},
"session_name_contains": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Substring match on SESSION_NAME (typically a workspace UUID)."
},
"session_name_pattern": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Regex on SESSION_NAME. DuckDB RE2 syntax."
}
},
"type": "object"
},
"realtime": {
"default": false,
"description": "If true, query RECENT_QUERIES (active + just-completed). If false, query QUERY_HISTORY (historical, slight delay).",
"type": "boolean"
},
"limit": {
"default": 1000,
"description": "Number of rows to return.",
"type": "integer"
},
"include_query_text": {
"anyOf": [
{
"type": "boolean"
},
{
"description": "Controls how QUERY_TEXT is processed before returning to the caller.",
"properties": {
"char_limit": {
"default": 1000,
"description": "Truncate QUERY_TEXT to this many characters.",
"exclusiveMinimum": 0,
"type": "integer"
},
"redact_string_constants": {
"default": true,
"description": "Replace string literals in QUERY_TEXT with `?` placeholders.",
"type": "boolean"
}
},
"type": "object"
}
],
"default": true,
"description": "Controls QUERY_TEXT inclusion. False omits entirely. True applies default treatment (1000 char limit, redact string constants). Pass a QueryTextTreatment object for fine-grained control."
}
},
"required": [
"filters"
],
"type": "object"
}
Show output JSON schema
{
"description": "Result of a MotherDuck query history/recent queries search.",
"properties": {
"mode": {
"description": "'historical' or 'realtime' \u2014 which view was queried.",
"type": "string"
},
"returned": {
"description": "Number of rows returned (capped by `limit`). Narrow the filters or raise `limit` if you suspect there are more matching rows.",
"type": "integer"
},
"queries": {
"description": "Query records.",
"items": {
"description": "A single query record from QUERY_HISTORY or RECENT_QUERIES.",
"properties": {
"query_id": {
"description": "Unique query UUID.",
"type": "string"
},
"query_text": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Processed SQL text (may be truncated/redacted), or None if omitted."
},
"query_hash": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "SHA-256 hex of normalized query (whitespace-trimmed, metadata stripped)."
},
"query_metadata": {
"anyOf": [
{
"additionalProperties": true,
"type": "object"
},
{
"type": "null"
}
],
"description": "Parsed JSON from leading /* {...} */ comment, if present."
},
"query_type": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "MotherDuck native QUERY_TYPE (DDL/DML/QUERY/...)."
},
"query_subtype": {
"description": "Regex-derived statement classification (SELECT/INSERT/COPY/.../UNKNOWN).",
"type": "string"
},
"start_time": {
"description": "Query start timestamp.",
"type": "string"
},
"end_time": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Query end timestamp (null if still running)."
},
"is_running": {
"description": "Whether the query is still executing (derived from end_time being null).",
"type": "boolean"
},
"execution_time_seconds": {
"anyOf": [
{
"type": "number"
},
{
"type": "null"
}
],
"description": "Active execution duration in seconds."
},
"wait_time_seconds": {
"anyOf": [
{
"type": "number"
},
{
"type": "null"
}
],
"description": "Time spent waiting on resources in seconds."
},
"total_elapsed_seconds": {
"anyOf": [
{
"type": "number"
},
{
"type": "null"
}
],
"description": "Total wall-clock duration in seconds."
},
"error_message": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Error message if query failed."
},
"error_type": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Error classification."
},
"user_name": {
"description": "MotherDuck user/service account identifier.",
"type": "string"
},
"instance_type": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Duckling size that ran the query."
},
"duckling_id": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Specific duckling identifier (user_rw, user_rs.0, etc)."
},
"bytes_spilled_to_disk": {
"anyOf": [
{
"type": "integer"
},
{
"type": "null"
}
],
"description": "Bytes spilled for larger-than-memory workloads."
},
"session_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Client session name if supplied at connect time."
},
"connection_id": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"description": "Client connection UUID."
},
"database_name": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "MotherDuck database (= Sonar source schema) the query ran against, derived from the `iceberg_scan('s3://.../data/<database_name>.db/...')` path in the raw query text. `QUERY_HISTORY` exposes no native database column, so this is parsed from the query text; `None` when no such path is present."
},
"source_id": {
"anyOf": [
{
"type": "string"
},
{
"type": "null"
}
],
"default": null,
"description": "Airbyte source UUID parsed deterministically from `database_name` (Sonar format `{env_prefix}{slug}__{source_id}`, underscores mapped back to hyphens). `None` when `database_name` is absent or its trailing segment is not a canonical UUID (fails closed)."
}
},
"required": [
"query_id",
"query_text",
"query_hash",
"query_metadata",
"query_type",
"query_subtype",
"start_time",
"end_time",
"is_running",
"execution_time_seconds",
"wait_time_seconds",
"total_elapsed_seconds",
"error_message",
"error_type",
"user_name",
"instance_type",
"duckling_id",
"bytes_spilled_to_disk",
"session_name",
"connection_id"
],
"type": "object"
},
"type": "array"
}
},
"required": [
"mode",
"returned",
"queries"
],
"type": "object"
}
1# Copyright (c) 2025 Airbyte, Inc., all rights reserved. 2"""MCP tools for MotherDuck query diagnostics. 3 4Provides tools for querying MotherDuck QUERY_HISTORY, RECENT_QUERIES, 5and active server connections. Requires org admin credentials via the 6`MOTHERDUCK_ADMIN_TOKEN` environment variable. 7 8## MCP reference 9 10.. include:: ../../../docs/mcp-generated/context_store_ops.md 11 :start-line: 2 12""" 13 14from __future__ import annotations 15 16__all__: list[str] = [] 17 18from typing import Annotated 19 20from fastmcp import FastMCP 21from fastmcp_extensions import mcp_tool, register_mcp_tools 22from pydantic import Field 23 24from airbyte_ops_mcp.motherduck_diagnostics.models import ( 25 MotherDuckActiveConnectionsResult, 26 MotherDuckConnectionFilters, 27 MotherDuckQueryFilters, 28 MotherDuckQueryResult, 29 QueryTextTreatment, 30) 31from airbyte_ops_mcp.motherduck_diagnostics.queries import ( 32 query_active_connections, 33) 34from airbyte_ops_mcp.motherduck_diagnostics.queries import ( 35 query_motherduck_queries as _query_motherduck_queries, 36) 37 38 39@mcp_tool( 40 read_only=True, 41 idempotent=True, 42) 43def query_motherduck_queries( 44 filters: Annotated[ 45 MotherDuckQueryFilters, 46 Field(description="Structured query filters."), 47 ], 48 realtime: Annotated[ 49 bool, 50 Field( 51 default=False, 52 description=( 53 "If true, query RECENT_QUERIES (active + just-completed). " 54 "If false, query QUERY_HISTORY (historical, slight delay)." 55 ), 56 ), 57 ] = False, 58 limit: Annotated[ 59 int, 60 Field( 61 default=1000, 62 description="Number of rows to return.", 63 ), 64 ] = 1000, 65 include_query_text: Annotated[ 66 bool | QueryTextTreatment, 67 Field( 68 default=True, 69 description=( 70 "Controls QUERY_TEXT inclusion. " 71 "False omits entirely. " 72 "True applies default treatment (1000 char limit, redact string constants). " 73 "Pass a QueryTextTreatment object for fine-grained control." 74 ), 75 ), 76 ] = True, 77) -> MotherDuckQueryResult: 78 """Query MotherDuck query execution data for diagnostics. 79 80 Supports two modes via the `realtime` toggle: 81 82 - `realtime=false` (default): Queries QUERY_HISTORY -- historical data with 83 slight delay. Best for performance analysis, error pattern mining, cold 84 start investigation. 85 - `realtime=true`: Queries RECENT_QUERIES -- currently running + just-completed 86 queries not yet in QUERY_HISTORY. Best for live debugging, detecting stuck 87 queries. 88 89 The response includes `query_hash` (SHA-256 of normalized SQL for deduplication), 90 `query_metadata` (parsed from leading `/* {...} */` comments), and `query_subtype` 91 (regex-derived statement classification like select/insert/copy). 92 93 It also includes `database_name` (the MotherDuck database = Sonar source 94 schema the query ran against, extracted from the `iceberg_scan('s3://...')` 95 path in the raw query text) and `source_id` (the Airbyte source UUID parsed 96 from `database_name`). `database_name` is `None` only when the query has no 97 such path; `source_id` is `None` when `database_name` is absent *or* its 98 trailing segment is not a canonical UUID (so `database_name` may be present 99 while `source_id` is `None`). Together they let a failed or slow query be 100 traced back to its owning Sonar source. 101 """ 102 return _query_motherduck_queries( 103 filters, 104 realtime=realtime, 105 limit=limit, 106 include_query_text=include_query_text, 107 ) 108 109 110@mcp_tool( 111 read_only=True, 112 idempotent=True, 113) 114def query_motherduck_active_connections( 115 filters: Annotated[ 116 MotherDuckConnectionFilters, 117 Field(description="Structured connection filters."), 118 ], 119 include_query_text: Annotated[ 120 bool | QueryTextTreatment, 121 Field( 122 default=True, 123 description=( 124 "Controls client_query text inclusion. " 125 "False omits entirely. " 126 "True applies default treatment (1000 char limit, redact string constants). " 127 "Pass a QueryTextTreatment object for fine-grained control." 128 ), 129 ), 130 ] = True, 131) -> MotherDuckActiveConnectionsResult: 132 """List active MotherDuck server connections. 133 134 Calls `md_active_server_connections()` to show which ducklings currently 135 have active transactions. Useful for debugging connection pool exhaustion, 136 orphaned sessions, or verifying expected service accounts are live. 137 138 Use the `client_connection_id` from results to filter the query history 139 tool via `filters.query_connection_id` for correlated diagnostics. 140 141 The `client_query` field (currently running SQL) receives the same text 142 treatment as the query history tool: truncation, string constant redaction, 143 metadata extraction, hashing, and subtype detection. `database_name` and 144 `source_id` (the owning Sonar source database and its parsed source UUID) 145 are also derived from the raw `client_query`, so a connection can be mapped 146 to a source even when `include_query_text` omits the text. 147 """ 148 return query_active_connections(filters, include_query_text=include_query_text) 149 150 151def register_context_store_ops_tools(app: FastMCP) -> None: 152 """Register MotherDuck diagnostics tools with the FastMCP app.""" 153 register_mcp_tools(app, mcp_module=__name__)