# Design: Custom Report Type (CRT) Support in the Report Runner

**Status:** Revision 2 — decisions locked, ready for implementation
**Author:** Claude (worktree `fix/report-612`)
**Related:** `docs/design/crt-research-data.md` (data inventory), `backend/internal/features/analytics/**`
**Issue:** CRT-based reports return HTTP 404 `Object with ID '<CRT name>' not found`

---

## 1. Problem

`Report.SourceObject` can be one of three things:

| Class | Example | Count (live staging) | Current behavior |
|---|---|---|---|
| Standard SF report type | `CampaignLeadLifetime`, `LeadList`, `OpportunityList` | 5292 | ✅ Works via hardcoded `standardJoinTemplates` map or single-table fallthrough in `resolveSourceObject` |
| Custom Report Type stored in `_System_ReportType` | `Campaigns_with_Campaign_Members_and_more_fields__c` | 306 | ❌ 404 — backend never consults `_System_ReportType` |
| Synthetic CRT encoding (no stored row) | `CustomEntity$PoC__c@PoC__c.Opportunity__c` | 488 (most of which ≈375 are `CustomEntity$…` variants) | ❌ 404 — `resolveSourceObject` extracts the base entity name but drops the FK hint, and no JoinTemplate is synthesized |

Total impacted: **~794 reports / 88 distinct source objects**. Concrete trigger from user: `00ORC000003Gyxl2AC` "JP - Evergreen - DB" on CRT `Campaigns_with_Campaign_Members_and_more_fields__c`.

The report's detail columns use the polymorphic convention
`CampaignMember_ContactOrLead.{Lead,Contact}.Field` — this is a *column-path* grammar, not a CRT-metadata construct.

## 2. Goals / Non-Goals

### Goals

1. **`00ORC000003Gyxl2AC` renders with the columns, filters, grouping and aggregates that the report definition specifies.** Backed by real rows from the staging DB.
2. **All 306 `_System_ReportType`-matching reports execute without 404.** They may render with reduced column fidelity if their Sections reference fields we cannot resolve; that is acceptable, but the response must be 2xx and must not crash.
3. **The ~297 `CustomEntity$X@Y.Z` reports render** using a synthesized base-table + parent-join shape.
   - Two-hop `CustomEntityCustomEntity$A$B` (77 reports) is **explicitly out of this PR's happy path**. The synthetic parser recognises it, logs at INFO, and degrades to the single-table shape for object `A` — no 404, no crash, but no B-object columns either. Full support is a follow-up.
4. **Existing standard-SF reports are untouched** — no regression in any current test; the hardcoded `standardJoinTemplates` map stays as a fast path.
5. **46 reports currently misclassified as CRT are instead picked up by `resolveSourceObject`** because we expand the standard-type whitelist (`OrderItemList`, `QuoteProduct`, `OpportunityContact`, `ContractList`, `ProductList`, `User`, `PartnerFundAllocationList`, …).
6. **Polymorphic `CampaignMember_ContactOrLead.*` column paths resolve** against CampaignMember's denormalized fields (FirstName/LastName/Email/Phone/Company) plus per-side Lead/Contact joins for fields that only live on one side.
7. **SF-compatible wire format.** The response from `POST /services/data/v63.0/analytics/report-execution` still passes through `toSalesforceReportResult`; we do not touch the frontend contract.
8. **Read-only design** — no migrations, no DDL changes, no new tables. All state comes from existing `_System_ReportType` + runtime schema metadata.

### Non-Goals

1. **Custom Report Type editor / CRUD APIs.** We only *read* CRTs in this PR.
2. **Matrix reports over CRTs.** Tabular + Summary are required; Matrix stays with its current handling and may still fail on CRTs — tracked as follow-up.
3. **Fully covering the `Report.Columns` path grammar.** We cover the patterns found in the research inventory (legacy flat codes, 2-segment `Table.Field`, 3-segment `Object.Relationship.Field`, polymorphic `CampaignMember_ContactOrLead.*`). Unknown paths fall back to `NULL` as today rather than crashing.
4. **CRT write path (deploy via Metadata API).** Out of scope.
5. **Permission model for CRTs themselves.** We enforce existing row-level scope on the *base object*; CRT-level access control is a follow-up (captured as a known gap in §9).
6. **Caching.** First pass loads the CRT row + schema on every execution. Adding a TTL cache is trivial and deferred to a follow-up to keep this PR small.

## 3. Vocabulary

- **CRT** — Custom Report Type. A stored definition in `_System_ReportType` with `BaseObject`, `Sections` (column catalog per table), `Join` (join graph), and optional nested joins.
- **Synthetic CRT** — A `SourceObject` string encoding a CRT shape without a stored `_System_ReportType` row. The only forms observed in staging:
  - `CustomEntity$<Obj>` — pure custom object, no FK anchor.
  - `CustomEntity$<Obj>@<Obj>.<FKField>` — custom object scoped by a parent FK.
  - `CustomEntityCustomEntity$<A>$<B>` — two-hop chain A → B. **Recognised but degraded** to single-table on `A` (see §4.10).
  - `Order<Obj>List` — order + custom object, still synthetic.
