Formula fields

A formula field is a computed, read-only field whose value is a deterministic expression over other fields on the same record and over fields reached by explicit reference hops. It belongs to the pure tier: no side effects, no writes, statically analyzable, evaluated at read or save time depending on how it is materialized. The pure tier is a compiler-enforced subset of the one typed expression language — the same grammar used by roll-up conditions, validation rules, and calc functions — narrowed by the compiler to reject anything effectful.

The model

A formula field declares a return type and a body. The return type is fixed at authoring time and is part of the field’s contract; the body is an expression that must type-check to that return type.

Where the value lives is a materialization choice, not a property of “being a formula.” The kernel supports three modes over the same declared expression:

Mode	Postgres mechanism	Evaluated	Read cost	Consistency
Stored	`GENERATED ALWAYS AS (expr) STORED`	at row write	zero	always consistent (same-row only)
Virtual	plain `VIEW` column	at read	per read	always fresh
Materialized	`MATERIALIZED VIEW` / trigger-maintained summary	at refresh	zero	as fresh as last refresh (drift is surfaced)

Same-row scalar derivations default to stored generated columns — computed once at write, zero read cost, and never stale. Expressions that read across a reference hop cannot be stored generated columns in stock Postgres (a generated column may reference only same-row, non-generated columns), so cross-object formulas fall back to a virtual view or a materialized/trigger-maintained column.

The kernel infers the materialization mode from the static dependency graph and the field’s read/write ratio, and the author may override it in the component. The inference is fixed: a same-row scalar becomes a stored generated column; a low-fan-out single-value cross-reference derivation becomes a trigger-maintained denormalized column (fresh within the transaction that changes the referenced row); a read-heavy or aggregating cross-object derivation becomes a materialized column refreshed on a cadence, with its drift from the recomputed value surfaced. A frequently-read, rarely-written derivation biases toward a stored/materialized column; a rarely-read, frequently-written one biases toward a virtual view so the write path stays cheap. The author override is the escape hatch when the read/write profile is known better than the graph can infer it.

Read/write semantics: a formula field is read-only in every surface. It is never accepted in an insert or update payload; the save order of execution has no step that writes it. It participates in validation and in other formulas as an input, but nothing writes to it.

Tier: pure. The compiler rejects a formula body that calls an effectful construct, references a mutable/VOLATILE function, or attempts a write. Purity is what makes the three materialization modes interchangeable and what makes push-down safe.

Authoring

The authoring surface is the expression editor. A formula component declares three things: the return type, the blank-handling policy, and the body. The body is written in the typed expression grammar; the compiler checks that it produces the declared return type and that every construct it uses is in the pure subset.

Return types

The scalar return types mirror the incumbent’s set so migrated formulas keep their contract: boolean, text, number (arbitrary-precision numeric with declared scale), currency, percent, date, datetime, time. The return type is part of the field contract and is fixed at authoring time.

Grammar

A formula body is written in the platform’s one typed expression language — TypeScript syntax, no separate formula dialect. What makes it a formula is the contract of the slot it is authored in (this return type, pure tier), not a different vocabulary.

Operators. The TypeScript operator set, minus what an exact-decimal, side-effect-free expression cannot mean:

Arithmetic: + - * / % **, with ( ) grouping. All arithmetic is exact decimal, never IEEE binary.
Comparison: == != < > <= >=. There is no ===, because there is nothing for it to distinguish: operands are statically typed and the language has no implicit coercion, so == is already exact equality.
Logical: && || !.
Null: ?? (nullish coalescing) and ?. (optional chaining).
Conditional: cond ? a : b.
Text: + for concatenation, and template literals — `${record.first_name} ${record.last_name}`.

Absent by construction: assignment (=, +=, ++), await, new, and any statement form. An expression evaluates to a value and does nothing else — that is the pure tier’s contract expressed in the grammar itself.

Field references. The record under evaluation is bound to record, always explicitly. A field is a typed property of it: record.sell_price. Identifiers are the field’s API name verbatim, so one name identifies the field in the expression, in the metadata component, and as the Postgres column — there is no second casing convention to translate between.

A reference field is a typed property whose type is the related object, so crossing a relationship is ordinary property access: record.account.owner.region. Each crossing is one edge the compiler records in the static dependency graph and one join it must plan, and the editor annotates the hop inline with its planner cost. The incumbent’s problem was never the dot — it was that the dot’s cost was invisible and silently drew down a shared per-object budget (see How Salesforce does it). Dot-walking a typed object graph is what the syntax already means; the fix is making the cost visible, not inventing a heavier notation for it.

Standard library. Two shapes, both ordinary TypeScript call notation:

Methods on typed values, where the value’s own type carries the operation: record.name.trim().toUpperCase(), record.code.startsWith("PV-"), record.title.slice(0, 30), record.tags.includes("rush"), record.closed_on.year, record.opened_on.addMonths(3).
Free functions for what is not a method on a value: round(x, 2), mround(x, 0.25), ceil, floor, trunc, abs, min, max, sqrt, exp, ln, log, isBlank(x), isNumber(x), today(), now(), and — over a Geolocation field — distance, isWithin, latitude, longitude (see Places and distances).

The library covers the incumbent’s function set, so every migrated formula has a destination — but several of its built-ins disappear into the grammar rather than being ported. IF(a, b, c) is a ? b : c. AND/OR/NOT are &&/||/!. BLANKVALUE(x, 0) is x ?? 0. ISPICKVAL(status, "Confirmed") is record.status == "Confirmed". CASE is a chain of ternaries. Migration is therefore a mechanical transpile — a one-time converter over the AST, not a hand rewrite — and the result is shorter than the original.

The library is deliberately not grown past parity. Every extension beyond it ships as a calc function rather than a new built-in, because a calc function is purity-verified and version-pinned at registration, whereas each added built-in enlarges the compiler’s trusted, safe-to-compile surface. Built-ins are part of the language; calc functions are registered, version-pinned (content-addressed) user-defined units such as markup(cost, pct). A formula calls both with identical syntax and may invoke either freely. Each calc function carries its own type signature and is itself compiler-verified pure, so a formula that resolves entirely to pure inputs, built-ins, and pure calc-function calls is pure by construction. Because a calc-function reference is version-pinned, a stored formula result can be tagged with the exact function versions that produced it.

Places and distances

A Geolocation field holds a place — one value carrying a latitude and a longitude, not two loose numbers — and four free functions read one:

Call	Gives
`distance(a, b, unit)`	How far apart two places are, as a number in `unit`.
`isWithin(a, b, amount, unit)`	Whether `b` lies within `amount` of `a`. A place exactly on the boundary is within it.
`latitude(a)` / `longitude(a)`	Either coordinate on its own, as an exact decimal.

The unit is required, and it is written literally. It is one of m, km, or mi, spelled out in the formula: distance(record.site, record.depot, 'km'). There is no default, because a bare distance is a number whose meaning depends on who reads it. It must be a literal rather than something the formula works out as it runs, for the same reason a configuration key must be: a unit assembled at evaluation time cannot be checked while you are writing the formula, and the first anyone would learn of a typo is a distance in the wrong scale sitting on a live record. A unit outside those three is refused where it is written, and the message names the three it knows.

Distance is measured on the spheroid, not on a sphere. The globe is flattened at the poles, and the shortest path across it is measured accordingly. This matters because the platform can already filter records to those near a point, and that filter measures the same way: a formula that measured on a sphere instead would disagree with it by roughly half a percent — about 25 metres on a 5 kilometre radius — so a record could sit inside the filter and outside the formula that measured the same thing. Agreeing on the definition is what stops the product contradicting itself.

The arithmetic is exact decimal like every other formula value, so a distance is reproducible run for run and machine for machine rather than depending on the floating-point behaviour of whatever hardware evaluated it.

A blank place gives a blank answer, never zero. Latitude 0, longitude 0 is a real place in the Gulf of Guinea, so treating an empty field as the origin would not be a cautious default — it would confidently place every incomplete record a few hundred kilometres off the coast of Ghana. A distance involving a blank place is blank, and so is an isWithin over one.

These four are evaluated in the platform, not pushed into the database. A formula field mints no stored column for them, so there is one implementation and one answer. A second implementation down in SQL would agree with the first only to a few decimal places, and two subtly different answers to “how far apart are these” is worse than one.

Worked example

A same-row derivation compiled to a stored generated column:

// field: invoice.margin_pct → Percent, blanks as zero
record.sell_price > 0
  ? (record.sell_price - record.total_cost) / record.sell_price
  : 0

A cross-object formula, crossing two relationships:

// field: invoice.account_owner_region → string
record.account.owner.region

The first compiles to GENERATED ALWAYS AS (...) STORED because every input is same-row. The second crosses relationships, so it compiles to a view column (or a trigger-maintained column) and is registered as two edges in the dependency graph.

Optional chaining is how a nullable reference is handled without a wrapper function — if the invoice has no account, the expression is null rather than an error:

// field: invoice.account_owner_region → string
record.account?.owner?.region ?? "Unassigned"

Semantics & evaluation

