Reviewing SOQL for readability and risk before it ships

A query returns the right rows against twenty test records and gets approved. Three months later it runs against forty thousand records inside a trigger that fires on every save, and something that looked fine in review is now the reason a batch job times out. The failure mode isn’t wrong logic. It’s logic that was only ever tested at a scale where the risk doesn’t show up.

---

What a review actually needs to check

Four separate risks, and a query can pass three of them and still fail:

1. Does it return the right rows? Correctness, the thing everyone checks.
2. Does it scale? What happens at ten thousand records instead of ten.
3. Is it selective? Whether it can use an index, or has to scan every record on the object.
4. Is the intent obvious to the next reader? A query nobody can explain a year from now is a query nobody will safely change.

---

Step 1: give it the query and where it runs

Context changes what’s risky. A query in an anonymous script tolerates things a query inside a trigger cannot.

Object(s): [what this queries]
Where this runs: [trigger, Flow, batch class, scheduled job, report, LWC]
Approximate record volume on the object(s): [rough order of magnitude]
The query:
[paste it exactly]

---

Step 2: ask for the specific risks, not a general opinion

Review this SOQL query for the four things below. For each one, say
pass, risk, or fail, with the specific line or clause and why:

1. Selectivity: does the WHERE clause use an indexed or otherwise
   selective field, or would this require a full table scan at scale?
2. Governor limit risk: is this inside a loop, and could it run more
   than once per transaction?
3. Bulk safety: does it handle zero results, and multiple records,
   correctly, or does it assume exactly one?
4. Clarity: could someone unfamiliar with this object read the WHERE
   clause and understand what it's selecting for, without you explaining it?

Don't suggest a rewrite yet. List findings only.

Asking for findings before a rewrite matters here more than in most prompts. A rewrite offered immediately tends to fix the first problem it notices and quietly ignore the rest.

---

Step 3: verify the loop claim specifically

Point 2, whether the query sits inside a loop, is the single most expensive mistake to miss and the easiest for AI to get right or wrong depending on how much surrounding code it can see. If the query is inside a class or trigger, paste the method or trigger body, not just the query line, so it can actually see whether the query executes once per transaction or once per record.

Here's the full method this query lives in:
[paste it]

Confirm: does this query execute exactly once per transaction, or
could it run once per loop iteration? Show me the specific lines
that determine this.

A query correctly declared safe when reviewed in isolation and declared unsafe once the surrounding loop is visible is not a contradiction, it’s the review working as intended the second time.

---

Step 4: ask for the rewrite, with the findings restated

Given these confirmed findings: [paste what came back from steps 2 and 3]

Rewrite the query to address them. Add a one-line comment above it
explaining what it selects for and why, in plain language, for
someone who doesn't already know this object.

---

When it breaks

It calls a query selective when the field isn’t actually indexed in your org. Standard indexed fields (ID, Name, most lookup and master-detail fields, external IDs) are a safe default assumption; a custom field’s index status is an org-specific fact AI can’t see and shouldn’t guess at. Confirm it in Setup before trusting the claim.

It misses a loop because the query and the loop are in different methods. If a query sits in a helper method called from inside a loop, give it both methods together. A confident “this runs once” that only looked at the query’s own method is a false negative, not a real finding.

The rewrite is correct but changes behavior at an edge case you didn’t ask about. Bulkifying a query sometimes changes what happens with zero related records or duplicate keys. Test the rewrite against an edge case explicitly, not just the happy path that passed before.

The query is inside a managed package or something you can’t safely change. Then this review is diagnostic, not actionable. Document what you found and route it to whoever owns that package rather than patching around it.

---

What to try next

Run this pass before a query goes into anything that fires on save or runs in bulk, not only when something’s already broken. The four-question structure is fast enough to become a five-minute habit rather than an incident postmortem.