FAA Part 117 Rule Schema Design

Designing a compliant schema for FAA Part 117 requires more than translating regulatory text into database columns. It demands a deterministic evaluation pipeline capable of handling dynamic schedule mutations, multi-segment duty periods, and cumulative fatigue limits without introducing latency into crew pairing optimization. Within the broader Core Architecture & Regulatory Mapping framework, Part 117 schema design serves as the computational backbone for flight operations managers, crew schedulers, and compliance teams who must guarantee that every published roster survives regulatory audit while remaining operationally viable. The architecture must strictly separate static regulatory thresholds from dynamic operational variables, enabling Python automation builders to construct rule engines that evaluate constraints in real time rather than relying on post-hoc validation.

Scoping the Part 117 Schema Problem

The specific challenge this topic addresses is narrow but unforgiving: 14 CFR Part 117 expresses its limits as rolling functions of time, not as daily counters that reset at midnight. A flight duty period (FDP) that is legal in isolation can become illegal the moment a prior segment is retimed, because the same duty hour is simultaneously counted against a 168-hour window and a 672-hour window. A schema that stores only per-day totals therefore cannot answer the question the regulator actually asks — “was this crew member legal at the instant of report?” — and any engine built on it will generate both false clears and false violations.

Three properties make this hard to model. First, the limits are interval-scoped: §117.11 flight-time caps and §117.23 cumulative caps are evaluated over sliding windows anchored to the moment of evaluation, so the schema must preserve segment-level granularity rather than pre-aggregating. Second, several thresholds are state-dependent: the maximum FDP under §117.13 varies with the crew member’s acclimation state and scheduled start time, so the schema must carry acclimation as first-class state rather than deriving it on the fly. Third, evaluation is event-driven: a single pairing edit can invalidate windows for every crew member it touches, so the data model has to support incremental, targeted recomputation instead of nightly batch validation. Getting these three properties right is what separates a schema that merely stores schedules from one that proves them compliant.

A production-grade Part 117 rule engine operates as a directed acyclic graph (DAG) of constraint evaluators. Each node represents a discrete regulatory boundary — FDP limits, minimum rest requirements, cumulative duty caps, and split-duty provisions — while edges define dependency chains. The schema must capture temporal state transitions with millisecond precision, storing UTC-normalized timestamps alongside localized reporting times to prevent daylight-saving or timezone-boundary violations. When a scheduler modifies a pairing, the engine triggers a cascading evaluation that recalculates duty windows, validates minimum rest against prior segments, and flags cumulative-limit breaches before they propagate to downstream optimization routines. This event-driven approach eliminates the need for full-schedule revalidation and aligns with modern microservice deployment patterns.

Schema and Data Structure Design

The core entities that satisfy Part 117 are deliberately small in number but rich in temporal metadata. A crew_member owns many duty_segment rows; each duty_segment contains one or more flight_segment rows and is bounded on either side by a rest_period. Every timestamp is stored twice — once as a UTC instant used for arithmetic and once as a local wall-clock time used only for §117.13 start-time table lookups — so that daylight-saving transitions never enter a rolling-sum calculation. Acclimation state lives on the duty_segment because it is an input to the FDP-limit table, not a property of the crew member in the abstract.

The load-bearing decision is that duty_segment stores raw report_utc and release_utc and treats every cumulative total as a derived value computed over the segment history, never a stored counter that must be incremented and kept in sync. Denormalized counters drift the instant a segment is retimed; derived windows cannot. The DDL below models the four entities and defers all aggregation to query time.

CREATE TYPE acclimation_state AS ENUM ('acclimated', 'unknown', 'not_acclimated');

CREATE TABLE duty_segment (
    duty_id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    crew_id           text NOT NULL REFERENCES crew_member (crew_id),
    report_utc        timestamptz NOT NULL,
    release_utc       timestamptz NOT NULL,
    report_local      timestamp   NOT NULL,          -- wall clock for Table B lookup
    acclimation       acclimation_state NOT NULL,
    flight_segments   smallint NOT NULL CHECK (flight_segments BETWEEN 1 AND 10),
    CHECK (release_utc > report_utc)
);

CREATE INDEX duty_segment_crew_time
    ON duty_segment (crew_id, release_utc);

The composite (crew_id, release_utc) index is what makes the rolling-window queries later in this page cheap: every cumulative limit is evaluated per crew member, ordered by the end of the duty, so the planner can walk the index in range order without a sort.

Regulatory Mapping: The FAR Sections That Drive the Schema

Each schema decision traces back to a specific section of Part 117, and the citations must match the current eCFR text exactly, because these constants change through advisory circulars and rule amendments.

