> ## Documentation Index
> Fetch the complete documentation index at: https://docs.soundchecklive.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Event clock problem canvas

> One-page map of ENG-580: what the digits mean, which writers produced them, what the migration rewrites, and what is still leftover.

Full narrative: [Event clock writeup (ENG-580)](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md). Inventory SQL and leftover queries live on [Event clock cohort alignment](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md). Stored-active vs display-active by writer interval: [Event clock active intervals](https://docs.soundchecklive.io/engineering/backend/event-clock-active-intervals.md). Availability writers and engagement accept: [Availability and engagement clock semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md). Ledger calendar-date leftovers are [ENG-581](https://linear.app/soundcheck-live/issue/ENG-581/ledgeritemdate-invitation-payable-backfill-leftovers-vs-eventdate) / PR #1370, not this canvas. Storage target is real UTC Instants on call clocks. **Migrations and this-pass fixes are branch-only — not deployed.** Instant writers are **not** in production.

> [!NOTE]
>
> Prod counts below are the 2026-08-17 snapshot. DBeaver on a Central machine uses session `America/Chicago`. Naive `TIMESTAMP` `::date` / `::time` are safe. `(timestamptz)::date` is not.

## The split

Docs and Prisma comments said call clocks were **UTC Instants**, rendered in `Event.timezone` / `Organization.timezone` (`Null = UTC`). Runtime stored the opposite for almost every product writer: **venue wall-clock digits** in a naive `TIMESTAMP(3)` with a fake `Z` (`17:00` → `2026-08-05 17:00:00`).

Will’s SQL rule (naive timestamps are UTC Instants; product conversion belongs in the app) is still the default. **Call-time runtime is the exception.** Treating `17:00Z` as an Instant and projecting it through Chicago is the Mapache email bug (`12:00–6:00 PM` vs crew `5:00–11:00 PM`).

```mermaid theme={null}
flowchart LR
  subgraph stored["What is in Postgres"]
    D["eventDate / endDate<br/>UTC midnight of the calendar day"]
    T["startTime / endTime<br/>must become UTC Instant"]
    Z["Event.timezone<br/>IANA or null"]
    F["FlexQuote load / show / out<br/>real UTC Instants"]
  end
  subgraph readers["Readers after Phase 2 + Phase 4/5 branch code"]
    R["Format/rebase Instant<br/>through Event IANA"]
  end
  T --> R
  Z --> R
  R --> G["Correct crew-facing clock"]
```

`Event.timezone` (or org) **null does not mean the digits are Instants**. It means no IANA label is stored. Readers then assume UTC. Flex create without `metadata.timezone` writes `'UTC'` — the same lie, not Instant proof.

`Organization.timezone` has **no API writer**. `17180000` fills it when Events collapse to one real IANA. Mixed orgs stay null unless already written in prod. Do not treat a still-null org TZ as Instant proof.

## Target after alignment

| Column | Meaning | This migration |
| - | - | - |
| `Event.eventDate` / `endDate` | UTC midnight of the calendar day | Noon `date_trunc`; Flex Instant local day when a zone exists |
| `Event.startTime` / `endTime` | UTC Instant of the call | Wall → Instant when IANA exists and hour is 10–23; leave quote-equal Flex rows |
| `Event.timezone` | Display IANA, or null | Copy Flex metadata IANA onto null/`UTC` in the Instant window |
| `FlexQuote` load / show / out | Real UTC Instants | Unchanged |
| Offer / availability / RoS start/end | UTC Instant | Instant-ize offer/request/avail evening walls; null all-day and inverted hour-0 ends; RoS through Event IANA (0 clocks in prod) |
| `respondedAt` | Server Instant | Leave |
| `DATE` keys (`offer.date`, `avail.date`) | Calendar day | Leave |
| `LedgerItem.date` | Business date | ENG-581 only |

Two corrections, never mixed:

| Correction | Safe when | CT prior (“most orgs are Central”) |
| - | - | - |
| **Fill the zone label** | Needed before wall → Instant | Reasonable if venue/coords agree. Not a convert zone. |
| **Rewrite wall digits to UTC** | Proven wall-clock **and** a real IANA | Unsafe as a CT default. Skip already-Instant rows. |

## Writer timeline

`created_at` names a writer era only if the row was never saved again. A later header save re-encodes the clocks.

```mermaid theme={null}
gantt
  title Writer eras (UTC created_at)
  dateFormat YYYY-MM-DD
  axisFormat %b %d
  section Event
  pre-mobile / unused     :done, e0, 2026-01-01, 2026-04-14
  mobile local-as-Z #379  :crit, e1, 2026-04-14, 2026-05-06
  gap before web RFC      :e2, 2026-05-06, 2026-07-16
  web fake Z #1022        :done, e3, 2026-07-16, 2026-08-31
  Flex Instant #1062      :active, e4, 2026-07-23, 2026-08-16
  Flex wall clock #1328/#1387 :done, e5, 2026-08-16, 2026-08-31
  section Offer / availability
  early fake Z            :done, o0, 2026-04-09, 2026-04-20
  device-offset Instant   :crit, o1, 2026-04-20, 2026-07-15
  late fake Z             :done, o2, 2026-07-15, 2026-08-31
  section Run of show
  device-offset           :crit, r1, 2026-06-16, 2026-07-15
```

| Window | Writer | Prod 2026-08-17 | Disposition |
| - | - | - | - |
| Event before 2026-04-14 | sparse | 571 events, 120 starts, hour 19 × 80 | Already look like typed `Z`. No-op. |
| Event `#379` 2026-04-14 .. 2026-05-06 | mobile local digits as `Z` | 79 events, **0** starts | Nothing to rewrite. |
| Event gap .. 2026-07-16 | mixed | 196 events, 33 starts, **25 Flex-linked** | Flex-linked need Instant proof (query 11). |
| Event web `Z` pre-Flex | web fake `Z` | 12 events, 4 starts, 1 Flex-linked | Same: prove Instant on the 1. |
| Event Flex Instant `#1062` .. `#1328` | Flex wrote quote Instants onto Event | 58 events, 32 starts, **11 Flex-linked** | **10** already Instant — fill IANA only. 11th is wall `Z` → Instant after fill. 21 non-Flex starts Instant-ize if they have a zone. |
| Event after `#1328` | Flex `#1328` writer + `#1387` Instant→wall | empty in the 2026-08-17 snapshot; later prod has Formula A walls | Restore quote Instant when live clock equals Formula A. `20260819160000` undoes origin `18140000` after merge. |
| Offer offset era | device offset | 20 with `start_time`; hours 13–23; 0 next-day Instant | Wall `Z`. Later file. |
| Avail offset era | device offset + all-day `00:00Z` | 655; hour 0 × 625; **15** are `5 → 4` next UTC day | The 15 are already Instant — leave. Hour-0 wall nulls in migration. Other 15 timed leftover Instant-ize when row IANA exists. |
| RoS offset era | device offset | **0 clocks** (12 items / 12 sections) | Closed — query 15. Migration RoS path is a no-op in prod. |
| `eventDate` noon | Studio / web | 698 | `date_trunc` (implemented). |
| `eventDate` other | mixed | 63 (19 Flex, 44 not) | Flex: query 12. Non-Flex: query 13 / 20. |

Empty windows in the 2026-08-17 snapshot (`o_early_z`, `a_early_z`, then-empty post-`#1328` Flex) were no-ops. Later `#1328` / `#1387` writes fill `e_after_flex_wallclock` with Formula A walls — restore from the quote Instant.

## What the migration already rewrites

`20260817180000_event_clock_alignment` — prove, then rewrite. Never convert a whole window.

```mermaid theme={null}
flowchart TD
  A[Row] --> B{Already Instant<br/>or noon calendar-day junk?}
  B -->|noon 12:00| N["date_trunc to midnight"]
  B -->|Flex startTime = show_start_at| F[Fill IANA only]
  B -->|avail 05:00Z to 04:xxZ next day| Null["Null all-day clocks"]
  B -->|hour-0 avail sentinel| Null
  B -->|wall Z hour 10-23 + real IANA| W["Wall → UTC Instant"]
  B -->|Event wall Z hour 0-9| W9["Wall → Instant in writer windows"]
  B -->|offer/request/avail evening + IANA| W
  B -->|wall Z no IANA| X[Leftover]
```

| Cohort | Guard | Prod hit |
| - | - | - |
| Flex metadata → `Event.timezone` | Instant window, Event null/`UTC`, metadata is real `pg_timezone_names` | 11 Instant-window Flex events were null/UTC; metadata `America/Chicago` × 10 + `US/Central` × 1 |
| Flex Instant `eventDate` / `endDate` | UTC day still equals quote Instant UTC day | After the fill, local calendar day |
| Event wall-clock `startTime` / `endTime` | Real IANA; hour 10–23 anytime; hour 0–9 only in writer windows (`[2025-08-09, 2025-11-22)`, `#1022` ≥ `2026-07-16 20:13:31`, Flex `#1328` ≥ `2026-08-16 20:08:29`); SQL upper bound `< 2026-09-15` (freeze **2026-08-20 23:19:32 UTC**); not equal to quote Instant | Web / Flex-after-`#1328` / edited Flex with a zone. Quote-equal Flex rows stay Instant. |
| Offer / request / avail / RoS evening walls | Event or row IANA; Formula B floor `#1012`; SQL upper bound `< 2026-09-15` (freeze **2026-08-20 23:19:32 UTC**) | **56** offers, **81** explicit offer days, avail evenings, **2** request clocks (RoS: 0 clocks) — counts from 2026-08-17 snapshot; fresh 2026-08-19 pm dump has different candidate sets (see below) |
| Avail all-day sentinels | hour-0 wall or Instant `5→4` | **926** hour-0 + **42** Instant all-day → `(null,null)` + required IANA |
| Noon calendar day | `::time = 12:00:00` | **698** Event + `LeadRequest.event_date` |

Sibling IANA is a minority path: 12 orgs / 314 events `single_iana`, **165 orgs / 596 events** have no IANA, 1 org is Chicago+Detroit.

## Leftovers that are classified

No more inventory needed. Do not convert.

Already Instant — leave (do not Formula-B): Flex `startTime = show_start_at` (10); avail device-offset `5→4` (15 — migration nulls as all-day, not wall→Instant); `respondedAt`.

`20260817180000_event_clock_alignment` owns the cutover (no six-file split). Hour-0 availability prefixes and after-cutoff evenings Formula B in this file (upper bound `< 2026-09-15`). Device Instant evenings leave. Remaining unlabeled Events after fill have no `startTime`. RoS has 0 clocks. Require `Event.timezone` later; this file does not `SET NOT NULL`. **Phase 4/5 branch code** (not prod): notifications, documents, duplicate/RoS encode, inbox, Google/offer topology (one resolved zone on create + resync), Staffing Board Event-IANA encodes, mobile schedule `past_midnight` + per-row civil dates, portal/storefront resolved timezone on the API contract, engagement `event_timezone`, timeline civil-day, and many client readers — deploy with the migrations. **`20260819170000_event_schedule_item_formula_b`** writes schedule-item Formula B SQL (hour **10–23**, inverted overnight ends **including exact `00:00:00`**, invalid Event IANA → org, five dump ids skip-as-Instant; ceiling `< 2026-09-15`) — **not applied in prod**, and **no rows convert** while `convert_schedule_wall` is empty. **This pass (branch only):** MOBILE-01, NOTIFY-01, CAL-03, CLIENT-01, CAL-02, WEB-02, MOBILE-02, CONTRACT-01. See [merge / deploy](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md#merge--deploy).

Fresh dump `prod-postgres-20260819-pm.dump` leftovers (classify only — **not converted this pass**): 145 Event evening starts with no IANA; 874 schedule evenings before Jul 15 floor; 65 in-window schedule evenings with no Event/org IANA; 686 Events with neither Event nor org IANA; MIG-02/03/04 empty (0 rows each). Classifier convert sets: **35** Event evening (not Flex-equal); **109** schedule evening (not Event-equal, not skip-listed); **5** schedule skip-as-Instant; **7** other low-hour schedule starts unconverted.

* `#379` mobile Events (0 starts).
* `Organization.timezone`, ledger dates.

## Still open

Prove wall-clock (or already-Instant) **before** picking a convert zone.

```mermaid theme={null}
flowchart TD
  Q[Open leftover] --> I{Instant proof?}
  I -->|Flex: startTime = show_start_at| Z[Zone ladder]
  I -->|eventDate UTC day = show UTC day<br/>and local day differs| Z
  I -->|RoS / avail: Instant pair<br/>like 5 to 4 next day| Z
  I -->|hours 10-23, no quote| F[Fill label only]
  I -->|unknown| X[Leave digits]
  Z --> M[metadata.timezone]
  M -->|missing| C[Event then Venue lat/lng<br/>tz-lookup]
  C -->|missing| S[Venue address state]
  S -->|missing| CT[America/Chicago label fill only]
  CT --> W{Rewrite digits?}
  W -->|Instant proven| R[Rewrite]
  W -->|already Z| L[Label only]
```

| Leftover | Instant proof | Zone | Next |
| - | - | - | - |
| 25+1 Flex-linked before `#1062` | Query 11 vs `show_start_at` | Metadata, then coords | If Instant, fill IANA only. If wall + IANA, wall → Instant. |
| 19 Flex `eventDate` `other` (12 at 23:00) | Query 12 UTC day vs quote | Same | Existing calendar-day UPDATE after a zone fill |
| 44 non-Flex `eventDate` `other` | Weak — query 13 vs `startTime` | Venue state (query 20) | Leave digits unless it is clearly “calendar day + junk clock” |
| Event wall `Z` with no IANA | Query 14 hour histogram | Coords / state | Zone first, then wall → Instant. 0–7 Instant candidate. |
| RoS offset era | Query 15 | Parent Event / Venue | **Closed** — 0 clocks in prod. Migration RoS Instant-ize is a no-op. |
| CRM Instant without a quote | None today | — | Do not wall→UTC on a CT prior |

## Zone ladder

1. `Event.timezone` if it is a real IANA (not `UTC`).
2. `flex_quotes.metadata->>'timezone'` (skip `UTC`).
3. `tz-lookup` on `Event` lat/lng, then `Venue` via `Event.venue_id` (skip `0,0`). Same helper web uses on save. api-events has no server lookup; a one-off pass can run outside Postgres.
4. Sibling Event IANA (mode per org) — minority.
5. `Organization.timezone` — required. Single-IANA fill in `17180000`; mixed / leftover-Event orgs stamped in prod 2026-08-17 and 2026-08-18; leftover empty orgs Chicago-filled then SET NOT NULL. Product writer exists (create defaults UTC; settings update). ENG-588 replaces Chicago placeholders with Clerk / PostHog where known.
6. `Venue.address.state` (`IL` / `Illinois`, …).
7. Default `America/Chicago` — **label fill only**, after a sample of venues agrees.

Venue has **no timezone column**. `FlexQuote.venue_id` is a bare string, not a Prisma FK.

The one multi-IANA org (Chicago + Detroit) is why venue coords beat an org-level CT default. Query 19 is the human check; summarize, do not paste the grid.

## How to read the leftover queries

Paste-safe counts: queries **1–17** on the [alignment page](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md).

Human look, **do not paste names / cities / org ids** — send a one-liner:

| Query | Look at | Reply like |
| - | - | - |
| 18 | Venues on pre-`#1062` Instant candidates | “5 venues, all IL, metadata Chicago, coords on 4” |
| 19 | Venues on the multi-IANA org | “Chicago-labeled rows are IL; Detroit-labeled rows are MI” |
| 20 | Non-Flex `eventDate` other venues | “17:00 cluster all IL” vs “mixed coasts” |

## Out of scope

* **ENG-581 / PR #1370** — `LedgerItem.date` calendar day, payable snap, `20260307` leftovers.
* **API / client writers and readers** — **Phase 1** Event save passes Instants through; **Phase 4/5 branch code** (not prod) formats/rebases call clocks through Event IANA (Staffing encodes, mobile schedule civil-day + `past_midnight`, portal/storefront timezone on the API contract, one Google zone on create/resync, projection shift-by-gap). **This pass (branch only):** cancellation civil dates (NOTIFY-01), accepted ICS duration (CAL-03), RoS second-save reseed (CLIENT-01), resolved Event DTO `timezone` (CAL-02), mobile civil-day grouping (MOBILE-01), CRM/DayStripPreview readers (WEB-02/MOBILE-02), `event_timezone` type (CONTRACT-01). Deploy migrations + reader cutover together. Import/CRM and HubSpot-Flex n8n inbound paths stay a later pass.
* **Rewriting applied migrations** — never edit `20260307`. This work is a new file, `20260817180000_event_clock_alignment`.

## Related

* [ENG-580](https://linear.app/soundcheck-live/issue/ENG-580/event-call-times-utc-as-wall-clock-encoding-vs-orgevent-timezone-docs)
* [Event clock writeup (ENG-580)](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md)
* [Event clock cohort alignment](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md) — time bounds, migration guards, leftover SQL
* [Availability and engagement clock semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md) — locked writer/accept contract
* [Database](https://docs.soundchecklive.io/engineering/backend/database.md) — never edit applied migration SQL
* [ENG-581](https://linear.app/soundcheck-live/issue/ENG-581/ledgeritemdate-invitation-payable-backfill-leftovers-vs-eventdate)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.