14 KiB
Tabulae Aeriae — Project Scope Statement
Version: 1.0
Date: March 29, 2026
License: AGPL-3.0 (pending final confirmation)
Repository: Forgejo (self-hosted)
1. Project Overview
Tabulae Aeriae is a self-hostable, web-based pilot logbook system designed to provide FAA-compliant flight record keeping, robust reporting, currency tracking, and data visualization. The system targets two tiers of users: a free open-source tier for non-commercial pilots (Part 91, recreational, student) and a paid professional tier for commercial operators (Part 135, Part 121).
The project is motivated by the absence of a quality open-source, self-hostable pilot logbook that provides genuine FAA regulatory compliance, one-click report generation for common FAA forms, and a data-driven currency tracking engine. Existing open-source projects (web-logbook, jetlog, flightlog) are either EASA-focused, passenger flight trackers, or lack the reporting depth that professional pilots require.
2. Objectives
Primary Objectives (v1)
- Provide a complete FAA-compliant logbook meeting 14 CFR 61.51 requirements
- Generate FAA Form 8710-1 Section III data with one-click reporting
- Generate FAA medical (8500-8) total time and last-6-months data with one-click reporting
- Track pilot currency under 14 CFR 61.57 (day, night, instrument, flight review) with real-time status display
- Visualize flight data through maps (route visualization) and charts (time breakdowns, trends)
- Support self-hosted deployment on low-resource hardware (Raspberry Pi / Docker Swarm)
- Maintain a complete audit trail of all logbook changes
Secondary Objectives (v1)
- Provide a skeleton framework for future EASA (FCL.050) compliance
- Support configurable, user-saveable custom reports for insurance, company, and personal use
- Support CSV import with a pluggable adapter architecture for future format support
- Track pilot qualifications, certificates, ratings, endorsements, and currency events in a unified table
- Track medical certificate status with expiry countdowns
Deferred Objectives (Paid Tier / Future Releases)
- PF/PNF (Pilot Flying / Pilot Not Flying) as first-class fields
- IOE (Initial Operating Experience) tracking
- High minimums captain status tracking
- Duty/rest tracking (Part 117, Part 135 Subpart F)
- Augmented operations crew management
- Operator-specific currency rule overrides (61.57(e) exceptions)
- GPS track import (KML/GPX) for precise route/night calculation
- SQLite backend option for simplified single-user deployment
- Full EASA compliance (FCL.050 fields, EASA PDF export formats)
- OAuth2/OIDC/LDAP authentication providers
3. Technical Architecture
Stack
| Layer | Technology | Rationale |
|---|---|---|
| Backend | Go with Chi router | Low memory footprint, single binary, fast compilation, strong stdlib |
| Database | PostgreSQL 16 | Robust, supports JSONB for flexible fields, trigger-based audit trail, already deployed on target infrastructure |
| Frontend | React + Vite + Tailwind CSS | Rich interactivity for report builder, maps, charts; static build embedded in Go binary |
| Charts | Recharts | React-native, lightweight, composable |
| Maps | react-leaflet | React-native, lighter than OpenLayers, strong community |
| Deployment | Docker (multi-stage build) | Single image containing Go binary with embedded frontend assets; no runtime dependencies on Node or Nginx |
Deployment Target
Docker Swarm on Raspberry Pi cluster. Estimated resource consumption:
| Container | Memory Limit | CPU Limit |
|---|---|---|
| Tabulae Aeriae (Go + static assets) | 128 MB | 0.5 CPU |
| PostgreSQL 16 Alpine | 256 MB | 0.5 CPU |
| Total | ~384 MB | 1.0 CPU |
Build Pipeline
Multi-stage Docker build:
- Stage 1 (Node): Build React frontend with Vite → static assets
- Stage 2 (Go): Compile Go binary embedding frontend via
embed.FS - Stage 3 (Alpine): Minimal runtime image with binary + CA certificates + timezone data
Final image size: estimated 20–30 MB.
4. Data Architecture
Database Tables (v1 — 10 tables)
| Table | Purpose |
|---|---|
users |
Pilot accounts, authentication, regulatory profile, tier |
flights |
Core logbook entries — all time fields, approaches, custom fields |
aircraft |
Real aircraft and simulators/FTDs — tail numbers, type, attributes, device info |
airports |
Seeded from OurAirports; includes user-defined custom airports |
airport_identifiers |
Maps current and former airport codes to physical airports |
pilot_qualifications |
Certificates, ratings, endorsements, and currency events (IPC, flight review, checkrides) |
medical_certificates |
Medical class, issue/expiry dates, examiner info |
saved_reports |
User-saved report configurations with JSONB config |
currency_rules |
JSON-driven currency rule definitions (system and user-defined) |
audit_log |
Append-only changelog triggered by INSERT/UPDATE/DELETE on tracked tables |
Key Schema Design Principles
- All times stored as INTEGER minutes. Floating-point hours introduce rounding errors that accumulate over a career. Rounding to display hours (truncate, round-nearest, or round-up) is applied only at the reporting layer, after aggregation.
- All timestamps stored as TIMESTAMPTZ in UTC. No local time in storage or computation. Local time conversion is a display-layer concern only.
- No pre-aggregated compound categories. Fields like "cross-country while under instruction" are derived at query time using
LEAST()intersection logic. Pilots log atomic observations; the system computes intersections. - JSONB for flexible structures. Instrument approaches (variable count and type), custom fields (company/insurance-specific), and report configurations all use JSONB for schema flexibility without sacrificing queryability.
- Historical accuracy over normalization. The
tail_numberis denormalized onto the flight record because aircraft registrations change. Thedeparture_airportandarrival_airportstore the code the pilot used at the time, even if that code has since been retired. - Simulators and FTDs in the aircraft table. Training devices are aircraft for logging purposes. A boolean
is_simulatorflag plus device-specific nullable columns (level, serial, approval number) avoid a separate table and dual foreign key complexity.
Airport Data
Source: OurAirports (davidmegginson/ourairports-data), Unlicense. Over 78,000 airports worldwide, updated daily, includes closed airports.
Strategy: Embed a snapshot at build time for offline-capable first boot. Provide an admin command (--refresh-airports) or API endpoint to pull updated data on demand.
Historical code changes (e.g., 09G → KTEW) are handled by the airport_identifiers table, which maps multiple codes to a single physical airport with is_current and date-range fields.
5. Reporting
Built-in Reports (One-Click)
| Report | Output |
|---|---|
| FAA 8710-1 Section III | Complete pilot time matrix — all categories, classes, and time fields. Derived at query time from atomic flight fields using LEAST() for compound categories. Rounded to whole hours per user preference. |
| FAA Medical (8500-8) | Total all-time hours and total hours in last 6 calendar months. Two numbers, one click. |
| Currency Status | Real-time status of all active currency rules — green/yellow/red with days remaining and remediation guidance. |
Configurable Reports
Users can create and save custom reports by selecting date range (fixed, rolling, or all-time), filters (aircraft, category/class, airport), grouping (month, year, type, category/class), columns to include, and output format (on-screen, PDF, CSV).
Report configurations are stored as JSONB and can be pinned to the dashboard for one-click access.
8710 Derivation Method
Compound time categories are derived using LEAST() logic at query time:
- XC Instruction Received =
SUM(LEAST(cross_country, dual_received))on flights where both > 0 - Night PIC/SIC =
SUM(LEAST(night_time, pic_time + sic_time))on flights where both > 0
This prevents the aggregation errors that occur when pilots manually pre-compute compound categories. The pilot logs atomic observations; the system handles the math.
Rounding is applied after aggregation (sum all minutes across all flights, then round to hours), not per-flight, to prevent cumulative rounding error.
6. Currency Tracking
Engine Design
Currency rules are data, not code. Each rule is a JSON configuration stored in the currency_rules table. The engine evaluates rules against flight records and qualification events to produce real-time status.
FAA Rules (v1)
| Rule | Regulation | Lookback | Requirements |
|---|---|---|---|
| Day currency (per category/class/type) | 61.57(a) | 90 days rolling | 3 takeoffs + 3 landings |
| Night currency (per category/class/type) | 61.57(b) | 90 days rolling | 3 night takeoffs + 3 night full-stop landings |
| Instrument currency | 61.57(c) | 6 calendar months | 6 approaches + holding + tracking |
| Instrument grace period | 61.57(c)/(d) | Months 7–12 after lapse | Same tasks to self-restore; IPC required after month 12 |
| Flight review | 61.56 | 24 calendar months | Flight review, checkride, or WINGS phase |
| Medical certificate | 61.23 | Per class/age | Valid medical not expired |
Custom Rules
Users can define personal minimums, insurance requirements, or company-specific rules using the same engine.
Night Definition Handling
The night_time field stores sunset-to-sunrise time (the standard logging definition). Night takeoff/landing classification for currency purposes uses the 1-hour-after-sunset to 1-hour-before-sunrise standard, auto-suggested from departure/arrival times and airport coordinates.
Night Time Auto-Calculation
Solar position computed at intervals along the great circle route between departure and arrival airports. Accounts for aircraft movement relative to the terminator — critical for long-haul flights crossing significant longitude. Produces the sunset-to-sunrise value for logging. Always pilot-overridable.
7. Authentication & Authorization
Architecture
Single auth system with a pluggable provider interface. No separate codepaths for free vs. commercial.
v1 provider: Username/password with bcrypt hashing and JWT token issuance.
Future providers (same interface): OAuth2/OIDC (Google, generic), LDAP. Adding a provider means implementing the AuthProvider interface — no changes to middleware, handlers, or session management.
Feature Gating
Tier-based authorization middleware reads a tier field (free/professional) and gates access to paid-tier features. Auth and authorization are separate concerns — the auth system doesn't know about tiers.
8. Import / Export
Import
Pluggable adapter architecture. Each adapter implements an ImportAdapter interface with Detect() (auto-format detection) and Parse() (conversion to internal model) methods.
v1 adapter: Generic CSV with user-defined column mapping.
Future adapters: ForeFlight, LogTen Pro, Logbook Pro, AeroLog.
Import supports partial success (bad rows reported individually, good rows imported), duplicate detection, and detailed per-row error reporting.
Export
- CSV: Full logbook export
- PDF: Configurable column layout (user-defined logbook page format)
9. Visualization
Flight Map
Interactive world map (react-leaflet) displaying great circle routes between airports. Filterable by date range, aircraft, and category/class.
Statistics Dashboard
Recharts-based charts showing: time by category/class over time, cumulative hours, flights by month/year, time by aircraft type, airport frequency, and custom breakdowns.
10. Audit Trail
PostgreSQL trigger-based changelog. Every INSERT, UPDATE, and DELETE on tracked tables (flights, aircraft, users) writes a row to audit_log with the full old and new row values as JSONB. The changed_by user ID is captured via a PostgreSQL session variable set by the application middleware.
Enables: full reconstruction of any record's history at any point in time, regulatory compliance documentation, and dispute resolution for commercial operators.
11. EASA Skeleton
FAA compliance is the v1 target. EASA support is prepared via:
- A
regulatory_profilefield on the user record (values:faa,easa,both) - Nullable EASA-specific columns on
flightsandaircrafttables (multi-pilot time, co-pilot time, SPSE/SPME, function type, etc.) - These columns are hidden in the UI when
regulatory_profile = 'faa' - Full EASA implementation is a future release — the schema is ready, the UI and reporting are not
12. Out of Scope (v1)
- Duty/rest tracking (Part 117, Part 135 Subpart F)
- Augmented operations / multi-crew scheduling
- PF/PNF as first-class schema fields (available via custom_fields JSONB)
- IOE tracking as a first-class field
- High minimums captain tracking
- Operator-specific currency overrides (61.57(e) exceptions)
- GPS track import (KML/GPX)
- SQLite backend
- Full EASA compliance
- OAuth2/OIDC/LDAP authentication
- Mobile-native applications (responsive web only)
- Integration with external services (ForeFlight, Garmin Pilot, etc.)
- Electronic endorsement/signature system
- Multi-tenant SaaS hosting infrastructure
13. Risks and Mitigations
| Risk | Impact | Mitigation |
|---|---|---|
| FAA regulatory changes | Currency rules or reporting requirements change | Rules are data-driven JSON configs; updates are database migrations, not code changes |
| PostgreSQL resource consumption on Pi | Exceeds memory limits under load | Alpine image, connection pooling, aggressive work_mem tuning; schema is simple (10 tables) |
| Airport data staleness | New airports or code changes not reflected | Build-time embed with on-demand refresh command; quarterly refresh cadence is sufficient |
| Night time calculation accuracy | Solar algorithm produces incorrect results for edge cases | Algorithm uses well-established NOAA equations; pilot always has manual override; auto-calculation is a suggestion, not a mandate |
| Scope creep from professional tier features | v1 becomes unshippable | Professional features are explicitly deferred and documented; schema is designed to accommodate them without destructive migrations |