Database Design

Database Design

This document details the SQLite database schema, data sources, and design decisions for the tail-lookup database.

Overview

The tail-lookup database is a SQLite database containing FAA aircraft registration data. It consists of three tables and is optimized for read-heavy lookup operations.

Database Size: ~25MB Record Count: ~300,000 aircraft registrations Update Frequency: Daily at 6 AM UTC Source: FAA Releasable Aircraft Database

Schema Design

master Table

The master table contains aircraft registration records from the FAA MASTER.txt file.

CREATE TABLE master (
    n_number TEXT PRIMARY KEY,           -- Aircraft N-number (without 'N' prefix)
    serial_number TEXT,                  -- Manufacturer serial number
    mfr_mdl_code TEXT,                  -- Manufacturer model code (FK to acftref)
    eng_mfr_mdl TEXT,                   -- Engine manufacturer/model
    year_mfr INTEGER,                   -- Year of manufacture
    type_registrant TEXT,               -- Type of registrant (1-9 code)
    name TEXT,                          -- Registrant name
    street TEXT,                        -- Registrant street address
    street2 TEXT,                       -- Registrant street address line 2
    city TEXT,                          -- Registrant city
    state TEXT,                         -- Registrant state
    zip_code TEXT,                      -- Registrant ZIP code
    region TEXT,                        -- FAA region code
    county TEXT,                        -- County code
    country TEXT,                       -- Country code
    last_action_date TEXT,              -- Last action date (YYYYMMDD)
    cert_issue_date TEXT,               -- Certificate issue date (YYYYMMDD)
    certification TEXT,                 -- Aircraft certification (1-10 code)
    type_aircraft TEXT,                 -- Aircraft type code (1-9)
    type_engine TEXT,                   -- Engine type code (0-11)
    status_code TEXT,                   -- Registration status
    mode_s_code TEXT,                   -- Mode S transponder code
    fract_owner TEXT,                   -- Fractional ownership (Y/N)
    air_worth_date TEXT,                -- Airworthiness date (YYYYMMDD)
    other_names_1 TEXT,                 -- Other registered names
    other_names_2 TEXT,
    other_names_3 TEXT,
    other_names_4 TEXT,
    other_names_5 TEXT,
    expiration_date TEXT,               -- Registration expiration (YYYYMMDD)
    unique_id TEXT,                     -- Unique identifier
    kit_mfr TEXT,                       -- Kit manufacturer (if homebuilt)
    kit_model TEXT,                     -- Kit model (if homebuilt)
    mode_s_code_hex TEXT                -- Mode S code in hexadecimal
);

-- Index for JOIN optimization with acftref table
CREATE INDEX idx_mfr_mdl_code ON master(mfr_mdl_code);

Key Fields:

• n_number: Primary key, normalized without 'N' prefix (e.g., "172SP")
• mfr_mdl_code: Foreign key to acftref table for aircraft details
• year_mfr: Integer for easy filtering/sorting
• type_aircraft, type_engine: Numeric codes mapped to human-readable names in Python

Design Decisions:

• Why TEXT for n_number? N-numbers can contain letters and numbers, not purely numeric
• Why store without 'N' prefix? Normalization for consistent lookups
• Why TEXT for dates? FAA format is YYYYMMDD, stored as-is for simplicity
• Why separate address fields? Maintain FAA data structure, allow flexible queries
• Why index on mfr_mdl_code? This is the JOIN key, critical for performance

acftref Table

The acftref table contains aircraft model reference data from the FAA ACFTREF.txt file.

CREATE TABLE acftref (
    code TEXT PRIMARY KEY,              -- Manufacturer model code
    mfr TEXT,                           -- Manufacturer name
    model TEXT,                         -- Model designation
    type_acft TEXT,                     -- Aircraft type code (1-9)
    type_eng TEXT,                      -- Engine type code (0-11)
    ac_cat TEXT,                        -- Aircraft category
    build_cert_ind TEXT,                -- Builder certification indicator
    no_eng INTEGER,                     -- Number of engines
    no_seats INTEGER,                   -- Number of seats
    ac_weight TEXT,                     -- Aircraft weight class
    speed TEXT                          -- Speed class
);

