Skip to content

sql-generation-rules.md

Latest commit

 

History

History
143 lines (104 loc) · 7.78 KB

sql-generation-rules.md

File metadata and controls

143 lines (104 loc) · 7.78 KB
Raw

SQL Generation Rules

These rules apply to ALL SQL generated by Claude across all database types. Database-specific syntax variations are in dialect-rules.md.

Contents

  • Critical Rules
  • Growth / Period-Over-Period Queries
  • Foreign Key Handling
  • Safety Rules
  • Performance Hints

Critical Rules

1. No ID Fields in SELECT

NEVER include ID fields (sys_id, dept_id, user_id, or any column ending in _id) in SELECT clauses. Always JOIN to the lookup table and SELECT the human-readable name/label field instead.

-- WRONG
SELECT dept_id, COUNT(*) FROM incidents GROUP BY dept_id

-- CORRECT
SELECT d.name AS department_name, COUNT(*) AS incident_count
FROM incidents i
JOIN departments d ON i.dept_id = d.id
GROUP BY d.name

2. Always JOIN for Names

When a query mentions departments, users, categories, etc., JOIN to the referenced table and SELECT the name field. Never return raw ID values to the user.

3. Choice Field Handling

Choice fields (columns with a fixed set of values like status, priority, type) may store labels directly OR numeric codes depending on the database.

Check the semantic model: If choice_field in the YAML maps {"1": "New", "2": "In Progress", ...} (numeric keys), use codes in WHERE and CASE in SELECT. If choice_field maps {"Open": "Open", "Closed": "Closed"} (string keys matching values), use labels directly.

Numeric codes (Redshift pattern):

  • WHERE: Use numeric codes — WHERE status = 1
  • SELECT: Use CASE expressions — CASE status WHEN 1 THEN 'New' WHEN 2 THEN 'In Progress' END AS status_label
  • GROUP BY: Group by raw column, SELECT only the CASE label

String labels (PostgreSQL pattern):

  • WHERE: Use the label directly — WHERE status = 'Open'
  • SELECT: Use the column directly — SELECT status

Do NOT embed numeric codes in labels (wrong: WHEN 1 THEN '1 - Critical'; correct: WHEN 1 THEN 'Critical')

4. Avoid Cartesian Products

When aggregating from multiple child tables (e.g., counting incidents AND counting changes for each department), pre-aggregate each child table in a CTE or subquery, then JOIN the results. Never JOIN multiple un-aggregated child tables to the same parent.

-- WRONG: Cartesian product multiplies counts
SELECT d.name, COUNT(DISTINCT i.id), COUNT(DISTINCT c.id)
FROM departments d
LEFT JOIN incidents i ON i.dept_id = d.id
LEFT JOIN changes c ON c.dept_id = d.id
GROUP BY d.name

-- CORRECT: Pre-aggregate, then join
WITH inc AS (SELECT dept_id, COUNT(*) AS incident_count FROM incidents GROUP BY dept_id),
     chg AS (SELECT dept_id, COUNT(*) AS change_count FROM changes GROUP BY dept_id)
SELECT d.name, COALESCE(inc.incident_count, 0), COALESCE(chg.change_count, 0)
FROM departments d
LEFT JOIN inc ON inc.dept_id = d.id
LEFT JOIN chg ON chg.dept_id = d.id

5. Only Use Columns That Exist

Check the semantic model schema carefully. Do NOT assume columns exist. If a column is not listed in the table's YAML, do not use it in the query.

6. Unique Table Aliases

Every table alias in FROM/JOIN must be unique. When joining the same table multiple times, use different aliases (e.g., caller and assignee for two joins to a user table).

7. UNION/UNION ALL Column Count

Every SELECT in a UNION or UNION ALL MUST have exactly the same number of columns with compatible types.

8. Select Only Relevant Columns

Analyze the user's question and SELECT only columns that directly answer it. Do NOT select every column from the table. Less is more.

