GET /flowsheet/search returns play_date as raw PostgreSQL timestamp text — "2019-03-01 12:00:00+00" — where api.yaml declares it type: string, format: date-time (RFC 3339). The served value is not RFC 3339: it uses a space instead of T, and a two-digit +00 offset instead of +00:00 or Z.
Pre-existing and long-standing; clients cope because Date.parse in V8 is lenient about both deviations. A stricter parser — Swift's ISO8601DateFormatter, Python's datetime.fromisoformat before 3.11, most Go and Rust RFC 3339 parsers — rejects it.
Cause
apps/backend/services/search.service.ts declares the row type as play_date: Date and transformRow branches on it:
play_date: row.play_date instanceof Date ? row.play_date.toISOString() : String(row.play_date ?? ''),
The instanceof Date branch is dead. drizzle-orm's postgres-js driver installs an identity parser over the timestamp OIDs at construction — node_modules/drizzle-orm/postgres-js/driver.cjs:
const transparentParser = (val) => val;
for (const type of ["1184", "1082", "1083", "1114", "1182", "1185", "1115", "1231"]) {
client.options.parsers[type] = transparentParser;
...
}OID 1184 is timestamptz. So a raw db.execute never yields a Date, the String(...) branch always runs, and Postgres's own text rendering reaches the client verbatim.
The Date type annotation is therefore a type lie that has been masking this, and it also means the unit tier — whose mocks supply real Date objects — exercises a shape production never produces.
Scope
The observed instance is /flowsheet/search. The cause is not specific to it: any endpoint that reads a timestamptz through a raw db.execute and serializes it directly has the same behaviour. Part of the work is establishing how wide that is. Endpoints using the drizzle query builder with typed columns are not necessarily affected in the same way; that should be checked rather than assumed.
Desired end state
play_date — and any other format: date-time field served from a raw db.execute — is RFC 3339, matching what api.yaml promises, without a per-call-site new Date(...) round trip that would reintroduce millisecond truncation.
Suggested approach
Render the timestamp in SQL, the way #2346 now does for the cursor:
to_char(add_time AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS play_date
This is exact, avoids the JS Date millisecond floor, and keeps the response contract honest.
Whatever the fix, correct the SearchResultRow.play_date type so it describes what the driver actually returns, and make at least one unit mock supply the real shape — the current mocks supplying Date are why this was invisible.
Constraints
- This is a response-shape change on a live endpoint. Existing consumers parse the current form successfully; the new form is strictly more standard, but the change should be checked against dj-site and any other consumer before shipping.
- Per WXYC/wiki's api.yaml consumer fan-out notes, iOS uses operation-derived types while dj-site uses schema types, so a contract-adjacent change needs both checked.
- Do not fix this by parsing to a JS
Date and calling .toISOString() — Date is millisecond-precision and would silently floor the microseconds that Postgres returns.
Acceptance criteria
Related
GET /flowsheet/searchreturnsplay_dateas raw PostgreSQL timestamp text —"2019-03-01 12:00:00+00"— whereapi.yamldeclares ittype: string, format: date-time(RFC 3339). The served value is not RFC 3339: it uses a space instead ofT, and a two-digit+00offset instead of+00:00orZ.Pre-existing and long-standing; clients cope because
Date.parsein V8 is lenient about both deviations. A stricter parser — Swift'sISO8601DateFormatter, Python'sdatetime.fromisoformatbefore 3.11, most Go and Rust RFC 3339 parsers — rejects it.Cause
apps/backend/services/search.service.tsdeclares the row type asplay_date: DateandtransformRowbranches on it:The
instanceof Datebranch is dead.drizzle-orm's postgres-js driver installs an identity parser over the timestamp OIDs at construction —node_modules/drizzle-orm/postgres-js/driver.cjs:OID 1184 is
timestamptz. So a rawdb.executenever yields aDate, theString(...)branch always runs, and Postgres's own text rendering reaches the client verbatim.The
Datetype annotation is therefore a type lie that has been masking this, and it also means the unit tier — whose mocks supply realDateobjects — exercises a shape production never produces.Scope
The observed instance is
/flowsheet/search. The cause is not specific to it: any endpoint that reads atimestamptzthrough a rawdb.executeand serializes it directly has the same behaviour. Part of the work is establishing how wide that is. Endpoints using the drizzle query builder with typed columns are not necessarily affected in the same way; that should be checked rather than assumed.Desired end state
play_date— and any otherformat: date-timefield served from a rawdb.execute— is RFC 3339, matching whatapi.yamlpromises, without a per-call-sitenew Date(...)round trip that would reintroduce millisecond truncation.Suggested approach
Render the timestamp in SQL, the way #2346 now does for the cursor:
This is exact, avoids the JS
Datemillisecond floor, and keeps the response contract honest.Whatever the fix, correct the
SearchResultRow.play_datetype so it describes what the driver actually returns, and make at least one unit mock supply the real shape — the current mocks supplyingDateare why this was invisible.Constraints
Dateand calling.toISOString()—Dateis millisecond-precision and would silently floor the microseconds that Postgres returns.Acceptance criteria
play_datevalidates againstformat: date-time(RFC 3339)SearchResultRow's type matches the driver's actual return shape, and a unit test exercises that shapetimestamptzthrough rawdb.execute, with tickets or fixes for any that are also non-conformantRelated
instanceof Datebranch was found