Time-to-offer on the employer dashboard: stamp the duration on `ats_application`, seed offers for hired applications, and correct §04's wording

[os-sam]

The fourth tile of ats_employer_hiring that #8 could not build. Deliberately left out rather than approximated (PR #54); this card is what would make it real. Blocked-by: #53. Unblocked — #53 merged as PR #64 (62e496d).

DESIGN.md §04 asks for median days from applied_at to the offer's created_at for hired applications. #8 established, by measurement, four independent reasons it could not be expressed. Three still hold; the fourth is gone:

1. No median aggregate. The available set is count / sum / avg / min / max / count_distinct.
2. A dataset cannot compute a duration across two objects. The only derived form combines other measures of the same dataset by name.
3. A measure-scoped filter answers 501 NOT_IMPLEMENTED on the memory driver (objectstack#16642) — so even a single-object formulation would error on the boot AGENTS.md documents.
4. ⭐ The demo seed has no offer on any hired application — all 14 ats_offer rows sit on offer-stage applications (Demo seed: no ats_offer row belongs to a hired application — all 14 offers sit on offer-stage applications, so "days to offer for hired applications" is unmeasurable on the seed #53). DONE in feat(data): an accepted offer on every hired application; seed-count docs read 818 #64. The seed now carries one accepted ats_offer per hired application in both locale packs (23 offers: 14 on offer-stage applications + 9 accepted, one per hired). Verified in the artifact and through RLS at runtime: 9 of 9 hired applications have exactly one accepted offer, and no accepted offer sits anywhere else. The tile will have data; do not re-seed it.

Scope

• A stored scalar on ats_application — offer_created_at (or days_to_offer), written by an afterInsert hook on ats_offer. A duration the analytics layer can average has to be a column; there is no cross-object arithmetic in a dataset.
• ⛔ The hook must follow the Stamp hooks write one arbitrary employer's org onto every row: ctx.api.object(...).findOne({ where: { id } }) inside the hook sandbox returns an unrelated row (every seeded ats_job reads employer_org = org_ats_orbit) #43 contract: re-derive on update only when the payload names a source field or the derived field itself. A hook that recomputes unconditionally is corrupted by claimSeedOwnership's predicate update on the next boot and looks correct until someone counts. That defect cost this project a full card; do not re-introduce it on a new column.
• Demo seed: no ats_offer row belongs to a hired application — all 14 offers sit on offer-stage applications, so "days to offer for hired applications" is unmeasurable on the seed #53's seed fix — offers on the 9 hired applications, so the tile has data. Both locale packs, through scopeToDemo(). Already done in feat(data): an accepted offer on every hired application; seed-count docs read 818 #64 — out of scope now. If you find yourself editing src/data/, stop: the seed is correct, and moving applications between stages would move the 88 / 46 / 28 / 14 / 9 funnel the release screenshots show.
• The tile — avg, not median, on ats_employer_hiring.
• The comment block in src/dashboards/employer-hiring.dashboard.ts — it currently explains why the tile is absent (as of feat(data): an accepted offer on every hired application; seed-count docs read 818 #64, on reasons 1 and 2 only). Replace it with what the tile now is.
• DESIGN.md §04 — the wording moves from median to average, because median is not available and writing a spec the platform cannot express is how this tile came to be missing in the first place.

⛔ Do not touch src/security/, src/data/, the other two dashboards, or anything #39 / #45 covers.

What the seed gives you to check against

After a boot on the memory driver, the nine hired applications and their accepted offers are readable per employer. As a starting cross-check: admin@quillstone.example reads 3 offers (1 accepted), admin@harborline.example reads 4 (2 accepted). So the tile's denominator per employer is small and hand-checkable — which is the point: the acceptance below asks for the number, not the render.

Acceptance

• pnpm validate && pnpm lint && pnpm typecheck exit 0. CI now gates this repo (.github/workflows/ci.yml) and pnpm lint runs --i18n-strict, so a new field or label needs both locales.
• Boot, wait for [Seeder] Seed loading complete, and show the tile reading a plausible non-zero number for both Quillstone and Harborline, cross-checked against an independent computation over the same rows. ⛔ A tile that renders is not the acceptance; the number matching an independently-derived one is.
• ⚠️ Measurement trap, found while verifying feat(data): an accepted offer on every hired application; seed-count docs read 818 #64: POST /api/v1/analytics/dataset/query accepts a filter at body.filter, selection.filter, filters or where with HTTP 200 and silently ignores it. The key the analytics service actually reads is selection.runtimeFilter (which is what the Console sends). A probe using any other key reports unfiltered tenant-wide numbers as if they were the widget's.
• The stamped column is correct on every hired application after a boot — not just the first — and a no-op PATCH on an application leaves it unchanged (the Stamp hooks write one arbitrary employer's org onto every row: ctx.api.object(...).findOne({ where: { id } }) inside the hook sandbox returns an unrelated row (every seeded ats_job reads employer_org = org_ats_orbit) #43 regression test).
• Both drivers.

Out of scope

The conversion-% tiles: those need a ratio over two differently-filtered counts and are blocked on objectstack#16642. Separate card when that lands.

[claude]

os-dev-report

{
  "issue": 55,
  "status": "done",
  "branch": "claude/issue-55-time-to-offer",
  "pr": "https://github.com/objectstack-ai/ats/pull/68",
  "premise_still_valid": false,
  "summary": "Four of the card's five scope bullets stood and are delivered; the fifth's premise is falsified. DELIVERED: ats_application.days_to_offer (Field.number, min 0) written once by OfferTimeToOfferHook, an afterInsert hook on ats_offer (runAs system, by-id update, lowered to a metadata-only body with inferred capabilities api.read + api.write); the avg_days_to_offer measure (aggregate avg, no measure-scoped filter) on ats_application_metrics; the Average Days to Offer KPI tile on ats_employer_hiring filtered at the widget to stage hired, with the old comment block explaining the tile's ABSENCE replaced by what it now is; en and zh-CN labels for the field, measure and widget. FALSIFIED PREMISE (scope bullet 4): DESIGN.md section 04 does not say median and never has. Line 290 has read the Chinese for 'average days to offer' since the bootstrap commit. Evidence over the full 29-commit history, unshallowed first: `git log -S` for the Chinese word for median against DESIGN.md returns nothing, while the same search for the average phrase returns only 03fa775 (root). DESIGN.md is therefore NOT modified by this PR. The text that actually said 'median' was docs/backlog/13-dashboards.md, under a heading reading 'Spec - DESIGN.md section 04' - a restatement that had diverged from the authority it cites. That one word is the whole change to that file, and is what I moved to 'average'. SEED-HOOK QUESTION: measured, not assumed - the seed loader DOES fire afterInsert. All 23 offer-bearing applications (9 hired among them) are stamped at boot. The pre-authorised src/data/ fallback was NOT taken: src/data/ is untouched, funnel still 88/46/28/14/9, seeder still inserts 818 with errored 0. ASSIGNEE: the card arrived with assignee empty and no Claim comment on it (0 comments at pickup); per the dispatch contract I never write that field, so it is still empty - the branch named here is the identity bit. NOTE FOR REVIEW: the tile's denominator is genuinely tiny by design (Quillstone 1 hired application, Harborline 2), which is what makes it hand-checkable.",
  "tests": "GATES (exit codes captured before any pipe, on the committed tree b130cc7): `pnpm validate` exit=0 ('Validation passed (861ms)', 12 Objects 147 Fields, 3 Dashboards); `pnpm lint` (--i18n-strict) exit=0 ('3 suggestion(s)'); `pnpm typecheck` exit=0 (no output). The 3 lint suggestions are pre-existing approval-approvers-may-resolve-empty on employer_verification and job_publish_review; this diff touches no flow. `npx objectstack build` exit=0, 'all 9 callables are body-only'; the lowered body of ats_offer_time_to_offer carries capabilities ['api.read','api.write']. Control-character self-scan over the 8 changed files: no matches. BOOTS (port 4531, each counted only after '[Seeder] Seed loading complete', each shut down by pgid with a /dev/tcp probe confirming the port free before and after): memory --fresh; sqlite --fresh; sqlite on a persistent db file; second boot on that SAME file. THE NUMBER (not the render), both drivers: Quillstone tile avg_days_to_offer=30 over application_count=1; Harborline 58 over 2. Cross-checked against an independent recomputation that never reads the new column - it re-derives max(0, floor((first offer's created_at - applied_at)/86400000)) from the rows each persona can read: Quillstone [30] -> 30.0 (27 applications, 3 offers, 1 hired readable), Harborline [58,58] -> 58.0 (31, 4, 2). Match on both drivers, against both the stored column and the recomputation. Compiled SQL printed by the probe: SELECT AVG(days_to_offer) AS \"avg_days_to_offer\", COUNT(*) AS \"application_count\" FROM \"ats_application\" WHERE stage = $1 AND (\"ats_application\".\"employer_org\" IN ($2)) - the widget filter and RLS both visible. FILTER-KEY TRAP reproduced: body.filter, selection.filter and body.where each return HTTP 200 with NO stage clause in the compiled SQL and report 30.333... over 27 rows instead of 30 over 1. Only selection.runtimeFilter compiles the predicate. #43 REGRESSION, all nine hired applications with five distinct values [30,36,49,58,66]: three no-op PATCHes ({}, {\"rating\":4} same value, {\"stage\":\"hired\"} same value, all HTTP 200) -> 'CHANGED: none - all 9 unchanged'. Second boot on the same sqlite database ('[Seeder] Seed loading complete {\"inserted\":0,\"updated\":207,\"skipped\":611,\"errored\":0}', the claimSeedOwnership path) -> all nine values and both tiles reproduce exactly. Structurally the hook subscribes to afterInsert only, so the predicate update dispatches nothing into it at all. LIVE INSERTS (proving the seeded value is what the hook computes for a fresh offer, same code path): an offer inserted on an unstamped application wrote 21, independently computed 21 from that offer's real created_at; a SECOND offer on an already-stamped application left it at 23 (first offer wins). BOTH DRIVERS / #39: on sqlite an employer persona sees everything needed (their applications, their offers, the tile, the recomputation). A platform persona reads all 200 ats_application rows there (the nine-row column snapshot is from that read) but 0 ats_offer rows - #39, upstream, not this change - so the marketplace-wide recomputation FROM offers was done on the memory driver instead, where the same persona reads all 23. No ablation/mutation runs in this task: no new gate script was authored, so there was nothing whose ability to fail needed proving.",
  "mcp_calls": "0 - every GitHub read and write in this run went through repo-scoped REST (issue body, comments, open-issue list for the duplicate check, both new issues, the PR create and its read-back). The REST channel was probed first and answered 200.",
  "open_questions": [],
  "out_of_scope_findings": [
    "filed as #66: docs/screenshots/03-hiring-home-overview.png predates this tile - its caption's four numbers all still hold (no stage counts move) but the image no longer shows the shipped dashboard; regenerating a release screenshot is its own documented procedure and a caption-only edit would describe something the image does not show",
    "filed as #65: all 200 demo applications carry last_activity_at inside a 0.7-second window of boot because ApplicationStampHook assigns it unconditionally on beforeInsert, so the seed's activityDaysAgo never reaches the database - pre-existing on main, and the reason this PR's extra write to the application costs nothing"
  ]
}

---

Generated by Claude Code