# CRT Research — Staging Data Findings

Source: NexusCRM staging TiDB (`gateway01.us-east-1.prod.aws.tidbcloud.com:4000`, db `nexuscrm`).
Mode: READ-ONLY (SELECT / SHOW only). Run date: 2026-04-14.
Tables: `Report` (30 cols), `_System_ReportType` (13 cols).

`_System_ReportType` schema confirmed: `Id, FullName, Label, Description, BaseObject,
Deployed, Autogenerated, Sections, Categories, Join, Metadata, CreatedDate, LastModifiedDate`.

---

## Q1 — Total scope

| Metric | Value |
|---|---|
| `Report` rows where `IsDeleted=0` | **6086** |
| Distinct non-null `SourceObject` values | **111** |
| Rows with NULL `SourceObject` | 0 |
| Rows whose `SourceObject` is a known SF **standard** type (or `*Activity`/`*History` family) | **5292** (23 distinct) |
| Rows whose `SourceObject` looks like a **CRT** reference (everything else) | **794** (88 distinct) |
| Total CRT rows in `_System_ReportType` | **59** |

So ~13% of live reports reference something that is not a standard SF report type — those are the CRT candidates.

---

## Q2 — CRT match rate (top 50 CRT-ish `SourceObject`s)

Match strategy: try `Report.SourceObject` as `_System_ReportType.FullName`, then strip trailing `__c` and retry, then try as `_System_ReportType.Id`.

**Overall across all 88 CRT-ish source values:**

| Metric | Sources | Reports |
|---|---|---|
| Matched a `_System_ReportType` row | **47 / 88 (53%)** | **306 / 794 (39%)** |
| Unmatched | **41 / 88 (47%)** | **488 / 794 (61%)** |

The `__c` strip is what makes matching work for the bulk of hits — e.g.
`Campaigns_with_Campaign_Members_and_more_fields__c` →
`FullName='Campaigns_with_Campaign_Members_and_more_fields'`. No report SourceObject
matched by `Id` (all hits came via FullName). `Report.SourceObject` is **always a
developer name**, never a CRT Id.

Top 50 CRT-ish SourceObjects — full table (sourceObject | reports | matched CRT Id | base | has_join):

