Skill: Secure Data Ticket Workflow (Master SOP)

Description

The single authoritative entry point for handling Jira data requests.
This workflow is designed with Privacy-First Engineering, utilizing a local LLM to sanitize sensitive data before it reaches the cloud context. It enforces strict semantic grounding and conflict resolution protocols.

Prerequisites

• Runtime: Must be executed within the active .venv (Virtual Environment).
• Local Services: Local Ollama instance (e.g., Llama3) must be running on port 11434 for PII sanitization.
• MCP Servers: Snowflake and GitHub MCP servers must be active via .claude/config.json.

Master SOP (Standard Operating Procedure)

Step 1: Secure Context Retrieval

• Objective: Retrieve the Jira requirement and perform local-side PII sanitization.
• Action: Invoke the tool_jira_privacy skill.
• Command: python .claude/skills/tool_jira_privacy/scripts/fetch_sanitize.py --id {TICKET_ID}
• Constraint: You must ONLY utilize the sanitized_request output from this tool. Do not attempt to fetch raw Jira data directly via cloud APIs.

Step 2: Semantic Grounding (Layer 2)

• Objective: Map business intent to valid Database Entities.
• Action: Read the reference file: .claude/skills/0_MASTER_SOP/references/business_glossary.csv.
• Logic:
  • Glossary Hit: If terms (e.g., "Revenue", "Churn") exist in the glossary, you MUST adopt the defined Table Name and SQL Logic.
  • Glossary Miss: If terms are undefined, initiate the [Discovery Protocol]: use the Snowflake MCP to query INFORMATION_SCHEMA for metadata. Do not guess table names.

Step 3: Impact Analysis & Conflict Resolution

• Objective: Reconcile differences between the Request (Jira), the Current State (GitHub), and the Reality (Snowflake).
• Actions:
  1. Use GitHub MCP (search_code) to retrieve existing SQL logic.
  2. Use Snowflake MCP (get_schema or describe_table) to verify actual column existence.
• Resolution Protocol (Hierarchy of Truth):
  • Scenario A: Business Logic Conflict (Jira vs. GitHub)
    • Context: GitHub code defines logic (e.g., VIP > 5000) but Jira requests a change (e.g., VIP > 10000).
    • Resolution: Jira is the Truth.
    • Action: You must update the SQL logic to match Jira and initiate a GitHub Pull Request with the changes.
  • Scenario B: Technical Discrepancy (Snowflake vs. GitHub)
    • Context: GitHub code references columns that do not exist in the Snowflake Schema.
    • Resolution: Snowflake DB is the Truth.
    • Action: You must refactor the query to match the actual Snowflake Schema.

Step 4: Coding & Guardrails

• Objective: Draft the SQL query and perform static safety analysis locally.
• Action: Generate the SQL draft but DO NOT EXECUTE it yet.
• Validation: Invoke the tool_sql_guard skill.
• Command: python .claude/skills/tool_sql_guard/scripts/validator.py --sql "{SQL_DRAFT}"

Step 5: Execution & Delivery

• Objective: Execute valid queries and report results.
• Condition: Proceed only if Step 4 returns "PASS".
• Action: Use Snowflake MCP (run_query) to execute the final SQL.
• Output: Provide a summary of the results, the final SQL used, and the link to the created GitHub Pull Request (if applicable).