Shipped template formula fields silently evaluate to null on @objectstack 15.1.1 — daysBetween / Timestamp−Timestamp / floor in stored formulas (hr tenure_years, time_off days)

[os-zhuang]

Summary

While converting the templates repo's roll-ups (framework#1867 follow-up) I boot-verified several stored Field.formula fields that silently evaluate to null on the pinned runtime (@objectstack/formula@15.1.1). No parse error, no runtime error, no objectstack build warning — the field just returns null. This is the exact silent-miss class ADR-0053 / ADR-0032 exist to kill, but for function calls / arithmetic, not the == today() equality case that #3183 just fixed.

Two of these are real, user-facing fields in the shipped hr template:

Field	Expression (origin/main)	Runtime value (15.1.1)
hr_time_off_request.days	(record.end_date - record.start_date) + 1	null for every request
hr_employee.tenure_years	floor((today() - record.hire_date) / 365)	null for every employee

hr_time_off_request.days is the leave-duration in the core time-off workflow — every seeded request shows a blank "Calendar Days".

Verified repro

Fresh objectstack dev --fresh on hr (@objectstack 15.1.1), signed in, GET /api/v1/data/...:

• hr_time_off_request.days → null (all 6 seeded rows).
• hr_employee.tenure_years → null (all 7 rows), and I could not get a non-null result with any of:
  • floor((today() - hire_date) / 365) (original)
  • daysBetween(record.hire_date, today()) ← bare, still null
  • daysBetween(date(record.hire_date), today()) ← explicit coercion, still null
  • daysBetween(...) / 365, daysBetween(...) / round(365) ← still null

By contrast, today(), daysFromNow(n), addDays(date, n), round, abs, and date ordering comparisons all work in 15.1.1 (I used daysFromNow/addDays to fix two sibling formulas — see below).

What appears to be going on

daysBetween is correct in framework HEAD — implemented in packages/formula/src/stdlib.ts:176, listed in CEL_STDLIB_FUNCTIONS (validate.ts:446), and engine-tested (cel-engine.test.ts:449,456, including a date-string field arg). But calling it from a stored formula field on the published 15.1.1 returns null, and templates pin 15.1.1 (the latest published — there is nothing newer to bump to). So either the working daysBetween stored-formula path never shipped in 15.1.1, or the end-to-end stored-formula path has a gap the engine-level unit tests don't cover. Two related operators have no support at all and also null out silently: Timestamp − Timestamp (→ a number) and a floor builtin — both things template authors reach for naturally.

Related prior work: #1302 (original "missing daysBetween/date-arithmetic", closed not_planned then implemented via ADR-0053), #1979 (thread ctx into applyFormulaPlan), #3183 (silent dateField == today(), fixed 2026-07-18). This is the same silent-null family, one layer over: function/arith calls in stored formulas rather than equality.

Ask

1. Confirm end-to-end + release: add a test that a stored formula field (via engine.find() → applyFormulaPlan, real driver YYYY-MM-DD round-trip) using daysBetween returns the right value, and make sure it ships in the next published @objectstack/formula. Then templates can restore time_off.days / tenure_years.
2. Fail loudly, don't null: an unresolved function / unsupported operator in a formula should be a build-time error (like Bare dateField == today() silently returns false — fix via AST temporal-comparison rewrite in the CEL engine #3183's philosophy — cel-to-filter already fails closed), not a silent runtime null. Silent null is the trap here.
3. Template regression coverage: nothing currently catches that a shipped template's formula field evaluates to null on the pinned release — a boot-and-assert-non-null smoke over template formula fields would have caught all of these.

What I already shipped template-side (using functions that DO work in 15.1.1)

Same root class, fixable without daysBetween, so done in templates:

