# Issue 949: Formula Language SSoT and Lowering Contract

Issue: https://github.com/pingcap-inc/NexusCRM/issues/949
Status: staged design — architectural reference only, NOT execution-ready (see Execution Status)
Date: 2026-05-17 (original); execution-status refreshed 2026-05-30

## Execution Status (2026-05-30)

This document was originally headed `implementation-ready` with a closing
`Verdict: PASS / 100% confident`. That header was wrong as an execution
signal and has been downgraded. The arbiter PASS applied to the *shape* of
the staged plan (collapse authority first, IR last); it was never a
green-light that current `main` satisfies the children's preconditions.
Reviewer @IANTHEREAL flagged the contradiction on #949 (2026-05-29): the doc
claimed readiness while its own body lists gates that `main` does not meet.

This section records the verified gap. The staged plan below remains a valid
**future blueprint**; it is **not** an instruction to start child work now.
The #949 epic is **frozen / deferred-with-trigger** (see #949 epic-tracker
comments for the revisit triggers).

### What actually landed on `main` (function-spec layer only)

- `backend/pkg/formula/spec/` exists (`spec.go`, `catalog.go`, `registry.go`,
  `returntype.go`) with a `FunctionSpec` type.
- VS1–VS4 merged 2026-05-29 (mapping by matching `(VSn)` tag on issue and PR
  titles): VS1 function arity SSoT = #1464 (closed by PR #1550); VS2 return-type
  + arg-type SSoT = #1466 (PR #1561); VS3 active-DSL compiler cutover = #1467
  (PR #1572); VS4 dual-compiler function-set reconciliation = #1471 (PR #1577).
  Children #1465, #1543, #1468, #1438 also closed.
- This is the "one function catalog" slice — and **only** that slice. There is
  no IR, no parser unification, no operator/context authority on `main`.

### What did NOT land (verified on `origin/main`, matches @IANTHEREAL's review)

- **No `OperatorSpec` type** anywhere in `backend/pkg/formula` (grep empty).
- **`FunctionSpec` carries no `Visibility` / `ValidContexts` /
  `SalesforceSurface` fields** — public formula functions and internal helpers
  (BCRYPT, ISNULLX, ISBLANKX, SAFELT, …) are still undifferentiated.
- **Active-mode `INCLUDES` still emits invalid `FIND(...)`** at
  `backend/internal/features/analytics/formula_dsl_compiler.go:1177`. #1432
  OPEN; PR #1559 draft/unmerged; PR #1589 closed without merge (parked after
  repeated codex BLOCK rounds — the real fix needs the preview/view-builder
  context alignment, larger than a SQL swap).
- **`FormulaMode` is still admin-facing** and new formula fields still default
  to `"active"` (`backend/internal/features/metadata/formula_fields.go:21`).
  #1437 OPEN (defer-recommended).
- **Aggregate authoring residue still present** (`AggregateExpr`,
  `FindAggregateAuthoringExprs` in
  `backend/internal/features/analytics/aggregate_authoring_validation.go`).
- Children **#1469, #1472, #1474 OPEN** (retire legacy maps / rewrite eval
  kernel / retire expr-lang parser) — the IR/parser-unification phase, not
  started. IR-shim attempt #1543 was closed; the IR direction is deferred to a
  future Phase-2 spike per @IANTHEREAL's guidance (introduce IR late, after
  operator/context/predicate/structured-criteria/FormulaMode/aggregate
  authority is collapsed).

### Reading guide

Treat everything below "## Purpose" as the **target architecture and the
ordered plan to reach it**, with the function-spec layer (Children 1–2,
function half) already partially realized by VS1–VS4. Before any further child
is dispatched, this doc must be refreshed against `main` and @IANTHEREAL's
required tightenings (surface-inventory table; public-vs-internal FunctionSpec
split; `OperatorSpec` with a live consumer, not inventory-only; executable
aggregate boundary; FormulaMode storage-only; Child-11 IR entry gates).

## Purpose

Issue #949 tracks a real formula substrate split. NexusCRM exposes what should
be one Salesforce v63-compatible formula language, but the implementation is
spread across independent registries, parsers, SQL emitters, runtime
evaluators, and structured-criteria switches.

The goal is one language authority with multiple internal lowerings:

- runtime evaluation for validation rules, Flow formula resources, approval
  formula criteria, action guards, defaults, and frozen formula snapshots
- predicate SQL for query filters, analytics filters, RLS / sharing criteria,
  Flow record filters, scheduled-start filters, and other WHERE contexts
- projection SQL for active formula fields and view-backed computed columns

