tabulae-aeriae/documentation/administrative/tabulae-aeriae-data-dictionary.md

Tabulae Aeriae — Data Dictionary

Version: 1.0
Date: March 29, 2026

This document defines every table and field in the Tabulae Aeriae database schema. All tables use PostgreSQL. All timestamps are stored in UTC. All time durations are stored as INTEGER minutes.

---

Table: users

Pilot accounts, authentication credentials, and profile settings.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
username	VARCHAR(100)	NO	—	Unique login username
email	VARCHAR(255)	YES	—	Unique email address
password_hash	TEXT	NO	—	Bcrypt-hashed password
role	VARCHAR(20)	NO	'pilot'	User role: pilot, admin
tier	VARCHAR(20)	NO	'free'	Feature tier: free, professional
pilot_cert_num	VARCHAR(50)	YES	—	FAA pilot certificate number
regulatory_profile	VARCHAR(10)	NO	'faa'	Active regulatory framework: faa, easa, both
auth_provider	VARCHAR(30)	NO	'password'	Auth method: password, google, oidc, ldap
external_auth_id	VARCHAR(255)	YES	—	External provider's user ID (for OAuth/LDAP)
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp (UTC)
updated_at	TIMESTAMPTZ	NO	now()	Last modification timestamp (UTC)

Indexes:

• users_username_key — UNIQUE on username
• users_email_key — UNIQUE on email

Audit: Tracked (INSERT/UPDATE/DELETE trigger → audit_log)

---

Table: flights

Core logbook entries. Each row represents one flight leg or one simulator/training device session. All time fields are stored as INTEGER minutes. All timestamps are UTC.

Identification

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id. Owner of this flight record

Date, Route, and Block Times

Column	Type	Nullable	Default	Description
flight_date	DATE	NO	—	Date of flight (UTC date)
departure_airport	VARCHAR(10)	NO	—	Departure airport code (ICAO or FAA LID). Stored as-entered for historical accuracy
arrival_airport	VARCHAR(10)	NO	—	Arrival airport code. Stored as-entered for historical accuracy
route	TEXT	YES	—	Free-text route of flight (intermediate waypoints, airways, etc.)
departure_time	TIMESTAMPTZ	YES	—	Block out / wheels up time (UTC). NULL if pilot logs duration only
arrival_time	TIMESTAMPTZ	YES	—	Block in / wheels down time (UTC). NULL if pilot logs duration only

Aircraft

Column	Type	Nullable	Default	Description
aircraft_id	UUID	YES	—	FK → aircraft.id. Links to aircraft or simulator record
tail_number	VARCHAR(20)	NO	—	Aircraft registration or simulator identifier. Denormalized from aircraft record for historical accuracy (registrations change over time)

Flight Times (INTEGER minutes)

Column	Type	Nullable	Default	Description
total_time	INTEGER	NO	0	Total flight/session time. Authoritative value — may be auto-derived from departure/arrival times but is always manually overridable
pic_time	INTEGER	YES	0	Pilot-in-command time
sic_time	INTEGER	YES	0	Second-in-command time
solo_time	INTEGER	YES	0	Solo flight time
dual_received	INTEGER	YES	0	Dual instruction received time
dual_given	INTEGER	YES	0	Instruction given time (CFI logging)
cross_country	INTEGER	YES	0	Cross-country time. Pilot's determination per 14 CFR 61.1(b)
night_time	INTEGER	YES	0	Night time (sunset to sunrise definition per 61.51(b)(3)(i))

Instrument

Column	Type	Nullable	Default	Description
actual_instrument	INTEGER	YES	0	Time in actual instrument meteorological conditions
simulated_instrument	INTEGER	YES	0	Simulated instrument time (hood/foggles/view-limiting device)
holds	SMALLINT	YES	0	Number of holding patterns performed
approaches	JSONB	YES	'[]'	Instrument approaches. Array of objects: [{"type": "ILS", "airport": "KTPA", "runway": "19L", "notes": ""}]. Type validated against approach enum

Approach type enum values: ILS, LOC, LOC-BC, VOR, VOR/DME, NDB, RNAV (GPS), RNAV (RNP), LPV, LNAV, LNAV/VNAV, GPS, MLS, SDF, LDA, PAR, ASR, VISUAL, CONTACT

