Split out of #16707 ("One more, adjacent, and load-bearing"), where the filer wrote "Say the word and I will split it out." The two lint findings that card carried are already fixed on main (see the triage comment there); this one is not, and it is the only live item, so it gets its own card rather than keeping that one open.
⛔ Filed by the triage seat, unassigned. The measurement below is the original filer's (objectstack-ai/hotclm, @objectstack/* 17.3.0, better-sqlite3 13.0.3, single-org dev posture); ⛔ I did not re-drive it. What I did verify is the in-repo half, marked as such.
The measurement (reported, not re-driven)
sqlite> select typeof(submitted_at), submitted_at from clm_contract limit 1;
text|2026-05-19T00:00:00.000Z
sqlite> select avg(submitted_at) from clm_contract;
2025.9166666666667 ← SQLite's text→numeric coercion: the average YEAR
Downstream, derived: { op: 'difference', of: [avg_a, avg_b] } does not error — it parses, runs, and returns -0.849999999999909: the difference of two average years, a clean plausible number that renders happily on a dashboard tile.
Why this is the dangerous shape
A wrong number that looks wrong gets caught by the first reader. This one does not: -0.85 on a tile labelled "average cycle time delta" is exactly what a correct answer looks like. The filer's own note is the point — "That is the form that would have shipped had the app not asked what the number meant."
Nothing refuses it at any layer: not the schema, not os validate / os lint, not the analytics service, not the renderer.
In-repo half, verified on origin/main 5e53d73d
The annotation the filer flagged is real, and it is not a single site:
git grep -n 'INTEGER epoch' origin/main -- packages/services/service-analytics/src/
strategies/native-sql-strategy.ts:963 "A SQLite `Field.datetime` column carries an INTEGER epoch (a `Date` write)"
strategies/objectql-strategy.ts:1669 "a SQLite `Field.datetime` is an INTEGER epoch (#2034)"
__tests__/native-sql-datetime-filter.test.ts:9, :80
analytics-service.ts:493 "INTEGER epoch (a `Date` write) and ISO TEXT (a REST/JSON write, a `NOW()` …"
plugin.ts:662 "holds BOTH storage forms — INTEGER epoch from a `Date` write, ISO TEXT from …"
⭐ The package already contradicts itself. analytics-service.ts:493 and plugin.ts:662 state the column holds BOTH storage forms depending on the write path; objectql-strategy.ts:1669 states flatly that it is an INTEGER epoch. The filer's row was written through a REST/JSON path, which is precisely the case the first two describe and the third denies. So the reported TEXT reading is consistent with what the package's own newer comments say — it is the older flat claim that is stale.
⚠️ Not measured here: whether NativeSQLStrategy's coerceTemporal (which the :1669 docblock cites as the reason that path needs coercion) is correct for the mixed-storage reality, or is itself written against the flat claim. That is the first thing to establish, and ⛔ it must not be assumed either way from this card.
What is NOT claimed
- ⛔ Not that
AVG() over a datetime should have a defined meaning. It may well be that the right answer is to refuse it.
- ⛔ Not that the remedy is the comment. The filer explicitly declined to pick: "the remedy could be the comment, the storage, or a duration primitive."
- ⛔ Not re-confirmed on a second dialect. On Postgres/MySQL a text→numeric coercion of an ISO string does not silently succeed the way SQLite's does, so the silent half may be SQLite-specific while the meaningless half is not.
Acceptance
- Establish the storage reality first, and write it down in one place: for
Field.datetime on SQLite, which write paths produce INTEGER and which produce TEXT. Every remedy below depends on this, and the package currently states two incompatible answers.
- Reconcile the annotations —
native-sql-strategy.ts:963 and objectql-strategy.ts:1669 must agree with analytics-service.ts:493 / plugin.ts:662, whichever direction the reading settles. ⛔ Do not fix only the one the filer quoted.
- Decide and implement one refusal or one definition for an aggregate over a datetime measure. Whichever is chosen, the acceptance criterion is the same: the shape the filer measured must stop producing a plausible number. Either it errors, or it returns something a reader cannot mistake for a duration.
derived compounding is part of the scope, not a follow-up. An op: 'difference' over two such aggregates is where the nonsense becomes presentable; a fix that only refuses the bare aggregate while derived keeps accepting its output leaves the demonstrated failure intact.- Second dialect: confirm the behaviour on Postgres or MySQL before closing, and say which half (silent vs meaningless) is dialect-specific.
- Negative control: an
AVG() over a genuine numeric measure must still work, and a datetime used as a dimension (grouping, date-range filtering) must be untouched — this card is about aggregation only.
Related: #16707 (the card this was split from) · #2034 (the annotation's source) · ADR-0053 D-F1/D-F3 and #16728 (the neighbouring question of what a SQLite datetime read door hands back — same storage, different layer).
Split out of #16707 ("One more, adjacent, and load-bearing"), where the filer wrote "Say the word and I will split it out." The two lint findings that card carried are already fixed on
main(see the triage comment there); this one is not, and it is the only live item, so it gets its own card rather than keeping that one open.⛔ Filed by the triage seat, unassigned. The measurement below is the original filer's (
objectstack-ai/hotclm,@objectstack/*17.3.0,better-sqlite313.0.3, single-org dev posture); ⛔ I did not re-drive it. What I did verify is the in-repo half, marked as such.The measurement (reported, not re-driven)
Downstream,
derived: { op: 'difference', of: [avg_a, avg_b] }does not error — it parses, runs, and returns-0.849999999999909: the difference of two average years, a clean plausible number that renders happily on a dashboard tile.Why this is the dangerous shape
A wrong number that looks wrong gets caught by the first reader. This one does not:
-0.85on a tile labelled "average cycle time delta" is exactly what a correct answer looks like. The filer's own note is the point — "That is the form that would have shipped had the app not asked what the number meant."Nothing refuses it at any layer: not the schema, not
os validate/os lint, not the analytics service, not the renderer.In-repo half, verified on
origin/main5e53d73dThe annotation the filer flagged is real, and it is not a single site:
⭐ The package already contradicts itself.
analytics-service.ts:493andplugin.ts:662state the column holds BOTH storage forms depending on the write path;objectql-strategy.ts:1669states flatly that it is an INTEGER epoch. The filer's row was written through a REST/JSON path, which is precisely the case the first two describe and the third denies. So the reported TEXT reading is consistent with what the package's own newer comments say — it is the older flat claim that is stale.NativeSQLStrategy'scoerceTemporal(which the:1669docblock cites as the reason that path needs coercion) is correct for the mixed-storage reality, or is itself written against the flat claim. That is the first thing to establish, and ⛔ it must not be assumed either way from this card.What is NOT claimed
AVG()over a datetime should have a defined meaning. It may well be that the right answer is to refuse it.Acceptance
Field.datetimeon SQLite, which write paths produce INTEGER and which produce TEXT. Every remedy below depends on this, and the package currently states two incompatible answers.native-sql-strategy.ts:963andobjectql-strategy.ts:1669must agree withanalytics-service.ts:493/plugin.ts:662, whichever direction the reading settles. ⛔ Do not fix only the one the filer quoted.derivedcompounding is part of the scope, not a follow-up. Anop: 'difference'over two such aggregates is where the nonsense becomes presentable; a fix that only refuses the bare aggregate whilederivedkeeps accepting its output leaves the demonstrated failure intact.AVG()over a genuine numeric measure must still work, and adatetimeused as a dimension (grouping, date-range filtering) must be untouched — this card is about aggregation only.Related: #16707 (the card this was split from) · #2034 (the annotation's source) · ADR-0053 D-F1/D-F3 and #16728 (the neighbouring question of what a SQLite datetime read door hands back — same storage, different layer).