PILOT SCHEDULE BIDDING TOOL: FRAMEWORK

Scoping draft

Red = must, no exceptions
Yellow = needs a decision
Green = settled
Automation category
Rules-based automation with weighted optimization
Process automation Constraint filtering Weighted scoring

The engine applies fixed rules and a scoring formula we define, so the same inputs always produce the same output and every result can be traced back to a rule (defined parameter).

A machine learning layer becomes possible later, once several quarters of bid history exist: forecasting coverage gaps before a window opens, for example. Out of scope for this build.

1

Bid Cycle Flow

Per quarter
ROSTER LOADED FROM JETINSIGHT Bid window opens, approx. 30 days? Pilot submits bid Ranks up to 5 line and slot choices. WINDOW CLOSES, HARD CUTOFF MUST PASS Type qualification, seat authority, coverage minimum, never-pair list PAIRING ENGINE Filter first, then score what survives SCORING INPUTS Bid rank, seniority, crew preference Weighting not yet set Ranked pairings, each with a score Score shows why, so a low one is explainable Scheduler approves or overrides Engine recommends. A person publishes.

The engine never publishes on its own. It narrows the field and shows its work; the scheduler still makes the call.

The pairing engine is the puzzle assembly step Every box above the engine is data collection: who is qualified, who wants which line, who cannot fly with whom. The engine box is where those pieces get assembled into a quarter that actually works. Today that assembly happens by hand in the spreadsheet, one line at a time, holding qualification and coverage and preference in your head at once. Automating the engine does not change the puzzle. It changes who solves it, and how long a rebuild takes when one piece moves.
2

What Pilots Actually Bid On

From the current spreadsheet

Reading the existing sheet changed my assumption here. Pilots are not picking individual days off a blank calendar. They are picking from pre-built rotation lines, so a bid is a ranked list of line plus slot combinations. That is a far simpler thing to build and a far simpler thing for a pilot to fill out.

Line Pattern Start date Slots
A Standard 8 on / 6 off Jul 30 P1, P2 (PIC) · S1, S2 (SIC)
B Standard 8 on / 6 off Jul 31 P1, P2 (PIC) · S1, S2 (SIC)
C Standard 8 on / 6 off Aug 1 P1, P2 (PIC) · S1, S2 (SIC)
HB Home based 9 on / 7 off Jul 30 P1, P2 (PIC) · S1, S2 (SIC)
How the slots work P is the PIC seat, S is the SIC seat. Each line carries two of each. P1 and P2 are opposing rotations on the same line: when P1 is on, P2 is off. Same for S1 and S2. On every date block, P1 flies with S1 and P2 flies with S2. So the crew pairing is decided the moment slots are assigned, which is why the qualification rules have to be enforced at assignment rather than checked afterward.
Confirm before building Are the lines fixed for the quarter and published before the window opens, or does the number of lines change based on how the fleet is scheduled? This determines whether the tool needs a line builder or just a line importer. Also: is each line tied to a specific aircraft type, or can a line be flown by either fleet?
Other things the sheet is carrying Alongside line choices, the current sheet holds hard-off date requests, preferred crew groupings, do-not-pair entries marked with a minus, training assignments, and notes about who sells often. Each of those needs its own field rather than living in a comment. Confirm I have read the minus notation correctly as do-not-pair.
3

Pairing Rules, In Priority Order

Company rules
# Type Rule What the engine does
1 ★ MUST Both pilots qualified on the same aircraft type Phenom crew fly the Phenom, Praetor crew fly the Praetor. Dual-qualified pilots are eligible for either. Any mismatch is rejected before scoring.
2 ★ MUST Every crew has at least one PIC An SIC pairs only with a PIC. SIC plus SIC is rejected outright.
3 ★ MUST Never-pair list respected Any listed combination is removed from the candidate pool. Static list, maintained by Ops.
4 ★ MUST Daily coverage minimum met A pairing that leaves a day short is rejected regardless of how well it scores. Threshold still needs defining, see section 5.
5 ★ MUST Seniority breaks every tie When two pilots bid the same days and coverage supports only one, the senior pilot wins. Applied consistently, not case by case.
6 Preferred Honor the highest-ranked bid choice possible A first-choice hit scores above a third-choice hit. Relative weighting still open.
7 Preferred Avoid PIC plus PIC when PIC plus SIC works Legal, but it spends two PICs on one seat requirement. Scored down so it only surfaces when nothing better fits.
8 Preferred Match stated crew preference Lowest weight. Moves a score only when two pairings are otherwise equal. Never overrides anything above it.
Consequence for the database Rules 1 and 2 mean qualification cannot be a single field. A pilot might hold PIC on the Phenom and SIC on the Praetor, PIC on both, or SIC only. Seat authority has to be stored per pilot per aircraft type or dual-qualified crew get mishandled.
4

How It Is Built

Same stack as existing tools
JETINSIGHT ROSTER CSV Loaded once, before each window opens BID FORM CLOUDFLARE PAGES Pilot sees the real dates, ranks five line choices. PAIRING ENGINE CLOUDFLARE WORKER Rejects illegal pairs, scores and ranks the rest. SCHEDULE REVIEW HUMAN DECISION Approves, overrides, publishes. @Paolo, this is you PERSISTENT DATABASE CLOUDFLARE D1 Pilots and qualifications, submitted bids, generated pairings, override log. Nothing lives in the browser.
Two working mockups attached, as an example Pilot view. Ranking five line choices, preloaded with the real Q4 line data from the current sheet. Click a slot to rank it.

Line setup view. The step that has to happen before any pilot can bid. Lines get defined once per quarter, then published. Enter the pattern and a start date and every rotation block generates itself.
5

Needs a Decision

Blocks the database design
1. Coverage minimums Is there a documented minimum PIC and SIC count per day per aircraft type, or is it currently judgment based? A ratio per tail rather than a flat headcount would scale as the fleet grows, so going from seven Phenoms to nine does not mean rebuilding the rule. A fractional figure per aircraft may be the honest number given how often we run above the scheduled bid on sold days.
2. Sell days Sell-day designation already lives in another platform. Does the bidding tool need to read or write any of that, or does selling stay entirely downstream of the base schedule?
3. Bid window mechanics Assumption is a hard cutoff: opens roughly 30 days out, closes on a set date, no late submissions and no edits after close. Confirm that matches how the cycle should run.
4. Seniority list Is there a written seniority list already, something the engine could read directly? If it is established, I will pull from it as is. If it is not, we need one before the tie-breaking rule means anything, and I have some ideas on how to structure it. I did not want to build one if it already exists.
5. Scoring weights How much a first choice beats a second, what a crew-preference match adds, how hard to penalize a double-PIC pairing. Tunable after the first cycle, but the inputs need agreeing before the engine gets written.
6

My Assumptions, Confirm These

Bid format Quarterly. Pilots rank up to five line and slot choices rather than three, and see the actual date blocks for each line while choosing.
Tie-breaking Seniority, applied consistently rather than case by case.
Crew preference Recorded and used, but as the lowest-weighted input. Never overrides coverage or qualification.
Never-pair list Static, maintained manually, applied as a filter.