# Formula Field Activation: Frozen → Active Migration

## Problem

NexusCRM has 257 formula fields registered in `_System_Field`, but only 4 (1.4%) are actively computed via TiDB Views. The remaining 253 are in "frozen" mode — serving stale Salesforce-synced snapshot values. Once Salesforce sync stops, these values will never update.

The DSL engine infrastructure (parser, compiler, view builder, DAG solver) is built and functional, but has only been exercised on 4 fields across 2 objects (`v_contract`, `v_order`).

## Business Requirements

Formula fields are central to how PingCAP's sales, finance, and operations teams use Salesforce. They power derived metrics that drive decision-making across the entire deal lifecycle. The key business domains are:

### Quote Approval Workflow

The largest cluster: ~40 formula fields on Quote implementing a **multi-dimensional discount approval matrix**. Each field answers "does this quote need approval from [role]?" where:
- **Roles**: Sales Leader, RGM, FP&A, Finance Head
- **Product tiers**: Premium, Enterprise, Standard, Pilot, Service, Cloud (further split by ACV band: 0K, 25K, 50K, 100K, 200K, 500K, 1M, 2M, 5M, 10M+)
- **Logic**: `IF(Average_Discount__c > threshold, "Y", "N")` with nested vCPU and ACV brackets

These fields gate the approval process — without them, deal desk cannot determine which approvals are needed. Supporting formulas compute the inputs: `Average_Discount__c`, `Additional_Discount__c`, `ACV_Amount_USD__c`, `CP_Discount_Gap_*` fields.

### Revenue & Financial Reporting

Order and Quote objects carry currency conversion chains that finance relies on for reporting:
- `ACV_Amount__c` → `Exchange_Rate_to_Use__c` → `ACV_Amount_USD__c` → `CD_Rate__c` → `ACV_USD_with_CD_Rate__c`
- Deferred/recognized revenue calculations: `Deferred_Revenue__c = Net_Fees__c - Recognized_Revenue__c`
- Cash collection tracking: `Cash_Uncollected__c`, `Prepayment_or_Not__c`
- Fiscal period derivation: `"FY"&RIGHT(text(YEAR(ADDMONTHS(CloseDate,9))),2)&"Q"&...`

These power dashboards and reports that finance uses for revenue recognition, cash forecasting, and fiscal planning.

### Lead Lifecycle & Scoring

Lead formulas track progression through the sales funnel:
- **Lifecycle indicators**: `MQL__c`, `SQL__c` (binary 1/0 based on `Lead_Lifecycle_Stage__c`)
- **Aging metrics**: `MQL_Aging__c = today() - MQL_Date__c`, then `MQL_Aging_3_Days__c`, `MQL_Aging_14_Days__c`, `MQL_Aging_30_Days__c` for SLA monitoring
- **Dwell times**: `AEMeetingCompletedDwellTime__c = (NOW() - timestamp) * 24` (hours since event) — used for workflow triggers and manager alerts
- **Quality scoring**: `IQM_Quality_Score__c` and `Initial_AI_Scoring__c` use CASE statements mapping job roles to numeric scores

SDR/BDR managers use these to monitor lead follow-up SLAs and pipeline health.

### Campaign Attribution

CampaignMember formulas handle **polymorphic dispatch** — every formula follows `IF(ISBLANK(ContactId), Lead.X, Contact.X)` because a CampaignMember can link to either a Lead or Contact. These surface region, lifecycle stage, lead status, hubspot scores, and cloud usage data for marketing attribution reporting.

### Opportunity Pipeline

Opportunity formulas derive fiscal periods, segmentation, ACV calculations, and deal age metrics. `Fiscal_Period__c`, `Segmentation__c`, `Region__c`, and scoring fields (`Why_Now_Average_Score__c`, `Why_TiDB_Average_Score__c`) are used in pipeline reviews and forecasting.

### Invoice & Revenue Recognition

Invoice_Collection__c and Revenue__c formulas compute USD conversions, payment status, fiscal periods, and cloud product amount allocations (dedicated/serverless/essential/premium split). These support the finance team's monthly close process.

### Account Classification

Account formulas derive `Segmentation__c` (SKA/KA/GA classification based on revenue thresholds), `ICP__c` (ideal customer profile from industry), and `Installbase_since__c` for customer tenure tracking.

## Salesforce Formula Inventory

