salesforce-soql-generator-skill

salesforce-soql-generator-skill

Pure generation skill — plain English to production-ready SOQL. This skill is a translator, not an executor. It produces ready-to-paste queries with selectivity analysis and governor-limit guidance, then hands off to salesforce-soql-explorer-skill for live execution.

When This Skill Owns the Task

Use salesforce-soql-generator-skill when the work requires query authoring from a description, not live data retrieval:

• "Write SOQL to find all opportunities closing this quarter with no next step"
• "Give me a query to count contacts by account with no email"
• "How do I query stale leads not modified in 90 days?"
• "I need aggregate SOQL for pipeline by stage and owner"
• "Generate the SOQL equivalent of this report"

Delegate elsewhere when:

| Situation | Skill to use | |---|---| | Query must be executed against a live org | salesforce-soql-explorer-skill | | User needs permission-set or profile diagnostic SOQL | salesforce-permission-model-review-skill | | Query result will drive DML or automation | Review with salesforce-metadata-review-skill first | | Apex selector pattern needed (not raw SOQL) | querying-soql (sf-skills) | | Report or dashboard conversion | Assess whether SOQL truly maps to Report; flag gap |

Required Context to Gather First

Before generating any query, confirm or infer:

1. Target sObject — API name (e.g., Opportunity, Lead, Contact,

My_Custom_Object__c). Is this a single-object or multi-level relationship query?

1. Fields needed — enumerate explicitly, or accept 'minimal' and choose

the fewest informative fields.

1. Filter criteria — WHERE conditions, date literals, owner scope, record

type, status values.

1. Sort and pagination — ORDER BY field, ASC/DESC, LIMIT, OFFSET if needed.
2. Intended use — display in UI, CSV export, automation trigger, Apex

selector, or report equivalent. Affects LIMIT choice and field set.

1. Expected result volume — rough estimate. Affects selectivity guidance.
2. Relationship depth — parent lookup traversal, child subquery, or

polymorphic relationship?

If any of these are ambiguous, make a conservative assumption, state it explicitly, and offer an alternative.

Recommended Workflow

Step 1 — Confirm target sObject and primary purpose

Identify the root sObject and clarify whether the query is for:

• Record retrieval (display, export)
• Aggregate analysis (GROUP BY, COUNT, SUM)
• Relationship traversal (parent fields from child, child subquery from parent)
• Anti-join / semi-join (NOT IN, EXISTS equivalent)

State the confirmed interpretation before generating.

Step 2 — Generate the simplest correct query

Apply these constraints from the start:

• Only enumerate required fields — no SELECT *
• Use the most selective WHERE clause available (indexed fields first)
• Apply LIMIT appropriate to the use case (default 200 for display, omit for

aggregates that return few rows, use 50000-ceiling awareness for exports)

• Traverse relationships only as deep as necessary

Produce one canonical query first, then offer variants.

Step 3 — Score selectivity

Evaluate the generated query against the selectivity rubric:

• Does the WHERE clause use at least one indexed field? (Id, Name, OwnerId,

ExternalId__c, CreatedDate, standard lookup fields, custom external IDs)

• Is a LIMIT present (where appropriate)?
• Is the field count ≤ 10?
• Are date literals used correctly (TODAY, THIS_WEEK, LAST_N_DAYS:n)?
• Are formula fields or non-indexed fields used in WHERE without a companion

indexed filter?

Document the selectivity assessment inline.

Step 4 — Add governor-limit guidance

For every generated query, provide:

• Estimated row return range and relationship to the 50,000-row limit
• Whether a LIMIT is essential or advisory
• Whether the query is safe to embed in a transaction (≤ 100 SOQL queries per

transaction ceiling awareness)

• Whether USING SCOPE or FOR REFERENCE / FOR VIEW / FOR UPDATE locking

hints apply

Step 5 — Provide variant queries

Emit at least two variants beyond the canonical query:

• Filtered variant — narrower scope (add date filter, status filter, or

owner scope)

• Aggregate variant — GROUP BY with COUNT / SUM where applicable
• Paginated variant — with LIMIT + OFFSET or a keyset-pagination pattern

using WHERE Id > :lastId when the result set could exceed one page

Step 6 — Output ready-to-paste SOQL and handoff recommendation

Emit the structured output (see Output Format). Always include a handoff_recommendation pointing to salesforce-soql-explorer-skill for live execution, or to the appropriate review skill if the query surfaces a governance concern.

Quality Scoring Rubric (100-point)

Score every generated query before emitting. Threshold: 80+ acceptable for paste-ready output; 60–79 emit with caveat; below 60 revise before emitting.

| Dimension | Points | What earns full marks | |---|---|---| | Selectivity | 30 | WHERE uses at least one indexed field; no full-table-scan pattern on objects likely > 10k records | | Field minimality | 20 | Only required fields; no SELECT *; field count ≤ 10 for display queries | | LIMIT present | 15 | LIMIT applied where result set could be unbounded; aggregate queries exempt when GROUP BY guarantees few rows | | Syntax correctness | 20 | Valid SOQL syntax; correct relationship traversal notation; correct date literal usage; no reserved word conflicts | | Governor-limit safety | 15 | Row return estimate well below 50k ceiling; embeddable in transaction; no query-in-loop anti-pattern implied |

Scoring penalties:

• No WHERE clause on a high-volume object (> 50k records likely): −20
• SELECT * pattern: −15
• Missing LIMIT on unbounded query: −15
• Formula field in WHERE without indexed companion: −10
• Incorrect date literal syntax: −10

Output Format

generated_query: |
  SELECT <fields>
  FROM <SObject>
  WHERE <filter>
  ORDER BY <field> <ASC|DESC>
  LIMIT <n>

interpretation: "<what was understood from the natural-language request>"

alternate_queries:
  filtered: |
    SELECT <fields>
    FROM <SObject>
    WHERE <narrower filter>
    LIMIT <n>
  aggregate: |
    SELECT <grouping_field>, COUNT(Id) record_count
    FROM <SObject>
    WHERE <filter>
    GROUP BY <grouping_field>
  paginated: |
    SELECT <fields>
    FROM <SObject>
    WHERE Id > :lastId AND <filter>
    ORDER BY Id ASC
    LIMIT 200

selectivity_analysis:
  indexed_filters_used: ["<field1>", "<field2>"]
  non_indexed_filters: ["<field>"]
  full_table_scan_risk: "low | medium | high"
  notes: "<explanation>"

governor_limit_notes:
  estimated_row_range: "<low estimate> – <high estimate>"
  limit_advisory: "<why this LIMIT was chosen>"
  transaction_safe: true | false
  notes: "<any ceiling-proximity warnings>"

suggested_index:
  - field: "<FieldApiName>"
    reason: "<why an index on this field would help>"

score: <0-100>
score_breakdown:
  selectivity: <0-30>
  field_minimality: <0-20>
  limit_present: <0-15>
  syntax_correctness: <0-20>
  governor_limit_safety: <0-15>
score_notes: "<what drove the score; any penalties applied>"

handoff_recommendation:
  execute_live: "salesforce-soql-explorer-skill"
  governance_review: "<skill if a concern surfaced, else 'none'>"
  notes: "<when to execute, any pre-execution checks>"

assumptions:
  - "<explicit list of assumptions made where input was ambiguous>"

Handoff Rules

| Situation | Hand off to | |---|---| | User wants to execute the generated query against a live org | salesforce-soql-explorer-skill | | Query reveals a governance or access concern | salesforce-permission-model-review-skill | | Query is a Report-equivalent but a Report likely meets the need | Note the gap; suggest the Report builder as an alternative | | Permission diagnostic SOQL needed | `salesforce-perm