- **Synthesized JoinTemplate** — A `*JoinTemplate` built at runtime (from a CRT row or from a synthetic string) with the same shape the existing `executeJoinReport` consumes. Zero changes to the execution path.
- **Column-path resolver** — A new function that turns an arbitrary `Report.Columns` entry (dot-notation, legacy alias, polymorphic) into either a `QualifiedColumn` against a `JoinTemplate`, a direct physical column on a single table, or `NULL` if unresolvable.

## 4. Architecture

### 4.1 Big picture

```
POST /services/data/v63.0/analytics/report-execution
      │
      ▼
RunReportExecution                             (analytics_handler.go:89)
      │
      ▼
RunReportDefinition                            (report_service.go:66)
      │
      ▼
executeReport                                  (report_service.go:370)   ←── PATCH SITE
      │
      ├── (1) LookupJoinTemplate(SourceObject) ──► static map hit ──► executeJoinReport
      │
      ├── (2) NEW: crtResolver.Resolve(ctx, SourceObject)
      │        │
      │        ├── crtLoader.LoadByFullName(…)       → _System_ReportType row
      │        ├── crtLoader.LoadByID(…)             → _System_ReportType row
      │        ├── syntheticParser.Parse(…)          → synthetic shape (no row)
      │        │
      │        └── synthesize(*JoinTemplate)
      │
      │       if synthesized template is non-nil ──► executeJoinReport (existing code)
      │
      ├── (3) NEW: resolveSourceObjectStrict(sourceObj, metadata)
      │        │
      │        └── expanded whitelist + trim `__c` + verify against live schema
      │
      └── (4) EXISTING: resolveSourceObject single-table fallthrough (unchanged)
```

The important property is that **the execution path (`executeJoinReport`, filter builder, column resolver) does not change**. We only add a new *producer* of `*JoinTemplate`.

### 4.2 Module layout

All new code lives in `backend/internal/features/analytics/` alongside the existing templates.

```
backend/internal/features/analytics/
├── report_service.go              (edit: add crtResolver field + dispatch hook)
├── report_join_templates.go       (edit: expand standard-type whitelist)
├── report_join_exec.go            (no change)
│
├── crt_resolver.go                (NEW — top-level orchestrator)
├── crt_loader.go                  (NEW — reads _System_ReportType)
├── crt_synthesizer.go             (NEW — CRT row → *JoinTemplate)
├── crt_synthetic_parser.go        (NEW — "CustomEntity$…" → canonical shape)
├── crt_column_path.go             (NEW — column-path resolver incl. polymorphic)
│
├── crt_resolver_test.go           (NEW)
├── crt_loader_test.go             (NEW)
├── crt_synthesizer_test.go        (NEW)
├── crt_synthetic_parser_test.go   (NEW)
└── crt_column_path_test.go        (NEW)
```

No new packages, no new domain ports (see §4.6 on the `CRTRepository` interface).

### 4.3 Core types

```go
// crt_resolver.go

// CRTResolver turns an incoming Report.SourceObject into a *JoinTemplate when
// the SourceObject refers to a Custom Report Type (stored or synthetic).
// Returns (nil, nil) when the SourceObject is not a CRT — callers must fall
// through to single-table handling. Returns (nil, err) on infrastructure errors
// (DB read failures, JSON parse errors) that the caller should surface to the
// client as 500.
type CRTResolver interface {
    Resolve(ctx context.Context, sourceObject string) (*JoinTemplate, error)
}

// crtResolver is the default implementation.
type crtResolver struct {
    loader     CRTRepository      // reads _System_ReportType
    synth      *crtSynthesizer    // row -> *JoinTemplate
    parser     *syntheticParser   // string -> syntheticShape
    metadata   *metadata.MetadataService
    logger     *logging.Logger
}
```

```go
// crt_loader.go

type CRTRepository interface {
    LoadByFullName(ctx context.Context, fullName string) (*models.SystemReportType, error)
    LoadByID(ctx context.Context, id string) (*models.SystemReportType, error)
}

// Thin wrapper over QueryService.Query so we reuse the existing system query
// layer (with its IsDeleted handling and tracing). No raw SQL here.
type crtLoader struct {
    queryService ports.QueryService
}
```

`models.SystemReportType` already has `Id`, `FullName`, `Label`, `BaseObject`, `Deployed`, `Sections` (`string`), `Join` (`string`), `Metadata` (`string`) fields matching the DB shape — to be confirmed when the implementor runs the existing generated-constants build. If the model does not exist we add it to `shared/pkg/models/system.go` with the same style as `SystemConnectedApp`.

### 4.4 `Join` grammar (recursive)

Observed shapes (from research) — all JSON objects, max depth 3:

```json
// Shape A — simple (30 rows)
{"outerJoin": "false", "relationship": "CampaignMembers"}

// Shape B — recursive (19 rows)
{
  "outerJoin": "false",
  "relationship": "Opportunities",
  "join": {
    "outerJoin": "true",
    "relationship": "OpportunityLineItems"
  }
}

// Shape C — null / missing / empty object (10 rows, treated as single-table)
```

Parser:

```go
// crt_synthesizer.go

type crtJoin struct {
    Relationship string   `json:"relationship"`
    OuterJoin    string   `json:"outerJoin"` // "true" or "false" (string, not bool — that's how SF serializes it)
    Join         *crtJoin `json:"join,omitempty"` // recursive
}
```

Depth cap: **hard-coded at 3.** Staging data tops out at depth 3; SF's documented max of 4 is ignored for this PR to keep the fallback surface smaller. Anything deeper is logged at WARN and the CRT degrades to a base-object-only template (§4.10).

### 4.5 CRT → JoinTemplate synthesis

Input: `*models.SystemReportType` (BaseObject, Sections JSON, Join JSON).

Algorithm:

1. Parse `BaseObject` → primary physical table name via metadata (`metadata.GetSchema`). If unknown, fail with a typed `CRTLoadError`.
2. Parse `Join` JSON into `crtJoin` tree (nil-tolerant).
3. Walk the tree breadth-first, accumulating `JoinStep` entries:
   - Look up child relationship on the parent's schema (`metadata.LookupChildRelationship(parentObj, relationshipName)`).
   - Child relationship metadata gives us: child table physical name, child foreign-key column, parent primary-key column.
   - Pick an alias: `primaryAlias` = first letter of base object lowercased; child aliases = `t2, t3, t4` (stable, monotonic).
   - Map `outerJoin=="true"` → `LEFT JOIN`, else `INNER JOIN`.
4. Walk `Sections[].columns[]`. For each `{field, table}` pair:
   - Resolve `table` to a join alias (base object or one of the join steps).
   - Build a `QualifiedColumn{TableAlias, Column: <field>}` keyed by `field` (canonical) and by `<table>.<field>` (dot-notation alias).
5. If `Sections` references a `table` that is not in the join tree, it is **ignored with a warning** (this matches how the hardcoded templates handle unknown columns: `NULL`).
6. Return the `*JoinTemplate`.

Metadata helper to be added if missing:

```go
// metadata.LookupChildRelationship returns the child table's physical name,
// the FK column on the child, and the PK column on the parent for a given
// relationshipName.
func (m *MetadataService) LookupChildRelationship(parentObj, relationshipName string) (*ChildRelationship, error)
```

If this helper doesn't exist we add it — it walks `MetadataService.GetSchema(parentObj).ChildRelationships`.

### 4.6 Synthetic CRT parser

```go
// crt_synthetic_parser.go

type syntheticShape int
const (
    shapeUnknown syntheticShape = iota
    shapeCustomEntity           // CustomEntity$X
    shapeCustomEntityScoped     // CustomEntity$X@X.Parent__c   ← PRIMARY TARGET (297 reports)
    shapeOrderCustomEntity      // OrderCustomEntity$X@X.Parent__c
    shapeNestedCustomEntity     // CustomEntityCustomEntity$A$B  ← recognised but degraded, see §4.10
)

type parsedSynthetic struct {
    shape        syntheticShape
    baseObject   string  // physical object name (e.g. "PoC__c", "Revenue__c")
    anchorObject string  // parent entity when scoped (e.g. "Opportunity__c")
    anchorField  string  // FK column on baseObject pointing at anchorObject
    secondary    string  // for nested: the second custom object (unused in synthesis, kept for logging)
}
```

Parser is a single regex cascade — patterns are anchored and exhaustively tested via a table-driven suite **plus property-based generation** (see §7.1) to guarantee no input can panic the parser.

Synthesis of successful matches:

| Shape | Template produced |
|---|---|
| `shapeCustomEntity` | single-table (base = `X`, no joins) |
| `shapeCustomEntityScoped` | two-table (`baseObject LEFT JOIN anchorObject ON baseObject.anchorField = anchorObject.Id`) |
| `shapeOrderCustomEntity` | two-table, same shape as scoped |
| `shapeNestedCustomEntity` | **base-object-only template on `A`**, with `B` ignored. A WARN is logged with the original SourceObject; the response carries an inline warning (§4.11). |

All joins from synthetic templates are `LEFT JOIN` by default — synthetic CRTs have no `outerJoin` marker, and falsely dropping rows is worse than returning a few extras.

### 4.7 Column-path resolver

```go
// crt_column_path.go

// ResolveReportColumn turns a single entry from Report.Columns into
// (selectExpr, resultAlias, ok). ok==false means the column could not be
// resolved — caller should emit NULL in its place.
func ResolveReportColumn(col string, tmpl *JoinTemplate, md *metadata.MetadataService) (selectExpr string, resultAlias string, ok bool)
```

Covered patterns (drawn from the research inventory):

| Pattern | Example | Strategy |
|---|---|---|
| Flat legacy alias | `CUST_NAME`, `SUBJECT`, `STATUS` | Lookup in legacy alias table (new constant map). |
| Dot-notation against template tables | `Campaign.Name`, `CampaignMember.Status` | Template.Columns lookup by dot key, else split and match `TableAlias` to join aliases. |
| Three-segment cross-join | `Opportunity.Owner.Name`, `affectlayer__Engagement__c.affectlayer__Account__c.Name` | Walk segments; first segment must be a known join table, subsequent segments are FK traversal via metadata's lookup relationships. Emit `(t).<FK>_Name`-style hydration using the existing `_Name` hydration pattern from `report_service.go:490`. If FK traversal cannot be satisfied, return `ok=false`. |
| Polymorphic | `CampaignMember_ContactOrLead.LastName`, `CampaignMember_ContactOrLead.Lead.Company` | Special-cased; see §4.8. |
| SCREAMING_SNAKE | `CAMPAIGN_NAME`, `CLOSE_DATE` | Template.Columns lookup; unchanged. |

