All datetimes stored in UTC or with explicit timezone offset

Storing appointment datetimes without timezone information turns a scheduling platform into a time-bomb. A host in PST and a customer in EST reading the same DATETIME 14:00 literal interpret it as different wall-clock times. This violates CWE-686 (wrong type used for value) and ISO 25010 functional correctness. The failure mode is invisible until someone misses an appointment and support has to reconstruct what actually happened. Once daylight saving time transitions enter the picture, the mismatches compound: a DATETIME column that was correct in January silently drifts by an hour in March.

Use TIMESTAMP WITH TIME ZONE (timestamptz) in PostgreSQL for every appointment datetime column, and store a companion customer_timezone VARCHAR for display purposes. Convert incoming local times to UTC at the API boundary using an IANA library.

-- supabase/migrations/YYYYMMDDHHMMSS_appointments.sql
CREATE TABLE appointments (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  start_time      TIMESTAMPTZ NOT NULL,
  end_time        TIMESTAMPTZ NOT NULL,
  customer_timezone VARCHAR(50) NOT NULL
);

// src/lib/appointments.ts
import { fromZonedTime } from 'date-fns-tz'

export function toUtc(localIso: string, tz: string): Date {
  return fromZonedTime(new Date(localIso), tz)
}

Call toUtc(body.startTime, body.timezone) before writing to the database in src/app/api/appointments/route.ts.

• ID: booking-calendar-availability.calendar-display.utc-storage

• Severity: critical

• What to look for: Before evaluating, extract and quote the exact column type definitions for all datetime/timestamp columns in the database schema (e.g., from prisma/schema.prisma, drizzle/schema.ts, or SQL migration files). Count all datetime columns used for appointment start/end times. Check if they use timestamp with timezone / timestamptz (Postgres), DATETIME with offset (MySQL), bigint for Unix milliseconds, or ISO 8601 with Z suffix. Check if there's a customer_timezone field stored alongside each datetime. Look at API responses — if they include timezone info, it's a sign of proper timezone handling. Search for any timezone conversion logic in booking creation; if dates are stored "as-is" from the client without UTC conversion, flag it. Examine files matching prisma/schema.prisma, drizzle/*schema*, supabase/migrations/*, src/lib/db/*.

• Pass criteria: Count all datetime columns for appointment times. 100% of appointment datetime columns must use a timezone-aware type (PostgreSQL timestamptz, ISO 8601 with Z suffix, or Unix timestamps in milliseconds). Alternatively, if not UTC, each datetime must have an explicit timezone offset stored alongside it (e.g., a customer_timezone VARCHAR column). At least 1 datetime column must exist for start time and 1 for end time. Report: "X of Y datetime columns use timezone-aware storage."

• Fail criteria: Datetimes are stored without timezone info (e.g., datetime type in MySQL without offset), or as a mix of timezones with no explicit offset.

• Skip (N/A) when: The platform operates in a single fixed timezone and does not support customer timezones.

• Detail on fail: Example: "Appointments stored as MySQL DATETIME (no timezone). When a customer in EST books a 2pm slot, it's stored as-is without UTC conversion. A host in PST sees the same value, interpreting it as 2pm PST (3 hours off)."

• Report even on pass: "Storage type used: [exact column types found]. Timezone companion field: [present/absent]."

• Cross-reference: Check customer-timezone-display — UTC storage is the prerequisite for correct timezone display.

• Cross-reference: Check iana-timezone-library — conversions to/from UTC must use an IANA library.

• Cross-reference: Check dst-handling — UTC storage alone does not guarantee correct DST handling during display conversion.

• Remediation: Store all datetimes in UTC. In PostgreSQL:

  CREATE TABLE appointments (
    id UUID PRIMARY KEY,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL, -- Always UTC
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    resource_id UUID NOT NULL,
    customer_timezone VARCHAR(50), -- For display purposes
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
  )
  

  When receiving a booking request, convert to UTC:

  import { toZonedTime, fromZonedTime } from 'date-fns-tz'
  
  export async function createAppointment(
    customerTzName: string,
    localTime: Date,
    duration: number
  ) {
    // Convert from customer's timezone to UTC
    const utcTime = fromZonedTime(localTime, customerTzName)
  
    const appointment = await db.appointments.create({
      data: {
        start_time: utcTime,
        end_time: new Date(utcTime.getTime() + duration * 60 * 1000),
        customer_timezone: customerTzName,
      },
    })
  
    return appointment
  }