> ## 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 active intervals

> 2026-08-17 prod split of stored-active vs display-active Events by writer interval, IANA coverage, and what the Instant-target file can rewrite.

Companion pages: the [Event clock writeup](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md) is the narrative; the [problem canvas](https://docs.soundchecklive.io/engineering/backend/event-clock-problem-canvas.md) is the one-page map; [cohort alignment](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md) is the leftover SQL; [availability and engagement semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md) is the locked writer/accept contract. Ticket: [ENG-580](https://linear.app/soundcheck-live/issue/ENG-580/event-call-times-utc-as-wall-clock-encoding-vs-orgevent-timezone-docs).

This page is the 2026-08-17 DBeaver snapshot after the Instant-target retarget: how to read a writer interval, how many Events are still Active on the gigs list, which `createdAt` window those rows sit in, and what the migrations plus Phase 2 + Phase 4/5 **branch** Instant readers mean for those rows. Migrations are still **not deployed** on cited dumps; reader code on `cursor/eng-580-timebound-clock-alignment-b9d9` is likewise branch-only.

> [!NOTE]
>
> Inventory ran from DBeaver with session `America/Chicago`, not the deployed app. Naive `TIMESTAMP` `::date` / `::time` / `EXTRACT(HOUR)` are session-safe. `(timestamptz)::date` is not.

## Locked target

Naive `TIMESTAMP(3)` call clocks are **UTC Instants**. `Event.timezone` is a display IANA. Date-key columns stay calendar dates (UTC midnight of the venue's calendar day), not venue-midnight Instants.

Two formulas, never mixed:

| Id | SQL | Use |
| - | - | - |
| A | `((instant AT TIME ZONE 'UTC') AT TIME ZONE iana)` then `date_trunc('day')` | Calendar-day keys from a Flex quote Instant |
| B | `((wall AT TIME ZONE iana) AT TIME ZONE 'UTC')` | Wall digits → Instant |

Do not run B on a row that is already Instant (`startTime = show_start_at`, or availability `05:00 → 04:xx` next UTC day).

## How to read an interval

A writer interval is UTC `created_at` / `Event.createdAt`, half-open: `>= start AND < end`. Both ends are named even when a side is indefinite (`−∞` or `+∞`). The right end is the start of the next writer, not the last row of this one.

`created_at` names a writer only if the row was never saved again. A later header save re-encodes call times. Flex quotes attached after create-time (25 in the gap, 1 in web-Z) are the usual lie.

Calendar-day bounds are midnight UTC. PR / first-row bounds are merge or first-row seconds.

## Event intervals

| SQL name | From (UTC, inclusive) | Until (UTC, exclusive) | Bound kind | Writer |
| - | - | - | - | - |
| `e_pre_mobile` | `−∞` | `2026-04-14 00:00:00` | unbounded · calendar midnight | Sparse / unused |
| `e_mobile_local_as_z` | `2026-04-14 00:00:00` | `2026-05-06 14:53:38` | calendar midnight · writer-end second | Mobile `#379` local digits as `Z` |
| `e_gap_before_web_rfc` | `2026-05-06 14:53:38` | `2026-07-16 20:13:31` | writer-end second · PR second | Mixed. Encore rejected bare-local. 25 Flex quotes attached later |
| `e_web_z_pre_flex` | `2026-07-16 20:13:31` | `2026-07-23 00:27:23` | PR second · first-row second | Web `#1022` / `#1255` fake `Z` |
| `e_flex_instant_overlap` | `2026-07-23 00:27:23` | `2026-08-16 20:08:29` | first-row second · PR second | Flex `#1062` copied quote Instants. Web fake `Z` still lands |
| Flex Instant `eventDate` / `endDate` | `2026-07-23 00:27:23` | `2026-08-12 22:37:31` | first-row · first-row | Same left end as Instant overlap. Shorter right end at `#1292` |
| `e_after_flex_wallclock` | `2026-08-16 20:08:29` | `+∞` | PR second · unbounded | Flex `#1328` writer walls. `#1387` / `20260818140000` Instant→wall (same Formula A shape; one-shot around `2026-08-18 16:30` UTC). Restore from quote Instant; `20260819160000` after merge. |

Noon calendar days (`eventDate::time = 12:00:00`) are not a writer interval. Both ends are indefinite; the predicate is the clock.

## Offer, availability, and RoS intervals

| SQL name | From (UTC, inclusive) | Until (UTC, exclusive) | Writer |
| - | - | - | - |
| `o_early_z` / `a_early_z` | `−∞` | `2026-04-20 21:31:03` | Fake `Z`. Empty in prod. Writeup cites `2026-04-09` |
| `o_device_offset` / `a_device_offset` | `2026-04-20 21:31:03` | `2026-07-15 00:00:19` | `#392` … `#1012` device-offset Instant |
| `o_late_z` / `a_late_z` | `2026-07-15 00:00:19` | `+∞` | Fake `Z` again |
| `ros_device_offset` | `2026-06-16 00:00:00` | `2026-07-15 00:00:19` | Calendar midnight start, later than offer `#392`. **0 clocks** in prod (query 15 — closed) |

Availability splits **inside** `a_device_offset` by clock shape, not by another `created_at` bound: hour-0 wall (`625`), `5→4` Instant pair (`15`, leave), timed leftovers (`15`).

## Two meanings of active

| Term | Predicate | Count |
| - | - | - |
| All Events | — | 916 |
| Stored-active | `eventStatus IN ('ACTIVE', 'UNARCHIVED')` | **880** (879 `ACTIVE` + 1 `UNARCHIVED`) |
| Display-active | stored-active **and** `COALESCE(endDate, eventDate)::date >= DATE '2026-08-17'` | **156** |
| Stored-active, already past | stored-active, calendar day before 2026-08-17 | **724** |
| Other statuses | draft / template / archived | **36** (24 + 9 + 3) |

Display-active is what the gigs list calls **Active**. A past stored-active gig already shows as Completed. The one `UNARCHIVED` row is stored-active but past, so it is in the 880 and not in the 156.

| `eventStatus` | Events | Display-active |
| - | -: | -: |
| `ACTIVE` | 879 | 156 |
| `DRAFT` | 24 | 0 |
| `TEMPLATE` | 9 | 0 |
| `ARCHIVED` | 3 | 0 |
| `UNARCHIVED` | 1 | 0 |

Display-active is a **calendar-day** filter, not a writer interval. A March create for a September gig is still `e_pre_mobile`.

## Active Events by `createdAt` interval

All 880 stored-active and 156 display-active rows, same half-open CASE as query 1.

| Interval | Stored-active | Display-active | Already past | With `startTime` | Has real IANA |
| - | -: | -: | -: | -: | -: |
| `e_pre_mobile` | 569 | 47 | 522 | 119 | 0 |
| `e_mobile_local_as_z` | 79 | 17 | 62 | 0 | 0 |
| `e_gap_before_web_rfc` | 170 | 67 | 103 | 15 | 4 |
| `e_web_z_pre_flex` | 11 | 4 | 7 | 4 | 0 |
| `e_flex_instant_overlap` | 49 | 19 | 30 | 23 | 26 |
| `e_after_flex_wallclock` | 2 | 2 | 0 | 1 | 2 |
| **Total** | **880** | **156** | **724** | — | — |

Display-active with a `startTime`: **22**. Display-active with a real IANA (`timezone` not null and not `'UTC'`): **17**.

Of the 156 display-active:

* **131** are pre-mobile / mobile / gap (47 + 17 + 67). Almost no IANA (0 + 0 + 3).
* **19** are Flex Instant overlap (14 starts, 12 with IANA).
* **4** are web fake Z (2 starts, 0 IANA).
* **2** are after `#1328` (1 start, both have IANA).

## What this file can rewrite of the 156

`20260817180000_event_clock_alignment` Instant-izes Event wall hours 10–23 only when a real IANA exists, and skips quote-equal Flex clocks. It also fills Flex metadata IANA and projects Flex Instant calendar days in the `#1062` … `#1292` window.

It can touch the IANA slice of display-active — mainly Flex Instant overlap (19, 12 zoned) and the 2 after `#1328` in that snapshot. Later `#1387` Formula A walls restore from the quote Instant (metadata zone first). It cannot Instant-ize the 131 display-active rows in pre-mobile / mobile / gap without a zone ladder first. Mobile `#379` display-active is 17 events with **0** starts.

## Recommended fix

Same-release cutover. Do not teach every reader “these digits are UTC” until that row is already Instant or a proven IANA exists to Instant-ize it. A global reader flip on the 596 no-zone Events shows UTC hours and recreates Mapache in reverse.

1. **Same deploy:** writers store a real Instant and a real IANA; readers format Instant → `Event.timezone` (Phase 2 + Phase 4/5 **branch** code); ship `20260817180000_event_clock_alignment` and `20260819170000_event_schedule_item_formula_b` as written. Schedule Formula B SQL covers hour **10–23**, overnight inverted ends, and invalid Event IANA → org, but **no schedule rows convert** while `convert_schedule_wall` is empty — populate dump-proved ids before prod. Run the `:5434` integration test before prod.
2. **Transitional read for leftovers:** real IANA → Instant; `startTime = show_start_at` → Instant even if the zone was just filled; null / `'UTC'` and not quote-equal → still wall.
3. **Zone ladder, display-active first.** Fill a label, then formula B. Never use a CT prior as the convert zone. Order: real `Event.timezone` → Flex `metadata.timezone` (skip `UTC`) → Event / Venue lat/lng (skip `0,0`) → sibling Event IANA → `Venue.address.state` → `America/Chicago` **label only** after venues agree. Then the 25+1 Flex-linked created before `#1062` (query 11). Hours 0–9 stay Instant candidates.
4. **The same file** Instant-izes offer Formula B (56) + `crew_offer_days` overrides, and avail same-UTC-day hour 10–23 evening walls (row IANA, fake-`Z` `updated_at` band). Avail hour-0 and `5→4` are nulled with a required IANA. RoS has 0 clocks. See cohort-alignment leftovers.
5. **Make `Organization.timezone` a real writer** so new orgs stop landing in the leftover bucket.

Do not convert a whole `created_at` window. Do not run B on Flex quote-equal or avail `5→4`. Do not default leftovers through `America/Chicago`. Do not mix `LedgerItem.date` (ENG-581). Do not edit applied `20260307`.

## Queries

Paste-safe. Same DBeaver session as cohort-alignment query 1.

### Status rollup

```sql theme={null}
SELECT
  e."eventStatus",
  COUNT(*) AS events,
  COUNT(*) FILTER (
    WHERE e."eventStatus" IN ('ACTIVE', 'UNARCHIVED')
      AND COALESCE(e."endDate", e."eventDate")::date >= DATE '2026-08-17'
  ) AS display_active_on_snapshot
FROM "Event" e
GROUP BY 1
ORDER BY events DESC;
```

### Stored-active and display-active by interval

```sql theme={null}
SELECT
  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 interval,
  COUNT(*) AS stored_active,
  COUNT(*) FILTER (
    WHERE COALESCE(e."endDate", e."eventDate")::date >= DATE '2026-08-17'
  ) AS display_active,
  COUNT(*) FILTER (
    WHERE COALESCE(e."endDate", e."eventDate")::date < DATE '2026-08-17'
  ) AS stored_active_already_completed_in_list,
  COUNT(e."startTime") AS with_start,
  COUNT(*) FILTER (
    WHERE e.timezone IS NOT NULL AND e.timezone <> 'UTC'
  ) AS has_iana
FROM "Event" e
WHERE e."eventStatus" IN ('ACTIVE', 'UNARCHIVED')
GROUP BY 1
ORDER BY 1;
```

## Related

* [Event clock writeup (ENG-580)](https://docs.soundchecklive.io/engineering/backend/event-clock-writeup.md)
* [Event clock problem canvas](https://docs.soundchecklive.io/engineering/backend/event-clock-problem-canvas.md)
* [Event clock cohort alignment](https://docs.soundchecklive.io/engineering/backend/event-clock-cohort-alignment.md)
* [Availability and engagement clock semantics](https://docs.soundchecklive.io/engineering/backend/event-clock-availability-semantics.md)
* [Database](https://docs.soundchecklive.io/engineering/backend/database.md)
* [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)


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