Encoding §117.13 Table B Lookups in SQL

The exact task this guide solves is making the §117.13 Table B ceiling available inside the database, so a fleet-wide compliance query can join each duty to its maximum flight duty period without round-tripping every row to application code. When the grid lives only in Python, a nightly sweep of a hundred thousand duties pays a function call per row; when it lives in an indexed lookup table, the same sweep resolves every ceiling in one set-based query. This page models Table B as range-typed rows, indexes it for containment queries, writes a resolver function, and joins it into an evaluation pass. It implements the schema decisions from the FAA Part 117 rule schema design and complements the Python treatment in the regulatory reference tables.

Prerequisites

Step 1 — Model the grid with an integer range

Each Table B band is a half-open range of minutes-past-midnight; each segment count is a small integer. Representing the band as an int4range lets PostgreSQL answer “which band contains 330 minutes?” with a containment operator instead of a pair of comparisons, and a GiST index makes that containment lookup fast.

CREATE TABLE fdp_table_b (
    ruleset_id   int         NOT NULL REFERENCES reg_ruleset,
    start_band   int4range   NOT NULL,   -- minutes past midnight, half-open
    segments     smallint    NOT NULL CHECK (segments BETWEEN 1 AND 7),
    limit_minutes smallint   NOT NULL CHECK (limit_minutes > 0),
    EXCLUDE USING gist (ruleset_id WITH =, segments WITH =, start_band WITH &&)
);

-- Example rows for the 0500-0559 band (minutes 300..360).
INSERT INTO fdp_table_b (ruleset_id, start_band, segments, limit_minutes) VALUES
  (1, '[300,360)', 1, 720), (1, '[300,360)', 2, 720),
  (1, '[300,360)', 3, 720), (1, '[300,360)', 4, 720),
  (1, '[300,360)', 5, 690), (1, '[300,360)', 6, 660),
  (1, '[300,360)', 7, 630);

The EXCLUDE constraint is doing real work: it makes it physically impossible to insert two overlapping bands for the same segment and ruleset, so the off-by-one band overlap that plagues hand-coded lookups is rejected at write time, not discovered in production.

Verify: attempting to insert a second [300,400) row for segment 1 in ruleset 1 raises a constraint violation, because [300,400) overlaps the existing [300,360).

Step 2 — Resolve a ceiling with a containment query

The resolver clamps the segment count to seven and looks up the band that contains the report minute. A wrapping midnight band is stored as two rows — [1380,1440) and [0,240) — so no range has to cross the day boundary, which keeps the containment operator simple.

CREATE FUNCTION resolve_fdp_minutes(p_ruleset int, p_report_min int, p_segments int)
RETURNS smallint LANGUAGE sql STABLE AS $$
    SELECT limit_minutes
    FROM fdp_table_b
    WHERE ruleset_id = p_ruleset
      AND segments = LEAST(GREATEST(p_segments, 1), 7)
      AND start_band @> p_report_min
    LIMIT 1;
$$;

Verify: SELECT resolve_fdp_minutes(1, 330, 4) returns 720 — 330 minutes is 05:30, the four-segment column of the 0500–0559 band. SELECT resolve_fdp_minutes(1, 330, 12) returns 630, the clamped 7+ column.

Step 3 — Join the ceiling into a fleet evaluation

The point of putting Table B in the database is to resolve every duty’s ceiling in one set-based pass. A LATERAL join calls the resolver once per duty and returns the headroom alongside the actual FDP, so the violations fall straight out of a WHERE.

SELECT
    d.duty_id,
    d.crew_id,
    d.fdp_minutes,
    b.limit_minutes,
    d.fdp_minutes - b.limit_minutes AS overage_minutes
FROM duty_period AS d
CROSS JOIN LATERAL (
    SELECT resolve_fdp_minutes(
        current_ruleset(d.report_utc::date, 'FAA'),
        EXTRACT(hour FROM d.report_acclimatised)::int * 60
            + EXTRACT(minute FROM d.report_acclimatised)::int,
        d.segments
    ) AS limit_minutes
) AS b
WHERE d.fdp_minutes > b.limit_minutes;
Range-indexed Table B containment lookup A report time of 330 minutes past midnight is tested for containment against the half-open integer ranges of the Table B bands. It falls inside the 300-to-360 band; the four-segment column of that band yields a 720-minute flight-duty-period ceiling, which is compared against the duty's actual FDP to produce headroom. report_min 330 [240,300) [300,360) ✓ [360,420) start_band @> 330 seg 4 column 720 min headroom = 720 − fdp_minutes
The report minute is tested for containment against half-open band ranges; the matching band's segment column gives the ceiling, and a single subtraction yields per-duty headroom across the whole fleet.

Failure modes and troubleshooting

Frequently Asked Questions

Why use a range type instead of two integer columns?

A range type lets PostgreSQL answer containment with the @> operator and enforce non-overlap with an EXCLUDE constraint. Two plain columns require a BETWEEN and give the database no way to reject overlapping bands at write time, which is exactly where band bugs originate.

Does resolving Table B in SQL replace the Python resolver?

They serve different callers. The SQL resolver powers set-based fleet sweeps and reporting; the Python resolver powers the interactive pairing optimiser. Both read the same seed values, so a single amendment updates both.

How do I keep the SQL and Python grids from drifting?

Seed both from one source of truth — the effective-dated reference rows — and load the Python dictionary from the same table at start-up rather than hand-transcribing it. A test asserts the two resolvers agree on every cell.

What about augmented operations?

Table B is unaugmented only. Augmented duties resolve against a separate Table C lookup, modelled the same way with its own range-typed rows and a resolver keyed on rest-facility class.

Back to FAA Part 117 Rule Schema Design.