How to Map FAR 117 Duty Limits to Database Schemas

Translating the textual complexity of 14 CFR Part 117 into a queryable, auditable database architecture requires moving beyond simple date-time fields and isolated scheduling blocks. Flight operations managers and compliance teams routinely encounter scheduling violations because legacy systems treat duty periods as discrete events rather than interdependent temporal windows governed by rolling cumulative thresholds. The foundation of a compliant crew scheduling engine begins with a normalized relational schema that explicitly models report times, release times, acclimatization states, and mandatory rest intervals. When you align your data architecture with the Core Architecture & Regulatory Mapping framework, you eliminate ambiguous date arithmetic and establish a deterministic validation layer that scales across multi-fleet operations without manual reconciliation.

Foundational Schema Architecture

A robust FAR 117 implementation requires strict separation between raw assignment data and computed compliance states. The primary schema should consist of four interrelated tables: crew_members, duty_periods, flight_segments, and rest_periods. The duty_periods table must store report_time_utc, release_time_utc, scheduled_start, actual_start, acclimatization_status, and duty_type. Storing all temporal values in Coordinated Universal Time prevents daylight saving time anomalies and ensures consistent cross-border scheduling logic. A dedicated time_zone_offset column on the flight_segments table preserves local reporting context for FAR 117 §117.15 acclimatization calculations.

Figure: Normalized relational schema: duty periods link crew members to flight segments and rest periods, isolating computed compliance state from raw assignment data.

Foreign keys should link duty_periods to crew_members and flight_segments, but the critical compliance logic resides in materialized views or computed columns that aggregate rolling duty windows. This structural approach is detailed further in the FAA Part 117 Rule Schema Design documentation, which emphasizes separating regulatory thresholds from operational data to enable version-controlled rule updates without schema migrations. By isolating threshold constants in a compliance_rules table, operators can adjust limits for EASA FTL or IATA guidance without altering core transactional tables.

Temporal Precision and Rolling Cumulative Limits

Mapping cumulative limits to a relational schema requires window functions rather than static counters. FAR 117 imposes strict rolling limits: under §117.23, no flightcrew member may exceed 60 flight duty period hours in any 168 consecutive hours or 190 flight duty period hours in any 672 consecutive hours, alongside flight-time caps of 100 hours in any 672 consecutive hours and 1,000 hours in any 365 consecutive days. In PostgreSQL or SQL Server, you can compute rolling totals using SUM() OVER (ORDER BY report_time_utc RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW), evaluating each window directly against its lookback interval.

