Analytics datasets: no measure for the difference between two datetime columns (cycle time, per-stage duration, SLA overrun) — #16737 refused the wrong path, nothing provides the right one

Filing class: ③ platform gap, filed at destination from an app ruling. Reader: domain:server / area:records analytics triage. Unlabelled, no assignee. Origin: objectstack-ai/hotclm#31, ruled C by the maintainer on 2026-10-25 (ruling comment objectstack-ai/hotclm#31 (comment)): keep the goal, file the capability, do not stamp durations app-side.

Context

hotclm's DESIGN.md §09 wants two numbers every CLM buyer asks for: average cycle / per-stage duration (submitted_at → review_started_at → approved_at → activated_at) and review SLA overrun (review_started_at compared against clm_contract_type.review_sla_days, 2/3/5/10 days by type). The semantic layer takes no SQL and no expressions (ADR-0021), so there is nowhere to subtract two timestamps, and a dataset filter cannot compare a column against another object's column.

Measured

17.3.0/17.4.0: derived: { op: 'difference', of: [avg_activated, avg_submitted] } validated, ran and rendered -0.85 — the difference of two average years, because AVG() over an ISO-text datetime coerces to the leading number on SQLite (service-analytics: AVG() over a Field.datetime measure returns SQLite's text→numeric coercion (an average YEAR) with no error, and derived: { op: 'difference' } renders the difference of two of them as a clean plausible number #16737).
17.7.0 (service-analytics: AVG() over a Field.datetime measure returns SQLite's text→numeric coercion (an average YEAR) with no error, and derived: { op: 'difference' } renders the difference of two of them as a clean plausible number #16737 fixed by refusing): os validate exits 1 (measure-aggregate-field-type-refused), PUT /api/v1/meta/dataset/… → 422, POST /api/v1/analytics/query avg(submitted_at) → 400. The trap is closed. The capability is still absent: DerivedMeasureOp is ratio | sum | difference | product over other measures (packages/spec/src/ui/dataset.zod.ts:208), and no measure function takes two datetime columns.

hotclm therefore ships stage-arrival counts, approval throughput (compareTo: previousPeriod) and a fixed 30-day review-age tile whose title states the threshold, and its §09 records both metrics as "waiting on the platform's date capability". The maintainer ruled in September that the app must not stamp durations with a daily job (that would re-implement a platform rule in the app), and reaffirmed it today.

What is asked

A dataset measure that yields the duration between two datetime columns of the same row, aggregated — shape the platform's to choose, e.g. measure: { fn: 'avgDuration', from: 'submitted_at', to: 'activated_at', unit: 'days' } (with min/max/sum variants), with null rows excluded and the result typed as a number of the declared unit so the tile cannot be misread. Must work on SQLite and Postgres alike (the two backends diverge today: epoch mean vs error).
A per-row comparison of a datetime column against "now minus N days" where N comes from a related object's column (SLA by type) — or, failing that, a documented statement that this needs a stamped column, so apps know which half is theirs.

Cross-app pull: CRM (lead-to-close days, stage ageing), SRM (PO cycle, supplier onboarding time) need the same measure.

Dedupe

search_issues on this repo for datetime difference / duration / cycle-time measure: 0 results. Related, not the same: #16737 (closed; refuse avg over datetime), #16099 (no layer refuses inconsistent aggregate/field pairs), #15768 (datetime measure typed as number).

Not asked

No expression language or raw SQL in datasets (ADR-0021 stands).
No app-side stamping in hotclm; hotclm#31's Blocked-by moves from service-analytics: AVG() over a Field.datetime measure returns SQLite's text→numeric coercion (an average YEAR) with no error, and derived: { op: 'difference' } renders the difference of two of them as a clean plausible number #16737 to this card.

Triage: first grade — `enhancement` · `priority:p1` · `domain:spec` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-25T11:55Z

⛔ Not a claim, ⛔ not a dispatch.

Ruled on [Decision] §09 要「各段时长」和「超 SLA」，但语义层算不出时间差——两问共用一个补法 hotclm#31 (C): keep the goal, file the capability, and stamp no durations on the app side.
p1 by 「协议新增 p1」: the platform answer adds an authorable key (a protocol addition). Spec first, then the runtime. A datetime-difference measure in the semantic layer, with no SQL or expressions (ADR-0021). Comparing against another object's column is a second, separate ask; split it if the dev finds it needs its own key.

Generated by Claude Code

Claim: PM loop round 2
Session: session_01AKPixnkg91qCkQCVFDJuqW (the card thread; seat session session_01K2Tat8JEt4AhLASV4V4QWE posts this claim)
Account: marchtian
Branch: claude/project-thread-4m2bvo
Worktree: objectstack-issue-25778
Domain: domain:spec
Seat: domain:spec#4 (seat post #18917)
File surface: packages/spec/src/ui/dataset.zod.ts + the analytics query compiler + the lint/validate measure rule + tests + changeset
Container & model: M, mode:thread, model: default tier (dispatch-gates --tier: "no path-derived mandate", floor sonnet · default opus · ceiling fable), effort: xhigh (p1)
Clause-②: yes
Metadata-protocol: added
Responsibility: n/a — not a defect card
Thread-read: 6397322215
Premise: holds (at main 5f5f0b42: DerivedMeasureOp is ratio | sum | difference | product over measures; no column-pair duration measure; no open PR names #25778)
Ruling-ref: hotclm#31 comment 6396322580 (letter C)
Serial constraints cleared: none in the lane touches dataset.zod.ts.

Order: /mnt/project-files/spec-seat/orders/25778.md with _common.md (project files). Batch of six on the project owner's order 「你应该并发6」 (2026-10-25). Independent full Fable review of the diff before the seat's contract review and ACCEPT.

os-dev-report

{
  "issue": 25778,
  "status": "done",
  "branch": "claude/project-thread-4m2bvo",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/25903",
  "session": "session_01AKPixnkg91qCkQCVFDJuqW",
  "premise_still_valid": true,
  "summary": "Item 1 only, as ordered. DatasetMeasureSchema gains duration {from, to, unit} (seconds|minutes|hours|days, aggregate avg|min|max|sum); the analytics compiler refuses a non-datetime column (400 DATASET_INVALID); NativeSQLStrategy computes to - from per row in double precision per dialect (SQLite julianday, PostgreSQL EXTRACT(EPOCH), MySQL TIMESTAMPDIFF MICROSECOND / 1.0E6), nulls excluded, and the preview evaluator matches it. The measure is absent from cube.measures, so every other path refuses it 400 INVALID_FIELD naming it; os validate gains the gating rule measure-duration-field-type-refused. Item 2 is split to #25808 as the order directed. After the ui/ merge (#25825) the one ui/ mirror, DatasetDefaultInspector.measureNamesNoField in ui/packages/app-shell, is fixed in the same PR with a test.",
  "tests": "Live on SQLite and PostgreSQL: service-analytics dataset-duration-measure.test.ts 14/14 per driver (OS_TEST_POSTGRES_URL set); native-sql-duration-measure.test.ts 23/23; spec dataset-measure-duration.test.ts 18/18 + type-alias-convention.pin.test.ts 3/3; lint validate-dataset-measure-duration.test.ts 12/12; ui app-shell DatasetDefaultInspector.firstPickKeepsCount-12848.test.tsx 6/6, and it fails with the predicate reverted. Full suites before the ui/ merge: service-analytics 4813 pass + 1 pre-existing failure (server-not-on-UTC, the local PG runs on UTC); lint 7310 pass. Typecheck exit 0: service-analytics, lint, platform-objects, spec (full script). Rejection tests assert code + status.",
  "gates": "dispatch-gates --commands: 121 derived; all 121 run under the verify lock, each exit recorded before any pipe, all exit 0 (check:dts-closure exit 0 after deleting a half-built gitignored ui/packages/components/dist). --ran: 121 run, 0 NOT-MEASURED, a DERIVED zero. Measured on the tree of 24e5933a2; the final merge of main 6e0597f2e touched only ui/scripts/__tests__ and the derived list re-read at head 3f3f0959d is identical (121). NOT MEASURED locally: pnpm --filter @object-ui/app-shell type-check, because @object-ui/components fails its own declaration build in this container at the main tip, in files this diff does not touch; CI Type Check UI judges it. PR check-runs at report time (head 3f3f0959d): success = Check Changeset, Check PR Size, Spec property liveness, Governed Surface Queue Guard, The card this PR closes must claim this branch, No other open PR may claim the same issue, No other open PR may claim the same single-writer path, Part-of PR must not also close its card, Flag docs affected by code changes, Check Documentation Links, Dogfood Regression Gate, Auto Label, filter; skipped = Temporal Conformance, Dogfood Regression Gate shards, Dogfood Verify CLI, Packed-tarball smoke, 18.x upgrade harness; in_progress = Lint (all lanes), Type Check (all lanes incl. Type Check UI), Build Core, Build Docs, Test Core 1-8/8, Test UI 1-16/16, Build and E2E UI, Lint UI. No failure at report time.",
  "dogfood_battery": "n/a: no ! in the PR title and Clause-② is yes (widening), no (narrowing) arm",
  "line_budget": "2126 changed lines (2055 added, 71 removed) across 38 files: 1029 test lines, 97 generated-artifact lines, the rest source, docs and changeset. Under the 3000-line Check PR Size bar (green).",
  "files_changed": [
    ".changeset/25778-dataset-duration-measure.md",
    "content/docs/data-modeling/analytics.mdx",
    "content/docs/deployment/validating-metadata.mdx",
    "content/docs/references/index.mdx",
    "content/docs/references/ui/dataset.mdx",
    "docs/audits/2026-07-unknown-key-strictness-ledger.counts/ui.md",
    "packages/lint/src/index.ts",
    "packages/lint/src/rule-explanations.ts",
    "packages/lint/src/validate-dataset-measure-aggregates.ts",
    "packages/lint/src/validate-dataset-measure-duration.test.ts",
    "packages/lint/src/validate-dataset-references.ts",
    "packages/platform-objects/src/apps/translations/en.metadata-forms.generated.ts",
    "packages/platform-objects/src/apps/translations/es-ES.metadata-forms.generated.ts",
    "packages/platform-objects/src/apps/translations/ja-JP.metadata-forms.generated.ts",
    "packages/platform-objects/src/apps/translations/zh-CN.metadata-forms.generated.ts",
    "packages/services/service-analytics/src/__tests__/dataset-duration-measure.test.ts",
    "packages/services/service-analytics/src/__tests__/native-sql-duration-measure.test.ts",
    "packages/services/service-analytics/src/analytics-service.ts",
    "packages/services/service-analytics/src/dataset-compiler.ts",
    "packages/services/service-analytics/src/dataset-executor.ts",
    "packages/services/service-analytics/src/duration-measure-sql.ts",
    "packages/services/service-analytics/src/preview-evaluator.ts",
    "packages/services/service-analytics/src/strategies/native-sql-strategy.ts",
    "packages/services/service-analytics/src/strategies/types.ts",
    "packages/spec/api-surface/ui.json",
    "packages/spec/authorable-surface/ui.json",
    "packages/spec/declaration-map/ui.json",
    "packages/spec/export-origins/ui.json",
    "packages/spec/json-schema.manifest/ui.json",
    "packages/spec/liveness/dataset.json",
    "packages/spec/liveness/state-counts/dataset.md",
    "packages/spec/src/data/analytics-column-reference.ts",
    "packages/spec/src/type-alias-convention.pin.test.ts",
    "packages/spec/src/ui/dataset-measure-duration.test.ts",
    "packages/spec/src/ui/dataset.form.ts",
    "packages/spec/src/ui/dataset.zod.ts",
    "ui/packages/app-shell/src/views/metadata-admin/inspectors/DatasetDefaultInspector.firstPickKeepsCount-12848.test.tsx",
    "ui/packages/app-shell/src/views/metadata-admin/inspectors/DatasetDefaultInspector.tsx"
  ],
  "deviations": [
    "Commit trailers: 4 local commits (now f18374527, c06b33cdb, 4edb2dea5, eda01f1f9) first carried the model-named Co-Authored-By and 2 carried Refs #25778; pre-push check:commit-card-trailers refused. With the user's explicit OK on a decision card in the project thread (2026-10-26), they were reworded before the first push of them with git filter-branch --msg-filter (trees identical, verified per commit; fast-forward from the pushed 0dec20d2, no force-push). OS_ALLOW_CARD_TRAILER_PUSH not used.",
    "Writes beyond the budget of three: the issue_create of #25808 (item 2, filed as the order directed) and one PR comment 6407520493 carrying the verbatim Fable review record (the order requires the verdict comment id).",
    "Merged main twice after the first PR-ready state (the ui/ merge c475b45b5, then 6e0597f2e) as the seat asked; merges, not rebases, because the branch is shared.",
    "Fable review F6 (refuse a dimension and a measure sharing a name) not applied: it is Metadata-protocol narrowed, outside a widening card."
  ],
  "review": "Independent Fable review, 4 rounds: round 1 CHANGES REQUESTED (F1-F7), F1-F5 and F7 applied; rounds 2, 3 and 4 (the last after the ui/ merge) ACCEPT. Verdict record: PR comment 6407520493.",
  "mcp_calls": "8 — all reads: issue_read x2 (card body, labels), search_issues x4 (dedupe for #25808 and for the prefixed-filter finding), actions_list x1, pull_request_read get_check_runs x1. No MCP write tool.",
  "api_writes": "4 relay writes as objectstack-fleet[bot] plus git push: issue_create #25808 (run 40202207382); pr_create #25903 with assignee marchtian (run 40263744478); issue comment 6407520493 on #25903 (run 40263903533); this os-dev-report comment on #25778. No card claim, assignee or label written.",
  "open_questions": [],
  "out_of_scope_findings": [
    "class: a · reach: measured in-process at AnalyticsService.query, the service the POST /analytics/query handler calls (packages/runtime/src/domains/analytics.ts), native-SQL strategy, SQLite dialect; not measured over HTTP · evidence: a registered dataset measure won_amount (sum amount, filter stage=won) asked as measures [sales.won_amount], the spelling getMeta lists, runs SUM(amount); asked as [won_amount] it runs SUM(CASE WHEN stage = $1 THEN amount END). measureFilters is read by the spelling as asked while lookupMember strips the cube prefix · dedupe words: cube prefix measure filter dropped, measureFilters qualified member (0 hits on 2 searches) · ready-to-file draft: /mnt/project-files/spec-seat/findings/25778-prefixed-measure-filter.md",
    "class: a · reach: observed live in dataset-duration-measure.test.ts through AnalyticsService.query on SQLite and PostgreSQL · evidence: a pushed-down sum over an all-NULL group folds to 0 after the statement ordered it (NULLS LAST on SQLite vs NULLS FIRST on PostgreSQL), so ORDER BY + LIMIT returns different pages per driver; the duration test excludes that group · dedupe words: analytics sum null group order by limit sqlite postgres nulls first",
    "carrier: PR #25903 Acceptance notes · noted, not filed: a dimension and a measure may share a name (unmeasured, review F6, a narrowing); duration with a granularity bucket is refused because the engine aggregate has no two-column operand; the console inspector has no from/to/unit editor (the spec refuses field/derived/currency beside duration at save); no wire unit on result fields"
  ]
}

Generated by Claude Code

Landed · seat domain:spec#4 · 2026-10-26T08:01Z. PR #25903 merged through the queue as merge commit d1663ba1826c. Before the merge it had seat ACCEPT 6410241981 and delta contract review 6410232496 on head 8821a8d40e7c, the head that carries #25953's empty-group rule (a duration sum answers 0 in the statement; avg / min / max stay NULL). This card closed completed by Fixes; pm:dispatched is removed. The out-of-scope finding from this card's work is #25906 (a filtered dataset measure asked on the cube door as CUBE.MEASURE answers the unfiltered aggregate), in the queue; #25907 was fixed on main by #25953.

Navigation Menu

Repository navigation

Analytics datasets: no measure for the difference between two datetime columns (cycle time, per-stage duration, SLA overrun) — #16737 refused the wrong path, nothing provides the right one #25778

Context

Measured

What is asked

Dedupe

Not asked

Triage: first grade — `enhancement` · `priority:p1` · `domain:spec` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-25T11:55Z

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Analytics datasets: no measure for the difference between two datetime columns (cycle time, per-stage duration, SLA overrun) — #16737 refused the wrong path, nothing provides the right one #25778

Description

Context

Measured

What is asked

Dedupe

Not asked

Activity

objectstack-fleet commented on Oct 25, 2026

Triage: first grade — enhancement · priority:p1 · domain:spec · area:reports · pm:queue · triage seat · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-25T11:55Z

objectstack-fleet commented on Oct 25, 2026

objectstack-fleet commented on Oct 26, 2026

objectstack-fleet commented on Oct 26, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Triage: first grade — `enhancement` · `priority:p1` · `domain:spec` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-25T11:55Z