Key Fields:

• code: Primary key, links to master.mfr_mdl_code
• mfr: Manufacturer name (e.g., "CESSNA", "BOEING")
• model: Model designation (e.g., "172S", "737-800")
• type_acft, type_eng: Numeric codes for type classification
• no_eng, no_seats: Integer values for specifications

Design Decisions:

• Why separate table? Normalize model data, avoid duplication
• Why INTEGER for counts? Enable numeric comparisons and aggregations
• Why TEXT for weight/speed? FAA uses class codes (e.g., "CLASS 1"), not numeric values

metadata Table

The metadata table stores database metadata and update tracking.

CREATE TABLE metadata (
    key TEXT PRIMARY KEY,               -- Metadata key
    value TEXT                          -- Metadata value
);

Current Metadata:

• last_updated: ISO 8601 timestamp of when database was built

Design Decisions:

• Why key-value structure? Flexible for adding new metadata without schema changes
• Why store last_updated? Used by health check endpoint to show data freshness
• Future use cases: Could store FAA data version, build hash, etc.

Data Source: FAA Releasable Aircraft Database

Source Information

Official URL: https://www.faa.gov/licenses_certificates/aircraft_certification/aircraft_registry/releasable_aircraft_download

File: ReleasableAircraft.zip (~30MB compressed)

Update Schedule: FAA updates daily at 11:30 PM CT (5:30 AM UTC)

Our Update Schedule: Daily at 6:00 AM UTC (30-minute buffer)

File Formats

MASTER.txt

Format: Fixed-width text file Records: ~300,000 active registrations Encoding: ASCII

Column Positions (from FAA documentation):

N-NUMBER          1-5
SERIAL_NUMBER     6-35
MFR_MDL_CODE      36-42
ENG_MFR_MDL       43-47
YEAR_MFR          48-51
TYPE_REGISTRANT   52
NAME              53-102
STREET            103-135
STREET2           136-168
CITY              169-186
STATE             187-188
ZIP_CODE          189-197
REGION            198-199
COUNTY            200-202
COUNTRY           203-204
LAST_ACTION_DATE  205-212
CERT_ISSUE_DATE   213-220
CERTIFICATION     221-228
TYPE_AIRCRAFT     229
TYPE_ENGINE       230
STATUS_CODE       231-240
MODE_S_CODE       241-248
FRACT_OWNER       249
AIR_WORTH_DATE    250-257
OTHER_NAMES_1     258-307
OTHER_NAMES_2     308-357
OTHER_NAMES_3     358-407
OTHER_NAMES_4     408-457
OTHER_NAMES_5     458-507
EXPIRATION_DATE   508-515
UNIQUE_ID         516-522
KIT_MFR           523-552
KIT_MODEL         553-572
MODE_S_CODE_HEX   573-582

Parsing Strategy:

1. Read file line by line
2. Extract each field using slice indices (positions - 1 for 0-based)
3. Strip whitespace from extracted strings
4. Convert year to integer if present
5. Skip header row
6. Insert in batch for performance

ACFTREF.txt

Format: Fixed-width text file Records: ~10,000 aircraft models Encoding: ASCII

Column Positions (from FAA documentation):

CODE              1-7
MFR               8-37
MODEL             38-57
TYPE_ACFT         58
TYPE_ENG          59-60
AC_CAT            61
BUILD_CERT_IND    62
NO_ENG            63
NO_SEATS          64-66
AC_WEIGHT         67-73
SPEED             74-77

Parsing Strategy:

1. Read file line by line
2. Extract each field using slice indices
3. Strip whitespace
4. Convert no_eng and no_seats to integers if present
5. Skip header row
6. Insert in batch for performance

Data Quality Considerations

