caddis-plugin: Instructions file for GitHub Copilot

SQL Server Standards

Query Externalization (CRITICAL)

All SQL queries that fetch data from the database MUST be externalized to the query config file path defined in project-config.md.

Do NOT:

• Write inline SQL in Python code
• Perform aggregations (COUNT, SUM, GROUP BY) in Python when database can do it
• Fetch all rows and filter in memory

# ✅ CORRECT: Define query in queries.yaml
workbasket_distribution_filtered:
  description: "Count cases by WorkbasketHeading with date filter"
  entity: pega_cases
  sql: |
    SELECT 
      ISNULL(WorkbasketHeading, 'Unknown') AS workbasket_heading,
      COUNT(*) AS count
    FROM {table}
    WHERE [Created Date Time] >= ? AND [Created Date Time] < ?
    GROUP BY WorkbasketHeading
    ORDER BY count DESC

# ✅ CORRECT: Call externalized query from repository
def get_workbasket_distribution(self):
    return self._execute_distribution_query(
        section="home_charts",
        query_name="workbasket_distribution_filtered"
    )

# ❌ WRONG: Inline SQL with Python aggregation
df = adapter.execute_query("SELECT * FROM L7DCases")
result = df.groupby("WorkbasketHeading").size()  # Never do this!

When to use in-memory processing:

• JSON file data (not in database)
• Post-processing already-returned SQL results (formatting, labeling)
• Combining results from multiple cached queries

⚠️ YAML Query Template Gotchas

Queries in queries.yaml use {table} placeholders via Python str.format(). This creates two traps:

Trap 1: Curly braces in SQL (e.g., JSON functions) must be double-escaped: {{ and }}

# ❌ BAD: Single braces interpreted as format() placeholders → KeyError
sql: |
  SELECT JSON_VALUE(data, '$.status') FROM {table}

# ✅ GOOD: Double-escape literal SQL curly braces
sql: |
  SELECT JSON_VALUE(data, '$[0]."status"') FROM {table}

Trap 2: Every query entry MUST include an entity: key that maps to the table configuration:

# ✅ CORRECT: entity key present → table name resolved from config
workbasket_distribution:
  description: "Count by workbasket"
  entity: pega_cases              # ← REQUIRED
  sql: |
    SELECT WorkbasketHeading, COUNT(*) AS count
    FROM {table}
    GROUP BY WorkbasketHeading

# ❌ WRONG: Missing entity key → KeyError at runtime
workbasket_distribution:
  description: "Count by workbasket"
  sql: |
    SELECT WorkbasketHeading, COUNT(*) FROM {table}