Takeoffs and Landings

Column	Type	Nullable	Default	Description
day_takeoffs	SMALLINT	YES	0	Day takeoffs (sun above horizon at departure airport at departure time)
night_takeoffs	SMALLINT	YES	0	Night takeoffs (1 hr after sunset to 1 hr before sunrise per 61.57(b) at departure airport)
day_landings	SMALLINT	YES	0	Day landings
night_landings	SMALLINT	YES	0	Night landings (1 hr after sunset to 1 hr before sunrise per 61.57(b) at arrival airport)
day_full_stop	SMALLINT	YES	0	Day full-stop landings (required for tailwheel currency)
night_full_stop	SMALLINT	YES	0	Night full-stop landings (required for night currency per 61.57(b))

Note on day/night classification: Takeoffs and landings are independently classified as day or night. A single-leg flight may have a day takeoff and a night landing (or vice versa). Auto-suggestion is computed from departure/arrival times and airport solar position, always pilot-overridable.

Simulator / Training Device

Column	Type	Nullable	Default	Description
simulator_time	INTEGER	YES	0	Time logged in an FFS, FTD, or ATD
sim_type	VARCHAR(20)	YES	—	Type of device used for this session: FFS, FTD, AATD, BATD. NULL for real aircraft flights
ground_training	INTEGER	YES	0	Ground instruction time (minutes)

Glider Operations

Column	Type	Nullable	Default	Description
launch_type	VARCHAR(20)	YES	—	Glider launch method: aero_tow, ground_launch, powered_launch, self_launch. NULL for non-glider flights. Required by 8710 for glider time reporting

People

Column	Type	Nullable	Default	Description
persons_on_board	TEXT	YES	—	Free text or crew listing
instructor_name	VARCHAR(100)	YES	—	Name of instructor (when receiving instruction)
instructor_cert_num	VARCHAR(50)	YES	—	Instructor's CFI certificate number

Remarks

Column	Type	Nullable	Default	Description
remarks	TEXT	YES	—	Free-text remarks, endorsement references, crew names, etc.

EASA Skeleton Fields

These columns are nullable and hidden in the UI when users.regulatory_profile = 'faa'. They exist to enable future EASA compliance without destructive schema migrations.

Column	Type	Nullable	Default	Description
multi_pilot_time	INTEGER	YES	—	EASA: multi-pilot operations time (minutes)
co_pilot_time	INTEGER	YES	—	EASA: co-pilot time (minutes)
spse_time	INTEGER	YES	—	EASA: single-pilot single-engine time (minutes)
spme_time	INTEGER	YES	—	EASA: single-pilot multi-engine time (minutes)
pic_name	VARCHAR(100)	YES	—	EASA: name of PIC (required for all flights under EASA)
function_type	VARCHAR(20)	YES	—	EASA: pilot function — PIC, co-pilot, dual, instructor

Custom and Extension Fields

Column	Type	Nullable	Default	Description
custom_fields	JSONB	YES	'{}'	User-defined fields for company, insurance, or personal tracking. Examples: {"pf": true, "ioe_time": 120, "company_route_id": "ATL-MIA-142"}

Metadata

Column	Type	Nullable	Default	Description
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp (UTC)
updated_at	TIMESTAMPTZ	NO	now()	Last modification timestamp (UTC)

Indexes:

• idx_flights_user_date — on (user_id, flight_date DESC)
• idx_flights_user_aircraft — on (user_id, aircraft_id)

Audit: Tracked (INSERT/UPDATE/DELETE trigger → audit_log)

---

Table: aircraft

Aircraft and training device records. Simulators and FTDs are stored here alongside real aircraft, distinguished by is_simulator = TRUE.

Identification

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id. Owner of this aircraft record
tail_number	VARCHAR(20)	NO	—	Aircraft registration (e.g., N453FX) or simulator identifier (e.g., SIM-GLF4-01)

Aircraft Details