Missing Data:

• Not all aircraft have year_mfr, serial_number, etc.
• Some fields are blank in FAA data
• Our API returns null for missing optional fields

Data Inconsistencies:

• Manufacturer names have varying formats (e.g., "CESSNA" vs "CESSNA AIRCRAFT CO")
• Some model codes in master don't exist in acftref (rare)
• Date formats are YYYYMMDD strings, not ISO 8601

Handling Strategy:

• Optional fields in Pydantic models allow null
• LEFT JOIN ensures we return data even if acftref lookup fails
• Input validation prevents malformed requests

Type Code Mappings

The FAA uses numeric codes for aircraft and engine types. We map these to human-readable strings in database.py.

Aircraft Type Codes

AIRCRAFT_TYPES = {
    "1": "Glider",
    "2": "Balloon",
    "3": "Blimp/Dirigible",
    "4": "Fixed Wing Single-Engine",
    "5": "Fixed Wing Multi-Engine",
    "6": "Rotorcraft",
    "7": "Weight-Shift-Control",
    "8": "Powered Parachute",
    "9": "Gyroplane",
    "H": "Hybrid Lift",
    "O": "Other"
}

Source: FAA aircraft type classification

Most Common:

• Type 4: Fixed Wing Single-Engine (~60% of registrations)
• Type 5: Fixed Wing Multi-Engine (~25% of registrations)
• Type 6: Rotorcraft (~10% of registrations)

Engine Type Codes

ENGINE_TYPES = {
    "0": "None",
    "1": "Reciprocating",
    "2": "Turbo-Prop",
    "3": "Turbo-Shaft",
    "4": "Turbo-Jet",
    "5": "Turbo-Fan",
    "6": "Ramjet",
    "7": "2 Cycle",
    "8": "4 Cycle",
    "9": "Unknown",
    "10": "Electric",
    "11": "Rotary"
}

Source: FAA engine type classification

Most Common:

• Type 1: Reciprocating (~65% of registrations) - piston engines
• Type 2: Turbo-Prop (~20% of registrations)
• Type 5: Turbo-Fan (~10% of registrations) - jets

Query Patterns

Single Aircraft Lookup

SELECT
    m.n_number,
    m.year_mfr,
    m.type_aircraft,
    m.type_engine,
    a.mfr,
    a.model,
    a.model as series,
    a.no_eng,
    a.no_seats
FROM master m
LEFT JOIN acftref a ON m.mfr_mdl_code = a.code
WHERE m.n_number = ?

Explanation:

• LEFT JOIN ensures we get master record even if acftref match fails
• Parameterized query prevents SQL injection
• Index on m.mfr_mdl_code makes JOIN fast
• Primary key lookup on n_number is O(log n) with B-tree index

Performance: ~0.1ms per query on typical hardware

Bulk Aircraft Lookup

Same query executed N times in Python loop (where N ≤ 50).

Why not single query?

• Variable-length IN clause is harder to optimize
• Individual lookups allow per-tail error handling
• Performance is still excellent (~5ms for 50 lookups)

Future optimization: Could use single query with IN clause for better performance at scale.

Health Check Query

SELECT COUNT(*) FROM master

Purpose: Verify database is accessible and has records

Performance: Fast count on small database, no index needed

Statistics Query

SELECT value FROM metadata WHERE key = 'last_updated'

Purpose: Show when database was last updated

Performance: Single-row primary key lookup, instant

Database Build Process

See scripts/update_faa_data.py for implementation details.

Build Steps

1. Download

   • Fetch ReleasableAircraft.zip from FAA (~30MB)
   • Verify download completed successfully
   • Extract zip to temporary directory

2. Parse MASTER.txt

   • Read file line by line (avoid loading entire file in memory)
   • Skip header row
   • Extract fields using fixed-width positions
   • Normalize n_number (strip 'N' prefix, uppercase)
   • Convert year_mfr to integer if present
   • Collect all records in list

