Menu
Back to Documentation Index

ADL Flights Schema

Database schema reference for adl_flights table

Download Markdown Interactive API Docs All Documentation

VATSIM_ADL Database Schema Reference

adl_flights Table

Generated: 2026-01-15 (Updated 2026-01-27) Total Columns: 171 (13 new FIXM columns added) Database: Azure SQL (VATSIM_ADL)

FIXM Migration (2026-01-27): New FIXM-aligned time columns have been added alongside legacy OOOI columns. During the 30-day transition, both sets are populated. After transition, legacy columns will be deprecated. See migration 097_adl_fixm_time_columns.sql for details.

New FIXM Columns: actual_off_block_time, actual_time_of_departure, actual_landing_time, actual_in_block_time, estimated_time_of_arrival, estimated_off_block_time, estimated_runway_arrival_time, controlled_time_of_departure, controlled_time_of_arrival, taxi_start_time, departure_sequence_time, hold_short_time, runway_entry_time

Column Reference

Identity & Core (Positions 1-19)

Position Column Type Length Description
1 id bigint Primary key
2 cid int VATSIM CID
3 callsign nvarchar 16 Flight callsign
4 flight_id nvarchar 32 VATSIM flight ID
5 flight_key nvarchar 64 Unique flight identifier
6 phase nvarchar 16 Flight phase (PREFLIGHT, PUSHBACK, TAXI_OUT, TAKEOFF, CLIMB, CRUISE, DESCENT, APPROACH, TAXI_IN, ARRIVED)
7 last_source nvarchar 16 Last data source
8 is_active bit Active flight flag
9 adl_date date ADL date
10 adl_time time ADL time
11 aircraft_type nvarchar 8 ICAO aircraft type
12 ac_cat nvarchar 32 Aircraft category
13 weight_class nvarchar 16 Wake turbulence category
14 major_carrier nvarchar 8 Major carrier code
15 cdm_participant bit CDM participant flag
16 user_category nvarchar 16 User category
17 first_seen_utc datetime2 First position timestamp
18 last_seen_utc datetime2 Last position timestamp
19 logon_time_utc datetime2 VATSIM logon time

Flight Plan (Positions 20-42)

Position Column Type Length Description
20 fp_rule nvarchar 1 Flight rules (I/V/Y/Z)
21 fp_dept_icao char 4 Departure airport ICAO
22 fp_dest_icao char 4 Destination airport ICAO
23 fp_alt_icao char 4 Alternate airport ICAO
24 fp_dept_time_z char 4 Filed departure time (HHMM)
25 fp_enroute_minutes int Filed enroute time (minutes)
26 fp_fuel_minutes int Filed fuel endurance (minutes)
27 fp_altitude_ft int Filed cruise altitude (feet)
28 fp_tas_kts int Filed true airspeed (knots)
29 fp_route nvarchar MAX Filed route string
30 fp_remarks nvarchar MAX Flight plan remarks
31 aircraft_icao nvarchar 64 Full ICAO aircraft designator
32 aircraft_equipment nvarchar 64 Equipment codes
33 aircraft_transponder nvarchar 16 Transponder codes
34 dfix nvarchar 8 Departure fix
35 eftd_utc datetime2 Estimated flight time departure
36 dp_name nvarchar 16 Departure procedure name
37 dtrsn nvarchar 16 Departure transition
38 gcd_nm decimal Great circle distance (nm)
39 afix nvarchar 8 Arrival fix
40 eaft_utc datetime2 Estimated arrival fix time
41 star_name nvarchar 16 STAR name
42 strsn nvarchar 16 STAR transition

Position & Velocity (Positions 43-46, 130-132)

Position Column Type Length Description
43 lat decimal Latitude
44 lon decimal Longitude
45 altitude_ft int Current altitude (feet)
46 groundspeed_kts int Ground speed (knots)
130 heading_deg smallint Heading (degrees)
131 qnh_in_hg decimal Altimeter setting (inHg)
132 qnh_mb int Altimeter setting (millibars)

Times - Estimated (Positions 47-56)

Position Column Type Length Description
47 etd_prefix nchar 1 ETD prefix
48 etd_runway_utc datetime2 ETD runway time
49 entry_utc datetime2 Entry time
50 exit_utc datetime2 Exit time
51 ete_minutes int Estimated time enroute (minutes)
52 eta_prefix nchar 1 ETA prefix
53 eta_runway_utc datetime2 ETA runway time
54 ctd_utc datetime2 Controlled time departure
55 cta_utc datetime2 Controlled time arrival
56 cete_minutes int Controlled ETE (minutes)

Times - Scheduled/Proposed (Positions 57-65)