Timing. A stored formula is evaluated during the transactional write, as part of shaping the record before commit — it is derived, not authored, and it is settled before any after-save effect runs. A virtual formula is evaluated at read time by the query planner. A materialized formula is evaluated at refresh. In all three cases the formula is computed in one pass with no re-entrancy: a formula cannot trigger an effect that rewrites its own inputs mid-save.

Recompute of dependents. When a referenced row changes, the derivations that depend on it recompute according to the same fan-out budget that governs the field. Same-row stored generated columns are synchronous by construction — Postgres recomputes them in the writing statement. A trigger-maintained cross-table column recomputes synchronously inside the transaction that changed the referenced row when its dependent set is within the per-object write-amplification budget, so the derived value is transactionally consistent. When the dependent set exceeds that budget, the recompute is enqueued as a post-commit async job and the field is marked stale until it completes; the staleness is surfaced the same way a materialized column’s refresh drift is, never left silent. The default is therefore synchronous-when-bounded, async-when-not, chosen per field from its measured fan-out rather than configured by hand.

Null / blank handling. Each formula declares a blank policy, mirroring the incumbent’s two modes:

blanks-as-zero — a blank numeric operand coalesces to 0 before arithmetic.
blanks-as-blank — a blank operand propagates, so arithmetic touching a blank yields blank/null.

The policy is compiled as an explicit COALESCE discipline on the generated expression rather than left to ambient SQL null semantics, so the same body produces identical results in stored, virtual, and materialized modes. Per-expression, ?? and ?. override the global policy locally — record.discount ?? 0 forces zero-coalescing inside a blanks-as-blank formula, and record.account?.region opts one hop out of propagating an error. isBlank(x) tests the field-type notion of empty (null, or empty text); x == null tests strict null.

Precision & determinism. Numeric results use Postgres numeric with the field’s declared scale, so precision is explicit rather than implied — an authored scale, not the incumbent’s implicit ~18-significant-digit internal carry. Rounding does not need bug-for-bug replication of that internal behavior: Postgres round(numeric) rounds half away from zero (1.45 → 1.5, −1.45 → −1.5), the same direction the incumbent’s ROUND uses (Round() in Salesforce), so ROUND-based formulas migrate identically. The only reachable divergence is at the last digit of an unrounded chained division whose intermediate carried more significant digits under the incumbent; declaring adequate scale on such a field removes it. Determinism is otherwise guaranteed structurally: every input is a field or a pure, version-pinned calc function, and the compiler forbids VOLATILE functions — so the same inputs always yield the same output, and a result can be reproduced from the recorded formula-hash and calc-function versions.

Type contracts. The body must type-check to the declared return type; a mismatch is a compile-time error, not a runtime coercion. Reference hops are type-checked hop by hop against the relationship’s target object.

Limits, and the reasons behind them

The incumbent’s formula limits are real and are cited below. Some guard a constraint the Postgres kernel shares; some guard a constraint the kernel removes because it has a better mechanism.

Concern	Salesforce exact limit (cited)	Why the limit exists	CAOS equivalent
Source expression length	3,900 characters max (formula size tipsheet)	Cap on the human-authored source string.	A source-length guard is cheap and worth keeping as an authoring sanity bound; not a hard architectural limit.
Saved definition size	4,000 bytes (formula size tipsheet)	Storage cap on the persisted definition (bytes ≠ chars for multibyte).	The definition is a metadata row; no fixed byte cap, but a sane bound is retained.
Compiled size	5,000 bytes compiled (compile-size tipsheet) — and shortening whitespace/field names does not reduce it; nested functions, spanned relationships, and CASE branches drive it	A blanket guard on per-read execution cost, because the platform does not expose a truthful cost figure to the author.	Replaced by a planner-cost budget: the static dependency graph plus `EXPLAIN` cost gives a truthful per-formula cost, so cheap-but-large expressions are admitted and genuinely expensive ones are rejected with a real reason.
Reference traversal depth	up to 10 relationships away, per Salesforce Help (cross-object formula help)	Bounds JOIN depth the compiled query must traverse per read.	Depth is admitted or rejected on proven planner cost, not a fixed number — but deep chains still cost, so the budget is the real guard.
Distinct relationships per object	10 unique relationships per object, cumulative across all formula fields, validation rules, and lookup filters on that object, per Salesforce Help (cross-object build tips)	Caps the total distinct JOIN paths the platform must maintain and invalidate per object — a metadata-graph fan-out guard, not a per-formula guard.	The dependency graph makes fan-out measurable per edge, so the equivalent is a per-object fan-out/write-amplification budget rather than a shared count of 10. The cost does not vanish — it moves to write time — so an equivalent guard is still needed.
Child aggregation in a formula	not possible — requires a Roll-Up Summary (master-detail only) or code (cross-object help)	Aggregating child rows at read time is unbounded per read.	Offered as a first-class computed field via materialized view / trigger-maintained summary — a genuine capability gap the kernel closes (see below).
Formula fields per object	incumbent guidance sits around a few dozen	Object-level metadata budget.	No hard per-object formula count — there is no inherent mechanism limit; the real bound is the per-object write-amplification/fan-out budget above, so a page of formulas is admitted while genuinely expensive graphs are rejected with a reason.

