Event clock problem canvas

Full narrative: Event clock writeup (ENG-580). Inventory SQL and leftover queries live on Event clock cohort alignment. Stored-active vs display-active by writer interval: Event clock active intervals. Availability writers and engagement accept: Availability and engagement clock semantics. Ledger calendar-date leftovers are ENG-581 / PR #1370, not this canvas. Storage target is real UTC Instants on call clocks. Migrations and this-pass fixes are branch-only — not deployed. Instant writers are not in production.

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

The split

Docs and Prisma comments said call clocks were UTC Instants, rendered in Event.timezone / Organization.timezone (Null = UTC). Runtime stored the opposite for almost every product writer: venue wall-clock digits in a naive TIMESTAMP(3) with a fake Z (17:00 → 2026-08-05 17:00:00). Will’s SQL rule (naive timestamps are UTC Instants; product conversion belongs in the app) is still the default. Call-time runtime is the exception. Treating 17:00Z as an Instant and projecting it through Chicago is the Mapache email bug (12:00–6:00 PM vs crew 5:00–11:00 PM). Event.timezone (or org) null does not mean the digits are Instants. It means no IANA label is stored. Readers then assume UTC. Flex create without metadata.timezone writes 'UTC' — the same lie, not Instant proof. Organization.timezone has no API writer. 17180000 fills it when Events collapse to one real IANA. Mixed orgs stay null unless already written in prod. Do not treat a still-null org TZ as Instant proof.

Target after alignment

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

Two corrections, never mixed:

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

Writer timeline

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

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

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

What the migration already rewrites

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

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

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

Leftovers that are classified

No more inventory needed. Do not convert. Already Instant — leave (do not Formula-B): Flex startTime = show_start_at (10); avail device-offset 5→4 (15 — migration nulls as all-day, not wall→Instant); respondedAt. 20260817180000_event_clock_alignment owns the cutover (no six-file split). Hour-0 availability prefixes and after-cutoff evenings Formula B in this file (upper bound < 2026-09-15). Device Instant evenings leave. Remaining unlabeled Events after fill have no startTime. RoS has 0 clocks. Require Event.timezone later; this file does not SET NOT NULL. Phase 4/5 branch code (not prod): notifications, documents, duplicate/RoS encode, inbox, Google/offer topology (one resolved zone on create + resync), Staffing Board Event-IANA encodes, mobile schedule past_midnight + per-row civil dates, portal/storefront resolved timezone on the API contract, engagement event_timezone, timeline civil-day, and many client readers — deploy with the migrations. 20260819170000_event_schedule_item_formula_b writes schedule-item Formula B SQL (hour 10–23, inverted overnight ends including exact 00:00:00, invalid Event IANA → org, five dump ids skip-as-Instant; ceiling < 2026-09-15) — not applied in prod, and no rows convert while convert_schedule_wall is empty. This pass (branch only): MOBILE-01, NOTIFY-01, CAL-03, CLIENT-01, CAL-02, WEB-02, MOBILE-02, CONTRACT-01. See merge / deploy. Fresh dump prod-postgres-20260819-pm.dump leftovers (classify only — not converted this pass): 145 Event evening starts with no IANA; 874 schedule evenings before Jul 15 floor; 65 in-window schedule evenings with no Event/org IANA; 686 Events with neither Event nor org IANA; MIG-02/03/04 empty (0 rows each). Classifier convert sets: 35 Event evening (not Flex-equal); 109 schedule evening (not Event-equal, not skip-listed); 5 schedule skip-as-Instant; 7 other low-hour schedule starts unconverted.

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

Still open

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

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

Zone ladder

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

Venue has no timezone column. FlexQuote.venue_id is a bare string, not a Prisma FK. The one multi-IANA org (Chicago + Detroit) is why venue coords beat an org-level CT default. Query 19 is the human check; summarize, do not paste the grid.

How to read the leftover queries

Paste-safe counts: queries 1–17 on the alignment page. Human look, do not paste names / cities / org ids — send a one-liner:

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

Out of scope

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

ENG-580
Event clock writeup (ENG-580)
Event clock cohort alignment — time bounds, migration guards, leftover SQL
Availability and engagement clock semantics — locked writer/accept contract
Database — never edit applied migration SQL
ENG-581

Overview

Backend

Backend integrations

API reference

Frontend

Mobile

Infrastructure

CI/CD

Development

Guides

Reference

Event clock problem canvas

The split

Target after alignment

Writer timeline

What the migration already rewrites

Leftovers that are classified

Still open

Zone ladder

How to read the leftover queries

Out of scope

​The split

​Target after alignment

​Writer timeline

​What the migration already rewrites

​Leftovers that are classified

​Still open

​Zone ladder

​How to read the leftover queries

​Out of scope

​Related

The split

Target after alignment

Writer timeline

What the migration already rewrites

Leftovers that are classified

Still open

Zone ladder

How to read the leftover queries

Out of scope

Related