fix(service-queue,platform-objects): the claim path's sort keys join the declared index, and due-ness becomes a SQL predicate - #18105

Part of #17612

Clause-②: no

sys_job_queue's claim path sorted by a key no declared index carried, and decided due-ness after LIMIT had already chosen the rows. Both are fixed; the fork the card wrote is decided below with its costs measured, not asserted.

Premise, re-verified on origin/main

Both readings the card carries still hold, checked at the merge base a26a114d7:

packages/platform-objects/src/audit/sys-job-queue.object.ts
  indexes: [ ['queue','status','scheduled_for'], ['idempotency_key','queue'], ['status'] ]
  ⇒ `priority` appears in NO declared index

packages/services/service-queue/src/db-queue-adapter.ts
  orderBy: [ { field: 'priority', order: 'asc' }, { field: 'scheduled_for', order: 'asc' } ]
  ⇒ the sort's FIRST key is the unindexed one

One thing the card could not have known, and it changes the acceptance criterion. The claim query the planner sees is not the one the adapter writes. SqlDriver.orderKeysFor appends the unique tie-breaker of the deterministic-paging contract (ADR-0053 D-A1 / objectstack#4363) to every paged read, so the real ORDER BY ends …, "id" ASC:

SELECT * FROM "sys_job_queue" WHERE "queue" = ? AND "status" = ?
  ORDER BY "priority" ASC, "scheduled_for" ASC, "id" ASC LIMIT ?

That term is in no index either, and no platform object in this repo bounds its id — which puts the card's acceptance 4 ("EXPLAIN QUERY PLAN no longer shows USE TEMP B-TREE FOR ORDER BY") one step further away than it reads. The statement pinned in the new test is captured from the driver, never retyped, precisely so this term stays visible.

The fork, measured on the real tree

The matrix below is EXPLAIN QUERY PLAN of the driver-emitted claim statement, run through a real TursoDriver on both faces (libsql:// over file::memory: and local :memory:), one index set per row.

Option 1 taken — priority joins a declared index. Three reasons, any one sufficient:

1. Option 2 is not the cheaper half of the fork, it is a strictly worse trade. Rows four and five say so: dropping priority from the sort does not avoid touching the index — with the index left alone it lands on exactly the same partial sorter option 1 reaches, and to do better it needs its own four-column index. So the choice is not "widen the index" against "change behaviour"; it is "widen the index" against "change behaviour and widen a different index".
2. priority is a declared, documented, authorable field whose description is Lower = higher priority, and the claim sort is its only runtime effect. Dropping it leaves the field inert — a capability the runtime advertises and does not deliver, which Prime Directive 10 names directly.
3. It is a behaviour change to every deployment that currently sets priority, taken to avoid a cost measured below at one index column.

What option 1 costs, measured. One index, widened from three key columns to four, on one table. The table still declares three indexes: the new one REPLACES ['queue','status','scheduled_for'] rather than joining it, which is safe because the equality prefix queue, status is unchanged (getQueueSize and purge keep the identical seek) and no reader in this repo uses scheduled_for as an index RANGE. Write cost is one extra key column per row insert/update on sys_job_queue, not an extra index.

Why the sorter is not closed all the way. Row three of the matrix is reachable and it is the shape I first delivered. pnpm check:keyed-text-bounds rejected it, and it is right to:

✗ check:keyed-text-bounds: 1 unbounded keyed text-family column(s)
  • sys_job_queue.id  [text]  no maxLength
A text-family column a declared index keys on must declare a `maxLength` (route A, #11374).
Without one `driver-sql` emits it TEXT; MySQL then refuses `ALTER TABLE ... ADD INDEX` with
ER_BLOB_KEY_WITHOUT_LENGTH, and the object lands REGISTERED-BUT-BROKEN with its declared index
silently absent (measured live on MySQL 8.0.46, #12058).

That gate's allowlist is empty and 147 of 147 keyed text columns are bounded — mine would have been the repo's only exception, and the failure mode is the index vanishing on MySQL, which is the very defect this card is about. Bounding a primary key's column type on provisioned tables is its own piece of work. So the delivery stops at four columns, the declaration says why in as many words, and the residual sorter is bounded to rows tying on the whole indexed prefix rather than covering the queue.

Head-of-line starvation (ruled into this card in its comments)

The due bound was applied in JS to the rows SQL had already chosen:

limit: max * 3,
orderBy: [{ field: 'priority', order: 'asc' }, …],
…
if (sched > now.getTime()) continue;   // ← after LIMIT

So a candidate window full of not-yet-due high-priority rows hid already-due work behind it — not for one tick, indefinitely. It now reads $or: [{ scheduled_for: null }, { scheduled_for: { $lte: now } }] in the where, the same shape SqlOutboxStore.claim uses; null needs its own leg because a NULL column never satisfies a less-than-or-equal comparison in SQL — the comparison answers NULL, never true.

Measured on this package's own fake engine at the default batchSize: 10 (candidate window 30):

The second row is the control: it read 1 before the change too, which is what makes the first row a measurement rather than a coincidence.

Reverse verification

Every leg: commit first, mutate on disk, prove the mutation landed by occurrence count, rebuild where the consumer resolves through dist/, run, restore with git checkout HEAD -- path, prove the blob hash returns and the whole tree is clean. trap … EXIT INT TERM on absolute paths throughout.

⚠️ The first attempt at leg 1 was a no-op and is reported rather than quietly re-run. Its --absent marker was the source spelling 'queue', 'status', … with single quotes; esbuild emits double quotes, so the marker was never in dist/ and the pre-flight passed vacuously. scripts/ablation-dist-preflight.mjs caught it on the next leg by failing its present-check. Both legs were redone with the marker that is actually in the built bytes, and a sanity leg now asserts the fixed dist/ carries it before anything is mutated.

Why that failing set is the right one. Leg 1 reddens exactly one test, the guard that reads the SHIPPED declaration against the claim the adapter actually emits — nothing else in service-queue depends on the index, which is precisely the drift that let this defect exist. Leg 2 reddens the two pins about LIMIT's input set and nothing about ordering, because the predicate changes which rows are candidates and not their order. Leg 3 reddens the two pre-existing #17609 index-set pins plus the new plan pin, because the fixture is the input to all three.

Restores (whole-tree git status --porcelain empty after each):

sys-job-queue.object.ts                      -> 944483cb… (HEAD 944483cb…)
db-queue-adapter.ts                          -> cb8ef2f6… (HEAD cb8ef2f6…)
turso-local-remote-declared-index-parity.ts  -> 82c2c695… (HEAD 82c2c695…)

Clause-2 re-derivation, from the delivered diff

The claim predicted no. Re-derived: no — no new exported symbol is reachable from a published entry, and no new key lands on an already-published payload. The indexes key already existed on SysJobQueue; only its value changed. Measured, not reasoned:

head surface: platform-objects 98 names · service-queue 15 names
base surface: platform-objects 98 names · service-queue 15 names   (rebuilt at a26a114d7)
DIFF platform-objects (base -> head): identical export surface
DIFF service-queue    (base -> head): identical export surface

POSITIVE  SysJobQueue    in platform-objects dist/*.d.ts : 1 hit
POSITIVE  DbQueueAdapter in service-queue    dist/*.d.ts : 8 hits
NEGATIVE  JOB_QUEUE_PRE_17612  across both dist trees    : 0 hits
NEGATIVE  PAGING_TIE_BREAKER   across both dist trees    : 0 hits
NEGATIVE  capturedJobClaim     across both dist trees    : 0 hits
NEGATIVE  JOB_CLAIM_INDEX      across both dist trees    : 0 hits

control — the surface diff CAN report:  2a3  > C

The four negatives are identifiers this diff introduces; all four are test-local and none reaches a published artifact. The changeset is graded patch accordingly.

Gates — with the denominator

Derived from the change set by node scripts/pm/dispatch-gates.mjs --commands --repo objectstack-ai/objectstack at d9e2830ba (6 paths vs merge base, three-dot):

The two NOT MEASURED both exit 3, the "PREREQUISITE NOT MET" code both scripts define as explicitly not a pass and not a finding:

• pnpm check:dual-build-cjs-loads — no packages/adapters/hono/dist … and 47 more. Needs a whole-repo pnpm build.
• pnpm check:type-check-debt — Build the closure first, exactly as lint.yml does. Same.

pnpm check:i18n was a third until its named closure was built; it then read OK (9 packages — all bundles in sync, no undeclared authoring keys) and is counted in the 63. pnpm check:keyed-text-bounds is the gate that redirected the fix, and now reads 147 keyed text-family columns judged, 147 bounded. Allowlist: 0 pending, 0 unboundable.

Package-level, at 533e41385:

pnpm --filter @objectstack/service-queue test                     70 passed (70)   [64 before]
pnpm --filter @objectstack/driver-turso test                    1249 passed (1249)
pnpm --filter @objectstack/platform-objects test                 575 passed (575)
pnpm --filter {those three} typecheck                            exit 0
eslint . --no-inline-config --format json                6746 files, 0 errors, 0 warnings

The lint number is the full repo run, not a narrowing — it completed inside the foreground budget, so no scope claim is needed for it.

Operational note for an existing database

The retrofit adds idx_sys_job_queue_queue_status_priority_scheduled_for and does not drop the superseded idx_sys_job_queue_queue_status_scheduled_for (measured: 3 indexes before, 4 after, the row untouched). A provisioned table therefore carries one redundant index until an operator drops it through the migrate-plan path; a freshly created table gets three.

Acceptance notes

• Card scope item 3 (idle polling backoff) is NOT delivered here, deliberately. The card says to reuse service-messaging: NotificationDispatcher issues 32 statements per tick on an EMPTY outbox (reap runs per partition, twice) — idle cost scales with partitions × table size and never backs off #17610's mechanism rather than write a second one. That mechanism landed as DispatchLoop in packages/services/service-messaging/src/dispatch-loop.ts (PR fix(service-messaging): reap once per dispatcher tick, back off while idle, wake on emit #17622) and it is not exported from that package's index.ts, so reusing it means publishing a new symbol from service-messaging and adding a service-queue → service-messaging dependency — a queue service depending on a messaging service. That is an architecture decision this card does not authorize, and writing a second copy is what its own scope item prohibits. Named here for the routing seat; the per-tick cost this card was filed about is gone either way.
• The two Turso faces run different SQLite builds, so EXPLAIN QUERY PLAN renders the same partial-sort plan as RIGHT PART OF ORDER BY (remote, @libsql/client) and LAST TERM OF ORDER BY (local, better-sqlite3). The existing delivery-claim pin compares the two faces by EXPLAIN text equality and passes only because its plan happens to have no sorter line; the new job-claim pin compares by index and by sort class instead, and says why in the file. Noted, not filed — no PR or person is heading for that file with a partial-sort plan.
• countingClient in the parity test now records bound args beside the statement text, additively; the existing pins read .sql and are untouched.
• No row was added to scripts/engine-double-contract.pinned.json: the two fake engines in service-queue were EXTENDED (both learn $or/$lte/IS NULL; every other $ key stays a loud throw) rather than replaced, so the ledger stays at 1493 and no contender needs sequencing.

Generated by Claude Code

Generated by Claude Code