-- Gate Gap API — initial schema. -- -- Notes that matter for security and for the client: -- -- · Every row that belongs to a person carries user_id and every query in the -- application filters on it. Identifiers are chosen by the client (the app -- already works offline and mints UUIDs), so ownership is never inferred -- from the identifier — it is always checked. -- · Deletions are recorded in `tombstones` so a device that was offline when -- something was deleted learns about it on the next pull instead of -- resurrecting it. -- · Booking references and document numbers are stored encrypted; the column -- holds AES-256-GCM ciphertext, so a database dump on its own does not give -- up a passport number. -- · utf8mb4 throughout: airport names and notes contain accents and emoji. SET NAMES utf8mb4; SET time_zone = '+00:00'; -- ---------------------------------------------------------------- accounts CREATE TABLE IF NOT EXISTS users ( id CHAR(36) NOT NULL, email VARCHAR(254) NOT NULL, -- Lower-cased copy used for uniqueness and lookup, so two people cannot -- register the same address in different cases. email_normalised VARCHAR(254) NOT NULL, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(80) NOT NULL DEFAULT '', -- Bumped whenever every session must fall over: password change, or the -- traveller pressing "sign out everywhere". token_version INT UNSIGNED NOT NULL DEFAULT 1, failed_logins INT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_users_email (email_normalised) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One row per signed-in device. CREATE TABLE IF NOT EXISTS sessions ( id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, device_label VARCHAR(80) NOT NULL DEFAULT '', user_agent VARCHAR(255) NOT NULL DEFAULT '', ip_hash CHAR(64) NOT NULL DEFAULT '', created_at DATETIME NOT NULL, last_used_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL DEFAULT NULL, PRIMARY KEY (id), KEY idx_sessions_user (user_id, revoked_at), CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- The chain of refresh tokens for a session. -- -- Each token may be spent exactly once, and the spent row is KEPT. That is the -- whole point: if a token that has already been used turns up again, either -- the real device is replaying it or somebody copied it, and there is no way -- to tell which — so the session dies and both have to sign in. Overwriting -- the row instead would make the old token merely unknown, and a theft would -- look exactly like an expired session. CREATE TABLE IF NOT EXISTS refresh_tokens ( id CHAR(36) NOT NULL, session_id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, -- SHA-256 of the token. The token itself is never stored, so a database -- dump cannot be replayed against the API. token_hash CHAR(64) NOT NULL, created_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL DEFAULT NULL, revoked_at DATETIME NULL DEFAULT NULL, replaced_by CHAR(36) NULL DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uq_refresh_hash (token_hash), KEY idx_refresh_session (session_id), CONSTRAINT fk_refresh_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE, CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Short-lived bearer tokens. Kept server-side so "sign out" is immediate -- rather than "immediate once the token expires". CREATE TABLE IF NOT EXISTS access_tokens ( id CHAR(36) NOT NULL, session_id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, token_hash CHAR(64) NOT NULL, created_at DATETIME NOT NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uq_access_hash (token_hash), KEY idx_access_session (session_id), CONSTRAINT fk_access_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE, CONSTRAINT fk_access_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------- traveller CREATE TABLE IF NOT EXISTS profiles ( user_id CHAR(36) NOT NULL, display_name VARCHAR(80) NOT NULL DEFAULT '', passport_country VARCHAR(80) NOT NULL DEFAULT '', second_passport_country VARCHAR(80) NOT NULL DEFAULT '', walking_pace VARCHAR(32) NOT NULL DEFAULT 'normal', mobility_needs JSON NULL, usual_baggage VARCHAR(32) NOT NULL DEFAULT 'oneChecked', home_airport VARCHAR(8) NOT NULL DEFAULT '', travel_to_airport_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60, arrive_before_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 120, applies_six_month_rule TINYINT(1) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL, PRIMARY KEY (user_id), CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------- the trip CREATE TABLE IF NOT EXISTS trips ( id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, name VARCHAR(120) NOT NULL DEFAULT '', purpose VARCHAR(32) NOT NULL DEFAULT 'holiday', start_date DATE NOT NULL, end_date DATE NOT NULL, travellers TINYINT UNSIGNED NOT NULL DEFAULT 1, ticket_structure VARCHAR(16) NOT NULL DEFAULT 'single', status VARCHAR(16) NOT NULL DEFAULT 'draft', draft_step TINYINT UNSIGNED NOT NULL DEFAULT 0, -- Baggage plan, per-connection overrides, packing, events, live state and -- recap travel as documents: they are edited as a whole by one device and -- never queried field by field. baggage JSON NULL, connection_settings JSON NULL, baggage_rules JSON NULL, packing JSON NULL, events JSON NULL, live JSON NULL, recap JSON NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_trips_user_updated (user_id, updated_at), CONSTRAINT fk_trips_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS flights ( id CHAR(36) NOT NULL, trip_id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, number VARCHAR(16) NOT NULL DEFAULT '', airline VARCHAR(80) NOT NULL DEFAULT '', from_code VARCHAR(8) NOT NULL DEFAULT '', to_code VARCHAR(8) NOT NULL DEFAULT '', from_terminal VARCHAR(16) NOT NULL DEFAULT '', to_terminal VARCHAR(16) NOT NULL DEFAULT '', -- Wall clock at the airport concerned, exactly as the client stores it. departure_at DATETIME NOT NULL, arrival_at DATETIME NOT NULL, -- AES-256-GCM ciphertext. Never queried, only returned to its owner. booking_reference_enc VARBINARY(512) NULL, seat VARCHAR(16) NOT NULL DEFAULT '', cabin VARCHAR(24) NOT NULL DEFAULT 'economy', checked_bags TINYINT UNSIGNED NOT NULL DEFAULT 0, notes TEXT NULL, ticket_group TINYINT UNSIGNED NOT NULL DEFAULT 0, gate_closes_minutes TINYINT UNSIGNED NOT NULL DEFAULT 20, state VARCHAR(16) NOT NULL DEFAULT 'scheduled', delay_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0, actual_departure_at DATETIME NULL DEFAULT NULL, actual_arrival_at DATETIME NULL DEFAULT NULL, source VARCHAR(24) NOT NULL DEFAULT 'manual', edited_fields JSON NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_flights_trip (trip_id), KEY idx_flights_user_updated (user_id, updated_at), CONSTRAINT fk_flights_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE, CONSTRAINT fk_flights_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------- paperwork CREATE TABLE IF NOT EXISTS documents ( id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, trip_id CHAR(36) NULL DEFAULT NULL, type VARCHAR(32) NOT NULL DEFAULT 'passport', belongs_to VARCHAR(80) NOT NULL DEFAULT '', valid_until DATE NULL DEFAULT NULL, -- AES-256-GCM ciphertext of the document number. reference_number_enc VARBINARY(512) NULL, notes TEXT NULL, -- Photographs stay on the device. Only the file name travels, so a lost -- phone does not put a passport scan on a server. photo_name VARCHAR(128) NULL DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_documents_user_updated (user_id, updated_at), KEY idx_documents_trip (trip_id), CONSTRAINT fk_documents_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_documents_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS transit_requirements ( id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, trip_id CHAR(36) NULL DEFAULT NULL, country VARCHAR(80) NOT NULL DEFAULT '', airport_code VARCHAR(8) NOT NULL DEFAULT '', passport_used VARCHAR(80) NOT NULL DEFAULT '', transit_visa_required VARCHAR(16) NOT NULL DEFAULT 'unknown', airside_transit_allowed VARCHAR(16) NOT NULL DEFAULT 'unknown', confirmed_by VARCHAR(120) NOT NULL DEFAULT '', confirmation_date DATE NULL DEFAULT NULL, notes TEXT NULL, document_id CHAR(36) NULL DEFAULT NULL, state VARCHAR(24) NOT NULL DEFAULT 'notChecked', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_transit_user_updated (user_id, updated_at), CONSTRAINT fk_transit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_transit_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------- knowledge CREATE TABLE IF NOT EXISTS airport_cards ( user_id CHAR(36) NOT NULL, code VARCHAR(8) NOT NULL, name VARCHAR(120) NOT NULL DEFAULT '', city VARCHAR(120) NOT NULL DEFAULT '', country VARCHAR(80) NOT NULL DEFAULT '', country_code VARCHAR(4) NOT NULL DEFAULT '', time_zone_id VARCHAR(64) NULL DEFAULT NULL, terminals JSON NULL, terminals_are_suggestions TINYINT(1) NOT NULL DEFAULT 0, transfer_mode VARCHAR(24) NOT NULL DEFAULT 'unknown', transfer_minutes SMALLINT UNSIGNED NULL DEFAULT NULL, minimum_connection_domestic SMALLINT UNSIGNED NULL DEFAULT NULL, minimum_connection_international SMALLINT UNSIGNED NULL DEFAULT NULL, passport_control_place VARCHAR(24) NOT NULL DEFAULT 'unknown', security_after_transfer VARCHAR(16) NOT NULL DEFAULT 'unknown', notes TEXT NULL, is_user_created TINYINT(1) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL, PRIMARY KEY (user_id, code), KEY idx_airports_user_updated (user_id, updated_at), CONSTRAINT fk_airports_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- The measurements that make the connection engine better than an average. CREATE TABLE IF NOT EXISTS own_timings ( id CHAR(36) NOT NULL, user_id CHAR(36) NOT NULL, airport_code VARCHAR(8) NOT NULL, step VARCHAR(32) NOT NULL, minutes SMALLINT UNSIGNED NOT NULL, hour_of_day TINYINT UNSIGNED NOT NULL, note VARCHAR(255) NOT NULL DEFAULT '', recorded_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_timings_user_updated (user_id, updated_at), KEY idx_timings_airport (user_id, airport_code), CONSTRAINT fk_timings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------- plumbing -- What was deleted and when, so a device that was offline at the time stops -- pushing it back. CREATE TABLE IF NOT EXISTS tombstones ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id CHAR(36) NOT NULL, entity VARCHAR(32) NOT NULL, entity_id VARCHAR(80) NOT NULL, deleted_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_tombstone (user_id, entity, entity_id), KEY idx_tombstones_user_time (user_id, deleted_at), CONSTRAINT fk_tombstones_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Counts attempts per action per identity. Used for login, registration, -- password change and refresh, so a stolen address cannot be brute forced. CREATE TABLE IF NOT EXISTS rate_limits ( bucket VARCHAR(160) NOT NULL, window_start DATETIME NOT NULL, hits INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (bucket), KEY idx_rate_window (window_start) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Security-relevant events. No request bodies, no tokens — just what happened. CREATE TABLE IF NOT EXISTS audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id CHAR(36) NULL DEFAULT NULL, event VARCHAR(48) NOT NULL, detail VARCHAR(255) NOT NULL DEFAULT '', ip_hash CHAR(64) NOT NULL DEFAULT '', created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_audit_user (user_id, created_at), KEY idx_audit_event (event, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;