449988251): Event save passes Instants through, ICS/Google emit IANA walls, the shared encoder is calendar-correct, migration Formula B SQL still uses ceiling < 2026-09-15; Instant-writer freeze is dump now() 2026-08-20 23:19:32 UTC (do not rewrite applied SQL), and Flex restore uses the metadata → Event → org zone ladder with DISTINCT ON (event_id) … ORDER BY updated_at DESC. Phase 2 code (same branch, not deployed): leftover call-clock readers format and rebase through Event (or row) IANA — notifications, call sheets, gig documents, duplicate/RoS, multiday ICS, inbox/reconciliation, and web/mobile display. Phase 4 + Phase 5 code (same branch, not deployed): web timeline civil dayDate from Event IANA; mobile schedule PUT Instant encode with past_midnight retention and per-row Event-IANA civil dates; 20260819170000_event_schedule_item_formula_b schedule-item Formula B (hour 10–23 guard, overnight inverted ends through hour 0–9, invalid Event IANA → org); one Event→org→default zone for Google/offer topology (including dedicated create); Staffing Board / invite / Edit Offer encode through Event IANA; accepted ICS/feed day projection and recipient-day staffing truth; portal/storefront emit resolved Event→org IANA on the API contract; independent duplicate schedule civil-day offsets; projection shift-by-gap DST policy (Chicago 02:30 → 03:30, not clamp 03:00); engagement event_timezone; accepted all-day staffing IANA civil-day bounds; RoS dirty autosave pins the decode zone; OpenAPI snapshot for new engagement fields. This pass (branch only, not deployed): MOBILE-01 (schedule civil-day grouping/save), NOTIFY-01 (cancellation civil dates), CAL-03 (accepted ICS/feed duration contract), CLIENT-01 (RoS second-save zone reseed), CAL-02 (resolved Event DTO timezone), WEB-02 (CRM civil dates), MOBILE-02 (DayStripPreview divergent chip), CONTRACT-01 (event_timezone on OrgEngagement). Migrations 20260817180000_event_clock_alignment and 20260819170000_* are not applied in prod on cited dumps. Instant writers are not in production.
Ticket: ENG-580.
Prod counts are the 2026-08-17 snapshot. Inventory was run from DBeaver with session
America/Chicago, not the deployed app. Naive TIMESTAMP ::date / ::time / EXTRACT(HOUR FROM ts) do not use session TZ. (timestamptz)::date does — that is how query 9 first reported a fake -1 day on availability.What went wrong
Crew-facing call times and some outbound mail (the Mapache thread: emails showed12:00–6:00 PM while the gig UI showed 5:00–11:00 PM) disagreed by several hours. The same digits were being read two ways.
Docs and Prisma comments said typed clocks were UTC Instants. Render them in Event.timezone, falling back to Organization.timezone, falling back to UTC. The comment on Organization.timezone still says timestamps are stored as instants and Null = UTC.
Runtime did the opposite for almost every product writer. An admin typing 17:00 stored naive TIMESTAMP(3) digits 2026-08-05 17:00:00 with a fake Z on the wire. The watch time is the venue wall clock. The IANA zone, when present, is a label for display — not the key that turns an Instant back into a wall clock.
Will’s SQL-migration rule is still the default for the rest of the schema: naive timestamps are UTC Instants; product conversion belongs in the app. Call-time runtime is the exception to that rule. A migration that treats every 17:00Z as an Instant and projects it through America/Chicago recreates Mapache in reverse (17:00 becomes 12:00).
Event.timezone or org timezone null does not mean the digits are Instants. It means no IANA label is stored. Readers that honor the old comment then format the wall-clock digits as UTC. Flex create without metadata.timezone writes Event.timezone = 'UTC' — the same lie, not proof the clocks are Instants.
Organization.timezone still has no API writer. This Event file fills it when an org’s Events have exactly one real IANA after collapsing 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 Event labels. It does not overwrite a real Event IANA. It is a label fallback for avail Formula B / display, not a convert-everything-as-CT prior.
Target meaning (storage)
NaiveTIMESTAMP(3) call clocks are UTC Instants. Will’s SQL rule has no call-time exception. Event.timezone is a display IANA. Date-key columns stay calendar dates — they are not venue-midnight Instants.
Two corrections, never mixed:
- Fill the zone label before wall → Instant. A CT prior is allowed for a label if venues agree. It is not a convert zone.
- Rewrite wall digits to UTC only when the row is proven wall-clock and the Instant’s zone is a real IANA. Skip rows that already equal
show_start_at/ the avail5→4pair. Wall→UTC through a guessedAmerica/Chicagodiscards the watch time.
How we chose to fix it
Not “convert every row in a date range.” Time-bounded means one explicit permutation per writer window: rewrite, no-op (already Instant or already a calendar date), or leftover.created_at only names the writer if the row was never saved again. A later gig-header save re-encodes the clocks. Flex Instant rows are left when startTime still equals flex_quotes.show_start_at.
Never edit an applied migration (20260307 and friends). The work is a new file, 20260817180000_event_clock_alignment. Prisma wraps it; the file has no BEGIN/COMMIT. Wall → Instant is ((ts AT TIME ZONE iana) AT TIME ZONE 'UTC'). Calendar-day projection from a quote Instant is still ((instant AT TIME ZONE 'UTC') AT TIME ZONE iana). timezone('UTC', ts) is not used.
Ledger stay on ENG-581 / PR #1370. Do not mix LedgerItem.date into this Event-clock pass.
Writer eras
Approximate UTCcreated_at bounds vs the PR that shipped each writer:
Web wrote wall-clock
Z in the same calendar dates as Flex Instant. Wall → Instant on a row that still equals show_start_at (22:00 treated as 10pm Chicago) writes 03:00 the next UTC day. Skip quote-equal rows.
Prod inventory (2026-08-17)
Scale (query 1): 916 events (189 withstartTime, 37 Flex-linked). Offers 116. Availability 1,046. Empty in that snapshot: early-Z offer/avail, then-empty post-#1328 Flex Event window. Later #1328 / #1387 writes are Formula A walls restored from the quote Instant.
Orgs (query 2): 165 orgs / 596 events have events but no real IANA. 12 orgs / 314 events are single-IANA. 1 org is multi-IANA (Chicago + Detroit, 6 events). Sibling-zone inference is a minority path. Organization.timezone is not a product writer.
Flex Instant window (#1062 .. #1328): 11 linked events. All Event.timezone null/UTC. All quotes have metadata.timezone: America/Chicago × 10, US/Central × 1 (real pg_timezone_names, not 'UTC'). 10 still match show_start_at — already Instant; fill the label only. The 11th diverged (wall digits); wall → Instant through the filled IANA.
Offers, offset era: 20 with start_time. Query 4: 15 already match UTC day and local day to offer.date; 5 have no inferred tz; 0 next-day Instant. Hours 13–23 only. Wall-clock Z. Instant-ize in this file when a zone exists.
Availability, offset era: 655 with start_time. The Event-join form of query 4 fans out (member_availability has no event_id) — ignore those inflated counts. Org-mode-only query 4 looked like 606 both-mismatch because DBeaver Chicago turned UTC midnight into the previous local evening.
Query 7: hour 0 × 625. Query 9 session-sensitive form: -1 × 627, 0 × 28. Session-safe start_time::date - date: 0 × 655. Start days are real Saturday-heavy availability dates, not a dummy epoch.
All-day writers store date + 00:00Z / 23:59Z (fake Z today; device-offset Instant in the offset window). Hour 0 × 625 is wall-clock all-day — this file nulls it with a required IANA. Do not Formula-B 00:00 Chicago → 05:00 UTC. Do not use a CT prior as the convert zone.
Query 10 on the 30 non-hour-0 rows: 15 are start hour 5, end hour 4 the next UTC day, start day = date. That pair is already a UTC Instant (00:00-05:00 / 23:59-05:00). Leave. The other 15 (18–22, 23–23, 2–23, singletons) are wall leftovers.
Pre-mobile Event starts: hour 19 × 80 of 120. Typed 7pm Z. Wall → Instant when Event.timezone is a real IANA.
eventDate clock: non-Flex midnight 145 / noon 690 / other 44. Flex midnight 10 / noon 8 / other 19. Noon date_trunc is the bulk calendar-day win (698). Flex other is 23:00 × 12 plus sparse hours — still needs query 12 vs show_start_at. Non-Flex other (5:00 × 8, 17:00 × 8, …) has no quote; do not Instant-convert on a CT prior.
25 Flex-linked in e_gap_before_web_rfc (plus 1 in e_web_z_pre_flex): created before the Flex Instant writer; quote attached later. The Instant-window UPDATEs do not touch them. Query 11 asks whether startTime still equals show_start_at.
RoS: query 15 — 12 items / 12 sections, 0 clocks (closed).
What we shipped
Migration (pre-deploy on cited dumps)
Migration20260817180000_event_clock_alignment plus eventClockCohortAlignment.integration.test.ts (real Postgres on :5434). Cohort detail stays on these engineering pages. Public changelog Instant wording stays staged until deploy — do not publish independently of the migration + reader cutover.
Same file also: availability nullable clocks + required IANA, hour-0 /
5→4 all-day null, offer/request/crew_offer_days evening Instant-ize, overnight hour-0 end nulling, and RoS clocks through Event IANA (prod dump has 0 RoS clocks). Formula B uses #1012 floor 2026-07-15 00:00:19 and SQL upper bound < 2026-09-15 on updated_at / updatedAt (applied bound kept; Instant-writer freeze is dump now() 2026-08-20 23:19:32 UTC). Event Formula B hour 0–9 uses writer windows [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.
Integration fixtures: Flex Instant with Event zone (digits stay Instant); Flex metadata-only zone (fill, digits stay Instant); Flex leftover with no zone; already-edited Flex wall Z (wall → Instant); web noon (date only; no zone → start leftover); web wall-clock with Chicago (17:00 → 22:00); offset-era offer (untouched); Central all-day availability Instant (null after all-day pass); hour-0 all-day (null after all-day pass); 18:00–22:00 evening window (Instant-ize when row IANA exists).
Instant readers (Phase 2 + Phase 4/5 branch code)
Call-clock paths that usedtimeZone: 'UTC' or getUTCHours() format or rebase stored Instants through the resolved Event (or row) IANA where wired on cursor/eng-580-timebound-clock-alignment-b9d9. None of this is deployed production behavior until migration + reader cutover ship together.
respondedAt / occurred_at stay on their existing viewer/UTC formatting path. Import/CRM (SERVER-07/08) and HubSpot-Flex n8n (SERVER-09) remain a later pass unless a live inbound path writes zone-less datetimes in prod.
Fresh dump leftovers (2026-08-19 pm — classify only, do not convert now)
Dumpprod-postgres-20260819-pm.dump (920 Events, restored on eng580-pm:5443; taken 2026-08-19 22:29 UTC). Instant writers are not in production — starts still cluster at hour 18–19; 0 rows are post-ceiling on the prior 2026-08-20 bound.
Formula B classifier (2026-08-19 pm dump, SQL not applied): 35 Event evening convert (not Flex-equal); 109 schedule evening convert (not Event-equal, not skip-listed); 5 schedule skip-as-Instant ids; 7 other low-hour schedule starts unconverted; MIG-03 event-less request parents 0; MIG-04 multi-quote Events 0. Applied SQL ceiling remains < 2026-09-15. Instant-writer freeze is dump now() 2026-08-20 23:19:32 UTC — do not rewrite applied migrations.
Not converted in this pass (record for a later wave):
MIG-02/03/04 are empty on this dump — add cheap fixtures later; do not block this pass.
Candidate convert sets on this dump (evening-shaped; Instant writers not live):
At Instant-writer deploy: freeze the Formula-B ceiling to that deploy instant, take a fresh dump, and Formula-B only rows with
updatedAt in (prior_ceiling, freeze]. Rows after freeze must not convert.
Classified leftovers
Already Instant — do not wall→UTC:- Flex
startTime = show_start_at(the 10). - Availability
5→4next UTC day (15). respondedAt,createdAt, FlexQuote load/show/out.
- Device-offset-era offers (hours 13–23) — wall
Zin dump; Formula B in this file when Event IANA exists. #379mobile Events (0 starts).- Pre-mobile / web Event starts with null/
UTCtimezone (165 orgs / 596 events have no IANA). - Empty writer windows.
Organization.timezone(now filled for single-IANA orgs; still no product writer).- Ledger dates (ENG-581).
5→4 all-day are nulled in this file (not Formula-B’d). Same-UTC-day hour 10–23 evening avail Instant-izes here. crew_offer_days explicit evening clocks (81) Instant-ize with parent offer rules.
Still open
Prove wall-clock (or already-Instant) before choosing a convert zone.
Zone ladder, in order: real
Event.timezone; Flex metadata.timezone (skip UTC); tz-lookup on Event then Venue lat/lng (skip 0,0 — same helper web uses on save; api-events has no server lookup); sibling Event IANA (minority); Organization.timezone (this Event file fills single-IANA orgs; prod also has the 2026-08-17 test-org writes and the 2026-08-18 mixed / leftover-Event stamps); Venue.address.state; default America/Chicago for label fill only after a venue sample agrees. Migration location fill does not read venues; WA / NE are not in the CASE.
Hour-0 availability (date + 00:00Z/23:59Z) is “all day on this calendar day.” The DATE is zoneless; the timestamps were a sentinel in Instant-shaped columns. New writes use (null,null) plus a required IANA. 5→4 is the same all-day Instant (local midnight → 23:59 in UTC−5), not a timed gig — Ben Stacy’s 27 rows are this shape and have 0 Events. 20260817180000_event_clock_alignment fills timezone (org / single Event IANA / 5→4/4→3/7→6 infer / UTC) and nulls those sentinels. Offers are three orgs (Mapache, Soundcheck HQ, Demo Group). Availability is 18 orgs — not Mapache-only.
Venue has no timezone column. FlexQuote.venue_id is a bare string, not a Prisma FK. The Chicago+Detroit org is why venue coords beat an org-level CT default (query 19 — summarize).
Queries 1–17 are paste-safe counts. Queries 18–20 return venue names; reply with a one-liner (“5 venues, all IL, metadata Chicago”), not the grid.
Session TZ (DBeaver)
Inventory ran on a Central laptop, not in the app. That is fine for naive::date / ::time. It is fatal for (timestamp AT TIME ZONE 'UTC')::date, which yields a timestamptz and then a session date. UTC midnight in America/Chicago is the previous evening — that produced availability -1 × 627 until we used start_time::date - date (0 × 655).
Local calendar day, when you need it: ((ts AT TIME ZONE 'UTC') AT TIME ZONE iana)::date. The outer AT TIME ZONE returns naive local digits; ::date on those is session-safe.
Out of scope
- ENG-581 / PR #1370 —
LedgerItem.dateas a business date, snap open payables on gig reschedule, restore applied20260307midnight-equality text. - API / client writers and readers — locked contract is availability and engagement semantics. Phase 1 Event save passes Instants through; Phase 2 + Phase 4/5 branch readers format/rebase call clocks through Event IANA (see Instant readers table — not prod). Availability create/update require UTC Instants that agree with
date+ IANA. Invitation / offer / request accept copy Instants and collapse00:00–23:59*to(null,null).Event.timezoneis still optional (raw prod 2026-08-18 post-stamp: 63 real IANA / 853 null;17180000on a dump fills most of those). Do not add IANA columns to offer/request/invitation this pass — inherit Event → org onto the availability row. Deploy migrations + reader cutover together; readers alone on unmigrated rows still misread wall digits. - Rewriting applied migrations.
- A “convert everything as CT” pass. Wall proof, then zone. Label fill can use a CT prior; digit rewrite cannot.
show_start_at is an Instant. When it disagrees with Event.eventDate, the gig start day wins. Mapache 2026-08-17: 33/36 local days match; the three mismatches are a Sept 3 gig with an Oct 30 quote, and two multi-day gigs whose quote Instant is day 2. Leave those date keys. The −8-day draft was confirmed as July 28 and aligned before deploy. A leftover clock on a left date key is still stripped to midnight on the stored day.
Mapache leftover eventDate clocks (hour 5/6, 17/18, and 13–20) are almost all one createdAt batch (2026-03-25 14:41:04.91). Git supports Chicago local midnight/noon stored as UTC Instants through the Go {seconds,nanos} passthrough — not Studio UTC noon (12:00:00Z). Writer client is not in-repo. Treat the batch as Central-created. Those clocks do not flip the calendar day vs UTC. This file strips them in two steps (midnight on the stored day, then date_trunc). Two internal-test IANA leftover clocks (ET 09:14, PT 17:50; zones confirmed correct) use the same two-step in the row’s own timezone. After step 1 the value is 00:00 UTC, so step 2 cannot reuse a US-zone projection guard.
Merge / deploy
Formula-B ceiling (MIG-01): Both20260817180000_event_clock_alignment and 20260819170000_event_schedule_item_formula_b cap Formula-B rewrites at updated_at / updatedAt < 2026-09-15 in applied SQL. Instant-writer freeze is dump now() 2026-08-20 23:19:32 UTC. Do not rewrite applied migrations to chase the freeze instant. Rows written after freeze must not convert at runtime.
Migration order (OPS-01): Before merge, reconcile this branch’s 20260817180000_*, 20260818*, 20260819160000_flex_quote_instant_restore, and 20260819170000_event_schedule_item_formula_b with origin/dev’s 20260818120000, 20260818130000, and 20260818140000. Check each target database’s _prisma_migrations table — preview and developer DBs may already have dev-only stamps prod lacks.
Phase 4/5 code is on the branch; still out of prod: Event Formula B writer windows and #1012 floor unchanged; origin/dev merge / migration-order reconciliation (OPS-02). SCHEDULE-00 ships on the branch (hour guard + midnight overnight + skip-as-Instant VALUES + invalid-tz org fallback in SQL) — API already rejects new offset-less schedule writes — but prod has not applied 20260819170000_*. Deploy migration + reader cutover together; do not treat branch HEAD as production. Do not claim Instant-in-prod in the changelog until apply.