New legacy alias table is seeded from the research data's top flat codes plus all existing `standardJoinTemplates` keys reused as a fallback.

### 4.8 Polymorphic `CampaignMember_ContactOrLead`

CampaignMember has `LeadOrContactId` polymorphic → Lead or Contact.

Denormalized fields live directly on CampaignMember (`FirstName`, `LastName`, `Email`, `Phone`, `Title`, `CompanyOrAccount`) and cover the bulk of the user's report.

Algorithm for `CampaignMember_ContactOrLead.{suffix}`:

1. If `suffix` is one of the denormalized fields (CampaignMember has it directly) → resolve to `cm.{suffix}`. Covers 5 of the 9 columns in the user's report directly.
2. If `suffix` is `Lead.{fld}` or `Contact.{fld}` → synthesize a conditional LEFT JOIN to `lead` / `contact` on `CampaignMember.LeadOrContactId = {target}.Id`, and project `{alias}.{fld}`. Row emission for the "other side" is `NULL`. Covers `Lead.Company`, `Lead.Replacing_Database__c`, `Lead.CloudServices__c` in the user's report.
3. If `suffix` is a bare `First_Conversion__c` / `Key_Message__c` (custom lead fields) with no `Lead.` prefix → heuristic: look up each field on `lead` and `contact` schemas. If it exists on exactly one → join that side. If on both or neither → `ok=false`, caller emits `NULL` and logs at DEBUG. This covers the last two columns in the user's report.

This logic lives in `crt_column_path.go` as a standalone function `resolveLeadOrContactPath`. It is independent of `_System_ReportType` (the research confirmed no CRT exposes these synthetic tables in Sections — they only appear in `Report.Columns`).

### 4.9 Standard-type whitelist — auto-scanned at startup

The old plan was a hand-maintained map. Replaced by a **runtime scan** at service bootstrap:

```go
// analytics.BuildStandardObjectResolver(ctx, md) reads every deployed object
// from metadata and registers two keys per object:
//   1. canonical API name                ("orderitem")
//   2. legacy "<PascalCase>List" alias   ("OrderItemList")
// Returns a *StandardObjectResolver that resolveSourceObject consults
// before its current string-trim heuristic.
func BuildStandardObjectResolver(ctx context.Context, md *metadata.MetadataService) *StandardObjectResolver
```

Called once during `service_manager_init.go` wiring, result injected into `ReportService`. Rebuilt on explicit metadata refresh events (existing `MetadataService.Invalidate` hook).

**Why auto-scan over hand-seed:**
- Zero maintenance when new custom objects are added.
- Immediately covers any `XxxList` / bare object name that exists as a real table.
- Captures `OrderItemList`, `QuoteProduct`, `User`, `ContractList`, `ProductList`, `OpportunityContact`, `PartnerFundAllocationList`, and future objects with no code change.

**Fail-safe:** if metadata is empty at the moment of resolution (cold start race), `resolveSourceObject` falls back to its existing string-trim logic — identical to today's behavior.

The scan is O(N) over objects (a few hundred) and runs once at startup, not per request. Memory footprint: ~2 maps with ≤1000 entries each.

### 4.10 Synthesizer 容错机制 (Fault tolerance matrix)

Given the variety of synthetic `SourceObject` strings in the wild, **the synthesizer must never panic and must never surface a 5xx for a parseable shape**. The contract:

