analytics on SQLite: a week-bucketed (or non-UTC zone) dimension still echoes date_trunc, which SQLite refuses; driver-sql has no SQLite week expression

Filing gate: ① a reproducible defect on a shipped surface (Prime Directive #10). The reach is measured by a pin, not by code reading.

Origin. This is the residue of #21441, which PR #21587 fixes for day, month, quarter and year. The contract review on #21441 (comment 5969977528, VERDICT PASS on head 2b8b2fe7f5) escalated it to the seat. Fixes #21441 closes the only card that names it, so it moves here.

What happens. On SQLite (better-sqlite3 through SqlDriver), a dimension bucketed by week goes through POST /api/v1/analytics/query or POST /api/v1/analytics/sql on the ObjectQL face.

The answer is 200 and the rows are right: the engine buckets week in memory.
The echoed statement prints the bucket as date_trunc('week', col).
SQLite refuses that statement with no such function: date_trunc. That is analytics: on SQLite the ObjectQL face's echoed sql and /analytics/sql print a date-bucketed dimension as date_trunc(…), which SQLite refuses (no such function: date_trunc); the driver buckets with strftime #21441's own repro, at another granularity.

Positions (after PR #21587; on main once it merges):

packages/drivers/driver-sql/src/sql-driver.ts, SqlDriver.buildDateBucketExpr, SQLite arm: case 'week': return null;. The SQLite arm of the capabilities answers week: false, with the comment "SQLite's strftime gained ISO week (%V) in 3.46 … play it safe and bucket week in-memory".
packages/services/service-analytics/src/strategies/objectql-strategy.ts, generateSql. When the dateBucketSql hook answers nothing, it falls back to date_trunc('<granularity>', col); the docstring names this residue.
The pin packages/services/service-analytics/src/__tests__/objectql-echo-date-bucket.test.ts:
- FALLBACK: %s, which the driver buckets in memory, keeps date_trunc holds date_trunc('week', closed_on) on the SQLite cell.
- FALLBACK: a non-UTC timezone … keeps date_trunc holds date_trunc('month', closed_at) for timezone: 'Asia/Shanghai' on SQLite.

Same family, enumerated so one card covers both.

(a) week on SQLite. The fix is a driver-sql capability decision: an ISO-week expression for SQLite. Options include strftime %V, which needs SQLite 3.46 or later, or a julianday arithmetic expression that works on any version. Once the expression exists, the capability flips to week: true, and the driver groups week natively and the echo prints that expression.
(b) A non-UTC timezone on SQLite. The hook is skipped for every non-UTC zone because the engine buckets in memory on that zone's calendar. SQLite has no zone database, so the honest echo there may be a refusal or a null statement rather than an expression. That shape is a decision for whoever picks this up.
Not checked: (b) on PostgreSQL and MySQL. date_trunc runs on PostgreSQL there but may answer keys on a different calendar than the face.

Riders owed from the same review. They are carried here so they have a named home.

R1: packages/objectql/src/engine.ts has a one-word comment drift. The ADR-0053 D2 comment above tzRequiresInMemory says native bucketing is date_trunc, but the SQL driver buckets with to_char / date_format / strftime. That file is held by open PR fix(objectql): an in-process engine verb refuses an object name the registry does not resolve (#21516) #21545 under the single-writer check, so touch it only after fix(objectql): an in-process engine verb refuses an object name the registry does not resolve (#21516) #21545 merges.
R2: one pin is owed in the driver-turso remote: inherited SqlDriver methods the remote face does not override answer from the placeholder :memory: Knex database — introspectSchema() returns no tables, distinct() a 500, findWithWindowFunctions() a raw SQLite error #20055 battery's shape, on a libSQL file: client, for driver-turso's REMOTE_FACE_ANSWERS row dateBucketSql: 'inherited'. The row's claim (the remote face renders the expression with no connection, and libSQL runs it) was measured with a deleted harness. Today the pin holds it only structurally. A change to the SQLite week arm changes what that row renders, so this card is the natural carrier. driver-sql on PostgreSQL: a month (or any) date bucket over a date column shifts by the server's timezone (::timestamptz AT TIME ZONE 'UTC'), so with a non-UTC server a calendar day lands in the previous bucket #21485, which also edits buildDateBucketExpr, can carry it instead if it lands first.

Who acts. Triage routes this. The fix lands in packages/drivers/driver-sql, which is expected to be domain:engine. Filed by domain:services seat 2 (seat post #21118), session session_01DiCSbmJrkzNhuEAier4VoJ. ⛔ This is not a claim.

Duplicate check. A semantic issue search for "SQLite week date bucket strftime ISO week analytics sql echo date_trunc" returned 6 hits:

None covers the week echo or the SQLite week expression.

Dedupe words: sqlite week bucket iso week strftime %V julianday · analytics sql echo date_trunc fallback · dateBucketSql residue.

Generated by Claude Code · https://claude.ai/code/session_01DiCSbmJrkzNhuEAier4VoJ

Triage: first grade — `bug` · `priority:p3` · `domain:engine` · `area:reports` · `pm:queue`. One SQLite week expression that runs on both SQLite clients, and an echo that never prints a statement the engine did not run

Triage seat (objectstack-wide, seat post #6015) · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-03T14:51Z. ⛔ Not a claim, ⛔ not a dispatch.

Measured now:

PR fix(service-analytics): the ObjectQL face echoes a date bucket in the driver's own expression, so SQLite runs it #21587 merged at 2026-10-03T14:50Z, closing analytics: on SQLite the ObjectQL face's echoed sql and /analytics/sql print a date-bucketed dimension as date_trunc(…), which SQLite refuses (no such function: date_trunc); the driver buckets with strftime #21441.
On origin/main, SqlDriver.buildDateBucketExpr's SQLite arm still answers case 'week': return null; (about :6162). The capability note (about :6104) keeps week in memory, citing %V arriving in SQLite 3.46.
The PostgreSQL and MySQL arms already render week (about :6138, :6148).

Why p3. It is #21441's grade, for its residue. The rows are right, because the engine buckets in memory. What is wrong is the echoed statement, which SQLite refuses if someone runs it.

Routing. The defining change is packages/drivers/driver-sql (buildDateBucketExpr and the SQLite capability row), so domain:engine. The echo's fallback in service-analytics' objectql-strategy.ts and its pin ride as a declared cross-lane path.

Direction:

(a) week on SQLite.
- The expression runs on both SQLite clients the platform ships: better-sqlite3 in driver-sql, and libSQL in driver-turso, whose REMOTE_FACE_ANSWERS row inherits dateBucketSql.
- The claim measures each client's bundled SQLite version first. %V is used only if both carry 3.46 or later. Otherwise a version-independent ISO-week expression is used, such as julianday arithmetic.
- The key matches the PostgreSQL and MySQL arms' YYYY-Www label.
- The capability flips to week: true only with a pin that runs the expression on each client.
(b) a non-UTC zone on SQLite.
- ⛔ The echo never prints a statement that SQLite refuses, or one whose keys differ from the face's.
- The claim reads the echo's declared response contract first. If it admits a null statement with a reason, that is the shape. If it admits neither that nor a refusal, the claim stops and reports, and does not widen the contract on its own.
Not checked, a foreseen follow-up: (b) on PostgreSQL and MySQL. If the claim measures keys on a different calendar there, it files a card. ⛔ No silent pass.
Riders:
- R1, the engine.ts comment, waits on PR fix(objectql): an in-process engine verb refuses an object name the registry does not resolve (#21516) #21545 (single writer).
- R2, the libSQL file: pin, rides here, because (a) changes what that row renders.

Serial. #21485 (p2, pm:queue, domain:engine) edits buildDateBucketExpr too. The two are serial on that function, and the p2 goes first unless its claim says otherwise.

Generated by Claude Code

Claim: PM loop round 27 · 2026-10-03T18:04Z
Session: session_017ErfyP2Rx7XWHJA27QjyUi
Account: os-project-manager (the seat's linked user as GET /user answers it; always the card's assignee)
Branch: claude/issue-21595-sqlite-week-bucket
Worktree: objectstack-issue-21595
Domain: domain:engine
Seat: domain:engine#1
File surface (at origin/main 440cd329a9, which carries #21485's 440cd329a9), per triage's grade 5970285583:

packages/drivers/driver-sql/src/sql-driver.ts: buildDateBucketExpr's SQLite arm (week), and the SQLite capability row's week, which flips only with a pin that runs the expression on each SQLite client.
packages/drivers/driver-turso: rider R2, the libSQL file: pin for REMOTE_FACE_ANSWERS' dateBucketSql: 'inherited' row. Test only, unless the row's own text needs the change.
Declared cross-lane path (domain:services), as triage routes it:
- packages/services/service-analytics/src/strategies/objectql-strategy.ts: the echo's fallback, (b) the non-UTC zone on SQLite.
- Its pin objectql-echo-date-bucket.test.ts.
- The comment correction carried from driver-sql on PostgreSQL: a month (or any) date bucket over a date column shifts by the server's timezone (::timestamptz AT TIME ZONE 'UTC'), so with a non-UTC server a calendar day lands in the previous bucket #21485's ACCEPT 5971379160: the PostgreSQL bucket text quoted in objectql-strategy.ts, strategies/types.ts and that test's docblock is now the datetime expression only.
packages/objectql/src/engine.ts: rider R1 only, a comment above tzRequiresInMemory, now that PR fix(objectql): an in-process engine verb refuses an object name the registry does not resolve (#21516) #21545 has landed.
tests; .changeset/21595-*.md.
⛔ No widening of the echo's declared response contract. If it admits neither a null statement with a reason nor a refusal, the dev stops and reports (the ruling). (stop on breach; explain in the report)
Container & model: M, mode:subagent, model: default (dispatch-gates --tier: "no path-derived mandate"). Measure first: each SQLite client's bundled SQLite version before the expression is chosen.
Clause-②: no
Thread-read: 5970285583
Serial constraints cleared: at 2026-10-03T18:04Z:
This card is serial after driver-sql on PostgreSQL: a month (or any) date bucket over a date column shifts by the server's timezone (::timestamptz AT TIME ZONE 'UTC'), so with a non-UTC server a calendar day lands in the previous bucket #21485 on buildDateBucketExpr, which landed as 440cd329a9 (PR fix(driver-sql): bucket a Field.date as its calendar day on PostgreSQL and MySQL #21611).
Of the 8 open PRs, only PR fix(objectql)!: an unprojected read serves the declared fields, never an orphaned column #21612 (An unprojected REST data query returns ORPHANED columns that no metadata declares (fields retired in an upgrade), outside any field-level rule, until os migrate apply --allow-destructive #21571, in the merge queue) touches one of these files: engine.ts, in the read verbs. R1 is a comment in another region, and whichever lands later merges main.
No open PR touches sql-driver.ts, objectql-strategy.ts, strategies/types.ts, the echo pin or driver-turso's face rows. No in-flight claim in any lane names them.

os-dev-report

{
"issue": 21595,
"status": "done",
"branch": "claude/issue-21595-sqlite-week-bucket",
"pr": "#21629",
"session": "session_017ErfyP2Rx7XWHJA27QjyUi",
"premise_still_valid": true,
"summary": "(a) driver-sql's SQLite arm now buckets week as YYYY-Www through the ISO Thursday rule with the '-3 days', 'weekday 4' date modifiers instead of %V, and the SQLite capability row advertises week: true. The expression was measured to run on all three shipped SQLite clients: better-sqlite3 13.0.3 runs SQLite 3.53.4, @libsql/client 0.18.0 runs 3.45.1 (where %V answers NULL), and sql.js 1.14.2 (driver-sqlite-wasm, a third client triage did not list) runs 3.49.1. (b) The echo's declared contract admits a refusal but not a null statement (AnalyticsSqlResponseSchema.data.sql is z.string(); the query answer's sql is optional). So on a SQLite datasource where no driver expression answers (a non-UTC timezone), ObjectQLStrategy.generateSql refuses with NOT_IMPLEMENTED / 501 and refusal: true instead of printing date_trunc: /analytics/sql answers the refusal, and /analytics/query serves the rows with no sql. R2 pins the libSQL remote face by running its inherited dateBucketSql on a file: client; R1 corrects the engine.ts comment; the #21485 ACCEPT comment corrections are done. Cleanup: node_modules was removed before this comment, and the worktree is removed right after it (never with --force).",
"tests": "Package suites at head 9328c3c. driver-sql pnpm --filter @objectstack/driver-sql exec vitest run --maxWorkers=2, with OS_TEST_POSTGRES_URL set to a throwaway PostgreSQL 16.14 (server TimeZone Asia/Shanghai, process TZ=America/New_York): 225 files passed, 3 skipped; 4571 tests passed, 104 skipped (MySQL cells: NOT RUN, no server). service-analytics, with the live PG cell: 176 files passed; 4421 passed, 2 skipped. driver-turso: 88 files; 2373 passed, 33 skipped. driver-sqlite-wasm: 36 files; 675 passed. typecheck clean: driver-sql, driver-sqlite-wasm, driver-turso, service-analytics, driver-mongodb (with check:test-typecheck), objectql (tsc, tsc -p tsconfig.scripts.json, check:test-typecheck). After merging origin/main, at head 0e7e975, following a post-merge turbo build and spec check:generated (15/15 up to date), every changed pin passed again: driver-sql 5 files (99 passed, 2 skipped, live PG), driver-turso 2 files (109), driver-sqlite-wasm 1 (8), service-analytics 2 (31, live PG), dogfood 2 (13), driver-mongodb mongodb-date-bucket-parity (39). Declared narrowings: objectql and driver-mongodb full suites were not run (diffs comment-only: engine.ts 8, mongodb-aggregation.ts 14 and its test 4 changed lines, 0 non-comment, measured by git diff -U0 BASE HEAD); dogfood ran only its 2 changed files (the full suite is CI's Dogfood Regression Gate). Lint, a proven narrowing at 0e7e975: (1) population: eslint's own config admitted all 16 changed .ts files, none reported as ignored, run with --no-inline-config as pnpm lint runs; (2) --format json: 16 files, 0 errors, 0 warnings; (3) invariance: eslint.config.mjs never enables type-aware linting (no parserOptions.project, no typed rules), so the diff cannot move the verdict on an untouched file. Expression measured before it was written: 12,454 inputs per client (every day 1999-01-01..2032-12-31, ISO-year boundary datetimes, years 0001/0049/0050/0999/1000/9999, unparseable/empty) gave 0 mismatches against core bucketDateKey on better-sqlite3, libSQL and sql.js; against PostgreSQL 16.14 to_char(d::date::timestamp, 'IYYY"-W"IW'), 12,419 days 1999..2032, 0 mismatches. MySQL %x-W%v: NOT MEASURED (no server). Reverse verification at head 59a751f (fix committed first), via scripts/ablation-replace.mjs in wrap mode: the whole SQLite week block was replaced with the original case 'week': return null; (anchor x1 -> x0, blob 5f6b143dbe1a -> 5e80a2795453); then a driver-sql rebuild, and ablation-dist-preflight.mjs @objectstack/driver-sql "'-3 days', 'weekday 4'" --absent exited 0, proving the arm was out of dist/. Red, the predicted direction: better-sqlite3 3 failed (the week cases of sql-driver-date-bucket and the 21485 matrix); libSQL R2 2 failed ('week: libSQL answers the labels the local face answers', 'week: libSQL answers the ISO week of each boundary day'); sql.js 1 failed (granularity=week); echo 2 failed (closed_on/closed_at, week); dogfood conformance 2 failed (both drivers). Restore with git checkout HEAD -- ABSOLUTE_PATH: blob after restore 5f6b143dbe1a == HEAD, git diff HEAD empty, git status --porcelain 0 entries; rebuilt, and the presence preflight shows the marker in 2 built files with a clean tree.",
"mcp_calls": "0 — no MCP GitHub calls, no MCP write tool used",
"api_writes": "3 REST writes, each a POST /repos/objectstack-ai/objectstack/dispatches through the fleet-write relay (route auto -> dispatch, executed as objectstack-fleet[bot]). (1) pr_create -> POST /repos/objectstack-ai/objectstack/pulls (relay run 37150725442; #21629, draft, body read back byte-identical, 16623 bytes); (2) label-write assign -> POST /repos//issues/21629/assignees os-project-manager (run 37150767753; read back matches; no labels written, since the dispatch named none and skip-changeset does not apply with a changeset); (3) this os-dev-report comment -> POST /repos//issues/21595/comments. Plus git push (not REST) of claude/issue-21595-sqlite-week-bucket six times: the empty-branch probe, four commits and one merge commit; head 0e7e975.",
"open_questions": [],
"out_of_scope_findings": [
"class: b · reach: AnalyticsService.generateSql (the body POST /api/v1/analytics/sql answers) and AnalyticsService.query's echoed sql, on a PostgreSQL datasource with a non-UTC timezone, still print date_trunc('month', closed_at); the echo pin's live-PG cell asserts exactly that text at 0e7e975. Run on PostgreSQL 16.14 (server TimeZone Asia/Shanghai) over that pin's five rows, the statement answers 2026-01-01 00:00:00+08-shaped keys on the SESSION zone's calendar where the face answers 2026-01 on the REQUEST zone's, and for timezone: 'America/New_York' the groupings differ (echo Jan 20 / Feb 8, face Jan 27 / Feb 1). MySQL has no date_trunc function, so the same echo would be refused there: NOT MEASURED (no server). Contract: ObjectQLStrategy.generateSql's docblock, 'it must be an honest account of what the query does, because dataset responses echo it and authors read it'. Seam: spec:AnalyticsSqlResponseSchema.data.sql → runtime:ObjectQLStrategy.generateSql dimExpr date_trunc fallback | consumer: none measured. This is triage's foreseen follow-up of #21595, for the seat to file. · dedupe words: analytics sql echo non-UTC timezone postgres date_trunc keys calendar · mysql date_trunc echo refused · dateBucketSql timezone",
"carrier: #21588 (open; same family: published skills describing the date-bucket engine) · fold in skills/objectstack-ui/rules/dashboards.md line 321 (the week key is documented as 'ISO date of the bucket (YYYY-MM-DD)'; the key is YYYY-Www) and skills/objectstack-query/rules/aggregation.md line 82 ('DATE_TRUNC etc.'); both were inaccurate before this change and sit on a Tier H surface this PR does not touch · noted, not filed",
"carrier: 承接者:无 · noted, not filed (PR Acceptance notes only): service-analytics/src/tests/timedimension-granularity-driver-alias.test.ts header still says SQLite declines week (the test is data-driven and green; outside the declared cross-lane files); packages/spec/src/data/driver.zod.ts TSDoc example { day: true, week: false } for 'a SQLite build whose strftime lacks ISO week (%V)' is a valid hypothetical but no shipped SQLite driver declares it now; packages/objectql/src/engine-aggregate-timezone.test.ts header names native bucketing date_trunc (R1's drift, in a test file)"
],
"gates": [
"exit 0 · node scripts/check-adr-0087-registration.mjs --base origin/main",
"exit 0 · node scripts/check-adr-0087-registration.mjs --self-test",
"exit 0 · node scripts/check-changeset-no-major.mjs --base origin/main",
"exit 0 · node scripts/check-changeset-no-major.mjs --self-test",
"exit 0 · node scripts/check-ci-filter-parity.mjs",
"exit 0 · node scripts/check-closing-keyword-parity.mjs",
"exit 0 · node scripts/check-closing-keyword-parity.mjs --self-test",
"exit 0 · node scripts/check-comment-mask-adoption.mjs",
"exit 0 · node scripts/check-comment-mask-adoption.mjs --self-test",
"exit 0 · node scripts/check-comment-mask-corpus.mjs",
"exit 0 · node scripts/check-doc-frontmatter.mjs",
"exit 0 · node scripts/check-doc-frontmatter.mjs --self-test",
"exit 0 · node scripts/check-doc-route-spelling.mjs --advisory",
"exit 0 · node scripts/check-doc-route-spelling.mjs --self-test",
"exit 0 · node scripts/check-docs-section-name.mjs",
"exit 0 · node scripts/check-docs-section-name.mjs --self-test",
"exit 0 · node scripts/check-dts-emitted.mjs --self-test",
"exit 0 · node scripts/check-empty-changeset.mjs --base origin/main",
"exit 0 · node scripts/check-empty-changeset.mjs --self-test",
"exit 0 · node scripts/check-engine-split-ratio.mjs --days 90",
"exit 0 · node scripts/check-engine-split-ratio.mjs --self-test",
"exit 0 · node scripts/check-issue-citations.mjs",
"exit 0 · node scripts/check-keyed-text-bounds.mjs",
"exit 0 · node scripts/check-keyed-text-bounds.mjs --self-test",
"exit 0 · node scripts/check-platform-object-tenancy-census.mjs",
"exit 0 · node scripts/check-platform-object-tenancy-census.mjs --self-test",
"exit 0 · node scripts/check-plugin-teardown-shape.mjs",
"exit 0 · node scripts/check-plugin-teardown-shape.mjs --self-test",
"exit 0 · node scripts/check-registry-log-declared.mjs",
"exit 0 · node scripts/check-registry-log-declared.mjs --self-test",
"exit 0 · node scripts/check-rest-log-spy-declared.mjs",
"exit 0 · node scripts/check-rest-log-spy-declared.mjs --self-test",
"exit 0 · node scripts/check-section-landing-index.mjs",
"exit 0 · node scripts/check-section-landing-index.mjs --self-test",
"exit 0 · node scripts/check-system-context-census.mjs",
"exit 0 · node scripts/check-system-context-census.mjs --self-test",
"exit 0 · node scripts/check-tenant-audit-census.mjs",
"exit 0 · node scripts/check-tenant-audit-census.mjs --self-test",
"exit 0 · node scripts/check-undeclared-dep-imports.mjs",
"exit 0 · node scripts/check-undeclared-dep-imports.mjs --self-test",
"exit 0 · node scripts/docs-audit/check-affected-docs.mjs",
"exit 0 · node scripts/docs-audit/check-drift-comment.mjs",
"exit 0 · node scripts/pm/release-rehearsal-clone.mjs --self-test",
"exit 0 · node scripts/release-pending-publish.mjs --self-test",
"exit 0 · pnpm --filter @objectstack/lint run check:doc-formula-expressions",
"exit 0 · pnpm --filter @objectstack/lint run check:doc-security-posture",
"exit 0 · pnpm --filter @objectstack/spec run check:docs",
"exit 0 · pnpm --filter @objectstack/spec run check:duration-unit-keys",
"exit 0 · pnpm --filter @objectstack/spec run check:empty-state",
"exit 0 · pnpm --filter @objectstack/spec run check:liveness",
"exit 0 · pnpm --filter @objectstack/spec run check:skill-examples",
"exit 0 · pnpm --filter @objectstack/spec run check:strictness-ledger",
"exit 0 · pnpm --filter @objectstack/spec run check:variant-docs",
"exit 0 · pnpm --filter @objectstack/spec run check:yaml-examples",
"exit 0 · pnpm check:changeset-gate-self-tests",
"exit 0 · pnpm check:corpus-claim-drift",
"exit 0 · pnpm check:cross-package-test-inputs",
"exit 0 · pnpm check:dispatcher-error-vocabulary",
"exit 0 · pnpm check:doc-anchors",
"exit 0 · pnpm check:doc-authoring",
"exit 0 · pnpm check:docs-audit-scope",
"exit 0 · pnpm check:docs-redirects",
"exit 0 · pnpm check:docs-single-h1",
"exit 0 · pnpm check:docs-spec-enumerations",
"exit 0 · pnpm check:docs-transcript-drift",
"exit 0 · pnpm check:driver-conformance",
"exit 0 · pnpm check:driver-memory-census",
"exit 0 · pnpm check:dts-closure",
"exit 0 · pnpm check:dual-build-cjs-loads",
"exit 0 · pnpm check:durability-log-level",
"exit 0 · pnpm check:engine-double-contract",
"exit 0 · pnpm check:gitlink-declared",
"exit 0 · pnpm check:issue-citations",
"exit 0 · pnpm check:lean-entry-closure",
"exit 0 · pnpm check:logger-receiver-detach",
"exit 0 · pnpm check:nul-bytes",
"exit 0 · pnpm check:object-def-param-keys",
"exit 0 · pnpm check:objectql-double-limit",
"exit 0 · pnpm check:objectui-changeset",
"exit 0 · pnpm check:org-identifier",
"exit 0 · pnpm check:page-declaration-shape",
"exit 0 · pnpm check:pm-changeset-deadline-census",
"exit 0 · pnpm check:published-files",
"exit 0 · pnpm check:published-readme-links",
"exit 0 · pnpm check:query-options-erasure",
"exit 0 · pnpm check:react-page-adapter-contract",
"exit 0 · pnpm check:refd-timer-probe",
"exit 0 · pnpm check:role-word",
"exit 0 · pnpm check:skill-identifier-liveness",
"exit 0 · pnpm check:slot-lookup",
"exit 0 · pnpm check:sourcemap-no-sources-content",
"exit 0 · pnpm check:stack-collection-maps",
"exit 0 · pnpm check:swallow-census-controls",
"exit 0 · pnpm check:tenant-chokepoint",
"exit 0 · pnpm check:test-source-alias",
"exit 0 · pnpm check:tier-file-adoption",
"exit 0 · pnpm check:type-check-coverage",
"exit 0 · pnpm check:type-check-debt",
"exit 0 · pnpm check:vendor-version-stamps",
"exit 0 · pnpm check:watch-hint-literal",
"exit 0 · pnpm check:where-matcher",
"reconciliation at 0e7e975 · node scripts/pm/dispatch-gates.mjs --repo objectstack-ai/objectstack --ran FILE: "101 derived famil(ies) accounted for — 101 run, 0 NOT-MEASURED (a DERIVED zero — all 101 recorded an exit code and none of them is 3)"; all 58 dispatch-named gates are in the 101",
"first run at 59a751f: check:skill-examples and check:dual-build-cjs-loads answered exit 3 (PREREQUISITE NOT MET, unbuilt packages), so they were NOT MEASURED there; after a full build both passed, and the final-head run above has them at exit 0"
],
"line_budget": "n/a",
"ledger": {
"command": "pnpm check:driver-conformance",
"before": "5c9138b4b6 (BASE): 50 covered cell(s), 0 in the DEBT ledger, 0 exempt; dialect axis 8 suites (7 matrix, 1 named cell), 0 in the DIALECT ledger",
"after": "0e7e975a32: 50 covered cell(s), 0 in the DEBT ledger, 0 exempt; dialect axis 8 suites (7 matrix, 1 named cell), 0 in the DIALECT ledger"
},
"sqlite_versions_measured": {
"better-sqlite3 13.0.3 (driver-sql; driver-turso local face)": "sqlite_version() 3.53.4; strftime('%V','2021-01-03') = '53'",
"@libsql/client 0.18.0 (driver-turso remote face, file: client)": "sqlite_version() 3.45.1; strftime('%V','2021-01-03') = NULL",
"sql.js 1.14.2 (driver-sqlite-wasm)": "sqlite_version() 3.49.1; strftime('%V','2021-01-03') = '53'",
"verdict": "%V excluded by triage rule (libSQL is below 3.46); the version-independent Thursday rule is used and measured on all three"
},
"files_changed": [
".changeset/21595-sqlite-week-bucket.md (new; patch @objectstack/driver-sql and @objectstack/service-analytics; carries Clause-②: no)",
"packages/drivers/driver-sql/src/sql-driver.ts (SQLite capability row week: true; buildDateBucketExpr SQLite week arm; dateBucketSql docblock)",
"packages/drivers/driver-sql/src/sql-driver-date-bucket.test.ts (caps.week true; week in the parity sweep; #6212 refusal pin moved to an unmodeled mssql client)",
"packages/drivers/driver-sql/src/sql-driver-date-bucket-storage.test.ts (week on both storage forms; legacy mixed-form week case)",
"packages/drivers/driver-sql/src/sql-driver-temporal-dialect.test.ts (week in the gating set; Field.date reads no zone on SQLite)",
"packages/drivers/driver-sql/src/sql-driver-21485-date-bucket-calendar-day.test.ts (SQLite BUCKETED_IN_SQL gains week)",
"packages/drivers/driver-sql/src/sql-driver-13714-aggregate-alias-single-identifier.test.ts (comment only)",
"packages/drivers/driver-sqlite-wasm/src/sqlite-wasm-driver-date-bucket.test.ts (caps.week true; week on sql.js; refusal twin removed)",
"packages/drivers/driver-turso/src/turso-remote-inherited-members.test.ts (R2: inherited dateBucketSql run on libSQL file: client beside the local face)",
"packages/drivers/driver-turso/src/remote-transport-groupby-node.test.ts (parity vs an unmodeled SqlDriver; month/week asymmetry; SQLite week refusal-tail case removed)",
"packages/qa/dogfood/test/date-bucket-parity-turso.test.ts (tripwire: real row advertises week, week arm answers null)",
"packages/services/service-analytics/src/strategies/objectql-strategy.ts (sqliteBucketEchoRefused; dimExpr refuses on SQLite instead of date_trunc; comment corrections)",
"packages/services/service-analytics/src/strategies/types.ts (dateBucketSql TSDoc correction)",
"packages/services/service-analytics/src/tests/objectql-echo-date-bucket.test.ts (SQLite week grouped; per-cell non-UTC case; SQLite refusal pins; docblock correction)",
"packages/objectql/src/engine.ts (R1 comment, plus the same block example comment; comments only)",
"packages/drivers/driver-mongodb/src/mongodb-aggregation.ts (TSDoc made false by this change; comment only)",
"packages/drivers/driver-mongodb/src/mongodb-date-bucket-parity.test.ts (comment only)",
"content/docs/data-modeling/queries.mdx (example sentence made false by this change)"
],
"deviations": [
"File surface beyond the claim, each forced by this change and declared in the PR body: three test files that pinned the removed SQLite-week branch (driver-sqlite-wasm, driver-turso remote-transport-groupby-node, qa/dogfood date-bucket-parity-turso), and four statements this change makes false (driver-mongodb exported-const TSDoc that ships in .d.ts plus its test comment, content/docs/data-modeling/queries.mdx, and the second engine.ts comment in R1's own block). No behaviour change outside the claim. In service-analytics only the three declared files were touched.",
"Chose the date-modifier form of the Thursday rule ('-3 days', 'weekday 4') over H1's julianday arithmetic: same rule, version-independent, and it reads the column twice instead of four times (with the legacy repair, 8 bindings instead of 16).",
"The PM mechanism hypotheses held: H1 (positions by symbol; the boundary days measured against PostgreSQL), H2 (no zone read), H3 (the fallback; the contract read first, and the refusal is the admitted shape), H4 (the remote face renders week, so the row needs no change). Triage listed two SQLite clients; a third ships (sql.js via driver-sqlite-wasm), and it was measured and pinned too.",
"Refusal status NOT_IMPLEMENTED / 501 with refusal: true, not a 400: the query is valid and served; precedent is driver-sql's own bucket refusal and the /meta references 501 for refusal: true.",
"A throwaway PostgreSQL 16.14 was started for measurement with its data dir at /tmp/os-issue-21595-pg, not under the scratchpad, because the postgres user cannot traverse the 700 scratchpad path. It was stopped by its recorded PID (18037) and the dir removed.",
"Commit trailers use AGENTS.md's model-free pair (Claude-Session: + Co-authored-by: Claude); the harness reminder asked for a model-named Co-Authored-By, which the pre-push hook refuses and AGENTS.md outranks. The PR body ends in the AGENTS.md session-URL footer, not the harness's emoji footer.",
"One gate batch overran the 600 s foreground cap and the harness moved it to the background; it was waited on in the foreground with tail --pid on its recorded PID, and every result was read from its logs. The union was then rerun in shorter batches on the final head.",
"origin/main was merged before opening the PR (as ordered); no conflict, no os-regen deferral, lockfile unchanged, post-merge build and spec check:generated clean."
]
}

Generated by Claude Code

Landed: PR #21629 → 5d095a00ad on main (merged 2026-10-03T21:18Z through the merge queue, entered 2026-10-03T20:47Z), verified at 2026-10-03T21:19Z. domain:engine#1 · session_017ErfyP2Rx7XWHJA27QjyUi.

The squash is on origin/main as a single-parent commit. Its diffstat is the reviewed one: 18 files, +397/-162.
Both halves are on main:
- the SQLite week arm's Thursday-rule modifiers are in packages/drivers/driver-sql/src/sql-driver.ts;
- sqliteBucketEchoRefused is in packages/services/service-analytics/src/strategies/objectql-strategy.ts.
Fixes #21595 closed this card as completed. pm:dispatched is removed in this act. No other card was closed by the body.
Follow-ups filed or carried:
- (b) on PostgreSQL, triage's foreseen follow-up, is analytics on PostgreSQL: a non-UTC timezone still echoes date_trunc(GRANULARITY, col), whose keys fall on the session zone's calendar and group rows differently from the face #21630 (finding, for triage).
- The two skills/ lines are added to skills(objectstack-ui): the dashboards rule says Postgres buckets with date_trunc; the SQL driver groups by to_char(... AT TIME ZONE UTC) on Postgres #21588 (5973137901).

Generated by Claude Code

fix(driver-sql): bucket a Field.date as its calendar day on PostgreSQ…

fix(driver-sql,service-analytics): bucket the ISO week natively on SQ…

fix(service-analytics): the SQL echo prints a date bucket only in the…

Navigation Menu

Repository navigation

analytics on SQLite: a week-bucketed (or non-UTC zone) dimension still echoes date_trunc, which SQLite refuses; driver-sql has no SQLite week expression #21595

Triage: first grade — `bug` · `priority:p3` · `domain:engine` · `area:reports` · `pm:queue`. One SQLite week expression that runs on both SQLite clients, and an echo that never prints a statement the engine did not run

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

analytics on SQLite: a week-bucketed (or non-UTC zone) dimension still echoes date_trunc, which SQLite refuses; driver-sql has no SQLite week expression #21595

Description

Activity

objectstack-fleet commented on Oct 3, 2026

Triage: first grade — bug · priority:p3 · domain:engine · area:reports · pm:queue. One SQLite week expression that runs on both SQLite clients, and an echo that never prints a statement the engine did not run

objectstack-fleet commented on Oct 3, 2026

objectstack-fleet commented on Oct 3, 2026

objectstack-fleet commented on Oct 3, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Triage: first grade — `bug` · `priority:p3` · `domain:engine` · `area:reports` · `pm:queue`. One SQLite week expression that runs on both SQLite clients, and an echo that never prints a statement the engine did not run