This is L0 platform substrate. It must stay object-neutral and metadata-driven:
no SObject-name branches, tenant custom-field literals, profile literals,
record-type developer-name policy, or CRM-specific business semantics.

## Current Failure

The current tree has two formula-text grammars and three live lowering paths:

- `backend/pkg/formula` normalizes Salesforce-like formula text into
  `expr-lang` expressions, validates against `runtimeFunctionSignatures`, and
  evaluates through `backend/pkg/expression`.
- `backend/pkg/formula.ToSQL` normalizes formula text, parses it again through
  the `expr-lang` AST, and lowers it with `backend/pkg/expression.SQLWalker`.
- `backend/internal/features/analytics/formula_dsl_*` owns a separate parser,
  AST, compiler, aggregate node, function switch, and view builder for active
  formula fields.

There are also formula-shaped surfaces that bypass formula text entirely:

- Flow decision and loop conditions use `evaluateCondition` and
  `evaluateConditionCtx`, each with a large operator switch.
- Flow record filters and scheduled-start filters use separate SQL builders
  and allowlists in `buildFilterFromConditionsImpl` and
  `scheduledStartValidRuntimeOperators`.
- Approval structured criteria use `matchesCriterion` and `evaluateCriterion`,
  including a fail-open default for unknown operators.

The result is not just code duplication. It is user-visible semantic drift:

- Function existence is declared in at least four places:
  `runtimeFunctionSignatures`, analytics `compileFuncCall`,
  `knownUnsupportedSalesforceFunctions`, and the analytics SQL-function
  `allowedFuncs` security allowlist. The last one is a SQL safety boundary,
  not a Salesforce function catalog, so it must not be folded blindly into a
  formula catalog.
- `FormulaMode` is exposed as author metadata even though it is a backend
  lowering/cache decision. Existing comments disagree with actual behavior:
  `active` means TiDB view computation, while `frozen` means engine snapshot at
  write time.
- `applySalesforceEmptyStringSemantics` rewrites `Field == ""` only on the
  runtime `Evaluate` path. Predicate SQL and projection SQL keep different
  NULL/empty-string behavior.
- Active formula `INCLUDES` projection SQL emits `FIND(value, field) > 0`.
  TiDB supports `LOCATE`, `INSTR`, and `FIND_IN_SET`, but not plain `FIND`, so
  this is broken SQL rather than merely a different implementation.
- Analytics parsing routes `SUM`, `COUNT`, `MIN`, `MAX`, and `AVG` through
  `parseAggregateOrFunc`, which accepts exactly one argument. The compiler has
  a two-argument `MAX` / `MIN` lowering, but the parser never produces that
  shape.
- Validation-rule import compatibility can skip formulas by function name or by
  `__c`-looking missing identifiers. That is a fail-open runtime default where
  steady-state metadata validation should fail closed.

## Contract

The target contract has one authoritative formula language definition and
separate, explicit lowerings.

`FormulaSpec` is the single machine-readable catalog for Salesforce-formula
surface area. It declares:

- function name and aliases
- arity and argument families
- return family
- Salesforce v63 NULL and blank behavior
- valid contexts
- runtime support
- predicate-SQL support
- projection-SQL support
- unsupported reason when a Salesforce function is known but not implemented

`OperatorSpec` is the same idea for operators and structured criteria. It owns:

- formula operators such as `=`, `<>`, `<`, `>`, `&`, `AND`, `OR`, and `NOT`
- structured Flow / approval operator spellings such as `EqualTo`,
  `NotEqualTo`, `IsBlank`, `Includes`, and `Excludes`
- NULL/empty-string rewrites
- multi-picklist `INCLUDES` / `EXCLUDES` set semantics

`FormulaContext` is the call-site contract. It replaces backend-mode guessing
with explicit intent:

- `ValidationRule`
- `FormulaFieldProjection`
- `FrozenFormulaSnapshot`
- `QueryPredicate`
- `RLSPredicate`
- `AnalyticsPredicate`
- `FlowFormulaResource`
- `FlowDecisionCondition`
- `FlowRecordFilter`
- `FlowScheduledStartFilter`
- `ApprovalFormulaCriteria`
- `ApprovalStructuredCriteria`
- `ActionGuard`
- `ActionDefault`
- `PackVerify`

Each formula-shaped call site must pass one context. Context decides which
functions, operators, globals, relationship forms, and lowerings are valid.
Error messages name the user-facing formula problem and context; they must not
mention `active`, `frozen`, `interpreter`, `DSL`, or backend topology.

