# All datetimes stored in UTC or with explicit timezone offset

- **Pattern:** `ab-000519` (`booking-calendar-availability.calendar-display.utc-storage`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000519
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000519)

## Why it matters

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.

## Severity rationale

Critical because timezone-naive storage causes silent appointment time mismatches that are invisible to both parties until a no-show occurs, with no automatic correction path.

## Remediation

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.

```sql
-- 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
);
```

```ts
// 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`.

## Detection

- **ID:** `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:

  ```sql
  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:

  ```ts
  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
  }
  ```

## External references

- cwe CWE-686
- iso-25010 functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000519