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