AssetDB Ad-Hoc SQL Queries

Relevant source files

The following files were used as context for generating this wiki page:

• stacklet/mcp/assetdb/tools.py
• stacklet/mcp/assetdb/sql_info.md
• stacklet/mcp/assetdb/redash.py
• stacklet/mcp/assetdb/models.py
• tests/test_tools_assetdb.py
• stacklet/mcp/mcp_info.md
• CLAUDE.md

AssetDB Ad-Hoc SQL Queries provide a mechanism to execute custom PostgreSQL 16 queries directly against Stacklet's cloud asset data warehouse. This feature enables developers and automated agents to perform complex multi-cloud discovery, cost analysis, and relationship mapping across AWS, GCP, Azure, and Tencent environments. Sources: stacklet/mcp/mcp_info.md:12-21, stacklet/mcp/assetdb/sql_info.md:1-5

The system exposes these capabilities via the assetdb_sql_query tool, which interfaces with a Redash-backed API to process raw SQL strings. It includes built-in safety mechanisms such as automatic row limiting and support for asynchronous job polling for long-running analytical queries. Sources: stacklet/mcp/assetdb/tools.py:270-296, stacklet/mcp/assetdb/redash.py:145-160

Architecture and Execution Flow

The ad-hoc query process involves a coordination between the MCP tool layer, the AssetDBClient, and the Redash API. When a user submits a query, the system first resolves the appropriate data_source_id before dispatching the request.

Query Execution Sequence

The following diagram illustrates the lifecycle of an ad-hoc SQL query, handling both synchronous immediate returns and asynchronous background jobs.

The diagram shows how the client manages the transition from a Redash Job to a completed Query Result through exponential backoff polling. Sources: stacklet/mcp/assetdb/redash.py:145-215, stacklet/mcp/assetdb/tools.py:321-380

Core Components

Component	Responsibility
assetdb_sql_query	Tool entry point; handles JSON guarding and output schema formatting.
AssetDBClient	Manages HTTP sessions, authentication cookies, and Redash job polling.
JobStatus	Enum defining terminal states: FINISHED, FAILED, and CANCELED.
QueryResult	Data model for Redash responses containing columns, rows, and runtime metrics.
ToolQueryResult	Optimized model for LLM consumption, providing data previews and file links.

Sources: stacklet/mcp/assetdb/tools.py:270, stacklet/mcp/assetdb/redash.py:26, stacklet/mcp/assetdb/models.py:27-210

Query Parameters and Options

The assetdb_sql_query tool accepts specific parameters to control performance and data retrieval behavior.

@json_guard
async def assetdb_sql_query(
    ctx: Context,
    query: Annotated[str, Field(min_length=1)],
    max_age: Annotated[int, Field(ge=-1, default=3600)],
    timeout: Annotated[int, Field(ge=5, le=300, default=60)],
) -> ToolResult:

Sources: stacklet/mcp/assetdb/tools.py:270-281

Parameter	Type	Default	Description
query	str	N/A	The raw SQL query string to execute against the warehouse.
max_age	int	3600	Max age of cached results in seconds. Use 0 for fresh data, -1 for any cache.
timeout	int	60	Execution timeout in seconds (maximum 300s).
apply_auto_limit	bool	True	Internal flag used by execute_adhoc_query to prevent massive data dumps.

Sources: stacklet/mcp/assetdb/tools.py:270-281, stacklet/mcp/assetdb/redash.py:155

Data Safety and Performance

AssetDB contains large-scale datasets, necessitating strict adherence to performance best practices.

• Row Limits: Ad-hoc queries automatically apply limits. Users must manually use LIMIT and OFFSET clauses for manual pagination within the SQL string. Sources: stacklet/mcp/assetdb/sql_info.md:7-9, stacklet/mcp/assetdb/redash.py:155
• Indexing: Always filter by indexed columns (e.g., _account_id, _type) to avoid full table scans on the resources table. Sources: stacklet/mcp/assetdb/sql_info.md:13-26
• Explain Plan: Use EXPLAIN to verify index usage before executing queries against "Very Large" tables like resource_revisions. Sources: stacklet/mcp/assetdb/sql_info.md:46-51

Table Scale Reference

Table	Purpose	Scale	Recommendation
resources	Current resource JSON	Very Large	Use indexed filters
resource_revisions	Resource history	Extremely Large	Primary key access only
aws_s3, gcp_gke_cluster	Provider specific	Large	Preferred over raw JSON
account_cost_monthly	Monthly costs	Medium	Best starting point for costs

Sources: stacklet/mcp/assetdb/sql_info.md:13-26

Result Handling and Artifacts

The system processes query results to ensure efficiency in LLM context windows while preserving full data access.

Result Truncation and Persistence

1. Inline Preview: The tool returns only the first 20 rows (some_rows) in the primary response to reduce token usage.
2. Full Results: The complete dataset is handled based on the downloads_enabled setting:
   • Enabled: The server writes a JSON array to STACKLET_MCP_DOWNLOADS_PATH. The tool returns the file path in full_results_saved_to.
   • Disabled: The server attaches the full JSON array as an EmbeddedResource (URI: assetdb://query_results/{id}.json).
3. Alternate Formats: Ad-hoc queries do not provide direct download links for CSV/XLSX. To access these formats, you must save the query using assetdb_query_save and use assetdb_query_result.

Sources: stacklet/mcp/assetdb/tools.py:330-380, stacklet/mcp/assetdb/models.py:210-250, tests/test_tools_assetdb.py:465-485

Conclusion

AssetDB Ad-Hoc SQL Queries serve as the primary tool for custom cloud data exploration within the Stacklet MCP environment. By combining PostgreSQL flexibility with managed Redash execution, the system balances the need for deep analytical access with performance and security constraints. Users should prioritize the use of indexed filters and the assetdb_sql_info guide to ensure efficient operation against large-scale cloud datasets. Sources: stacklet/mcp/assetdb/tools.py:284-296, stacklet/mcp/assetdb/sql_info.md:40-55