aircraft
Tables (19)
aircraft_models
- id — BIGINT — Primary key — NOT NULL
- manufacturer — VARCHAR(100) — NOT NULL
- model_name — VARCHAR(100) — NOT NULL
- category — ENUM('LIGHT','MIDSIZE','HEAVY','TURBOPROP','OTHER') — NOT NULL
- typical_seats — INT
- max_range_km — INT
- max_payload_kg — INT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
airports
- id — BIGINT — Primary key — NOT NULL
- iata_code — CHAR(3) — NOT NULL
- icao_code — CHAR(4)
- name — VARCHAR(255) — NOT NULL
- city — VARCHAR(255)
- country — VARCHAR(255)
- timezone — VARCHAR(64)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
availability_rules
- id — BIGINT — Primary key — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id — NOT NULL
- origin_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- destination_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- start_date — DATE — NOT NULL
- end_date — DATE — NOT NULL
- dow_mask — TINYINT — NOT NULL
- window_start — TIME
- window_end — TIME
- aircraft_model_id — BIGINT — Foreign key → aircraft_models.id
- aircraft_id — BIGINT — Foreign key → operator_aircraft.id
- min_seats — INT — NOT NULL
- cargo_allowed — BOOLEAN — NOT NULL
- max_cargo_kg — INT
- status — ENUM('ACTIVE','PAUSED') — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
bookings
- id — BIGINT — Primary key — NOT NULL
- quote_id — BIGINT — Foreign key → quotes.id — NOT NULL
- passenger_user_id — BIGINT — Foreign key → users.id — NOT NULL
- status — ENUM('BOOKED','CANCELLED') — NOT NULL
- booked_at — DATETIME — NOT NULL
- cancelled_at — DATETIME
- settlement_status — ENUM('UNSETTLED','INVOICED','PAID') — NOT NULL
- settlement_due_date — DATE
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
empty_leg_offers
- id — BIGINT — Primary key — NOT NULL
- source — ENUM('SCRAPED','OPERATOR_UPLOAD') — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id
- origin_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- destination_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- depart_at — DATETIME — NOT NULL
- arrive_at — DATETIME
- aircraft_model_id — BIGINT — Foreign key → aircraft_models.id
- seats_available — INT
- fetched_at — DATETIME — NOT NULL
- status — ENUM('ACTIVE','INACTIVE') — NOT NULL
inquiries
- id — BIGINT — Primary key — NOT NULL
- passenger_user_id — BIGINT — Foreign key → users.id — NOT NULL
- source — ENUM('SEARCH_NO_SLOT','SELECTED_SLOT','MANUAL') — NOT NULL
- pax_count — INT — NOT NULL
- cargo_weight_kg — INT
- passenger_note — TEXT
- status — ENUM( 'INQUIRY_CREATED', 'SENT_TO_OPERATORS', 'OPERATOR_REPLIED', 'SHOW_TO_PASSENGER', 'QUOTE_REQUESTED', 'QUOTING', 'QUOTED', 'BOOKED', 'CANCELLED' ) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
inquiry_legs
- id — BIGINT — Primary key — NOT NULL
- inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
- leg_no — SMALLINT — NOT NULL
- origin_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- destination_airport_id — BIGINT — Foreign key → airports.id — NOT NULL
- depart_date — DATE — NOT NULL
- preferred_time_bucket — ENUM('MORNING','AFTERNOON','EVENING','NIGHT')
- preferred_time_start — TIME
- preferred_time_end — TIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inquiry_operator_legs
- id — BIGINT — Primary key — NOT NULL
- inquiry_operator_id — BIGINT — Foreign key → inquiry_operators.id — NOT NULL
- leg_no — SMALLINT — NOT NULL
- aircraft_model_id — BIGINT — Foreign key → aircraft_models.id
- aircraft_id — BIGINT — Foreign key → operator_aircraft.id
- eta_time_bucket — ENUM('MORNING','AFTERNOON','EVENING','NIGHT')
- eta_window_start — TIME
- eta_window_end — TIME
- flight_duration_min — INT
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inquiry_operators
- id — BIGINT — Primary key — NOT NULL
- inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id — NOT NULL
- sent_at — DATETIME
- status — ENUM('SENT','VIEWED','REPLIED') — NOT NULL
- can_operate — BOOLEAN
- internal_price_estimate — DECIMAL(12,2)
- reply_note — TEXT
- replied_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
notifications
- id — BIGINT — Primary key — NOT NULL
- receiver_user_id — BIGINT — Foreign key → users.id — NOT NULL
- type — ENUM( 'NEW_INQUIRY', 'INQUIRY_RESULT', 'QUOTE_REQUESTED', 'QUOTE_READY', 'OPERATOR_SIGNUP', 'OPERATOR_APPROVED', 'OPERATOR_REJECTED' ) — NOT NULL
- entity_type — VARCHAR(30) — NOT NULL
- entity_id — BIGINT — NOT NULL
- title — VARCHAR(255) — NOT NULL
- body — TEXT — NOT NULL
- is_read — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- read_at — DATETIME
operator_aircraft
- id — BIGINT — Primary key — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id — NOT NULL
- aircraft_model_id — BIGINT — Foreign key → aircraft_models.id — NOT NULL
- tail_no — VARCHAR(50)
- seat_capacity — INT — NOT NULL
- max_payload_kg — INT
- active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
operator_companies
- id — BIGINT — Primary key — NOT NULL
- company_name — VARCHAR(255) — NOT NULL
- business_reg_no — VARCHAR(50) — NOT NULL
- company_phone — VARCHAR(30) — NOT NULL
- company_email — VARCHAR(255) — NOT NULL
- operation_type — ENUM('BIZJET','PAX','CARGO','MIXED') — NOT NULL
- status — ENUM('PENDING','APPROVED','REJECTED','INACTIVE') — NOT NULL
- reviewed_by_admin_user_id — BIGINT — Foreign key → users.id
- reviewed_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
operator_members
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id — NOT NULL
- position — VARCHAR(100)
- is_primary_contact — BOOLEAN — NOT NULL
- notification_setting — JSON
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
passenger_profiles
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- organization_name — VARCHAR(255)
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
quote_legs
- id — BIGINT — Primary key — NOT NULL
- quote_id — BIGINT — Foreign key → quotes.id — NOT NULL
- leg_no — SMALLINT — NOT NULL
- aircraft_model_id — BIGINT — Foreign key → aircraft_models.id
- eta_window_start — TIME
- eta_window_end — TIME
- note — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
quote_request_operators
- id — BIGINT — Primary key — NOT NULL
- quote_request_id — BIGINT — Foreign key → quote_requests.id — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id — NOT NULL
- contacted_by_admin_user_id — BIGINT — Foreign key → users.id
- contact_method — ENUM('PHONE','EMAIL','OTHER') — NOT NULL
- contacted_at — DATETIME
- memo — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
quote_requests
- id — BIGINT — Primary key — NOT NULL
- inquiry_id — BIGINT — Foreign key → inquiries.id — NOT NULL
- passenger_user_id — BIGINT — Foreign key → users.id — NOT NULL
- request_note — TEXT
- status — ENUM('REQUESTED','IN_PROGRESS','FINALIZED','CANCELLED') — NOT NULL
- requested_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
quotes
- id — BIGINT — Primary key — NOT NULL
- quote_request_id — BIGINT — Foreign key → quote_requests.id — NOT NULL
- operator_company_id — BIGINT — Foreign key → operator_companies.id
- created_by_admin_user_id — BIGINT — Foreign key → users.id — NOT NULL
- currency — CHAR(3) — NOT NULL
- price_to_passenger — DECIMAL(12,2)
- operator_cost — DECIMAL(12,2)
- terms — TEXT
- valid_until — DATETIME
- status — ENUM('DRAFT','SENT','ACCEPTED','EXPIRED','CANCELLED') — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- sent_at — DATETIME
users
- id — BIGINT — Primary key — NOT NULL
- email — VARCHAR(255) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- name — VARCHAR(100) — NOT NULL
- phone — VARCHAR(30) — NOT NULL
- role — ENUM('PASSENGER','OPERATOR','ADMIN') — NOT NULL
- email_verified — BOOLEAN — NOT NULL
- fcm_token — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME