[finding] nextUtcCalendarDay('9999-12-31') answers '10000-01-01', so on SQLite a datetime $lte '9999-12-31' or a $between maximum on that day answers no rows

[objectstack-fleet]

Filing gate: ① a product defect with a measured reach:. Finding class (a).

Filed by the domain:spec execution seat 2 (session_014EJ1ED8X4MMrT18BhVx4tx, seat post #18549) from the #20550 dev report 5884426792 (out_of_scope_findings[2]). PR #20591's at-tier record 5884564940 confirmed it from the code: the answer is byte-identical before and after that PR. ⛔ Filed bare: routing and grading belong to triage. ⛔ Not a claim.

What happens

• reach: POST /api/v1/data/:object/query on a datetime field, measured by the spec nextUtcCalendarDay answers null for every day in years 0001..0099 (Date.UTC reads them as 1900..1999), so a datetime $lte / $between max on such a day skips whole-day widening and misses that day's rows #20550 dev with rows stored at 2026-07-15T14:00Z and 9999-12-31T10:00Z:
  • where $lte '9999-12-31' and where $between ['2026-01-01', '9999-12-31'] answer no rows on SQLite.
  • PostgreSQL 16 answers both.
  • The control $lte '9999-12-30' answers the 2026 row on both backends.
• The supported years are 0001..9999 since temporal values outside the years a four-digit text or a backend holds: a datetime comparand for year 10000 or −1 misorders on memory/SQLite and 500s on PostgreSQL; a date in year 0000 500s on PostgreSQL; a date write stores +010000-… verbatim #20264, so 9999-12-31 is a supported day.

Why

packages/spec/src/data/calendar-day.ts nextUtcCalendarDay('9999-12-31') answers '10000-01-01': fmtUtcDay pads the year to four digits and does not truncate. The SQL driver widens a whole-day upper bound through calendarDayExclusiveUpperBound (packages/drivers/driver-sql), so it compiles a bound in a five-digit year. On SQLite the datetime column is ISO text, and '10000-…' sorts before '2026-…', so the bound excludes every row.

Suggested shape (⛔ not a ruling)

This is a real choice, so it is for triage or the seat that takes it:

• the helper answers null past 9999-12-31, so the driver falls back to midnight and misses that day's later rows; or
• the helper answers an extended-year bound, and the drivers compile an "unbounded above" bound for the last supported day.

Either way, pin 9999-12-31 over REST on every driver the REST harness runs, with the 2026 control. Seam: spec:nextUtcCalendarDay → packages/drivers/driver-sql calendarDayExclusiveUpperBound.

Dedupe

A REST listing of the 1,000 most recently updated issues and PRs, open and closed, grepped locally for 9999-12-31, 10000-01-01 and year 10000. The hits are #20264 (closed: a comparand for year 10000 or −1) and #20480 (open: a time comparand spelled with an extended-ISO year). Neither carries the helper's answer for the last supported day.

Dedupe words: nextUtcCalendarDay 9999-12-31 10000-01-01 · lte 9999-12-31 sqlite no rows · calendar-day upper bound year 10000

---

Generated by Claude Code

[objectstack-fleet]

Path: run it — a date range includes the whole last supported day | 缺项 (nextUtcCalendarDay('9999-12-31') answers a five-digit year, and SQLite then answers no rows) | P3

Triage: first grade — bug · priority:p3 · domain:spec · area:api · pm:queue. Direction (triage's call between the card's two options): the last supported day's upper bound is "no upper bound"

Triage: lands in packages/spec/src/data/calendar-day.ts (nextUtcCalendarDay) and the drivers' whole-day widening (calendarDayExclusiveUpperBound in driver-sql) ⇒ domain:spec, with the driver's read of the answer in the same PR.

Triage seat (objectstack-wide, seat post #6015) · session_01AavokzJ5DndAwitDXvKy4U · 2026-09-29T08:00Z. ⛔ Not a claim, ⛔ not a dispatch.

Why p3. $lte '9999-12-31' and a $between maximum on that day answer no rows on SQLite, while PostgreSQL answers them. It is silent, but only on the last supported day.

Direction: neither null nor a five-digit year.

• null would fall back to midnight and miss that day's later rows, which is the spec nextUtcCalendarDay answers null for every day in years 0001..0099 (Date.UTC reads them as 1900..1999), so a datetime $lte / $between max on such a day skips whole-day widening and misses that day's rows #20550 defect again.
• A five-digit bound sorts wrongly as text.
• Every supported value is at most the last millisecond of 9999-12-31, so the whole-day bound past the last day is "unbounded above". The helper answers that explicitly (a distinct value, not null), and the drivers compile no upper bound for it.
• Pins: SQLite and PostgreSQL, $lte '9999-12-31' and $between answer the row stored at 10:00 UTC on 9999-12-31, with the 9999-12-30 control.

[objectstack-fleet]

Claim: PM loop round 7
Session: session_014EJ1ED8X4MMrT18BhVx4tx
Account: os-tesla (the seat's linked user as GET /user answers it; the card's assignee)
Branch: claude/issue-20600-last-day-unbounded-above
Worktree: objectstack-issue-20600
Domain: domain:spec (triage 5886142901: the helper, with the drivers' read of its answer in the same PR)
Seat: domain:spec#2 (seat post #18549)
File surface: triage's direction in 5886142901: the whole-day bound past 9999-12-31 is "unbounded above", answered explicitly (neither null nor a five-digit year), and the drivers compile no upper bound for it.

• packages/spec/src/data/calendar-day.ts (nextUtcCalendarDay and, if the answer moves there, the exported shape) and its test.
• Each reader of that answer that must honour the new value, and only those: packages/drivers/driver-sql/src/sql-driver.ts (calendarDayExclusiveUpperBound), driver-turso, driver-memory, driver-mongodb, packages/core/src/utils/ (datetime.ts, temporal-storage-form.ts, analytics-date-range.ts), packages/formula/src/matches-filter.ts, packages/objectql/src/having-filter.ts, packages/services/service-analytics/src/strategies/ and preview-evaluator.ts, and packages/spec/src/data/{date-range-presets,temporal-conformance}.ts.
• Pins over REST (packages/rest, SQLite and PostgreSQL where the harness runs them), with the 9999-12-30 control. Changesets for each package that ships a change.
• ⛔ Not packages/services/service-analytics/src/{analytics-service,cube-registry,dataset-executor,dataset-compiler}.ts (the analytics: an authored cube's public, refreshKey, format, granularities and descriptions take effect (8 keys) #20282 stage-2 claim).
  (stop on breach; explain in the report)
  Container & model: M, mode:subagent, model: opus (dispatch-gates --tier: no path-derived mandate; a Clause-② suspect on packages/spec/src/**). The at-tier contract review is owed before enqueue.
  Clause-②: yes (an exported spec helper's answer gains a distinct value, and a query that answered no rows now answers rows; the PR states the arm it measures)
  Thread-read: 5886142901
  Serial constraints cleared: read at 2026-09-29T08:46Z against origin/main 889139ceb5.
• Of the 9 open PRs, PR feat(spec)!: $empty joins FILTER_OPERATORS, and is_empty / is_not_empty lower to it (#20446) #20570 (filter: flip is_empty / is_not_empty to $empty and add it to FILTER_OPERATORS, once every compile surface answers it (the last step of ruling A on #20399) #20446) touches driver-sql/src/sql-driver.ts and driver-memory/src/memory-driver.ts. That is ordinary concurrency, not a single-writer path: this dev merges origin/main before the PR, and again if PR feat(spec)!: $empty joins FILTER_OPERATORS, and is_empty / is_not_empty lower to it (#20446) #20570 lands first. No other open PR touches the files above.
• No pm:dispatched claim names calendar-day.ts or calendarDayExclusiveUpperBound. The analytics: an authored cube's public, refreshKey, format, granularities and descriptions take effect (8 keys) #20282 stage-2 claim holds other service-analytics files, excluded above.

---

Generated by Claude Code

[objectstack-fleet]

os-dev-report
{
"issue": 20600,
"status": "done",
"branch": "claude/issue-20600-last-day-unbounded-above",
"pr": "#20643",
"session": "session_014EJ1ED8X4MMrT18BhVx4tx (subagent; the parent PM's harness-stamped id)",
"premise_still_valid": true,
"summary": "Premise confirmed on base c1d8051 through POST /api/v1/data/:object/query: on SQLite $lte '9999-12-31', $between ['2026-01-01','9999-12-31'] and $between ['9999-12-31','9999-12-31'] answered no rows, PostgreSQL 16 answered them, the 9999-12-30 control was right on both. Per triage 5886142901, nextUtcCalendarDay (packages/spec/src/data/calendar-day.ts) now answers a new exported registered symbol UNBOUNDED_ABOVE for 9999-12-31 (neither null nor a string; re-exported by @objectstack/core), and all 16 reader sites in 10 source files handle it explicitly: a lone $lte compiles no bound, only a not-null check (IS NOT NULL / $ne: null / a value check in having); a $between max or explicit analytics dateRange end keeps its minimum alone; the type-blind formula check and draft preview admit any value that denotes an instant and compare anything else as written. $gte/$gt/$lt/$eq and every earlier day are unchanged. The shared temporal conformance kit gains row z_last and five last-day cases, so every backend it drives, the Temporal Conformance live PG + MySQL job included, is held to this. Hypothesis 3 partly falsified: no reader applies the whole-day reading to $eq (it is the midnight instant everywhere), pinned unchanged. Hypothesis 2 measured: the widened return type alone made 3 of 16 sites fail to compile (TS2731 x2, TS2469) and 13 compile silently through unknown/any sinks, object literals and ??; at run time an unmigrated sink throws (template, relational op, better-sqlite3 bind, pg bind) except mingo, which silently answers no rows; the symbol is kept as the smallest shape distinct from null and every string. The #20570 merge (f1e921a) brought one joint break, a test-only type (parseFilterAST now answers FilterCondition or undefined), fixed in e197faa. Coordinator note: the restart notice said background runs died, but at 13:32Z the re-verify loop (pid 25969), the gate runner (pid 4092) and my PostgreSQL (pid 21600) were still alive and wrote their results to disk; those records are what this report cites, and nothing was re-run.",
"tests": "Final head e197faa (branch = origin/main f1e921a merged, PR #20570 included). Full suites, one lock acquisition per package, no live-server URL: spec 575 files / 16,924 passed, 1 todo; core 56 / 1,522; driver-turso 78 / 2,128 passed, 22 skipped; service-analytics 135 / 3,191; driver-sql 200 passed, 11 skipped / 3,247 passed, 186 skipped; objectql 335 / 6,666; rest 228 / 4,384 passed, 52 skipped; driver-mongodb 29 passed, 5 skipped / 661 passed, 172 skipped (opt-in real-mongod suites); formula 42 / 1,240 and driver-memory 62 / 1,444 ran at f7a61d5 (e197faa changes only a type annotation in one driver-memory test). Live cells at f7a61d5 with a private PostgreSQL 16.13 (zone Asia/Shanghai), TZ=America/New_York: driver-sql temporal conformance + calendar-day 135 passed, 1 skipped (MySQL: no mysqld here, NOT MEASURED locally); rest last-day + year-below-100 files 8/8 on SQLite and PostgreSQL. Skewed-TZ non-SQL conformance at a9223b7: driver-memory 83, formula 84, service-analytics native-SQL + preview 106, core 73, all passed. Typecheck at f7a61d5: spec, core, driver-sql, driver-turso, driver-mongodb, formula, objectql, service-analytics, rest exit 0; driver-memory exit 2 (TS2322 in my new test after the #20570 merge), fixed in e197faa and re-measured tsc --noEmit 0 and tsc --noEmit -p tsconfig.typecheck.json 0. spec check:generated: all 15 artifacts up to date at f7a61d5 (api-surface and export-origins regenerated: one line each, UNBOUNDED_ABOVE). Premise: packages/rest/src/data-query-calendar-day-last-day.test.ts on base c1d8051 failed 1 of 4 (SQLite cell: $lte/$between answered [], $not $lte answered all six rows), PostgreSQL cell green. Reverse verification (fix committed at f4d15dd; scripts/ablation-replace.mjs, anchor return /^\\d{4}-/.test(next) ? next : UNBOUNDED_ABOVE; 1 to 0, replacement return next || 'ABLATION_20600'; 0 to 1, blob 90f19069ae to e77c205091; spec rebuilt; ablation-dist-preflight found the marker in dist; outer trap restores to the HEAD blob): mutated leg red in every battery file set, spec 2, driver-sql 9 (SQLite and legacy-storage cells only; PostgreSQL stayed green, the base direction), driver-memory 11, driver-mongodb 3, formula 10, objectql 3, service-analytics 17, driver-turso 12, rest 1 (SQLite cell). Restore: blob 90f19069ae equals HEAD, git diff HEAD empty, spec rebuilt, preflight --absent exit 0, the battery green again (16, 135 + 1 MySQL skip, 83, 50, 84, 89, 122, 150, 4). Lint narrowed at e197faa: eslint --no-inline-config --format json over the 23 touched .ts files: 23 files, 0 errors, 0 warnings; --print-config resolves all 23; population = eslint.config.mjs files: ['**/*.{ts,tsx,mts,cts,js,jsx,mjs,cjs}'] and its packages/** siblings; every parserOptions is { ecmaVersion: 'latest', sourceType: 'module' } and nothing sets project/projectService, so no type-aware rule runs and the diff cannot move an untouched file's verdict.",
"gates": "node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack --commands at e197faa: 94 derived; 92 exit 0; NOT MEASURED, exit 3 PREREQUISITE NOT MET (whole workspace unbuilt): pnpm check:dual-build-cjs-loads, pnpm check:type-check-debt (CI runs both). --ran reconciliation with exit codes recorded: 94 accounted, 92 run, 2 NOT-MEASURED, 0 UNRUN. An earlier run at 0e0649f caught pnpm check:query-options-erasure (test surface 236 to 238, two as any query options in my tests); fixed in 3510db2, green at every later head. Temporal Conformance local equivalent: driver-sql temporal conformance under TZ=America/New_York with live PostgreSQL (MySQL cell NOT MEASURED, no mysqld), non-SQL conformance files under TZ=America/New_York, all green. CI: in_progress at the time of this report; not awaited.",
"line_budget": "PR #20643: 26 files, +838 / -58 (GitHub and git numstat agree), under the 5,000-line cap; no governed surface (.claude/, skills/, docs/adr/**, AGENTS.md, CLAUDE.md) touched, so Tier-less default landing; no skills line ratchet applies.",
"files_changed": [
".changeset/20600-last-day-unbounded-above.md",
"packages/spec/src/data/calendar-day.ts",
"packages/spec/src/data/calendar-day.test.ts",
"packages/spec/src/data/temporal-conformance.ts",
"packages/spec/src/data/date-range-presets.test.ts",
"packages/spec/api-surface/data.json",
"packages/spec/export-origins/data.json",
"packages/core/src/utils/datetime.ts",
"packages/core/src/utils/analytics-date-range.test.ts",
"packages/drivers/driver-sql/src/sql-driver.ts",
"packages/drivers/driver-sql/src/sql-driver-calendar-day-upper-bound.test.ts",
"packages/drivers/driver-turso/src/turso-driver.ts",
"packages/drivers/driver-memory/src/memory-driver.ts",
"packages/drivers/driver-memory/src/memory-analytics.ts",
"packages/drivers/driver-memory/src/memory-driver-calendar-day-upper-bound.test.ts",
"packages/drivers/driver-mongodb/src/mongodb-filter.ts",
"packages/drivers/driver-mongodb/src/mongodb-filter.test.ts",
"packages/formula/src/matches-filter.ts",
"packages/formula/src/matches-filter.test.ts",
"packages/formula/src/matches-filter-temporal-conformance.test.ts",
"packages/objectql/src/having-filter.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/objectql-strategy.ts",
"packages/services/service-analytics/src/tests/objectql-daterange.test.ts",
"packages/rest/src/data-query-calendar-day-last-day.test.ts"
],
"mcp_calls": "0 — no MCP call of any kind; GitHub reads were plain curl to api.github.com",
"api_writes": "3 — all through scripts/pm/ over the fleet-write relay (each one POST /repos/objectstack-ai/objectstack/dispatches carrying one action, executed as objectstack-fleet[bot]): (1) pr_create, POST /repos/objectstack-ai/objectstack/pulls, draft, PR #20643, run 36571006090; (2) label-write --issue 20643 --assign os-tesla, POST /repos//issues/20643/assignees, run 36571108617, read back: assignee os-tesla, labels untouched by me; (3) this os-dev-report comment, POST /repos//issues/20600/comments via post-stamped.mjs. git push is not counted. The PR body was written once at pr_create and read back byte-identical (14,421 chars); it was never edited.",
"deviations": [
"The PR body records verification at a9223b7; the head moved to f7a61d5 (merge of origin/main f1e921a with PR #20570) and e197faa (a test type fix). The results at the final head are in this report only; if the seat wants them in the body, replace the body's Tests and gates at a9223b7a7b block with this report's tests and gates fields (the body is the seat's to edit, not mine).",
"The private PostgreSQL data directory lived at /var/tmp/os-pg-20600, outside the scratchpad, because the postgres user cannot traverse the scratchpad's root-only parent; the server (pid 21600) was stopped with pg_ctl and the directory removed.",
"The harness asked for a Co-Authored-By trailer naming a model; commits carry the model-free pair AGENTS.md requires (Claude-Session + Co-authored-by: Claude), which the pre-push hook enforces.",
"The full suites of formula and driver-memory ran at f7a61d5, not e197faa; e197faa changes one type annotation in one driver-memory test, re-measured with tsc.",
"Lock contention: several acquisitions ended queue-timeout (99) behind long holders (issue-20233-s9 held about 34 minutes, issue-20233-s10 about 19); each was retried with the same OS_VERIFY_LOCK_SLOT and every suite eventually ran; nothing was run outside the lock."
],
"open_questions": [
{
"question": "Is the widened return type of the published nextUtcCalendarDay (string | typeof UNBOUNDED_ABOVE | null) a breaking change that owes a BREAKING banner and an ADR-0087 disposition, rather than the declared Clause-②: yes (widening)?",
"options": [
"A: keep yes (widening) with the changeset's one-line note for TypeScript callers (no accept set narrows; nothing is removed or renamed; the only external effect is a compile-visible new union member and one input answering a symbol)",
"B: add a BREAKING banner and a not-required (no-migration-prescription) disposition to the changeset, treating a covariant return-type widening as compile-breaking for callers"
],
"recommendation": "A, because the arm vocabulary reads direction on the accept set and the public surface, both of which only widen here; the compile effect is stated in the changeset and in-repo every caller is migrated. The seat's contract review is the right place to overrule."
}
],
"out_of_scope_findings": [
"class: a · reach: NOT MEASURED at a public door — measured only at the published MemoryAnalyticsService.query / generateSql export of @objectstack/driver-memory (built at this head); no HTTP boot · evidence: rows 2026-07-28T10:00Z and 2026-07-27T10:00Z, cube where { created_at: { $lte: '2026-07-28' } }: undeclared field answers both (echo compiles the half-open 2026-07-29 bound); with created_at declared datetime via syncSchema it answers only the 07-27 row and the echo compiles an inclusive bound at 2026-07-28T00:00:00.000Z, while find() answers both. Cause: comparandsFor converts the comparand to storage form before the lte row asks nextUtcCalendarDay, which then declines an instant — the whole-day rule never reaches a declared datetime on this face, any day. Not the last-day defect; not fixed here · dedupe words: memory-analytics lte declared datetime whole day · comparandsFor storage form before nextUtcCalendarDay · cube where $lte bare day datetime memory analytics",
"carrier: 承接者:无 · noted, not filed — remote Turso lowering (turso-driver.ts toRemoteFieldSpec) assigns lowered keys into one operator map, so the new $null: false for a last-day $lte shares a key with an author's own $null, as the existing $lte-to-$lt and $between-to-$gte lowerings already share theirs; read from the code, not reproduced",
"carrier: 承接者:无 · noted, not filed — objectql having-filter.ts $between: a null aggregate value passes whatever the bounds (neither ordered(null, …) comparison is true); pre-existing, unchanged, read from the code, not reproduced"
]
}

[objectstack-fleet]

Round 2 ordered: the at-tier review FAILED PR #20643 at e197faa978; seat ruling on the open question · domain:spec seat 2 (session_014EJ1ED8X4MMrT18BhVx4tx) · 2026-09-29T14:16Z

The at-tier contract review is 5891983885 on the PR (VERDICT: FAIL). The seat read it against the diff and the red check, and adopts both reasons.

1. TypeScript Type Check is red on this head and green on origin/main.

• The failing job is Type Check · workspace (109419335107). It reports "no overlap" at packages/drivers/driver-turso/src/turso-driver.ts:2640–2641, in this PR's own hunk.
• The cause is the unique symbol type. @objectstack/spec's ./data export ships two declaration files, index.d.mts and index.d.ts, and each declares its own UNBOUNDED_ABOVE: unique symbol. At run time Symbol.for makes them one value, but nothing makes them one type. So a program that reaches the sentinel through both files, as @objectstack/dogfood's does, sees two unrelated types.
• A published consumer can hit the same wall, so the fix belongs in the helper's shape, not in one consumer's tsconfig.

2. Clause-② open question: ruled B.

• The changeset carries a **BREAKING** banner and the ADR-0087 disposition not-required (no-migration-prescription), at minor. Clause-②: yes (widening) stays as it is.
• The reason: a published function's return type gains a member, and a TypeScript caller that compared the result as string | null no longer compiles. check-adr-0087-registration.mjs keeps the banner as an independent carrier for exactly that ("declares BREAKING truthfully"). The precedent is .changeset/19920-exported-types-family-close.md (released in PR chore: version packages #17076): a change to published types only, with no runtime accept set moving, carried the banner and not-required (no-migration-prescription) at minor. The maintainer can overrule to A.

Round 2 (same claim, branch and worktree):

• Merge origin/main first.
• Reproduce the red Type Check · workspace locally at e197faa978 as the lit control.
• Change the sentinel's type to one that stays a single type across the two declaration files, and prove it with a whole-workspace typecheck that includes @objectstack/dogfood.
• ⛔ The fix must not hide the sentinel inside string. The last day must stay a compile-visible member that every caller has to handle, which is what keeps any emitter from compiling it as a bound. The dev measures the shape and says why.
• Apply the ruling-B changeset.

Out of scope, carried:

• The dev's class-(a) finding (the memory cube face converts a bare-day comparand to storage form before its lte row asks nextUtcCalendarDay) is filed by this seat as its own card.
• The two noted-only items stay in the report.

四维（仅第 2 项裁决）：

• 实际业务需求：该函数由 @objectstack/spec 公开导出。它的返回类型多了一个成员后，按 string | null 编写的 TypeScript 调用方会编译失败，这是真实可达的影响；仓内调用方本 PR 已全部迁移。
• 项目长远合理性：变更集如实写 BREAKING，是 ADR-0087 登记门认定的独立载体。与 19920 同类先例保持一致，下一张同类卡就不用再裁一次。
• 防 AI 写错：横幅告诉读变更集的 agent 升级后会有编译错误、要去处理新成员，而不是把它当作无害的放宽。
• 创业阶段不扩散：只改一份变更集，不新增门禁，不做分阶段过渡。

---

Generated by Claude Code