Normalising datetime storage: canonical form, where to normalise, and the migration ordering constraint (#2773)

[yet2come]

Opening this before writing any code. #2773 is filed as a bug, but fixing it properly means a behaviour change plus a data migration across every existing install — and one of the pieces looks deliberate rather than accidental, so CONTRIBUTING points here rather than at a PR.

I'm happy to implement whatever slice of this you want. This is a request for direction, not a request for someone else to do the work.

The constraint that shapes the order of everything else

The permissive schema is intentional. packages/core/src/schema/zod-generator.ts:

case "datetime":
	// Accept every value that legitimately round-trips through the admin
	// and seeds: ISO with `Z`, ISO with a timezone offset, a naive
	// datetime (`YYYY-MM-DDTHH:mm[:ss]` -- what `<input type="datetime-local">`
	// and many seeds produce), and a date-only value. The admin re-sends
	// every loaded field on autosave, so a stored naive datetime must
	// validate or the entry becomes unsavable through its own editor
	// (#1368; same class as #867). `z.iso.*` retains semantic validation,
	// so impossible dates are still rejected.
	return z.iso.datetime({ offset: true, local: true }).or(z.iso.date());

So tightening validation first would re-break #1368 on every install that already holds naive values. The migration has to land before any tightening. Any plan that opens with "reject naive input" is wrong in the order, whatever else it gets right.

Proposed canonical form

YYYY-MM-DDTHH:mm:ss.sssZ — UTC, milliseconds always three digits. Exactly Date.prototype.toISOString().

The fixed millisecond width is the part that is easy to drop: 2026-08-22T01:00:00Z and 2026-08-22T01:00:00.000Z are the same instant, but . sorts before Z, so a column of mixed width still does not order by time even when every value is UTC.

Where normalisation has to happen

Not in the validation layer alone. packages/core/src/api/handlers/validation.ts:

type ValidationResult =
	| { ok: true }
	| { ok: false; error: { code: "VALIDATION_ERROR" | "COLLECTION_NOT_FOUND"; message: string } };

schema.safeParse(data) is used only to collect issues — parsed.data is discarded and the caller passes the pre-validation data onward. Seeding does not reach this layer at all; it calls ContentRepository.create/update directly.

serializeValue in packages/core/src/database/repositories/content.ts is the other tempting single choke point, but it takes unknown and never sees a field type, so it cannot tell a datetime string from any other string.

What this seems to need instead is one schema-aware normaliser, called at four points:

Point	Why
REST/MCP handlers, after media processing	the external input boundary
immediately before revision creation	otherwise restoring a revision reintroduces the old notation into the column
seed, after $ref resolution, before the repository write	seed never passes through validation
built-in column overrides (published_at on publish and on update)	a separate path from custom fields

The revision point matters more than it looks. Normalising only the columns leaves the old notation inside revisions.data, and the next publish writes it straight back.

The admin round trip is the real design question

This is where I would most like your call, because the current behaviour is reasoned, not accidental. packages/admin/src/lib/datetime-local.ts:

The widget treats the value as UTC for a stable round-trip. Using new Date(...).toISOString() (the convention in the publish-schedule UI in ContentEditor.tsx) would shift values by the local-UTC offset on every save, mutating the persisted time on each edit cycle.

That reasoning holds against the alternative it names. The consequence is that an editor in Asia/Tokyo who types 01:00 gets 01:00:00.000Z stored and 10:00 rendered on the public site — and the form redisplays 01:00, so nothing inside the admin reveals the drift. It is visible only by leaving the admin and looking at the site.

I think the missing distinction is whose offset. The unstable option the comment rejects is the browser's offset, which differs per viewer and per DST date. But SiteSettings.timezone already exists (packages/core/src/settings/types.ts) and already has an input in the general settings UI. Converting against that is stable — the same result on every save, from any machine, for any editor — where today's behaviour is stable but wrong by the site's own configured offset.

So: convert symmetrically against SiteSettings.timezone (naive in → UTC on save, UTC → site-local on display), or keep the literal-UTC treatment and document the shift as expected? I would argue the former, but it changes what existing installs display, so it is yours to decide.

Two consequences hold either way:

• A normalised value is well-formed whether or not it is correct, so no format checker can catch this class of error. It can only be verified by entering a time in the admin and reading the public page.
• External API/MCP/CLI callers are a different case from the admin form: they know their own timezone, so requiring an explicit offset there — after the migration — costs them nothing and removes the guesswork.

Migration requirements

• Idempotent. Values already canonical are left untouched.
• The timezone used to interpret existing naive values is the operator's call, not the tool's: an explicit --assume-timezone with no default, and a count of affected rows reported before anything is written (skippable when that count is zero).
• Never new Date(naive). It resolves against the machine running the migration, so the same database migrates differently from different laptops.
• DST: a naive local time can be ambiguous (the repeated hour) or non-existent (the skipped hour). Stop and list those rows rather than silently picking one.
• Scope: every collection's datetime columns, the built-in datetime columns, and the matching values inside revisions.data.
• Verify instant-equality before and after, not just row counts. The string changes; the instant must not — except for the naive values, where changing is the entire point, so report those separately.

What I already have

On the consumer side I wrote a read-only detector: it walks a local SQLite database — each collection's datetime columns resolved from _emdash_fields, the built-in datetime columns, and revisions.data — and reports non-canonical values, split into live rows and soft-deleted ones. It exits 0 when clean, 1 on findings, and 2 when it could not inspect something (missing table, unreadable revision JSON, an exception mid-scan), so "could not check" never reports as "clean". It writes nothing.

Happy to contribute it — as an emdash doctor check, as a script, or as the dry-run mode of the migration tool — or to keep it out entirely if you would rather the migration carry its own.

What I'm asking

1. Is UTC with fixed three-digit milliseconds the canonical form you want?
2. Admin widget: convert against SiteSettings.timezone, or keep the literal-UTC treatment?
3. After the migration, should external API/MCP/CLI reject naive datetime input? (Date-only is a separate question — it may be exactly what a caller means for a date field.)
4. Would you take PRs for any of these slices, and in what order?

[ascorbic]

@yet2come these broadly seem like the right idea, so I'd say yes to fixed UTC, yes to site-timezone conversion, yes to rejecting naïve external input after migration, and yes to taking the work in those slices.

So:

• Define datetime as an instant.
• Canonical storage: Date.toISOString() (UTC with fixed milliseconds)
• Admin input should use the site’s configured timezone, not the browser's
• API/MCP/CLI should eventually require Z or an explicit offset.
• Add separate date or localDatetime types later for values that are deliberately not instants but ignore that for now

Migration order should be:

1. Make reads tolerant but all new writes canonical.
2. Add a read-only detector/dry run.
3. Normalize offset-bearing values automatically.
4. Require --assume-timezone for legacy naïve values and stop on ambiguous/nonexistent DST times.
5. Migrate revision JSON as well as physical columns.
6. Only then reject naïve input.