• §117.13 — Flight duty period, unaugmented operations. The maximum FDP is read from Table B as a function of the acclimated start time and the number of flight segments (ranging from 9 to 14 hours). This is why acclimation, report_local, and flight_segments are stored on the duty segment rather than derived: they are all inputs to the same lookup.
• §117.15 — Flight duty period, split duty. A qualifying rest opportunity inside the FDP can extend the permissible duty window. The schema models this as an optional in-duty rest_period whose duration and location determine the credited extension.
• §117.23 — Cumulative limitations. No more than 60 FDP hours in any 168 consecutive hours and 190 FDP hours in any 672 consecutive hours; no more than 100 flight hours in any 672 consecutive hours and 1,000 flight hours in any 365 consecutive days. These are the four rolling windows the schema must be able to reconstruct at any instant.
• §117.25 — Rest period. At least 30 consecutive hours free from all duty in any 168 consecutive hours, and a minimum 10-hour rest with an 8-hour uninterrupted sleep opportunity immediately before an FDP. Both are modeled as gaps between consecutive duty_segment rows.
• §117.11 — Flight time limitation. 100 flight hours in any 672 consecutive hours and 1,000 in any 365 consecutive days, evaluated against the summed flight_segment block times rather than FDP hours.

Storing the regulatory constants themselves in a version-controlled configuration table — rather than hardcoding 60, 190, or 100 into evaluator code — lets the engine pin a schedule to the rule version in force on its operating date, which is essential when an amendment lands mid-bid-period. Carriers running mixed fleets or dual-jurisdiction networks must additionally reconcile these tables against EASA FTL compliance frameworks, whose FDP tables, acclimation categories, and split-rest provisions differ enough that the two rule sets have to live in separate, jurisdiction-tagged branches of the same evaluator.

Python Implementation Walkthrough

Production-grade Part 117 evaluation relies on strongly typed data models so that malformed schedule payloads are rejected at the boundary rather than deep inside the DAG. Using pydantic to validate incoming segments guarantees that release_utc follows report_utc and that flight_segments stays within the table’s domain before any arithmetic runs.

from datetime import datetime
from enum import Enum

from pydantic import BaseModel, Field, ValidationInfo, field_validator


class Acclimation(str, Enum):
    ACCLIMATED = "acclimated"
    UNKNOWN = "unknown"
    NOT_ACCLIMATED = "not_acclimated"


class DutySegment(BaseModel):
    crew_id: str
    report_utc: datetime
    release_utc: datetime
    report_local: datetime
    acclimation: Acclimation
    flight_segments: int = Field(ge=1, le=10)

    @field_validator("release_utc")
    @classmethod
    def release_after_report(cls, value: datetime, info: ValidationInfo) -> datetime:
        report = info.data.get("report_utc")
        if report is not None and value <= report:
            raise ValueError("release_utc must be after report_utc")
        return value

    @property
    def fdp_hours(self) -> float:
        return (self.release_utc - self.report_utc).total_seconds() / 3600.0

The §117.13 limit itself is a pure function of the two stored inputs, which keeps it testable in isolation. The lookup below models the shape of Table B — reporting earlier in the day and flying more segments both compress the permissible FDP.

# Simplified §117.13 Table B: max FDP hours by acclimated start hour and segment count.
_TABLE_B = {
    "early": [13.0, 13.0, 12.0, 12.0, 11.5, 11.0, 10.5],  # 0500-0559 report
    "day": [14.0, 14.0, 13.0, 13.0, 12.5, 12.0, 11.5],    # 0700-1159 report
    "late": [12.0, 12.0, 11.0, 11.0, 10.5, 10.0, 9.0],    # 1700-2159 report
}


def max_fdp_hours(report_local: datetime, flight_segments: int) -> float:
    hour = report_local.hour
    if 5 <= hour < 7:
        band = "early"
    elif 7 <= hour < 17:
        band = "day"
    else:
        band = "late"
    index = min(flight_segments, len(_TABLE_B[band])) - 1
    return _TABLE_B[band][index]


def fdp_violation(segment: DutySegment) -> bool:
    return segment.fdp_hours > max_fdp_hours(segment.report_local, segment.flight_segments)

Because each evaluator is side-effect free, the DAG traversal can be run asynchronously with asyncio, fanning out independent boundary checks in parallel and joining their verdicts, which keeps latency flat as roster size grows. The full lookup tables and the DDL that backs them are covered step by step in the implementation guide linked at the end of this page.

Rolling-Window and Temporal Aggregation Patterns

The §117.23 and §117.11 caps are where naive schemas fail, because they require summing duty and flight hours over windows anchored to each segment’s release instant. PostgreSQL expresses this directly with a RANGE window frame measured in intervals, so the 60-hour-in-168 cumulative FDP check becomes a single pass over the (crew_id, release_utc) index:

SELECT
    crew_id,
    release_utc,
    fdp_hours,
    SUM(fdp_hours) OVER w AS fdp_rolling_168h,
    SUM(fdp_hours) OVER w_672 AS fdp_rolling_672h
FROM (
    SELECT
        crew_id,
        release_utc,
        EXTRACT(EPOCH FROM (release_utc - report_utc)) / 3600.0 AS fdp_hours
    FROM duty_segment
) s
WINDOW
    w AS (
        PARTITION BY crew_id ORDER BY release_utc
        RANGE BETWEEN INTERVAL '168 hours' PRECEDING AND CURRENT ROW
    ),
    w_672 AS (
        PARTITION BY crew_id ORDER BY release_utc
        RANGE BETWEEN INTERVAL '672 hours' PRECEDING AND CURRENT ROW
    );

