-- ===================================================================== -- Travel Docket · Migration 006 · Phase P6 — Live status and notifications -- Covers: STS-01..09, NTF-01..06, WTH-05, EVT-06 -- ===================================================================== SET NAMES utf8mb4; -- Latest known live values on the leg, so the timeline needs no join (STS-08). ALTER TABLE segments ADD COLUMN live_status VARCHAR(30) NULL AFTER is_cancelled, -- scheduled/delayed/departed/arrived/cancelled/diverted ADD COLUMN live_delay_min SMALLINT NULL AFTER live_status, ADD COLUMN live_dep_utc DATETIME NULL AFTER live_delay_min, -- expected/actual ADD COLUMN live_arr_utc DATETIME NULL AFTER live_dep_utc, ADD COLUMN live_dep_terminal VARCHAR(20) NULL AFTER live_arr_utc, ADD COLUMN live_dep_gate VARCHAR(10) NULL AFTER live_dep_terminal, ADD COLUMN live_dep_platform VARCHAR(10) NULL AFTER live_dep_gate, ADD COLUMN live_arr_terminal VARCHAR(20) NULL AFTER live_dep_platform, ADD COLUMN live_arr_platform VARCHAR(10) NULL AFTER live_arr_terminal, ADD COLUMN live_belt VARCHAR(10) NULL AFTER live_arr_platform, ADD COLUMN live_updated_at DATETIME NULL AFTER live_belt, ADD COLUMN poll_priority TINYINT UNSIGNED NOT NULL DEFAULT 5 AFTER live_updated_at; -- STS-07 -- Every poll result that changed something (STS-06). CREATE TABLE status_snapshots ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, segment_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(30) NOT NULL, fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, status VARCHAR(30) NULL, delay_min SMALLINT NULL, dep_utc DATETIME NULL, arr_utc DATETIME NULL, dep_terminal VARCHAR(20) NULL, dep_gate VARCHAR(10) NULL, dep_platform VARCHAR(10) NULL, arr_terminal VARCHAR(20) NULL, arr_platform VARCHAR(10) NULL, belt VARCHAR(10) NULL, changed_fields JSON NULL, -- ["dep_platform","delay_min"] raw JSON NULL, PRIMARY KEY (id), KEY ix_snap_segment_time (segment_id, fetched_at), CONSTRAINT fk_snap_segment FOREIGN KEY (segment_id) REFERENCES segments (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Web push per device (NFR-01, NTF-01) CREATE TABLE push_subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, endpoint VARCHAR(1000) NOT NULL, endpoint_hash CHAR(64) CHARACTER SET ascii NOT NULL, p256dh VARCHAR(255) NOT NULL, auth_secret VARCHAR(64) NOT NULL, user_agent VARCHAR(255) NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, failures TINYINT UNSIGNED NOT NULL DEFAULT 0, -- disable after repeated 404/410 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_push_endpoint (endpoint_hash), KEY ix_push_user (user_id, enabled), CONSTRAINT fk_push_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Sent / in-app notifications. dedupe_key stops duplicate alerts (NTF-02, WTH-05, EVT-06), -- e.g. 'sts:seg:4411:platform:5', 'wth:place:12:2026-10-18', 'evt:digest:trip:9'. CREATE TABLE notifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, user_id BIGINT UNSIGNED NOT NULL, trip_id BIGINT UNSIGNED NULL, type VARCHAR(40) NOT NULL, -- delay / platform / cancel / ripple / reminder / weather / events_digest dedupe_key VARCHAR(160) NOT NULL, title VARCHAR(150) NOT NULL, body VARCHAR(500) NOT NULL, url VARCHAR(255) NULL, -- deep link in the app ref_entity VARCHAR(40) NULL, ref_id BIGINT UNSIGNED NULL, channel ENUM('push','email','inapp') NOT NULL DEFAULT 'push', sent_at DATETIME NULL, read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_notifications_uuid (uuid), UNIQUE KEY uq_notification_dedupe (user_id, dedupe_key), KEY ix_notifications_user_unread (user_id, read_at), KEY ix_notifications_trip (trip_id), CONSTRAINT fk_ntf_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_ntf_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Mute alert types globally (trip_id = 0) or per trip (NTF-06). No FK on trip_id because of 0. CREATE TABLE notification_prefs ( user_id BIGINT UNSIGNED NOT NULL, trip_id BIGINT UNSIGNED NOT NULL DEFAULT 0, type VARCHAR(40) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id, trip_id, type), CONSTRAINT fk_nprefs_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- Scheduled reminders (NTF-04) CREATE TABLE reminders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) CHARACTER SET ascii NOT NULL, trip_id BIGINT UNSIGNED NOT NULL, segment_id BIGINT UNSIGNED NULL, stay_id BIGINT UNSIGNED NULL, kind ENUM('web_checkin','departure','checkout','custom') NOT NULL, remind_at_utc DATETIME NOT NULL, message VARCHAR(255) NULL, sent_at DATETIME NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_reminders_uuid (uuid), KEY ix_reminders_due (sent_at, remind_at_utc), KEY ix_reminders_trip (trip_id), CONSTRAINT fk_rem_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_rem_segment FOREIGN KEY (segment_id) REFERENCES segments (id) ON DELETE CASCADE, CONSTRAINT fk_rem_stay FOREIGN KEY (stay_id) REFERENCES stays (id) ON DELETE CASCADE, CONSTRAINT fk_rem_user FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO schema_migrations (version) VALUES ('006_p6_status_notifications');