Formulas — CEL expressions on records

Expressions

ObjectStack uses a single canonical expression language for every place a piece of metadata needs to compute a value or evaluate a condition:

• Formula fields (type: 'formula')
• Predicates — validation condition, sharing condition, field conditional rules (visibleWhen, readonlyWhen, requiredWhen), view section/column visibility (visibleWhen), action disabled, hook condition, flow decisions
• Dynamic seed values — fixtures whose value depends on the install-time clock or identity context

The language is CEL (Google's Common Expression Language, Apache-2.0), evaluated through @objectstack/formula (thin wrapper over @marcbachmann/cel-js) plus the ObjectStack standard library.

Why CEL? Formal grammar, abundant public training corpus (so AI authors emit it natively), AST-first persistence, and sandboxed execution with bounded cost. ObjectStack does not ship a Salesforce-flavor DSL — the previous custom 22-function engine was deleted in M9. See the north-star principles on typed metadata over custom DSLs.

The Expression envelope

Every expression in metadata is persisted as the same envelope:

type Expression = {
  dialect: 'cel' | 'cron' | 'template';
  source?: string;          // surface syntax
  ast?: unknown;            // parsed AST (filled by `os compile`)
  meta?: { rationale?: string; generatedBy?: string };
};

ObjectStack ships three registered expression dialects:

The js expression dialect was retired in v16 (#3278, ADR-0058 addendum) — it was a stub with no engine. Procedural JavaScript is unaffected: it remains the L2 authoring surface as a sandboxed ScriptBody { language: 'js' } in hook/action bodies (a separate enum), not an expression dialect.

All dialects share the same variable scope (record, previous, os.user, os.org, os.env, vars for flow steps), so you author once and never have to relearn the syntax across surfaces.

Template formatting. A template hole is a field path with an optional whitelisted formatter — {{ path | formatter[:arg] }} — with defined value→string semantics (no arbitrary logic; put logic in a CEL field):

tmpl`Deal {{record.name}} — {{record.amount | currency}} closes {{record.close_date | date:long}}`

Formatters: currency[:CODE], number[:decimals], percent[:decimals], date[:short|long|iso], datetime[:…], upper, lower, trim, truncate:N, default:'…', json. Single-brace {x} is not a valid template hole.

At input time you may write a bare string for shorthand — the spec transforms it into the right envelope based on the field type. The compiled artifact always contains the full envelope: source is the canonical persisted form, and ast is an optional opaque structured value.

import { F, P, cel, cron, tmpl } from '@objectstack/spec';
import { ObjectSchema, Field } from '@objectstack/spec/data';

export const Invoice = ObjectSchema.create({
  name: 'invoice',
  sharingModel: 'private',
  nameField: 'display_title', // ADR-0079 — the record title is a designated field; composite titles migrate off the deprecated `titleFormat` to a text formula
  fields: {
    // Composite record title as a text formula, surfaced via `nameField` above.
    display_title: Field.formula({
      returnType: 'text',
      expression: F`"Invoice " + string(record.invoice_no) + " – " + record.customer.name`,
    }),
    total: Field.formula({
      returnType: 'number',
      expression: F`record.subtotal + record.subtotal * record.tax_rate`,
    }),
    po_number: Field.text({
      requiredWhen: P`record.amount > 10000`,
    }),
    // M9.9b — declarative default, evaluated at insert time
    issued_date: Field.date({
      defaultValue: cel`today()`,
    }),
  },
});

The three CEL tagged-template helpers — cel, F (formula alias), P (predicate alias) — are interchangeable; pick whichever reads best at the call site. cron and tmpl produce their respective envelopes.

CEL primer

CEL is documented at cel-spec. Cheat-sheet of what you'll write daily:

has() gotcha: has(record.x) is true whenever the key exists, even when its value is null. To check for "not blank" use isBlank(...) or record.x != null — see stdlib below.

ObjectStack CEL standard library

Registered automatically into every Environment by @objectstack/formula. All functions are pure given a pinned now, which is what makes os build artifacts byte-stable across runs.

Add new helpers in packages/formula/src/stdlib.ts. Keep them pure, dependency-free, and AI-readable.

Variable scope

@objectstack/formula builds the scope per evaluation:

Build-time validation

os build type-checks every expression against the object schema, so a mistake that would silently evaluate to null at runtime is caught before it ships. The same shared validator also runs when a flow is registered (registerFlow), so a flow authored dynamically gets the same verdict. Findings come at two severities: errors fail the build; warnings are advisory (surfaced, not fatal — os validate --strict promotes them to errors).

Integer literals in arithmetic are fine. record.amount / 100, record.price * 2, record.total - fee all work — the engine handles mixed double × int arithmetic, so you never need the 100.0 / 2.0 float-literal workaround. Equality against any type is also safe (record.stage == 5 simply returns false), so only arithmetic/ordering on a genuinely text/boolean field is flagged.

In flow and automation conditions the record's fields are bound bare (stage == "won", not record.stage), so a bare reference is correct there. Instead, a bare identifier that is a near-miss of a real field gets a did-you-mean warning (a genuine flow variable is left alone), and the same type-soundness check applies.

Cookbook

Computed full name (handle nullable parts)

{
  name: 'full_name',
  type: 'formula',
  expression: F`joinNonEmpty([record.salutation, record.first_name, record.last_name], ' ')`,
}

joinNonEmpty skips blank parts, so you avoid the verbose triple-coalesce. Building the string by hand with + would also work, but CEL throws on null + string, so each nullable operand must be wrapped in coalesce(..., '').

Conditional formula

{
  name: 'roi',
  type: 'formula',
  expression: F`coalesce(record.actual_cost, 0) > 0
             ? ((coalesce(record.actual_revenue, 0) - record.actual_cost) * 100.0) / record.actual_cost
             : 0.0`,
}

Predicate (visibility, read-only, conditional required)

A conditional-rule predicate binds whatever its layer binds, and visibleWhen is one key spelled on layers that do not share a scope — object field, per-option, form-view field, page component, flow screen. So write the enclosing structure: the fields: map below is what says object field, the layer on which a predicate binds record (plus previous, and parent on a master-detail line item) and nothing else. A bare { name, type, visibleWhen } fragment names no layer, so neither a reader nor os build can tell which scope it is written against.

ObjectSchema.create({
  name: 'opportunity',
  fields: {
    // Raw (non-factory) spelling — the map key is the field name.
    rating: {
      type: 'select',
      options: [{ label: 'Hot', value: 'hot' }, { label: 'Cold', value: 'cold' }],
      visibleWhen: P`record.status == 'qualified'`,
    },
  },
})

Field.text({
  visibleWhen: P`record.type == 'business'`,
  readonlyWhen: P`record.status == 'approved'`,
  requiredWhen: P`record.amount > 10000`,
})

Dynamic seed dates

Use CEL inside seed records to defer evaluation to install time. The SeedLoader pins a single now per load run, so the same dataset produces identical SHA-1 across builds while still being "fresh" relative to whoever installs the package.

import { defineSeed } from '@objectstack/spec/data';
import { cel } from '@objectstack/spec';
import { Opportunity } from './objects/opportunity.object';

export const opportunitySeed = defineSeed(Opportunity, {
  records: [
    {
      name: 'Acme Q3 Renewal',
      close_date: cel`daysFromNow(45)`,
      created_at: cel`now()`,
    },
  ],
});

Without CEL, new Date() would be evaluated at compile time — your package would ship with the timestamp from your laptop and every customer would see "stale" demo data. With CEL, the date resolves on the customer's clock at install time.

Validation rule

{
  name: 'amount_positive',
  type: 'script',
  condition: P`record.amount <= 0`,
  message: 'Amount must be positive.',
}

The generic CEL rule is type: 'script'. For script rules, the condition is the failure predicate — when it evaluates TRUE the validation fails, so invert the test (here amount <= 0 rejects non-positive amounts).

Hook condition

{
  name: 'notify_on_escalation',
  object: 'case',
  events: ['afterUpdate'],
  condition: P`previous.status != 'escalated' && record.status == 'escalated'`,
  body: {
    language: 'js',
    source: "ctx.log.warn('case escalated', { id: ctx.input.id });",
    capabilities: ['log'],
  },
}

The condition is the CEL part — it binds record / previous directly. The JS body is a different surface: it is wrapped as new AsyncFunction('ctx', source), so inside it the record is ctx.input (there is no bare record), and every ctx API it touches must be covered by a declared capability.

Transitions on bulk writes

The condition above is a transition — record.status == 'escalated' alone would be true on every update of an already-escalated case, so "just became" is only expressible by comparing against previous.

Write it once. A predicate (multi: true) write is N record changes, so hooks in both phases — before* and after* alike, and the record-change flow triggers that ride the latter — are evaluated and fired once per matched row, with previous bound to that row's own pre-write state and record holding that row's own state (stored ⊕ payload) rather than the bare payload. The same condition therefore means the same thing whether the write targets one id or matches a thousand rows:

await data.update('case', { status: 'escalated' }, { multi: true, where: { severity: 'high' } });
// → `notify_on_escalation` fires once per case that ACTUALLY transitioned;
//   cases already escalated do not fire.

The matched rows are read once for the whole batch and reused for every per-row evaluation in both phases, so this costs one extra query per write, not one per row. Above 10 000 matched rows a predicate write against an object carrying per-row hooks in either phase is refused rather than fanned out — one ceiling covers before* and after* together, and it is checked before the first dispatch, so nothing is written and no handler runs. Paginate the write. The refusal is loud (ERR_BULK_PER_ROW_HOOK_LIMIT, naming the event, the matched count and the limit); the platform never silently downgrades it to a single hook call.

Formula patterns

Complex calculations and logic in formula fields.

Conditional Logic

Formula fields are CEL. Use ternary cond ? a : b for conditionals and &&/||/! for boolean logic. Reference fields via record.<field>:

// Tiered pricing
Field.formula({
  label: 'Discount Tier',
  returnType: 'text',
  expression: `
    record.amount > 1000000 ? "Platinum" :
    record.amount > 500000 ? "Gold" :
    record.amount > 100000 ? "Silver" : "Bronze"
  `,
})

// Status indicator
Field.formula({
  label: 'Health Score',
  returnType: 'text',
  expression: `
    record.is_active && record.days_since_contact < 30 ? "Healthy" :
    record.days_since_contact < 90 ? "At Risk" : "Critical"
  `,
})

Date Calculations

// Days until close
Field.formula({
  label: 'Days to Close',
  returnType: 'number',
  expression: 'daysBetween(today(), record.close_date)',
})

// Contract end in 30 days
Field.formula({
  label: 'Expiring Soon',
  returnType: 'boolean',
  expression: 'record.status == "active" && daysBetween(today(), record.end_date) <= 30',
})

// Age in days
Field.formula({
  label: 'Age (Days)',
  returnType: 'number',
  expression: 'daysBetween(record.created_date, today())',
})

Financial Calculations

// Gross margin (computes a number; a formula's type is always 'formula' —
// declare the value type with returnType, and decimal places with scale)
Field.formula({
  label: 'Gross Margin %',
  returnType: 'number',
  expression: 'record.revenue > 0 ? ((record.revenue - record.cost) / record.revenue) * 100 : 0',
  scale: 2,
})

// Weighted pipeline
Field.formula({
  label: 'Weighted Amount',
  returnType: 'number',
  expression: 'record.amount * (record.probability / 100)',
  scale: 2,
})

// Total with tax
Field.formula({
  label: 'Total with Tax',
  returnType: 'number',
  expression: 'record.subtotal * 1.0825', // 8.25% tax
  scale: 2,
})

Text Manipulation

// Uppercase
Field.formula({
  label: 'Code',
  returnType: 'text',
  expression: 'upper(record.sku)',
})

// Has a company email
Field.formula({
  label: 'Internal',
  returnType: 'boolean',
  expression: 'endsWith(lower(record.email), "@example.com")',
})

Lead scoring

// Formula: Lead score
Field.formula({
  label: 'Lead Score',
  returnType: 'number',
  expression: `
    (record.rating == "hot" ? 30 : record.rating == "warm" ? 20 : 10) +
    (record.annual_revenue > 1000000 ? 25 : 0) +
    (record.number_of_employees > 500 ? 20 : 0) +
    (record.industry in ["technology", "finance"] ? 15 : 0)
  `,
})

Best practices

✅ DO:

• Keep formulas simple and readable
• Add comments for complex logic
• Test edge cases (null, zero, empty)
• Use appropriate data types

❌ DON'T:

• Create circular references
• Use formulas for frequently changing data
• Nest ternaries so deeply the expression stops being readable
• Ignore null handling

Determinism contract

Two consecutive os build runs with no source changes must produce byte-identical dist/objectstack.json. CEL plus pinned now is what makes this possible — there are zero compile-time-evaluated Date.now() calls in seed metadata: a CEL seed value travels into the artifact as unevaluated source and only resolves at install. This is a design contract held by review, not a gate — no CI job currently diffs two builds — so if you add a new dialect or stdlib helper, ensure it preserves determinism.

Migration from the legacy formula DSL

The previous custom recursive-descent engine (packages/objectql/src/formula.ts, deleted in M9.5) had a Salesforce-style function set. Mechanical translation:

Salesforce compatibility is not a project goal. Authors targeting Salesforce semantics rewrite their formulas in CEL.

Programmatic API

import { ExpressionEngine } from '@objectstack/formula';

// Compile — parse + type-check, returning the engine-native AST as `value`
const compiled = ExpressionEngine.compile({ dialect: 'cel', source: 'record.x * 2' });

// Evaluate
const result = ExpressionEngine.evaluate(
  { dialect: 'cel', source: 'record.x * 2' },
  { record: { x: 21 } },
);
// => { ok: true, value: 42 }

The low-level engine never throws — evaluate() returns { ok: false, error }. But call sites must not silently swallow that (ADR-0032): os build fails on an invalid expression (with a located, schema-aware message), and at runtime the flow/rule engines throw a loud, attributed error instead of treating a bad expression as false/null. The same validateExpression validator backs os build and metadata registration (and the agent-callable validate_expression tool), so an expression is checked before it ships.

Related

• Field Metadata — declaring type: 'formula' fields
• Seed Data — using cel\...`` for dynamic install-time values
• Hook Bodies — when to use a JS hook vs a CEL condition
• packages/formula/ — engine + stdlib source
• North Star — architecture principles (typed metadata over custom DSLs)