tools.yaml

# MCP Toolbox Configuration for Portfolio Tracker
# Defines database sources and tools for managing stock portfolio

sources:
  portfolio-db:
    kind: cloud-sql-postgres
    # These values are replaced from environment variables at runtime
    project: ${GCP_PROJECT_ID}
    region: ${CLOUD_SQL_REGION}
    instance: ${CLOUD_SQL_INSTANCE}
    database: ${CLOUD_SQL_DB_NAME}
    user: ${CLOUD_SQL_DB_USER}
    password: ${CLOUD_SQL_DB_PASS}

tools:
  # Portfolio viewing tools
  view-portfolio:
    kind: postgres-sql
    source: portfolio-db
    description: View all current portfolio positions with total quantity, average cost basis, total invested, and realized gains
    statement: |
      SELECT 
        symbol,
        total_quantity as quantity,
        avg_cost_basis,
        total_invested,
        realized_gains
      FROM portfolio_positions
      WHERE total_quantity > 0
      ORDER BY symbol;

  view-all-positions:
    kind: postgres-sql
    source: portfolio-db
    description: View all portfolio positions including closed positions (quantity = 0)
    statement: |
      SELECT * FROM portfolio_positions
      ORDER BY symbol;

  # Transaction management tools
  add-transaction:
    kind: postgres-sql
    source: portfolio-db
    description: Add a new stock transaction (BUY or SELL). The database triggers will automatically update portfolio_positions.
    parameters:
      - name: symbol
        type: string
        description: Stock ticker symbol (e.g., AAPL, MSFT, GOOGL)
      - name: transaction_type
        type: string
        description: Type of transaction - must be either 'BUY' or 'SELL'
      - name: quantity
        type: float
        description: Number of shares
      - name: price
        type: float
        description: Price per share
      - name: transaction_date
        type: string
        description: Date of transaction in YYYY-MM-DD format
      - name: notes
        type: string
        description: Optional notes about the transaction
        required: false
    statement: |
      INSERT INTO stock_transactions (symbol, transaction_type, quantity, price, transaction_date, notes)
      VALUES ($1, $2, $3, $4, $5, $6)
      RETURNING *;

  view-transactions:
    kind: postgres-sql
    source: portfolio-db
    description: View all stock transactions ordered by date (most recent first)
    statement: |
      SELECT * FROM stock_transactions
      ORDER BY transaction_date DESC, created_at DESC;

  view-transactions-by-symbol:
    kind: postgres-sql
    source: portfolio-db
    description: View all transactions for a specific stock symbol
    parameters:
      - name: symbol
        type: string
        description: Stock ticker symbol
    statement: |
      SELECT * FROM stock_transactions
      WHERE symbol = $1
      ORDER BY transaction_date DESC;

  # Portfolio analysis tools
  calculate-portfolio-value:
    kind: postgres-sql
    source: portfolio-db
    description: Calculate total portfolio value by summing all positions' total_invested values
    statement: |
      SELECT 
        SUM(total_invested) as total_portfolio_value,
        COUNT(*) as number_of_positions,
        SUM(CASE WHEN realized_gains > 0 THEN realized_gains ELSE 0 END) as total_realized_gains,
        SUM(CASE WHEN realized_gains < 0 THEN realized_gains ELSE 0 END) as total_realized_losses
      FROM portfolio_positions
      WHERE total_quantity > 0;

  portfolio-summary:
    kind: postgres-sql
    source: portfolio-db
    description: Get a summary of the portfolio with position details
    statement: |
      SELECT 
        symbol,
        total_quantity,
        avg_cost_basis,
        total_invested,
        realized_gains,
        ROUND((total_invested / NULLIF(SUM(total_invested) OVER (), 0) * 100), 2) as allocation_pct
      FROM portfolio_positions
      WHERE total_quantity > 0
      ORDER BY total_invested DESC;

  # Stock metadata tools
  get-stock-info:
    kind: postgres-sql
    source: portfolio-db
    description: Get cached stock metadata (company name, sector, industry, market cap)
    parameters:
      - name: symbol
        type: string
        description: Stock ticker symbol
    statement: |
      SELECT * FROM stock_metadata
      WHERE symbol = $1;

  update-stock-info:
    kind: postgres-sql
    source: portfolio-db
    description: Update or insert stock metadata information
    parameters:
      - name: symbol
        type: string
        description: Stock ticker symbol
      - name: company_name
        type: string
        description: Full company name
      - name: sector
        type: string
        description: Company sector
        required: false
      - name: industry
        type: string
        description: Company industry
        required: false
      - name: market_cap
        type: float
        description: Market capitalization in USD
        required: false
    statement: |
      INSERT INTO stock_metadata (symbol, company_name, sector, industry, market_cap)
      VALUES ($1, $2, $3, $4, $5)
      ON CONFLICT (symbol) 
      DO UPDATE SET
        company_name = EXCLUDED.company_name,
        sector = EXCLUDED.sector,
        industry = EXCLUDED.industry,
        market_cap = EXCLUDED.market_cap,
        updated_at = CURRENT_TIMESTAMP;

  # Portfolio snapshot tools
  save-portfolio-snapshot:
    kind: postgres-sql
    source: portfolio-db
    description: Save a snapshot of the current portfolio value for historical tracking
    parameters:
      - name: total_value
        type: float
        description: Total portfolio value
      - name: notes
        type: string
        description: Optional notes about this snapshot
        required: false
    statement: |
      INSERT INTO portfolio_snapshots (total_value, notes)
      VALUES ($1, $2)
      RETURNING *;

  view-portfolio-history:
    kind: postgres-sql
    source: portfolio-db
    description: View historical portfolio value snapshots
    statement: |
      SELECT * FROM portfolio_snapshots
      ORDER BY snapshot_date DESC
      LIMIT 100;

  # Database introspection tools
  list-all-symbols:
    kind: postgres-sql
    source: portfolio-db
    description: List all unique stock symbols in the portfolio (including closed positions)
    statement: |
      SELECT DISTINCT symbol FROM portfolio_positions
      ORDER BY symbol;

toolsets:
  # Default toolset - includes all portfolio management tools
  portfolio-management:
    - view-portfolio
    - view-all-positions
    - add-transaction
    - view-transactions
    - view-transactions-by-symbol
    - calculate-portfolio-value
    - portfolio-summary
    - get-stock-info
    - update-stock-info
    - save-portfolio-snapshot
    - view-portfolio-history
    - list-all-symbols