# Pilot Logbook — Architecture Overview ## Project Summary A self-hostable, web-based pilot logbook system. FAA-compliant (14 CFR 61.51) by default, with optional EASA compliance as a configurable profile. Designed for low-resource deployment (Raspberry Pi / Docker Swarm). PostgreSQL backend, Go API server, lightweight web frontend. **Licensing model:** Free open-source tier for non-commercial pilots (Part 91, recreational, student). Paid tier for professional pilots (Part 135, 121) with crew management, duty/rest calculations, and advanced compliance features. --- ## High-Level Architecture ``` ┌─────────────────────────────────────────────────┐ │ Browser │ │ (React + Tailwind or HTMX) │ └──────────────────────┬──────────────────────────┘ │ HTTPS (JSON API) ▼ ┌─────────────────────────────────────────────────┐ │ Go API Server │ │ │ │ ┌───────────┐ ┌──────────┐ ┌───────────────┐ │ │ │ Auth/RBAC │ │ Logbook │ │ Reporting │ │ │ │ Middleware │ │ Service │ │ Engine │ │ │ └───────────┘ └──────────┘ └───────────────┘ │ │ ┌───────────┐ ┌──────────┐ ┌───────────────┐ │ │ │ Aircraft │ │ Airport │ │ Audit Trail │ │ │ │ Service │ │ Service │ │ Service │ │ │ └───────────┘ └──────────┘ └───────────────┘ │ │ ┌───────────┐ ┌──────────┐ │ │ │ Currency │ │ Medical │ │ │ │ Tracker │ │ Reporter │ │ │ └───────────┘ └──────────┘ │ │ │ │ DB Interface (database/sql) │ └──────────────────────┬──────────────────────────┘ │ ▼ ┌─────────────────┐ │ PostgreSQL │ │ │ │ Audit triggers │ │ installed on │ │ tracked tables │ └─────────────────┘ ``` ### Key Design Decisions - **Go standard library router (net/http) or Chi** — Chi is minimal and well-proven. No heavy frameworks. Keeps binary small. - **database/sql with pgx driver** — Direct SQL, no ORM. Migrations handled by golang-migrate or a simple embedded migration system. - **Audit trail via PostgreSQL triggers** — Changes to logbook entries, aircraft, and user data are captured automatically at the DB level. The application doesn't need to remember to log; the trigger handles it. - **Frontend TBD** — Two reasonable paths: (1) React + Tailwind (like jetlog) for a modern SPA, or (2) Go templates + HTMX for a server-rendered approach that eliminates the Node.js build step entirely. The HTMX path is simpler to deploy, lighter, and arguably better suited to a Pi. Worth discussing. --- ## Database Schema (Core Tables) ### Users & Auth ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), username VARCHAR(100) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE, password_hash TEXT NOT NULL, role VARCHAR(20) NOT NULL DEFAULT 'pilot', -- pilot, admin pilot_cert_num VARCHAR(50), -- FAA certificate number created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ``` ### Aircraft ```sql CREATE TABLE aircraft ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), tail_number VARCHAR(20) NOT NULL, type_code VARCHAR(10), -- ICAO type designator (e.g., C172) make_model VARCHAR(100) NOT NULL, -- Cessna 172S Skyhawk SP category_class VARCHAR(50) NOT NULL, -- ASEL, AMEL, RH, Glider, etc. is_complex BOOLEAN DEFAULT FALSE, is_high_perf BOOLEAN DEFAULT FALSE, is_tailwheel BOOLEAN DEFAULT FALSE, is_taa BOOLEAN DEFAULT FALSE, -- Technologically Advanced Aircraft gear_type VARCHAR(20), -- fixed_tri, retract, conventional, float, etc. engine_type VARCHAR(20), -- piston, turboprop, jet, electric num_engines SMALLINT DEFAULT 1, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE(user_id, tail_number) ); ``` ### Airports ```sql -- Seeded from OurAirports or similar open dataset -- Users can also add custom/private strips CREATE TABLE airports ( id SERIAL PRIMARY KEY, icao_code VARCHAR(4), faa_code VARCHAR(4), -- FAA LID (e.g., JFK, 7B2) iata_code VARCHAR(3), name VARCHAR(200) NOT NULL, city VARCHAR(100), state_region VARCHAR(100), country VARCHAR(2), latitude DECIMAL(10, 7), longitude DECIMAL(10, 7), elevation_ft INTEGER, type VARCHAR(30), -- large_airport, medium_airport, small_airport, heliport, seaplane_base, closed is_user_defined BOOLEAN DEFAULT FALSE, user_id UUID REFERENCES users(id), -- NULL for system airports created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_airports_icao ON airports(icao_code); CREATE INDEX idx_airports_faa ON airports(faa_code); ``` ### Flight Log Entries (the core table) This is the big one. Columns are driven by FAA 14 CFR 61.51 requirements plus common practical fields. ```sql CREATE TABLE flights ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), -- Date & Route flight_date DATE NOT NULL, departure_airport VARCHAR(10) NOT NULL, -- ICAO or FAA LID arrival_airport VARCHAR(10) NOT NULL, route TEXT, -- free-text route of flight -- Aircraft aircraft_id UUID REFERENCES aircraft(id), tail_number VARCHAR(20) NOT NULL, -- denormalized for historical accuracy -- Times (stored in minutes for easy math; displayed as HH:MM) total_time INTEGER NOT NULL DEFAULT 0, -- total flight time pic_time INTEGER DEFAULT 0, sic_time INTEGER DEFAULT 0, solo_time INTEGER DEFAULT 0, dual_received INTEGER DEFAULT 0, dual_given INTEGER DEFAULT 0, -- CFI logging instruction given cross_country INTEGER DEFAULT 0, night_time INTEGER DEFAULT 0, -- Instrument actual_instrument INTEGER DEFAULT 0, simulated_instrument INTEGER DEFAULT 0, holds SMALLINT DEFAULT 0, -- Instrument Approaches (stored as JSONB for flexibility) -- e.g., [{"type": "ILS", "airport": "KTPA", "runway": "19L"}, ...] approaches JSONB DEFAULT '[]', -- Landings day_landings SMALLINT DEFAULT 0, night_landings SMALLINT DEFAULT 0, day_full_stop SMALLINT DEFAULT 0, -- needed for tailwheel currency night_full_stop SMALLINT DEFAULT 0, -- needed for night currency -- Simulator / Training Device simulator_time INTEGER DEFAULT 0, sim_type VARCHAR(20), -- FTD, AATD, BATD, FFS -- Ground Training ground_training INTEGER DEFAULT 0, -- People persons_on_board TEXT, -- free text or JSONB instructor_name VARCHAR(100), instructor_cert_num VARCHAR(50), -- Remarks & Endorsements remarks TEXT, -- Metadata is_checkride BOOLEAN DEFAULT FALSE, is_ipc BOOLEAN DEFAULT FALSE, -- instrument proficiency check is_bfr BOOLEAN DEFAULT FALSE, -- biennial flight review (now "flight review") flight_review BOOLEAN DEFAULT FALSE, -- alias for BFR per 61.56 created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_flights_user_date ON flights(user_id, flight_date DESC); CREATE INDEX idx_flights_user_aircraft ON flights(user_id, aircraft_id); ``` ### Audit Log ```sql CREATE TABLE audit_log ( id BIGSERIAL PRIMARY KEY, table_name VARCHAR(50) NOT NULL, record_id UUID NOT NULL, action VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE field_name VARCHAR(100), -- NULL for INSERT/DELETE old_value TEXT, new_value TEXT, changed_by UUID REFERENCES users(id), changed_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_audit_record ON audit_log(table_name, record_id); CREATE INDEX idx_audit_time ON audit_log(changed_at); -- Trigger function (simplified; real version would iterate over changed columns) CREATE OR REPLACE FUNCTION audit_trigger_fn() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO audit_log(table_name, record_id, action, new_value, changed_at) VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW)::TEXT, now()); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log(table_name, record_id, action, old_value, new_value, changed_at) VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD)::TEXT, row_to_json(NEW)::TEXT, now()); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO audit_log(table_name, record_id, action, old_value, changed_at) VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD)::TEXT, now()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; -- Attach to flights table CREATE TRIGGER flights_audit AFTER INSERT OR UPDATE OR DELETE ON flights FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn(); ``` ### Saved Reports ```sql CREATE TABLE saved_reports ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), name VARCHAR(100) NOT NULL, -- "8710 Application", "Insurance Renewal", "Annual Totals" description TEXT, report_type VARCHAR(30) NOT NULL, -- summary, detailed, 8710, medical, currency, custom config JSONB NOT NULL, -- filters, date ranges, grouping, columns to include is_quick_access BOOLEAN DEFAULT FALSE, -- pin to dashboard for one-click access created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ``` ### Currency Tracking ```sql -- Predefine standard FAA currency rules; users can add custom ones CREATE TABLE currency_rules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id), -- NULL = system-defined name VARCHAR(100) NOT NULL, -- "Night Currency (61.57a)", "IFR Currency (61.57c)" regulation_ref VARCHAR(50), -- "61.57(a)", "61.57(c)", etc. rule_config JSONB NOT NULL, -- defines the lookback window, required counts, etc. is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Example rule_config for night passenger currency: -- { -- "lookback_days": 90, -- "required": { -- "night_full_stop": 3 -- }, -- "applies_to": { -- "category_class": ["ASEL", "AMEL"] -- } -- } ``` ### Medical Certificates (for the one-click medical report) ```sql CREATE TABLE medical_certificates ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), class SMALLINT NOT NULL, -- 1, 2, 3 issue_date DATE NOT NULL, expiry_date DATE NOT NULL, -- calculated per 61.23 based on age + class examiner_name VARCHAR(100), notes TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ``` --- ## Reporting Engine Design The goal is to make reporting maximally flexible without requiring users to write SQL. ### Built-in Report Types | Report | Description | Key Fields | |--------|-------------|------------| | **8710** | FAA Airman Certificate application totals | Total time, PIC, SIC, XC, night, instrument (actual/sim), category/class breakdowns | | **Medical** | Total time + last 6 months | One-click: total all-time hours, total hours in last 6 calendar months | | **Currency** | Current status of all active currency rules | Green/yellow/red status per rule, days remaining, what's needed to regain currency | | **Insurance** | Configurable — typically total time, time in type, retract time, last 90 days | User defines which fields matter | | **Period Summary** | Totals for any date range | All time categories summed for the range | | **By Aircraft** | Breakdown by tail number or type | Time per aircraft/type with sub-totals | ### Custom Report Builder Users can create a saved report by selecting: 1. **Date range** — fixed dates, rolling window (last N days/months), or all-time 2. **Filters** — by aircraft, category/class, airport, tags 3. **Grouping** — by month, year, aircraft type, category/class 4. **Columns** — which time fields to include 5. **Output format** — on-screen table, PDF, CSV The `saved_reports.config` JSONB column stores all of this, so reconstructing the report is just loading the config and re-running the query. ### One-Click Reports Certain reports (8710 totals, medical totals, currency status) are flagged as `is_quick_access = true` and rendered as dashboard widgets or a single button press. The medical report specifically: - **Total time (all time)** — sum of `total_time` across all flights - **Last 6 calendar months** — sum of `total_time` where `flight_date >= first day of (current month - 6)` No configuration needed, no date pickers. One click, two numbers. --- ## API Structure (Go) ``` /api/v1/ ├── auth/ │ ├── POST /login │ ├── POST /logout │ └── POST /refresh │ ├── flights/ │ ├── GET / -- list (paginated, filterable) │ ├── POST / -- create │ ├── GET /:id -- get one │ ├── PUT /:id -- update │ ├── DELETE /:id -- delete │ └── POST /import -- CSV/bulk import │ ├── aircraft/ │ ├── GET / │ ├── POST / │ ├── GET /:id │ ├── PUT /:id │ └── DELETE /:id │ ├── airports/ │ ├── GET / -- search/autocomplete │ ├── GET /:code -- lookup by ICAO or FAA LID │ └── POST / -- add custom airport │ ├── reports/ │ ├── GET /8710 -- 8710 totals │ ├── GET /medical -- medical totals (one-click) │ ├── GET /currency -- all currency statuses │ ├── GET /summary?from=&to= -- period summary │ ├── POST /custom -- run a custom report from config │ ├── GET /saved -- list saved reports │ ├── POST /saved -- save a report config │ ├── PUT /saved/:id -- update saved report │ └── DELETE /saved/:id -- delete saved report │ ├── audit/ │ ├── GET / -- paginated audit log │ └── GET /record/:table/:id -- audit history for a specific record │ ├── medical/ │ ├── GET / │ ├── POST / │ └── GET /status -- current medical validity │ └── export/ ├── GET /csv -- full logbook CSV export └── GET /pdf -- PDF export (FAA format) ``` --- ## Go Project Layout ``` / ├── cmd/ │ └── server/ │ └── main.go -- entry point ├── internal/ │ ├── config/ -- env var parsing, app config │ ├── database/ │ │ ├── migrations/ -- SQL migration files │ │ ├── postgres.go -- connection setup │ │ └── migrate.go -- migration runner │ ├── models/ -- struct definitions │ │ ├── flight.go │ │ ├── aircraft.go │ │ ├── airport.go │ │ ├── user.go │ │ └── report.go │ ├── handlers/ -- HTTP handlers (one file per resource) │ │ ├── flights.go │ │ ├── aircraft.go │ │ ├── reports.go │ │ └── auth.go │ ├── services/ -- business logic │ │ ├── flights.go │ │ ├── currency.go │ │ ├── reporting.go │ │ └── audit.go │ ├── middleware/ │ │ ├── auth.go │ │ ├── logging.go │ │ └── cors.go │ └── seed/ -- airport data seeding │ └── airports.go ├── web/ -- frontend assets (if using HTMX/templates) │ ├── templates/ │ └── static/ ├── Dockerfile ├── docker-compose.yml ├── go.mod └── go.sum ``` --- ## Deployment (Docker Compose for Swarm) ```yaml version: "3.8" services: logbook: image: yourproject/logbook:latest ports: - "8080:8080" environment: DB_HOST: postgres DB_PORT: 5432 DB_NAME: logbook DB_USER: logbook DB_PASSWORD_FILE: /run/secrets/db_password JWT_SECRET_FILE: /run/secrets/jwt_secret LOG_LEVEL: info secrets: - db_password - jwt_secret deploy: replicas: 1 resources: limits: memory: 128M # Go binary + serving requests cpus: "0.5" depends_on: - postgres postgres: image: postgres:16-alpine volumes: - pgdata:/var/lib/postgresql/data environment: POSTGRES_DB: logbook POSTGRES_USER: logbook POSTGRES_PASSWORD_FILE: /run/secrets/db_password secrets: - db_password deploy: resources: limits: memory: 256M cpus: "0.5" volumes: pgdata: secrets: db_password: external: true jwt_secret: external: true ``` Estimated total memory footprint: **~200-300MB** for both containers on a Pi. The Go binary itself will likely be 15-30MB and use ~20-50MB of RAM under normal load. --- ## Open Questions / Next Steps 1. **Frontend approach** — React SPA vs. Go templates + HTMX? HTMX eliminates the Node build pipeline entirely, keeps the Docker image smaller, and is arguably simpler. React gives richer interactivity for things like the report builder and map visualizations. A hybrid is possible (HTMX for most pages, a small JS bundle for the map/charts). 2. **Project name** — Needed before creating the repo. 3. **Map/visualization library** — OpenLayers (heavier, more capable) vs. Leaflet (lighter) vs. something React-based like react-simple-maps. Depends on frontend choice. 4. **EASA profile** — How deep to go in v1? Could be as simple as alternative column labels and a different PDF export layout, or a full parallel schema with EASA-specific fields (multi-pilot time, SPSE/SPME columns, etc.). 5. **Import formats** — ForeFlight CSV is probably the most common for FAA pilots. LogTen Pro is another big one. Both have documented export formats. 6. **Authentication** — JWT for API, but also support OAuth2 (Google, etc.) for the hosted/multi-user scenario? Or just username/password for v1? 7. **License choice** — Apache 2.0, MIT, or AGPL? AGPL would prevent someone from running a competing hosted service without open-sourcing their changes, which aligns with the free/paid tier model.