| SourceObject | Reports | CRT Id | BaseObject | Has Join |
|---|---|---|---|---|
| CustomEntity$PoC__c@PoC__c.Opportunity__c | 109 | — | — | — |
| Leads_with_or_without_Campaigns__c | 96 | b52abd887df736257b | Lead | yes |
| OrderCustomEntity$Revenue__c@Revenue__c.Account__c | 83 | — | — | — |
| CustomEntityCustomEntity$CLDR__Call_Session__c$CLDR__CallLog__c | 73 | — | — | — |
| OrderCustomEntity$Invoice_Collection__c@Invoice_Collection__c.Account__c | 43 | — | — | — |
| Month_End_ARR_New__c | 39 | ca4d7676f2bdc6e3d9 | Account | yes |
| CustomEntity$PoC__c | 37 | — | — | — |
| CustomEntity$Elvance__Opp_Scorecard__c@…Related_Opportunity__c | 32 | — | — | — |
| Campaigns_with_Campaign_Members_and_more_fields__c | 30 | dc4531df40f7892d3a | Campaign | yes |
| Leads_with_Matching_Accounts__c | 26 | 2f451554cfbdefdc9e | Lead | no |
| Month_End_ARR__c | 20 | afe208590c3cae10cc | Account | yes |
| Feature_Gap_Entries__c | 10 | 7fabbb3064778067df | Elvance__Feature_Gap_Entry__c | no |
| OrderItemList | 9 | — | — | — |
| QuoteProduct | 8 | — | — | — |
| OpportunityContact | 7 | — | — | — |
| pqcrush__Account_Plans_with_Key_Stakeholders__c | 7 | 1b98b268a4ee96bb76 | pqcrush__Account_Plan__c | yes |
| affectlayer__Opportunity_With_Engagement__c | 6 | ca4f131dc610b76635 | Opportunity | yes |
| CustomEntity$CLDR__Call_Session__c | 6 | — | — | — |
| CustomEntity$CLDR__CL_ROI__c@CLDR__CL_ROI__c.CLDR__Opportunity__c | 6 | — | — | — |
| CustomEntity$awsapn__Sync_Log__c | 6 | — | — | — |
| pqcrush__Contacts_with_Relationship_Map_Members__c | 5 | b2d19325145e3819d4 | Contact | yes |
| CustomEntity$PoC__c@PoC__c.Account__c | 5 | — | — | — |
| CustomEntity$Org__c@Org__c.Account__c | 5 | — | — | — |
| CustomEntityCustomEntity$CLDR__Call_Session__c$CLDR__CallLog__c@CLDR__CallLog__c.CLDR__Lead__c | 5 | — | — | — |
| ContractOrder | 5 | — | — | — |
| OrderCustomEntity$Invoice_Collection__c | 5 | — | — | — |
| Elvance__Opportunities_With_Scorecards_and_Feature_Gaps__c | 4 | 3b16502daccadb4075 | Opportunity | yes |
| User | 4 | — | — | — |
| Elvance__Opportunities_With_Scorecards_and_Risks__c | 4 | 4fcc47ca2af0f4d67f | Opportunity | yes |
| Opportunity_Operation_View__c | 4 | e89c7517fd7701470f | Opportunity | no |
| pqcrush__Account_Plans_with_Objectives__c | 4 | d1b0a0d702597323dc | pqcrush__Account_Plan__c | yes |
| pqcrush__Account_Plans__c | 3 | b37ec593001a844ff5 | pqcrush__Account_Plan__c | no |
| affectlayer__Account_Opportunites_with_enggaement__c | 3 | cf00b9328591212fd5 | Account | yes |
| zoom_app__Activities_with_Zoom_Events__c | 3 | 727c6dcedd0cddf9ac | zoom_app__Zoom_Event__c | yes |
| Global_ARR_Forcast_Report__c | 3 | 8d9a29fc74f7755a32 | Account | yes |
| ContractList | 3 | — | — | — |
| awsapn__AWS_Marketplace_Reports__c | 3 | 5cad3b7e71819b399b | awsapn__MPPrivateOffer__c | yes |
| ProductList | 3 | — | — | — |
| ActivityCustomEntity$PoC__c | 3 | — | — | — |
| CustomEntity$Elvance__Activity_Log__c@…Elvance__Opportunity__c | 3 | — | — | — |
| OrderCustomEntity$Revenue__c | 3 | — | — | — |
| CustomEntityCustomEntity$pqcrush__Relationship_Map__c$pqcrush__Relationship_Map_Member__c | 3 | — | — | — |
| AccountCustomEntity$Feature_Request__c@Feature_Request__c.Opportunity__c | 3 | — | — | — |
| pqcrush__Account_Plans_with_Opportunities__c | 3 | 95409405e510c7ebda | pqcrush__Account_Plan__c | yes |
| pqcrush__Account_Plans_with_Tasks__c | 3 | 7b3622e2960e245430 | pqcrush__Account_Plan__c | yes |
| pqcrush__Contacts_with_Key_Stakeholders__c | 2 | 747073903789ea0ce7 | Contact | yes |
| CustomEntity$awsapn__ACE_Opportunity__c | 2 | — | — | — |
| ContractOrderItem | 2 | — | — | — |
| ReportList | 2 | — | — | — |
| pqcrush__Key_Dates_with_Account_Plans__c | 2 | fe835cf433f27895ae | pqcrush__Account_Plan__c | yes |

Observation: 31 of the matched rows (~66%) have a non-empty `Join`; the remainder are single-object CRTs where `Join` is null/empty.

---

## Q3 — Unmatched pattern classification (41 sources, 488 reports)

| Pattern | Sources | Reports | Example |
|---|---|---|---|
| `CustomEntity$X__c@Y__c.Parent__c` (dollar + at sign — **parent-scoped entity**) | 13 | **297** | `CustomEntity$PoC__c@PoC__c.Opportunity__c` (109) |
| `CustomEntityCustomEntity$A$B` (**two-level nested** custom entity chain) | 3 | 77 | `CustomEntityCustomEntity$CLDR__Call_Session__c$CLDR__CallLog__c` (73) |
| `CustomEntity$X__c` (single custom entity, **no parent context**) | 13 | 68 | `CustomEntity$PoC__c` (37) |
| Other / standard-looking-but-unmatched | 12 | 46 | `OrderItemList` (9), `QuoteProduct` (8), `OpportunityContact` (7), `User` (4), `ContractList`, `ProductList`, `ReportList`, `ContractOrder`, `ContractOrderItem` |

Syntactic interpretation (hypothesis, not proven by source):