Position Column Type Length Description
57 sgtd_utc datetime2 Scheduled gate departure
58 sgta_utc datetime2 Scheduled gate arrival
59 pgtd_utc datetime2 Proposed gate departure
60 pgta_utc datetime2 Proposed gate arrival
61 pete_minutes int Proposed ETE (minutes)
62 lrtd_utc datetime2 Last revised departure
63 lrta_utc datetime2 Last revised arrival
64 lgtd_utc datetime2 Last gate departure
65 lgta_utc datetime2 Last gate arrival

Times - Initial/Actual (Positions 66-84)

Position Column Type Length Description
66 igtd_utc datetime2 Initial gate departure
67 igta_utc datetime2 Initial gate arrival
68 ientry_utc datetime2 Initial entry time
69 artd_utc datetime2 Actual runway departure
70 arta_utc datetime2 Actual runway arrival
71 out_utc datetime2 OOOI - OUT
72 off_utc datetime2 OOOI - OFF
73 on_utc datetime2 OOOI - ON
74 in_utc datetime2 OOOI - IN
75 ertd_utc datetime2 Early runway departure
76 erta_utc datetime2 Early runway arrival
77 eentry_utc datetime2 Early entry time
78 oetd_utc datetime2 Original ETD
79 oeta_utc datetime2 Original ETA
80 oentry_utc datetime2 Original entry
81 oete_minutes int Original ETE (minutes)
82 betd_utc datetime2 Base ETD
83 beta_utc datetime2 Base ETA
84 bentry_utc datetime2 Base entry

Times - TMA/Control (Positions 85-87)

Position Column Type Length Description
85 tma_rt_utc datetime2 TMA runway time
86 octd_utc datetime2 Original controlled departure
87 octa_utc datetime2 Original controlled arrival

TMI Control (Positions 88-111)

Position Column Type Length Description
88 ctl_exempt bit Exempt from TMI flag
89 ctl_type nvarchar 8 Control type (GDP, AFP, GS, etc.)
90 subbable bit Substitution eligible
91 ctl_program nvarchar 16 Control program name
92 ctl_element nvarchar 32 Controlled element (airport/FCA)
93 slot_id nvarchar 16 Slot identifier
94 slot_hold bit Slot hold flag
95 div_recovery nchar 1 Diversion recovery
96 delay_status nvarchar 16 Delay status
97 ltod_minutes int Late takeoff delay (minutes)
98 cnx_status nvarchar 2 Cancellation status
99 remark_code nvarchar 8 Remark code
100 nrp_flag bit NRP flag
101 lfg_flag bit Long-haul flag
102 iii_flag bit International flag
103 atv_flag bit ATV flag
104 swp_flag bit Swap flag
105 dvt_flag bit Divert flag
106 adc_flag bit ADC flag
107 fca_flag bit FCA flag
108 wxr_flag bit Weather flag
109 alarm_code nvarchar 2 Alarm code
110 do_flag bit DO flag
111 absolute_delay_min int Absolute delay (minutes)

Delay & Schedule (Positions 112-117, 121)

Position Column Type Length Description
112 schedule_variation_min int Schedule variation (minutes)
113 program_delay_min int Program-assigned delay (minutes)
114 estimated_dep_utc datetime2 Estimated departure
115 estimated_arr_utc datetime2 Estimated arrival
116 eta_source nvarchar 16 ETA source
117 arrival_bucket_utc datetime2 Arrival bucket time
121 arrival_bucket_minutes datetime2 Arrival bucket (minutes)

Status & Flags (Position 119-122)

Position Column Type Length Description
119 last_raw_json nvarchar MAX Last raw JSON data
120 flight_status nvarchar 32 Flight status
122 gs_flag bit Ground stop flag

Facility Assignment (Positions 123-128)

Position Column Type Length Description
123 fp_dept_tracon nvarchar 64 Departure TRACON
124 fp_dept_artcc nvarchar 8 Departure ARTCC
125 fp_dest_tracon nvarchar 64 Destination TRACON
126 fp_dest_artcc nvarchar 8 Destination ARTCC
127 fp_alt_tracon nvarchar 64 Alternate TRACON
128 fp_alt_artcc nvarchar 8 Alternate ARTCC

VATSIM/Extended Aircraft (Positions 129, 133-140)

Position Column Type Length Description
129 vatsim_server nvarchar 32 VATSIM server
133 aircraft_faa nvarchar 16 FAA aircraft designator
134 aircraft_short nvarchar 16 Short aircraft name
135 fp_revision_id int Flight plan revision ID
136 fp_assigned_transponder nvarchar 16 Assigned transponder code
137 fp_dof_utc date Date of flight
138 fp_eet_minutes int Extended EET (minutes)
139 fp_opr nvarchar 8 Operator code
140 fp_eet nvarchar MAX Extended EET string

