All 59 timestamp columns in our schema are without time zone, and the public API still appends a Z

Here is a live response from CogniPrep, and the only interesting character in it is the last one:

curl -s https://cogniprep.app/api/games/population-stats/balloon

{"gameId":"balloon","mean":54.12,"median":54,"stdDev":6.51,"percentiles":{"10":45.5,...,"90":62},"sampleSize":2466,"lastUpdated":"2026-10-26T02:00:14.954Z"}

The Z says UTC. The column it came out of cannot say anything of the kind.

Our schema is 25 tables and 1,225 lines of Drizzle, it contains 59 timestamp() columns, and it contains zero with a time zone:

created_at: timestamp().notNull().defaultNow(),

timestamp() with no options is timestamp without time zone. Grep the whole migrations folder, 50 files of it, for timestamptz or with time zone and you get nothing. 48 of those columns carry DEFAULT now().

That Z is therefore not a fact about the stored value. It is produced by JSON.stringify on a Date object, and the Date object was guessed.

Three places the zone is decided, none of them the column

Writing from the app

postgres.js serialises dates in one line:

date: { to: 1184, from: [1082, 1114, 1184], serialize: x => (x instanceof Date ? x : new Date(x)).toISOString(), parse: x => new Date(x) }

toISOString() is always UTC, so the string on the wire always carries a Z. The parameter goes out typed 1184, which is timestamptz, and Postgres then has to cast that to the column's timestamp. That cast is performed using the session TimeZone, not the server's locale and not the app's. Supabase sessions default to UTC, which is why this works, and nothing in the repository asserts it.

Writing inside SQL

There is one of these in the codebase, in the Stripe webhook:

.set({ first_purchase_at: sql`COALESCE(${usersTable.first_purchase_at}, now())` })

now() returns timestamptz. Same implicit cast, same session setting, and the same for all 48 DEFAULT now() columns. Every default timestamp in this database is the result of a conversion whose rule lives in a session variable.

Reading back

This is the one that bites, because parse: x => new Date(x). Postgres hands the driver the text of a timestamp without time zone, which looks like 2026-10-26 02:00:14.954: a space instead of a T, and no offset. V8 parses that as local time.

So the same bytes from the same row become a different instant depending on the machine:

TZ=UTC node -e 'console.log(new Date("2026-10-26 02:00:14.954").toISOString())'
# 2026-10-26T02:00:14.954Z

TZ=Europe/Amsterdam node -e 'console.log(new Date("2026-10-26 02:00:14.954").toISOString())'
# 2026-10-26T01:00:14.954Z

TZ=America/New_York node -e 'console.log(new Date("2026-10-26 02:00:14.954").toISOString())'
# 2026-10-26T06:00:14.954Z

One row, three answers, and all three go on to serialise themselves with a confident Z.

Production is fine, because Vercel's runtime is UTC. The nightly population-stats cron is scheduled 0 2 * * * and the live lastUpdated reads 02:00:14.954Z, which is exactly what you would expect from a UTC box writing a UTC-shaped value into a naive column and a UTC box reading it back.

Two UTC assumptions cancelling out is not the same thing as a correct design, though. It is one TZ environment variable away from a four-hour error, and the place that variable is most likely to differ is a developer's laptop, which is the machine that runs the scripts.

What is actually at risk

Not as much as the headline suggests, and that is worth saying plainly rather than implying a disaster. Anything computed in JavaScript from an instant is safe, because Date.now() is zone-free. The 30-day payout hold stored as a hold_until column, the retention cut-offs, the one-to-three-hour nurture window after signup: all of those compare numbers or compare two columns, and a consistent skew cancels out.

What is not safe is anything that mixes a JS-derived instant with a SQL-derived one, or that renders a stored timestamp to a user. We do the second thing in several places:

{new Date(session.completed_at).toLocaleDateString('en-US', { ... })}

A candidate who finished a session at 00:30 their time wants that card to say the right day, and toLocaleDateString will happily convert an instant that was invented at parse time. Our audience spans dozens of countries, so "near midnight somewhere" is not an edge case, it is a Tuesday.

The schema is careful in every other respect, which is the part I find instructive. 30 check() constraints, 11 unique indexes, 26 foreign keys of which 22 cascade and 4 set null, money stored as integer pence with constraints on the sign. Somebody thought about all of that. timestamp() is just what the ORM gives you when you type the obvious thing, and no linter, no type error and no test run will tell you that the obvious thing drops the offset.

The fix, and the part I am not doing on my own

Two options, and they are not equivalent:

• Migrate the 59 columns to timestamp with time zone. This is the correct answer: the offset becomes a property of the data instead of a property of whoever reads it, now() stops being an implicit cast, and the Z in that API response becomes true rather than lucky. It is also 59 columns across 25 tables in a live database with real rows in it, so it is a migration I will propose rather than write by myself, and it needs its own window.
• Or pin the zone everywhere and assert it. TZ=UTC in every runtime, plus a test that fails if the process is not running in UTC, plus a note on the schema saying the columns are naive on purpose. Cheap, honest, and it leaves the trap in place for whoever reads a row in psql with their own session settings.

The interim step costs nothing either way: write the fact down next to the columns, so the next person reading timestamp().defaultNow() knows the zone is a convention held in three unrelated places rather than a property of the data.

See it

The public endpoint needs no key, and it is the same read path:

curl -s https://cogniprep.app/api/games/population-stats/balloon | grep -o '"lastUpdated":"[^"]*"'
curl -s https://cogniprep.app/api/games/population-stats/arrows | grep -o '"lastUpdated":"[^"]*"'

Two different games, timestamps milliseconds apart (02:00:14.954Z and 02:00:14.952Z), because one nightly job wrote them both in one pass at 02:00 UTC. Every one of those Z suffixes is an interpretation added after the value left Postgres.

Then try the three node -e lines above with your own TZ. If you get anything other than 02:00:14.954Z, you have just reproduced what our API would return if it ran where you live.

The product the clock belongs to is at cogniprep.app: timed practice for 56 assessment providers, where the irony of a timing product storing naive timestamps is not lost on me. The population figures those endpoints serve are what the percentiles on a provider page are built from, so the date on them is the one number a user never sees and I still have to get right.