`FormulaMode` remains a stored compatibility field during migration, but it is
not the long-term admin authoring contract. The values must not be renamed in a
compatibility PR because Tooling API and metadata GET/PUT clients can roundtrip
the current strings. Deprecation means:

- fix the inaccurate comments and docs first
- default omitted formula-field mode internally
- stop requiring authors to choose a mode in new UI/API flows
- later hide or ignore the field after all persisted rows have an internal
  lowering strategy

## Target Architecture

```text
Formula text or structured criteria
          |
          v
Context-aware normalization
          |
          v
FormulaSpec + OperatorSpec checks
          |
          v
Canonical typed AST / IR
          |
          +--> RuntimeLower    -> Go evaluation
          +--> PredicateLower  -> SQL WHERE
          +--> ProjectionLower -> SQL SELECT / view expression
```

The canonical IR is deliberately smaller than either current parser shape:

- literals: string, number, bool, null, date/time where represented
- references: local field, global, relationship path, prior/current record path
- unary and binary operators
- logical n-ary operators
- function calls
- conditional expressions
- typed structured criteria nodes that normalize to the same operator forms
- no aggregate expression in the formula-field language

Rollup aggregation stays with `RollupService` / rollup metadata. `SUM`,
`COUNT`, and `AVG` over child records are not projection formula functions.
Scalar `MIN` and `MAX` remain formula functions and must not be deleted with
aggregate support.

## Migration Plan

The design intentionally does not start with a parser rewrite. Parser collapse
is valuable, but starting there creates either unused infrastructure or a large
multi-surface cutover. The safe path is to collapse authority first, then
collapse grammar and AST shape.

### Child 1: FormulaSpec and OperatorSpec Package

Add `backend/pkg/formula/spec` with function, operator, context, and lowering
capability declarations. Add parity tests that compare the function spec
against the current runtime catalog, analytics compiler switch, validation
unsupported list, and SQLWalker function switch.

Add operator parity tests in the same child. The tests must compare
`OperatorSpec` against every current operator vocabulary:

- formula text operators and normalization rewrites in `pkg/formula`
- predicate SQL operators in `SQLWalker`
- Flow decision / loop operators in `evaluateCondition` and
  `evaluateConditionCtx`
- Flow record-filter SQL operators in `buildFilterFromConditionsImpl`
- scheduled-start operator allowlist in `scheduledStartValidRuntimeOperators`
- approval structured criteria operators in `evaluateCriterion`

The parity gate is inventory-only in this child: it records the current support
matrix and fails CI when a new operator spelling is added to a switch, map, or
slice without a corresponding `OperatorSpec` row.

Atomicity proof: behavior stays unchanged; this PR only creates the authority
and gates future function/operator drift with tests. Flow, approval, and
scheduled-start operators are not migrated yet, but they are no longer
ungated.

Scope validation: about 400 production lines and 350 test lines.

### Child 2: Runtime and Validation Catalogs Use FormulaSpec

Drive `runtimeFunctionSignatures` and validation-rule unsupported-function
classification from `FormulaSpec`. Delete the hand-owned
`knownUnsupportedSalesforceFunctions` table. Keep the `__c` import-artifact
heuristic temporarily, but require an explicit compatibility flag/path before
it can skip a rule.

Atomicity proof: runtime validation keeps the same supported functions, while
unsupported known Salesforce functions now come from the same catalog.

Scope validation: about 350 production lines and 300 test lines.

### Child 3: Context-Aware Syntax Validation

Introduce `ValidateSyntaxInContext(expr, FormulaContext, options)` and route
pack install, frozen formula verification, active formula verification, and
metadata authoring through it. The old context-blind `ValidateSyntax` can
remain as a compatibility wrapper for runtime contexts that have no stronger
contract yet.

Atomicity proof: every changed caller declares its context in the same PR; no
new function validity path is left context-free.

Scope validation: about 450 production lines and 350 test lines.

### Child 4: Projection SQL Catalog Use and Immediate DSL Fixes

Make analytics `compileFuncCall` consult `FormulaSpec` for function existence,
arity, context validity, and projection-SQL capability. Keep the SQL
`allowedFuncs` allowlist separate as a lowering-internal safety allowlist.
Fix the verified projection bugs in the same slice:

- route scalar `MIN` / `MAX` through generic multi-argument function parsing
  instead of aggregate parsing
- lower `INCLUDES` to valid set-membership SQL, not `FIND(...)`

Atomicity proof: active formula-field behavior keeps one compiler entry point,
but the compiler no longer owns an independent formula catalog.

