Module 1 — ANSWER SHEET (SEALED)

Companion to 01_Topic01_Apex_Triggers_SOQL_Limits.md — open ONLY after you have written your own attempt.

Protocol (file 00, M1→M2): write ≥2 hypotheses + 2 solution attempts before reading. Then compare your attempt against this sheet like an investigator compares a suspect's story to the evidence. The contrast IS the learning. Then REDO from memory, then do the drill.

INCIDENT 1 — THE 101 WALL

The problem restated

Unit test says 14 queries. Production says 101. Nothing changed. Where did the other 87 come from?

Model answer (the 2-minute interview version)

Mechanism: Governor limits are per transaction, not per trigger invocation. A unit test that runs the trigger in isolation counts only its own queries. Production runs the whole chain: the Lead update → your after-update trigger → convertLead() (which re-fires Lead and Contact triggers) → workflow rules with field updates (re-firing triggers a second time) → Flows — all sharing one 100-query wallet. With 200 records per API call, one transaction can contain multiple conversions, each costing ~4+ queries (the repoint custom objects) on top of the cascade.
Diagnosis: CUMULATIVE_PROFILING debug log on the integration user, reproduce with a 200-record batch, and read who spent the queries. The loop isn't necessarily in your class — it's in the chain.
Fix: Bulkify every consumer in the chain (Set → one IN query → Map → single DML). Guard convertLead with change detection. Kill the workflow re-fire (migrate to Flow or guard). Regression test = 200 records through the real entry point, asserting Limits.getQueries() < 100.
Contrast (why the "obvious fixes" failed): smaller batch = fewer records per DML, not fewer queries per conversion — it moves the wall, doesn't remove it. Selective queries fix CPU, not count.

Self-grade — you "got it" if you named:

Limits are per-transaction and shared by ALL automation
convertLead cascades into Lead + Contact trigger re-fires
Workflow/Flow re-entry as additional consumers
The unit test tested one invocation in isolation
CUMULATIVE_PROFILING as the diagnostic
A bulk 200-record regression test with Limits.getQueries() assertion

THE REDO — model answer

trigger AccountAfterUpdate on Account (after update) {
    Set<Id> ids = new Set<Id>();
    for (Account a : Trigger.new) ids.add(a.Id);

    Map<Id, Integer> counts = new Map<Id, Integer>();
    for (AggregateResult ar : [SELECT AccountId a, COUNT(Id) c FROM Contact
                                WHERE AccountId IN :ids GROUP BY AccountId]) {
        counts.put((Id) ar.get('a'), (Integer) ar.get('c'));
    }

    List<Account> toUpdate = new List<Account>();
    for (Account a : Trigger.new) {
        toUpdate.add(new Account(Id = a.Id, Contacts_Count__c = counts.get(a.Id) ?? 0));
    }
    update toUpdate;
}

Test to write: insert 200 Accounts + 200 Contacts in one transaction, assert one SOQL query pattern (≤3 total), counts correct, Accounts with zero Contacts get 0 (the ?? 0).

RETRIEVAL DRILL — model answers

What does Too many SOQL queries: 101 mean, and what is rolled back? → The 101st SOQL query fired. 100 is the limit; the failing query is #101. The entire transaction rolls back — every DML in that transaction, all records in the batch.
3 things sharing the same wallet inside one save. → Your trigger(s), other triggers on the same or related objects (fired by your DML), record-triggered Flows, workflow rule field-update re-fires. (Bonus: any Apex classes the chain calls.)
Why doesn't "reduce Data Loader batch size" fix a query-limit problem? → It changes records-per-DML, not queries-per-transaction. A bulkified fix reduces queries per record regardless of batch size; an unbulkified chain still burns queries per record and hits the wall at whatever volume.
Collect→query→map→DML for "200 Contacts change, update their Accounts' custom field":

trigger ContactAfterUpdate on Contact (after update) {
    Set<Id> accIds = new Set<Id>();
    for (Contact c : Trigger.new) if (c.AccountId != null) accIds.add(c.AccountId);

    Map<Id, Account> accounts = new Map<Id, Account>(
        [SELECT Id, My_Custom_Field__c FROM Account WHERE Id IN :accIds]);

    for (Account a : accounts.values()) a.My_Custom_Field__c = 'stale';
    update accounts.values();
}