Source: `local_docs/formula_fields_all_objects.md` (scraped from SF describe() API, 2026-04-20).

**326 total** formula fields across 38 objects. After excluding 59 fields on 20 third-party package objects (pqcrush, CLDR, affectlayer, awsapn, Elvance, zoom_app) and 9 third-party fields on standard objects, the in-scope set is **258 native formula fields across 18 objects**.

### Distribution by Object

| Object | Formula Fields | Dominant Patterns |
|--------|---:|---|
| Quote | 71 | Approval matrix (~40 `Need_*_Approval_*` fields), discount/ACV calculations |
| Order | 47 | Revenue/cash calculations, USD conversions, cross-ref to Quote/Opportunity |
| Lead | 29 | Lifecycle scoring (MQL/SQL/IQM), dwell times, owner info |
| Opportunity | 29 | Fiscal period, ACV, cross-ref to Account, scoring |
| CampaignMember | 17 | Lead/Contact polymorphic dispatch (`IF(ISBLANK(ContactId), Lead.X, Contact.X)`) |
| Invoice_Collection__c | 17 | Order cross-refs, USD conversions, payment status |
| Revenue__c | 14 | Cloud product amount allocation, fiscal period |
| Account | 12 | Segmentation, ICP classification, website domain parsing |
| PoC__c | 11 | Cross-refs to Account/Lead/Opportunity, approval counts |
| Contact | 5 | Account cross-refs, owner profile |
| Contract | 3 | Account cross-refs, archived doc link |
| Order_Product__c | 3 | Product family, list price subtotal |
| Quote_Product__c | 3 | Autoapprove logic, net fees, list price |
| Opportunity_Product__c | 2 | Product family, valid days |
| Event | 1 | Region (deep cross-ref via Contact) |
| OrderItem | 1 | Discount calculation |
| Task | 1 | Region (deep cross-ref via Contact) |
| Org__c | 1 | Account name cross-ref |

### Notable Formula Patterns

- **Approval matrix** (Quote): ~40 fields implementing a discount-approval decision tree by role x product tier x ACV band. All are `IF(threshold, "Y", "N")` with nested brackets. These form dependency chains: `Average_Discount__c` → `Additional_Discount__c` → `CP_Discount_Gap_*` → `Need_*_Approval_*`.
- **CampaignMember polymorphic dispatch**: Every formula follows `IF(ISBLANK(ContactId), Lead.X, Contact.X)` — CampaignMember can be linked to either a Lead or Contact.
- **USD conversion chain** (Order, Quote): `ACV_Amount__c` → `Exchange_Rate_to_Use__c` → `ACV_Amount_USD__c` → `CD_Rate__c` → `ACV_USD_with_CD_Rate__c`. Multiple currency formulas depend on each other — activation requires respecting the DAG order.
- **Dwell time formulas** (Lead): `(NOW() - TimestampField) * 24` — computing hours since a lifecycle event. These are inherently time-varying — frozen values are guaranteed stale.
- **Fiscal period derivation**: `"FY"&RIGHT(text(YEAR(ADDMONTHS(date,9))),2)&"Q"&text(CEILING(MONTH(ADDMONTHS(date,9))/3))` — PingCAP's fiscal year starts in April, so dates are shifted by 9 months before extracting year/quarter.

## Current State (as of 2026-04-20)

| Metric | Value |
|--------|-------|
| SF spec (native, in-scope) | 258 fields across 18 objects |
| Registered in prod `_System_Field` | 257 (99.6%) |
| Missing from prod | 1 — `order.Archived_Order_Form__c` |
| Active (View-computed) | 4 (1.4%) — all on contract (3) and order (1) |
| Frozen with formula expression | 253 |
| Frozen with empty formula | 22 (rollup summaries / system-computed aggregates) |
| TiDB Views in prod | 2 — `v_contract`, `v_order` |

### Active Fields

| Object | Field | Formula |
|--------|-------|---------|
| contract | Account_Country_Code__c | `Text(Account.BillingCountryCode)` |
| contract | Archived_Contract__c | `if(ISBLANK(StandardUploadUrl__c),"",HYPERLINK(...))` |
| contract | Region__c | `TEXT(Account.Region__c)` |
| order | Contract_Number__c | `HYPERLINK(ContractId, Contract.Name)` |

### Object-by-Object Breakdown