For pre-flight validation inside the pairing loop — where round-tripping to the database is too slow — the same logic runs in-memory with a time-based rolling sum. Polars evaluates the window per crew member without materializing intermediate frames:

import polars as pl


def rolling_fdp(frame: pl.DataFrame) -> pl.DataFrame:
    """Attach 168h and 672h rolling FDP totals; frame needs crew_id,
    release_utc (datetime), and fdp_hours (float)."""
    return (
        frame.sort("release_utc")
        .with_columns(
            pl.col("fdp_hours")
            .rolling_sum_by("release_utc", window_size="168h")
            .over("crew_id")
            .alias("fdp_168h"),
            pl.col("fdp_hours")
            .rolling_sum_by("release_utc", window_size="672h")
            .over("crew_id")
            .alias("fdp_672h"),
        )
        .with_columns(
            (pl.col("fdp_168h") > 60.0).alias("breach_168h"),
            (pl.col("fdp_672h") > 190.0).alias("breach_672h"),
        )
    )

Keeping the SQL and in-memory implementations behaviorally identical — same window semantics, same inclusive boundaries — is what lets the fast pre-flight check and the authoritative database check agree, so a pairing that clears optimization also clears the audit query.

Integration Points Across the Scheduling Stack

This schema is one node in a larger evaluation network and only produces correct verdicts when its neighbors feed it clean, consistently-typed events. Upstream, it depends on flight data ingestion to stream pairing mutations as idempotent, versioned upserts; without idempotency, a replayed message double-counts a duty segment and manufactures a phantom §117.23 breach. Message-queue delivery lets the Part 117 schema receive atomic updates without race conditions, and versioned snapshots let a mis-timed segment be rolled back to the last compliant state.

Semantically, every event must be classified against the shared crew duty time taxonomy mapping, which fixes the vocabulary distinguishing report times, block-out events, actual off-duty intervals, and reserve-activation windows. When ingestion and evaluation disagree on whether a period is duty or rest, the rule engine misclassifies transitional periods and emits false violations, so the taxonomy is the contract that keeps the two subsystems aligned. Downstream, the computed rolling windows feed the Duty Time Validation & Rule Engines that turn raw breach flags into scored, actionable alerts, and every verdict is written to the append-only audit log governed by System Security & Access Boundaries. Those boundaries enforce that compliance auditors hold read-only access to immutable evaluation logs while dispatchers write only through approval workflows, with hash-chained trails and row-level security preventing unauthorized schedule overrides. If the primary engine degrades, a conservative fallback check must still block any illegal pairing from reaching publication.

Testing and Edge Cases

The boundary conditions unique to Part 117 are temporal, so property-based testing with hypothesis earns its keep by generating the odd timestamps that break hand-written fixtures. The invariant worth asserting first is that duty duration is always positive and that a valid segment never reports negative FDP hours, regardless of the instant it starts.

from datetime import timedelta

from hypothesis import given
from hypothesis import strategies as st


@given(
    report=st.datetimes(),
    duration=st.timedeltas(min_value=timedelta(hours=1), max_value=timedelta(hours=16)),
)
def test_fdp_hours_always_positive(report, duration):
    segment = DutySegment(
        crew_id="C1",
        report_utc=report,
        release_utc=report + duration,
        report_local=report,
        acclimation=Acclimation.ACCLIMATED,
        flight_segments=1,
    )
    assert segment.fdp_hours > 0

Beyond the happy path, four edge cases dominate real-world defects. Midnight and month crossings must not reset a rolling window — the 672-hour sum has to span calendar boundaries, which the RANGE ... INTERVAL frame handles but a GROUP BY date never will. Daylight-saving transitions must touch only report_local (the Table B input) and never the UTC instants used for arithmetic, or a spring-forward hour silently inflates an FDP. Split-duty rest under §117.15 must be tested for the exact credited-extension boundary, where one minute of qualifying rest flips legality. And acclimation-state conflicts — where an ingestion feed reports acclimated but the time-zone history implies not_acclimated — must resolve to the more conservative state so the engine never over-credits an FDP. Each of these belongs in a regression suite that replays against the version-controlled §117 constants in force on the schedule’s operating date.

Implementation Guides

This topic is supported by a focused, step-by-step guide that turns the schema described above into working DDL and validation code:

• How to Map FAR 117 Duty Limits to Database Schemas — a numbered walkthrough that builds the crew_members, duty_periods, flight_segments, and rest_periods tables, wires in the rolling-window queries, and adds verification assertions plus troubleshooting for the timezone and split-duty edge cases named above.

A robust FAA Part 117 rule schema transforms regulatory compliance from a retrospective audit burden into a proactive, computationally verifiable asset. By decoupling static limits from dynamic operational variables, enforcing strict temporal normalization, and aligning with a standardized duty-time taxonomy, aviation organizations can deploy scheduling systems that scale with network complexity while maintaining zero-tolerance compliance. The integration of event-driven evaluation, secure access boundaries, and deterministic conservative fallback logic ensures that crew pairings remain both legally defensible and operationally resilient in an increasingly dynamic flight environment.