3. Parse ACFTREF.txt

   • Read file line by line
   • Skip header row
   • Extract fields using fixed-width positions
   • Convert no_eng and no_seats to integers if present
   • Collect all records in list

4. Create Database

   • Create SQLite database file
   • Execute CREATE TABLE statements
   • Execute CREATE INDEX statement
   • Insert all master records in single batch transaction
   • Insert all acftref records in single batch transaction
   • Insert metadata record with current timestamp
   • Commit transaction
   • Close database

5. Cleanup

   • Remove temporary files
   • Remove downloaded zip

Build Time

Local Build: ~30 seconds on modern hardware

• Download: ~5 seconds (depends on internet speed)
• Parsing: ~10 seconds
• Database creation: ~10 seconds
• Indexing: ~5 seconds

CI/CD Build: ~45 seconds on GitHub Actions runners

Build Optimizations

Why batch insert?

• Individual INSERT statements: ~5 minutes for 300K records
• Batch INSERT with transaction: ~10 seconds
• 30x speedup!

Why index after insert?

• Inserting into indexed table is slower
• Creating index after bulk insert is faster
• Overall build time is reduced

Memory usage:

• Streaming file read: ~100MB peak (reading line by line)
• Batch insert: ~200MB peak (holding records in memory)
• Total: ~300MB peak during build
• Final database: ~25MB on disk

Database Maintenance

Updates

Automated: GitHub Actions nightly build workflow runs daily at 6 AM UTC

Manual: Run python scripts/update_faa_data.py data/aircraft.db

Backup

Automated: GitHub Releases stores database snapshot with each build

Download: curl -L -o aircraft.db https://github.com/ryakel/tail-lookup/releases/latest/download/aircraft.db

Verification

Check record count:

sqlite3 data/aircraft.db "SELECT COUNT(*) FROM master"

Check last updated:

sqlite3 data/aircraft.db "SELECT value FROM metadata WHERE key='last_updated'"

Check database size:

du -h data/aircraft.db

Expected values:

• Record count: ~300,000 (±10,000 as registrations change)
• Size: ~25MB (±2MB)
• Last updated: Recent date (within 24 hours if automated builds are working)

Troubleshooting

Database file not found:

• Ensure data/ directory exists
• Run build script manually
• Check GitHub Releases for pre-built database

Record count is zero:

• FAA download may have failed
• Check FAA website is accessible
• Verify parsing logic hasn't broken due to FAA format change

Database is much larger than expected:

• May have duplicate records (check parsing logic)
• May have included test data or other files

Old last_updated timestamp:

• Nightly build workflow may not be running
• Check GitHub Actions logs
• Trigger manual workflow run

Future Enhancements

Potential Schema Changes

1. Full-text search

   • Add FTS5 virtual table for manufacturer/model search
   • Enable queries like "find all Cessna 172 variants"

2. Historical tracking

   • Add history table with timestamp + record snapshot
   • Track registrations over time
   • Show when aircraft changed hands

3. Computed columns

   • Add age column (current_year - year_mfr)
   • Add display_name (with 'N' prefix)

4. Additional indexes

   • Index on manufacturer for search
   • Index on year_mfr for filtering
   • Index on state for geographic queries

Migration to PostgreSQL

When to consider:

• Need for concurrent writes
• Need for complex queries with aggregations
• Multi-container deployment with shared database
• Horizontal scaling requirements

Migration strategy:

1. Create equivalent PostgreSQL schema
2. Add connection pooling (e.g., pgbouncer)
3. Update database.py to support both SQLite and PostgreSQL
4. Add environment variable to choose backend
5. Update CI/CD to build both options

Trade-offs:

• PostgreSQL: Better concurrency, more features, requires external database
• SQLite: Simpler deployment, lower latency, sufficient for read-only workload

Current recommendation: Stick with SQLite unless specific need arises.

References

• FAA Aircraft Registry
• FAA Releasable Database
• SQLite Documentation
• SQLite Performance Tuning