--- name: nexuscrm-connect-cloner description: > Clone Salesforce dashboards AND their source reports into self-contained, browsable HTML using live NexusCRM data. Trigger when the user uploads SFDC `.dashboard-meta.xml` / `.report-meta.xml` and wants a faithful clone — especially when they also provide the per-component source reports and want each dashboard visual to open the underlying report (Matrix/Summary + Details views). Works even when the sandbox cannot reach NexusCRM directly, by running queries through the user's signed-in browser via the Claude-in-Chrome tools. --- # NexusCRM SFDC Dashboard + Report Cloner ("near-perfect" process) This skill encodes the process that produces a faithful clone: exact source-report filters, a clickable report page behind every visual (with a Matrix/Summary ↔ Details toggle), and a robust data path that works when the sandbox is network-restricted. ## 0. The golden rules (learned the hard way) 1. **Never infer filters when the source report XML exists.** The dashboard `.xml` only gives component type + groupings + which report feeds it. The real filters (campaign, region, fiscal-year, email exclusions, MQL priority, stage thresholds) live in each `.report-meta.xml`. Always ask for / read the source reports and apply their `` verbatim. Inferring produces plausible-but-wrong numbers. 2. **One canonical HTML per dashboard, kept in sync with a Cowork artifact.** Build the file, register it with `create_artifact`, and on every change `update_artifact` it. 3. **Be transparent in a banner.** State the exact filters applied, any field mappings, the fiscal-year assumption, and any row caps. Use a green "exact filters applied" banner once filters are sourced (not the yellow "inferred" one). 4. **Never assume a field's API name — confirm it from the object describe first.** Guessed names silently return nothing and the metric drifts to 0 (this happened with `IQM__c`, `IQM_Date__c`). Before querying, pull `/services/data/vXX.X/sobjects//describe`, find the real field by matching name/label/type, and use that. Pin the confirmed names in the per-dashboard README. A two-level relationship like `Lead.Owner.Name` may not resolve via CampaignMember — select `Lead.OwnerId` and resolve names with `SELECT Id,Name FROM User WHERE Id IN (...)` instead. 5. **Sanity-check against a known baseline before declaring done.** Re-pull a couple of totals you already trust (e.g. member-status total = campaign member count, grand total = kept rows). If a metric is 0 or wildly off, suspect a wrong field name, not "the data changed." 6. **NexusCRM returns boolean fields as `1`/`0`, not `true`/`false`.** Never test `=== true` — it silently misses every record (this is what made IQM read 0). Use a truthy check: `const truthy = v => v === true || v === 1 || v === '1' || v === 'true';`. Same idea for picklist numbers / "Yes" strings — coerce, don't strict-compare. ## 1. Connect — three transports, pick what works NexusCRM speaks SFDC-compatible REST/SOQL at `/services/data/vXX.X/`. Auth is OAuth Authorization-Code + PKCE, scope `external.records.read` (read-only, runs as the signed-in user). - **Path C — `nexus_connect.mjs` (preferred when it works):** `node nexus_connect.mjs auth` then `... query ""`. Requires the sandbox/host to reach NexusCRM AND a loopback listener. - **Sandbox can't reach NexusCRM / can't host the loopback?** This is common in Cowork. Symptoms: `getaddrinfo EAI_AGAIN` or `403 from proxy after CONNECT` on `*.pingcap.com`, and the `auth` loopback never receives the callback. **Switch to Path B (browser).** - **Path B — browser session (the reliable fallback):** The user's browser CAN reach NexusCRM. 1. Generate PKCE in the sandbox (verifier+challenge), build the authorize URL, give it to the user. 2. User opens it in a browser signed in to NexusCRM, approves; the redirect to `http://127.0.0.1:1717/callback?code=...` fails to load (nothing listening) — that's fine. **The auth code is in the address bar.** User pastes that URL back. 3. The sandbox can't reach the token endpoint, so **exchange the code IN THE BROWSER** via `mcp__Claude_in_Chrome__javascript_tool`: `fetch('/services/oauth2/token', {POST, code, verifier})`, store `access_token` in `localStorage.__nexus_tok` on the NexusCRM origin (do NOT return the token to chat — the Chrome extension's safety filter will blank outputs containing tokens). 4. Run all queries in the browser with `fetch(inst+'/services/data/v63.0/query?q='+enc(soql), {headers:{authorization:'Bearer '+tok}})`; paginate via `nextRecordsUrl`. ### Transferring query results browser → sandbox - `javascript_tool` return values truncate ~1.9 KB. `get_page_text` returns far more but **caps ~28-30 KB** and refuses larger bodies ("page body text exceeds max_chars"). - Pattern: compute/aggregate IN the browser, inject the JSON string into a `
`,
  then `get_page_text`. For payloads >28 KB, slice the **array by index** (≈100 rows) and read each
  slice; append a `<<>>` sentinel to detect truncation.
- Prefer transferring **aggregates and compact summaries**, not raw rows, wherever possible.

## 2. SOQL gotchas in NexusCRM (these WILL bite)

The authority for what this dialect accepts is
`nexuscrm-connect/references/nexuscrm-semantics.md`, which carries per-row verification dates.
Re-verified live 2026-08-03; the notes below only summarise it.

- **No multi-field GROUP BY.** `GROUP BY OwnerId, Status` → MALFORMED_QUERY *(still true 2026-08-03;
  NexusCRM#186)*. Do NOT substitute one query per column — that returns each column's totals, never
  the per-pair counts. Learn the second column's values, then one query per value for the cross.
- **Multiple aggregates in one SELECT now WORK** *(fixed NX#3035; re-verified live 2026-08-03:
  `COUNT(Id) c, SUM(ARR_Amount_USD__c) total ... GROUP BY StageName` returns both)*. The old
  split-into-two-queries workaround is retired.
- **GROUP BY on relationship fields now WORKS** *(re-verified live 2026-08-03: `GROUP BY Owner.Name`
  returned 112 groups)*. Resolving OwnerId against User in a second query is no longer needed.
- **Alias on a grouped field is ignored** — results come back under the real field name
  (`SELECT Status v ... GROUP BY Status` → read `r.Status`, not `r.v`).
- **Empty-string vs null.** In JS, a returned `CampaignId:""` is falsy; `WHERE CampaignId != null`
  counts both. Decide per case.
- **Report/Analytics API is blocked** on the read-only scope (403 INSUFFICIENT_SCOPE), so you can't
  resolve report IDs for deep links — build the report pages locally instead (see §4).
- Date literal `THIS_FISCAL_YEAR` is accepted in report filters; when querying yourself, translate
  it to an explicit `>= `.

## 3. PingCAP-specific field/semantics map

- **APAC** = `Region__c = 'APAC'` (on Lead and Opportunity). Other regions: Japan, NA, EMEA, LATAM.
- **Current fiscal year (FY27)**: not stored on records. Assume **FY27 starts 2026-02-01**
  (events dated Apr/May 2026 are "FY27Q1"). Document this assumption.
- **Dashboard "Priority"** → real field is `MQL_Priority__c` (values: "MQL Priority 1/2/3"). Confirmed.
- **IQM Scheduled / Completed** → an IQM boolean flag + an IQM date. **DO NOT hard-code `IQM__c` /
  `IQM_Date__c` — those guesses returned 0.** Describe the Lead object, find the boolean and date
  fields whose name/label contains "IQM" ("completed" one has `complete` in it; the other is
  "scheduled"), and use those. If there's no boolean flag, treat scheduled/completed as the matching
  date being non-null.
- **Member status (donut)** = `CampaignMember.Status` (e.g., Attended / Hot Leads / Ticard). Confirmed.
- **Lead owner** → select `Lead.OwnerId` and resolve via `SELECT Id,Name FROM User WHERE Id IN (...)`.
  The 2-hop `Lead.Owner.Name` does NOT resolve through CampaignMember (owners came back blank); a
  single-hop `Owner.Name` directly on Opportunity DOES resolve.
- **All-Leads / Lead-Status reports** exclude internal staff: `EMAIL` not containing `pingcap.com`.
- **Opportunity / Pipeline (OpportunityCampaign report type)** count opps linked to the campaign
  (campaign influence). Direct `WHERE CampaignId='...'` is a reasonable proxy; note that amounts may
  be null → pipeline $0.
- **ARR** = `Opportunity.ARR_Amount_USD__c`. **"with MKT"** has no boolean flag
  (`Is_Opp_From_Marketing_Activity__c` is uniformly "No"); use "has a primary Campaign" or the
  report's actual criteria. **Owner full name** = `Owner.Name`. **SDR** = `SDR_For_NA_Only__c`
  (returns a User Id → resolve to a name).

## 4. The deliverable: dashboard + browsable source reports

Each dashboard clone is ONE self-contained HTML with two layers:

**Dashboard view** — faithful to the `.dashboard-meta.xml`: 12-col CSS grid, exact component
types (Donut, Metric, Gauge, Bar/BarStacked/BarGrouped, Column*, FlexTable), light theme, currency
from ``, indicator colors from `` + breakpoints. Under
each visual put a link: **"Open source report ↗"**.

**Report pages** (one per source report) — in-page navigation (`showReport(key)` / a back button),
NOT cross-artifact links (artifacts are sandboxed; in-page nav is the only thing guaranteed to work
in one click). Each report page shows: report title, type+format, the **exact filters** (chips), the
groupings — then the data with a **Summary/Matrix ↔ Details toggle**, because every report has
`true`:
- *Summary/Matrix view*: grouped aggregation matching ``/`` with
  subtotals/grand total.
- *Details view*: a flat scrollable table of the report's `` (the detail rows). Because the
  HTML is a static, offline handoff the recipient can't regenerate, include **ALL** detail rows, not a
  sample — set `shown = total`. Only cap if file size / render performance is genuinely a problem (and
  then keep the "showing X of N" note honest).

## 4a. Matrix (2-level) reports — get the field, the labels, and the layout right

SFDC Matrix reports (`Matrix`) have BOTH `` (row dimensions) and
`` (column dimensions). Render them as a true 2-level cross-tab, not a flattened list.

**Read the dashboard component XML to know which field feeds the visual.** A `BarGrouped`/`Column*`
component has `groupingColumn` (g1 = category/Y axis), a second grouping (g2 = series), and
`chartSummary` (the measure = bar length / X). Example: All-Leads Lead-Status component → Y=`LEAD_OWNER`,
series=`STATUS`, X=`RowCount`. The matching report is a `CampaignLeadLifetime` Matrix with
rows `LEAD_OWNER › COUNTRY_CODE` and columns `STATUS × MEMBER_STATUS`. Don't guess the axis — the XML
states it. (A frequent bug: mixing **Lead Status** and **Member Status** into one row. They are two
different fields — `Lead.Status` vs `CampaignMember.Status` — and in a Matrix they are different axes.)

**Field identity matters (PingCAP):** the dashboard "Lead Status" visual uses **`Lead.Status`** (NexusCRM
field *Status* under the Lead object = SFDC *Lead Status* under Lead object). It is NOT the campaign
member status. Group/serialize on `Lead.Status`.

**Picklist API-value ≠ label — display the LABEL.** NexusCRM stores API values that differ from what
SFDC's UI/reports show. Pull the picklist once via field describe and map. Observed Lead.Status map:

```
const STATUS_LABELS = {"OPEN":"Open","Working":"Working","Engaged":"Engaged",
  "Nurture":"Nurture - Marketing","Meeting Scheduled":"Meeting Scheduled",
  "Meeting Completed":"Meeting Completed","Nurture - Sales":"Nurture - Sales",
  "Open Opportunity":"Open Opportunity","Lost Opportunity":"Closed Lost Opportunity",
  "Customer":"Customer","UNQUALIFIED":"Disqualified","No Engagement":"No Engagement"};
function statusLabel(s){ return STATUS_LABELS[s] ?? s; }
```

**Normalize mis-stored label-as-value rows.** Some records store the LABEL string in the value field
(import/clone artifact), e.g. literal `"Nurture - Marketing"` or `"Disqualified"`. These are the SAME
status as their canonical API value. Collapse them so each status appears once (matches SFDC):

```
const STATUS_SYN = {"Nurture - Marketing":"Nurture","Disqualified":"UNQUALIFIED",
  "Open":"OPEN","Closed Lost Opportunity":"Lost Opportunity"};
function canonStatus(s){ return STATUS_SYN[s] ?? s; }
```

Rule: **aggregate/group by `canonStatus(raw)`, display `statusLabel(canonStatus(raw))`.** So raw
`UNQUALIFIED` and literal `Disqualified` merge into one "Disqualified" column; raw `Nurture` and literal
`Nurture - Marketing` merge into one "Nurture - Marketing" column. When the user is unsure whether to
merge, ASK explicitly (they did, and chose "normalize to one") rather than guessing.

**Layout that reads like SFDC (no clutter):**
- Two header rows. Row 1: `Lead Owner` + `Country` with `rowspan=2`; one `` per
  canonical Lead Status (N = number of member statuses for that campaign); `Total` with `rowspan=2`.
  Row 2: the member-status sub-column names under each status (member statuses are campaign-specific,
  e.g. Attended/Hot Leads, or Attended/Hot Leads/Ticard).
- Body: one row per `owner × country` (sorted). Cell = sum of member-status counts.
- **Gridlines + shading for differentiation** (user-preferred final look): `.grp` =
  `border-left:2px solid #C9CDD3` on the first sub-column of each status group AND the Total column;
  `.totcol` = `background:#EAF1FB` (shaded blue) on the Total column; grey header.
- **No subtotals.** The user explicitly removed per-status Subtotal columns and per-owner Subtotal
  rows — keep only the gridline group separators + the single shaded grand-Total column, plus one
  grand-total row. (SFDC shows subtotals by default, but this user finds the clean version more
  readable — confirm the preference.)
- **Center-align numbers AND their column headers** in every matrix/summary table (CSS
  `.num-c{text-align:center}`, applied to both `` and the numeric ``). The first/label column
  (Lead Owner, Country, Fiscal Quarter, …) stays left-aligned; only the numeric columns and the Total
  centre. Right-aligned numbers under left-aligned headers read as misaligned — the user rejected both
  left and right and chose centered. Make the header helper take the index where numeric columns begin
  (e.g. `th([label, ...cols, 'Total'], 1)`) so headers and values line up.
- Verify the grand total equals the campaign's kept record count.

**Data shape to pass between sessions/sub-agents** (compute in browser, one file per dashboard):
`{kept, leadStatuses[], memberStatuses[], matrix:{owner:{country:{leadStatus:{memberStatus:count}}}}}`.
Build one finished dashboard as the reference template, then hand each sub-agent its JSON + the
reference path so all matrices look identical.

## 5. Build mechanics

- Self-contained HTML. Chart.js from the artifact-approved CDN ONLY:
  ``
  Include `:root{color-scheme:light}` and `` (lead data may be CJK).
- Charts: `responsive:true, maintainAspectRatio:false` inside fixed-height containers; build charts
  only while visible.
- **The workspace bash mount lags behind file writes** — verify generated files with the Read tool,
  not bash. (A `node --check` via bash may read a stale copy.)
- **Never edit a big HTML with a bash/node script that does read-file → modify → write-file.** The
  read can get a TRUNCATED stale copy of the mount, and you then write the truncation back, silently
  cutting the file off mid-``. Do in-place edits
  with the Read/Edit tools (canonical FS) instead; if you must use a script, afterwards confirm the
  file still ends with `` and the inline script parses (`new Function(scriptBody)`), and repair
  a truncated tail by appending the missing closing code + tags.
- Parallelize across dashboards with sub-agents: gather all data centrally (only the main session can
  drive the browser), write per-dashboard JSON data files, then give each sub-agent its data file +
  a reference template and have it build/edit the HTML. Keep one consistent template so they don't diverge.

## 5a. Delivery & refresh (sharing the result)

- **Static HTML is the review artifact.** The clone bakes its data into JS constants, so it opens
  with zero login and no network — ideal for sending to management for current review. CORS / OAuth
  never enter the picture for a viewer.
- **Do NOT rely on in-browser live refresh from a shared file.** A page opened from `file://` (or any
  origin not on the org's CORS allowlist) cannot call the token/query endpoints — the browser blocks
  it. Salesforce-style orgs need the origin on the CORS allowlist *and* "Enable CORS for OAuth
  Endpoints" turned on, plus the page hosted (not `file://`), for in-browser PKCE refresh to work.
- **Refresh = re-cook, not a button.** The reliable model: ship the recipient the
  `dashboard-meta.xml` + each `report-meta.xml` + the latest HTML + a one-line README (campaign name,
  fiscal-year assumption, and the *confirmed* field map). Whoever needs fresh numbers regenerates with
  this skill using their own NexusCRM access. Managers consume the static HTML; a capable operator
  regenerates on demand.
- **For automated/zero-touch refresh**, the right pattern is a small server-side job (BFF): authenticate
  once, run the validated queries server-side (no CORS, reliable field handling), and publish/refresh a
  static HTML to a shared location or email. Don't put tokens or queries in the distributed file.

## 6. Output checklist (verify before done)

- [ ] Exact `` from each source report applied (no inference); banner lists them.
- [ ] Every dashboard component present, correct type, correct grouping, light theme, right currency.
- [ ] Each visual links to its source-report page; back button returns; one-click navigation.
- [ ] Every report page has Summary/Matrix + Details toggle (showDetails=true); Details lists the
      report's `` with ALL rows (shown=total) for offline use.
- [ ] A refresh rewrites EVERY linked source report (matrix + all detail rows + metrics), not just the
      dashboard visuals — each visual's "Open source report" page reflects the same live pull.
- [ ] Matrix reports rendered 2-level (rows = groupingsDown, columns = groupingsAcross); right field
      (`Lead.Status` not member status); picklist labels shown; mis-stored values normalized; gridlines +
      shaded Total column; no subtotals (if user prefers); grand total = kept count.
- [ ] Every field API name confirmed from the object describe (no guessed names); owners resolved
      via `OwnerId`→User; metrics sanity-checked against a known baseline (no silent 0s).
- [ ] Field mappings + fiscal-year assumption + email exclusion documented.
- [ ] Saved to reports folder; registered/updated as a Cowork artifact; verified via Read tool.