| Object | SF Spec | In Prod | Active | Frozen | Missing | Extra (rollup/system) |
|--------|---------|---------|--------|--------|---------|-------|
| account | 10 | 10 | 0 | 10 | 0 | 1 |
| campaignmember | 17 | 17 | 0 | 17 | 0 | 0 |
| contact | 3 | 3 | 0 | 3 | 0 | 0 |
| contract | 3 | 3 | **3** | 0 | 0 | 0 |
| event | 1 | 1 | 0 | 1 | 0 | 0 |
| invoice_collection__c | 17 | 17 | 0 | 17 | 0 | 0 |
| lead | 26 | 26 | 0 | 26 | 0 | 0 |
| opportunity | 27 | 27 | 0 | 27 | 0 | 4 |
| opportunity_product__c | 2 | 2 | 0 | 2 | 0 | 0 |
| order | 47 | 46 | **1** | 45 | **1** | 10 |
| order_product__c | 3 | 3 | 0 | 3 | 0 | 1 |
| orderitem | 1 | 1 | 0 | 1 | 0 | 0 |
| org__c | 1 | 1 | 0 | 1 | 0 | 0 |
| poc__c | 11 | 11 | 0 | 11 | 0 | 0 |
| quote | 71 | 71 | 0 | 71 | 0 | 4 |
| quote_product__c | 3 | 3 | 0 | 3 | 0 | 0 |
| revenue__c | 14 | 14 | 0 | 14 | 0 | 0 |
| task | 1 | 1 | 0 | 1 | 0 | 0 |

"Extra" fields are registered as `Type='formula'` with empty `Formula` expressions — Salesforce rollup summaries or system-computed aggregates that were synced as formula-typed metadata. They have physical columns and cannot be activated through the DSL pipeline.

## Formula Complexity Tiers

### Tier 1 — Trivial (field references, literals)

```
OwnerId                           → t.`OwnerId`
1                                  → 1
```
AST: `FieldRef`, `NumberLit`. Examples: `lead.Owner_Id__c`, `campaignmember.Count__c`.

### Tier 2 — Arithmetic & Simple Functions

```
Amount / Rate__c                  → t.`Amount` / t.`Rate__c`
ISBLANK(MQL_Date__c)              → (t.`MQL_Date__c` IS NULL)
TODAY() - CreatedDate             → DATEDIFF(CURDATE(), t.`CreatedDate`)
```
AST: `BinaryOp`, `FuncCall`. Examples: `opportunity.ACV_Amount_USD__c`, `lead.MQL_Aging__c`.

### Tier 3 — Cross-Object References (1-hop)

```
Account.Region__c                 → a.`Region__c`   (LEFT JOIN account a ON t.AccountId = a.Id)
```
AST: `CrossRef`. Requires FK field + target table existence. Examples: `opportunity.Region__c`, `contract.Account_Country_Code__c`.

### Tier 4 — Conditional Logic + CASE

```
IF(Amount > 1000, "Y", "N")       → CASE WHEN ... END
CASE(CurrencyIsoCode, "USD", 1, "JPY", 147.6, 7.27)
```
AST: `IfExpr`, `CaseExpr`. Examples: `order.Cloud_Order_Type__c`, all `Need_*_Approval_*` fields on Quote.

### Tier 5 — Complex

Deep cross-object (`Opportunity.Account.BillingCountryCode`), nested formula DAG (formulas referencing other formulas), polymorphic lookups (`Owner:User.IsActive`). Examples: `quote.Account_Country_Code__c`, `lead.MQL_Aging_3_Days__c`.

## Activation Strategy

### Wave 1 — Already-Proven Objects

`contract` (all 3 already active) and `order` (45 frozen fields to add to existing `v_order` view). Lowest risk — views already exist and are exercised in production. Order is high-value: revenue calculations, USD conversions, and fiscal period derivation that finance depends on.

### Wave 2 — High-Value Objects

Start with Tier 1-2 formulas to establish the view, then incrementally add higher tiers.
- **quote** (71 frozen): Dominated by the approval matrix — all Tier 4 IF/CASE. The dependency chain (`Average_Discount__c` → `Additional_Discount__c` → `CP_Discount_Gap_*` → `Need_*_Approval_*`) requires DAG-driven layering. Critical for deal desk workflow.
- **opportunity** (27 frozen): ACV calculations, fiscal period, pipeline scoring. High visibility in sales reviews.
- **lead** (26 frozen): Lifecycle scoring and dwell times. Dwell time formulas (`(NOW() - timestamp) * 24`) are inherently time-varying — they are the most urgent to activate since frozen values are guaranteed stale.
- **account** (10 frozen): Segmentation and ICP classification. Used in account planning.

