# Design: Declarative Report Type Templates

Generated by /office-hours on 2026-04-16
Revised by /plan-eng-review on 2026-04-17
Branch: main
Repo: pingcap-inc/MetaDrivenPlatform4AI
Status: APPROVED (scope-reduced after eng review)
Mode: Startup

## Problem Statement

NexusCRM has 19 hardcoded Salesforce standard report type JOIN templates in `report_join_templates.go` (743 lines, ~260 column mappings). This is the ONLY significant per-object code duplication in the analytics pipeline.

The formula engine, DSL compiler, view builder, rollup service, and CRT synthesizer are all already metadata-driven and fully generic.

## What already works (no changes needed)

| Component | File | Status |
|---|---|---|
| Formula engine | `backend/pkg/formula/engine.go` | Shared globally, generic |
| DSL compiler | `formula_dsl_compiler.go` | Fully generic, no per-object branches |
| View builder | `formula_dsl_viewbuilder.go` | Fully generic, no per-object branches |
| Rollup service | `rollup_service.go` | Metadata-driven via `GetSchemas()` |
| CRT synthesizer | `crt_synthesizer.go` | Already converts metadata into `JoinTemplate` for custom objects |
| Metadata service | `metadata_service.go` | Caches all schemas with fields/relationships |

## What needs to change

Replace the hardcoded Go `standardJoinTemplates` map in `report_join_templates.go` with a declarative YAML/JSON data file loaded at startup into the same map.

### Key insight (from Codex eng review challenge)

This is a **Salesforce report-compatibility catalog problem**, not a metadata-inference problem. The 19 standard report types encode SF-specific semantics: SCREAMING_SNAKE column aliases (`CAMPAIGN_NAME`), output row keys (`ResultAlias`), raw SQL display expressions (`SelectExpr`), and semantic exceptions (e.g., `OpportunityLead.LEAD_SOURCE` meaning the lead-side field). These are NOT derivable from field metadata. They need declarative data, not a graph compiler.

Custom report types are already handled by `crt_synthesizer.go` via metadata. This change only targets the standard SF report type catalog.

## Architecture

```
┌─────────────────────────────────────────────┐
│  report_type_templates.yaml                 │
│  (19 standard SF report type definitions)   │
│  Composable: extends pattern for DRY        │
│  Fields: PrimaryTable, Joins, Columns,      │
│          ResultAlias, SelectExpr             │
└──────────────────┬──────────────────────────┘
                   │ loaded at startup
                   ▼
┌─────────────────────────────────────────────┐
│  standardJoinTemplates map[string]*JoinTemplate │
│  (SAME runtime contract as today)           │
│  LookupJoinTemplate() stays O(1)            │
└──────────────────┬──────────────────────────┘
                   │ used by
          ┌────────┴────────┐
          ▼                 ▼
  report_service.go   crt_resolver.go
  (unchanged)         (unchanged)
```

### YAML format (composable with extends)

```yaml
reportTypes:
  # Base templates for shared columns
  _ActivityBase:
    columns:
      SUBJECT: {table: "t", column: "Subject"}
      DUE_DATE: {table: "t", column: "ActivityDate"}
      STATUS: {table: "t", column: "Status"}
      # ...

  LeadActivity:
    extends: _ActivityBase
    primaryTable: task
    primaryAlias: t
    joins:
      - {table: lead, alias: l, joinType: "INNER JOIN", onLeft: "l.Id", onRight: "t.WhoId"}
    columns:
      FIRST_NAME: {table: "l", column: "FirstName"}
      LAST_NAME: {table: "l", column: "LastName"}
      # ...

  CampaignLeadLifetime:
    primaryTable: campaign
    primaryAlias: c
    joins:
      - {table: campaignmember, alias: cm, joinType: "INNER JOIN", onLeft: "cm.CampaignId", onRight: "c.Id"}
      - {table: lead, alias: l, joinType: "INNER JOIN", onLeft: "l.Id", onRight: "cm.LeadId"}
      - {table: opportunity, alias: o, joinType: "LEFT JOIN", onLeft: "o.Id", onRight: "l.ConvertedOpportunityId"}
    columns:
      CAMPAIGN_NAME: {table: "c", column: "Name"}
      MEMBER_STATUS: {table: "cm", column: "Status"}
      # ... full column mapping
```

