Sf Soql

sf-soql: Salesforce SOQL Query Expert

Use this skill when the user needs SOQL/SOSL authoring or optimization: natural-language-to-query generation, relationship queries, aggregates, query-plan analysis, and performance/safety improvements for Salesforce queries.

Brite Context

Brite's object model wraps a standard Salesforce core (Account, Contact, Lead, Opportunity, Campaign, Activity) with five extensions that drive nearly every non-trivial query — three custom (Territory__c, Lifecycle_Stage_History__c, In_App_Checklist_Settings__c) and two standard objects used in Brite-specific ways (Location via FSL, AccountContactRelation). Know the four business lines (Rule 3), the FSL/Territory distinction (Rules 4-5), and the automation-written audit table (Rule 6) before writing any pipeline or attribution SOQL.

Authoritative landmarks:

• brite-salesforce/force-app/main/default/objects/ — canonical schema
• brite-salesforce/CLAUDE.md §Business Context + §Apex & Automation — behavioral gotchas
• brite-salesforce/docs/artifacts/data-dictionary.md — authoritative field reference

SOQL-specific gotchas in Rules 9, 11-13 trace to verified incidents: Rule 13 (BC-5021 before-update cascade), Rule 9 (BC-5545 Task re-parenting), Rules 11-12 (BC-5609 cross-env User + sharing).

See also: sf-apex for DML context · sf-data for execution · sf-permissions for FLS-aware queries · sf-metadata for field-shape authoring.

Brite SOQL Conventions

18 rules grounded in brite-salesforce/CLAUDE.md. Each rule cites a specific source line where possible; a few synthesized patterns (Rules 6, 18) also reference the BC-5798 issue body.

Theme A — Source + API context

1. API version 65.0 — queries inherit sourceApiVersion from sfdx-project.json. Bump via BC-issued follow-up when brite-salesforce moves to v66.0. (§Engineering Standards line 40)
2. Tooling API via useToolingApi: true — the same run_soql_query MCP tool switches to Tooling API for CustomField, ApexClass.Body, and Flow status verification. Canonical prod-deploy verification: SELECT Status FROM Flow WHERE Definition.DeveloperName = 'Flow_Name'. (§Apex & Automation lines 186-187)

Theme B — Brite object model

3. Four business lines → Opportunity RT name prefixes: Brite_Nites_* (residential), Brite_Labs_* (commercial), Brite_Supply_* (ecommerce/B2B + Brite Base), Acquisition (M&A). Scope pipeline queries with RecordType.DeveloperName LIKE 'Brite_Nites_%' rather than hand-listing RT IDs. (§Business Context lines 213-216)
4. Location (standard FSL) separates "who" (Account) from "where" (service address). Join via the AssociatedLocation junction (many-to-many), NOT a direct Account.Location__c lookup. (§Business Context line 220)
5. Territory__c is custom, NOT Enterprise Territory Management. Simple custom object for 12-15 territories. Look up via Account.Territory__c / Lead.Territory__c — do not query UserTerritory2Association / Territory2. (§Business Context line 221)
6. Lifecycle_Stage_History__c is an automation-written audit trail — read-only for SOQL purposes; never INSERT/UPDATE directly. Driven by Lifecycle_Stage__c transitions (see Rule 16). (§Metadata Authoring line 168 + issue body)

Theme C — Task & polymorphic relationship gotchas

7. SOQL semi-join from other objects to Task is not supported. Contact WHERE Id IN (SELECT WhoId FROM Task ...) fails with Entity 'Task' is not supported for semi join inner selects. Use two queries: pull WhoIds from Task first, then filter Contact by Id. (§Apex & Automation line 189)
8. Polymorphic Who/What relationships don't support dot-walking. SELECT Who.AccountId FROM Task fails because Who is the polymorphic Name entity. Prefer TYPEOF Who WHEN Contact THEN AccountId END (single query, conditional field selection). Fall back to a separate query by WhoId only when the fields needed don't fit TYPEOF's constraints or when multiple polymorphic targets complicate the branch list. (§Apex & Automation line 190)
9. Task.AccountId does NOT cascade when Contact.AccountId changes. It's set at creation from WhatId (or derived from the WhoId Contact's AccountId at that moment). To re-parent: UPDATE Task SET WhatId = :newAccountId — AccountId follows WhatId. (§Apex & Automation line 188, verified during BC-5545 contact re-parenting)
10. Activity-object field authoring — custom fields on Task/Event live in objects/Activity/fields/, but SOQL filter references still use Task.Custom_Field__c / Event.Custom_Field__c, NOT Activity.Custom_Field__c. (§Metadata Authoring line 122)

Theme D — User object cross-env + sharing

11. Cross-env User lookup: query by Email, NOT Username. Sandbox refreshes append .sandbox / .full / .preview to every User.Username; User.Email is NOT rewritten on refresh. Always pair with IsActive = TRUE and LIMIT 1 (Email is not unique). For scratch-org CI, combine with a @TestVisible override field set to UserInfo.getUserId() so the empty-query doesn't NPE. Pattern: LeadTriggerHandler.resolveWebFormOwnerId. (§Apex & Automation line 194, BC-5609)
12. with sharing does NOT restrict User object queries. User is always org-wide visible to any authenticated Apex context (including Guest and Integration Users). public with sharing on a handler that queries User is misleading — add a code comment noting the distinction. Only User is special; other standard objects respect sharing. (§Apex & Automation line 196, BC-5609)