What tool shows who spent the queries? → Debug logs with CUMULATIVE_PROFILING (or the LIMITS fields in the log); in-code: Limits.getQueries() / Limits.getLimitQueries().

INCIDENT 2 — THE TRIGGER THAT COULDN'T STOP FIRING

The problem restated

Same trigger fired 15× for one record; a static Boolean guard is present. Both true. Explain how, and fix it to fire exactly once per real stage change.

Model answer (2-min interview version)

Mechanism — two compounding re-entry sources:
1. Guard placed at the END of the method — every re-entry re-runs the logic before the flag is set. The guard as written never prevents the first echo.
2. Workflow rule with a field update (OOE step 11) re-fires after-update triggers exactly one more time — and on that second pass Trigger.old still holds the pre-workflow values, so the StageName old-vs-new check still evaluates TRUE. The change detection passed code review because it was correct — just not against the workflow's second pass.
3. Any DML inside the handler touching Opportunity compounds each pass into echoes (up to depth 16 → Maximum trigger depth exceeded).
Fix: static Set<Id> processedIds in a handler class (trigger statics reset between contexts), guard checked and set before DML; per-record granularity so legitimate work on other records in the same transaction still runs; change detection that also skips your own stamps; migrate the workflow rule to a Flow; never DML in the trigger body.
Proof: a test that calls the handler twice with the same old/new maps (asserts one log row), plus a test simulating the workflow's second pass (update the record's other field in the same transaction) asserting no second log row.

Self-grade — you "got it" if you named:

Guard set at the END as a re-entry hole
Workflow field-update re-fire (OOE step 11)
Trigger.old still showing pre-workflow values → change detection fooled
static Set<Id> over static Boolean (multi-chunk survival)
Guard in the handler class, not the trigger body
A test proving "exactly one log row"

THE REDO — model answer

public class AccountOwnerHandler {
    private static Set<Id> processedIds = new Set<Id>();

    public static void onAfterUpdate(Map<Id, Account> newMap, Map<Id, Account> oldMap) {
        List<Contact> toUpdate = new List<Contact>();
        Set<Id> accIds = new Set<Id>();
        for (Id id : newMap.keySet()) {
            if (processedIds.contains(id)) continue;
            if (newMap.get(id).OwnerId == oldMap.get(id).OwnerId) continue; // change detection
            processedIds.add(id);
            accIds.add(id);
        }
        if (accIds.isEmpty()) return;
        for (Contact c : [SELECT Id, OwnerId FROM Contact WHERE AccountId IN :accIds]) {
            c.OwnerId = newMap.get(accIds.iterator().next()).OwnerId; // map properly in real code
            toUpdate.add(c);
        }
        update toUpdate; // single DML, outside the loop
    }
}

RETRIEVAL DRILL — model answers

Order of execution one-liner: System validation → before-save Flow → before trigger → validation rules → duplicate rules → save (ID assigned) → after trigger → assignment/auto-response → workflow (field updates re-fire triggers once) → escalation → after-save Flow → rollups → criteria-based sharing → commit → post-commit (emails, async).
3 ways to prevent recursion, ranked: (1) change detection (Trigger.oldMap vs Trigger.newMap) + skip own stamps — the most robust; (2) static Set<Id> processedIds — per-record, survives chunks; (3) static Boolean isFirstRun — coarse, breaks on multi-chunk transactions. Plus discipline: no DML in trigger body, before-context for same-record stamps.
Why did Trigger.old fool the change-detection? → On the workflow's second pass, Trigger.old still holds the pre-workflow values, so the stage-change comparison remains true — the code correctly saw a "change" that was actually the workflow's own write.
What happens at OOE step 11 and what does NOT re-run? → Workflow rules with field updates run; the before/after triggers fire one more time. NOT re-run: validation rules, duplicate rules, assignment rules, auto-response rules, workflow rules (no infinite loop), escalation rules, criteria-based sharing.
Can trigger-body static variables control recursion? → No — static variables declared in the trigger body are not preserved across trigger contexts (each firing resets them). They must live in a class.

INCIDENT 3 — THE 4:00 PM CPU TIMEOUT

The problem restated

(1) Same code passes in sandbox, dies in prod — which limit, why is it a counting problem? (2) Why did enabling debug logs make it worse? (3) The actual fix?

Model answer (2-min interview version)

