rule-engine-search

Rule Search

Find applicable rules for a converted SQL file. All search happens in Snowflake — no local rule files. The search_rules MCP tool handles syncing file content and running queries so the SQL file content stays out of the agent's context window.

Prerequisites

• Rule engine set up (../SKILL.md)
• A converted SQL file to scan (e.g., snowflake/<type>/<schema>/<name>.sql)

Search for Applicable Rules

Use the search_rules tool with:

• file_path = <sql_file_path>
• description = "<brief description of the code and its migration-relevant patterns>"

Always pass description — your description of the code's structure and patterns (e.g., "procedure with dynamic SQL via sp_executesql, CONVERT with date style codes, and cursor-based iteration"). This is prepended to the structural fingerprint the tool extracts, significantly improving Cortex semantic search relevance. You've already read the code, so summarize what you see.

The tool:

1. Reads the SQL file locally
2. Syncs it to RULE_ENGINE.CODE_UNITS_SQL via MERGE (so it's available for reverse searches)
3. Runs regex pattern matching (REGEXP_LIKE) — deterministic, pattern-based hits
4. Builds a search query from your description + a structural fingerprint (migration-relevant lines extracted from the procedure body)
5. Runs Cortex semantic search (RULE_SEARCH!SEARCH) using that query — catches structural patterns regex might miss
6. Scans for EWI markers (SSC-EWI-*, SSC-FDM-*) and resolves to local reference files
7. Deduplicates rules by id (regex hits take precedence)
8. Returns a JSON object with rules (sorted by priority) and ewi_references

Output Format

{
  "rules": [
    {
      "id": "isnull-to-coalesce",
      "name": "Replace ISNULL with COALESCE",
      "description": "...",
      "replacement_mode": "regex",
      "priority": 10,
      "replacement_find": "ISNULL\\s*\\(",
      "replacement_replace": "COALESCE(",
      "ai_context": null,
      "examples": [...],
      "match_source": "regex"
    },
    {
      "id": "variable-binding",
      "name": "Variable binding colon prefix",
      "description": "...",
      "replacement_mode": "ai",
      "ai_context": "...",
      "match_source": "semantic"
    }
  ],
  "ewi_references": [
    {
      "ewi_code": "SSC-EWI-0056",
      "title": "SSC-EWI-0056 - User-Defined Types Not Supported",
      "reference_file": "/path/to/resolving-ewis/reference/SSC-EWI-0056.md"
    }
  ]
}