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 exact task this guide solves is narrow: given a stream of crew assignments, model report times, release times, acclimation states, and rest intervals so that a single query can answer “was this crew member legal at the instant of report?” — the question a regulator actually asks. This page applies the FAA Part 117 rule schema design patterns from the broader Core Architecture & Regulatory Mapping domain, and assumes duty records already arrive normalised through your flight data ingestion pipeline. When the definitions of report, block-out, and off-duty are anchored to a shared crew duty time taxonomy, the schema below becomes a deterministic validation layer rather than a source of ambiguous date arithmetic.

Prerequisites

Before building the schema, confirm the following are in place:

Step 1 — Model the core tables

A robust Part 117 implementation requires strict separation between raw assignment data and computed compliance state. Create four interrelated tables: crew_members, duty_periods, flight_segments, and rest_periods. Store every temporal value in Coordinated Universal Time to prevent daylight-saving anomalies, and keep a time_zone_offset on flight_segments so local reporting context survives for acclimation calculations.

CREATE TYPE acclimatization_status AS ENUM ('LOCAL', 'TRANSIENT', 'ACCLIMATED');

CREATE TABLE crew_members (
    crew_id      text PRIMARY KEY,
    home_base    text NOT NULL
);

CREATE TABLE duty_periods (
    duty_id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    crew_id            text NOT NULL REFERENCES crew_members (crew_id),
    report_time_utc    timestamptz NOT NULL,
    release_time_utc   timestamptz NOT NULL,
    scheduled_start    timestamptz NOT NULL,
    actual_start       timestamptz,
    acclim_status      acclimatization_status NOT NULL DEFAULT 'ACCLIMATED',
    duty_type          text NOT NULL,
    duty_duration_hours numeric(5, 2)
        GENERATED ALWAYS AS
        (EXTRACT(EPOCH FROM release_time_utc - report_time_utc) / 3600.0) STORED,
    CHECK (release_time_utc > report_time_utc)
);

CREATE TABLE flight_segments (
    segment_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    duty_id          bigint NOT NULL REFERENCES duty_periods (duty_id),
    time_zone_offset integer NOT NULL,
    flight_hours     numeric(4, 2) NOT NULL
);

CREATE TABLE rest_periods (
    rest_id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    crew_id             text NOT NULL REFERENCES crew_members (crew_id),
    rest_start_utc      timestamptz NOT NULL,
    rest_end_utc        timestamptz NOT NULL,
    sleep_opportunity_h numeric(4, 2) NOT NULL,
    rest_type           text NOT NULL,
    rest_justification_ref text
);

The duty_duration_hours generated column removes an entire class of drift bugs — the stored value can never disagree with the timestamps it is derived from. The entity relationships are shown below.

Part 117 core-table entity-relationship diagram A crew_member owns many duty_periods rows and many rest_periods rows. Each duty_periods row stores raw assignment columns — duty_id, crew_id, report_time_utc, release_time_utc, scheduled_start, actual_start, acclim_status and duty_type — plus one generated column, duty_duration_hours, computed from the report and release instants and stored so it can never drift. Each duty_periods row contains many flight_segments rows carrying time_zone_offset and flight_hours. The rest_periods table links back to crew_member and records sleep_opportunity_h, rest_type and a justification reference. owns 1 N rest log 1 N contains 1 N crew_members crew_idPK home_base rest_periods rest_idPK crew_idFK rest_start_utc rest_end_utc sleep_opportunity_h rest_type rest_justification_ref duty_periods STORED — RAW ASSIGNMENT duty_idPK crew_idFK report_time_utc release_time_utc scheduled_start actual_start acclim_status duty_type GENERATED ALWAYS (STORED) duty_duration_hours derived from report/release — cannot drift flight_segments segment_idPK duty_idFK time_zone_offset flight_hours
Normalized relational schema centred on duty_periods: a crew member owns duty periods and rest periods, each duty period contains flight segments, and the single generated column duty_duration_hours isolates computed state from the raw report and release instants it is derived from.

Verify: \d duty_periods should list duty_duration_hours as a generated always column and show the CHECK constraint on the release/report ordering.

Step 2 — Isolate regulatory thresholds in a versioned table

Hardcoding limits into application logic makes every rule change a code deployment. Instead, store thresholds as data in a compliance_rules table keyed by an effective date, so historical schedules remain auditable under the rules in force at the time of assignment and future updates never require a schema migration.

CREATE TABLE compliance_rules (
    rule_key       text NOT NULL,
    limit_value    numeric NOT NULL,
    window_hours   integer NOT NULL,
    citation       text NOT NULL,
    effective_from date NOT NULL,
    PRIMARY KEY (rule_key, effective_from)
);