The limit: Apex CPU time limit exceeded — 10,000 ms (10 s) synchronous, cumulative across the whole transaction, including debug logging and managed packages.
Why sandbox ≠ prod: the code loops over every Account per Lead. Sandbox 3,000 Accounts × 200 conversions ≈ 600K iterations. Prod 1.2M Accounts × 400 conversions ≈ 480M iterations — O(n) per conversion, O(n²) overall. The limit is time, not rows — so it's a counting/algorithm problem, not a volume problem.
Why logs made it worse: log generation itself consumes CPU and counts against the same 10-second wallet (the "debug-log paradox" — the save was just under the limit until you turned logging on). The CPU limit is also a soft limit (burst allowed when the pod is idle) — which is why it's flaky.
Fix: collect all Lead websites into a Set<String> → ONE selective query WHERE Website IN :set → Map<String, Account> (normalized keys) → in-memory lookup. O(n), ~2 queries, independent of org size. Remove System.debug from hot paths; skip no-op records via change detection; measure with Limits.getCpuTime().

Self-grade — you "got it" if you named:

CPU is the limit (10 s sync), not the SOQL count
Cumulative across the transaction (logs + packages included)
O(n²) math (per-Lead full scan × conversions)
Debug logs burn CPU and count against the limit
Soft limit / burst behavior
The collect → one IN query → Map fix

THE REDO — model answer

public static void matchLeadsToAccounts(List<Lead> leads) {
    Set<String> domains = new Set<String>();
    for (Lead l : leads) if (l.Website != null) domains.add(l.Website.toLowerCase());

    Map<String, Account> byDomain = new Map<String, Account>();
    for (Account a : [SELECT Id, Website FROM Account WHERE Website IN :domains]) {
        if (a.Website != null) byDomain.put(a.Website.toLowerCase(), a);
    }
    // ... per-Lead O(1) Map lookup ...
}

Optimized: the SOQL count and the CPU (time) — untouched: heap and DML count (no DML here).

RETRIEVAL DRILL — model answers

CPU sync vs async, heap sync vs async: CPU 10 s / 60 s; heap 6 MB / 12 MB.
Map vs List for 10,000 lookups: Map = O(1) hash lookup per access; List = O(n) linear scan per access (worst case 100M comparisons).
Selective query = what, protecting which limit? → Uses an indexed field, returns <10% of first million rows, <5% beyond. Protects CPU time (and avoids QUERY_TOO_COMPLICATED / non-selective errors) — not the 100-query count.
Two reasons debug logging can cause the failure you're investigating: (a) log generation burns CPU against the same 10 s wallet; (b) raising log levels makes System.debug in the hot path more expensive per call.
Two ways to skip no-op processing in a trigger: (a) change detection (Trigger.oldMap vs Trigger.newMap — only act when the relevant field changed); (b) static Set<Id> processedIds / recursion guard. (Bonus: do same-record stamps in before-context to avoid DML entirely.)

INCIDENT 4 — THE BATCH THAT BURNED THE NIGHT

The problem restated

Three separate failures, three mechanisms, one class: (1) "First error: CPU" with no log; (2) heap 12,000,034; (3) "Completed" with 376/1,000 batches.

Model answer (2-min interview version)

"First error" + no log = failure in start(). The QueryLocator's relationship subquery ((SELECT ... FROM ChildRecords)) expands to millions of child rows — pure CPU burn inside the scheduling transaction, which shares the caller's clock. start() died before any execute(), so no chunk ever logged. Fix: flat, selective query in start(); query children per chunk in execute() (each chunk gets a fresh 200-query wallet anyway).
Heap 12,000,034 = Database.Stateful + growing JSON accumulator. Instance variables survive across chunks (that's the point of Stateful) — but heap is measured at ANY point in the transaction, so the accumulator grows unboundedly until it crosses 12 MB (async). 200K records ≈ 50 MB, so it was a time bomb. Fix: Stateful keeps small state only (counters, error lists); write per-chunk output (files/ContentVersions/callouts), never accumulate data.
"Completed" with 376/1,000 = don't trust status. Known platform bug (W-3634737) lets batches complete early without error; also, per-chunk exceptions can be silent without a BatchApexErrorEvent subscriber. Fix: finish() reconciles processed counts (AsyncApexJob.JobItemsProcessed vs TotalJobItems) and raises an alert on mismatch; register a BatchApexErrorEvent handler.

Self-grade — you "got it" if you named:

start() subquery → CPU in the scheduling transaction ("First error" prefix)
No log because start() died before execute()
Stateful + accumulating state = heap bomb at 12 MB
Small-state-only rule for Stateful
Early-completion bug / status ≠ full processing
finish() count reconciliation + BatchApexErrorEvent

THE REDO — model answer

public class NightlyExportBatch implements Database.Batchable<sObject>, Database.Stateful {
    private Integer processed = 0;
    private List<String> errors = new List<String>();

    public Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('SELECT Id, CaseNumber FROM Case WHERE IsClosed = false');
    }

    public void execute(Database.BatchableContext bc, List<sObject> scope) {
        Set<Id> ids = new Map<Id, sObject>(scope).keySet();
        // children queried HERE (fresh 200-query wallet per chunk)
        // write per-chunk output; never accumulate
        processed += scope.size();
    }

    public void finish(Database.BatchableContext bc) {
        // compare processed vs TotalJobItems; alert on mismatch; chain next job
    }
}

