Use inter-app agents and MCP servers¶

-- In the Financial Toolbox setup script
CREATE AGENT IF NOT EXISTS core.financial_agent
  FROM SPECIFICATION $$
  models:
    orchestration: auto

  instructions:
    system: "You are a financial analysis assistant with access to historical
             stock market data."

  tools:
    - tool_spec:
        type: "cortex_analyst_text_to_sql"
        name: "MarketData"
        description: "Query historical stock market data"

  tool_resources:
    MarketData:
      semantic_view: "core.market_data_sv"
      execution_environment:
        type: warehouse
        warehouse: "TOOLBOX_WH"
  $$;

-- Wrapper procedure that client apps call to run the agent
CREATE OR REPLACE PROCEDURE core.run_financial_agent(message STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
  EXECUTE AS OWNER
AS
$$
import json

def run(session, message):
    body = json.dumps({
        "messages": [{"role": "user", "content": [{"type": "text", "text": message}]}]
    })
    escaped = body.replace("'", "''")
    result = session.sql(
        f"SELECT SNOWFLAKE.CORTEX.DATA_AGENT_RUN("
        f"  'FINANCIAL_TOOLBOX.CORE.MARKET_ANALYST',"
        f"  '{escaped}'"
        f")"
    ).collect()
    return str(result[0][0]) if result else ''
$$;

GRANT USAGE ON PROCEDURE core.run_financial_agent(STRING) TO APPLICATION ROLE fin_app_user;

-- SPCS service running the ML forecasting model
CREATE SERVICE IF NOT EXISTS services.forecast_service
  IN COMPUTE POOL toolbox_pool
  FROM SPECIFICATION '
  spec:
    containers:
      - name: forecast
        image: /TOOLBOX_PKG/IMG/REPO/forecast-server:latest
        readinessProbe:
          port: 8081
          path: /health
    endpoints:
      - name: mcp_endpoint
        port: 8080
        public: true
  ';

GRANT SERVICE ROLE services.forecast_service!ALL_ENDPOINTS_USAGE
  TO APPLICATION ROLE fin_app_user;

-- Register the SPCS endpoint as a custom MCP server
CREATE CUSTOM MCP SERVER IF NOT EXISTS services.forecast_mcp
  SERVICE = services.forecast_service
  ENDPOINT = mcp_endpoint
  PATH = '/mcp';

GRANT USAGE ON CUSTOM MCP SERVER services.forecast_mcp TO APPLICATION ROLE fin_app_user;

-- Request the Financial Toolbox app name from the consumer
ALTER APPLICATION SET CONFIGURATION DEFINITION financial_toolbox_config
  TYPE = APPLICATION_NAME
  LABEL = 'Financial Toolbox App'
  DESCRIPTION = 'Connection to the Financial Toolbox app for market data
                 and forecasting'
  APPLICATION_ROLES = (portfolio_app_user);

-- Callback that creates the connection request and the portfolio agent
CREATE OR REPLACE PROCEDURE core.before_config_change(config_name STRING, config_value STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS
$$
def run(session, config_name, config_value):
    if config_value is None or config_name != 'FINANCIAL_TOOLBOX_CONFIG':
        return 'skipped'

    server_app = config_value
    dollar_quote = chr(36) * 2  # creating a string of "$$" this way to escape, to not interfere with the outer $$...$$

    # 1. Create the connection request
    session.sql(f"""
        ALTER APPLICATION SET SPECIFICATION CONNECTION_FINANCIAL_TOOLBOX
          TYPE = CONNECTION
          LABEL = 'Financial Toolbox App'
          DESCRIPTION = 'Connection to the Financial Toolbox app for market
                         data and forecasting'
          SERVER_APPLICATION = {server_app}
          SERVER_APPLICATION_ROLES = (fin_app_user)
    """).collect()

    # 2. Create the portfolio agent with inter-app tool references.
    #    Tool identifiers are resolved at runtime, not creation time,
    #    so the agent can be created before the connection is approved.
    session.sql(f"""
        CREATE OR REPLACE AGENT core.portfolio_agent
          FROM SPECIFICATION {dollar_quote}
          models:
            orchestration: auto

          instructions:
            system: "You are a portfolio management assistant. You can look up
                     the user's stock holdings, call the Financial Toolbox agent
                     for historical analysis, and use the forecast MCP server
                     for price predictions."

          tools:
            - tool_spec:
                type: "cortex_analyst_text_to_sql"
                name: "PortfolioLookup"
                description: "Look up the user's stock holdings and cost basis"
            - tool_spec:
                type: "generic"
                name: "FinancialToolboxAgent"
                description: "Call the Financial Toolbox agent for historical
                              stock market data"
                input_schema:
                  type: object
                  properties:
                    message:
                      type: string
                      description: "Message to send to the Financial Toolbox agent"
                  required: ["message"]

          tool_resources:
            PortfolioLookup:
              semantic_view: "core.holdings_sv"
              execution_environment:
                type: warehouse
                warehouse: "PORTFOLIO_WH"
            FinancialToolboxAgent:
              identifier: "{server_app}.CORE.RUN_FINANCIAL_AGENT"
              type: procedure
              execution_environment:
                type: warehouse
                warehouse: "PORTFOLIO_WH"

          mcp_servers:
            - server_spec:
                name: "{server_app}.SERVICES.FORECAST_MCP"
          {dollar_quote}
    """).collect()

    session.sql("""
        GRANT USAGE ON AGENT core.portfolio_agent TO APPLICATION ROLE portfolio_app_user
    """).collect()

    return 'success'
$$;

-- Approve the connection between Portfolio and Financial Toolbox
ALTER APPLICATION financial_toolbox
  APPROVE SPECIFICATION connection_financial_toolbox
  SEQUENCE_NUMBER = 1;

-- Allow PORTFOLIO_APP to manage objects in FINANCIAL_TOOLBOX app.
-- This high-level caller privilege covers the custom MCP server and its underlying service.
GRANT CALLER OBJECT MANAGEMENT ON APPLICATION FINANCIAL_TOOLBOX TO APPLICATION PORTFOLIO_APP;

-- Allow PORTFOLIO_APP to run programs in FINANCIAL_TOOLBOX app.
-- This high-level caller privilege covers the stored procedure run_financial_agent(STRING).
GRANT CALLER PROGRAM USAGE ON APPLICATION FINANCIAL_TOOLBOX TO APPLICATION PORTFOLIO_APP;