fix(v2): give the keyset cursor's timestamp an explicit SQL type - #6636

The defect

Every v2 collection hands back a nextCursor. Passing that cursor straight back returned 500 INTERNAL_ERROR on every list whose sort includes a timestamp key — which is the default sort on five of them.

The keyset compares millisecond-truncated timestamps on both sides:

date_trunc('milliseconds', "created_at") > date_trunc('milliseconds', $n)

$n went out as a bare placeholder, which Postgres types as unknown. date_trunc is overloaded across timestamp, timestamptz, and interval, so date_trunc(unknown, unknown) matches no single candidate and the statement fails with function date_trunc(unknown, unknown) is not unique.

The cursor value was never unvalidated — a non-string or unparseable date is already rejected as a 400 by KeysetKey.bind. It simply reached SQL with no type. The old TSDoc claimed sql.param(date, column) bound it "through the column so drizzle's own timestamp encoder serializes it"; the encoder does run, but drizzle still emits an untyped $n, and nothing about a placeholder tells Postgres what it is. The file's own unit test asserted the untyped form and passed.

Not a regression from the recent v2 work

git log -S"date_trunc" -- apps/sim/lib/api/list-query.ts, and the same for sql.param(date, column), both point at a single commit: #5273, the original v2 PR. v2 keyset pagination has never worked past page one on a timestamp-sorted list. #6620 made it reachable and uniform — it gave four previously-unpaginated collections working cursors — which is how it surfaced, but it did not introduce it.

The fix

Cast the bound value to the column's own SQL type, inside timestampKey, so all twelve call sites across six modules inherit it rather than each patching itself:

sql`date_trunc('milliseconds', cast(${sql.param(date, column)} as ${sql.raw(column.getSQLType())}))`

Derived from the column rather than hardcoded timestamp: every column reaching this today is timestamp without time zone, but a timestamptz column would need its Z offset honoured rather than ignored, and the derivation keeps that correct for free. It is read inside bind rather than at construction because every caller builds its sort map at module scope.

The millisecond truncation is unchanged. It is load-bearing: Postgres stores microseconds, a cursor value round-trips through a millisecond-only JS Date, and comparing the raw column against the truncated value re-admits the page's own last row. Verified against a real database that a row stored at …568999 is still correctly excluded by a cursor stamped …568.

Affected sorts

Every keyset containing a timestamp key — 7 collections, 15 sorts. Default sorts in bold.

Not affected at all: logs, skills, billing-logs, mcp-servers, audit-logs — none of them build a keyset over a truncated timestamp.

numberKey and textKey carry no equivalent exposure. Their bound values only ever sit as one side of a comparison against a typed column or expression (sort_order > $1, coalesce(size_bytes, size) > $1), where Postgres infers the type from the comparison. date_trunc is the only place a keyset passes a bound value into a function — which is exactly why this stayed invisible everywhere else.

I swept every sql.param( call site and every SQL-function call with an interpolated operand across apps/ and packages/. The others either already carry an explicit cast by hand (::jsonb, ::text, ::vector — seven sites, each author having hit the same wall independently) or are single-candidate and resolve fine (websearch_to_tsquery(regconfig, text), lower, make_interval). This was the only live instance.

Verification

• Reproduced against a real Postgres 17.9 before the fix, rendering the actual keysetAfter SQL through PgDialect and executing it: 4 of 5 sort shapes failed with function date_trunc(unknown, unknown) is not unique; the name (text-key) control passed. After the fix, 5/5 pass and return the correct rows, including the microsecond-truncation case above.
• New unit tests fail before the fix and pass after. Three added/changed: the cast is emitted; the cast is taken from the column (a timestamptz fixture renders cast($1 as timestamp with time zone)); and a class guard asserting no bound value is left bare inside any function call, so a future key that wraps its value is caught.
• bun run type-check, bunx turbo run lint:check, bun run check:api-validation:strict, bun run check:openapi all pass. lib/api suites: 723 tests green; the six modules that call timestampKey plus their dependents: 4,470 green.

Skill

.agents/skills/v2-api-conventions/SKILL.md states "500 is never caller-reachable" and keeps an incident log against it. This class is added, along with the generalisation it produces — if a bound value is an argument to a SQL function rather than one side of a comparison, write its type down — and the reason it survived review: the generated SQL was well-formed and every assertion passed, so only a database could reject it. Projections regenerated with scripts/sync-skills.ts.

Note for a follow-up (not in this PR)

lib/data-drains/sources/cursor.ts (timeCursorPredicate) applies date_trunc('milliseconds', …) to the column but not to the bound cursor value. It does not hit this bug — a row-constructor comparison types the parameter from the left-hand element — but that asymmetry is the same one timestampKey's TSDoc argues re-admits the page's own last row. Worth a look separately.