Regulatory Reference Tables

Every duty-time verdict a compliance engine produces is ultimately a comparison against a number that a regulator wrote down: a maximum flight duty period, a minimum rest, a cumulative cap. Those numbers are scattered across tables, definitions, and exception clauses in 14 CFR Part 117 and EASA CS-FTL.1, and in most systems they end up transcribed — half into a lookup dictionary, half into a validation branch, a few hardcoded as magic constants deep in a scheduling loop. This reference layer does the opposite: it treats the regulatory constants themselves as a first-class, queryable, effective-dated data set that the rest of the Core Architecture & Regulatory Mapping domain reads from. When the FDP table, the rest floor, and the 672-hour cap all live in one versioned reference layer, an inspector can be handed a single query result instead of a code walkthrough, and a regulatory revision becomes a data migration rather than a code change.

The Problem: Numbers Buried in Prose

The scoped challenge here is that regulatory limits are authored for human reading, not machine lookup, and every team that builds a rule engine re-encodes them in a slightly different, undocumented way. The §117.13 Table B limit for a report at 05:30 with four segments is twelve hours — but that single fact is only correct for an acclimatised crew member, only for unaugmented operations, and only under the amendment in force on the operation date. A validation branch that hardcodes 12 loses all three qualifications the moment someone reads the code six months later.

Three properties make a reference layer worth building rather than inlining the constants. First, the same limit is consumed by many subsystems — the flight-duty-period ceiling drives the rule engine, the audit report, the roster optimiser, and the what-if planner, and they must all read one authoritative value. Second, limits are effective-dated, not eternal — an FAA amendment or an operator’s approved EASA FTL compliance deviation changes a number on a known date, and every verdict must be reproducible against the value that was in force when the schedule was published. Third, the numbers must be scannable — a compliance officer needs to see the whole Table B grid at once to sanity-check an edge case, which is impossible when the values are spread across conditional branches. This section develops the schema, the seed data, and the query patterns that make the constants behave like the reference data they are.

Schema and Data Structure Design

The reference layer separates three kinds of constant that a naive implementation collapses into one dictionary: the two-dimensional lookup grid of Table B, the scalar floors and caps, and the metadata that dates and cites each value. Keeping them in distinct effective-dated tables is what lets a single amended row be diffed and rolled back without disturbing the rest of the grid.

The core entities are the reg_ruleset — a versioned container keyed by jurisdiction and an effective_from/effective_to range that every other row references; the fdp_limit_cell — one row per Table B cell carrying the report-time band, the segment count, and the limit in minutes; the rest_minimum — the scalar rest floors with a location discriminator for home-base versus down-route; and the cumulative_cap — the rolling-window limits with an explicit window_hours and a metric discriminator distinguishing flight-time caps from duty caps. Every limit is stored in minutes as an integer, never as a float of hours, because minute precision is exact and float hours accumulate rounding error across the millions of comparisons a fleet sweep performs.

The Constants, Made Scannable

The whole point of a reference section is that the numbers are visible at a glance. The FAA §117.13 Table B grid gives the maximum flight duty period, in hours, for an acclimatised crew member in unaugmented operations, keyed on the scheduled start time and the number of flight segments.

The scalar limits are shorter but no less load-bearing. Under §117.25(e), the minimum rest before a flight duty period is 10 hours with an opportunity for 8 uninterrupted hours of sleep; §117.25(b) requires 30 consecutive hours free from all duty in any 168 consecutive hours. The §117.23 cumulative caps are 60 flight-duty-period hours in any 168 consecutive hours, 190 flight-duty-period hours in any 672 consecutive hours, 100 flight hours in any 672 consecutive hours, and 1,000 flight hours in any 365 consecutive days. The equivalent EASA ORO.FTL.210 duty caps are 60 hours in 7 days, 110 hours in 14 days, and 190 hours in 28 days, with flight time capped at 100 hours in 28 days and 1,000 hours in any 12 consecutive calendar months. The precise cell-by-cell encoding of these grids is the subject of the dedicated reference pages linked below.

Python Implementation Walkthrough

The reference values are loaded once into typed, immutable structures so a lookup can never mutate the source of truth. A pydantic model validates each cell at load time — a limit that is negative, a segment count outside 1–7, or a band that does not parse is rejected before it can ever be returned as a verdict input. The Table B grid becomes a dictionary keyed on (start_band, segments) for constant-time lookup.

from datetime import date, time
from pydantic import BaseModel, field_validator


class FdpCell(BaseModel):
    jurisdiction: str
    effective_from: date
    start_band: tuple[time, time]   # inclusive-exclusive local band
    segments: int                   # 1..7, where 7 means "7 or more"
    limit_minutes: int

    @field_validator("segments")
    @classmethod
    def segments_in_range(cls, v: int) -> int:
        if not 1 <= v <= 7:
            raise ValueError("segments must be 1..7 (7 == 7 or more)")
        return v

    @field_validator("limit_minutes")
    @classmethod
    def positive_limit(cls, v: int) -> int:
        if v <= 0:
            raise ValueError("limit_minutes must be positive")
        return v

