Problem 1: show_id FK violation
In jobs/flowsheet-etl/job.ts:129, the bulk load path uses show_id: backendShowId ?? 0. When a legacy entry references a show that doesn't exist in the shows table, this falls back to 0. No show has id = 0, so this violates the FK constraint. The incremental sync path correctly uses ?? null.
Problem 2: Hardcoded EDT timezone
In jobs/flowsheet-etl/transform.ts:52, parseMySQLDatetime appends -04:00 (EDT) to all timestamps. During EST (November-March), the correct offset is -05:00. All winter timestamps are off by one hour when imported.
Fix
- Change
?? 0 to ?? null for show_id
- Use
Intl.DateTimeFormat with timeZone: 'America/New_York' to determine the correct EST/EDT offset dynamically, with a two-pass approach for DST transition edge cases
Problem 1: show_id FK violation
In
jobs/flowsheet-etl/job.ts:129, the bulk load path usesshow_id: backendShowId ?? 0. When a legacy entry references a show that doesn't exist in the shows table, this falls back to 0. No show hasid = 0, so this violates the FK constraint. The incremental sync path correctly uses?? null.Problem 2: Hardcoded EDT timezone
In
jobs/flowsheet-etl/transform.ts:52,parseMySQLDatetimeappends-04:00(EDT) to all timestamps. During EST (November-March), the correct offset is-05:00. All winter timestamps are off by one hour when imported.Fix
?? 0to?? nullfor show_idIntl.DateTimeFormatwithtimeZone: 'America/New_York'to determine the correct EST/EDT offset dynamically, with a two-pass approach for DST transition edge cases