Replace to_char/raw string timestamp handling with Temporal.Instant and Drizzle customType

[brendanlong]

Summary

PR #681 fixed sync cursor precision loss by switching from new Date().toISOString() (millisecond precision) to to_char(..., 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') with raw string passthrough. While this works correctly, it's verbose and error-prone — every query that touches a cursor timestamp needs the to_char pattern, and forgetting it silently truncates microseconds.

We should replace this with a proper type-safe solution using TC39 Temporal.Instant and Drizzle's customType<>.

Proposed approach

1. Drizzle customType for timestamptz

Define a custom column type that maps Postgres timestamptz to Temporal.Instant:

import { customType } from "drizzle-orm/pg-core";

const temporalTimestamp = customType<{ data: Temporal.Instant; dpiData: string }>({
  dataType() {
    return "timestamptz";
  },
  fromDriver(value: unknown): Temporal.Instant {
    // Postgres returns ISO string via node-postgres
    return Temporal.Instant.from(String(value));
  },
  toDriver(value: Temporal.Instant): string {
    return value.toString(); // ISO 8601 with full precision
  },
});

This removes the need for to_char in every query — the schema handles precision automatically.

2. Temporal polyfill

Until Node.js ships native Temporal support (currently Stage 3, shipping in Chrome 144 and Firefox 139 but not yet in Node.js stable):

• Use temporal-polyfill (~20KB, lightweight) or @js-temporal/polyfill (~52KB, reference implementation)
• Import with import { Temporal } from "temporal-polyfill"
• When Node.js adds native support, remove the polyfill import — Temporal becomes a global

3. Scope

Primarily server-side. Research confirmed:

• Entry list cursors are completely opaque on the client (base64 encoded, never decoded)
• Sync cursors are stored as strings on the client and only parsed with new Date() for > comparison in useRealtimeUpdates.ts — this is the only client-side change needed
• All to_char formatting and ::timestamptz casting in sync.ts and entries.ts would be replaced by the customType

Files to change

• src/server/db/schema.ts — Define temporalTimestamp custom type, use for updatedAt, fetchedAt, publishedAt, etc.
• src/server/trpc/routers/sync.ts — Remove to_char expressions, use Temporal.Instant for cursor comparisons
• src/server/services/entries.ts — Remove to_char / sortTsRaw pattern, encode cursors from Temporal.Instant
• src/lib/hooks/useRealtimeUpdates.ts — Replace new Date() comparison with Temporal.Instant.compare() (only client-side change)

Benefits

• Type safety: Temporal.Instant instead of raw strings eliminates precision bugs at the type level
• Less boilerplate: No to_char(... AT TIME ZONE 'UTC', '...') pattern repeated in every query
• Future-proof: Temporal is a TC39 Stage 3 proposal heading toward native support
• Nanosecond capable: Temporal.Instant supports up to nanosecond precision (more than we need, but no truncation risk)

Risks

• Polyfill adds ~20KB to server bundle (negligible for server-side)
• Need to verify node-postgres driver returns enough precision for fromDriver to preserve microseconds
• Migration is mechanical but touches many files

Context

This is a follow-up to #680 / PR #681 which fixed the immediate precision loss bugs with the to_char approach.