cursor/eng-580-timebound-clock-alignment-b9d9. Dump counts are the post-Event-align eng580-fresh snapshot (localhost:5436, availability migration not applied — counts below simulate 20260817180000_event_clock_alignment).
Locked target
NaiveTIMESTAMP(3) call clocks are UTC Instants. Date-key columns stay calendar dates. member_availability.timezone is the IANA for that row’s date.
Two formulas, never mixed (same as Event):
Do not run B on Instant all-day (
5→4) or on leftover hour-0 walls this pass — those become (null, null).
Availability writers
Clients always send UTC Instants (RFC3339 with a real offset, typicallyZ) plus IANA.
The server rejects a write when:
- a present clock’s calendar day in the row IANA is not
date(start_time is not on date in timezone/end_time is not on date in timezone) - both clocks are present and
end <= start(end_time must be after start_time) - IANA is missing and the org has no resolvable timezone
splitOvernight.
All-day from the client is (null, null) (or omitted clocks). The server also collapses UTC 00:00–23:59* and local full-day sentinels to (null, null) on write.
Web and mobile encode local wall → Instant with Date.toISOString() and send the viewer IANA. They must not use timeToRFC3339 (fake Z) for availability.
Invitation, offer, and request accept
These writers copy UTC Instants ontomember_availability. They do not gate those Instants to event_date / offer date / request date. A Chicago 10pm Instant can be the next UTC calendar day and still belong on that calendar date.
00:00–23:59* (UTC date sentinel) and local full-day sentinels collapse to (null, null) before insert.
Invitation IANA for the new availability row is
Event.timezone, else org, else UTC. Offer and request use the same ladder via getEventTimeZone(eventId, orgId).
TIME_WINDOW (Specific time) requires start/end on every request day. availability_requests.start_time / end_time are still two full timestamps (same TIMESTAMP(3) as Event clocks) — a write-through of the first day’s Instants for emails/legacy, not a time-of-day type. Respond does not paste those UTC hours onto other days.
Event IANA is still optional
Event.timezone stays String?. 20260817180000 does not SET NOT NULL.
Raw prod (prod-postgres-20260818-post-stamp.dump, clock migrations not applied): 916 Events, 63 real IANA, 0 'UTC', 853 null. The 819 / 97 leftover split is what 17180000 does locally (Flex + sibling + location fill) — those labels are not in prod yet. After that file on the earlier 2026-08-18 PM dump: 0 hour 10–23 starts lack IANA; remaining nulls have no startTime.
20260817180000_event_clock_alignment copies Organization.timezone onto leftover null/UTC Events when the org already has a real IANA. It does not overwrite a real Event IANA. Location fill in 17180000 uses Event.location + Event.longitude only (not venues). Sibling fill skips multi-IANA orgs. WA / NE are not in the location CASE.
Event.startTime / endTime are already nullable. Making Event IANA required is not a prerequisite for nullable clocks.
Migration 20260817180000_event_clock_alignment
One file, this order: Event IANA + Instant-ize → availability nullable clocks + all-day null → offer/request Instant-ize → availability evening Instant-ize → overnight hour-0 ends → null → org TZ copy onto leftover null/UTC Events.
Overnight hour-0 end nulling stays after every hour 10–23 Instant-ize. Availability end_time is still NOT NULL until the ALTER. Chicago 16:00–18:00 walls Instant-ize to 21:00–23:00 (still hour 10–23); overnight then Formula-B’s mixed pairs and nulls the end. Instant-ize after that would treat the start as wall again. Event 19:00 walls Instant-ize to hour 0 first; overnight then nulls midnight open bounds. Event overnight nulling could sit immediately after Event Instant-ize (it does not Formula B Event starts). Offer/availability overnight cannot. Org-copy may stay after overnight.
- Drop NOT NULL on availability clocks; add required
timezone. - Fill IANA: org (skip
'UTC'), else single Event IANA on the org (US/Central≡America/Chicago), else infer from Instant all-day shape (5→4Chicago,4→3New York,7→6Los Angeles), else'UTC'. - Null hour-0 same-UTC-day
00:00–23:59*sentinels. - Null Instant all-day (
endnext UTC day, ~23h duration, minute-0 start / minute-59 end). That includes Ben Stacy.
org_3G8sNsXlz3GINWkmV0h4XSvJEMx) has 0 Events. All 27 availability rows are 05:00 → 04:59 next UTC day — Instant-encoded all-day in UTC−5, not a 5am–4am gig. Infer Chicago and null. One other org has 15 of the same shape (42 Instant all-day total).
Dump findings (2026-08-18)
Availability before the simulated null: 1076 rows. 884 hour-0 all-day, 42 Instant all-day, 8end < start, 0 start::date ≠ date, 43 end::date ≠ date.
After fill + null: 926 all-day (null, null), 150 timed remain.
IANA leftover census (prod stamp 2026-08-18)
Clock migrations are not in prod yet. Dumpprod-postgres-20260818-post-stamp.dump (sha256 0183bec469b9c1873beb7d8df3dc6ddf062beec96ab0a0a735ca9c6fe711774b).
Manual prod writes (do not put these IDs in Prisma SQL):
- 2026-08-17: 12 timed-start Event IANAs (leftover-1). Benji Uzo + Test org
Organization.timezone = America/Chicago. - 2026-08-18: 13 org defaults + Event labels on five orgs that had zero Event IANA (no
startTime). Mixed-org Event labels were never in prod — sibling/location fill only exists after17180000.
America/Argentina/San_Juan). All 13 stamped orgs match the intended IANA. The five Event fills landed (Murder House / 3 Room Tent Los Angeles; Animal Andino San Juan; Gone With The West Event Denver + org Chicago; testing Chicago). 0 mixed orgs still have null org TZ.
This file does not overwrite a real Event IANA (WHERE timezone IS NULL OR timezone = 'UTC'). Filling Soundcheck Team leftover nulls with Chicago is a blank fill, not a rewrite of New York.
Location fill gaps that needed a human stamp: WA and NE are not in the 17180000 CASE; CA/TN need a real longitude (dummy 0,0 refuses). Fill does not read venues. PostHog $geoip_time_zone is last-IP, not home base (Spain IP on the San Juan festival). 3 Room Tent has no public home base — last IP only.
After this file on the earlier PM dump, unlabeled Events dropped to 85 (all no startTime). Deploy it after the 2026-08-18 org stamps so Soundcheck leftovers copy Chicago. Leftover empty orgs are Chicago-filled so the column can be required; ENG-588 replaces those Chicago placeholders with Clerk / PostHog where known.
Should other tables get an IANA column?
Availability needed its own required IANA because the row has a calendardate, Instant clocks that must agree with that date, no event_id, and recurring / open-bound resolution that cannot inherit a gig zone.
Do not add a parallel
timezone on every table that happens to have start_time. Add IANA where a calendar date and Instant clocks must agree and there is no Event to inherit. That is availability. Engagement accept already stores the resolved IANA on the availability row it writes.
Locked 2026-08-18 evening
Instant writers and20260817180000_event_clock_alignment ship together. The 2026-08-18 evening dump still has old-writer fake-Z walls after the cutoff — do not treat those rows as Instant-era.
Writer floor (locked): device-offset Instant updated_at < 2026-07-15 00:00:19 → leave. Fake-Z from that timestamp onward Formula B in migration, SQL upper bound < 2026-09-15. Instant-writer freeze is dump now() 2026-08-20 23:19:32 UTC. Phase 1/2 code writers on the branch store real Instants — deploy with the migration.
00:30 is a real 12:30am Instant, not an open bound. Only exact 00:00 (UTC leftover midnight or IANA civil midnight) nulls as open-ended.
Offer / request expires_at: leftover walls Instant-ize through Event IANA, or org IANA when the row has no event. All hours. Not compared to Event start. Not required to match offer.date. Do not Instant-ize engagement_sms_reply_codes.expires_at.
Offer accept (ENG-587): end <= start skip was a guard against inverted clocks, not a product rule. Accept now splits overnight into two civil-day member_availability rows (day D start→open EOD; day D+1 open SOD→end). Exact 00:00 stays one start→open row. start === end still skips. Manual create/update still reject overnight in one request. Request respond still throws — dump has 0 request inversions.
Org calendar: auto-block availability notes stay hidden (isAutoStaffingAvailabilityNote). Staffing blocks come from listAcceptedOfferStaffingByOrgAndDateRange / invitation sibling. Two availability rows alone do not paint two calendar blocks. Staffing emits one block per civil day and fetches o.date >= from - 1 day so the morning-after day is visible. Dedupe key includes date, so both days survive. API-00 (Phase 4/5 branch): accepted all-day offer rows with null clocks resolve to IANA civil-day bounds (civilDayBoundsInTimeZone), not UTC 00:00Z..23:59:59Z. CAL-03 / STAFFING-02 (branch): accepted ICS/feeds project per civil day; staffing truth uses recipient-day rows.
Engagement inbox (CLIENT-02, Phase 4/5 branch): user and org engagement list/get responses include event_timezone. Display keeps RFC3339 Instants until a final zoned format lands. Crew-offer create/update/send/accept/decline mutations attach the resolved Event→org zone, not only list/get.
Flex #1328 / #1387: those PRs stored Formula A of flex_quotes Instants as Event fake-Z walls so leftover UTC readers showed the watch time. The Flex writer now copies the quote Instants again. 20260817180000 restores rows that still equal that Formula A projection. Origin/dev 20260818140000 Instant→walls Flex/HubSpot-linked quote-equal rows; do not edit that file (prod checksum). It is already applied on prod (2026-08-18 20:15:38Z) and stamped updatedAt, so the backfill hour is not a writer discriminator. 20260819160000_flex_quote_instant_restore undoes 18140000 after merge / on fresh apply order. Quote columns stay Instants. Phase 2 Instant readers are on the branch — deploy them with the migration so restored Instants display in Event IANA, not as UTC digits.
2026-08-19 flex-wall dump (clock migrations not applied): 36 Flex-linked Events. Start: 3 still quote Instant, 33 diverged, 31 Formula A. End: 10 quote Instant, 26 diverged, 24 Formula A. The other diverged clocks are not Formula A — leave them.
Still open
- Leftover timed availability. Same-UTC-day hour 10–23 evenings, hour 1–9 walls, and hour-0 prefixes (
0→00:30,0→1–9,0→evening) Instant-ize in20260817180000_event_clock_alignmentthrough row IANA whenupdated_at >= 2026-07-15 00:00:19and< 2026-09-15(applied SQL bound; freeze is 2026-08-20 23:19:32 UTC). Device-offset Instant writes (#392.. web fake-Z#1012) leave. Overnight exact00:00ends null (open bound); inverted:01–:59Instant-ize as next local morning. Instant UTC-crossing14:00→04:59leaves. Phase 2 readers format any remaining present clocks through row/Event IANA after deploy. - Offer / offer-day Instant-ize. Hour 10–23 wall
Zwith Event IANA is done in20260817180000_event_clock_alignment(skip Event-equal Instants; skip hour 0–9). Inverted hour-0 ends null in20260817180000_event_clock_alignment. Hour-0 ends withend > start(typically Formula B of a 19:00 wall →00:00UTC) stay Instants. ENG-587 is offer accept of overnight windows, not this leftover rewrite. - Require
Event.timezone. Allowed only after filling leftover nulls. Raw prod 2026-08-18 post-stamp still has 853 unlabeled Events because this file is not deployed. After it on the PM dump, 85 Events had no org TZ to copy; the 2026-08-18 org stamps close the mixed-org gap so org copy can fill Soundcheck Team leftovers (TN/CA strings with dummy0,0coords — location fill refused them). Not needed for nullable Event clocks. - Orgs with no Events. Leftover empty org timezones are filled with America/Chicago so the column can be required. ENG-588 replaces those Chicago placeholders with Clerk / PostHog where known.
- Mobile generated client. Availability editor + Instant display are in the tree; commit is gated on
npm run generate:api-clientmatching services.