16-22: 🗄️ Data Integrity & Integration | 🟠 Major | ⚡ Quick win
All timestamp columns are declared without a time zone. Drizzle maps a bare timestamp() to PostgreSQL timestamp without time zone. That type stores no offset. The stored value then depends on the TimeZone setting of the session that wrote it. A local development session, a CI container, and a production instance can write different absolute instants for the same moment. Comparisons such as expiry checks and review scheduling become incorrect. Use timestamp({ withTimezone: true }), which maps to timestamptz, for every column that records an absolute instant. Apply this before the first migration runs, because changing the type later requires a rewrite of each table.
packages/db/schema-tmp.ts#L16-L22: set withTimezone: true on both createdAt and updatedAt in the shared createdAtUpdatedAt helper. This fixes every table in the tmp schema at once.
packages/db/schema.ts#L64-L82: set withTimezone: true on spaceRepetitions.answeredAt. This column drives review scheduling, so an offset error changes which items a user sees.
packages/db/schema.ts#L101-L118: set withTimezone: true on notificationsRead.readAt.
packages/db/schema.ts#L120-L128: set withTimezone: true on cache.insertedAt and cache.expiresAt. Expiry comparisons against now() are wrong when the session time zone differs from the writer's.
📍 Affects 2 files
🤖 Prompt for AI Agents
16-22: 🗄️ Data Integrity & Integration | 🟠 Major | ⚡ Quick win
All timestamp columns are declared without a time zone. Drizzle maps a bare timestamp() to PostgreSQL timestamp without time zone. That type stores no offset. The stored value then depends on the TimeZone setting of the session that wrote it. A local development session, a CI container, and a production instance can write different absolute instants for the same moment. Comparisons such as expiry checks and review scheduling become incorrect. Use timestamp({ withTimezone: true }), which maps to timestamptz, for every column that records an absolute instant. Apply this before the first migration runs, because changing the type later requires a rewrite of each table.
📍 Affects 2 files
🤖 Prompt for AI Agents