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:

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?
2. Fields needed — enumerate explicitly, or accept 'minimal' and choose the fewest informative fields.
3. Filter criteria — WHERE conditions, date literals, owner scope, record type, status values.
4. Sort and pagination — ORDER BY field, ASC/DESC, LIMIT, OFFSET if needed.
5. Intended use — display in UI, CSV export, automation trigger, Apex selector, or report equivalent. Affects LIMIT choice and field set.
6. Expected result volume — rough estimate. Affects selectivity guidance.
7. 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.

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

Common Pitfalls

These are the most frequent errors in generated SOQL. Check each before emitting the final query.

SELECT * pattern

SOQL does not support SELECT *. Always enumerate fields. When fields are unknown, use a minimal set (Id, Name, one or two distinguishing fields) and note that the user should expand once the schema is confirmed.

Missing LIMIT

Any query on a high-volume object without a LIMIT risks hitting the 50,000-row governor ceiling or causing a query timeout. Always include LIMIT unless:

• The query is a COUNT aggregate with GROUP BY that produces few groups
• The user has explicitly confirmed they need all records and the object has low cardinality

Non-indexed filters

Filtering on formula fields, long-text-area fields, or custom fields without an explicit custom index is a common performance trap. Always flag these in selectivity_analysis.non_indexed_filters and recommend that the user check with their Salesforce admin whether a custom index exists or is needed.

Date literals

Use Salesforce date literal syntax precisely:

• TODAY, YESTERDAY, TOMORROW
• THIS_WEEK, LAST_WEEK, NEXT_WEEK
• THIS_MONTH, LAST_MONTH, NEXT_MONTH
• THIS_QUARTER, LAST_QUARTER, NEXT_QUARTER
• THIS_YEAR, LAST_YEAR, NEXT_YEAR
• LAST_N_DAYS:n, NEXT_N_DAYS:n
• LAST_N_WEEKS:n, NEXT_N_WEEKS:n
• LAST_N_MONTHS:n, NEXT_N_MONTHS:n

Do not mix date literals with datetime comparisons without explicit timezone handling. Salesforce date literals respect the running user's timezone.

Formula fields in WHERE

Formula fields are not indexed and cannot use standard indexes. Including a formula field in a WHERE clause without a companion indexed filter forces a full table scan. Flag this in selectivity_analysis and suggest an alternative (e.g., use the underlying source fields instead of the formula).

Polymorphic relationships

Querying the Who or What fields on Task / Event, or the Owner field when it can be a User or Queue, requires a TYPEOF expression or a Type filter. Incorrect assumptions produce incomplete results. Always clarify whether the relationship is polymorphic and use TYPEOF where needed.

Deep relationship traversal

SOQL allows up to 5 levels of parent traversal and up to 1 level of child subquery nesting (for relationship queries). Beyond these limits, the query will fail at runtime. Flag any traversal depth that approaches the limit.

Reference File Index