Column	Type	Nullable	Default	Description
type_code	VARCHAR(10)	YES	—	ICAO type designator (e.g., C172, GLF4, B738)
make_model	VARCHAR(100)	NO	—	Full make and model (e.g., Gulfstream G450, Cessna 172S Skyhawk SP)
category_class	VARCHAR(50)	NO	—	FAA category/class. Values: ASEL, AMEL, ASES, AMES, RH, RG, GL, LTA-B, LTA-A, PL

Aircraft Attributes

Column	Type	Nullable	Default	Description
is_complex	BOOLEAN	YES	FALSE	Complex aircraft (retractable gear + flaps + controllable prop)
is_high_perf	BOOLEAN	YES	FALSE	High performance (>200 HP)
is_tailwheel	BOOLEAN	YES	FALSE	Conventional landing gear (tailwheel/taildragger)
is_taa	BOOLEAN	YES	FALSE	Technologically Advanced Aircraft (PFD + MFD + 2-axis autopilot)
gear_type	VARCHAR(20)	YES	—	Landing gear type: fixed_tri, retract, conventional, float, amphibious, ski
engine_type	VARCHAR(20)	YES	—	Engine type: piston, turboprop, jet, electric
num_engines	SMALLINT	YES	1	Number of engines

Simulator / Training Device Fields

Column	Type	Nullable	Default	Description
is_simulator	BOOLEAN	YES	FALSE	TRUE for FFS, FTD, ATD devices; FALSE for real aircraft
device_level	VARCHAR(20)	YES	—	Qualification level: FFS_A, FFS_B, FFS_C, FFS_D, FTD_4, FTD_5, FTD_6, FTD_7, AATD, BATD. NULL for real aircraft
device_serial	VARCHAR(50)	YES	—	Device serial number or regulatory identifier. NULL for real aircraft
device_approval	VARCHAR(100)	YES	—	FAA approval/qualification number. NULL for real aircraft

EASA Skeleton Fields

Column	Type	Nullable	Default	Description
easa_type_designator	VARCHAR(20)	YES	—	EASA type designator (may differ from ICAO)
sp_mp	VARCHAR(5)	YES	—	Single-pilot or multi-pilot: SP, MP

Metadata

Column	Type	Nullable	Default	Description
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp (UTC)
updated_at	TIMESTAMPTZ	NO	now()	Last modification timestamp (UTC)

Indexes:

• UNIQUE on (user_id, tail_number)

Audit: Tracked (INSERT/UPDATE/DELETE trigger → audit_log)

---

Table: airports

Airport reference data. Seeded from OurAirports (davidmegginson/ourairports-data). Users may add custom airports.

Column	Type	Nullable	Default	Description
id	SERIAL	NO	auto-increment	Primary key
icao_code	VARCHAR(4)	YES	—	ICAO airport code (e.g., KJFK)
faa_code	VARCHAR(4)	YES	—	FAA Location Identifier (e.g., JFK, 7B2). For US airports, may differ from ICAO
iata_code	VARCHAR(3)	YES	—	IATA code (e.g., JFK)
name	VARCHAR(200)	NO	—	Airport name
city	VARCHAR(100)	YES	—	Primary municipality served
state_region	VARCHAR(100)	YES	—	State or administrative region
country	VARCHAR(2)	YES	—	ISO 3166-1 alpha-2 country code
latitude	DECIMAL(10,7)	YES	—	Latitude in decimal degrees (positive = north)
longitude	DECIMAL(10,7)	YES	—	Longitude in decimal degrees (positive = east)
elevation_ft	INTEGER	YES	—	Field elevation MSL in feet
type	VARCHAR(30)	YES	—	Airport type: large_airport, medium_airport, small_airport, heliport, seaplane_base, closed
is_user_defined	BOOLEAN	YES	FALSE	TRUE for user-added custom airports/strips
user_id	UUID	YES	—	FK → users.id. NULL for system (OurAirports) records
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Indexes:

• idx_airports_icao — on icao_code
• idx_airports_faa — on faa_code
• idx_airports_name — GIN index on to_tsvector('english', name) for full-text search

---

Table: airport_identifiers

Maps current and historical airport codes to physical airport records. Enables lookup of retired codes (e.g., 09G → KTEW) while preserving historical accuracy in flight records.

