queue: no test executes the worker's raw SQL, so a type-resolution error would ship green

[jerelvelarde]

Raised repeatedly during the #224 review rounds, and it is the structural limit of the current approach rather than anything wrong with the tests that exist.

The gap

Every claim and reclaim assertion in queue.test.ts and worker-concurrency.test.ts is a substring match against mock.calls[0][0].join(' ') — the template literal's static fragments. The tests mock prisma, so CI never executes the SQL either.

Unverified anywhere, and all of it is load-bearing:

• gen_random_uuid()::text
• (jsonb ->> "Job".type)::double precision — the per-type timeout lookup
• <param> * INTERVAL '1 millisecond' operator resolution against Prisma's parameter typing
• LEAST(<float8>, $n) with a Prisma-typed integer parameter — a classic "could not determine data type of parameter" shape
• POWER, random(), left(job.error, 200), and the string concatenation on the error column
• type unification across the CASE arms
• UPDATE ... RETURNING "claimToken" semantics
• whether the planner actually uses Job_status_lockUntil_idx — a property asserted as fact in both schema.prisma and the migration, and only provable by EXPLAIN

Why it is worth doing now

A type-resolution error in reclaimStaleJobs fails on the first poll of every replica, with CI fully green — the sweep is the first statement of every poll. That is a fleet-wide failure that no gate we have can catch.

Behavioural gaps that fall out of the same absence:

• The reclaim predicate has no behavioural test. The premise of fix(queue): fence job claims and drain in-flight work on shutdown #224 is "reclaim on the deadline the claiming worker recorded", and no test asserts which rows that predicate actually selects. Wanted: lockUntil = now + 120s is left alone by a worker whose defaultTimeoutMs is 5s; lockUntil = now - 1s is left alone (inside the grace); lockUntil = now - 31s is reclaimed.
• The legacy arm is untested behaviourally — lockUntil IS NULL with lockedAt 20 min ago reclaims, 5 min ago does not.
• The DEAD_LETTER vs PENDING branch is untested — attempts + 1 >= maxAttempts drives three different columns. A row at attempts=4, maxAttempts=5 must land DEAD_LETTER with completedAt set and runAt untouched; at attempts=0, PENDING with a future runAt and completedAt NULL.

Shape

The repo already runs a postgres:18 service in CI. One Postgres-backed test file against a migrated database would cover every item above at once, and queue.test.ts's own header has said integration tests "should be added separately" since it was written — with no owner and no link. This is that link.