Scope validation: about 450 production lines and 350 test lines.

### Child 5: Shared Semantic Normalization for SQL Lowerings

Move empty-string equality rewriting and shared operator normalization out of
the runtime-only `Evaluate` path into a reusable normalization step. This child
owns the known `Field = ""` / `Field <> ""` drift for every SQL lowering by
routing both SQL-lowering families through that same step in one PR:

- `formula.ToSQL` / `ToSQLWithMapper` for query filters, analytics filters,
  and RLS predicates
- analytics formula-field projection compilation for active formula views

Atomicity proof: callers keep the same public API, and no projection-specific
temporary blank-comparison rule is introduced. Runtime, predicate SQL, and
projection SQL all read the blank-comparison rule through one shared
pre-lowering normalization step, with parity tests pinning all three paths.

Scope validation: about 400 production lines and 350 test lines.

### Child 6: Aggregate Removal From Formula Projection DSL

Remove child-record aggregate formulas from the active formula-field language.
This child owns the concrete extraction path that the target IR requires:

- reject `SUM`, `COUNT`, and `AVG` as formula-field projection functions with a
  metadata error that points pack authors to rollup metadata / `RollupService`
- keep scalar `MIN` and `MAX` as ordinary formula functions after Child 4 has
  separated them from aggregate parsing
- remove `AggregateExpr` from `formula_dsl_ast.go`
- remove `parseAggregateOrFunc` for child-object aggregate expressions
- remove aggregate marker emission from `formula_dsl_compiler.go`
- remove aggregate JOIN marker resolution and pass-through plumbing from
  `formula_dsl_viewbuilder.go`
- delete or rewrite aggregate formula-view tests so rollup behavior is covered
  by rollup metadata tests, not formula projection tests

Atomicity proof: the old aggregate projection parser/compiler/view-builder
path is removed in the same PR that introduces the rejection and rollup
redirection message. There is no intermediate state where aggregate semantics
are accepted by the parser but silently lowered by a deprecated path.

Scope validation: likely over the normal limit. If it exceeds 500 production
lines, split only at a fail-closed boundary: first reject new aggregate
formula-field metadata while preserving existing persisted rows, then remove
the parser/compiler/view-builder aggregate machinery after a migration or
compatibility check proves no persisted active formula field still depends on
it.

### Child 7: FormulaMode Deprecation Stage 1

Preserve `active` and `frozen` wire values. Update new authoring flows to omit
mode when the default is sufficient, while backend normalization still persists
the compatibility value needed by existing view and snapshot paths. The design
PR corrected the inaccurate static descriptions so future work starts from the
actual semantics instead of the inverted comments.

Atomicity proof: no wire value is renamed, no persisted row is migrated, and
every runtime branch that still reads `FormulaMode` keeps working.

Scope validation: about 200 production/docs lines and 150 test lines.

### Child 8: Predicate Lowering Authority

Replace `SQLWalker.visitCall`'s independent function switch with
FormulaSpec-driven predicate-SQL lowering. Keep the existing expr-lang parser
for this child, but the function/operator authority becomes shared with the
runtime and projection lowerings.

Atomicity proof: query, analytics predicate, and RLS callers still call
`formula.ToSQLWithMapper`; only the lowering internals switch authority.

Scope validation: about 500 production lines and 450 test lines.

### Child 9: Flow Structured Criteria Adapter

Convert Flow decision, loop, record-filter, and scheduled-start criteria into a
shared structured-criteria AST that uses `OperatorSpec`. Remove the independent
`sfToSQL` operator map and scheduled-start allowlist after the adapter is the
only path. Keep field-aware multi-picklist helpers behind the shared
`INCLUDES` / `EXCLUDES` operator implementation.

Atomicity proof: all Flow structured criteria move together because splitting
runtime and SQL criteria would preserve the current divergence.

Scope validation: likely near the limit; if it exceeds 500 production lines,
split only by a stable adapter boundary: first introduce the adapter and parity
tests with both old callers, then switch all Flow callers and delete old
switches in the next PR.

### Child 10: Approval Structured Criteria Adapter

Convert approval structured criteria into the same structured-criteria AST.
Delete the local `evaluateCriterion` operator switch and its fail-open unknown
operator default.

Atomicity proof: approval formula criteria already use the formula engine;
this PR moves only the separate structured form and keeps approval process
metadata semantics unchanged.

Scope validation: about 300 production lines and 250 test lines.

### Child 11: Canonical Parser and IR Cutover

