# Account / Lead Industry → Vertical Cross-Dependency — NexusCRM Design

**Objects:** `Account.Vertical__c`, `Lead.Vertical__c` (controlled by `Industry`)
**Status:** Proposal — supersedes the docs-only plan in PR #2307
**Date:** 2026-07-01
**Grounded in:** the SFDC as-built spec (`account-industry-vertical-dependency-design.md`) + a live read of the NexusCRM engine (`D:\Git_Projects\NexusCRM`, commit `519518d`)

---

## 0. Decision — RESOLVED

There are two viable ways to deliver Industry→Vertical filtering on NexusCRM. **Decision (2026-07-01): Option B — native `runtime_scoped_picklist` over a mapping object, downstream-only, ships on today's engine.** Option A (SFDC-faithful, upstream L0) is retained in §5 as the considered-and-deferred alternative to revisit if upstream invests. Implementation plan follows the recommended design in §6.

---

## 1. Purpose

On `Account` (and `Lead`), **`Vertical__c` is a dependent picklist controlled by `Industry`**. When a user picks an Industry, the Vertical field must filter to only the verticals mapped to that Industry. In Salesforce this is a native field dependency (`<valueSettings>` bitmap); see the SFDC spec for the as-built source. The mapping must stay identical on Account and Lead so Lead-Convert never produces an invalid pair.