### Wave 3 — Cross-Object Heavy Objects

These objects' formulas primarily reference parent records:
- **campaignmember** (17 frozen): Lead/Contact polymorphic dispatch — all formulas use `IF(ISBLANK(ContactId), Lead.X, Contact.X)`. Requires JOINs to both Lead and Contact tables.
- **invoice_collection__c** (17 frozen): Order cross-refs for USD conversions and payment status. Used in finance monthly close.
- **revenue__c** (14 frozen): Order cross-refs, cloud product amount allocation (dedicated/serverless/essential/premium split). Revenue recognition reporting.
- **poc__c** (11 frozen): Cross-refs to Account/Lead/Opportunity for PoC tracking and approval.

### Wave 4 — Remaining Objects

contact (3), event (1), order_product__c (3), orderitem (1), org__c (1), quote_product__c (3), opportunity_product__c (2), task (1).

### Excluded from Activation

Fields with empty formula expressions (rollup summaries / system-computed aggregates). They have no expression to compile and cannot be activated through the DSL pipeline. They need separate treatment — either rollup summary implementation or reclassification to a non-formula field type.

## Validation Requirements

Before activating any field:

1. **DSL Parse**: `Parse(formula_text)` must succeed
2. **DSL Compile**: `Compile(ast, ctx)` must produce valid SQL
3. **JOIN Dependencies**: FK fields and target tables must exist for all cross-object references
4. **DAG Cycle Check**: No circular dependencies between formula fields
5. **Return Type**: `_System_Field.ReturnType` must match formula output type (wrong types cause `DATEDIFF`/`DATE_ADD` miscompilation)
6. **View DDL**: `BuildViewDDL(cfg)` must produce valid DDL
7. **Row Count**: `SELECT COUNT(*) FROM v_{object}` must match physical table (no row loss from JOIN fanout)
8. **Value Comparison**: Sample rows, compare view-computed vs physical snapshot values. Classification: MATCH (>=90%), PARTIAL (50-89%), MISMATCH (<50%). Only MATCH fields should be activated.

Existing integration tests cover these checks:
- `TestIntegration_DSL_ParseRealFormulas` — parse rate
- `TestIntegration_DSL_CompileRealFormulas` — compile rate
- `TestIntegration_DSL_BuildAndExecuteViews` — DDL execution
- Value comparison: sample 100 rows with 1% numeric tolerance, exact text/date match

## Known DSL Limitations

| Area | Limitation |
|------|-----------|
| `{!$Record...}` syntax | DSL tokenizer doesn't handle merge-field wrappers |
| `__r` custom relationships | View builder skips `__r`-suffixed cross-refs, falls back to physical column |
| Polymorphic lookups | `Owner:User.IsActive` — resolves against first type only |
| Aggregate FK naming | `SUM(Child.Field)` assumes FK is `{ParentObject}Id` |
| Date type inference | Compiler needs `FieldTypes` map populated for correct `DATEDIFF` |
| Percent scaling | Behavior differs between legacy translator and DSL pipeline |

## Rollback

Frozen fields being migrated retain their physical columns, so rollback is safe:

1. Revert `_System_Field.FormulaMode` to `'frozen'`
2. Invalidate metadata cache
3. `FormulaViewListener` rebuilds the view without the field (or drops it if no active formulas remain)
4. Physical snapshot column still contains last-known-good value

Fields originally *created* as active have no physical column — but all 253 candidates were originally frozen, so this caveat doesn't apply.

## References

- `.agent/skills/formula-fields/SKILL.md` — what formula fields are, how they're implemented, known gaps
- `local_docs/formula_fields_all_objects.md` — complete SF formula field inventory (326 fields, 38 objects)
- `docs/design/metadata-driven-formula-compiler.md` — DSL compiler design
- `docs/design/artifact-3-formula-matrix.md` — SF flow formula compatibility matrix
- `docs/design/artifact-5-underscorer-inventory.md` — `__r` traversal inventory