# Design - Lead ALL FIELDS Search Root Fix

**Status:** Draft for review
**Date:** 2026-06-14
**Scope:** downstream PingCAP `search_all_default_fields` ownership for Lead,
the current NexusCRM single-query `ALL FIELDS` execution contract, and
downstream validation for the Campaign Member Lead lookup.
**Relation:** companion to `docs/prds/campaign-member-lead-lookup-search.md`,
pingcap-inc/nexuscrm-pingcap-pack#2027, pingcap-inc/nexuscrm-pingcap-pack#2648,
and upstream NexusCRM PRs #2017, #2192, #2227, #2287, and #2505. This is not a
competing runtime authority for NexusCRM; it is a downstream design contract for
the PingCAP pack declaration that can safely ship on the current runtime, plus a
non-binding note on what a future upstream planner must prove.

**Milestone:** make the New Campaign Member Lead lookup searchable on staging by
keeping `Lead IN ALL FIELDS` on an explicit, pack-owned default field set that
the current single-query runtime can execute without a timeout.

## 0. Bottom Line

The mergeable root fix for #2027 is pack-owned field authority, not runtime
chunking. PingCAP Lead must stop inheriting a broad "all useful string-ish
fields" default for Campaign Member lookup. On the current runtime,
`ALL FIELDS` still becomes one combined SQL predicate and the caller-visible
limit is applied once to that predicate, so the safe downstream contract is a
small explicit Lead default:

```text
Id, Name, FirstName, LastName, Email
```

This is a correction to the earlier upstream chunking proposal. NexusCRM PR
#2505 showed that naive chunk execution is not an execution-only optimization:
even when every chunk runs, concatenating chunk results before applying `LIMIT`
changes the public result/ranking contract from "top N rows matching any field"
to "top rows from earlier field chunks first." A future upstream planner must
preserve the current global ordering/ranking semantics or introduce an explicit
metadata-owned ranking contract before replacing the single combined OR read
path.

The read path for this PR remains:

```text
search request
  -> upstream search route / dialect adapter
  -> searchmetadata.ResolveSOSLSearchFields
  -> current QueryRepository combined OR query
  -> Salesforce-shaped response
```

`ALL FIELDS` must no longer mean "every metadata-searchable field on the object"
for PingCAP Lead. It means "the ordered, pack-declared default searchable field
set for this object, executed through the runtime's existing search read path."
The field list is the owned fact in this PR; planner/ranking semantics remain
upstream future work.

The authority split is:

```text
NexusCRM owns generic search routing, field-resolution semantics, planner,
permissions, RLS, generated-field handling, and SQL execution.
nexuscrm-app-packs owns generic Salesforce CRM default search metadata.
nexuscrm-pingcap-pack owns PingCAP-specific identifiers such as Lead.Portal_ID__c
and verifies the final assembled pack/search contract.
```

## 1. Governing Principle

This repository is a downstream configuration-and-orchestration repo. It does
not own platform runtime behavior. The project rule from `CLAUDE.md` and
`docs/stack-test-model.md` is:

```text
upstream NexusCRM owns platform/runtime behavior
official nexuscrm-app-packs owns generic standard CRM pack content
nexuscrm-pingcap-pack owns PingCAP-specific inputs, final pack assembly, and
deploy orchestration
```

One search fact needs one durable source:

| Fact | Durable authority | Not authority |
|---|---|---|
| How a search request is planned and executed | upstream NexusCRM search/query services | downstream shell scripts, DB knobs, UI fallback behavior |
| Which standard CRM fields are default-searchable | official app pack metadata | hardcoded platform field allowlists |
| Which PingCAP business identifiers are default-searchable | downstream PingCAP pack metadata | broad metadata inference over every field |
| Whether a deployed pack satisfies the contract | watchdog/API/browser validation against the installed final pack | manual UAT only |

The design deliberately separates mechanism from content. NexusCRM supplies the
runtime search mechanism. Packs supply object-specific search defaults.

## 2. Current State

### 2.1 Observed failure

Issue #2027 reopened on staging build `v17807259` on 2026-06-06. The New
Campaign Member form could search Contact, but the Lead lookup returned "No
results found." Initial API probes showed:

| Probe | Result |
|---|---|
| `parameterizedSearch` Lead | HTTP 500 |
| SOSL `/search` Lead | HTTP 500 |
| `parameterizedSearch` Contact / Account | HTTP 200 |
| Lead SOQL | HTTP 200 |
| Lead suggestions API | HTTP 200 |