INSERT INTO compliance_rules (rule_key, limit_value, window_hours, citation, effective_from) VALUES
  ('fdp_hours_168h',   60,   168, '14 CFR 117.23(c)', '2014-01-04'),
  ('fdp_hours_672h',   190,  672, '14 CFR 117.23(c)', '2014-01-04'),
  ('flight_hours_672h', 100, 672, '14 CFR 117.11(a)', '2014-01-04'),
  ('flight_hours_365d', 1000, 8760, '14 CFR 117.11(b)', '2014-01-04'),
  ('min_rest_hours',   10,   0,   '14 CFR 117.25(e)', '2014-01-04'),
  ('min_sleep_opp_h',  8,    0,   '14 CFR 117.25(e)', '2014-01-04'),
  ('free_hours_168h',  30,   168, '14 CFR 117.25(b)', '2014-01-04');

Because the same constants drive both US and European engines, this table is where you diverge for EASA FTL compliance without touching any transactional table.

Verify: SELECT rule_key, limit_value, citation FROM compliance_rules ORDER BY rule_key; returns seven rows, each carrying an accurate eCFR citation.

Step 3 — Compute rolling cumulative limits with window functions

Part 117 expresses its cumulative caps as rolling functions of time, not daily counters that reset at midnight. A flight duty period that is legal in isolation can become illegal the moment a prior segment is retimed, because the same duty hour is counted against both a 168-hour and a 672-hour window. Static counters therefore cannot model it — you need window functions evaluated against a 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_fdp_168h,
    SUM(duty_duration_hours) OVER (
        PARTITION BY crew_id
        ORDER BY report_time_utc
        RANGE BETWEEN INTERVAL '28 days' PRECEDING AND CURRENT ROW
    ) AS rolling_fdp_672h
FROM duty_periods
WHERE report_time_utc >= CURRENT_TIMESTAMP - INTERVAL '365 days';

Each row now reports the cumulative flight-duty-period hours in the 168-hour and 672-hour windows anchored to its own report time, ready to compare against the fdp_hours_168h (60) and fdp_hours_672h (190) limits from §117.23©.

Verify: For a crew member with a known dense block of duties, rolling_fdp_168h should rise monotonically inside the window and fall as older duties age past seven days — never reset to zero at a calendar boundary.

Step 4 — Carry acclimation as first-class state

The maximum flight duty period under §117.13 varies with the crew member’s acclimated time of report and the number of flight segments — it is state-dependent, so the schema must store acclimation rather than derive it at query time. Under §117.3, a crew member is acclimated after 72 hours in a theater or after at least 36 consecutive hours free from duty; until then they are treated as being on home-base time. The acclim_status enum (LOCAL, TRANSIENT, ACCLIMATED) from Step 1 lets the validation engine select the correct §117.13 Table B row without hardcoding offsets in application logic. Recompute and persist this status whenever a positioning or rest event changes the theater the crew member is in.

Step 5 — Build the timezone-aware Python validation layer

Database constraints alone cannot express the conditional logic Part 117 requires. A production-grade Python layer bridges raw SQL results and operational decisions, keeping every comparison in timezone-aware UTC to eliminate daylight-saving drift. The rest check enforces the two distinct §117.25 requirements: the 10 consecutive hours immediately before an FDP with an 8-hour sleep opportunity (§117.25(e)), and the 30 consecutive hours free from duty in any 168-hour window (§117.25(b)).

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

logger = logging.getLogger("crew_compliance")

# 14 CFR 117.25 rest thresholds
REST_MIN = timedelta(hours=10)          # 117.25(e): consecutive rest before an FDP
SLEEP_OPPORTUNITY_MIN = timedelta(hours=8)  # 117.25(e): uninterrupted sleep opportunity


class DutyComplianceValidator:
    def __init__(self, db_connection: Any) -> None:
        self.conn = db_connection

    def validate_rest_period(
        self,
        crew_id: str,
        rest_start: datetime,
        rest_end: datetime,
        sleep_opportunity: timedelta,
    ) -> dict[str, Any]:
        """Validate a pre-FDP rest period against 14 CFR 117.25(e)."""
        if rest_start.tzinfo is None or rest_end.tzinfo is None:
            raise ValueError("Temporal inputs must be timezone-aware UTC.")

        duration = rest_end - rest_start
        duration_hours = round(duration.total_seconds() / 3600, 2)

        if duration < REST_MIN:
            return {
                "compliant": False,
                "violation": "REST_BELOW_10_HOURS",
                "duration_hours": duration_hours,
                "citation": "14 CFR 117.25(e)",
            }
        if sleep_opportunity < SLEEP_OPPORTUNITY_MIN:
            return {
                "compliant": False,
                "violation": "SLEEP_OPPORTUNITY_BELOW_8_HOURS",
                "duration_hours": duration_hours,
                "citation": "14 CFR 117.25(e)",
            }
        return {
            "compliant": True,
            "violation": None,
            "duration_hours": duration_hours,
            "citation": "14 CFR 117.25(e)",
        }

    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 fdp7, fdp28 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 fdp7,
                    SUM(duty_duration_hours) OVER (
                        ORDER BY report_time_utc
                        RANGE BETWEEN INTERVAL '28 days' PRECEDING AND CURRENT ROW
                    ) AS fdp28
                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 {"fdp_168h_exceeded": False, "fdp_672h_exceeded": False}

        return {
            "fdp_168h_exceeded": (result[0] or 0) > 60.0,   # 117.23(c)(1)
            "fdp_672h_exceeded": (result[1] or 0) > 190.0,  # 117.23(c)(2)
        }

