# NexusCRM Semantics — single source of truth

Canonical reference for **what the fields are** and **what the business actually steers by**,
plus the verified SOQL dialect limits. Consumed by every tool that reads NexusCRM as an agent:

- `nexuscrm-connect` (the OAuth+PKCE engine)
- `nexuscrm-mcp` (live conversational tools — `query_opportunities`, `get_account`, …)
- `nexuscrm-connect-cloner` / `nexuscrm-sfdc-cloner` (SFDC report/dashboard → HTML)
- any ad-hoc `run_soql`

Verified live against `nexuscrm-new-prod.pingcap.com` (API v63.0) on **2026-06-23**.
Re-verify the SOQL section per environment / after platform upgrades — it has drifted before
(see the "corrections" notes below).

---

## 0. The two-layer model (read this first)

There are **two different layers** and they must not be collapsed:

| Layer | Question it answers | Who honors it |
|---|---|---|
| **Schema / field layer** | Does field `X` exist? What's its API name? | Field-mapping / validation. `Amount`, `CloseDate` etc. **exist** and are standard. |
| **Business doctrine layer** | Which field is the *metric the company runs on*? | Fresh analysis. ARR = `ARR_Amount_USD__c`; time = `Launch_Date__c`. |

**Why both matter:** `Amount` and `CloseDate` are — and always have been — standard Opportunity
fields that still exist today. They were *not* deprecated. What changed is that PingCAP adopted
**Net New ARR** (`ARR_Amount_USD__c`, a *custom* field added later) and **Launch_Date** as the
operating metrics, demoting `Amount`/`CloseDate`. Older SFDC reports predate the ARR custom field,
so they legitimately use `AMOUNT`/`CLOSE_DATE`.

### Fidelity vs doctrine — which to apply
- **Cloning an existing SFDC report/dashboard → FIDELITY.** Render exactly what the source XML
  asks for. If the XML says `AMOUNT`/`CLOSE_DATE`, show `Amount`/`CloseDate`. Silently remapping
  them to ARR/Launch_Date would change the report's meaning — that's a bug, not an improvement.
- **Fresh / ad-hoc analysis, new reports, MCP tools → DOCTRINE.** Default to Net New ARR and
  Launch_Date and the open/closed rules below.

### Banner disclosure rule (for the cloner)
When a cloned report uses `Amount` and/or `CloseDate`, surface a banner in the HTML, e.g.:

> ℹ️ This report uses the original SFDC measures **Amount** and **Close Date**. PingCAP's current
> operating metric is **Net New ARR** (`ARR_Amount_USD__c`) measured by **Launch Date**
> (`Launch_Date__c`). Numbers here are faithful to the original report, not the current ARR doctrine.

So a reader comparing an old cloned report with a fresh MCP report understands why the numbers differ.

---

## 1. Schema layer — fields that exist (verified)

**Account**: `Name`, `Account_ID__c` (internal ID, `IBG-B-XXXXXXXX-XXXXXX`), `Industry`,
`Region__c`, `Segmentation__c`, `Vertical__c`, `OwnerId` / `Owner.Name`.

**Opportunity** (287 fields total) — the ones that matter here:

| API name | Label | Use |
|---|---|---|
| `ARR_Amount_USD__c` | **Net New ARR Amount (USD)** | **deal size (doctrine)** |
| `Launch_Date__c` | Launch Date | **time / ARR realization (doctrine)** |
| `Amount` | Amount | **DEPRECATED (owner 2026-07-22): do not call.** Legacy SFDC field; SFDC-report fidelity only when a human explicitly reproduces an old report |
| `ACV_Amount_USD__c` | ACV Amount (USD) | **HIDDEN (owner 2026-07-22):** derived `Amount / Rate`, not an amount index — `describe_object` omits it by default; agents default to Net New ARR |
| `CloseDate` | Close Date | standard; legacy (old Closed Won), **don't use for doctrine** |
| `StageName` | Stage | open/closed (see §2) |
| `Forecast_Quarter__c` | Forecast Quarter | sales-entered FYnnQn string; **messy** (see §2) |
| `AccountId` / `Account.Name` | | relationship |
| `OwnerId` / `Owner.Name` | | relationship |
| `RecordTypeName__c` | | |

SFDC virtual-column → NexusCRM mappings (for the cloner) live in the cloner's `field-mappings.md`;
this file owns the *doctrine*, that file owns the *name translation*.

---

## 2. Business doctrine layer (verified 2026-06-23, confirmed by the business owner)