Theme E — Trigger-context + scheduler SOQL

13. Before-update triggers querying the same object see pre-update DB state. When checking "does this Contact have other open Opps?" while closing an Opp, the closing Opp still appears open. Exclude current trigger records: AND OpportunityId NOT IN :closedLostOpps.keySet(). (§Apex & Automation line 183, BC-5021)
14. Schedulable DML row limit = 10,000 per transaction. Each query's result rows flow into downstream DML, so the DML cap constrains combined query output. DisqualifiedRecycleScheduler runs 4 queries (DQ Contacts, DQ Leads, Lost Contacts, Lost Leads) each with LIMIT 2500 so the total 10,000 rows stay within the Schedulable DML budget. Raising any individual query's LIMIT requires switching to Batchable. (§Apex & Automation line 184)
15. LeadSource is a WHERE-clause dispatcher, not just a label. Web_Form → webFormAfterInsertServices registry; Newsletter_Signup → newsletterAfterInsertServices. Any SOQL-driven Lead analytics grouping by source must use these exact picklist values. (§Apex & Automation line 177)

Theme F — Lifecycle_Stage picklist semantics

16. Lifecycle_Stage__c filters use restricted-picklist values defined at the RT level. Each record type carries explicit picklistValues blocks even though the GVS defines all values. Query side: use exact case from the GVS. Write side: Lifecycle_Stage__c is automation-only — editable only via the Sales_Operations permset — so UPDATE from arbitrary user context will silently drop the field. (§Metadata Authoring lines 128 + 168)

Theme G — Campaign Influence 2.0

17. Campaign Influence 2.0 is enabled in config/project-scratch-def.json. Use SELECT CampaignId, ContactId, Influence FROM CampaignInfluence for attribution rollups (NOT the legacy CampaignInfluenceModel). (§Metadata Authoring line 158)

Theme H — Canonical Brite query patterns

18. Composite pattern reference:
    • Territory scope: SELECT Id FROM Lead WHERE Territory__c = :terrId AND IsConverted = FALSE
    • Pipeline by business line: SELECT Id, Amount FROM Opportunity WHERE RecordType.DeveloperName LIKE 'Brite_Nites_%' AND IsClosed = FALSE
    • Task re-parenting (Rule 9): UPDATE Task SET WhatId = :acctId WHERE WhoId IN :contactIds
    • Cross-env user (Rule 11): SELECT Id FROM User WHERE Email = :e AND IsActive = TRUE LIMIT 1 (Issue body + §Business Context)

When This Skill Owns the Task

Use sf-soql when the work involves:

• .soql files
• query generation from natural language
• relationship queries and aggregate queries
• query optimization and selectivity analysis
• SOQL/SOSL syntax and governor-aware design

Delegate elsewhere when the user is:

• performing bulk data operations → sf-data
• embedding query logic inside broader Apex implementation → sf-apex
• debugging via logs rather than query shape → sf-debug

Required Context to Gather First

Ask for or infer:

• target object(s)
• fields needed
• filter criteria
• sort / limit requirements
• whether the query is for display, automation, reporting-like analysis, or Apex usage
• whether performance / selectivity is already a concern

Recommended Workflow

1. Generate the simplest correct query

Prefer:

• only needed fields
• clear WHERE criteria
• reasonable LIMIT when appropriate
• relationship depth only as deep as necessary

2. Choose the right query shape

3. Optimize for selectivity and safety

Check:

• indexed / selective filters
• no unnecessary fields
• no avoidable wildcard or scan-heavy patterns
• security enforcement expectations

4. Validate execution path if needed

If the user wants runtime verification, hand off execution to:

• sf-data

High-Signal Rules

• never use SELECT * style thinking; query only required fields
• do not query inside loops in Apex contexts
• prefer filtering in SOQL rather than post-filtering in Apex
• use aggregates for counts and grouped summaries instead of loading unnecessary records
• evaluate wildcard usage carefully; leading wildcards often defeat indexes
• account for security mode / field access requirements when queries move into Apex

Output Format

When finishing, report in this order:

1. Query purpose
2. Final SOQL/SOSL
3. Why this shape was chosen
4. Optimization or security notes
5. Execution suggestion if needed

Suggested shape:

Query goal: <summary>
Query: <soql or sosl>
Design: <relationship / aggregate / filter choices>
Notes: <selectivity, limits, security, governor awareness>
Next step: <run in sf-data or embed in Apex>

Cross-Skill Integration

Reference Map

Start here

• references/soql-syntax-reference.md
• references/query-optimization.md
• references/cli-commands.md

Specialized guidance

• references/soql-reference.md
• references/anti-patterns.md
• references/selector-patterns.md
• references/field-coverage-rules.md
• assets/

Score Guide