Monitor: AsyncApexJob.TotalJobItems, AsyncApexJob.JobItemsProcessed, AsyncApexJob.Status.

RETRIEVAL DRILL — model answers

Batch: default scope, max scope, max records, max concurrent jobs: 200 default, 2,000 max scope; QueryLocator up to 50M records; 5 concurrent jobs (100 in Flex Queue holding).
Which limits reset per execute()? → All of them: 200 SOQL, 150 DML, 60 s CPU, 12 MB heap, 100 callouts, 50,000 rows. Each chunk is a new transaction.
Two reasons a batch can "complete" without processing everything: (a) platform early-completion bug (W-3634737); (b) no reconciliation — silent per-chunk failures and no finish() count check mean partial processing looks like success. (Bonus: batch size 1 on huge data blows the 250K async executions/day limit.)
Why is Stateful + growing collection a heap bomb? → Stateful serializes instance variables across chunks; heap is measured at any point in the transaction; the collection never shrinks until it crosses 12 MB.
Can scheduled Apex call out directly? → No. Callouts require @future(callout=true), Queueable/Batch with Database.AllowsCallouts; a Schedulable must delegate to one of those.

INCIDENT 5 — THE MIXED DML MYSTERY

The problem restated

The UserRoleId = null loophole is in place. Why does it still fail? Why do 13 old tests fail in prod but not sandbox?

Model answer (2-min interview version)

Why the loophole fails: the mixed-DML rule is about transaction composition, not the User record's fields. Any other setup-object DML anywhere in the transaction — e.g., a workflow rule on the User object — is a second setup-object DML mixing with your non-setup DML (Account/Contact). The documented loophole only works when the User insert is the only setup-object operation in the transaction.
Why prod-only test failures: environment drift. Production has platform features/packages the sandbox lacks — the real case was Lightning Sync (Exchange) creating internal S2X setup-object records, tripping the restriction. Also: runAs tests that clone the running user inherit UserRoleId, so the cloned user isn't "loophole-clean."
Fix: isolate setup-object DML in its own transaction — @future, Queueable, or Platform Event handler (each runs with a fresh transaction). Audit workflow rules/flows on User. Test hygiene: clone users with UserRoleId = null in runAs tests; compare installed packages prod vs sandbox.

Self-grade — you "got it" if you named:

Rule is about transaction composition, not record fields
Workflow rule on User = second setup-object DML = loophole dead
Prod-only packages/features (Lightning Sync/S2X) as the test breaker
runAs clones inherit UserRoleId
The fix: separate transaction (@future / Queueable / Platform Event)

Architecture: Account after-insert trigger → collect → Queueable (or @future) that inserts the community User and the Contact inside the queueable's own transaction? No — careful: the User insert must be in a separate transaction from any data-object DML. Design: trigger does data-object work synchronously (nothing to do for Account itself) and enqueues a Queueable for the User creation; the Contact creation happens in the same queueable (it's a separate transaction from the trigger, so mixing User + Contact within the queueable is legal — the mixed-DML restriction applies per transaction, and a queueable's transaction has no prior non-setup DML). Test skeleton:

@IsTest
static void testCommunityUserCreation() {
    Profile p = [SELECT Id FROM Profile WHERE Name = 'Community User' LIMIT 1];
    User u = new User(LastName = 'Test', ProfileId = p.Id, UserRoleId = null, // ← the clone detail
                      UserName = 'u' + DateTime.now().getTime() + '@t.test',
                      Email = 't@t.test', Alias = 'tst');
    System.runAs(u) { /* ... or insert User here in its own transaction ... */ }
    Test.startTest();
    // fire the queueable
    Test.stopTest();
}