- **Deal size = `ARR_Amount_USD__c`** ("Net New ARR Amount (USD)"). Never `Amount` or `ACV_*` for size.
  Per the business owner (2026-07-22): **`Amount` is deprecated — agents must not call it**; **`ACV_Amount_USD__c` is hidden** (a derived `Amount / Rate` FX conversion, not an amount index) and `describe_object` drops it from the default field list. Accounting confirmed it does not use Opportunity amounts. Both remain physically present for explicit legacy-report reproduction only. This policy is code-fixed in the MCP server defaults and is deliberately NOT tunable via semantics override files (overrides to `productChain.deprecatedFields` are ignored); changing it requires a reviewed PR editing the defaults.
- **Time = `Launch_Date__c`**, NOT `CloseDate`. ARR is realized when a deal *launches*
  (subscription live / cloud customer live).
- **Fiscal year starts April 1.** `FY27 = 2026-04-01 .. 2027-03-31`. Therefore:
  - `FY27Q1` = 2026-04-01 .. 2026-06-30
  - `FY27Q2` = 2026-07-01 .. 2026-09-30
  - `FY27Q3` = 2026-10-01 .. 2026-12-31
  - `FY27Q4` = 2027-01-01 .. 2027-03-31
  - General rule: `FYnnQ1` starts in calendar year `nn-1`. Apply to **`Launch_Date__c`**.
- **Open vs closed — derive from `StageName`** (`IsWon`/`IsClosed` disagree with it — see field traps):
  - **CLOSED** = `Launched`, `Closed Lost`, and legacy `Closed Won`.
  - **OPEN** = everything else, **including `Deal Closed`** (counterintuitive: "Deal Closed" is
    still an open pipeline deal).
  - **Realized ARR** = `Launched` only.
- **Current stage sequence:** Prospecting → Discovery & Qualification → Technical Validation →
  Business Validation → Negotiation → Deal Closed → Launched.
  - `Negotiating` is a spelling variant → normalize to `Negotiation`.
  - `Contract Review` and `Closed Won` are **legacy** (not part of the current sequence).
- **`Forecast_Quarter__c` is NOT a reliable date.** It is sales-entered and disagrees with launch
  dates; it contains junk (`FYQ` on 2464 rows, far-future `FY40`/`FY41`). Use it only as a coarse
  sales-forecast tag, never as the time dimension.

Sanity number: FY27Q1 realized Net New ARR (Launched, Launch_Date in Apr–Jun 2026) ≈ **$7.92M / 34 deals**.

---

## 3. SOQL dialect limits (verified live 2026-06-23)

NexusCRM SOQL is *mostly* Salesforce-compatible. Verified behavior on current prod:

| Construct | Result | Workaround |
|---|---|---|
| `WHERE CreatedDate = THIS_YEAR` (relative keyword) | ❌ `unknown column: this_year` | client-side, or `Forecast_Quarter__c LIKE 'FY27%'` |
| `WHERE x = LAST_N_DAYS:90` (`:`-literal) | ❌ `unexpected token: :` | absolute ISO datetime (below) |
| `WHERE CreatedDate >= 2025-01-01T00:00:00Z` (ISO datetime) | ✅ **works** | — |
| `WHERE Launch_Date__c >= 2026-04-01` (bare date on date field) | ✅ **works** | — |
| `WHERE Account.Region__c = 'APAC'` (parent-field filter, non-polymorphic) | ✅ **works** *(fixed NX#3036, live 2026-06-23)* | — |
| `SELECT COUNT(Id), SUM(x) ... GROUP BY ...` (multiple aggregates) | ✅ **works** *(fixed NX#3035, live 2026-06-23)* | — |
| `WHERE WhatId IN (SELECT Id FROM Opportunity WHERE …)` (semi-join, subquery >1000 rows) | ❌ `semi-join subquery returned 1001 IDs, exceeding limit 1000` (NX#3037, **fix not yet on prod** as of 2026-06-23) | subquery must return ≤1000; else client-side |
| `WHERE What.Region__c = 'APAC'` (parent field on a POLYMORPHIC lookup) | ❌ not supported (v63 restricts this too) | semi-join (when #3037 lands) or TYPEOF |
| `ORDER BY Account.Name` (relationship sort) | ✅ **works** *(re-verified live 2026-08-03; was ❌ in early July)* | — |
| `GROUP BY CreatedBy.Name` (relationship group) | ✅ **works** *(re-verified live 2026-08-03; was ❌ in early July)* | — |
| `GROUP BY A, B` (more than one grouping column) | ❌ `unexpected token: ,` *(verified live 2026-08-03; NexusCRM#186)* | see "joint groups" below — one query per distinct value of B, **not** one query per column |
| `ORDER BY COUNT(Id)` (function expression in ORDER BY) | ❌ `function expressions are not supported in ORDER BY: COUNT` *(verified live 2026-08-03; NexusCRM#186)* | raise `limit` toward 200, check `truncated`, sort client-side |
| `GROUP BY StageName` (object-local group) | ✅ works | — |
| `ORDER BY x DESC NULLS LAST` | ❌ `unexpected token: NULLS` *(re-verified live 2026-08-03)* | plain `DESC` (nulls sort last on DESC) |
| `WHERE x = LAST_N_DAYS:90` / `THIS_YEAR` (relative literals) | ❌ | absolute ISO datetime |
| `WHERE CreatedDate >= 2025-01-01T00:00:00Z` / `Launch_Date__c >= 2026-04-01` (absolute dates) | ✅ works | — |
| `WHERE StageName IN ('A','B')` (object-local IN) | ✅ works | — |
| relationship traversal in `SELECT` (`Account.Name`, `Owner.Name`) | ✅ works | — |
| `LIMIT` / `OFFSET` | ✅ works | — |

**Joint groups, when `GROUP BY A, B` is what you actually wanted.** Running one query per column does
**not** substitute: it returns the marginal totals for A and for B separately, never the count for
each `(a, b)` cell, so a report built that way answers a different question. To recover the cells:
first `GROUP BY B` to learn its distinct values, then one `WHERE B = <value> GROUP BY A` per value.
The discovery query is sliced like any other — check `truncated`, and page it with explicit
`LIMIT`/`OFFSET` if set, since a value missed there silently drops an entire row of cells.
**`OFFSET` works on aggregate queries** (verified live 2026-08-03: `GROUP BY StageName LIMIT 3
OFFSET 2` returns groups 3-5), so there is no hard ceiling — but the cost is N+1 queries growing
with B, which is why this gap is worth closing upstream rather than documenting around.

**A grouped result is not automatically complete.** `run_soql` fetches up to 200 groups but then
slices to the caller's `limit` (default 50) and sets `truncated`. Sorting client-side is only valid
over a complete set, so check `truncated` and page with explicit `LIMIT`/`OFFSET` before ranking
anything.

**What changed (verified live 2026-06-23):**
- **Multiple aggregates in one SELECT** now work (NX#3035) — `COUNT(Id), SUM(x)` together; no more two-query split.
- **Parent-field WHERE on non-polymorphic lookups** now works (NX#3036) — e.g. filter Opportunity by `Account.Region__c` server-side.
- **Semi-join >1000 (NX#3037) is fixed upstream but NOT yet deployed to prod** — still hits the 1000-ID cap. Re-verify before relying on it; the polymorphic Task-by-related-Region use case waits on this.

**What changed (verified live 2026-08-03):**
- **Relationship sort and group now work** — `ORDER BY Account.Name`, `GROUP BY CreatedBy.Name`. The
  client-side workaround below is retired; doing it anyway costs an extra round trip.
- **`GROUP BY` still takes only one column**, and **`ORDER BY` still rejects function expressions**
  (NexusCRM#186). See the two notes above the matrix for the shapes that actually substitute.

**Recommended pattern:** filter object-local + non-polymorphic parent fields + absolute date ranges
server-side; aggregate (multi-metric) server-side; sort and group on relationship fields
server-side; still do semi-joins over >1000 rows client-side until that lands.

---

## 4. Data caveats (will skew reports if ignored)

- **Duplicate Opportunity rows exist** → dedupe by `Id` before summing/counting.
- **`Open_Pipeline_ACV__c` on Account is ~99.6% NULL** (SFDC roll-up not computed here) — filters on
  it collapse result sets; surface the cause in a banner.
- **`Opportunity.Region__c` is sparse**; Region is reliable on Account, not Opportunity.
- **`IsWon`/`IsClosed` are populated but wrong** → always derive from `StageName` (§2). They were
  largely empty when this line was first written; that is no longer the failure. Measured on
  production 2026-08-04 (3734 opportunities): `Closed Won` 872/872 agree, `Closed Lost` 1770/1818
  agree, but `Launched` only 13/214 agree, plus 82 NULL. A present-and-wrong value is worse than a
  null — `WHERE IsClosed = true` returns cleanly while dropping ~249 closed deals, nearly all
  realized ARR. Open upstream: `nexuscrm-app-packs#322`, `nexuscrm-pingcap-pack#3716`.

<!-- FIELD-TRAPS:BEGIN — checked against the server's resolved dictionary by lib/semantics_coverage.test.mjs.
     Both directions are enforced: a trap listed here must exist in the dictionary, and a dictionary
     entry must be listed here. Add field traps in BOTH places or CI fails and names the other one. -->

| field | status | why it is here |
|---|---|---|
| `Opportunity.Amount` | deprecated | legacy SFDC deal-size field; use `ARR_Amount_USD__c` |
| `Opportunity.ACV_Amount_USD__c` | hidden | derived `Amount / Rate`, not an amount index |
| `Opportunity.IsClosed` | unreliable | disagrees with `StageName`, worst on `Launched` |
| `Opportunity.IsWon` | unreliable | 13 true rows object-wide; none of the 872 `Closed Won` |

<!-- FIELD-TRAPS:END -->

---

## 5. How each consumer uses this file

- **`nexuscrm-mcp`**: encodes §2 doctrine in `query_opportunities` (`fiscal_quarter`→Launch_Date,
  `open_only`/`realized_only`), `get_account` rollup, and field choices. References §3 in tool descriptions.
  Encodes §6 as the `productChain` semantics block (list_objects filtering and describe warning).
- **cloner skills**: apply §0 fidelity (honor the XML), use this file's §1 schema truth + the cloner's
  own `field-mappings.md` for name translation, add the §0 banner when the source uses Amount/CloseDate,
  and follow §3 for query construction. For anything touching product lines or quote/order money,
  apply §6 (a cloned SFDC report over quote totals is UNTRUSTWORTHY on this platform until the P1
  re-source — banner it).
- **ad-hoc `run_soql`**: consult §3 before writing queries and §6 before touching line items.

---

## 6. Product / line-item chain (audited on prod 2026-07-12; epic nexuscrm-pingcap-pack#3873)

**Truth chain (the ONLY chain to read/write):**
`Product2` + `PricebookEntry` (Standard Price Book) → `OpportunityLineItem` → `QuoteLineItem` → `OrderItem`.
`Product2` is the only catalog; `Family` (5-value picklist) is the only categorization.

**Retired legacy chain (migration-era; NEVER write):**
`Opportunity_Product__c` / `Quote_Product__c` / `Order_Product__c` — all three `retired`, so read
their successor instead: `OpportunityLineItem` / `QuoteLineItem` / `OrderItem`. The Opportunity and
Quote tables are **100% empty husk rows** (old-org automation artifacts — no product/quantity/money
data). `Order_Product__c` is **not** empty: 5,523 historical billing rows through 2026-07-04 are
kept deliberately as an audit archive and must not be deleted — read it when the question is
specifically about the pre-migration archive, and `OrderItem` otherwise. Retirement does not license
deletion, and nothing enforces that retention.

> Corrected 2026-08-06 (issue #4881). This paragraph previously said `Order_Product__c` was "pending
> re-source to `OrderItem`" — the backfill was confirmed complete on 2026-07-23, eleven days after
> this section was audited, and the paragraph did not follow. It also inherited a blanket "do not
> read product lines from it", which was written for the two empty husk tables and was wrong about
> the one object of the three whose rows exist to be read. Per-object status now lives in
> `objectLifecycle`; `tests/test_object_lifecycle_consistency.py` binds it to the pack declarations
> in both directions, which is the check that would have caught the eleven-day drift.

**Object lifecycle (`get_semantics` → `objectLifecycle`).** Two states, and the difference is not an
MCP write decision: this server has no mutation dispatch:

| | `deprecated` | `retired` |
|---|---|---|
| meaning | historical rows still live here; lookups belong here | data has moved to `supersededBy`; read there |
| in default `list_objects` | **yes** — hiding it would remove the only path to the data | no |
| NexusCRM MCP mutation dispatch | absent | absent |
| guard validation rule | **no** — the UI is deliberately open, and a write block would make a form users can reach, fill in and never save | yes |
| example | `PoC__c` (421 rows still to migrate to `Credits__c`) | `Order_Product__c` |

The test between them is objective: **has the migration completed.** `PoC__c` becomes `retired` when
its 421 rows land in `Credits__c` — not on a chosen date.

**Money-field truth table (TRANSITIONAL — update when #3873 P1 rollup re-source lands):**

| Object | Trust | Do NOT trust |
|---|---|---|
| Opportunity | `ARR_Amount_USD__c` (manual Net New ARR) | `Amount` (SUM of placeholder OLI TotalPrice) |
| OpportunityLineItem | `Net_fees__c` | `UnitPrice`/`TotalPrice` ($1 placeholders on ~72% of rows) |
| QuoteLineItem | `Unit_Net_Price__c` (placeholder-SKU rows); `TotalPrice` only for unit-priced SKUs | blanket `TotalPrice` sums |
| Quote | nothing at quote level — SUM the QLI rows yourself | `GrandTotal`/`Subtotal` (frozen imports or NULL), `TCV_Amount_c__c`/`NetFees__c` (aggregate the dead legacy chain: 0/NULL) |
| Order | header rollups only for migrated/billing-script orders | header money on natively created standard-chain orders (EMPTY until P1) |

> The Order header rollups are still *computed from* the retired `Order_Product__c`. That is a
> migration leftover awaiting re-source in PR-B3, **not** a reason to read that object — order lines
> come from `OrderItem`. Treat the header rollups as unreliable until the re-source lands.

Root cause and fix plan: epic nexuscrm-pingcap-pack#3873 (P0 = stop husk cloning, PR #3874;
P1 = re-source Quote/Order rollups to QLI/OrderItem; P2 = placeholder-SKU pricing decision).