9. Daily-Snapshot Tables

If a table's description identifies it as a daily/periodic snapshot (phrases like "daily snapshot", "one row per X per day", or a refresh column like report_date, snapshot_date, as_of_date), EVERY query against that table MUST filter to the latest snapshot:

WHERE t.snapshot_date = (SELECT MAX(snapshot_date) FROM schema.table)

Omit this filter ONLY when the user explicitly asks for historical trends ("trend over time", "month-by-month", "how has X changed").

10. Deterministic Column Aliases

Use predictable alias patterns:

Type Alias pattern Examples
Temporal year, month_start, quarter_start, week_start DATE_TRUNC('month', date) AS month_start
Day of week day_of_week, day_of_week_label
Choice labels FIELDNAME_label priority_label, status_label
Counts Must end with _count and describe what's being counted. Never use bare n, N, cnt, or count — they render as un-readable column headers. incident_count, user_count, applicant_count (NOT n / cnt / count)
Amounts Must end with _amount total_revenue_amount
Percentages Must end with _percent or _percentage growth_percentage

Growth / Period-Over-Period Queries

For growth rates (QoQ, MoM, YoY):

  1. Use a CTE to aggregate per period first, then apply LAG() in the outer query
  2. Do NOT expose intermediate LAG columns (e.g., previous_quarter_revenue) as output
  3. Growth percentage alias must end with _percentage
  4. Produce ONE combined period column (not separate year + quarter): 
    TO_CHAR(DATE_TRUNC('quarter', date_col), 'YYYY') || '-Q' || EXTRACT(QUARTER FROM date_col) AS quarter_start
  5. Final output: period column, base metric, growth percentage

Foreign Key Handling

  1. When to JOIN: Use LEFT JOIN when the query asks for info from the referenced table
  2. JOIN syntax: FK field joins to the target table's primary key
  3. SELECT names, not IDs: Always SELECT the name/label from the joined table
  4. Multiple FKs to same table: Use different aliases

Safety Rules

  • Result-set size policy — do NOT auto-append LIMIT 1000 (or any other implicit cap): write the LIMIT the question actually calls for, or none. There is a deployment ceiling on how many rows one result may carry (AGAMI_SQL_MAX_ROWS, default 1000), and a result that exceeds it is refused, not trimmed — you get a structured refusal and no rows, never a partial answer that reads as a whole one. So: add a LIMIT (with an ORDER BY, or the rows you get are arbitrary) when the user asks for "top N" / "first N" / "the largest", or when you expect a long row listing and only need a sample. For an aggregate, do not reach for LIMIT if the refusal comes back — a LIMIT on a grouped result drops groups and the breakdown will look complete when it is not; narrow the grouping or add a filter instead. The refusal's remediation says which of the two applies to the statement you sent.
  • Never generate DROP, DELETE, TRUNCATE, ALTER, INSERT, UPDATE, or CREATE statements
  • Never include actual credential values in SQL comments or strings
  • Use NULLIF(denominator, 0) to guard against division by zero

These are enforced, not just guidance. Every query runs through a read-only gate (sql_guard, at the executor chokepoint) that rejects anything other than a single SELECT / WITH...SELECT — multi-statement SQL, data-modifying CTEs, transaction-control / session-state / prepared statements, SELECT ... INTO, row-level locks, and dangerous server-side functions (pg_read_file, lo_export, dblink, copy_program, pg_sleep, advisory locks, query_to_xml, …). A rejected query never reaches the database. Generate read-only SQL and this gate stays invisible.

Performance Hints

If the semantic model includes performance_hints for a table:

  • recommended_filters: Always filter on these columns when the table has estimated_row_count > 1000000. These are sort keys (Redshift) or indexed columns (PostgreSQL) that dramatically reduce scan time.
  • Large tables: For tables with millions of rows, add date range filters even if the user doesn't explicitly ask for one. Default to last 12 months if no time frame is specified.