Sequencing Times (Positions 141-148)

Position Column Type Length Description
141 sequence_time_utc datetime2 Sequence assignment time
142 holdshort_time_utc datetime2 Hold short time
143 runway_time_utc datetime2 Runway time
144 eta_vt_utc datetime2 ETA VT
145 vt_utc datetime2 Virtual target time
146 sequence_utc datetime2 Sequence time
147 holdshort_utc datetime2 Hold short time
148 runway_utc datetime2 Runway assignment time

GDP Integration (Positions 149-158)

Position Column Type Length Description
149 gdp_program_id nvarchar 50 GDP program ID
150 gdp_slot_index int GDP slot index
151 gdp_slot_time_utc datetime2 GDP assigned slot time
152 ctl_prgm nvarchar 50 Control program (alternate)
153 delay_capped bit Delay capped flag
154 gs_held bit Ground stop held flag
155 gs_release_utc datetime2 Ground stop release time
156 ctl_exempt_reason nvarchar 64 Exemption reason
157 slot_time nvarchar 8 Slot time (HHMM format)
158 slot_time_utc datetime2 Slot time UTC

SWIM API Column Usage

flights.php & flight.php (Full Flight Record)

flight_key, callsign, cid, aircraft_type, aircraft_faa, aircraft_icao, aircraft_short,
aircraft_equipment, aircraft_transponder, ac_cat, weight_class, major_carrier, user_category,
fp_dept_icao, fp_dest_icao, fp_alt_icao, fp_altitude_ft, fp_tas_kts, fp_route, fp_remarks, fp_rule,
fp_dept_time_z, fp_enroute_minutes, fp_assigned_transponder, fp_dept_artcc, fp_dest_artcc,
fp_dept_tracon, fp_dest_tracon, dfix, dp_name, afix, star_name, phase, flight_status, is_active,
lat, lon, altitude_ft, heading_deg, groundspeed_kts, eftd_utc, out_utc, off_utc, eta_runway_utc,
estimated_arr_utc, on_utc, in_utc, ete_minutes, gcd_nm, gs_flag, ctl_type, ctl_program, ctl_element,
ctl_exempt, gdp_program_id, gdp_slot_index, gdp_slot_time_utc, first_seen_utc, last_seen_utc, logon_time_utc

positions.php (GeoJSON - Minimal)

flight_key, callsign, aircraft_type, aircraft_short, ac_cat, weight_class, fp_dept_icao, fp_dest_icao,
fp_dest_artcc, phase, lat, lon, altitude_ft, heading_deg, groundspeed_kts, eta_runway_utc,
estimated_arr_utc, fp_route, gcd_nm, ete_minutes, gs_flag, ctl_type, ctl_program, ctl_element,
gdp_program_id, gdp_slot_time_utc

tmi/controlled.php (TMI Focus)

flight_key, callsign, cid, aircraft_type, aircraft_icao, ac_cat, weight_class, fp_dept_icao,
fp_dest_icao, fp_alt_icao, fp_altitude_ft, fp_route, fp_dept_artcc, fp_dest_artcc, dfix, dp_name,
afix, star_name, phase, is_active, lat, lon, altitude_ft, heading_deg, groundspeed_kts, eftd_utc,
out_utc, off_utc, eta_runway_utc, ete_minutes, gcd_nm, gs_flag, ctl_type, ctl_program, ctl_element,
ctl_exempt, gdp_program_id, gdp_slot_index, gdp_slot_time_utc, first_seen_utc, last_seen_utc

Key Column Groups for TMI Operations

Ground Stop Detection

  • gs_flag (bit) - Primary GS indicator
  • gs_held (bit) - Currently held
  • gs_release_utc (datetime2) - Release time

GDP Assignment

  • gdp_program_id (nvarchar 50) - GDP program identifier
  • gdp_slot_index (int) - Position in GDP sequence
  • gdp_slot_time_utc (datetime2) - Assigned departure slot

General TMI Control

  • ctl_type (nvarchar 8) - GDP, AFP, GS, REROUTE, etc.
  • ctl_program (nvarchar 16) - Program name
  • ctl_element (nvarchar 32) - Controlled element (airport, FCA)
  • ctl_exempt (bit) - Exemption status
  • ctl_exempt_reason (nvarchar 64) - Exemption reason

OOOI Times (SWIM Primary)

  • out_utc - Pushback/gate departure
  • off_utc - Takeoff
  • on_utc - Landing
  • in_utc - Arrival at gate

Last Verified: 2026-01-15 against live VATSIM_ADL Azure SQL database