RETRIEVAL DRILL — model answers

5 setup objects: User, Profile, PermissionSet, Group, UserRole. (Bonus: GroupMember, Queue, Organization.)
Does with sharing fix mixed DML? → No. Sharing is about record visibility; mixed DML is a platform-level transaction-composition rule. No Apex keyword bypasses it.
Which async tools can host the User DML? → @future, Queueable, Platform Event handler; from a batch: chain a separate batch from finish().
Why do prod-only failures happen in tests? One real example: installed packages/features add setup-object activity the sandbox lacks — e.g., Lightning Sync (S2X internal objects) made 13 long-passing tests fail in prod (Winter '19).
The null-UserRoleId loophole, and when it fails: documented workaround where the created User has a null role, keeping the transaction clean; fails the moment any other automation (workflow rule, Flow, package) performs setup-object DML in the same transaction.

INCIDENT 6 — THE ONE-LINER THAT DELETED THOUSANDS

The problem restated

One line, passed review, deleted thousands. Exact mechanism + defensive design + recovery play.

Model answer (2-min interview version)

Mechanism: WHERE AccountId = :acc.Id with acc == null becomes WHERE AccountId = :null — and = :null is not an error, it's a silent no-filter: SOQL matches every record where AccountId is null. The delete removes all of them. No error, no warning.
Defensive layers (≥2): (1) null-guard first line: if (acc == null || acc.Id == null) return;; (2) count assertion before delete (log/verify within expected bounds); (3) delete through a logged, auditable process (never anonymous code at 11 PM); (4) soft-delete dry run first; (5) never hard delete without ID-list review; (6) backups with a tested restore path (the Recycle Bin is not a backup: ~15 days, storage-dependent, and hard deletes skip it entirely).
Recovery play: Recycle Bin restore — parents first, then children (children get new IDs), then re-run automation, then verify counts. Hard deletes → restore from backup, verify in sandbox first, then re-test in prod.
Testing: 3 cases — normal (happy path), acc == null (asserts nothing deleted), bulk 200 (asserts count semantics).

Self-grade — you "got it" if you named:

= :null silently matches nothing → unfiltered delete
Null-guard + count assertion + audit
Soft-delete window before hard delete
Recycle Bin ≠ backup (15 days, hard-delete bypass)
Recovery order: parents first, children second (new IDs)
Negative-path tests (null input, bulk)

THE REDO — model answer

public static void cleanupOrphans(Id accountId) {
    if (accountId == null) return;                                   // guard
    List<Contact> targets = [SELECT Id FROM Contact WHERE AccountId = :accountId];
    if (targets.isEmpty()) return;
    System.assertEquals(targets.size() <= expectedMax, true);        // count check
    Audit_Log__c log = new Audit_Log__c(Type__c = 'Cleanup', Count__c = targets.size());
    insert log;
    delete targets;                                                  // soft delete
}

Tests: (1) normal — 5 orphans deleted, audit logged; (2) null input — zero deletes, zero exceptions; (3) bulk 200 — all deleted, count asserted.

RETRIEVAL DRILL — model answers

What does WHERE AccountId = :null match? NOT IN with nulls? → = :null matches nothing (silently — no filter applied for null). NOT IN with null values in the list excludes null results (rows where the field is null evaluate unknown and are dropped) — the classic NOT IN gotcha.
Recycle Bin retention and what bypasses it: ~15 days (storage-dependent purging possible); bypassed by Database.emptyRecycleBin() and Data Loader/Bulk API Hard Delete.
Restore a master-detail hierarchy: parents first, then children (recreated children get new IDs), then re-run automation, then verify counts. (Children can't exist without their master.)
Two tools that permanently delete data: Data Loader (Hard Delete checked) / Bulk API hardDelete; Database.emptyRecycleBin().
Why is 1-record testing dangerous for delete logic? → The bug lives in the negative and bulk paths: null inputs, empty lists, 200-record chunks, and delete semantics (pre-commit visibility, cascade). 1-record happy-path tests never execute those paths.

INCIDENT 7 — THE ROLLUP THAT WENT DARK

The problem restated

(1) Why doesn't after-delete logic fire when the parent is deleted? (2) Why does the after-delete aggregate still "see" just-deleted rows? Fix both, bulkified.

Model answer (2-min interview version)

Mechanism 1 — cascade deletes bypass detail triggers: deleting the master (MD) cascades to details internally; the platform never fires the detail-object's triggers, so your rollup logic never runs. Fix: a before delete trigger on the master explicitly deletes the children — forcing their triggers to fire. (Real rollups on MD are maintained by the platform, which knows about the cascade; custom code on Lookups must handle it manually.)
Mechanism 2 — pre-commit visibility: trigger code runs before commit, so SOQL in after delete still sees the rows you deleted. Trigger.old is your only view of what's going away. Fix: exclude them — WHERE Id NOT IN :deletedIds.
Bulkified design: collect AccountIds from Trigger.new and Trigger.old (all 4 events), one aggregate query GROUP BY AccountId, build Map<Id, Decimal>, initialize defaults (?? 0 — parents with no qualifying children are absent from the map and must be reset, not left stale), single DML on Accounts.

Self-grade — you "got it" if you named:

Cascade delete doesn't fire detail triggers
Master-side before delete trigger workaround
After-delete queries see pre-commit rows
Trigger.old ID exclusion in the aggregate
Defaults initialized for parents with zero children
All 4 events (insert/update/delete/undelete) handled

THE REDO — model answer

public static void maintainAccountTotals(Set<Id> accountIds, Set<Id> deletedOppIds) {
    if (accountIds.isEmpty()) return;
    Map<Id, Decimal> totals = new Map<Id, Decimal>();
    for (AggregateResult ar : [SELECT AccountId a, SUM(Amount) s FROM Opportunity
                               WHERE AccountId IN :accountIds
                                 AND Id NOT IN :deletedOppIds
                                 AND StageName != 'Closed Lost'
                               GROUP BY AccountId]) {
        totals.put((Id) ar.get('a'), (Decimal) ar.get('s'));
    }
    List<Account> toUpdate = new List<Account>();
    for (Id id : accountIds) {
        toUpdate.add(new Account(Id = id, Total_Open_Amount__c = totals.get(id) ?? 0));
    }
    update toUpdate;
}

Cascade fix (master side):

trigger AccountBeforeDelete on Account (before delete) {
    List<Case> cases = [SELECT Id FROM Case WHERE AccountId IN :Trigger.old];
    delete cases; // fires Case triggers so their logic runs
}

RETRIEVAL DRILL — model answers

"Total Contacts per Account, only active":

SELECT AccountId a, COUNT(Id) c FROM Contact WHERE Active__c = true GROUP BY AccountId

When do grouped aggregates hit a limit, and what is it? → Grouped results cap at 2,000 rows (no queryMore for aggregate queries) — runtime error beyond that. Page by subsets or process per chunk.
Two ways to maintain a rollup on a Lookup: (a) Apex trigger on the child (all 4 events, with after-delete filtering + cascade handling); (b) DLRS (Declarative Lookup Rollup Summary) — same two bugs, plus Flow (record-triggered) for simpler cases.
Why do cascade deletes break custom rollups? → The cascade is performed internally by the platform; detail triggers don't fire, so code listening on the detail object never hears about the deletion.
In after delete, what does SOQL still see, and how do you exclude it? → The deleted rows (transaction not committed). Exclude via WHERE Id NOT IN :Trigger.old / the passed deleted-ID set.

INCIDENT 8 — THE SECURITY HOLE NOBODY SAW

The problem restated

Three separate security failures in 3 lines. Name them, their real-world impact, the secure version. What does with sharing fix — and what does it NOT fix?

Model answer (2-min interview version)

Failure 1 — no sharing keyword: Apex defaults to system mode — the class sees ALL records regardless of the running user. with sharing must be explicit. Impact: @AuraEnabled methods are callable by Guest User / anyone who can run the LWC → tenant-wide record enumeration (the real 2025 breach: PII pulled silently over a weekend, Event Monitoring off).
Failure 2 — no CRUD/FLS: with sharing alone enforces record-level sharing only. Object/field permissions are NOT enforced by Apex by default — a user can query a field they can't see in the UI. Impact: FLS-restricted fields (PII) returned without error. Fix: explicit isAccessible() checks, WITH USER_MODE on the query, Security.stripInaccessible() on results.
Failure 3 — concatenated input = SOQL injection: string concatenation into a query allows payloads like %' AND LastName != 'x — blind injection can extract arbitrary fields (real bounty: dumped a custom password field; another: platform controller with predictable IDs dumped PII and password hashes). Fix: bind variables always; String.escapeSingleQuotes() for dynamic values; whitelist object/field names (they can't be bound).
Plus: Guest User legacy profile access survives page removals — least privilege + regular audit.
with sharing fixes: record-level visibility (running user's sharing rules). It does NOT fix: CRUD/FLS, injection, or accumulation of legacy permissions.

Self-grade — you "got it" if you named:

System-mode default (no keyword = sees everything)
with sharing ≠ CRUD/FLS enforcement
Concatenated input = SOQL injection (bind variables)
Guest User legacy access
WITH USER_MODE / stripInaccessible as modern enforcement
Treat every @AuraEnabled method as public API

THE REDO — model answer

public with sharing class CaseSearch {
    @AuraEnabled(cacheable=true)
    public static List<Case> search(String term) {
        if (!Schema.sObjectType.Case.isAccessible()) return new List<Case>();
        if (!Schema.sObjectType.Case.fields.Subject.isAccessible()) return new List<Case>();
        if (String.isBlank(term)) return new List<Case>();
        String safeTerm = '%' + String.escapeSingleQuotes(term) + '%';
        List<Case> results = [SELECT Id, CaseNumber, Subject FROM Case
                              WHERE Subject LIKE :safeTerm
                              WITH USER_MODE];
        return Security.stripInaccessible(AccessType.READABLE, results).getRecords();
    }
}

Dynamic field name version: whitelist against Schema.getGlobalDescribe() before building the query — never trust raw input as a field name.

RETRIEVAL DRILL — model answers

3 sharing keywords: with sharing = enforce the running user's record-level sharing; without sharing = bypass (privileged operations only — security-review flag); inherited sharing (API 45+) = inherit the caller's mode — recommended default.
What does with sharing NOT enforce? → Object/field-level security (CRUD/FLS). A with-sharing class still returns fields the user can't read without explicit checks.
WITH SECURITY_ENFORCED vs WITH USER_MODE: SECURITY_ENFORCED covers SELECT/FROM fields only (errors on WHERE clauses, polymorphic fields); USER_MODE (API 59+) enforces full CRUD/FLS including WHERE and polymorphic references — the modern default.
Why can't you bind object/field names? → Bind variables substitute values only; object/field names are structural (compiled into the query plan). Dynamic names must be validated against Schema describe results.
Two real-world SOQL injection impacts: (a) tenant-wide record enumeration (a user with one-account access could enumerate every Account via injected OR Name LIKE '%'); (b) blind extraction of sensitive custom fields (password fields dumped via %' AND LastName != 'x payloads; platform-controller injection exposed PII and password hashes).

🏆 CAPSTONE — MODEL REPORT CHECKLIST

The module's model report (part 8 of the module file) covers the analysis. This sheet adds the two things you must be able to produce, not just read:

The 2-minute spoken answer (closed notes) — model script

"Every clue is one disease: code written for a single record and a single transaction, tested at demo volume. A is the shared wallet — the workflow re-fire from B and the convertLead-style cascades all spend the same 100 queries. B is a guard set at the end of the method plus a workflow field update that re-fires the trigger with stale Trigger.old values — so I move to a static Set<Id> guard, set it before DML, and migrate the workflow to a Flow. C is O(n²) CPU — I collect all Leads and do one IN query into a Map. D is a setup-object mixing into the Account transaction — I isolate the User insert into its own Queueable. E is the null-filter delete — 4,000 rows matched on = :null with no count check — restored from backup, and the batch gets a finish() reconciliation and a BatchApexErrorEvent subscriber so 'Completed' can never mean 'silently partial' again. Tonight I stop the bleeding; Monday I add three regression tests — a bulk 200 test asserting limits, a double-fire test asserting one log row, and a delete test asserting nothing deletes on null input."

The 3 regression tests (model sketches)

@IsTest static void bulk200_conservesLimits() {
    List<Account> accts = new List<Account>();
    for (Integer i = 0; i < 200; i++) accts.add(new Account(Name = 'B' + i));
    insert accts;   // fires all chain automations at 200-record volume
    System.assert(Limits.getQueries() < 100);
    System.assert(Limits.getDmlStatements() < 150);
}

@IsTest static void stageChange_logsExactlyOnce() {
    Opportunity o = new Opportunity(Name = 'X', StageName = 'Prospecting', CloseDate = Date.today());
    insert o;
    Test.startTest();
    o.StageName = 'Closed Won';
    update o;                 // fires trigger + workflow re-fire
    Test.stopTest();
    System.assertEquals(1, [SELECT COUNT() FROM Opportunity_Stage_Log__c WHERE Opportunity__c = :o.Id]);
}

@IsTest static void delete_nullAccount_deletesNothing() {
    Account acc = null;                       // the killer input
    Integer before = [SELECT COUNT() FROM Contact WHERE AccountId = null];
    // run the cleanup with acc.Id == null
    System.assertEquals(before, [SELECT COUNT() FROM Contact WHERE AccountId = null]);
    System.assertEquals(0, [SELECT COUNT() FROM Contact WHERE AccountId = null AND Deleted__c = true]);
}

INTERLEAVED PRACTICE SET — model answers

Which limit fires and why? (a) SOQL 101 — one query per record × 200 records in the chunk. (b) None automatically — each execute() has a fresh wallet; the failure would be 101 per chunk if the re-query sits in a loop, or heap/aggregation problems in finish() if results accumulate without Stateful discipline. (c) Heap 12 MB — the Stateful JSON accumulator grows across chunks until it crosses the async heap limit. (d) CPU 10 s — per-Lead full-table scan × 400 conversions = O(n²) iteration (480M ops).
Aggregate + Map with after-delete filter:

Map<Id, Decimal> totals = new Map<Id, Decimal>();
for (AggregateResult ar : [SELECT AccountId a, SUM(Amount) s FROM Opportunity
                           WHERE AccountId IN :accIds
                             AND Id NOT IN :deletedIds
                             AND StageName != 'Closed'
                           GROUP BY AccountId]) {
    totals.put((Id) ar.get('a'), (Decimal) ar.get('s'));
}
// ... update with totals.get(id) ?? 0, single DML ...

Trigger "runs twice" — 4 reasons, in order of likelihood, and how to confirm each:
1. Workflow rule field update (OOE step 11) — most likely when field updates exist. Confirm: debug log shows the workflow step between the two trigger fires.
2. Your own DML in the handler (recursion echo). Confirm: CUMULATIVE_PROFILING shows the DML statement and the re-entering trigger event.
3. Another automation updating the same object (ping-pong between trigger and Flow/another trigger). Confirm: log shows the other automation's DML on the same records.
4. Multiple triggers on the object (each fires — order undefined). Confirm: log shows two different trigger names.
with sharing class still returns records the user shouldn't see — what did you forget? → (1) CRUD/FLS enforcement: system mode is the default for object/field access — add explicit checks, WITH USER_MODE, or stripInaccessible; (2) legacy permissions: the user's profile/permission sets (or Guest User) may still hold object access from older automations — audit and remove. (And verify the sharing context: if called from a without sharing chain, inherited sharing semantics can leak.)
Safe bulk cleanup job design:
- Guards: null/empty input checks; only process IDs matching the intended criteria (verified by a LIMIT-bounded preview query).
- Count checks: assert expected range before any delete; log counts to an audit object.
- Soft-delete window: delete (Recycle Bin) first; hard delete (emptyRecycleBin/Bulk hardDelete) only after a review + 7-day window.
- Audit + monitoring: every run logs start/end/counts; alert on anomalies; BatchApexErrorEvent subscriber.
- Batch protections: scope 200; small Stateful state (counters only); finish() reconciles JobItemsProcessed vs TotalJobItems; idempotent re-runs (skip already-processed IDs).

Quick answer-key summary (print on one card)

101 = the 101st query; transaction rolled back. Fix: collect→query→map→DML.
Recursion = static Set<Id> + change detection, guard in handler class, before DML.
CPU = 10 s sync / 60 s async; logs count; soft limit; fix algorithmically (Map).
Batch = fresh limits per chunk; Stateful = small state only; finish() reconciliation.
Mixed DML = setup objects can't mix with data objects; separate transactions.
Null delete = = :null matches nothing; guard, count, soft-delete, backup.
Rollup on Lookup = trigger all 4 events + deleted-ID filter + cascade handling (or DLRS).
Security = with sharing (records) + WITH USER_MODE/stripInaccessible (CRUD/FLS) + bind variables (injection).

Module 1 — ANSWER SHEET (SEALED)

On this page