This layer relies on parameterised queries to prevent injection and should run asynchronously against a read replica so it never blocks scheduling transactions. Flag every result to an immutable compliance_audit_log table. For the temporal arithmetic behind these comparisons, the Python datetime documentation is authoritative.

Three-phase duty-compliance validation pipeline Phase one, pre-scheduling feasibility, runs asynchronously against the read replica while candidate pairings are built: it checks rolling FDP headroom and coarse rest gaps. Phase two, real-time assignment validation, runs synchronously against the transactional primary at the moment of the write and blocks the commit: it enforces the section 117.25(e) ten-hour rest, rejects overlapping duties and applies the hard cumulative caps. The scheduling transaction commits between phase two and phase three. Phase three, post-assignment reconciliation, runs asynchronously against the read replica after commit: it recomputes the 168-hour and 672-hour rolling sums and writes an immutable compliance_audit_log row. transaction commits Pre-scheduling feasibility while pairings are built rolling FDP headroom coarse rest-gap scan async · replica Real-time validation at the moment of the write §117.25(e) rest ≥ 10 h no overlapping duty hard cumulative cap sync · primary · blocks commit Post-assignment reconciliation after commit recompute 168 h / 672 h sums write compliance_audit_log async · replica synchronous, transactional primary — blocks the scheduling write asynchronous, read replica — never blocks a scheduling transaction
The validation layer as a three-phase pipeline: feasibility and reconciliation run asynchronously against a read replica, while only the assignment-time checks run synchronously against the transactional primary and block the commit — so the expensive rolling recompute never stalls a scheduling write.

Verification queries and assertions

After loading a test roster, confirm the schema behaves deterministically:

# A 9h59m rest must fail; a 10h rest with an 8h sleep opportunity must pass.
v = DutyComplianceValidator(conn)
short = v.validate_rest_period(
    "CM-1001",
    datetime(2026, 3, 1, 22, 0, tzinfo=timezone.utc),
    datetime(2026, 3, 2, 7, 59, tzinfo=timezone.utc),
    sleep_opportunity=timedelta(hours=8),
)
assert short["compliant"] is False and short["violation"] == "REST_BELOW_10_HOURS"
-- No duty period may overlap another for the same crew member.
SELECT a.duty_id, b.duty_id
FROM duty_periods a
JOIN duty_periods b
  ON a.crew_id = b.crew_id
 AND a.duty_id < b.duty_id
 AND tstzrange(a.report_time_utc, a.release_time_utc)
     && tstzrange(b.report_time_utc, b.release_time_utc);
-- Expected: zero rows.

Failure modes and troubleshooting

Frequently Asked Questions

How do I handle a rest period that spans a daylight-saving transition?

Compute the rest duration entirely in UTC — the timestamptz values already encode the absolute instant, so subtraction yields the true elapsed hours regardless of any local clock change. Only convert to the crew member’s local zone for display and audit context. The 10-hour minimum in §117.25(e) is measured as consecutive real time, not wall-clock time, so a rest that “loses” an hour to a spring-forward transition still needs a full 10 real hours.

Does the 60-hour FDP cap reset at the start of a calendar week?

No. §117.23©(1) is a rolling 168 consecutive hours anchored to the moment of evaluation, not a Monday-to-Sunday bucket. That is exactly why the schema uses a RANGE ... INTERVAL '7 days' PRECEDING window frame rather than grouping by ISO week — the limit must be recomputed against the trailing 168 hours every time a duty is added or retimed.

Should acclimation state be stored or computed on the fly?

Store it. Acclimation under §117.3 depends on elapsed time in a theater and prior duty-free periods, which are historical facts that a point-in-time query cannot cheaply reconstruct for every candidate assignment. Persisting acclim_status and recomputing it on positioning and rest events keeps FDP evaluation fast and makes the input to each §117.13 lookup auditable.

How do I keep historical schedules auditable when the thresholds change?

Never mutate a threshold in place. The compliance_rules table is keyed by effective_from, so evaluation joins the rule row in force at the assignment’s report time. Deploy changes via blue-green migrations and keep prior rows intact; an inspector can then reproduce the exact verdict a schedule received under the rules that applied when it was published.

What about split duty under §117.15?

Split duty lets a portion of the FDP be offset by a rest opportunity during the duty period, extending the permissible FDP under defined conditions. Model it as a rest_periods row with a rest_type of SPLIT_DUTY linked to the enclosing duty, and adjust the effective FDP limit only when the break meets the §117.15 minimum and occurs in suitable accommodation. Treat it as a distinct rule path, not a reduction of the §117.25(e) pre-FDP rest.

Back to FAA Part 117 Rule Schema Design.