- `CustomEntity$X__c` is an SF internal encoding for **"report type auto-generated from a single custom object"**. Those are NOT stored in `_System_ReportType` — they are implicit. The backend needs to synthesize a single-table CRT from metadata when it sees this prefix.
- `CustomEntity$X__c@Y__c.Parent__c` is the same thing but **parameterized by a parent object**: "a PoC__c report scoped via PoC__c.Opportunity__c master-detail, i.e. report joins on Opportunity". The `@` introduces a foreign-key hint; the token after `.` names the FK column on the child.
- `OrderCustomEntity$...`, `ActivityCustomEntity$...`, `AccountCustomEntity$...` are **prefixed variants** binding the synthetic CRT to a related standard object context (Order / Activity / Account). So `OrderCustomEntity$Revenue__c@Revenue__c.Account__c` means "report of Revenue__c entries in the context of an Order, joined via Account".
- `CustomEntityCustomEntity$A$B` is a **two-hop chain**: report over a grandchild custom object `B` joined through parent `A`. The `@...` suffix (when present) adds a further parent-context constraint.
- The 12 "standard-looking" unmatched (`OrderItemList`, `QuoteProduct`, `OpportunityContact`, `ContractList`, `ProductList`, `ReportList`, `ContractOrder`, `ContractOrderItem`, `User`, …) are SF standard types that my STANDARD whitelist didn't include — they are NOT CRTs, they just need to be added to the standard-types allowlist. That's 46 reports (6% of "CRT-ish"), not a real CRT gap.

Net: genuine synthetic-CRT patterns account for **442 reports** (13+3+13 = 29 distinct source values) with **no matching row in `_System_ReportType`**. The backend must either (a) generate these synthetic CRTs on-the-fly from object metadata + FK graph, or (b) refuse to render them with a clear error.

---

## Q4 — CRT structural shapes (10 diverse samples)

All 10 picks (truncated Join where long):

1. **small / single-table** — `SyncLogDetails` (BaseObject `awsapn__Sync_Log_Detail__c`).
   Join: `""`. Tables: `{awsapn__Sync_Log_Detail__c: 331}`. Classification: **single-table** (no join).

2. **single-join** — `Forecasting_Items_with_Historical_Trending` (BaseObject `ForecastingItem`).
   Join: `{"outerJoin":"false","relationship":"ForecastingItem__hd"}`.
   Tables: `{ForecastingItem:23, ForecastingItem.ForecastingItem__hd:8}`. Classification: **single-join (inner)**.

3. **multi-join (2 hops)** — `Opportunities_With_Scorecards_and_Feature_Gaps` (BaseObject `Opportunity`).
   Join: `{"join":{"outerJoin":"false","relationship":"Elvance__Feature_Gap_Entries__r"}, "outerJoin":"false","relationship":"Elvance__Elvance_Scorecards__r"}`.
   Tables: `{Opportunity:54, Opportunity.Elvance__Elvance_Scorecards__r:21, Opportunity.Elvance__Elvance_Scorecards__r.Elvance__Feature_Gap_Entries__r:14}`. **Nested-join chain**: outer level joins `Scorecards__r` onto Opportunity, inner level joins `Feature_Gap_Entries__r` onto each Scorecard. Note the table path uses dot notation reflecting the nesting.

4. **multi-join (2 hops)** — `pqcrush__Account_Plans_with_White_Spaces_with_White_Space_Highlight_Associations`. Same shape: outer → `White_Spaces__r`, inner → `White_Space_Highlight_Associations__r`.

5. **multi-join (2 hops)** — `awsapn__MarketPlace_PrivateOffer_Reports`. Outer → `MPPrivateOffers__r`, inner → `MPOfferDimensions__r`. (60 columns total split 16/30/16.)

6. **3 hops** — `affectlayer__Account_Opportunites_with_enggaement` (BaseObject `Account`).
   Join path: Account → Opportunities → affectlayer__Engagements__r → affectlayer__Moments__r. Tables 49/32/33/20. Classification: **3-level left-ish join chain**.

7. **3 hops** — `pqcrush__Account_Plans_with_White_Spaces_with_Cross_Sell_Data_Types_with_Cross_Sell_Dat`. Account_Plan__c → White_Spaces → Cross_Sell_Data_Types → Associations. 27/13/6/6.

8. **3 hops** — `pqcrush__Account_Plan_White_Space`: Account_Plan__c → White_Spaces → White_Space_Cells → Amount_Type_Associations. 21/9/10/8.

9. **3 hops** — `pqcrush__Account_Plans_with_White_Spaces_with_White_Space_Cells_with_Cross_Sell_Data_Ty`. 27/13/11/7.

10. **2 hops** — `pqcrush__Account_Plans_with_Objectives_with_Objective_Task_Associations`. 27/11/9.