The failure was therefore not CampaignMember save logic, missing Lead data, or a
global outage. It was Lead search execution.

After the first downstream mitigation shipped a broader explicit Lead default,
the team rechecked staging build `v17814311` with admin credentials. The
failure persisted: direct SOQL could read the target Lead, but the frontend-shaped
SOSL request
`FIND {zhoushenghu} IN ALL FIELDS RETURNING Lead(Id, Name)` timed out with HTTP
504 after roughly 90 seconds. That corrected the diagnosis: explicit metadata
exists, but the Lead default was still too wide for the current single combined
OR execution path.

Issue #2648 later pinned the broader failure mode. On staging/prod, `Lead IN ALL
FIELDS` expanded to roughly 227 fields and produced SQL shaped like:

```sql
LOWER(field1) LIKE ?
OR LOWER(field2) LIKE ?
OR ...
OR LOWER(field227) LIKE ?
```

TiDB pushed that expression to TiKV and TiKV failed with
`WireError(OverRecursionLimit)`. That DB failure surfaced as HTTP 500. `Org__c`
worked because it had far fewer searchable fields. SOQL worked because it used
targeted predicates rather than the giant OR.

### 2.2 Code and PR evidence

| Surface | Current or recent behavior | Evidence | Target disposition |
|---|---|---|---|
| `parameterizedSearch` | POST route executes `executeSearchWithLimits(..., "ALL", ...)`, so scoped-looking parameterized probes still hit ALL-field resolution | upstream `search_parameterized.go`; seen in current checkout and PR #2017 | keep API shape, but make ALL safe |
| Legacy ALL expansion | name fields, email/phone fields, list fields, then every non-system metadata-searchable field | upstream pre-#2192 `buildSOSLSearchFields` | retire as unbounded runtime default |
| Generated/view-only fields | Lead search could include fields produced by `v_Lead`; unreadable generated aliases caused single-object 500 | upstream PR #2017 | keep narrow retry/empty behavior for retryable generated reads |
| Temporary narrow ALL | PR #2192 limited ALL to Id, NAME, and email fields to avoid wide-object failure | upstream PR #2192 | useful mitigation, not final product contract |
| Pack-defined defaults | PR #2227 added `search_all_default_fields` and a resolver with legacy fallback cap; PR #2287 verifies persisted declarations | upstream PR #2227 and #2287 | adopt as durable field authority |
| Downstream pack | current checkout has `search_all_default_fields`, but the Lead declaration still includes company/title, secondary email bags, phone, website, and business identifiers | local `packs/**/ux/search_all_default_fields.json`; staging admin probe on build `v17814311` | narrow Lead to the Campaign Member lookup corridor for the current runtime |
| Watchdog coverage | Campaign Member Lead lookup API case classifies lookup search failures as product failures | `watchdog/api/cases/campaign/campaign-member-lead-lookup-search-api.api.yaml` | keep and extend for field-default contract |

### 2.3 PingCAP Lead shape

The PingCAP Lead object is a wide object. The local downstream source pack has
340 Lead fields, including:

- 29 active formula fields, such as `BN_Email_Domain__c`, `Lead_Link__c`,
  `Lead_Owner_Name__c`, `Owner_Profile__c`, and scoring/aging formulas
- more than 200 text-like fields if string, email, phone, URL, picklist,
  textarea, rich text, and multipicklist fields are treated as searchable
- business identifier fields such as `Portal_ID__c` and `HubSpot_Id__c`

This shape makes broad metadata inference unsafe. It also shows why business
identifier search needs an explicit product/ranking contract: `Portal_ID__c` is
a real PingCAP key, but #2027's Campaign Member lookup contract is name/email,
and the current runtime does not have a ranking-preserving way to add every
useful business identifier to the default path.

## 3. Target Contract

### 3.1 Single durable runtime path

The runtime path for this PR is:

```text
request -> search route / dialect adapter -> search field resolver ->
current QueryRepository combined OR query -> wire response
```

Current competing paths and their target disposition:

| Path | Current use | Target disposition |
|---|---|---|
| Broad legacy ALL expansion | fallback when no explicit defaults exist | allowed only under a small cap; forbidden for wide objects |
| Suggestions API | narrow prefix path that can work while SOSL fails | separate feature; not proof that SOSL/lookup works |
| SOQL direct query | proves data exists | positive control only; not lookup-search authority |
| UI object-specific fallback | may explain why Org search works when SOSL fails | not a Lead search contract |
| DB recursion-limit tuning | emergency mitigation | not a product fix |

### 3.2 Native contract completeness

`search_all_default_fields` is the native field-authority contract.

Required semantics:

| Requirement | Contract |
|---|---|
| Declaration owner | source-backed pack that owns the object or downstream overlay fact |
| Field validity | all fields must exist in installed runtime metadata |
| Field allowlist | `Id` plus non-system fields that the search metadata layer allows |
| Ordering | declaration order is priority order |
| Exactness | declared objects do not silently add list fields, scope order, or every text-like field |
| Conflict handling | two active packs cannot own defaults for the same object unless a later explicit merge contract is designed |
| Missing declaration | legacy fallback allowed only when field count stays under cap |
| Wide legacy fallback | fail closed with `searchField` validation; do not emit the unbounded SQL |

For PingCAP `Lead`, the default declaration is intentionally the Campaign Member
lookup corridor: record id, person display name fields, and primary email. The
runtime currently applies `LIMIT` to the combined `ALL FIELDS` predicate; adding
secondary email bags, phone fields, website, company/title, or PingCAP business
identifiers to the default path makes the common lookup query expensive while
also broadening the default match surface beyond #2027's name/email contract.
The implemented downstream declaration is:

```text
Id
Name
FirstName
LastName
Email
```

The list should not include every scoring formula, every textarea note field,
every picklist, integration debug fields, secondary email bags, phone fields, or
business identifiers. Those are not default Campaign Member lookup keys under
the current search execution contract.

The upstream fallback cap also means other wide PingCAP objects in the same
final object spec need explicit bounded defaults when this runtime is selected.
The first downstream component therefore declares bounded defaults for
`Account`, `Contact`, `Contract`, `Lead`, `Opportunity`, `Order`, and `Quote`;
`Lead` is the incident-driving object, while the others keep pack installation
fail-closed instead of falling back to wide inference.

## 4. Key Decisions

### D-1. Search defaults are pack-authored facts, not runtime inference

`ALL FIELDS` for wide objects must read from source-backed search metadata. The
runtime may keep a legacy fallback for small/simple objects, but that fallback is
not the durable authority for PingCAP Lead.

Consequence: if a user needs to search Lead by `Portal_ID__c`, the product needs
an explicit search-ranking or surface-specific declaration before that field is
promoted into the default Lead `ALL FIELDS` lookup path. The platform should not
infer that need by scanning hundreds of fields, and the downstream pack should
not overload the default Campaign Member lookup path with every useful business
identifier.

### D-2. Runtime chunking is deferred until it preserves global result semantics

The current upstream repository path builds one SQL predicate with OR across all
active search fields, then applies the caller-visible limit once to that
combined predicate. Chunking can be a future execution strategy only if it is
observationally equivalent at the public SOSL and parameterized-search boundary,
or if the product deliberately changes that boundary with an explicit
metadata-owned ranking contract.

Rejected immediate behavior:

1. Split fields into chunks.
2. Run each chunk with the public limit or an overfetch limit.
3. Append chunk 1 rows before chunk 2 rows.
4. Deduplicate.
5. Apply the public limit to the concatenated list.

That behavior can hide records that only match later fields even when the
single OR query would have returned them in the first N rows. Dedupe removes
duplicates; it does not restore global top-N semantics.

Future upstream planner requirements:

1. Resolve fields from `search_all_default_fields` without inventing a second
   L0 priority order.
2. Preserve the unchunked global ordering/ranking before applying the public
   limit, or introduce a metadata-owned ranking primitive and update all callers
   and tests to that new contract.
3. Apply object permission, FLS, RLS/filter constraints, and requested return
   fields through the same visibility path as the current search implementation.
4. Prove with tests that a later-field match is not hidden solely because an
   earlier chunk filled the public limit.

Until that exists, this downstream PR keeps the current single-query path and
makes the PingCAP Lead field set small enough for that path.

### D-3. Term-aware priority is a future ranking contract, not this PR

A future planner should not treat every term as `%term%` over every field, but
field priority and predicate shape need a contract visible to metadata owners
before they can affect public result order.

| Term shape | First fields | Predicate preference |
|---|---|---|
| Salesforce-id-like | `Id`, reference/id-like fields | exact or prefix before contains |
| numeric/business-id-like | `Portal_ID__c`, `ID__c`, `*_ID__c`, `*_Id`, autonumber/id-like fields | exact or prefix before contains |
| email-like | email fields, then name | exact/prefix/contains in that order |
| normal text | name/compound name, company/list fields, then email/phone | prefix or contains based on UX contract |

If the column collation is already case-insensitive, a future planner should
avoid wrapping indexed columns in `LOWER(col)` for exact/prefix predicates.
Contains search remains a fallback, not the first operator for
identifier-shaped terms.

### D-4. Generated/view-only field tolerance stays narrow

PR #2017's generated-field retry rule is correct but must not become a general
error-swallowing rule.

| Error source | Behavior |
|---|---|
| Retryable generated/view-only search predicate | retry without that field when another field remains; empty result if it was the only retryable generated field |
| Requested projection is the unreadable generated field | return empty rather than mutate requested response shape |
| Physical field missing from selected read source | fail closed as internal/schema drift |
| RLS/filter predicate unknown column | fail closed |
| DB resource/recursion failure from oversized combined predicate | default field-set bug under the current runtime; reduce the installed default or design a ranking-preserving planner |

The product must not convert real schema drift into silent empty search results.

### D-5. List fields and search scope order are not search-default authority

`ListFields` answer "what should list/table surfaces display." Search scope
order answers "which objects appear in global search navigation." Neither is the
source of truth for which fields `ALL FIELDS` searches.

They may seed a migration report or a suggested declaration, but runtime must
not keep reading them as hidden policy once `search_all_default_fields` exists.

### D-6. Downstream validates the installed final pack, not raw fragments

The canonical downstream test artifact remains
`packs/NOT-EDIT-final-merged-packs` installed into a selected upstream runtime.
Validation must prove the installed runtime sees the expected Lead search
defaults and that the user journey works as `marketing-user`.

## 5. Field Authority Matrix

| Field class | Example | Owner | Default search disposition |
|---|---|---|---|
| Standard identity | `Id` | upstream/standard metadata | include |
| Standard person display | `Name`, `FirstName`, `LastName`, `Company` | official standard CRM pack | include `Name`, `FirstName`, and `LastName` for #2027; `Company` needs a separate lookup/search requirement |
| Standard contact channels | `Email`, `Phone`, `MobilePhone` | official standard CRM pack | include primary `Email` for #2027; phone fields need a separate lookup/search requirement |
| PingCAP business id | `Portal_ID__c` | downstream PingCAP pack | defer until product defines a user-facing lookup key and ranking/surface contract |
| Integration id | `HubSpot_Id__c` | downstream PingCAP/connector pack | defer unless a support/admin lookup surface explicitly owns it |
| Active scoring formula | `IQM_Quality_Score__c` | downstream metadata, upstream formula engine | do not include by default |
| Notes/text blobs | `Team_Notes__c`, `Developer_Summary__c` | downstream metadata | do not include in default lookup search |
| Debug/audit/integration text | `Unify_Metadata__c` | downstream metadata | do not include by default |

This is "document and bridge, do not infer." If a new business identifier
becomes important, add it to the pack declaration with tests rather than
expanding every string field again.

## 6. Implementation Runway

### Phase 1 - Confirm upstream metadata support

No new upstream runtime change is required for the current PR. The selected
runtime already has the `search_all_default_fields` read path from the earlier
upstream work:

- source-backed component parsing and verification
- runtime registration and persisted declaration loading
- `searchmetadata.ResolveSOSLSearchFields`
- same declared defaults used by REST and Salesforce dialect search routes

Gate:

```bash
bash scripts/build_pingcap_salesforce_sample_pack.sh --reset --from-final-pack-lineage
python3 scripts/audit_final_pack_metadata.py
```

This proves the validated lineage reproduces a final-pack mirror whose declared
defaults match the committed tree.

### Phase 2 - Downstream Lead declaration correction

Narrow the PingCAP Lead default search-field declaration in
`packs/Salesforce-PingCAP-L2`, then regenerate
`packs/NOT-EDIT-final-merged-packs` from validated final-pack lineage.

Gate:

- final-pack audit shows `Account`, `Contact`, `Contract`, `Lead`,
  `Opportunity`, `Order`, and `Quote` have explicit
  `search_all_default_fields` declarations
- Lead default is exactly `Id`, `Name`, `FirstName`, `LastName`, and `Email`
- `Portal_ID__c`, `HubSpot_Id__c`, secondary email bags, phone fields,
  website, company/title, formulas, scoring fields, note fields, and debug text
  are intentionally absent from the Campaign Member lookup default
- the final-pack contract digest matches the regenerated final pack

### Phase 3 - Staging release validation

Deploy the merged downstream main to staging and validate the actual user
journey, not only the static pack files.

Gate:

- direct SOQL positive control still finds the target Lead
- frontend-shaped SOSL
  `FIND {zhoushenghu} IN ALL FIELDS RETURNING Lead(Id, Name)` returns HTTP 200
  and includes the Lead
- New Campaign Member browser flow can search, select, and save a Lead
- validation screenshot shows the selected Lead on the NexusCRM staging UI

### Phase 4 - Future upstream planner design

If wider default search becomes required again, write a separate upstream design
or RFC before implementing planner chunking.

Minimum future gate:

- preserve the current single-OR global top-N semantics, or define a new
  metadata-owned ranking contract
- prove later-field matches are not hidden solely because earlier chunks filled
  the limit
- keep object permission, FLS, RLS, requested return fields, and error handling
  on the same visibility/fail-closed paths as current search
- validate with a wide Lead-like test and public SOSL/parameterized route tests

### Phase 5 - Future search-profile expansion

If product needs Lead search by `Portal_ID__c`, `HubSpot_Id__c`, phone, company,
or other business identifiers, add an explicit surface/search-profile contract
instead of widening the default Campaign Member lookup path.

Gate:

- product names the caller surface and expected ranking behavior
- metadata owns the field order/ranking signal
- API and browser tests cover the new key without regressing name/email lookup

## 7. Non-Goals

- Do not build a full inverted search index in this fix. That may be a later
  performance program.
- Do not make TiKV recursion-limit tuning the product fix.
- Do not turn generated-field retry into a blanket catch-and-empty behavior.
- Do not make `ListFields` or search scope order a hidden second search-field
  authority.
- Do not move generic search planner behavior into downstream scripts.
- Do not require every `__c` field to be searchable by default.

## 8. Acceptance Gates

| Gate | Proof |
|---|---|
| G1. No unbounded Lead default | installed Lead `ALL FIELDS` defaults are exactly `Id`, `Name`, `FirstName`, `LastName`, and `Email`, not the full legacy field set |
| G2. Explicit field authority | Lead default search fields come from installed pack declaration, not broad inference |
| G3. No generated-field outage | unreadable retryable generated predicates do not 500 a Lead search when healthy fields remain |
| G4. Physical drift still fails closed | missing physical columns do not become empty 200 responses |
| G5. Campaign journey works | marketing user finds and selects Lead in New Campaign Member workflow |
| G6. Business id behavior is explicit | `Portal_ID__c` is intentionally absent from the default Lead declaration and documented as not searchable by default until a separate search-ranking/profile contract exists |
| G7. Release evidence is pinned | deployed environment records upstream runtime commit plus downstream final-pack lineage |

## 9. Open Questions

1. Should `Portal_ID__c` be a standard PingCAP sales-user search key, or only a
   support/admin search key? The current answer for #2027 is "not in the default
   Campaign Member lookup declaration"; adding it later requires a narrower
   admin/search profile or explicit ranking contract.
2. What global ordering/ranking contract should a future planner preserve or
   replace before it can chunk wide searches?
3. If future planner chunking is approved, should chunks run sequentially first,
   then add bounded concurrency after correctness is proven?
4. Should declared search defaults eventually support per-profile or per-surface
   variants? This design intentionally does not add that axis.

## 10. References

- `CLAUDE.md`
- `docs/stack-test-model.md`
- `docs/prds/campaign-member-lead-lookup-search.md`
- `watchdog/api/cases/campaign/campaign-member-lead-lookup-search-api.api.yaml`
- pingcap-inc/nexuscrm-pingcap-pack#2027
- pingcap-inc/nexuscrm-pingcap-pack#2648
- pingcap-inc/NexusCRM#2017
- pingcap-inc/NexusCRM#2192
- pingcap-inc/NexusCRM#2227
- pingcap-inc/NexusCRM#2287
- pingcap-inc/NexusCRM#2505