Column	Type	Nullable	Default	Description
id	SERIAL	NO	auto-increment	Primary key
airport_id	INTEGER	NO	—	FK → airports.id. The physical airport this identifier refers to
identifier	VARCHAR(10)	NO	—	The airport code (e.g., 09G, KTEW, JFK)
identifier_type	VARCHAR(10)	NO	—	Code type: icao, faa, iata, former
is_current	BOOLEAN	YES	TRUE	TRUE if this code is currently active for the airport
effective_from	DATE	YES	—	Date this code became active (NULL if unknown)
effective_to	DATE	YES	—	Date this code was retired (NULL if currently active)
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Indexes:

• UNIQUE on (identifier, identifier_type) WHERE is_current = TRUE
• idx_airport_ident_all — on identifier (covers former codes)

Resolution flow: User enters airport code → system searches airport_identifiers → finds physical airport → resolves lat/long for map display → stores original code in flight record unchanged.

---

Table: pilot_qualifications

Unified table for pilot certificates, ratings, endorsements, and currency events. Each row represents one qualification or one currency event.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id
qual_type	VARCHAR(30)	NO	—	Qualification type (see enum below)
certificate_grade	VARCHAR(30)	YES	—	For pilot_certificate: student, private, commercial, atp, atp_restricted
certificate_number	VARCHAR(50)	YES	—	FAA certificate number
rating_name	VARCHAR(100)	YES	—	Rating or endorsement name (e.g., ASEL, AMEL, CE-500, Instrument Airplane, Complex, High Performance, High Altitude)
date_earned	DATE	NO	—	Date the qualification was earned or event occurred
expiry_date	DATE	YES	—	Expiration date (NULL for non-expiring qualifications)
flight_id	UUID	YES	—	FK → flights.id. Links to the flight where this event occurred (NULL for events not tied to a flight record, e.g., ground-only checkrides, sim-only IPCs)
issuing_authority	VARCHAR(100)	YES	—	Issuing body (e.g., FAA, EASA)
instructor_name	VARCHAR(100)	YES	—	Name of endorsing instructor (for endorsements and currency events)
instructor_cert	VARCHAR(50)	YES	—	Endorsing instructor's certificate number
examiner_name	VARCHAR(100)	YES	—	Name of designated examiner (for checkrides, IPCs)
regulation_ref	VARCHAR(50)	YES	—	Regulatory reference (e.g., 61.31(e), 61.56, 61.57(d))
document_ref	TEXT	YES	—	Reference to scan/photo of supporting document (future: attachment support)
notes	TEXT	YES	—	Free-text notes
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp
updated_at	TIMESTAMPTZ	NO	now()	Last modification timestamp

qual_type enum values:

Value	Category	Description
pilot_certificate	Certificate	Pilot certificate (student, private, commercial, ATP)
flight_instructor	Certificate	CFI, CFII, MEI certificate
ground_instructor	Certificate	Basic, Advanced, or Instrument ground instructor
type_rating	Rating	Aircraft type rating (e.g., CE-500, GLF4, B737)
instrument_rating	Rating	Instrument rating by category
category_class_rating	Rating	Additional category/class rating
endorsement	Endorsement	Instructor endorsement (complex, high-perf, high-altitude, tailwheel, etc.)
flight_review	Currency event	Flight review per 61.56
ipc	Currency event	Instrument proficiency check per 61.57(d)
checkride	Currency event	Practical test (also satisfies flight review)
wings_phase	Currency event	WINGS Pilot Proficiency Program phase completion
proficiency_check	Currency event	Part 135/121 proficiency check
135_recurrent	Currency event	Part 135 recurrent training completion
121_recurrent	Currency event	Part 121 recurrent training completion

Indexes:

• idx_qual_user_type — on (user_id, qual_type)
• idx_qual_user_date — on (user_id, date_earned DESC)

---

Table: medical_certificates

FAA medical certificate records.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id
class	SMALLINT	NO	—	Medical class: 1, 2, 3
issue_date	DATE	NO	—	Date medical was issued
expiry_date	DATE	NO	—	Calculated expiration date per 14 CFR 61.23 (based on age and class)
examiner_name	VARCHAR(100)	YES	—	Aviation Medical Examiner (AME) name
notes	TEXT	YES	—	Free-text notes (restrictions, limitations, BasicMed status, etc.)
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