| Condition | Regex matches? | Physical base table exists? | Physical anchor/parent exists? | Anchor FK column exists? | Resulting action |
|---|---|---|---|---|---|
| C1 | ✅ | ✅ | ✅ | ✅ | Full synthesized template (§4.6) |
| C2 | ✅ | ✅ | ✅ | ❌ | **Degrade** to base-object-only template on `X`. Inline warning: `"crt_anchor_fk_missing"` |
| C3 | ✅ | ✅ | ❌ | — | **Degrade** to base-object-only template on `X`. Inline warning: `"crt_anchor_object_missing"` |
| C4 | ✅ | ❌ | — | — | **Not a CRT** → `crtResolver.Resolve` returns `(nil, nil)`. Falls through to `resolveSourceObject` heuristic path (which may itself find a real object). No warning — this is not degradation, it's "not our problem". |
| C5 | ✅ (nested `CustomEntityCustomEntity$A$B`) | `A` exists | — | — | **Degrade** to base-object-only template on `A`. Inline warning: `"crt_nested_unsupported"` |
| C6 | ✅ (nested) | `A` missing | — | — | Same as C4. |
| C7 | ❌ | — | — | — | Not a synthetic CRT. Falls through. |
| C8 | ✅, parser panics (shouldn't happen — property tests guard this) | — | — | — | Recovered by a `defer recover()` at the top of `Resolve`. Logs `ERR` with stack and the original SourceObject. Returns `(nil, nil)` → single-table fallback. |

**Invariant:** no synthetic-CRT input can produce a 5xx. The only 5xx paths are infrastructure failures (DB read error on `_System_ReportType`), and those are distinct from the synthesizer.

**Rationale for degrading instead of erroring:** the user's expectation, set by `00ORC000003Gyxl2AC`, is "the report runs". A partial result (base-object columns + NULL for unresolvable columns) with a visible warning in `reportExtendedMetadata` is strictly better than a 404 that blocks the whole dashboard.

### 4.11 Inline warnings in `reportExtendedMetadata`

Per §7.4 and §10 decision, degradations emit structured warnings inline in the response:

```json
{
  "reportMetadata": { /* … */ },
  "reportExtendedMetadata": {
    "detailColumnInfo": { /* … */ },
    "warnings": [
      {
        "code": "crt_nested_unsupported",
        "message": "Nested custom entity report type 'CustomEntityCustomEntity$A$B' is rendered as base-object only in this NexusCRM version.",
        "sourceObject": "CustomEntityCustomEntity$A$B"
      }
    ]
  },
  "factMap": { /* … */ }
}
```

Warning codes are a closed set (`crt_nested_unsupported`, `crt_anchor_fk_missing`, `crt_anchor_object_missing`, `crt_base_object_missing`, `crt_join_depth_exceeded`, `crt_join_parse_failed`, `crt_sections_parse_failed`, `crt_column_unresolved`). `crt_base_object_missing` is distinct from `crt_anchor_object_missing`: the anchor code means a synthetic CRT's anchor edge resolved to an unknown object, whereas the base code means the CRT's primary `BaseObject` itself is missing from metadata and the synthesizer cannot proceed at all. The frontend currently ignores unknown fields in `reportExtendedMetadata`, so this is forward-compatible. Frontend display of these warnings is a separate PR.

## 5. Failure modes and error handling

| Condition | Behavior |
|---|---|
| `_System_ReportType` row loaded but `Join` JSON unparseable | Log `ERR` with CRT ID, return nil template (not an error) → caller falls through to single-table path using `BaseObject` |
| `Sections` unparseable | Same as above. The Sections are only used to enrich the Columns map — an empty Columns map just means more columns resolve to NULL |
| CRT references a `BaseObject` not in metadata | Return `NewNotFoundError("CRT base object", sourceObject)` so the API returns 400 with a meaningful message |
| Synthetic parser matches but FK field cannot be found on metadata | Fall back to single-table (base object only); log WARN once per `(sourceObject, missing FK)` pair |
| Resolved `JoinTemplate` has 0 columns | Still execute — the query will select only `PrimaryTable.Id`, matching the current behavior of an unknown-column report today |
| Depth > 3 in Join recursion | Reject with logged WARN + inline warning `crt_join_depth_exceeded`; degrade to base object only |
| Circular reference in Join (same relationship appears twice in walk) | Break out at first repeat; logged WARN |
| `queryService.Query` fails while loading CRT | Wrap and return — caller emits 500. This is infrastructure, not user error |

Errors never leak raw SQL or JSON to the client — they go through the existing `RespondAppError` machinery.

## 6. Permission & security model

- Row-level scope is enforced on the **base object**, which is exactly what the existing single-table / hardcoded-join code already does. No new surface.
- CRTs themselves have a `Deployed` flag. **We respect it**: if a CRT row is found but `Deployed = 0`, `crtLoader.LoadByFullName` returns `sql.ErrNoRows`-equivalent (treated as not-found) so the report falls back to single-table handling. This matches SF's behavior of hiding undeployed CRTs.
- No CRT-level ACL is evaluated (follow-up — tracked in §9 "Known gaps").
- The CRT loader issues reads only; there is no SQL injection surface because the only user-controlled input (`SourceObject`) is passed as a `QueryCriterion.Val` through the same prepared-statement path every other system query uses.
- No raw SQL. All DB access is via `queryService.Query`, which goes through the TracingDriver.

## 7. Test plan

All tests live in the new `crt_*_test.go` files plus touched areas of `report_service_unit_test.go`. The existing `mockQueryService` and `mockMetadataService` (report_service_unit_test.go) are reused.

### 7.1 Unit tests + property-based tests

**Standard unit** (testify/mock, existing harness):

| Target | Tests |
|---|---|
| `syntheticParser.Parse` | Table-driven: one entry per shape + 3-5 malformed inputs + the 13 real patterns from staging (hard-coded) |
| `crtSynthesizer.Synthesize` | Simple CRT (Campaigns_with_Members), recursive CRT depth-2, recursive CRT depth-3, null `Join`, depth-4 rejected, unknown `BaseObject`, section field on unknown table, every C1-C8 row of §4.10 |
| `crtLoader.LoadByFullName` | Happy path, trimming `__c`, `Deployed=0` returns not-found, DB error propagates, FullName-not-found returns not-found |
| `crtResolver.Resolve` | Dispatches to loader first, synthesizer for synthetic shapes, returns `(nil, nil)` for known standard types, recovers from parser panic (§4.10 C8) |
| `ResolveReportColumn` | Every pattern from §4.7 + malformed inputs; **specific golden tests for each of the user's 9 columns in `00ORC000003Gyxl2AC`** |
| `resolveLeadOrContactPath` | All three branches (denormalized, explicit `Lead.`/`Contact.`, bare custom field) — bare custom field branch fail-soft |
| `BuildStandardObjectResolver` | Covers all currently-existing objects in mocked metadata; `List` suffix alias is registered; cold-start empty metadata returns empty resolver without panic |

**Property-based** (`pgregory.net/rapid` — already in go.sum as of recent PRs, or add as a new dep; fallback: `gopter`). Reason: the synthesizer and the column-path resolver are the two places where ad-hoc string shapes can cause panics, and hand-picked inputs will miss edge cases.

Properties:

1. **Parser never panics.** For any string of length 0-200 drawn from the alphabet `[A-Za-z0-9$@._]`, `syntheticParser.Parse` must return normally. Assertion: no panic, return value is valid `parsedSynthetic` struct. 10,000 iterations.
2. **Parser is pure.** Same input → same output. 1,000 iterations with replay on failure.
3. **Degradation is monotonic.** If a `SourceObject` parses successfully at depth `d`, clamping to depth `d-1` still produces a valid template (just with fewer joins). 1,000 iterations.
4. **Column-path resolver is total.** For any `col` string of length 0-200, `ResolveReportColumn(col, tmpl, md)` returns either `ok=true` with a non-empty select expression, or `ok=false` — never panics. 10,000 iterations, iterated over 5 hand-crafted templates (including an empty template).
5. **Warning codes are a closed set.** For any result that the synthesizer flags as degraded, the emitted warning code is one of the declared constants. 1,000 iterations.

Property tests live in `crt_property_test.go` alongside unit tests in the same package.

**Golden fixtures** (`testdata/crt_goldens/`): 10 representative CRTs selected from the 59 in staging:

| # | CRT name | Shape | Why selected |
|---|---|---|---|
| 1 | `Campaigns_with_Campaign_Members_and_more_fields` | simple depth-1 | User's target report |
| 2 | `Forecasting_Items_with_Historical_Trending` | depth-1, ForecastingItem base | Standard SF parity |
| 3 | `Opportunities_With_Scorecards_and_Feature_Gaps` | depth-2 | Recursive path |
| 4 | `flow_orchestration_stage_run_ootb_crt_two_four_eight` | depth-3 | Max depth |
| 5 | `SyncLogDetails` | depth-1 custom object base | Managed package namespace prefix |
| 6 | `Month_End_ARR_New` | simple depth-0 (no join) | Null-Join branch |
| 7 | `Leads_with_or_without_Campaigns` | `outerJoin=true` | LEFT JOIN path |
| 8 | `Leads_with_Matching_Accounts` | unusual relationship name | Relationship metadata lookup stress |
| 9 | (pick one with `Join.join` nested) | depth-2, nested | Recursive descent |
| 10 | (pick one with `Sections[].columns[]` across 3 distinct tables) | 3-way column surface | Column map stress |

Each golden is a checked-in pair: `input.json` (the raw `_System_ReportType` row) + `expected.json` (the synthesized `JoinTemplate` serialized as JSON). Synthesizer runs against `input.json`, output is compared to `expected.json` with pretty-diff on mismatch. Update with `-update` flag (standard Go golden-test pattern).

**Rationale for 10 not 59:** the other 49 CRTs are structural duplicates (same depth, same shape, different labels). 10 hand-picked covers all shapes. Property tests cover the combinatorial space; goldens cover real wire data. Full 59 coverage buys very little after that.

### 7.2 Integration (already-mocked query service)

- `executeReport` with `def.SourceObject = "Campaigns_with_Campaign_Members_and_more_fields__c"` against a stubbed CRT row that matches the real staging row — assert the synthesized template has `PrimaryTable = "campaign"`, one inner join on campaignmember, Columns contains all 9 user-report paths, and the generated SQL (via ExecuteRawSQL expectation) joins exactly those tables.
- `executeReport` with `def.SourceObject = "CustomEntity$PoC__c@PoC__c.Opportunity__c"` — synthetic path, asserts `PrimaryTable = "poc__c"` + one left join to `opportunity`.
- Regression: `def.SourceObject = "CampaignWithCampaignMembers"` still hits the hardcoded map (unchanged assertion).

### 7.3 End-to-end (browser + real staging DB)

1. `00ORC000003Gyxl2AC` — target report. Must render rows. Screenshot → `evidence/issue-612/05-target-report-fixed.png`.
2. One `Leads_with_or_without_Campaigns__c` report — pick the top one by `LastRunDate`. Screenshot.
3. One `CustomEntity$PoC__c@PoC__c.Opportunity__c` report — pick the top one. Screenshot.
4. One `OrderItemList` report — regression check for the whitelist expansion. Screenshot.
5. One `CampaignLeadLifetime` report — regression check that the hardcoded map still works. Screenshot.

Each capture produces: URL, screenshot path, row count from the rendered table, any console errors. All go in `evidence/issue-612/`.

### 7.4 Negative

- A CRT with unparseable `Join` does not 5xx — it returns results from the base object only, and the response has an `X-NexusCRM-Report-Warnings` header listing the degradation.

## 8. Rollout

1. Feature is fail-safe by construction: `crtResolver.Resolve` returning `(nil, nil)` drops back to today's behavior. No flag needed.
2. PR targets `main`. Branch: `fix/report-612`.
3. Deploy to staging (`feat/integration` infra pattern if applicable). Run the `/e2e` suite against staging CRM.
4. Manual verification of the 5 reports in §7.3 using agent-browser.
5. Watch staging `ReportService` error counters for 24h before opening for prod deploy.

## 9. Known gaps (intentional, follow-up PRs)

1. **CRT-level ACL.** `_System_ReportType` has no ACL column in the current schema. Any user who can read Reports can execute any Deployed CRT. This matches current behavior for hardcoded join templates.
2. **CRT caching.** Every execution re-reads `_System_ReportType`. Top reports will hit this hundreds of times per hour. A 5-minute in-memory cache keyed by FullName is trivial but deferred.
3. **Matrix reports on CRTs.** Current `executeJoinReport` does not handle Matrix; falls back to Summary rendering. CRT path inherits this limitation.
4. **`resolveSFColumn` on filter columns.** `buildCriteria` still strips dot-notation naively (report_service.go) — it will not cleanly filter on cross-object CRT fields that don't resolve via the JOIN template. A filter on `Lead.Company` will resolve to `Company` on the base object and either 1054-error or match the wrong field. PR #845 closed this for the JOIN template path via `compileJoinFilterClause` → `resolveQualifiedColumn` (§9a); the single-table `buildCriteria` path still has the gap. Fix requires `buildCriteria` to accept a `*JoinTemplate`. Follow-up.
4a. **Multi-value `NOT IN` lacks NULL-safety.** Single-value `notContain`/`notEqual` wrap with `OR col IS NULL`, but multi-value `NOT IN` does not. LEFT-joined polymorphic columns with NULL values get silently dropped. No in-PR report hit this but it's a latent bug. Fix is a 3-line change in `compileJoinFilterClause`.
4b. **Currency stripping not applied in single-table formula path.** `stripCurrencyPrefix` runs in `buildJoinWhere` but not `reportFilterToFormula`. Single-table reports with `AMOUNT > "USD 500"` still have the original coercion bug. No in-PR report hit this.
5. **SF-wire-compatible `factMap`.** Current `ReportResult` returns flat rows and the frontend has a compatibility shim. Moving to proper `T!T → rows → dataCells` is independent of this PR.
6. **CRT writes (deploy via Metadata API).** Out of scope as stated.

## 9a. Filter compilation layer (added PR #845)

The original CRT doc treated `executeJoinReport` as a black box below the CRT
dispatcher. That was fine while the only JOIN consumer was the hardcoded
`standardJoinTemplates` map, but once CRT-synthesized templates started flowing
through the same path, several filter-side gaps became visible in production
reports. PR #845 reshaped the filter layer that both callers share. This section
documents the layer so future changes don't have to reverse-engineer it again.

### Where it lives

```
executeReport
 ├── [single-table path]
 │    buildCriteria  ── picklist resolver, multi-value IN,
 │    │                 lookup pre-resolution (resolveLookupFilterIDs)
 │    ↓
 │    compileReportBooleanFilter ── formula path (FilterExpr)
 │       uses: reportFilterToFormula + buildLookupFilterFragments
 │
 └── [JOIN template path — hardcoded OR CRT-synthesized]
      executeJoinReport
      └── buildJoinWhere
           ├── compileJoinFilterClause  (per-filter clause+params)
           └── BooleanFilter OR substitution  (numbered-token tree)
```

Both paths run against the same `def.Filters` and `def.BooleanFilter`, but they
emit SQL through different builders:

- Single-table goes through the generic QueryService + FilterExpr formula
- JOIN template goes through `pkg/query.Builder` + raw `?`-placeholder SQL

Historically these two surfaces drifted. PR #845 did not unify them — that's
still a follow-up — but both now honour the same filter semantics:

1. **BooleanFilter with OR / nested groups.** SF stores a numbered expression
   like `1 AND (4 OR 5) AND 6`. The default AND loop can't express this, so
   both paths substitute numbered tokens into compiled clauses / formula
   fragments and emit a single combined WHERE. `buildJoinWhere` walks
   `\b\d+\b` left-to-right so parameter ordering matches `?` placeholders in
   the builder.
2. **Multi-value `equals` → `IN`, `notEqual` → `NOT IN`.** Comma-separated
   filter values are treated as OR-of-equality per SF semantics.
3. **Multi-value `contains` → OR chain of `LIKE`; `notContain` → AND chain of
   `NOT LIKE`** (De Morgan dual). The default AND Criteria builder can only
   emit a single literal `LIKE '%v1,v2%'` which never matches; both paths now
   synthesize the chain via formula (`CONTAINS(col, "v1") OR CONTAINS(col, "v2")`)
   or SQL (`col LIKE ? OR col LIKE ?`).
4. **Currency-prefixed numeric filters.** SF filter values like `"USD 500"`
   were being passed verbatim to `>`, causing MySQL to coerce to `0` and
   silently drop the filter. `stripCurrencyPrefix` + `isNumericOp` (in
   `report_join_exec.go`) strip ISO/symbol currency prefixes for numeric
   comparison operators. Only applied in the JOIN template path; the formula
   path has the same gap but no in-scope report hit it.
5. **Picklist label → API value translation in the formula path.** The
   default Criteria path has always applied `resolver.resolve(...)` to pull
   `"3. MQL"` down to `"marketingqualifiedlead"`. The formula path
   (`reportFilterToFormula`) previously baked raw labels into the compiled
   expression, so any report with `BooleanFilter` containing OR lost the
   translation. Now `reportFilterToFormula(ctx, f, resolver, objectAPIName)`
   routes single- and multi-value values through the resolver.
6. **Lookup-field pre-resolution in the formula path.** Reference columns
   like `Lead.First_Campaign__c` store campaign Ids, but SF's `contains`
   operator is evaluated against the referenced object's `Name`. The default
   Criteria path already pre-queries the target object and rewrites the
   filter to `<fk> IN (ids)`. The formula path bypassed this, so it emitted
   `LIKE '%JPN%'` against a column full of 15-char Ids and returned zero rows.
   `buildLookupFilterFragments` walks `def.Filters` before compile, runs
   `resolveLookupFilterIDs` for each lookup-string filter, and produces a
   pre-built formula fragment (`fk in [id1,id2]` / `!(fk in [...])` /
   `fk = "__no_match__"`). `compileReportBooleanFilter` substitutes these
   fragments at the matching token position instead of calling
   `reportFilterToFormula`.

### Invariants to preserve

- **Parameter ordering in `buildJoinWhere` OR path must match left-to-right
  token order in the original `BooleanFilter` expression**, not substitution
  order. Each compiled clause carries its own params slice; the outer walker
  appends them in the order they appear in the final combined WHERE string.
- **Unresolvable filter aborts back to AND path.** If any numbered token
  references a filter whose `compileJoinFilterClause` returned `ok=false`,
  the OR compilation aborts and the default AND loop skips that specific
  filter. The OR semantics are silently lost rather than emitting an invalid
  WHERE — deliberate choice, matches the single-table path's fallthrough.
- **Negated operators must be NULL-safe on LEFT-joined columns** (e.g.
  polymorphic `CampaignMember.LeadOrContactId`). Single-value `notContain` /
  `notEqual` wrap with `OR col IS NULL`. Multi-value `NOT IN` currently does
  NOT — known gap, flagged in the PR #845 review as informational-level risk.
- **Lookup pre-resolution runs once per query**, before compile, not per
  filter inside the compile loop. `buildLookupFilterFragments` takes
  `(ctx, filters, objectAPIName, user)` and returns `map[int]string` keyed
  by filter index.

### SF column alias map — ongoing expansion

`sfColumnToField` (in `report_service.go`) maps `CAMPAIGN_NAME` →
`Name`, `OPP_NAME` → `Name`, etc. Reports synced from SF reference columns by
SF-internal alias, not physical column name. PR #845 added
`CAMPAIGN_STATUS` / `START_DATE` / `END_DATE`. Expect more aliases to land as
new report shapes surface — the fix is always one-line per alias, and
missing aliases silently fall through as literal column names (which the
repository then rejects with an unknown-column error).

### Physical-type whitelist gotcha

`backend/internal/bootstrap/system_tables.json` column types go through
`isValidPhysicalType` in `backend/internal/infrastructure/persistence/schema_types.go`.
**Only a narrow set of `VARCHAR(...)` widths pass the whitelist** —
`VARCHAR(500)` and similar get rejected outright, causing bootstrap to
crash-loop on the CI database. When a new physical column is added,
either use an allowed width (`VARCHAR(255)`) or `TEXT`. PR #845 hit this
twice on `Report.BooleanFilter`; the final landing uses `TEXT`.

## 10. Locked decisions

| # | Question | Decision |
|---|---|---|
| 1 | Standard-type whitelist | **Auto-scan at startup** via `BuildStandardObjectResolver` (§4.9). Zero hand-maintenance. |
| 2 | Legacy alias map seed | **Minimal seed + log unknowns at DEBUG** (§4.7). Grow with data. |
| 3 | Polymorphic bare-suffix custom fields | **Fail-soft** — emit NULL + log at DEBUG. Explicit `Lead.` / `Contact.` prefix still gets full resolution. |
| 4 | Join depth cap | **3** (matches staging data). Depth-4 logged + degraded (§4.10). |
| 5 | Degradation warnings | **Inline** in `reportExtendedMetadata.warnings` (§4.11) with a closed set of warning codes. |
| 6 | Scope of nested `CustomEntityCustomEntity$A$B` | **Cut** — recognised by the parser, degraded to base-object-only on `A`. Full two-hop nested support is a follow-up. |
| 7 | Test depth | **Property-based** on the synthesizer + column-path resolver (§7.1 properties 1-5). **10 golden fixtures**, not all 59 CRTs. |

---

*End of draft.*