After the catalogs, contexts, semantic normalization, and structured criteria
are shared, introduce the canonical typed AST / IR and move one live parser
boundary at a time. Prefer the analytics recursive-descent parser as the
starting point because it already produces NexusCRM-owned nodes; extend it for
the runtime-only constructs before replacing expr-lang parsing in formula text
validation and evaluation.

Atomicity proof: this is the first child that changes grammar authority, and
it happens only after every lowering already agrees on catalog and context.
If the line budget is too high, split by live context with a shared parity
suite that prevents two grammars from accepting different Salesforce formula
text without an explicit expected-divergence entry.

Scope validation: this is expected to need multiple PRs; each sub-PR must name
the contexts it cuts over and the old parser entry points it deletes.

## Rejected Plans

1. Rewrite the parser first.
   That creates a new IR before the runtime, predicate SQL, projection SQL, and
   structured criteria call sites are ready to consume it. It either ships
   unused infrastructure or exceeds a safe PR size.

2. Rename `FormulaMode` values to clearer names.
   That still exposes backend topology as admin metadata and breaks existing
   metadata roundtrip clients. The correct first step is deprecation, not
   renaming.

3. Fold analytics `allowedFuncs` into the Salesforce function catalog.
   `allowedFuncs` is a SQL injection guard for pass-through SQL functions. It
   is not an admin-visible formula registry and must stay lowering-internal.

4. Remove aggregates by deleting `SUM`, `COUNT`, `MIN`, `MAX`, and `AVG`
   together.
   Rollup aggregates should leave formula-field projection, but scalar `MIN`
   and `MAX` are valid formula functions and already have a compiler lowering.

5. Keep structured criteria out of scope.
   Flow and approval criteria are formula-shaped authoring surfaces with their
   own operator vocabularies. Excluding them would leave the largest semantic
   drift after function registries are unified.

## Design Challenge

Challenge: "A function spec alone does not fix operator drift."

Decision: accepted. The design includes `OperatorSpec` and shared semantic
normalization. Empty-string equality and multi-picklist set semantics are
operator-level rules, not function-list rows.

Challenge: "The analytics SQL allowlist is not a formula spec."

Decision: accepted. The projection lowering may consult `FormulaSpec`, but the
SQL pass-through allowlist remains a separate security boundary.

Challenge: "Starting with parser collapse is too risky."

Decision: accepted. Parser and IR cutover is last, after contexts, function
catalogs, operator semantics, and lowerings share authority.

Challenge: "FormulaMode cannot be fixed by renaming values."

Decision: accepted. The migration preserves existing values and removes the
authoring dependency before any future storage cleanup.

Challenge: "INCLUDES is not merely divergent; active projection SQL is broken."

Decision: accepted. Projection SQL gets an early fix in Child 4 because the
current `FIND(...)` emission is invalid for TiDB.

Verdict (original arbiter, 2026-05-17): PASS for the *shape* of the staged
design — each child removes one authority split, deletes or gates the old
authority in the same PR, and leaves no intermediate state where a new
declaration surface exists without an enforcing caller or parity test.

This verdict is about the plan's structure, NOT execution-readiness. As of
2026-05-30 only the function-spec layer (VS1–VS4) has landed; OperatorSpec,
FormulaContext, the INCLUDES SQL fix, aggregate removal, FormulaMode
deprecation, and the IR/parser cutover are all unbuilt on `main` (see
Execution Status at the top). The earlier `100% confident` phrasing overstated
this and has been removed. @IANTHEREAL's 2026-05-29 review lists the tightenings
required before any later child — especially Child 11 (IR) — can be treated as
ready.

## Verification Plan

Focused gates per child:

- spec parity tests for every existing function and operator table
- runtime/projection/predicate acceptance tests for shared functions
- context-validity tests for `PRIORVALUE`, `ISCHANGED`, formula-field
  projection, query predicates, and pack verify
- runtime/predicate/projection parity fixtures for NULL, empty string,
  `ISBLANK`, `NULLVALUE`, `BLANKVALUE`, and `INCLUDES`
- Flow structured criteria parity tests for runtime and SQL filter paths
- approval structured criteria tests that unknown operators fail closed
- FormulaMode compatibility tests proving existing `active` / `frozen` rows
  still route to view computation / engine snapshot respectively

Baseline checks:

- `cd backend && go test ./...`
- `cd shared && go test ./...`
- `cd frontend && npm run build`
- `git diff --check`

When backend tests or commands touch `backend/internal/controlplane/packs`, set
`NEXUSCRM_OFFICIAL_APP_PACKS_ROOT=/abs/path/to/nexuscrm-app-packs`.