---

Table: saved_reports

User-saved report configurations for one-click report generation.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id
name	VARCHAR(100)	NO	—	Report name (e.g., 8710 Application, Insurance Renewal, Annual Totals)
description	TEXT	YES	—	User-provided description of this report
report_type	VARCHAR(30)	NO	—	Report category: summary, detailed, 8710, medical, currency, by_aircraft, custom
config	JSONB	NO	—	Full report configuration: date range, filters, grouping, columns, output format
is_quick_access	BOOLEAN	YES	FALSE	If TRUE, report appears as a dashboard button for one-click access
sort_order	SMALLINT	YES	0	Display order for quick-access buttons
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp
updated_at	TIMESTAMPTZ	NO	now()	Last modification timestamp

Config JSONB structure example:

{
  "date_range": {"type": "rolling", "months": 6},
  "filters": {"category_class": ["AMEL"], "aircraft_type": ["GLF4"]},
  "grouping": "month",
  "columns": ["total_time", "pic_time", "night_time", "actual_instrument", "approaches"],
  "output_format": "screen",
  "rounding": "truncate"
}

---

Table: currency_rules

JSON-driven currency rule definitions. System-defined rules (FAA defaults) have user_id = NULL. Users can add custom rules for personal minimums or company requirements.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	YES	—	FK → users.id. NULL for system-defined rules
name	VARCHAR(100)	NO	—	Rule display name (e.g., Night Currency — ASEL, IFR Currency)
regulation_ref	VARCHAR(50)	YES	—	Regulatory reference (e.g., 61.57(a), 61.57(c))
rule_config	JSONB	NO	—	Complete rule configuration (lookback window, requirements, thresholds, grace periods, remediation text)
is_active	BOOLEAN	YES	TRUE	Whether this rule is currently being evaluated
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

See Currency Rules Engine document for complete rule_config schema and examples.

---

Table: audit_log

Append-only changelog capturing all modifications to tracked tables. Populated automatically by PostgreSQL triggers on flights, aircraft, and users.

Column	Type	Nullable	Default	Description
id	BIGSERIAL	NO	auto-increment	Primary key
table_name	VARCHAR(50)	NO	—	Name of the modified table
record_id	UUID	NO	—	Primary key of the modified record
action	VARCHAR(10)	NO	—	Operation type: INSERT, UPDATE, DELETE
old_value	JSONB	YES	—	Full row before modification (NULL for INSERT)
new_value	JSONB	YES	—	Full row after modification (NULL for DELETE)
changed_by	UUID	YES	—	FK → users.id. User who made the change. Set via PostgreSQL session variable app.current_user_id
changed_at	TIMESTAMPTZ	NO	now()	Timestamp of the change (UTC)

Indexes:

• idx_audit_record — on (table_name, record_id)
• idx_audit_time — on changed_at

Implementation: PostgreSQL AFTER INSERT OR UPDATE OR DELETE trigger on each tracked table calls audit_trigger_fn(). The application middleware sets SET LOCAL app.current_user_id = $1 on each transaction so the trigger can capture the acting user.

---

Deferred Table: pilot_status_periods

Not included in v1 schema. Designed for paid tier.

Tracks date-range statuses such as high minimums captain periods.

Column	Type	Nullable	Default	Description
id	UUID	NO	gen_random_uuid()	Primary key
user_id	UUID	NO	—	FK → users.id
status_type	VARCHAR(50)	NO	—	Status type: high_mins, line_check_required, probationary, etc.
start_date	DATE	NO	—	Start of status period
end_date	DATE	YES	—	End of status period (NULL = still active)
notes	TEXT	YES	—	Free-text notes
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

---

Foreign Key Reference Map

users
  ├── flights.user_id
  ├── aircraft.user_id
  ├── airports.user_id (user-defined airports only)
  ├── pilot_qualifications.user_id
  ├── medical_certificates.user_id
  ├── saved_reports.user_id
  ├── currency_rules.user_id (NULL for system rules)
  └── audit_log.changed_by

aircraft
  └── flights.aircraft_id

flights
  └── pilot_qualifications.flight_id

airports
  └── airport_identifiers.airport_id