### Semantic fields preserved

- `ResultAlias` — controls row key returned from raw SQL
- `SelectExpr` — hardcoded SQL expression for display columns (e.g., user display name concatenation)
- `Columns` — SF SCREAMING_SNAKE alias to table.column mapping
- `Joins` — multi-table JOIN chain with type, alias, and ON clause

## Implementation

**Phase 1 (Week 1): YAML schema + loader**
- Define YAML schema matching `JoinTemplate` struct
- Implement loader with `extends` composition
- Embed current Go map as compile-time fallback (safety net for YAML load failure)
- Warn (don't fail) on unresolvable fields — match existing fail-soft behavior

**Phase 2 (Week 1-2): Migration + tests**
- Convert all 19 templates from Go to YAML
- Regression tests: full `JoinTemplate` equivalence (not just JOINs — also `ResultAlias`, `SelectExpr`, filter/grouping semantics)
- Remove hardcoded Go map, load from YAML at startup

## Test Strategy

- **Regression:** For each of 19 report types, loaded YAML produces `JoinTemplate` identical to current hardcoded Go struct (deep equality)
- **Execution semantics:** Test `ResultAlias`, `SelectExpr`, filter compilation, grouping resolution, aggregate dedup, fail-soft behavior via `report_join_exec.go`
- **YAML composition:** Test `extends` pattern, column override precedence
- **Load failure:** YAML missing/malformed falls back to embedded Go default
- **Callers:** `report_service.go` and `crt_resolver.go` must not regress

## Failure modes

| Failure | Current behavior | New behavior | Mitigation |
|---|---|---|---|
| Unknown SF alias | NULL/skip | Same (warn + skip) | Preserve fail-soft |
| YAML load failure | N/A | Falls back to embedded Go map | Compile-time fallback |
| Schema drift (FK missing) | N/A | Warn at load, degrade at query | Match existing behavior |

## Success Criteria

1. All 19 report types produce identical `JoinTemplate` structs from YAML as from current Go code
2. Zero regression in report query output (all existing tests pass)
3. `LookupJoinTemplate()` stays O(1) — pre-loaded map at startup
4. Adding a new standard report type requires ONLY a YAML entry, no Go code changes

## NOT in scope (deferred)

- **FormulaContext** — view builder is already fully generic
- **DerivationPlanner** — formula dependency DAG already exists
- **RollupPlanner** — rollup service is already metadata-driven
- **ObjectGraph** — MetadataService already caches schemas; crt_synthesizer already handles dynamic objects
- **SelectExpr YAML validation** — tracked in TODOS.md, low risk while YAML is developer-maintained

## Dependencies

- `JoinTemplate` struct unchanged — all callers (`report_service.go`, `crt_resolver.go`, `report_join_exec.go`) continue using the same interface
- No frontend changes required
- No database changes required

## Background

### Original design (pre-eng-review)

The original /office-hours design proposed 5 new abstractions (ObjectGraph, FormulaContext, JoinPlanner, DerivationPlanner, RollupPlanner) with a 4-6 week timeline, based on the assumption that duplication existed in 2 layers (JOINs + view builder).

### Eng review findings

1. The view builder (`formula_dsl_viewbuilder.go`) has zero per-object branches. It's fully generic.
2. The DSL compiler (`formula_dsl_compiler.go`) has zero per-object branches. It's fully generic.
3. The ONLY per-object code is in `report_join_templates.go` (19 hardcoded SF report type templates).
4. `crt_synthesizer.go` already converts metadata into `JoinTemplate` for custom objects.

### Codex challenge (outside voice)

Codex (GPT-5.4) identified this as a "Salesforce report-compatibility catalog problem, not a metadata-inference problem." Key insights: keep `JoinTemplate` as the runtime contract, treat standard report types as declarative data, preserve fail-soft behavior, expand test scope to cover execution semantics (not just JOIN structure).