Cumulative Duty and Flight-Time Caps Reference

The exact task this guide solves is encoding every rolling cumulative cap — FAA and EASA, duty and flight time — as parameterised rows and evaluating them with one query instead of a bespoke check per limit. Cumulative caps are defined over any consecutive window, not calendar periods, and each regime meters a different quantity over a different interval, so the cap that binds a roster is rarely obvious. This page tabulates all of them, seeds them as data, and gives the SQL window function and the Polars rolling aggregation that evaluate them in a single pass. It is the numeric reference behind the regulatory reference tables section and supplies the caps the duty time validation rule engines enforce.

Prerequisites

• PostgreSQL 14+ for interval-bounded RANGE frames, or polars for the in-process path.
• A duty history with UTC report instants and per-duty flight and duty minutes.
• The cumulative_cap table from the regulatory reference tables schema.
• Regulatory versions pinned — the eCFR §117.23 amendment and the ORO.FTL.210 revision.

Every cumulative cap in one table

Each row is a rolling window: a metric, a window length, and a cap. The FAA meters over rolling hours; EASA meters over rolling days.

The 672-hour FAA window and the 28-day EASA window are both roughly four weeks, but they are not interchangeable: the FAA counts FDP or flight-time hours over an exact 672-hour clock, while EASA counts duty or flight over calendar-aligned day windows. The 14-day EASA window has no FAA counterpart at all.

Step 1 — Seed the caps as parameterised rows

Storing the window length and cap as data means one evaluation loop serves every limit; adding a cap is an INSERT, never a new branch.

CUMULATIVE_CAPS = [
    {"regime": "FAA",  "metric": "fdp",    "window_hours": 168,      "cap_minutes": 60 * 60},
    {"regime": "FAA",  "metric": "fdp",    "window_hours": 672,      "cap_minutes": 190 * 60},
    {"regime": "FAA",  "metric": "flight", "window_hours": 672,      "cap_minutes": 100 * 60},
    {"regime": "FAA",  "metric": "flight", "window_hours": 365 * 24, "cap_minutes": 1000 * 60},
    {"regime": "EASA", "metric": "duty",   "window_hours": 7 * 24,   "cap_minutes": 60 * 60},
    {"regime": "EASA", "metric": "duty",   "window_hours": 14 * 24,  "cap_minutes": 110 * 60},
    {"regime": "EASA", "metric": "duty",   "window_hours": 28 * 24,  "cap_minutes": 190 * 60},
    {"regime": "EASA", "metric": "flight", "window_hours": 28 * 24,  "cap_minutes": 100 * 60},
]

Verify: the list holds eight rolling caps (the 12-calendar-month EASA flight cap is evaluated separately because a calendar month is not a fixed number of hours); every cap_minutes is an exact integer.

Step 2 — Evaluate every window in one SQL pass

A window function with an interval-bounded RANGE frame sums the metric over each cap’s trailing window for every duty. Parameterising the frame length from the cap row lets one statement evaluate all the hour-based windows.

SELECT
    d.crew_id,
    d.duty_id,
    c.regime,
    c.metric,
    c.window_hours,
    SUM(
        CASE c.metric
            WHEN 'fdp'    THEN d.fdp_minutes
            WHEN 'flight' THEN d.flight_minutes
            ELSE d.duty_minutes
        END
    ) 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,
    (SUM(/* … */ d.duty_minutes) OVER (/* … */)) > c.cap_minutes AS breached
FROM duty_period AS d
JOIN cumulative_cap AS c ON c.ruleset_id = current_ruleset(d.report_utc::date, c.regime);

Rows where window_minutes exceeds cap_minutes are the violations. Because the window length comes from the cap row, the same statement evaluates the 168-hour, 672-hour, and every EASA day window without a line of per-cap code.

Step 3 — The in-process Polars equivalent

When the caps are checked inside a pairing optimiser rather than a reporting job, Polars evaluates the same rolling windows in memory. Each cap becomes one rolling aggregation keyed on the crew and anchored on the UTC instant.

import polars as pl


def evaluate_caps(duties: pl.DataFrame, caps: list[dict]) -> pl.DataFrame:
    frames = []
    for cap in caps:
        col = {"fdp": "fdp_minutes", "flight": "flight_minutes", "duty": "duty_minutes"}[cap["metric"]]
        rolled = (
            duties.sort("report_utc")
            .rolling(index_column="report_utc", period=f"{cap['window_hours']}h", group_by="crew_id")
            .agg(pl.col(col).sum().alias("window_minutes"))
            .with_columns(
                pl.lit(cap["regime"]).alias("regime"),
                pl.lit(cap["window_hours"]).alias("window_hours"),
                (pl.col("window_minutes") > cap["cap_minutes"]).alias("breached"),
            )
        )
        frames.append(rolled)
    return pl.concat(frames)

Verify: for a crew with 112 duty hours in a 14-day window, evaluate_caps marks the window_hours == 336 EASA row breached = True (6720 minutes against the 6600-minute cap) while leaving the 7-day and 28-day rows clear — the same result the cumulative duty caps compared scenario reaches by hand.

Failure modes and troubleshooting

• Anchoring windows on local time. A date-line crossing reorders duties by local clock and mis-sums the frame. Remediation: order and bound every window on the UTC instant.
• Metering the wrong quantity. Running FDP minutes against an EASA duty cap understates the total. Remediation: select the metric column from the cap row, never a fixed column.
• Calendar-month approximation. The 12-month EASA flight cap and the 365-day FAA flight cap are different windows; treating a calendar month as 730 hours drifts. Remediation: evaluate the calendar-aligned caps with date truncation, not an hour frame.
• Inclusive-boundary double counting. A duty whose report instant sits exactly on the window edge must be counted once; an off-by-one on the frame bound double-counts it. Remediation: use RANGE … PRECEDING AND CURRENT ROW and test the boundary duty.
• Sampling only some windows. Checking the 7-day and 28-day EASA caps but skipping the 14-day one hides the breach that only the 14-day window catches. Remediation: evaluate every seeded cap.

Frequently Asked Questions

Why store the window length as data instead of writing one query per cap?

Because the caps change and multiply. Storing the window and cap as rows lets one statement evaluate the current FAA and EASA sets and absorb a future amendment as an INSERT. Per-cap queries drift out of sync the moment a limit is revised.

Are the FAA 672-hour and EASA 28-day windows the same check?

No. Both are about four weeks, but the FAA window is an exact 672-hour clock metering FDP or flight hours, while the EASA window is a calendar-aligned 28-day window metering duty or flight. A roster can pass one and fail the other.

How is the 12-calendar-month EASA flight cap handled?

Separately, because a calendar month is not a fixed number of hours. It is evaluated by truncating report instants to months and summing flight minutes over a trailing twelve-month span, rather than with a fixed-hour rolling frame.

Do these caps change with augmentation?

No. Augmentation raises the daily FDP ceiling but leaves the cumulative caps untouched. An augmented block still meters against the same rolling windows seeded here.

Related

• Regulatory Reference Tables — the parent section and schema these caps seed.
• FAA Part 117 Table B FDP Limits as Queryable Data — the daily-ceiling grid that sits alongside these cumulative caps.
• Cumulative Duty Caps Compared — a worked scenario using these exact windows.
• Duty Time Validation & Rule Engines — the engines that enforce these caps against live rosters.

Back to Regulatory Reference Tables.