The lookup itself resolves the correct band for a report time, then clamps the segment count to the table’s ceiling of seven. Clamping rather than raising is deliberate: a nine-segment day is legal input and simply reads the 7+ row, so the resolver must never treat an out-of-grid segment count as an error.

def resolve_fdp_minutes(cells: dict, report_local: time, segments: int) -> int:
    """Return the Table B FDP ceiling in minutes for a report time and leg count."""
    seg_key = min(segments, 7)
    for (lo, hi), by_seg in cells.items():
        # Bands are contiguous and non-overlapping; hi is exclusive.
        if lo <= report_local < hi or (lo > hi and (report_local >= lo or report_local < hi)):
            return by_seg[seg_key]
    raise LookupError(f"no Table B band covers {report_local!r}")

The lo > hi clause handles the single wrap-around band that crosses midnight, so a 23:30 report resolves against the 2300–2359 row rather than falling through the grid. Anchoring the lookup on the crew member’s acclimatised local time — not the departure station’s wall clock — is the same discipline the FAA Part 117 rule schema design applies throughout.

Rolling Windows and Temporal Aggregation

The cumulative caps are the reference values that most often get inlined and most often go wrong, because they are defined over any consecutive window rather than calendar periods. Storing the window length and cap as data lets one rolling query serve every cap by parameterising the frame. In PostgreSQL, a window function with an interval-bounded RANGE frame sums duty minutes over each cap’s trailing window in a single pass.

SELECT
    d.crew_id,
    d.duty_id,
    c.metric,
    c.window_hours,
    SUM(d.duty_minutes) OVER (
        PARTITION BY d.crew_id
        ORDER BY d.report_utc
        RANGE BETWEEN (c.window_hours * INTERVAL '1 hour') PRECEDING AND CURRENT ROW
    ) AS window_minutes,
    c.cap_minutes
FROM duty_period AS d
CROSS JOIN cumulative_cap AS c
WHERE c.ruleset_id = current_ruleset(d.report_utc::date, 'FAA')
  AND c.metric = 'duty';

Rows where window_minutes exceeds cap_minutes are the violations. Because the cap and its window both come from the reference table rather than the query text, adding the EASA 14-day cap is an INSERT into cumulative_cap, not a new query. The critical correctness detail — shared with every rolling calculation in the duty time validation rule engines — is that the frame operates on the UTC instant, so a date-line crossing never reorders the window.

Integration Points

The reference layer sits beneath every other subsystem rather than beside them. The duty time validation rule engines call resolve_fdp_minutes and the cumulative-cap query instead of holding their own copies of the numbers, so a table amendment propagates to the engine, the rest period compliance checks, and the fatigue layer at once. The system security and access boundaries treat the reference tables as append-only, audited data: a change to a limit is itself a hash-chained audit event carrying the amendment citation, so an inspector can reconstruct not just the verdict but the exact constant that produced it. Upstream, the effective-dated rows are seeded and revised through the same validation discipline that flight data ingestion applies to operational feeds — a limit with no citation or no effective date is rejected at load, exactly as a duty event with no time zone is.

Testing and Edge Cases

Reference data fails in ways operational data does not, so the test suite targets the seams between rows rather than the values themselves.

• Band boundaries. A report at exactly 05:00 must resolve to the 0500–0559 row, not the 0400–0459 row; tests pin both edges of every band because an off-by-one on an inclusive/exclusive boundary silently returns the wrong limit for the busiest report times.
• Segment clamping. A twelve-segment day and a seven-segment day must return an identical limit; the clamp to seven is tested explicitly so a future refactor cannot turn an over-grid count into a KeyError.
• Effective-date selection. Two rulesets whose date ranges abut must never both match a single operation date; a property-based test asserts that for any date and jurisdiction exactly one ruleset resolves, catching an overlapping effective_to/effective_from seed error.
• Minute integrality. Every stored limit must be an exact integer minute; a 12.5-hour cell is 750 minutes, and a test rejects any seed row that would have required a fractional minute, because that is the signature of a float-hours transcription bug.
• Jurisdiction isolation. An FAA lookup must never return an EASA row even when the numbers coincide, so a test seeds a colliding value under both jurisdictions and confirms the resolver honours the jurisdiction key.

Explore This Topic in Depth

The general schema above becomes concrete seed data and query patterns in the reference pages beneath this topic:

• FAA Part 117 Table B FDP Limits as Queryable Data — the full §117.13 grid encoded as effective-dated rows, with the band-resolution and segment-clamping logic and the assertions that pin every cell.
• Cumulative Duty and Flight-Time Caps Reference — the §117.23 and ORO.FTL.210 rolling-window caps as parameterised data, with the SQL and Polars aggregations that evaluate them side by side.

Related

• FAA Part 117 Rule Schema Design — how the Table B grid and cumulative windows become queryable, versioned structures.
• EASA FTL Compliance Frameworks — the European CS-FTL.1 tables and ORO.FTL caps this layer also stores.
• Duty Time Validation & Rule Engines — the downstream engines that read these constants instead of hardcoding them.
• System Security & Access Boundaries — the audit trail that records every reference-value change.

Back to Core Architecture & Regulatory Mapping.