Maximum depth observed across the **59 CRTs**: **4 tables** (base + 3 nested subquery joins). No CRT in staging has more than 3 levels of nesting.

**Polymorphic split**: the `Campaigns_with_Campaign_Members_and_more_fields` CRT exposes `Campaign` (36 cols) and `Campaign.CampaignMembers` (87 cols) only — its `Sections` JSON does NOT contain any explicit `CampaignMemberContact` / `CampaignMemberLead` split table. The polymorphism is introduced **downstream** by Report.Columns (see Q6/Q7).

---

## Q5 — `Join` JSON schema inventory (across all 59 CRTs)

- `Join` type: always a JSON object when present (**49**), or NULL/empty (**10**). No arrays seen.
- Shape histogram (sorted top-level key-sets):
  - `{outerJoin, relationship}` — **30** (single-join leaf)
  - `{join, outerJoin, relationship}` — **19** (nested — `join` key holds the inner object, same recursive shape)
  - null / empty / not-parseable — **10** (these are all single-table CRTs like `SyncLogDetails`, `Leads_with_Matching_Accounts`)
- Key histogram: `outerJoin: 49`, `relationship: 49`, `join: 19`.
- `outerJoin` value is always a **string** `"false"` or `"true"` (not a bool). No CRT in staging has `outerJoin:"true"` at any level — all observed joins are inner joins.
- `relationship` is always a single relationship API name (e.g. `Elvance__Elvance_Scorecards__r`). Nested joins are left-associative via repeated `join` keys: the outermost joins the base table to X, then the nested `join` joins X to Y, etc.

**Grammar** (observed):
```
Join      ::= JoinLeaf | JoinNested | null
JoinLeaf  ::= { "outerJoin": "true"|"false", "relationship": <string> }
JoinNested::= { "join": Join, "outerJoin": "true"|"false", "relationship": <string> }
```
A 3-hop join is therefore a nested `join` two levels deep. Max observed depth: 3 nested levels → 4-table join chain.

---

## Q6 — Polymorphic handling evidence

**Explicit polymorphic split in `Sections` was NOT found.** I scanned all 59 CRT `Sections` JSONs for tables ending in `_Contact` / `_Lead` / containing `ContactOrLead`: **zero hits**. CRTs that involve CampaignMember (3 of them: `Campaigns_with_Campaign_Members_and_more_fields`, `Leads_with_or_without_Campaigns`, `Campaigns_with_Leads_with_Activities`) expose only the base polymorphic table names (`Campaign.CampaignMembers`, etc.).

**Polymorphic split appears only in `Report.Columns`** as a synthetic prefix: `CampaignMember_ContactOrLead`. There are **30 reports** (out of 6086) whose `Columns` JSON references this prefix. All 30 belong to the one CRT `Campaigns_with_Campaign_Members_and_more_fields__c` (which itself accounts for 30 reports total — i.e. every single report on that CRT uses the prefix).

**Implication**: polymorphism is **not a property of the CRT metadata** — it's a runtime convention in `Report.Columns`. The backend report resolver must recognise `CampaignMember_ContactOrLead.*` as "look at CampaignMember.ContactId OR CampaignMember.LeadId, coerce to the union field, and render the Contact-side path or Lead-side path depending on which FK is non-null". The path under the prefix is `Lead.<field>` (e.g. `CampaignMember_ContactOrLead.Lead.Company`), implying a `CASE WHEN LeadId IS NOT NULL THEN <join-Lead> ELSE NULL`-style resolution.

---

## Q7 — `Report.Columns` path patterns (20 sample CRT reports)

Path segment-count histogram (all columns in the 20 sampled reports):

| Segments | Count |
|---|---|
| 1 (flat, no dot) | 32 |
| 2 (`Table.Field`) | 97 |
| 3 (`Table.Ref.Field`) | 17 |

Top prefixes seen:
`Opportunity` (34), `<no-prefix>` (32 — legacy flat column codes like `CUST_NAME`, `USERS.NAME`, `FK_NAME`, `SUBJECT`, `STATUS`, `DUE_DATE`, `DESCRIPTION`, `RATING`, `ACCOUNT.NAME`, `FK_OPP_AMOUNT`), `affectlayer__Moment__c` (19), **`CampaignMember_ContactOrLead`** (18), `awsapn__MPPrivateOffer__c` (10), `Feature_Request__c` (8), `PoC__c` (8), `affectlayer__Engagement__c` (6), `Account` (4).

Concrete path patterns the resolver must handle:

1. **Legacy symbolic codes** (not dotted or upper-case dotted): `CUST_NAME`, `USERS.NAME`, `FK_NAME`, `FK_Opportunity.Net_Fees__c`, `ACCOUNT.NAME`, `SUBJECT`, `STATUS`, `DESCRIPTION`, `ASSIGNED`, `DUE_DATE`, `RATING`, `FK_OPP_AMOUNT`, `CUST_CREATED_NAME`, `CUST_RECORDTYPE`. These look like Salesforce's historical Analytics column-code format (ACCOUNT.NAME, USERS.NAME, FK_*, CUST_*). **The resolver needs a hardcoded alias table** to map these to real SObject fields.
2. **Object-qualified paths** (2 segments): `Opportunity.Amount`, `Account.Region__c`, `PoC__c.PoC_Status__c`, `Feature_Request__c.Date__c`, `awsapn__MPPrivateOffer__c.Name`. Here the first segment is a **table token from the CRT's `Sections[*].columns[*].table`** (possibly the dotted path like `Account.Opportunities`).
3. **3-segment cross-joins** (17 / 146): `Opportunity.Owner.Name`, `Opportunity.Account.Name`, `affectlayer__Engagement__c.affectlayer__Account__c.Name`, `affectlayer__Moment__c.affectlayer__SpeakerContact__c.Name`. These traverse an additional lookup **beyond** the join path declared by the CRT (they follow `Owner`, `Account`, `Contact` refs on tables that the CRT already brings in). The resolver therefore needs to do its own ad-hoc FK traversal on top of the CRT join tree.
4. **Polymorphic prefix**: `CampaignMember_ContactOrLead.LastName`, `CampaignMember_ContactOrLead.Lead.Company`, `CampaignMember_ContactOrLead.Lead.Replacing_Database__c`. Note the second segment can be either a direct field (`LastName`, `FirstName`, `Email`, `Phone`) meaning "field on whichever side of the polymorphic reference resolved" OR a branch selector (`Lead.<field>`) meaning "only when the resolved side is Lead".

---

## Q8 — `Report.ReportType` vs `Format` (CRT reports only, top 50 source objects)

Both columns exist on `Report`. Cross-tab for reports whose `SourceObject` is one of the top-50 CRT-ish values:

| Format | ReportType | Rows |
|---|---|---|
| Summary | Summary | **487** |
| Tabular | Tabular | **188** |
| Matrix | Summary | 78 |
| Multi_block | Summary | 2 |

So `ReportType` in this schema is **NOT the CRT name** — it's a coarser rendering-family string (`Summary` or `Tabular`, basically). `Format` is the fine-grained format (`Summary` / `Tabular` / `Matrix` / `Multi_block`). The CRT identity lives exclusively in `Report.SourceObject`. Matrix reports collapse to `ReportType='Summary'`. This confirms the backend must treat `Report.SourceObject` as the sole CRT pointer and treat `ReportType`/`Format` as rendering metadata.

---

## Summary for design

- **794 CRT-ish reports, 88 distinct source values.** 306 reports (47 sources) resolve cleanly to a `_System_ReportType` row via `FullName = TrimSuffix(SourceObject, "__c")`. Implement that lookup first and 39% of CRT reports become straightforward.
- **488 reports (29 genuine synthetic-CRT sources)** reference `CustomEntity$...` / `OrderCustomEntity$...` / `CustomEntityCustomEntity$...` patterns with NO row in `_System_ReportType`. These must be synthesized from object + FK metadata. Biggest single pattern: `CustomEntity$X__c@Y__c.Parent__c` (297 reports).
- **~46 reports (12 sources)** are standard-SF types my whitelist missed (OrderItemList, QuoteProduct, OpportunityContact, User, etc.) — fix the whitelist, they are not CRTs.
- **`Join` grammar is small and recursive**: `{join?, outerJoin:string, relationship:string}`, max depth 3 (4-table chain). Always inner-join in staging. Implement recursive descent.
- **Polymorphism (`CampaignMember_ContactOrLead`) is a Report.Columns convention, not CRT metadata** — handle it in the column-path resolver, not in the CRT loader.
- **`Report.Columns` path formats are heterogeneous**: legacy codes (`CUST_NAME`, `USERS.NAME`, `FK_*`), qualified (`Table.Field`), cross-join (`Table.Lookup.Field`), and polymorphic-prefixed. The resolver needs an alias table plus FK traversal.
- **`ReportType` is not the CRT identifier** — it's `Summary`/`Tabular`. `Format` is the actual format. The CRT identity is always `SourceObject` (strip `__c`).