> ## 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 cohort alignment

> Which Event date/time rows a time-bounded migration can rewrite, and which cohorts stay leftovers.

Full narrative: [Event clock writeup (ENG-580)](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md). One-page map: [Event clock problem canvas](https://docs.soundchecklive.io/engineering/backend/event-clock-problem-canvas.md). Active Events by writer interval: [Event clock active intervals](https://docs.soundchecklive.io/engineering/backend/event-clock-active-intervals.md). Locked availability / accept contract: [Availability and engagement clock semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md).

Target storage after this migration: naive call clocks are **UTC Instants**. Date-key columns stay calendar dates.

| Column | Meaning |
| - | - |
| `Event.eventDate` / `endDate` | UTC midnight of the calendar day (date key) |
| `Event.startTime` / `endTime` | UTC Instant of the call |
| `Event.timezone` | Display IANA, or null |
| `FlexQuote.show_start_at` / `load_in_at` / `load_out_at` | Real UTC Instants (unchanged) |
| `LedgerItem.date` | Business date — ENG-581 / PR #1370 (out of this migration) |

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

**Time-bounded** means one explicit permutation per writer window. Each window is rewrite, no-op (already Instant or already a calendar date), or leftover pending a zone. `20260817180000_event_clock_alignment` does the whole cutover: Flex metadata IANA, Flex Instant calendar days, Flex `#1328` / `#1387` restore-from-quote, Event wall-clock Instant-ize, noon `date_trunc`, availability nullable clocks + required IANA + all-day null, offer / request / availability evening Instant-ize (including hour-0 prefixes), offer/request `expires_at` and RoS clocks, overnight exact `00:00` ends → null open bound and inverted `:01`–`:59` as next morning, and org TZ copy onto leftover null Event labels. RoS clocks Instant-ize through Event IANA when present (prod dump has 0 clocks). `20260819160000_flex_quote_instant_restore` repeats the Flex restore after origin's `20260818140000` Instant→wall.

| Window (UTC `created_at`) | Writer | Target action |
| - | - | - |
| Event `startTime` before 2026-04-14 | sparse / unused | no-op if null; wall → Instant if IANA + hour 10–23 |
| Event 2026-04-14 .. 2026-05-06 (`#379`) | mobile local digits as `Z` | leftover (0 starts in prod) |
| Event 2026-07-16 .. now (`#1022` / `#1255`) | web fake `Z` | wall → Instant when IANA exists (implemented) |
| Event 2026-07-23 .. 2026-08-16 Flex (`#1062` .. `#1328`) | Flex Instant | leave digits if `startTime = show_start_at`; fill IANA (implemented) |
| Event 2026-08-16 .. now Flex (`#1328` writer + `#1387` / `20260818140000` backfill) | Flex Instant→wall (Formula A fake `Z`) | Restore quote Instant when live clock still equals Formula A in the backfill zone ladder (metadata, else Event, else org). Generic Formula B is the leftover path. `20260819160000` repeats the restore after merge so origin's Instant→wall file cannot win. |
| Offer / availability 2026-04-09 .. 2026-04-20 | fake `Z` | leftover (empty in prod) |
| Offer / availability 2026-04-20 .. 2026-07-13/15 | device-offset Instant | Avail `5→4` already Instant — leave. Hour-0 wall nulls; evening walls Instant-ize |
| Offer / availability 2026-07-13/15 .. now | fake `Z` | Instant-ize hour 10–23 when a zone exists; null inverted hour-0 ends |
| RoS 2026-06-16 .. 2026-07-15 | device-offset | no-op — 0 clocks |
| `eventDate` any noon | Studio / web | `date_trunc` (implemented) |
| `eventDate` Flex 2026-07-23 .. 2026-08-12 | Instant UTC day | local calendar day (implemented when zone is set) |
| `EventScheduleItem` fake-`Z` / offset-less | web `#1022` fake `Z`, mobile offset-less | Formula B through Event IANA in `20260819170000_event_schedule_item_formula_b`; ceiling `< 2026-08-20 23:19:32` UTC (frozen from `prod-postgres-20260820-2319-pm.dump`); convert dump-proved ids in `convert_schedule_wall` (**1046**, exact match vs the prior verify dump); hours 0–23; no `#1012` floor; Event IANA then org then Chicago; skip Event-equal clocks; **2 Flex quote-equal ids skip-as-Instant**; convert stamps `updatedAt` so replay cannot shift again |

## What the migration rewrites

`20260817180000_event_clock_alignment` uses writer-era bounds on `created_at` / `createdAt` to name cohorts; Event wall-clock Formula B for hours 0–9 additionally gates on `updatedAt` in the fake-`Z` writer windows (see table below). Offer / availability / RoS Formula B gates on `updated_at` / `updatedAt` with floor `#1012` and upper bound `< 2026-08-20 23:19:32` UTC (frozen from `prod-postgres-20260820-2319-pm.dump`; those tables stamp `updatedAt` after every convert on the row so replay cannot shift again). Schedule-item Formula B lives in a separate file, `20260819170000_event_schedule_item_formula_b` (same frozen ceiling on `updatedAt`; no `#1012` floor; Event IANA then org then Chicago; convert dump-proved ids in `convert_schedule_wall`, **1046** walls — exact match on this dump; hours 0–23; skip Event-equal clocks; two Flex quote-equal ids in `skip_schedule_instant`). **Neither Formula B file is applied in prod yet.**

| Cohort | Bound (`Event.createdAt` UTC) | Rewrite | Guard |
| - | - | - | - |
| Flex Instant missing zone | 2026-07-23 00:27:23 .. 2026-08-16 20:08:29 (`#1328`) | Copy `flex_quotes.metadata.timezone` onto `Event.timezone` when Event is null/`UTC` | Metadata is a real `pg_timezone_names` IANA, not `UTC` |
| Flex Instant `eventDate` | 2026-07-23 00:27:23 (`#1062`) .. 2026-08-12 22:37:31 (`#1292`) | Venue-local midnight from `flex_quotes.show_start_at` + `Event.timezone` | Flex-linked, valid IANA zone (after the metadata fill), stored UTC day still equals the Instant's UTC day |
| Flex Instant `endDate` | same window | Local midnight from `load_out_at`, or `NULL` when that day equals the show's local day | Same, and `endDate` is not null |
| Flex `#1328` / `#1387` wall clocks | any `createdAt` | Copy `flex_quotes.show_start_at` / `load_out_at` onto Event when the live clock is Formula A of that Instant | Same zone ladder as `20260818140000` (valid quote metadata, else Event, else org — skip `UTC` / unknown names). One quote per Event (`DISTINCT ON (event_id) … ORDER BY updated_at DESC, id DESC`). Quote-equal Instants stay. Runs before generic Formula B. `20260819160000_flex_quote_instant_restore` is the post-`18140000` undo. |
| Event wall-clock `startTime` / `endTime` | any `createdAt` | Wall digits → UTC Instant in `Event.timezone` | Real IANA (not `UTC`); hour 10–23 when `updatedAt` \< `2026-08-20 23:19:32`; hour 0–9 only in writer windows (EventForm `[2025-08-09, 2025-11-22)`, `#1022` ≥ `2026-07-16 20:13:31`, Flex `#1328` ≥ `2026-08-16 20:08:29`) with the same upper bound; not equal to `show_start_at` / `load_out_at` |
| Flex leftover `eventDate` clock on the correct calendar day | any | Same two-step as `20260817160000_ledger_item_dates_to_midnight`: move clock to `00:00:00` on the stored UTC day, then `date_trunc` | Quote local calendar day already equals `date_trunc(eventDate)`. Time correction only — no day-boundary rewrite. |
| Central leftover `eventDate` clocks, no real IANA | any | Same two-step: `::date + 00:00:00`, then `date_trunc` | UTC calendar day equals `America/Chicago` calendar day. Leaves hours that flip the Chicago day. |
| Leftover `eventDate` clock with a real IANA | any | Same two-step using `Event.timezone` | UTC calendar day equals that zone's calendar day. Leaves hours that flip that zone's day. Step 2 cannot reuse the no-flip projection: after step 1 the value is `00:00` UTC, which is the previous evening in US zones. |
| Studio / web noon calendar day | any | `date_trunc('day', …)` when `::time = 12:00:00` | Same UTC date. Also `LeadRequest.event_date` |
| `EventScheduleItem` wall-clock `startTime` / `endTime` | any (`20260819170000_*`) | Wall digits → UTC Instant through Event IANA, then org IANA, then `America/Chicago` | `updatedAt` `< 2026-08-20 23:19:32` (no `#1012` floor); row id in `convert_schedule_wall` (**1046** dump-proved walls); hours 0–23; not equal to Event `startTime` / `endTime`; two Flex quote-equal ids skip-as-Instant |

`timezone('UTC', ts)` is not used. Wall → Instant is `((ts AT TIME ZONE iana) AT TIME ZONE 'UTC')`. Calendar-day projection from a quote Instant is `((instant AT TIME ZONE 'UTC') AT TIME ZONE iana)`.

**Writer-era provenance (Event Formula B hour 0–9):** soundcheck-web EventForm fake-`Z` shipped in `cb1e7663` (#262, 2025-08-08 22:02 CDT) through `6ae2e73a` (#501, 2025-11-20). Migration uses half-open `updatedAt` window `[2025-08-09, 2025-11-22)`. Monorepo `#1022` fake-`Z` floor is `2026-07-16 20:13:31`. Offer / availability / RoS Formula B uses the `#1012` floor `2026-07-15 00:00:19` with the same `updated_at` / `updatedAt` upper bound `< 2026-08-20 23:19:32`.

## What it cannot rewrite

Time bounds identify these rows. They do **not** recover the typed clock.

| Cohort | Why |
| - | - |
| Flex Instant + no Event zone and no Flex metadata IANA | No zone to project into. Prod Instant-window leftovers with metadata are filled first. |
| Device-offset offers / RoS / non-signature availability (`#392` 2026-04-20 .. `#989`/`#1012` 2026-07-13/15) | Client stamped the **device** offset. The UTC-5 all-day pair (`05:00Z` .. `04:xxZ` next day) is already Instant — leave. Hour-0 `00:00Z` nulls and evening same-day rows Instant-ize in `20260817180000_event_clock_alignment` when row IANA exists — not a CT prior. |
| Mobile Event times 2026-04-14 .. 2026-05-06 (`#379`) | Device-local digits labeled `Z`. 0 starts in prod. |
| Mobile bare-local writes after 2026-05-06 | Encore rejects missing `Z`; those writes did not land. |
| CRM Instant times | Hours 0–9 may already be Instant. No Flex quote. Do not treat as wall. |
| Event wall `Z` with null/`UTC` timezone | No convert zone. 165 orgs / 596 events. Zone ladder first. |
| `Organization.timezone` | Filled when the org’s Events have exactly one real IANA (`US/Central` ≡ `America/Chicago`). Multi-IANA orgs stay null unless already written in prod (two internal test orgs 2026-08-17; eight mixed + five leftover-Event orgs 2026-08-18). The same file copies that org default onto leftover null/`UTC` Events. It does not overwrite a real Event IANA. Location fill uses `Event.location` + longitude only — not `venues`. WA / NE are not in the CASE. |
| `respondedAt` | Already a server Instant. |

Do not wall→UTC every row in a window. Flex Instant rows in the same dates still equal `show_start_at` (`22:00`). Treating that as 10pm Chicago writes `03:00` the next UTC day.

## Fresh dump leftovers (2026-08-19 pm — classify only)

Dump `prod-postgres-20260819-pm.dump` (920 Events; taken 2026-08-19 22:29 UTC). Instant writers are **not** in production; **0** rows were post-ceiling on the prior `2026-08-20` bound. Record these counts for a later wave — **do not convert in this pass**:

| Leftover | Count |
| - | - |
| Event evening starts with no IANA | 145 |
| Schedule evenings before `#1012` Jul 15 floor | 874 |
| In-window schedule evenings with no Event/org IANA | 65 |
| Events with neither Event nor org IANA | 686 |
| MIG-02 spring-forward 22h availability | 0 |
| MIG-03 event-less request clocks | 0 |
| MIG-04 multi-quote Events | 0 |

MIG-02/03/04 are empty on this dump — add cheap fixtures later.

**Candidate convert sets** (evening-shaped; Instant writers not live): **36** Event Formula-B starts (13 Flex-linked); **46** schedule rewrites; **54** in-window offer hour 10–23.

At Instant-writer deploy: freeze the ceiling to that deploy instant, dump, and Formula-B only rows with `updatedAt` in `(prior_ceiling, freeze]`.

**This pass (branch only, not deployed):** MOBILE-01, NOTIFY-01, CAL-03, CLIENT-01, CAL-02, WEB-02, MOBILE-02, CONTRACT-01 — see [writeup Instant readers table](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md#instant-readers-phase-2--phase-45-branch-code).

## Fresh dump leftovers (2026-08-20 verify — classify only)

Dump `prod-postgres-20260820-verify.dump` (921 Events; raw restore, clock migrations not applied). Dest already has `20260818140000` Flex Instant→wall and `20260817160000` ledger midnight. Instant writers are **not** in production. **Do not convert** from this table; it settles git-history unknowns.

| Unknown | Count | Verdict |
| - | - | - |
| Event hour 10–23 Formula B set (not quote-equal) | **180** (0 Flex-equal, 29 Flex-unequal, 151 non-Flex). Event-IANA **35** → simulated org/Chicago zone **180**. Peak hour **19** × 95. | Dump-proved wall-shaped. Non-Flex 21–23: **8** EventForm-early leftovers, not a CRM Instant cohort. |
| Event hour 0–9 gap `[2025-11-22, 2026-07-16 20:13:31)` | **0** | Vacuous. Git window can stay. |
| Evening starts with no Event IANA | **145** (same as 2026-08-19). After simulated zone: **0**. | Org TZ required collapses the convert-zone leftover. |
| Events with neither Event nor org IANA | **686** (same as 2026-08-19) | CTE still fills Chicago; do not treat as Instant proof. |
| Schedule evenings before `#1012` | **874** | Census leftover only. This pass dropped the floor; those walls are in `convert_schedule_wall`. |
| In-window schedule evenings | **116** (7 Event-equal, 0 Formula A, **109** neither). Hour 0–9 in-window **12**. Classify-only skip ids were 5/5; dump-proved walls were **0** at census. | Later pass filled `convert_schedule_wall` (**1046** walls; skip = two quote-equal ids). |
| Created before `#1012`, updated in Formula B band | **0** on offers, days, availability, requests, RoS | Vacuous. Mutable `updatedAt` Instant-risk did not fire on this dump. |
| Offer / offer-day start hour 0, ≠ Event | **1** (quote Instant hour 0; matching day) | Leftover by design. Do not generic-B. |
| Event-bound request / day hour 0–9 | **0** (clocks still hours 13 and 18 only) | Vacuous. |
| Post-freeze `updatedAt >= 2026-08-20 23:19:32 UTC` (SQL bound still `< 2026-09-15`) | **0** | Vacuous. Max Event `updatedAt` 2026-08-20 16:26:23 UTC. |
| `#379` created Event starts | **0** (85 events in the calendar window, 0 clocks) | Vacuous. |
| RoS section/item clocks | **0** | Vacuous. |
| MIG-03 event-less request clocks | **0** | Vacuous. |
| MIG-04 multi-quote Events | **0** | Vacuous. |

**Candidate convert sets:** Event evenings **180** after simulated zone (was 35 with Event IANA only). Schedule allowlist was **0** at this census — a later pass filled it (see below).

## Git + dump proof (2026-08-20)

Web Timeline (`formatTimeToTimestampUsingDateSelection`) has been fake-Z since soundcheck-web `#262` / timeline `#501`. Mobile schedule on `origin/dev` is bare-local wall digits — the same naive `TIMESTAMP` as fake-Z. UEF ingest can INSERT Instant `EventScheduleItem` rows but this dump has **0** `ingestion_batches` and **0** schedule-on-ingest.

| Leftover | Proved wall | Proved Instant | Unprovable |
| - | - | - | - |
| Schedule pre-floor evenings **874** + in-window neither **109** | Yes (web + mobile wall digits; ingest unused) | Skip in-window Event-equal **7** | **0** |
| Flex-linked schedule | **33** unequal (Mapache gig, web/mobile wall) | **2** quote-equal (`5e5a630f-…` hour 13, `9e7f9a67-…` hour 15) | **0** |
| Event hour 10–23 set **180** | **151** non-Flex + **29** Flex-unequal | Quote-equal skip in this slice | **0** |
| EventForm-early 21–23 **8** | **8** (Flex-linked **0**; EventForm fake-Z window) | **0** | **0** |

This pass filled `convert_schedule_wall` from `prod-postgres-20260820-verify.dump` (**1046** walls: every non-null start and/or end except the two Flex quote-equal Instant ids) and expanded `20260819170000` — no `#1012` floor, hours 0–23, Chicago fallback when Event and org IANA are missing/`UTC`/unknown, `skip_schedule_instant` = those two quote-equal ids. Untagged audit rows (621) are leftover-by-design: web bulk upsert also omits per-item ids.

## Leftover inventory (read-only)

Session TZ is assumed UTC; the queries still name UTC.

If most orgs are one zone and run similar gigs, a missing `Event.timezone` can be filled from the org's other events, and a device-offset Instant can be treated as “typed clock in that zone.” Queries 1–10 and 11–17 are counts and histograms only (no titles, names, or emails). Queries 18–20 return venue rows for a human look; **do not paste those result grids** — send a one-line summary (for example “8 IL / 1 MI, all look like houses of worship”).

DBeaver on a Central machine uses session `America/Chicago`. Naive `TIMESTAMP` `::date` / `::time` / `EXTRACT(HOUR FROM ts)` are safe. `(timestamptz)::date` is not. Local calendar day must be `((ts AT TIME ZONE 'UTC') AT TIME ZONE iana)::date` (the outer `AT TIME ZONE` yields naive local digits).

### Prod snapshot (2026-08-17)

Naive `TIMESTAMP` `::time` / `::date` does not use session `TimeZone`. The `AT TIME ZONE 'UTC'` form is still required when projecting into an IANA zone.

| Finding | What it rules |
| - | - |
| Mobile Event window: 79 events, **0** `startTime` | No `#379` Event-clock cohort to rewrite |
| No `o_early_z` / `a_early_z` / `e_after_flex_wallclock` rows | Those permutations are no-ops |
| 12 orgs `single_iana` (314 events), 165 orgs events-but-no-IANA (596 events), 1 multi-IANA org (6 events, Chicago+Detroit) | Sibling-zone inference covers a minority of events. Do not assume “most orgs have a stored zone.” |
| Flex Instant-window: **11** linked events, all `Event.timezone` null/UTC; metadata `America/Chicago`×10 + `US/Central`×1; 10 still match `show_start_at` | Metadata fill only on those 10 (already Instant). The 11th is wall `Z` — wall → Instant after the fill. |
| Offset-era offers (query 4): 20 with `start_time`; `false true true` **15** (UTC day and local day both match `offer.date`); `true true false` **5** (no inferred tz); **0** next-day Instant | Wall-clock `Z` hours 13–23. Instant-ize in this file when a zone exists. |
| Offset-era availability: session-safe `start_time::date - date` = **0** × **655**. DBeaver session `America/Chicago` made `(timestamptz)::date` report **-1** × 627. Query 10 non-hour-0: **15** are `5 → 4` next UTC day; the other 15 are same-day evening/odd windows | The 15-row `5/4/+1` cluster is already Instant — leave. Hour-0 × 625 is wall all-day — this file nulls it through a proven zone. Evening `18–22` Instant-ize in this file. |
| Pre-mobile Event starts: hour **19** × **80** of 120 | Typed 7pm `Z`. Wall → Instant when `Event.timezone` is a real IANA. |
| `eventDate` noon 698 / midnight 155 / other 63 (19 of the 63 are Flex). Non-Flex other: 5:00×8, 17:00×8, … Flex other: 23:00×12 plus sparse hours | Noon `date_trunc` is the bulk calendar-day win. Do not convert non-Flex `other` yet. Flex Instant `other` is covered by the metadata fill + local-day rewrite. |

Do not Formula-B whole availability or offset-era offer `created_at` windows without row-level guards. Device-offset `5→4` stays Instant (migration nulls as all-day). Hour-0 walls null in this file. Evening same-UTC-day walls Instant-ize when row/Event IANA exists. Event clocks with no IANA stay leftover until the zone ladder runs.

### Flex calendar-date leftovers (2026-08-17 evening)

Prod dump after manual IANA fills + the July 20 → July 28 date-key move on `c441fa1b-…`. Only **Mapache** (`org_371UdJ5So6gIkx7q7x0YNxGSfhT`) has Flex quotes (36 linked events). `flex_quotes.show_start_at` is a `TIMESTAMP(3)` Instant, not a date key.

Compare `Event.eventDate` to the quote **local** calendar day (`America/Chicago` / `US/Central`), not the Instant's UTC calendar day. 33 of 36 match that local day (10 of those 33 differ from the Instant's UTC date — Formula A Instant-vs-calendar-day, leave).

The three local-day mismatches are **leave**. Stored `eventDate` is the gig start day; do not rewrite from `show_start_at`:

| Event | Stored `eventDate` | Quote local day | Why leave |
| - | - | - | - |
| `7645f299-ad4b-47ca-998e-867bfbde36db` | 2026-09-03 (`endDate` 2026-09-05) | 2026-10-30 | Gig is Sept 3. Quote Instant is Flex noise. |
| `7b2b390b-ce56-4bbe-abb8-3eef9a53ef73` | 2026-07-16 (`endDate` null) | 2026-07-17 | Multi-day 16–17. Quote Instant is day 2. |
| `042d091e-9a16-4be6-85de-7d8d56673cd9` | 2026-08-24 (`endDate` null) | 2026-08-25 | Multi-day 24–25. Quote Instant is day 2. |

`c441fa1b-d39b-48a8-acf4-ca05e359ea83` was the −8-day draft (stored 2026-07-20 18:29:16 vs quote 2026-07-28). Confirmed gig day is the 28th. Manual write moved the date key and kept the leftover clock; the migration midnight-then-`date_trunc` finished it to `2026-07-28 00:00:00`. Do not hand-strip the other Flex leftover clocks — that two-step is in this file.

Filling a missing IANA is a manual prod write. Changing a date-key clock or Instant-izing a call time belongs in the migration.

After this file on that dump: 0 Flex rows without IANA, 0 hour-10–23 starts without IANA, 0 noon `eventDate`s, **0 leftover `eventDate` clocks**. The Mapache 10 + 9 + 23 leftover `eventDate` clocks are stripped by the Central two-step. The two ragged IANA `eventDate`s (`test-sms-a033d7f9-…` `09:14` `America/New_York` on org will; `6c2ae27f-…` `17:50` `America/Los_Angeles` on Demo Group) have correct zones and are internal test orgs — same two-step using `Event.timezone` when UTC day equals that zone's calendar day (`09:14` UTC → `04:14` ET same day; `17:50` UTC → `10:50` PDT same day). Offers / availability Instant-ize in this file — see below. RoS has no clocks. Hour-8 start with no IANA (`8261a2e1-…`) stays an Instant candidate.

Sibling fill stamps a single sibling IANA onto null-`timezone` rows with hour-10–23 (or null) starts. It collapses `US/Central` ≡ `America/Chicago` (canonical `America/Chicago`), so Mapache-like orgs with both labels count as one zone. True multi-IANA orgs (Chicago + New York) are still skipped. Tests for the null-tz Central two-step must not share an org with a lone real IANA. If sibling fill stamps a zone onto a leftover clock, the IANA two-step still strips it when UTC day equals that zone's calendar day.

Do not Formula-B `eventDate`.

### Learnings (2026-08-17 late)

* **Two-step clock strip is time correction, not Instant-ize.** Copy ledger: move clock to midnight on the *stored* UTC day (assert no day boundary), then `date_trunc`. Event.eventDate stays `TIMESTAMP`, so step 2 is `date_trunc` rather than `ALTER … TYPE DATE`. Do not fold clock-strip into Formula A (Formula A is the day-boundary rewrite).
* **Step 2 cannot reuse the no-flip WHERE.** After step 1 the value is `00:00` UTC. Projecting `00:00` UTC through Chicago / New York / Los Angeles is the **previous** local evening, so the same UTC-day = local-day guard would miss every row that step 1 just cleaned. Step 2 is “already midnight, date\_trunc.”
* **IANA leftover clocks:** if the stored IANA is trusted and UTC calendar day equals that zone's calendar day, strip with the row's own timezone — do not default leftovers through Chicago. The will / Demo Group leftovers were that case (correct IANA, internal test orgs). Hours that flip the zone's calendar day (e.g. `04:00` UTC) stay leftover Instant candidates.
* **A leftover clock on a wrong date key is still a leftover clock.** Formula A leaves `eventDate` when the stored UTC day disagrees with the quote Instant UTC day. The IANA two-step still midnight-truncates the time on the stored day. It does not move the date key to the quote day (the −8-day Flex draft needed a human date-key write first).
* **Sibling fill vs leftover clocks.** An org whose Events collapse to exactly one real IANA copies that label onto null-tz siblings *before* the Central two-step, so the null-tz WHERE misses them. Sibling fill collapses `US/Central` ≡ `America/Chicago`; Mapache now qualifies. The IANA two-step covers stamped rows when UTC day equals that zone's calendar day.
* **Manual prod writes vs this file.** Filling a missing IANA (or a confirmed date-key move like `c441fa1b-…` July 20 → 28) can be a human write. Instant-izing call times and stripping leftover clocks belong in the migration, same deploy as writer/reader cutover.
* **Flex `show_start_at` is an Instant, not a date.** Compare `eventDate` to the quote **local** calendar day. 10 Mapache rows differ from the Instant's UTC date and still match Chicago local day (Formula A). Three local-day mismatches are product dates (Sept 3 vs Oct 30 Flex noise; two multi-day gigs whose quote Instant is day 2) — leave. Null `endDate` on those multi-day rows is a product write, not this clock pass.
* **Mapache leftover clocks are Central Instants stuffed into `eventDate`, not Studio noon.** 41 of 42 share `createdAt = 2026-03-25 14:41:04.91`. In-repo Studio/web wrote `12:00:00Z`; these hours are Chicago local midnight / noon / daytime via the Go `{seconds,nanos}` passthrough. They do not flip calendar day vs UTC, so the Central two-step is time-only. Title `YYYYMMDD` can still disagree with the stored day.
* **Hour-0 availability is a zoneless all-day date stored as UTC timestamps.** `date` is the calendar day; `00:00`/`23:59` is a sentinel, not a call Instant. Formula B (decided) interprets local midnight in the org Event IANA. `5→4` is already Instant — leave. Offers are Mapache / Soundcheck HQ / Demo Group; availability is 18 orgs (RGE is New York). Two internal test orgs (Benji Uzo, Test org) had `Organization.timezone` set to `America/Chicago` in prod so the four mixed-IANA hour-0 rows have a convert zone.

### Same file — offers / leftover timed availability

Writer/accept semantics and the 2026-08-18 dump checks (Ben Stacy nulling, no request inversions, ENG-587 overnight two-row accept, post-migration calendar-day spans) live on [availability and engagement clock semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md). What remains below is Instant-ize inventory for **timed leftovers**, not all-day.

Hour-0 and Instant `5→4` are **not** Formula-B candidates anymore — `20260817180000_event_clock_alignment` nulls them.

Overnight hour-0 end nulling is after every hour 10–23 Instant-ize (and after availability `DROP NOT NULL`). Chicago 16:00–18:00 walls Instant-ize to 21:00–23:00; overnight then Formula-B’s mixed pairs so those starts are not Formula-B’d twice. Event 19:00 Instant-ize produces hour-0 starts; overnight then nulls midnight open bounds. Do not move overnight earlier.

Counts are the post-align dump (Event Instant-ize applied; offer/avail digits untouched). `DATE` keys stay calendar days. Discriminate by **clock shape**, not `created_at` — `5→4` Instant all-day also exists in `a_late_z`. RoS is closed (12 items / 12 sections, 0 clocks). `20260817180000_event_clock_alignment` Instant-izes classified offer/request leftovers: **56** `crew_offers`, **81** evening `crew_offer_days`, **2** `availability_requests` / request-day clocks (Formula B hour 10–23, Event IANA, UTC date = row date key, skip clocks already equal to Event `startTime` / `endTime`; explicit day clocks only — null inherit stays null).

**Decision (2026-08-17):** Formula B hour-0 and evening availability through a real IANA. Leave `5→4`.

**Update (same day):** `20260817180000_event_clock_alignment` stores calendar-day meaning as required `timezone` + nullable clocks instead of Formula-B'ing hour-0. Hour-0 sentinels and `5→4` are nulled. Evening timed windows Instant-ize in this file. Clients display present clocks as UTC Instants in the viewer timezone; the server does not convert them into the row IANA.

`Organization.timezone` is filled in this Event file when the org’s Events have exactly one real IANA after collapsing `US/Central` ≡ `America/Chicago`. Multi-IANA orgs are skipped. This is a label fill (avail Formula B / display fallback), not a convert-everything-as-CT prior. `20260817180000_event_clock_alignment` copies the org default onto Events that sibling fill left null (hour 0–9 start skip).

All-day `member_availability` clocks are **null,null** plus a required IANA (`20260817180000_event_clock_alignment`). Hour-0 sentinels and `5→4` Instant all-day share the product meaning “blocked on `date` in `timezone`.” Present clocks are UTC Instants; the server does not project them into the row IANA for display. Open bounds (`start` xor `end` null) use local start/end of that date in the row IANA.

Ben Stacy (`org_3G8sNsXlz3GINWkmV0h4XSvJEMx`) has **0 Events**. All 27 availability rows are one shape: `05:00:00` → `04:59:00` next UTC day (e.g. `2026-07-23 05:00:00`–`2026-07-24 04:59:00`). That is Instant-encoded all-day in UTC−5 (local midnight CDT = `05:00` UTC), not an event title and not a timed 5am–4am gig. “Leave” meant do not Formula-B again. `20260817180000_event_clock_alignment` infers `America/Chicago` from the `5→4` shape and nulls the clocks.

#### All-day availability is a zoneless date wearing UTC clothes

Hour-0 all-day (`date` + `00:00:00` / `23:59:xx` on that same UTC calendar day) is not a call Instant. The product meaning is “blocked on this calendar day,” and the calendar day already lives on `member_availability.date` (`DATE`, no zone). The timestamps are a sentinel for “all day,” used as **zoneless** bounds for that date.

New availability writes send UTC Instants. All-day is `(null,null)` plus the row IANA. The server rejects clocks whose IANA calendar day is not `date`; overnight / multi-day windows are multiple requests, not `splitOvernight`. Invitation accept, offer accept, and request respond copy UTC Instants as-is (no event-date gate) and collapse UTC `00:00`–`23:59*` sentinels to `(null,null)`.

`Event.timezone` stays optional. `20260817180000` does not `SET NOT NULL`. `20260817180000_event_clock_alignment` copies org TZ onto leftover null/`UTC` Events when the org has a real IANA.

Readers treat `00:00` + `23:59` (any seconds) as all-day (`isAllDayRange` / `normalizeAvailabilityResponse`). No current TypeScript writer produces the `5→4` Instant pair. `DAY`-mode request *days* store `date` only (null clocks).

Storing that sentinel in the same `TIMESTAMP(3)` columns as timed Instants is the strangeness: docs/Prisma treat those columns as UTC Instants, but hour-0 readers treat them as “midnight–end on `date`” with no zone. Instant readers format present clocks through row/Event IANA where wired on the branch; the migration nulls hour-0 / `5→4` sentinels so post-cutover rows use `(null,null)` plus required IANA. **Phase 4/5 branch code** (Staffing Event-IANA encodes, mobile schedule `past_midnight` + per-row civil dates, portal/storefront resolved timezone on the API contract, accepted ICS day projection, RoS decode-zone pin + second-save reseed, engagement `event_timezone`, cancellation civil dates, CRM civil dates) ships with **`20260819170000_event_schedule_item_formula_b`** — **neither migration nor readers are in prod**. See [merge / deploy](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md#merge--deploy).

Hour-0 and Instant `5→4` are nulled in `20260817180000_event_clock_alignment`, not Formula-B’d into local-midnight Instants. `5→4` is the same all-day idea already stored as a UTC-5 Instant — infer Chicago and null; do not B again.

`date` stays the calendar-day key. Do not Formula-B `date`.

#### Orgs (2026-08-17 late)

Offers are three orgs only — Mapache, Soundcheck HQ, Demo Group. Availability is **18 orgs**, not Mapache-only. Top Down Creative (353, `America/Chicago`), RGE Inc. (268, `America/New_York`), Mapache (244), and IMZ Artists (99, `America/Chicago`) are the bulk. Do not B every avail row through Chicago.

The 8 “other” avail windows (hours 1–9, not `5→4`) are all Mapache (`America/Chicago` + `US/Central`). They are an Instant-vs-wall question, not a missing-zone question.

Avail with no Event IANA on the org: Ben Stacy 27×`5→4` (leave), No Animals Were Harmed 3×hour-0, DJ Sam Patel 1×hour-0.

**Mixed Event IANAs, internal test, hour-0 wall** — `Organization.timezone` was null (no product writer). Prod write 2026-08-17: both orgs set to `America/Chicago`. Event labels were not touched.

| Org | Org id | Avail ids | `date` / clocks | Event IANAs on the org |
| - | - | - | - | - |
| Benji Uzo | `org_34QPOuQQcPeH7qBUzqD2AxcO2DT` | `0e8bbcba-83d5-4ad3-8c68-0b62961fc743`, `38674a6c-36d9-4efe-bfed-b121625dca69`, `1476901e-1d4f-4dee-8e7e-5c46d4dc6626` | 2026-06-06 / 06-13 / 06-27 · `00:00:00`–`23:59:59` | Chicago ×6, New York ×2 |
| Test org | `org_3AGArebKoLdV815lMiuOb1QvxL1` | `b49d6e5c-3512-4305-9d5d-85d4d17fbf69` | 2026-07-08 · `00:00:00`–`23:59:59` | Chicago ×4, Detroit ×2 |

Each avail row was created a few seconds after an Event on that day. June 6 is the mixed day (Chicago Event June 4, New York Event \~2s before the avail). Later-file B can use `Organization.timezone` (`America/Chicago`) when Event IANAs on the org disagree.

Hour-0 org-mode IANA (before that write): 740 single, 182 multi, 4 none. 178/182 multi are Mapache Chicago + `US/Central` — alias-collapse makes them single. The other 4 multi are the four ids above.

#### IANA leftovers (2026-08-18)

The Aug 17 user-by-user pass was the **12 timed-start Event IANAs** plus Benji Uzo / Test org. It was not the later 8 mixed / 31 zero-Event-IANA census.

`17180000` skips org fill when Events have two real IANAs. Location fill never reads `venues`. Dummy `0,0` fails the CA/TN longitude guards. WA and NE are absent from the CASE (Murder House Seattle coords; Gone With The West western Nebraska).

Prod write 2026-08-18 (org defaults; Event labels only on the five zero-IANA orgs). Dump `prod-postgres-20260818-post-stamp.dump`. Clock migrations still not deployed — **63** Event IANAs in prod (the 819 figure is post-`17180000` on a dump). **15** orgs have a real TZ. **0** mixed orgs still have null org TZ.

| Bucket | Org default | Event fill |
| - | - | - |
| Soundcheck Team, testing noti, The Nashville Band, Christian Wiggs (Dallas), Toph Ehresman (Austin) | `America/Chicago` | none — mixed Event labels wait for location/sibling fill; Soundcheck leftovers copy Chicago in the org-copy section |
| Coffee Chat | `America/New_York` (Atlanta IP) | none |
| Ben Ikwuagwu | `America/Los_Angeles` (LA IP) | none |
| Rebel Rose Ent | `America/Los_Angeles` (event majority + public HQ) | none |
| Murder House Studios | `America/Los_Angeles` | all 5 Events (WA not in CASE) |
| 3 Room Tent | `America/Los_Angeles` (last IP; no public home base) | that Event |
| Animal Andino | `America/Argentina/San_Juan` (festival home; Spain IP was travel) | that Event |
| Gone With The West | `America/Chicago` (Nashville) | that Event `America/Denver` (western NE) |
| testing | `America/Chicago` (Austin IP) | that Event |

Do not hardcode these org IDs in Prisma SQL. Stamp Events **before** org on the five so org copy cannot copy home-base onto a gig that already has a zone (Gone With The West Denver vs Nashville Chicago).

| Table | Already classified | Still to discover |
| - | - | - |
| `crew_offers` | **56** Formula B (hour 10–23, Event IANA, `start::date = date`, ≠ Event start): 50 `o_late_z` + 6 `o_device_offset`. Offset-era *offers* in this dump are already wall-clock `Z` (query 4: **0** next-day Instant) — same B as late\_z, not the avail Instant trap. **12** equal Event start and Flex `show_start_at` (11 hour 10–23, 1 hour 0) — already Instant, leave. | **3** hour 0–9 (shapes `0→23` span −1; `1→2`; `1→3`; `2→14` — one of the hour-0 rows is in the Instant-12). Wall vs Instant per shape. **3** other: 2 hour-23 Mapache with an Event but no Event IANA (sibling fill skipped Chicago + `US/Central`); 1 hour-14 Mapache with **no `event_id`**. Overnight `end_time` pairing on the 56. |
| `crew_offer_days` | 183 days; 90 inherit parent (`start` null — follow the parent rewrite). 93 explicit clocks: **81** hour 10–23 and `start::date = day.date` (51 eq parent ≠ Event; 16 diverge from parent; 11 eq parent and Event). 61 late\_z evening, 20 offset evening. | The **16** parent-divergent evening days: Event IANA is the convert zone if the day key matches, but confirm they are wall. **12** hour 0–9 (11 same UTC day, 1 not). 3 evening days with no Event IANA. Rewrite overrides; Instantizing the parent does not rewrite an explicit day clock. |
| `member_availability` | **926** hour-0 wall (625 offset + **301 late\_z**) — nulled with required IANA in `20260817180000_event_clock_alignment` (including Instant `5→4` all-day). Same-UTC-day hour 10–23 evening pairs Instant-ize in `20260817180000_event_clock_alignment` (row IANA; skip `'UTC'`; `updated_at` in fake-`Z` band only). Overnight inverted hour-0 ends null in `20260817180000_event_clock_alignment` (fake-`Z` evening starts Formula B in the same UPDATE). **42** `5→4` already Instant — null, do not Formula B. No `event_id`. | Leave: Instant-era evenings (`updated_at` before `2026-07-15 00:00:19`) and Instant UTC-crossing `14:00→04:59`. Hour-0 prefixes and hour 1–9 walls Formula B when `updated_at` is on or after that floor. |
| `availability_requests` (+ days) | 35 requests / 50 days. Only **2** request clocks and **2** day clocks (hours 13 and 18). One equals Event start (leave if quote-Instant); one does not, Event IANA present. | Same B/leave rules as offers. Too small to hide a new writer — confirm the two rows and stop. |

Do not convert a whole offer/avail `created_at` window. Do not run B on `5→4` or on offer clocks that still equal `show_start_at`. Do not default **unknown** leftovers through `America/Chicago` — RGE and Freshmakers already have a real Event IANA. Alias-collapse `US/Central` ≡ `America/Chicago` on sibling / org-mode fill is in this file (unblocks Mapache offers + avail).

### Mapache leftover `eventDate` clocks (2026-03-25 batch)

41 of 42 non-midnight Mapache `eventDate`s share `createdAt = 2026-03-25 14:41:04.91` UTC (`e_pre_mobile`, not Flex). That is one bulk write, not sequential UI saves. The 42nd is `2026-07-02 15:25:44.619` (title starts `Flex quote`).

Git does not name the client. In-repo Studio/web at that date wrote **UTC noon** (`12:00:00Z`) — these rows are not that. The Go API stored `{seconds,nanos}` as-is. On a Central machine that produces:

| Stored UTC hour | Chicago local | Count in the batch | Reading |
| - | - | -: | - |
| 5 (CDT) / 6 (CST) | `00:00` | 10 | Venue-midnight Instant |
| 17 (CDT) / 18 (CST) | `12:00` | 9 | Venue-noon Instant |
| 13–16, 20 | `07:00`–`15:00` | 22 | Same Instant encoding of whatever local clock the source had |

All 23 “other leftover clocks” are **`Event.eventDate` only**. `endDate` on those rows is already midnight or null.

Do not treat hour 5/6 as 5am wall or hour 17/18 as 5pm wall. Do not use the Studio/web `12:00:00` `date_trunc` for these — that guard misses them.

**2026-08-17 decision:** treat the batch as created in Central time. These leftover clocks (hour 5/6 midnight Instant, 17/18 noon Instant, and the 23 other `eventDate` hours) do not flip the calendar day between UTC and `America/Chicago`. This file now strips them in two steps (clock → `00:00:00` on the stored UTC day, then `date_trunc`), same as Flex leftover clocks / ledger. Title `YYYYMMDD` can still disagree with the stored day; that is a product date question, not a UTC/local flip. Do not run a CT-prior Formula B on `eventDate`.

### 1. Scale by window

```sql theme={null}
SELECT 'event' AS kind,
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-04-14 00:00:00' THEN 'e_pre_mobile'
    WHEN e."createdAt" < TIMESTAMP '2026-05-06 14:53:38' THEN 'e_mobile_local_as_z'
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    WHEN e."createdAt" < TIMESTAMP '2026-08-16 20:08:29' THEN 'e_flex_instant_overlap'
    ELSE 'e_after_flex_wallclock'
  END AS window,
  COUNT(*) AS events,
  COUNT(e."startTime") AS with_start,
  COUNT(*) FILTER (WHERE fq.event_id IS NOT NULL) AS flex_linked
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
GROUP BY 2
UNION ALL
SELECT 'offer',
  CASE
    WHEN created_at < TIMESTAMP '2026-04-20 21:31:03' THEN 'o_early_z'
    WHEN created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'o_device_offset'
    ELSE 'o_late_z'
  END,
  COUNT(*),
  COUNT(start_time),
  COUNT(event_id)
FROM crew_offers
GROUP BY 2
UNION ALL
SELECT 'availability',
  CASE
    WHEN created_at < TIMESTAMP '2026-04-20 21:31:03' THEN 'a_early_z'
    WHEN created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'a_device_offset'
    ELSE 'a_late_z'
  END,
  COUNT(*),
  COUNT(start_time),
  0
FROM member_availability
GROUP BY 2
ORDER BY 1, 2;
```

If a window has `with_start = 0`, that permutation is a no-op.

### 2. Are orgs single-zone?

```sql theme={null}
WITH event_zones AS (
  SELECT e."organizationId" AS org_id,
         e.timezone,
         COUNT(*) AS n
    FROM "Event" e
   WHERE e.timezone IS NOT NULL
     AND e.timezone <> 'UTC'
   GROUP BY 1, 2
),
org_summary AS (
  SELECT o.id AS org_id,
         o.timezone AS org_timezone,
         (SELECT COUNT(*) FROM "Event" e WHERE e."organizationId" = o.id) AS events,
         (SELECT COUNT(*) FROM event_zones z WHERE z.org_id = o.id) AS distinct_iana,
         (SELECT z.timezone FROM event_zones z
           WHERE z.org_id = o.id ORDER BY z.n DESC, z.timezone LIMIT 1) AS mode_timezone
    FROM "Organization" o
)
SELECT
  CASE
    WHEN events = 0 THEN 'no_events'
    WHEN distinct_iana = 0 THEN 'events_but_no_iana'
    WHEN distinct_iana = 1 THEN 'single_iana'
    ELSE 'multi_iana'
  END AS org_class,
  COUNT(*) AS orgs,
  SUM(events) AS events
FROM org_summary
GROUP BY 1
ORDER BY events DESC;
```

`single_iana` is the inference we want. `multi_iana` and `events_but_no_iana` are the leftovers that need a second look. Optional drill (still no PII):

```sql theme={null}
SELECT o.id,
       (SELECT COUNT(*) FROM "Event" e WHERE e."organizationId" = o.id) AS events,
       ARRAY(
         SELECT z.timezone || '×' || z.n::text
           FROM (
             SELECT timezone, COUNT(*) AS n
               FROM "Event"
              WHERE "organizationId" = o.id
                AND timezone IS NOT NULL
              GROUP BY 1
           ) z
          ORDER BY z.n DESC
       ) AS zones
  FROM "Organization" o
 WHERE (
         SELECT COUNT(DISTINCT timezone)
           FROM "Event"
          WHERE "organizationId" = o.id
            AND timezone IS NOT NULL
            AND timezone <> 'UTC'
       ) > 1
 ORDER BY events DESC
 LIMIT 20;
```

### 3. Flex Instant leftovers — can a sibling zone fill the gap?

```sql theme={null}
SELECT
  e.timezone IS NULL OR e.timezone = 'UTC' AS missing_or_utc,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  EXISTS (
    SELECT 1 FROM "Event" sib
     WHERE sib."organizationId" = e."organizationId"
       AND sib.id <> e.id
       AND sib.timezone IS NOT NULL
       AND sib.timezone <> 'UTC'
  ) AS org_has_sibling_iana,
  fq.metadata ? 'timezone' AS quote_has_metadata_tz,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."createdAt" >= TIMESTAMP '2026-07-23 00:27:23'
  AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
  AND fq.show_start_at IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;
```

`missing_or_utc + start_still_instant + org_has_sibling_iana` is the set we can convert without a new product writer.

### 4. Device-offset vs already-wall-clock (the discriminator)

For offset-era offers, project `start_time` through the event zone, else the org's mode IANA. Compare that local calendar day to `crew_offers.date`.

```sql theme={null}
WITH org_mode AS (
  SELECT "organizationId" AS org_id, timezone, COUNT(*) AS n,
         ROW_NUMBER() OVER (PARTITION BY "organizationId" ORDER BY COUNT(*) DESC, timezone) AS rn
    FROM "Event"
   WHERE timezone IS NOT NULL AND timezone <> 'UTC'
   GROUP BY 1, 2
),
inferred AS (
  SELECT o.id,
         COALESCE(e.timezone, m.timezone) AS tz,
         o.date AS offer_day,
         o.start_time,
         CASE
           WHEN COALESCE(e.timezone, m.timezone) IS NULL THEN NULL
           ELSE ((o.start_time AT TIME ZONE 'UTC')
                 AT TIME ZONE COALESCE(e.timezone, m.timezone))
         END AS local_ts
    FROM crew_offers o
    LEFT JOIN "Event" e ON e.id = o.event_id
    LEFT JOIN org_mode m ON m.org_id = o.organization_id AND m.rn = 1
   WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
     AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
     AND o.start_time IS NOT NULL
)
SELECT
  tz IS NULL AS no_inferred_tz,
  (start_time AT TIME ZONE 'UTC')::date IS NOT DISTINCT FROM offer_day AS utc_day_matches_offer,
  local_ts::date IS NOT DISTINCT FROM offer_day AS local_day_matches_offer,
  COUNT(*) AS n
FROM inferred
GROUP BY 1, 2, 3
ORDER BY n DESC;
```

How to read it:

| utc\_day\_matches | local\_day\_matches | Meaning |
| - | - | - |
| true, and hour 10–23 | true | Already wall-clock `Z` — do **not** convert |
| false | true | Device-offset Instant in the inferred zone — convert |
| false | false | Traveling author, wrong inferred zone, or edited — leftover |
| no\_inferred\_tz | | Cannot convert yet |

Hour histogram for the convert-candidate rows only (`utc_day` mismatch, `local_day` match):

```sql theme={null}
WITH org_mode AS (
  SELECT "organizationId" AS org_id, timezone, COUNT(*) AS n,
         ROW_NUMBER() OVER (PARTITION BY "organizationId" ORDER BY COUNT(*) DESC, timezone) AS rn
    FROM "Event"
   WHERE timezone IS NOT NULL AND timezone <> 'UTC'
   GROUP BY 1, 2
)
SELECT
  EXTRACT(HOUR FROM ((o.start_time AT TIME ZONE 'UTC')
    AT TIME ZONE COALESCE(e.timezone, m.timezone)))::int AS local_hour,
  COUNT(*) AS n
FROM crew_offers o
LEFT JOIN "Event" e ON e.id = o.event_id
LEFT JOIN org_mode m ON m.org_id = o.organization_id AND m.rn = 1
WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND o.start_time IS NOT NULL
  AND COALESCE(e.timezone, m.timezone) IS NOT NULL
  AND (o.start_time AT TIME ZONE 'UTC')::date IS DISTINCT FROM o.date
  AND ((o.start_time AT TIME ZONE 'UTC')
       AT TIME ZONE COALESCE(e.timezone, m.timezone))::date
      IS NOT DISTINCT FROM o.date
GROUP BY 1
ORDER BY 1;
```

If this is a tight evening cluster (for example 16–20) and query 2 is mostly `single_iana`, treat device-offset as recoverable. Repeat the same two queries on `member_availability` (`date` + `start_time`) and `run_of_show_items` (`start_time`, join Event for the zone; no DATE key — use `Event.eventDate` UTC day vs local day).

### 5. `eventDate` clock leftover

```sql theme={null}
SELECT
  fq.event_id IS NOT NULL AS flex_linked,
  CASE
    WHEN e."eventDate"::time = TIME '00:00:00' THEN 'midnight'
    WHEN e."eventDate"::time = TIME '12:00:00' THEN 'noon'
    ELSE 'other'
  END AS clock,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
GROUP BY 1, 2
ORDER BY 1, 2;
```

`other` is the only calendar-day leftover after the noon `date_trunc`. For Flex `other` / Instant-day rows, query 3 already says whether a sibling zone exists.

### 6. Flex metadata zone values

```sql theme={null}
SELECT COALESCE(fq.metadata->>'timezone', '<absent>') AS meta_tz,
       COUNT(*) AS n
  FROM flex_quotes fq
  JOIN "Event" e ON e.id = fq.event_id
 WHERE e."createdAt" >= TIMESTAMP '2026-07-23 00:27:23'
   AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
 GROUP BY 1
 ORDER BY n DESC;
```

Prod 2026-08-17: `America/Chicago` × 10, `US/Central` × 1. Both are real `pg_timezone_names` entries. The migration copies that value onto `Event.timezone` when Event is null/`UTC`, then runs the Instant projections. Sibling IANA is unused (minority of orgs).

### 7. UTC hour histograms (same-day offset vs wall-clock)

```sql theme={null}
SELECT 'offer_offset' AS kind,
       EXTRACT(HOUR FROM o.start_time)::int AS utc_hour,
       COUNT(*) AS n
  FROM crew_offers o
 WHERE o.created_at >= TIMESTAMP '2026-04-20 21:31:03'
   AND o.created_at < TIMESTAMP '2026-07-15 00:00:19'
   AND o.start_time IS NOT NULL
 GROUP BY 2
UNION ALL
SELECT 'avail_offset',
       EXTRACT(HOUR FROM a.start_time)::int,
       COUNT(*)
  FROM member_availability a
 WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
   AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
 GROUP BY 2
UNION ALL
SELECT 'event_pre_mobile',
       EXTRACT(HOUR FROM e."startTime")::int,
       COUNT(*)
  FROM "Event" e
 WHERE e."createdAt" < TIMESTAMP '2026-04-14 00:00:00'
   AND e."startTime" IS NOT NULL
 GROUP BY 2
ORDER BY 1, 2;
```

Hours 10–23 on the offer UTC clock, with UTC day = `date`, means those rows already look like wall-clock `Z`. Hours 0–7 are overnight Instants.

Prod 2026-08-17: `offer_offset` hours 13–23 only (20 rows). `event_pre_mobile` hour 19 × 80 of 120. `avail_offset` hour 0 × 625 of 655.

Availability query 4 must be the **org-mode-only** form (`member_availability` has no `event_id`; joining `Event` by org fans out). Prod: 606 both-mismatch, 28 both-match, 21 no tz, **0** Instant next-day. The 606 is the same session-TZ midnight pitfall as query 9, not a dummy date.

### 9. Availability `start_time` day vs `date`

`(timestamptz)::date` uses **session** `TimeZone`. `timestamp AT TIME ZONE 'UTC'` yields a timestamptz, so this is not a UTC day:

```sql theme={null}
-- Session-sensitive — do not use to decide a rewrite.
SELECT
  (a.start_time AT TIME ZONE 'UTC')::date - a.date AS session_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND a.start_time IS NOT NULL
GROUP BY 1
ORDER BY n DESC;
```

Naive `TIMESTAMP` `::date` does not use session TZ. That is the UTC day of the stored digits:

```sql theme={null}
SHOW TimeZone;

SELECT
  a.start_time::date - a.date AS utc_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
GROUP BY 1
ORDER BY n DESC;
```

Prod 2026-08-17 (session-sensitive form): **-1** × **627**, **0** × **28**. That split matches query 7 (hour 0+2 = 627, hour 5+ = 28) in a US zone: UTC midnight → previous local date. Start-day histogram is many real Saturdays (2026-06-13 × 32, 2026-08-29 × 26, …), not a dummy epoch.

All-day writers store `date` + `00:00Z`/`23:59Z` (fake Z today; device-offset Instant in the offset window, e.g. Central `00:00-05:00` → `05:00Z`). **Do not convert** hour-0 rows. Confirm with the session-safe query: expect **0** × \~627. If that is also **-1**, the digits are midnight on `date-1` and still leftover (no writer we want to invert).

### 10. Availability `end_time` pairing (the 28 only)

The hour-5+ / offset-0 rows may be device-offset all-day or timed Instants. Pair clocks before any rewrite.

```sql theme={null}
SELECT
  EXTRACT(HOUR FROM a.start_time)::int AS start_h,
  EXTRACT(HOUR FROM a.end_time)::int AS end_h,
  a.end_time::date - a.start_time::date AS end_utc_days_from_start,
  a.start_time::date - a.date AS start_utc_days_from_avail,
  COUNT(*) AS n
FROM member_availability a
WHERE a.created_at >= TIMESTAMP '2026-04-20 21:31:03'
  AND a.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND EXTRACT(HOUR FROM a.start_time) <> 0
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;
```

Prod 2026-08-17 (DBeaver session `America/Chicago`; naive `::date` is session-safe):

| start\_h | end\_h | end UTC days from start | start UTC days from avail | n | Action |
| - | - | - | - | - | - |
| 5 | 4 | 1 | 0 | 15 | Convert through `America/Chicago` |
| 18 | 22 | 0 | 0 | 4 | Already evening wall-clock — leave |
| 23 | 23 | 0 | 0 | 4 | Leave |
| 2 | 23 | 0 | 0 | 2 | Leave |
| 14 | 23 | 0 | 0 | 1 | Leave |
| 5 | 22 | 0 | 0 | 1 | Timed Instant or long day — leave |
| 22 | 23 | 0 | 0 | 1 | Leave |
| 14 | 4 | 1 | 0 | 1 | Overnight singleton — leave |
| 19 | 23 | 0 | 0 | 1 | Leave |

Session-safe query 9 is **0** × **655**. The DBeaver `-1` was session TZ only.

### 8. `eventDate` `other` clocks

```sql theme={null}
SELECT fq.event_id IS NOT NULL AS flex_linked,
       EXTRACT(HOUR FROM e."eventDate")::int AS h,
       EXTRACT(MINUTE FROM e."eventDate")::int AS m,
       COUNT(*) AS n
  FROM "Event" e
  LEFT JOIN flex_quotes fq ON fq.event_id = e.id
 WHERE e."eventDate"::time <> TIME '00:00:00'
   AND e."eventDate"::time <> TIME '12:00:00'
 GROUP BY 1, 2, 3
 ORDER BY 1, n DESC;
```

### 11. Pre-`#1062` Flex-linked — still Instant?

Create-time is before the Flex Instant writer. A quote attached later can still leave `startTime = show_start_at`.

```sql theme={null}
SELECT
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    ELSE 'other'
  END AS window,
  e.timezone IS NULL OR e.timezone = 'UTC' AS missing_or_utc,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  e."endTime" IS NOT DISTINCT FROM fq.load_out_at AS end_still_instant,
  NULLIF(fq.metadata->>'timezone', 'UTC') IS NOT NULL AS quote_has_real_meta_tz,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND fq.show_start_at IS NOT NULL
GROUP BY 1, 2, 3, 4, 5, 6, 7
ORDER BY n DESC;
```

`start_still_instant = true` is the convert-candidate set. Zone for that set: metadata first, then Event/Venue coords (query 18).

### 12. Flex `eventDate` `other` vs quote Instant

```sql theme={null}
SELECT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  EXTRACT(MINUTE FROM e."eventDate")::int AS event_date_m,
  e."eventDate"::date IS NOT DISTINCT FROM fq.show_start_at::date AS utc_day_matches_show,
  CASE
    WHEN NULLIF(fq.metadata->>'timezone', 'UTC') IS NULL THEN NULL
    ELSE ((fq.show_start_at AT TIME ZONE 'UTC')
          AT TIME ZONE NULLIF(fq.metadata->>'timezone', 'UTC'))::date
         IS NOT DISTINCT FROM e."eventDate"::date
  END AS local_show_day_matches_event_date,
  e."startTime" IS NOT DISTINCT FROM fq.show_start_at AS start_still_instant,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
GROUP BY 1, 2, 3, 4, 5
ORDER BY n DESC;
```

`utc_day_matches_show` + not `local_show_day_matches` + a real metadata IANA is the calendar-day rewrite. `23:00` + utc match is often the Instant stuffed into `eventDate`.

### 13. Non-Flex `eventDate` `other` vs `startTime`

```sql theme={null}
SELECT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  EXTRACT(HOUR FROM e."startTime")::int AS start_h,
  e."startTime" IS NOT NULL AS has_start,
  e."eventDate"::date IS NOT DISTINCT FROM e."startTime"::date AS same_utc_day_as_start,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  e.venue_id IS NOT NULL AS has_venue,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
WHERE fq.event_id IS NULL
  AND e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY n DESC;
```

`event_date_h = start_h` on the same UTC day means the call clock was copied onto `eventDate` — leave digits, maybe later `date_trunc`. Divergent hours without a quote stay leftover.

### 14. Non-Flex starts in the gap and Flex Instant window

```sql theme={null}
SELECT
  CASE
    WHEN e."createdAt" < TIMESTAMP '2026-07-16 20:13:31' THEN 'e_gap_before_web_rfc'
    WHEN e."createdAt" < TIMESTAMP '2026-07-23 00:27:23' THEN 'e_web_z_pre_flex'
    WHEN e."createdAt" < TIMESTAMP '2026-08-16 20:08:29' THEN 'e_flex_instant_overlap'
    ELSE 'other'
  END AS window,
  EXTRACT(HOUR FROM e."startTime")::int AS start_h,
  COUNT(*) AS n
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
WHERE e."startTime" IS NOT NULL
  AND fq.event_id IS NULL
  AND e."createdAt" >= TIMESTAMP '2026-05-06 14:53:38'
  AND e."createdAt" < TIMESTAMP '2026-08-16 20:08:29'
GROUP BY 1, 2
ORDER BY 1, 2;
```

Hours 10–23 → already wall-clock; fill a zone label later. Hours 0–7 → Instant candidates; do not convert on a CT prior alone.

### 15. Run of show offset era

```sql theme={null}
SELECT
  CASE
    WHEN i.created_at < TIMESTAMP '2026-06-16 00:00:00' THEN 'ros_before_offset'
    WHEN i.created_at < TIMESTAMP '2026-07-15 00:00:19' THEN 'ros_device_offset'
    ELSE 'ros_late_z'
  END AS window,
  COUNT(*) AS items,
  COUNT(i.start_time) AS with_start
FROM run_of_show_items i
GROUP BY 1
ORDER BY 1;
```

```sql theme={null}
SELECT
  EXTRACT(HOUR FROM i.start_time)::int AS start_h,
  EXTRACT(HOUR FROM i.end_time)::int AS end_h,
  i.end_time::date - i.start_time::date AS end_utc_days_from_start,
  i.start_time::date - e."eventDate"::date AS start_utc_days_from_event,
  COUNT(*) AS n
FROM run_of_show_items i
JOIN "Event" e ON e.id = i.event_id
WHERE i.created_at >= TIMESTAMP '2026-06-16 00:00:00'
  AND i.created_at < TIMESTAMP '2026-07-15 00:00:19'
  AND i.start_time IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;
```

Read like availability query 10. A tight `5 → 4` next-day cluster is UTC-5 all-day Instant. Evening same-day hours are already `Z`.

### 16. Zone-signal coverage on Instant candidates

Pre-`#1062` Flex rows that still match the quote Instant:

```sql theme={null}
SELECT
  NULLIF(fq.metadata->>'timezone', 'UTC') IS NOT NULL AS has_meta_tz,
  (
    e.latitude IS NOT NULL AND e.longitude IS NOT NULL
    AND NOT (e.latitude = 0 AND e.longitude = 0)
  ) AS event_has_coords,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  NULLIF(o.timezone, 'UTC') IS NOT NULL AS org_has_tz,
  COUNT(*) AS n
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
JOIN "Organization" o ON o.id = e."organizationId"
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND e."startTime" IS NOT DISTINCT FROM fq.show_start_at
GROUP BY 1, 2, 3, 4
ORDER BY n DESC;
```

If `has_meta_tz` covers them, extend the existing Flex UPDATEs (widen `createdAt`). If only coords, a `tz-lookup` pass can fill `Event.timezone` and then the same UPDATEs run.

### 17. Venue state band (CT prior, paste-safe)

`Venue.address` is `{ line1, city, state, postal_code, country }`. `state` may be `IL` or `Illinois`.

```sql theme={null}
SELECT
  CASE
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'IL', 'ILLINOIS', 'TX', 'TEXAS', 'WI', 'WISCONSIN', 'MN', 'MINNESOTA',
      'MO', 'MISSOURI', 'IA', 'IOWA', 'AR', 'ARKANSAS', 'LA', 'LOUISIANA',
      'OK', 'OKLAHOMA', 'KS', 'KANSAS', 'NE', 'NEBRASKA', 'ND', 'NORTH DAKOTA',
      'SD', 'SOUTH DAKOTA', 'AL', 'ALABAMA', 'MS', 'MISSISSIPPI', 'TN', 'TENNESSEE'
    ) THEN 'centralish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'NY', 'NEW YORK', 'NJ', 'NEW JERSEY', 'PA', 'PENNSYLVANIA', 'FL', 'FLORIDA',
      'GA', 'GEORGIA', 'NC', 'NORTH CAROLINA', 'SC', 'SOUTH CAROLINA',
      'VA', 'VIRGINIA', 'MD', 'MARYLAND', 'DC', 'MA', 'MASSACHUSETTS',
      'CT', 'CONNECTICUT', 'OH', 'OHIO', 'MI', 'MICHIGAN', 'IN', 'INDIANA'
    ) THEN 'easternish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'CA', 'CALIFORNIA', 'WA', 'WASHINGTON', 'OR', 'OREGON', 'NV', 'NEVADA'
    ) THEN 'pacificish'
    WHEN upper(COALESCE(v.address->>'state', '')) IN (
      'CO', 'COLORADO', 'AZ', 'ARIZONA', 'UT', 'UTAH', 'NM', 'NEW MEXICO',
      'MT', 'MONTANA', 'WY', 'WYOMING', 'ID', 'IDAHO'
    ) THEN 'mountainish'
    WHEN v.id IS NULL THEN 'no_venue'
    WHEN v.address->>'state' IS NULL OR v.address->>'state' = '' THEN 'venue_no_state'
    ELSE 'other_or_non_us'
  END AS state_band,
  COUNT(*) AS events,
  COUNT(*) FILTER (
    WHERE e.timezone IS NULL OR e.timezone = 'UTC'
  ) AS missing_event_tz
FROM "Event" e
LEFT JOIN "Venue" v ON v.id = e.venue_id
GROUP BY 1
ORDER BY events DESC;
```

`centralish` includes Indiana/some TN edges that are not always CT — query 18 is the check. This is a prior for **label fill**, not for rewriting `17:00Z`.

### 18. Assessment — venues on Instant-candidate Events (do not paste)

Look at the grid. Reply with counts by state / “looks CT” / outliers. No names, cities, or org ids.

```sql theme={null}
SELECT DISTINCT
  v.name AS venue_name,
  v.address->>'city' AS city,
  v.address->>'state' AS state,
  (
    v.latitude IS NOT NULL AND v.longitude IS NOT NULL
    AND NOT (v.latitude = 0 AND v.longitude = 0)
  ) AS venue_has_coords,
  NULLIF(fq.metadata->>'timezone', 'UTC') AS meta_tz
FROM "Event" e
JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE e."createdAt" < TIMESTAMP '2026-07-23 00:27:23'
  AND e."startTime" IS NOT DISTINCT FROM fq.show_start_at
ORDER BY 3, 2, 1;
```

### 19. Assessment — venues for the one multi-IANA org (do not paste)

```sql theme={null}
WITH multi AS (
  SELECT e."organizationId" AS org_id
    FROM "Event" e
   WHERE e.timezone IS NOT NULL AND e.timezone <> 'UTC'
   GROUP BY 1
  HAVING COUNT(DISTINCT e.timezone) > 1
)
SELECT DISTINCT
  e.timezone AS event_tz,
  v.name AS venue_name,
  v.address->>'city' AS city,
  v.address->>'state' AS state
FROM "Event" e
JOIN multi m ON m.org_id = e."organizationId"
LEFT JOIN "Venue" v ON v.id = e.venue_id
ORDER BY 1, 4, 3, 2;
```

Summarize as “Chicago-labeled rows are IL venues; Detroit-labeled rows are MI” (or not). That decides whether venue coords beat an org-level CT default.

### 20. Assessment — non-Flex `eventDate` `other` venues (do not paste)

```sql theme={null}
SELECT DISTINCT
  EXTRACT(HOUR FROM e."eventDate")::int AS event_date_h,
  v.address->>'state' AS state,
  v.name AS venue_name
FROM "Event" e
LEFT JOIN flex_quotes fq ON fq.event_id = e.id
LEFT JOIN "Venue" v ON v.id = e.venue_id
WHERE fq.event_id IS NULL
  AND e."eventDate"::time <> TIME '00:00:00'
  AND e."eventDate"::time <> TIME '12:00:00'
ORDER BY 1, 2, 3;
```

Summarize as “the 17:00 cluster is all IL” vs “mixed coasts.” Mixed coasts → do not `date_trunc` or convert on a CT prior.

## Related

* [ENG-580](https://linear.app/soundcheck-live/issue/ENG-580/event-call-times-utc-as-wall-clock-encoding-vs-orgevent-timezone-docs)
* [ENG-581](https://linear.app/soundcheck-live/issue/ENG-581/ledgeritemdate-invitation-payable-backfill-leftovers-vs-eventdate) / PR #1370 — ledger calendar day
* [Database](https://docs.soundchecklive.io/engineering/backend/database.md) — never edit applied migration SQL


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