**This is a runtime behavior, not a data question.** Having the right picklist *values* present (the subject of PR #3561) is necessary but not sufficient — the engine must actually filter the dependent field.

## 2. The AI Industry change (must be baked into whichever option)

The current business mapping (Feishu source of truth) promoted AI from a single flat vertical to a first-class industry:

- **Removed:** flat vertical `AI` (previously under `ISV/SaaS-Software`).
- **Added:** industry **`Artificial Intelligence`** → verticals `AI – LLM & MaaS`, `AI – Agents & Orchestration`, `AI – Native Applications`, `AI – Hardware & Robotics`.

Result: **15 industries**. Account already carries this in the pack; Lead was brought to parity in PR #3561. Any dependency implementation must reflect the 15-industry / AI-4-vertical shape, **not** the older flat-AI mapping.

---

## 3. Ground truth — how NexusCRM actually handles dependent picklists

The engine has **two unrelated representations** of a picklist dependency. This is the crux of the whole problem.

### 3a. Representation ①  — static `picklistDependency` map (what the pack declares today)

- Field metadata carries `ControllingField` + `PicklistDependency` (`map[controllingValue][]dependentValue`), stored on `_System_Field` (`schema_helpers.go:238-239`; model `shared/pkg/models/metadata.go:211`).
- There is a companion table `_System_FieldDependency` (`Id, ControllingFieldId, DependentFieldId, ControllingValue, DependentValues[json]`) — `metadata_schema.go:438-446`.
- **Install never materializes `_System_FieldDependency` rows.** Pack install writes the `PicklistDependency` JSON onto the field and stops; no code path expands it into rows (Explore trace: "NONE FOUND"; the only inserts are in test fixtures). The table is populated only by hand (the 2026-06-02 staging SQL backfill).
- **The create/edit UI never reads representation ①.** `MetadataRecordForm.tsx` does not reference `controllerName`, `picklistDependency`, or `validFor` anywhere — those exist only as dormant type fields (`types/sobject.ts:70,71,115`). The describe DTO emits a `dependentPicklist` bool + `controllerName` string but **not** the map itself, and no bitmap `validFor` is computed.

> **Consequence:** the pack's current `Vertical__c` (a plain picklist + `picklistDependency` map) **cannot filter in the UI no matter what** — not even after the `_System_FieldDependency` backfill, because nothing on the create/edit path consumes either the rows or the map. The 2026-06-02 staging backfill was a dead end for this use case, **and it is now stale** (it hard-codes flat `AI`, no `Artificial Intelligence`).

### 3b. Representation ②  — `runtime_scoped_picklist` over a source object (what the UI *does* honor)

The record form filters a dependent picklist through exactly one mechanism (`runtime_picklist.go:buildRuntimePicklistInfo`, `MetadataRecordForm.tsx:20-46, 383-434, 553`):

A field is treated as a filtered dependent picklist **iff**:
1. `validator == "runtime_scoped_picklist"`, and
2. type is `picklist`/`multipicklist`, and
3. `controllingField` is set, and
4. `referenceTo` has **exactly one** object (the *source* object), which must resolve.

Then describe emits:
```json
"controllerName": "<controllingField>",
"filteredLookupInfo": { "runtimePicklist": {
    "sourceObject": "<referenceTo[0]>",
    "valueField":  "<source Label/Name field>",
    "labelField":  "<source Label/Name field>",
    "filterMappings": [ { "sourceField": "<controllingField>", "recordField": "<controllingField>" } ]
}}
```
At runtime the form queries `sourceObject WHERE <controllingField> = record.<controllingField>` and uses the returned rows' Name/Label as the options. If the controlling value is empty **or** unmapped, the query returns nothing and **the dropdown is empty** (no "show all" fallback) — `MetadataRecordForm.tsx:399-417`.

- **Write-time enforcement exists** for representation ②: `validateRuntimeScopedPicklists` rejects a create/update whose dependent value isn't a row under the current controlling value (`runtime_scoped_picklist_validation.go:18-82`). Representation ① has no such enforcement wired.
- **Live precedent in this pack:** `CampaignMember.Status` (`packs/Salesforce-PingCAP-L2/objects/CampaignMember.json:169-188`): `validator: runtime_scoped_picklist`, `controllingField: CampaignId`, `referenceTo: ["CampaignMemberStatus"]`. It works today. (This is the same pair #1583 explicitly excluded as "reference-type, not picklist-to-picklist".)

### 3c. Net

| Capability | Rep ① static map (pack today) | Rep ② runtime_scoped_picklist |
|---|---|---|
| UI filters the dependent field | ❌ no consumer | ✅ `MetadataRecordForm` |
| Enforced on write (API/import) | ❌ | ✅ `validateRuntimeScopedPicklists` |
| Populated at pack install | ⚠️ JSON only; rows never materialized | ✅ (source-object rows via seed) |
| Admin can edit mapping without a metadata deploy | ❌ | ✅ (edit source-object rows) |
| SFDC-faithful (plain picklist + declarative map) | ✅ | ❌ (field sourced from an object) |

---

## 4. Root cause (restated)

Industry→Vertical doesn't filter because `Account/Lead.Vertical__c` is modeled with representation ① (the one the runtime ignores), while the engine only honors representation ②. Materializing `_System_FieldDependency` (the #2307 / 2026-06-02 approach) does **not** fix the create/edit UI, because the UI never reads that table.

---

## 5. The two designs

### Option A — SFDC-faithful, honor the static map (UPSTREAM L0)

Teach the engine to make representation ① real:
1. **Describe:** emit the dependency in a Salesforce-compatible shape (`controllerName` + per-value `validFor` bitmap, or the raw `picklistDependency` map) on the dependent field.
2. **Frontend:** `MetadataRecordForm` filters the dependent options client-side from that payload (empty controlling value ⇒ no options; unmapped value ⇒ no options).
3. **Write validator:** enforce the pair against the map (new validator, or extend existing).
4. **Install:** either materialize `_System_FieldDependency` from `PicklistDependency` at install, or have describe read the field JSON directly (drop the table).

- ✅ Keeps `Vertical__c` a plain picklist and keeps the declarative map already in the pack; fixes **all 7** dependent pairs at once; matches SFDC and PR #2307's intent.
- ❌ Requires upstream NexusCRM changes across describe + frontend + validator (+ optional install). Blocked on upstream; this is exactly the gap #2307 raised and did not land.

### Option B — NexusCRM-native, model as a runtime scoped picklist (DOWNSTREAM, ships today) — **RECOMMENDED**

Re-model `Vertical__c` using the mechanism the engine already honors (representation ②), backed by a small **Industry→Vertical mapping object**. Mirrors `CampaignMember.Status` and the existing `Country_Dictionary__c` object-backed-picklist precedent.

- ✅ Works with the current engine — UI filtering **and** write enforcement both come for free. Admin-maintainable (add a vertical = add a row). No upstream dependency. The AI change is just 4 extra rows. Aligns with the SFDC spec's own §7.1 recommendation (a mapping object / CMT as the source of truth).
- ❌ A modeling change: `Vertical__c` becomes object-sourced rather than a plain picklist; requires a new object + seed (~112 rows) + field reconfig on Account & Lead. The other 6 pairs would each need the same treatment to filter (can be phased).

**Recommendation: Option B**, because it is unblocked, matches how the engine actually works, has two in-repo precedents, and gives write-time enforcement immediately. Adopt Option A only if the team wants full SFDC fidelity and is ready to invest upstream. (A pragmatic path: ship B now for Industry→Vertical; pursue A upstream later and migrate back if/when it lands.)

---

## 6. Recommended design (Option B) in detail

### 6.1 New mapping object — `Industry_Vertical__c` (L2)

| Field | Type | Notes |
|---|---|---|
| `Name` | text/name | The **vertical value**, e.g. `AI – LLM & MaaS`. Used by the engine as both `valueField` and `labelField`. |
| `Industry` | picklist (or text) | The **controlling industry**, e.g. `Artificial Intelligence`. Field apiName must equal the controlling field name `Industry` so `filterMappings.sourceField` matches. |
| `SortOrder` | number (optional) | If present, drives option ordering (`runtimePicklistSort`). |

One row per (Industry, Vertical) pair (~112 rows for the 15-industry mapping). **Shared verticals** (`Furniture`, `Pet Products`, `Toys & Games`, each valid under `DNB-Retail` *and* `High Tech-Manufacturing`) get **two rows** — same `Name`, different `Industry`.

### 6.2 Field reconfig on `Account.Vertical__c` and `Lead.Vertical__c`

```json
{
  "apiName": "Vertical__c",
  "label": "Vertical",
  "type": "picklist",
  "required": true,
  "validator": "runtime_scoped_picklist",
  "controllingField": "Industry",
  "referenceTo": ["Industry_Vertical__c"],
  "options": [ /* keep the full static value list for describe / restricted-value integrity */ ]
}
```
Drop `picklistDependency` from the field (representation ①, now unused). The **stored value is unchanged** — still the vertical string — so existing Account/Lead records need no data migration, provided every stored vertical exists as a `Name` in the mapping object.

### 6.3 Seed data (deployment seed, idempotent)

Seed all pairs into `Industry_Vertical__c`, **including the AI rows**:

```
Artificial Intelligence → AI – LLM & MaaS
Artificial Intelligence → AI – Agents & Orchestration
Artificial Intelligence → AI – Native Applications
Artificial Intelligence → AI – Hardware & Robotics
```
Use deterministic `key`/`match` (e.g. `Industry` + `Name`) per the deployment-seeding contract so it converges on every env (fixing the staging-only, now-stale SQL backfill).

### 6.4 Behavior delivered

- Select `Industry = Artificial Intelligence` → Vertical filters to the 4 AI rows. Empty/unmapped Industry ⇒ empty Vertical (SFDC-parity for the blank-controlling case).
- Create/import with a mismatched pair ⇒ **rejected** by `validateRuntimeScopedPicklists` (stronger than SFDC, which only filters the UI — see SFDC spec §5.3). Document this as an intentional, stricter delta.
- **Lead-Convert:** continue to map `Lead.Industry`→`Account.Industry` **and** `Lead.Vertical__c`→`Account.Vertical__c` together; identical mapping object serves both, so no drift.

### 6.5 Data-quality carry-overs (from the SFDC spec)

- Preserve the SFDC value API-name typos (`Automoile Dealers`, `Museums & Art Galeries`) unless a coordinated rename+migration is scheduled — they're the stored keys.
- Optional: a Flow to null `Vertical__c` when `Industry` changes to one that no longer permits it (prevents orphaned pairs; SFDC spec §7.2).

---

## 7. Verification / acceptance

1. Re-enable the two excluded readiness probes (`dependent-picklist-field-dependency-readiness-api`, `picklist-value-readiness-api`) as the acceptance gate.
2. UI check on staging: Account create → set each Industry → assert Vertical shows exactly the mapped set (spot-check `Artificial Intelligence` → 4 AI verticals; a shared vertical under both parents).
3. Write-enforcement check: API create with `Industry=Artificial Intelligence`, `Vertical__c=Banking` → expect rejection.
4. Account/Lead parity check: diff the mapping used by both fields (single mapping object ⇒ inherently in sync).

## 8. Migration / cleanup

- Retire the stale `20260602_staging_dependent_picklist_fielddependency_backfill` bundle (it targets representation ①, is staging-only, and carries the pre-AI mapping). Under Option B the `_System_FieldDependency` table is irrelevant to this feature.
- Close the loop on #1583 (reopen or supersede — it was closed "completed" on the strength of that backfill, which does not deliver UI filtering) and reconcile PR #2307 (docs-only, Option-A-shaped) against this proposal.

---

## 9. Source evidence (NexusCRM @ 519518d)

| Fact | Location |
|---|---|
| Install writes `ControllingField`/`PicklistDependency`, never `_System_FieldDependency` rows | `backend/internal/infrastructure/persistence/schema_helpers.go:238-239`; `schema_metadata_persist.go:589,677-699`; (no materializer) |
| `_System_FieldDependency` schema | `backend/internal/infrastructure/persistence/metadata_schema.go:438-446` |
| UI filters only via `runtime_scoped_picklist` | `backend/internal/interfaces/rest/runtime_picklist.go:47-97`; `frontend/src/features/record/components/MetadataRecordForm.tsx:20-46,383-434,553` |
| `picklistDependency`/`controllerName`/`validFor` unused in the form | `frontend/src/types/sobject.ts:70,71,115` (types only) |
| Write enforcement (rep ②) | `backend/internal/features/records/runtime_scoped_picklist_validation.go:18-82` |
| Describe emits `dependentPicklist`/`controllerName`/`filteredLookupInfo` | `backend/internal/interfaces/rest/sobject_describe.go:308-311`; `salesforce_dtos.go:95,101,109` |
| Live precedent | `packs/Salesforce-PingCAP-L2/objects/CampaignMember.json:169-188` |