• compliance_control.is_overdue_for_review: last_assessed_at + freq*86400000 → addDays(last_assessed_at, freq) < today() (templates#92, merged).
• hr_document.is_expiring_soon / expiry_status: today() + 30/60 → daysFromNow(30/60) (templates#93).

time_off.days and tenure_years are left for this framework fix — they need a working daysBetween (or Timestamp subtraction), which 15.1.1 doesn't provide in stored formulas.

[os-zhuang]

Root cause found — it's not a daysBetween / stored-formula-path gap

Ran the real @objectstack/formula engine (celEngine.compile + celEngine.evaluate) against every one of these expressions. daysBetween is not broken — it evaluates correctly through the stored-formula path:

daysBetween(record.hire_date, today())        => OK = 2318      (hire_date = "2020-03-15")
daysBetween(record.hire_date, today()) / 365  => OK = 6

So the hypothesis "the working daysBetween never shipped in 15.1.1 / the stored path has a gap" is a red herring — and importantly, re-shipping daysBetween would not have fixed tenure_years. There are actually three independent CEL-engine gaps, and the reason every daysBetween rewrite you tried still nulled is #3 below (the ? … : null guard), not daysBetween itself.

The three gaps (all verified)

RC1 — floor / ceil are not registered. stdlib.ts registers abs/round/min/max only. floor(...) → found no matching overload for 'floor(dyn)'.

RC2 — Timestamp − Timestamp returns a Duration, not a number. cel-js does have the overload (GPT '-' GPT → Duration), so end_date - start_date yields a Duration object (coerces to {}), and there is no Duration + int / Timestamp + int overload — so (end - start) + 1 and today() + 30 fault. (today() - hire_date first faults Timestamp − string, gets hydrated to Timestamp − Timestamp, becomes a Duration, then faults on / 365.)

RC3 — the blessed guard ? <number> : null idiom is rejected. cel-js's ternary unifier requires both branches to share a type, and int/double do not unify with null. Even true ? 5 : null fails: "Ternary branches must have the same type, got 'int' and 'null'." This is why all your tenure_years attempts nulled — they were all inside record.hire_date != null ? <X> : null:

record.hire_date != null ? daysBetween(record.hire_date, today())       : null  => ERR[type] int vs null
record.hire_date != null ? daysBetween(date(record.hire_date), today()) : null  => ERR[type] int vs null
record.hire_date != null ? daysBetween(record.hire_date, today()) / 365 : null  => ERR[type] int vs null
daysBetween(record.hire_date, today())                                          => OK = 2318   ← unguarded

… ? dyn(daysBetween(...)) : null works, which confirms it's the type-unification, nothing to do with daysBetween or the driver round-trip.

Repro was run against the current framework HEAD engine (cel-js@8, stdlib.ts unchanged since #2697). All three gaps are inherent to cel-js + the current stdlib catalog, so they're equally present on 15.1.1 — which matches every observation in the report above.

The "silent" half is a real gate hole

There's already a build gate that compile-checks formula fields (validate-expressions.ts:201). It's asymmetric:

Formula	build compile	runtime evaluate
floor(...), today() + 30	ERR (caught)	null
end_date - start_date + 1, date + n*ms	OK ✅	null ❌
catalog-correct guard ? num : null	ERR (RC3)	null

So date−date arithmetic compiles clean and nulls at runtime = the silent hole. And the floor/today()+30 ones that the gate does catch shipped anyway → the templates repo isn't running validateStackExpressions in CI. Both halves need closing.

Answers to the three Asks

1. Confirm end-to-end + reship — daysBetween already works end-to-end; no reship needed. The missing piece for tenure_years is RC3. Fix: an AST rewrite making guard ? <typed> : null unify (wrap the non-null branch in dyn(...)), mirroring the merged Bare dateField == today() silently returns false — fix via AST temporal-comparison rewrite in the CEL engine #3183 rewriteTemporalEquality. Then record.hire_date != null ? daysBetween(record.hire_date, today()) / 365 : null compiles and evaluates to 6.
2. Fail loudly, don't null — agreed, and the gate hole is precise: date/timestamp arithmetic compiles clean because record.<field> is dyn at check time. Fix: type date/datetime fields as google.protobuf.Timestamp in the soundness checker so Timestamp − Timestamp → Duration, and Duration ± number → build error with "date arithmetic isn't supported — use daysBetween(a,b) for spans, daysFromNow(n)/addDays/addMonths to shift." (Comparisons <=/== keep their Timestamp×Timestamp overloads → stay green; must be proven red-on-broken / green-on-existing before landing.)
3. Template regression coverage — agreed; the sharper prevention is wiring validateStackExpressions into templates CI (it already catches floor / today()+30) plus the RC2 gate-hole fix above (so date−date also turns red).

Proposed fix

Guiding principle (this repo's existing #1928 design law): narrow, unambiguous catalog + everything off it fails loud at build. Make-work only where the construct is unambiguous and universally expected; reject-and-redirect where it's ambiguous or diverges from CEL.

• ① RC3 — fix guard ? number : null (compile + eval) via AST rewrite. (genuine engine bug — a formula field is inherently nullable)
• ② RC1 — register floor/ceil + add to the catalog (skill + docs). (unambiguous, universally expected; note int-division floors toward zero, not −∞, so it's not a safe substitute)
• ③ RC2 — reject + redirect, not make-work. date − date / today() + N have ambiguous units (day vs instant field) and diverge from CEL's Duration semantics; making them "work" trades silent-null for silent-wrong, which is worse for an AI author that only self-corrects on red build gates. So: gate turns red + points to daysBetween/daysFromNow/addDays/addMonths (which cover 100% of real date math — zero capability loss).

Template rewrites (the two left for framework, once ① lands)

tenure_years: record.hire_date == null ? null : daysBetween(record.hire_date, today()) / 365
days:         record.start_date == null || record.end_date == null ? null : daysBetween(record.start_date, record.end_date) + 1

→ evaluate to 6 and 5 (inclusive) respectively.

Two PRs: framework (①②③ + red→green tests + changeset; repo is on 16.0.0-rc.0) and templates (rewrite time_off.days + hr_employee.tenure_years, wire validateStackExpressions into CI).

[os-zhuang]

Fix up — two PRs

framework → #3348 (fix(formula), ready)
Closes all three engine gaps:

• ① cond ? <value> : null now compiles + evaluates (AST wraps the non-null branch in dyn(...), mirroring Bare dateField == today() silently returns false — fix via AST temporal-comparison rewrite in the CEL engine #3183). This is what made every daysBetween rewrite above still null.
• ② floor/ceil registered + added to the catalog (round toward −∞/+∞, so not substitutable by integer division).
• ③ date arithmetic (date − date + 1, today() + 30) is now a build error (os build exits 1) pointing at daysBetween/daysFromNow/addDays — closing the compile-clean-but-runtime-null hole. Sound: ordering/equality/concat of a date field stay clean; a != null guard no longer masks the inner fault.

templates → objectstack-ai/templates#94 (fix(hr), draft)
Rewrites the two remaining fields to daysBetween(...) (verified: days = 5, tenure_years = 6). Blocked on #3348 publishing — on 15.1.1 the null-guard form still nulls, so it merges after @objectstack 16.x ships and the hr deps bump.

On Ask #3 (regression coverage): no new CI step is needed — templates CI already runs objectstack build, and #3348 makes that gate turn red on date arithmetic in a formula, so this class can't ship green again once deps bump.