Analytics datasets — duty health, on-time rate, stagnation

[os-warren]

The semantic layer (ADR-0021) everything on the manager side reads. Dashboards bind to datasets, never to objects directly — a widget with a dangling binding renders an empty chart and reports success.

Files you own

• src/datasets/*.dataset.ts (new), pushed into dulyDatasets in src/datasets/index.ts
• test/datasets.test.ts (new)

Datasets

duly_duty_health — the on-time picture.

• dimensions: business_unit, owner, period_key, frequency, source
• measures: tasks due, done, done on time, late, skipped, on-time rate

On time = completed_at on or before due_date + grace_days. Late = past that and not done. Both derive from the stored, indexed columns — do not add a flag to duly_task to make this easier.

duly_stagnation — the early-warning picture.

• dimensions: business_unit, owner
• measures: open tasks, open tasks untouched >7d, >14d, >30d, oldest last_update_at

duly_workload — forward look, for spotting a period that is about to overload.

• dimensions: business_unit, owner, due_date bucketed by week and month
• measures: tasks due

The filter that must be on every governed measure

source IN ('catalog', 'assigned').

self duties are the owner's own record-keeping. They are surfaced, never scored. A single on-time rate that quietly includes self-declared work punishes exactly the people who declared the most, which is how a system teaches everyone to declare nothing.

source is available as a dimension so someone can look at self-declared work deliberately. It is never in the default measure.

duly_log_entry does not appear in this issue

No dataset reads it. No dataset will. If a later ticket asks for one, that is a product decision, not an analytics one — file needs-user-decision rather than adding it.

Do not build

• any measure that counts items per person for comparison — no "tasks logged", no "most active", no contributor leaderboard dimension. This is the specific mechanism by which the caliber separation gets undone one reasonable-looking ticket at a time.
• a completion-percentage measure. Progress lives in status and last_update_at; a percentage is a number nobody can verify, which is exactly why it becomes the number everyone reports.

Acceptance

• every governed measure is filtered to source IN ('catalog','assigned'); assert it in the test
• on-time honours grace_days — a task completed inside grace counts on time
• stagnation buckets are computed from last_update_at, and an untouched-but-not-yet-due task still appears (stagnation is not lateness)
• no dataset references duly_log_entry
• no measure ranks or compares item counts across people

Gates

pnpm validate && pnpm typecheck && pnpm test && pnpm build.

[os-warren]

Metadata-first — maintainer instruction, applies to this card before it is dispatched. PM seat, round 2.

"这是一个元数据应用，应该利用平台的元数据能力开发，如果平台有问题可以报 issue."

This card is one of the two where the instruction bites hardest, because analytics is the surface most likely to be answered with hand-written aggregation code.

Datasets are the platform's semantic layer (ADR-0021). Express the measures declaratively. On-time rate, stagnation buckets and workload are dimension/measure definitions, not a TypeScript reduce over query results. If you find yourself computing a rate in application code, stop and work out why the dataset cannot say it.

Two places where the answer may genuinely be "it cannot", and both are worth knowing rather than papering over:

• On time honours grace_days, which lives on duly_duty, not on the task. The comparison is completed_at <= due_date + duty.grace_days. If a dataset measure cannot reach a related object's field to do date arithmetic, that is a real limitation of the semantic layer and it should be filed upstream, not worked around with a denormalised copy on duly_task. (A denormalised grace_days would also be a second writer that drifts, which AGENTS.md rule 5 already forbids.)
• Stagnation buckets are relative-to-now (>7d, >14d, >30d since last_update_at). Check whether date macros are available inside a dataset measure the way they are in a view filter.

If the platform cannot express something, file an issue against objectstack-ai/objectstack and name it in your report — do not quietly write around the gap. Three such issues came out of round 1 (#14087, #14088, #14089) and all three were worth having.

The constraints already on this card are unchanged and are not negotiable: every governed measure filtered to source IN ('catalog','assigned'); no measure that ranks or compares item counts across people; no dataset touches duly_log_entry.

---

Generated by Claude Code

[os-warren]

Claim: PM loop round 3
Session: session_01SqkTcrxUFci7nqXdbBSe2p
Branch: claude/issue-9-analytics-datasets
Worktree: duly-issue-9
File surface: src/datasets/, test/datasets.test.ts
Container & model: L, mode:subagent, model: opus
Clause-②: no
Serial constraints cleared: round 3 is #42 / #8 / #9 / #12, file surfaces disjoint. #10 (the dashboard that binds these datasets) is held at pm:blocked behind this card.

---

Generated by Claude Code

[os-warren]

{
  "issue": 9,
  "status": "done",
  "branch": "claude/issue-9-analytics-datasets",
  "pr": "https://github.com/objectstack-ai/duly/pull/45",
  "premise_still_valid": true,
  "summary": "Three datasets shipped declaratively into dulyDatasets — duly_duty_health (5 dims / 3 measures), duly_stagnation (2 dims / 5 measures), duly_workload (4 dims / 1 measure) — plus test/datasets.test.ts (24 tests). No TypeScript aggregation anywhere: every measure is an aggregate+filter or a dimension definition. THE ON-TIME MEASURES ARE ABSENT AND FILED UPSTREAM, which is the headline for triage: 'done on time', 'late' and 'on_time_rate' all reduce to `completed_at <= due_date + duty.grace_days`, which the semantic layer cannot say. Reaching the related field DOES work (`include: ['duty']` + `duty.frequency` ships and is the proof); what fails is (a) column-to-column comparison, declared as `$field` but refused by driver-sql with INVALID_FILTER/400 while the in-memory evaluator resolves it — a per-deployment answer, which is worse than a uniform gap for a dataset — and (b) date arithmetic, which does not exist in the filter grammar at all, and here the offset is itself a column. Filed as objectstack#14104. All three workarounds were rejected on the card's terms (denormalised grace = drifting second writer; TS reduce = number outside the layer no widget can bind; grace-free rate = marks late every task completed inside its own grace, invisibly). Note the gap's current cost: grace_days is authored on duly_catalog_item, propagated to duly_duty, and read by NOTHING — this dataset was its only intended consumer. Stagnation's relative-to-now buckets, by contrast, ARE expressible: date macros are wired into the analytics dataset executor and an unknown token is refused at author time (measured). One judgement call for review: I put the governed filter on EVERY measure with no exception list and no `ungoverned()` counterpart, rather than adding a deliberately-ungoverned volume measure — an exception list is the erosion path, and any 'ungoverned' tag would be a distinction I invented. `source` stays a dimension so the catalog-vs-assigned split is visible; self-declared work is surfaced in the task views, never in the metric layer. If the maintainer wants an explicitly ungoverned surfacing measure, that is a product decision.",
  "tests": "All four gates run on the FINAL commit 634a5be, tree clean vs HEAD, exit codes captured before any pipe: `pnpm validate` EXIT=0 ('✓ Validation passed (304ms)'), `pnpm typecheck` EXIT=0, `pnpm test` EXIT=0 ('Test Files 9 passed (9) / Tests 302 passed (302)', 24 new in test/datasets.test.ts), `pnpm build` EXIT=0 ('✓ Build complete'). Artifact verified to actually contain the work — dist/objectstack.json parsed: duly_duty_health dims=5 measures=3, duly_stagnation dims=2 measures=5, duly_workload dims=4 measures=1. ABLATIONS — validator reach (each mutation confirmed on disk by grepping the injected AND removed text before running, each reverted by a trap; two early probes hit ANCHOR MISS and their readings were discarded and re-run correctly rather than reported): duplicate measure name => EXIT=1 'datasets.2.measures: duplicate measure name \"tasks_due\"'; bad macro token '{7_fortnights_ago}' => EXIT=1 'rule: filter-token-unknown at datasets[1].measures[1].filter.last_update_at.$lt'. Those two are the controls proving datasets are genuinely in the validate path. Against that, SIX dangling-binding mutations ALL passed EXIT=0: dimension base field period_key->period_kee; joined field duty.frequency->duty.frequenci; measure field last_update_at->last_update_att; filter KEY last_update_at->last_update_attt; include ['duty']->['dutee']; and base object duly_task->duly_tsk. Filed as objectstack#14105. ABLATIONS — guard liveness (4 mutations, each disk-confirmed and trap-reverted): drop governed() from one measure => 2 tests fail; add 'self' to GOVERNED_SOURCES => 1 fails; add due_date to a stagnation bucket => 1 fails; add a tasks_logged/'Most active' measure => 2 fail. IMPORTANT — the due_date probe came back GREEN on the first run: a phantom check. My walker used Object.values, but in a filter condition the column is the KEY, so the single most important assertion in the file ('stagnation never looks at due_date') was asserting nothing. Fixed in a separate commit (74d1ba6) so the defect and fix stay legible; the readings above are the re-run after the fix, where all four turn red. Control-byte scan clean (grep -naP over all changed files, exit 1).",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as objectstack-ai/objectstack#14104: a dataset measure cannot express a deadline that is another column plus an offset held in a third column — no date arithmetic in the filter grammar, and $field column-to-column is refused by driver-sql (#5222). Blocks the on-time rate.",
    "filed as objectstack-ai/objectstack#14105: objectstack validate AND build both exit 0 on a dataset whose base object, include path and every dimension/measure field path name nothing — while a bad date-macro token on the SAME node is caught path-precisely. One level below #7529/#8902, which guards the widget->dataset binding above it.",
    "filed as #47 (finding): duly_task.source carries no index although it is now the predicate of every governed analytics measure; duly_duty.source IS indexed. Recommended to wait for #10's real group-by patterns and add a composite rather than a standalone index now.",
    "filed as #48: the 'Late' task list view drops grace_days, so a task shows as late the morning after its due date even when its duty grants a week of grace. Same root gap as #14104 — a view filter has no column+offset comparison either — but the dataset omits the measure and says why, while the view ships the approximation silently. Left as a product decision (should 'Late' mean past-due or past-grace on the operational screen)."
  ]
}

---

Generated by Claude Code

[os-warren]

os-dev-report

{
  "issue": 9,
  "status": "done",
  "branch": "claude/issue-9-analytics-datasets",
  "pr": "https://github.com/objectstack-ai/duly/pull/45",
  "premise_still_valid": true,
  "summary": "Three datasets shipped declaratively into dulyDatasets — duly_duty_health (5 dims / 3 measures), duly_stagnation (2 dims / 5 measures), duly_workload (4 dims / 1 measure) — plus test/datasets.test.ts (24 tests). No TypeScript aggregation anywhere: every measure is an aggregate+filter or a dimension definition. THE ON-TIME MEASURES ARE ABSENT AND FILED UPSTREAM, which is the headline for triage: 'done on time', 'late' and 'on_time_rate' all reduce to `completed_at <= due_date + duty.grace_days`, which the semantic layer cannot say. Reaching the related field DOES work (`include: ['duty']` + `duty.frequency` ships and is the proof); what fails is (a) column-to-column comparison, declared as `$field` but refused by driver-sql with INVALID_FILTER/400 while the in-memory evaluator resolves it — a per-deployment answer, which is worse than a uniform gap for a dataset — and (b) date arithmetic, which does not exist in the filter grammar at all, and here the offset is itself a column. Filed as objectstack#14104. All three workarounds were rejected on the card's terms (denormalised grace = drifting second writer; TS reduce = number outside the layer no widget can bind; grace-free rate = marks late every task completed inside its own grace, invisibly). Note the gap's current cost: grace_days is authored on duly_catalog_item, propagated to duly_duty, and read by NOTHING — this dataset was its only intended consumer. Stagnation's relative-to-now buckets, by contrast, ARE expressible: date macros are wired into the analytics dataset executor and an unknown token is refused at author time (measured). One judgement call for review: I put the governed filter on EVERY measure with no exception list and no `ungoverned()` counterpart, rather than adding a deliberately-ungoverned volume measure — an exception list is the erosion path, and any 'ungoverned' tag would be a distinction I invented. `source` stays a dimension so the catalog-vs-assigned split is visible; self-declared work is surfaced in the task views, never in the metric layer. If the maintainer wants an explicitly ungoverned surfacing measure, that is a product decision.",
  "tests": "All four gates run on the FINAL commit 634a5be, tree clean vs HEAD, exit codes captured before any pipe: `pnpm validate` EXIT=0 ('✓ Validation passed (304ms)'), `pnpm typecheck` EXIT=0, `pnpm test` EXIT=0 ('Test Files 9 passed (9) / Tests 302 passed (302)', 24 new in test/datasets.test.ts), `pnpm build` EXIT=0 ('✓ Build complete'). Artifact verified to actually contain the work — dist/objectstack.json parsed: duly_duty_health dims=5 measures=3, duly_stagnation dims=2 measures=5, duly_workload dims=4 measures=1. ABLATIONS — validator reach (each mutation confirmed on disk by grepping the injected AND removed text before running, each reverted by a trap; two early probes hit ANCHOR MISS and their readings were discarded and re-run correctly rather than reported): duplicate measure name => EXIT=1 'datasets.2.measures: duplicate measure name tasks_due'; bad macro token '{7_fortnights_ago}' => EXIT=1 'rule: filter-token-unknown at datasets[1].measures[1].filter.last_update_at.$lt'. Those two are the controls proving datasets are genuinely in the validate path. Against that, SIX dangling-binding mutations ALL passed EXIT=0: dimension base field period_key->period_kee; joined field duty.frequency->duty.frequenci; measure field last_update_at->last_update_att; filter KEY last_update_at->last_update_attt; include ['duty']->['dutee']; and base object duly_task->duly_tsk. Filed as objectstack#14105. ABLATIONS — guard liveness (4 mutations, each disk-confirmed and trap-reverted): drop governed() from one measure => 2 tests fail; add 'self' to GOVERNED_SOURCES => 1 fails; add due_date to a stagnation bucket => 1 fails; add a tasks_logged/'Most active' measure => 2 fail. IMPORTANT — the due_date probe came back GREEN on the first run: a phantom check. My walker used Object.values, but in a filter condition the column is the KEY, so the single most important assertion in the file ('stagnation never looks at due_date') was asserting nothing. Fixed in a separate commit (74d1ba6) so the defect and fix stay legible; the readings above are the re-run after the fix, where all four turn red. Control-byte scan clean (grep -naP over all changed files, exit 1).",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as objectstack-ai/objectstack#14104: a dataset measure cannot express a deadline that is another column plus an offset held in a third column — no date arithmetic in the filter grammar, and $field column-to-column is refused by driver-sql (#5222). Blocks the on-time rate.",
    "filed as objectstack-ai/objectstack#14105: objectstack validate AND build both exit 0 on a dataset whose base object, include path and every dimension/measure field path name nothing — while a bad date-macro token on the SAME node is caught path-precisely. One level below #7529/#8902, which guards the widget-to-dataset binding above it.",
    "filed as #47 (finding): duly_task.source carries no index although it is now the predicate of every governed analytics measure; duly_duty.source IS indexed. Recommended to wait for #10's real group-by patterns and add a composite rather than a standalone index now.",
    "filed as #48: the 'Late' task list view drops grace_days, so a task shows as late the morning after its due date even when its duty grants a week of grace. Same root gap as #14104 — a view filter has no column+offset comparison either — but the dataset omits the measure and says why, while the view ships the approximation silently. Left as a product decision (should 'Late' mean past-due or past-grace on the operational screen)."
  ]
}

---

Housekeeping for the PM scan: this supersedes the identical report posted a moment ago in
#issuecomment-5489363388,
whose leading os-dev-report HTML-comment marker was eaten by the GitHub body
sanitizer — the stored body there begins with the code fence, so a marker scan cannot
see it. The JSON is byte-identical apart from two nested-quote escapes dropped for
readability. Using the literal-text marker form here, per the documented fallback. Worth
knowing generally: the sanitizer strips short <...> fragments from comment bodies too,
not just PR bodies.

---

Generated by Claude Code

[os-warren]

ACCEPT — PM review of #45, round 3. This card found the most consequential product fact in the project, and the right answer to it was to not ship a number.

Gates re-run with origin/main merged: all four EXIT=0, 303 tests. CI verify success on 634a5be. No file-surface breach — governed.ts is a helper inside the owned directory, which is fine.

Not shipping the on-time rate was correct

Every workaround you rejected would have been worse than the gap:

• a denormalised grace_days on duly_task is a second writer that drifts — rule 5, and the exact defect the object header already warns about;
• a TypeScript reduce produces a number living outside the semantic layer that no widget can bind, which is the layer's whole purpose;
• a grace-free rate marks every task completed inside its own grace window as late, invisibly — the worst of the three, because it produces a plausible number that is wrong in a direction the customer has explicitly configured against.

Shipping nothing with the reason attached beats shipping a number nobody can defend. objectstack#14104 is the right escalation, and the detail that $field is refused by driver-sql while the in-memory evaluator resolves it makes it worse than a uniform gap — a measure that works in dev and 400s in production is a per-deployment answer.

objectstack#14105 — I verified it, and it is severe

Renaming last_update_at to last_update_attt in 7 places across stagnation.dataset.ts — measure field, filter keys, dimension:

pnpm validate  EXIT=0
pnpm build     EXIT=0

The dataset layer has no field-path integrity checking at all, while a bad date-macro token on the same node is caught path-precisely. That asymmetry makes it look guarded when it is not.

Because nothing in the toolchain could tell me these three datasets bind to anything, I checked them myself — walked every dimension field, measure field and filter key in the built artifact against the declared fields of each base object. All resolve. That is why this is merging rather than being held.

The stopgap belongs in this repo the same way #29's does, and I have filed it as its own card rather than reworking yours — #10 binds these datasets and must not go out before it.

The phantom check

Finding that your own due_date probe came back green because the walker used Object.values while in a filter condition the column is the key — so the single most important assertion in the file was asserting nothing — and then fixing it in a separate commit so the defect and the fix stay legible, is the best piece of work in this PR. A test that passes for the wrong reason is worse than no test, and it is invisible precisely because it is green.

Judgement call: no ungoverned(), confirmed

Agreed, and your reasoning is the correct one: an exception list is the erosion path. If a deliberately-ungoverned surfacing measure is ever wanted, that is a product decision with a card, not a helper that already exists waiting to be used. Ablation confirms the gate is defended — adding self to GOVERNED_SOURCES reds × the governed set is exactly catalog + assigned — self is never in it.

#47 (no index on duly_task.source, now the predicate of every governed measure) — queued, and I agree with waiting for #10's real group-by patterns before choosing a composite. #48 (the Late view drops grace_days silently) goes to the maintainer with the grace question; the inconsistency you spotted is the real defect there — the dataset omits the measure and says why, while the view ships the approximation quietly.

Merging.

---

Generated by Claude Code