SELECT 
    crew_id,
    report_time_utc,
    SUM(duty_duration_hours) OVER (
        PARTITION BY crew_id 
        ORDER BY report_time_utc 
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS rolling_7d_hours
FROM duty_periods
WHERE report_time_utc >= CURRENT_DATE - INTERVAL '365 days';

Static daily counters fail because FAR 117 evaluates duty on a rolling, continuous basis. The schema must also track acclimatization dynamically. A crew member’s allowable duty period shifts based on their local time at report and the number of time zones crossed. Storing acclimatization_status as an enumerated type (LOCAL, TRANSIENT, ACCLIMATED) allows the validation engine to apply the correct §117.15 duty table without hardcoding offsets in application logic.

Rest Periods and Overlap Detection

FAR 117 §117.25 mandates a minimum of 10 consecutive hours of rest, reducible to 9 hours under specific operational conditions, but never less than 8 hours. Your rest_periods table should store rest_start_utc, rest_end_utc, is_compensatory_rest, and rest_type. A validation trigger or scheduled job must query for overlapping duty periods or rest windows that violate the minimum threshold.

Overlap detection requires interval arithmetic. A compliant rest period must satisfy rest_end_utc - rest_start_utc >= INTERVAL '10 hours' and must not intersect with any duty_periods record for the same crew member. When compensatory rest is applied, the system must log the justification code and ensure the subsequent duty period does not exceed the reduced limits. EASA FTL and IATA standards impose similar rest requirements, but FAR 117 uniquely ties rest reductions to specific operational disruptions and requires explicit documentation. The schema should include a rest_justification_ref column to maintain audit trails for FAA inspections.

Production-Grade Python Validation Layer

Database constraints alone cannot handle the conditional logic required by modern crew scheduling. A production-grade Python validation layer bridges the gap between raw SQL results and operational decision-making. The following implementation demonstrates type-safe, timezone-aware compliance checking using modern Python standards:

import logging
from datetime import datetime, timedelta, timezone
from typing import Dict, Any

logger = logging.getLogger("crew_compliance")

class DutyComplianceValidator:
    def __init__(self, db_connection: Any):
        self.conn = db_connection
        self.utc = timezone.utc

    def validate_rest_period(self, crew_id: str, rest_start: datetime, rest_end: datetime) -> Dict[str, Any]:
        if rest_start.tzinfo != self.utc or rest_end.tzinfo != self.utc:
            raise ValueError("Temporal inputs must be timezone-aware UTC.")
        
        duration = rest_end - rest_start
        min_required = timedelta(hours=10)
        
        if duration < timedelta(hours=8):
            return {"compliant": False, "violation": "REST_BELOW_ABSOLUTE_MINIMUM", "duration_hours": duration.total_seconds() / 3600}
        elif duration < min_required:
            return {"compliant": True, "violation": "REDUCED_REST_APPLIED", "duration_hours": duration.total_seconds() / 3600}
        
        return {"compliant": True, "violation": None, "duration_hours": duration.total_seconds() / 3600}

    def check_rolling_limits(self, crew_id: str, report_time: datetime) -> Dict[str, bool]:
        # Compute the rolling windows across the crew member's full history,
        # then select the row for the report time under evaluation. Filtering to
        # a single row *before* the window would leave each SUM seeing only that
        # row, defeating the cumulative check.
        query = """
            SELECT r7, r28 FROM (
                SELECT
                    report_time_utc,
                    SUM(duty_duration_hours) OVER (
                        ORDER BY report_time_utc
                        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
                    ) AS r7,
                    SUM(duty_duration_hours) OVER (
                        ORDER BY report_time_utc
                        RANGE BETWEEN INTERVAL '28 days' PRECEDING AND CURRENT ROW
                    ) AS r28
                FROM duty_periods
                WHERE crew_id = %s
            ) windowed
            WHERE report_time_utc = %s;
        """
        with self.conn.cursor() as cur:
            cur.execute(query, (crew_id, report_time))
            result = cur.fetchone()

        if not result:
            return {"7d_limit_exceeded": False, "28d_limit_exceeded": False}

        return {
            "7d_limit_exceeded": (result[0] or 0) > 60.0,
            "28d_limit_exceeded": (result[1] or 0) > 190.0
        }

This architecture keeps every comparison in timezone-aware UTC to eliminate daylight-saving drift and relies on parameterized queries to prevent injection vulnerabilities. For comprehensive temporal arithmetic, refer to the official Python datetime documentation. The validation layer should run asynchronously against a read replica to avoid blocking scheduling transactions, logging all flags to an immutable compliance_audit_log table.

Deterministic Compliance and Audit Readiness

Deterministic validation requires that every scheduling decision produces the same compliance result regardless of execution order or system state. By materializing rolling windows and caching acclimatization states, operators eliminate race conditions during bulk schedule generation. The validation pipeline should execute in three phases: pre-scheduling feasibility checks, real-time assignment validation, and post-assignment reconciliation.

When integrating with external regulatory updates, version-control your threshold tables and deploy changes via blue-green database migrations. This ensures that historical schedules remain auditable under the rules in effect at the time of assignment. For window function optimization and query planning, consult the PostgreSQL window function documentation.

Ultimately, mapping FAR 117 to a database schema is not merely a data modeling exercise; it is an operational risk mitigation strategy. By enforcing UTC temporal standards, isolating regulatory logic, and deploying a production-grade Python validation layer, flight operations teams can scale scheduling across complex networks while maintaining strict FAA compliance. The official eCFR Part 117 text remains the authoritative source for threshold definitions, but a properly engineered schema ensures those definitions translate into actionable, automated safeguards.