How Salesforce does it

Mechanism. A Salesforce formula field is a read-time expression evaluator. The value is not stored — it is recomputed on every read (record view, report, API read, or a referencing rule). Cross-object reads use an implicit dot/colon path (Account.Owner.Name) that walks parent-ward up to 10 relationships, and all such paths on an object share a cumulative budget of 10 distinct relationships across every formula, validation rule, and lookup filter on that object (cross-object build tips). The whole expression compiles to a bounded query artifact capped at 5,000 bytes (compile-size tipsheet). Forced recomputation is a distinct, deliberate API rather than the norm.

A documented consequence: a referenced record changing does not proactively refresh a cross-object formula on the reading record until that record is itself re-read or undergoes DML. This read-time-staleness is expected behavior in the incumbent, and it is the single biggest cross-object correctness gotcha.

Where CAOS is genuinely better:

Materialization is a choice. Stored GENERATED … STORED columns give same-row formulas zero read cost and always-consistent values — strictly better than recompute-every-read for the common single-object case.
Child aggregation as a first-class computed field via materialized view / trigger-maintained summary, closing the incumbent’s Roll-Up-Summary/master-detail-only restriction.
Push-based invalidation. The static dependency graph plus triggers (or logical decoding) incrementally recomputes or invalidates dependents when a referenced row changes — removing the read-time-staleness gotcha instead of documenting it as expected.
Truthful cost signal. EXPLAIN/planner cost replaces a blanket byte cap with a real per-formula cost.
Content-addressed versioning. The normalized AST hashes to a stable identity, so deploys diff exactly and a stored result can carry the formula-hash that produced it — where the incumbent’s formula is a mutable string with no version identity.

Where it is mere parity (not oversold): the return-type taxonomy, operator set, scalar function library, virtual read-time evaluation (a plain view), and the blank-as-zero/blank-as-blank policy (a COALESCE discipline) all map directly and are table-stakes.

Costs and risks:

Stored generated columns cannot reference other tables or other generated columns in stock Postgres, so multi-hop derivations fall back to views/matviews or trigger-maintained columns — the biggest architectural constraint to design around.
Materialized-view freshness is a distributed-systems problem; trigger-maintained summaries reintroduce the invalidation-ordering and write-amplification the 10-relationship cap was quietly avoiding. Deep chains move cost from read time to write time; they do not remove it. Where a matview’s change set is small and infrequent, an incremental-view-maintenance extension such as pg_ivm can maintain it at cost proportional to the change rather than a full refresh — an optimization over the default trigger/refresh path, not a replacement for it, since IVM loses to a from-scratch refresh when a base table churns heavily.
Compiling arbitrary author expressions to SQL is an injection / resource-exhaustion surface, requiring an allow-listed AST and a cost governor — mandatory work, not free.
The planner can misestimate; a formula column in a large list view can degrade to a seq-scan without per-formula cost budgets and statement timeouts.
Purity of calc functions must be verified, not trusted from an annotation — a mislabeled non-pure function silently corrupts stored/materialized results.

Metadata & deploy representation

A formula field is a component in the object’s field catalog. Canonical JSON component:

{
  "key": "invoice.margin_pct",
  "label": "Margin %",
  "type": "formula",
  "body": {
    "returnType": "percent",
    "blankPolicy": "blanks_as_zero",
    "materialization": "stored",
    "expression": "record.sell_price > 0 ? (record.sell_price - record.total_cost) / record.sell_price : 0"
  }
}

The presence of an expression in the body is what distinguishes a formula field from a stored authored field; returnType, blankPolicy, and materialization complete its contract. The component’s identity is the content-addressed hash of its normalized AST, so retrieve → diff → deploy produces exact diffs and exact rollbacks.

Salesforce Metadata API analog: a formula field is a CustomField component whose <formula> element carries the expression; <formulaTreatBlanksAs> (BlankAsBlank | BlankAsZero) carries the blank policy, and <type> carries the return type. It is deployed as objects/<Object>/fields/<Field>.field-meta.xml in SFDX source format (CustomField — Metadata API Developer Guide). The distinction is that the incumbent’s <formula> is a mutable string with no version identity, whereas the CAOS component’s identity is its AST hash.