Award Flight Daily MCP Server

A FastMCP server that wraps the Award Flight Daily database (12.3M award flight records across 25 loyalty programs) and exposes it to AI agents via tools.

Overview

The Award Flight Daily MCP server provides 7 core tools for searching, analyzing, and optimizing award travel:

1. afd_search_award_flights - Core search across 12M+ records
2. afd_list_programs - All 25 programs with statistics
3. afd_get_program_details - Deep dive on a single program
4. afd_get_route_availability - Calendar view for a route
5. afd_find_sweet_spots - Best-value redemptions
6. afd_check_transfer_partners - Credit card transfer ratios
7. afd_get_market_stats - Aggregate database statistics

File Structure

mcp_server/
├── __init__.py                 # Package definition
├── config.py                   # Constants: programs, cabins, banks
├── server.py                   # FastMCP server entry point (7 tools registered)
├── db/
│   ├── __init__.py
│   └── queries.py             # DuckDB queries (read-only, parameterized)
├── models/
│   ├── __init__.py
│   ├── inputs.py              # 8 Pydantic input models with validators
│   └── responses.py           # Formatting helpers (JSON/Markdown)
└── tools/
    ├── __init__.py
    ├── search.py              # afd_search_award_flights
    ├── programs.py            # afd_list_programs, afd_get_program_details
    ├── routes.py              # afd_get_route_availability
    ├── sweet_spots.py         # afd_find_sweet_spots
    ├── transfers.py           # afd_check_transfer_partners
    └── analytics.py           # afd_get_market_stats

Configuration

All environment and program configuration lives in config.py:

- MCP_SERVER_NAME: "awardflightdaily_mcp"
- DUCKDB_PATH: Environment variable, defaults to /data/award_flights.duckdb
- PROGRAMS: Dictionary of 25 programs (slug -> full name)
- CABINS: Cabin class codes (Y/W/J/F)
- BANKS: 7 credit card programs

Installation & Deployment

Requirements

fastmcp>=1.0.0
pydantic>=2.0
duckdb==1.1.3

Running

Stdio mode (local):

python -m mcp_server.server

HTTP mode (remote):

python -m mcp_server.server --http 8001

Tools API

1. Search Award Flights

SearchInput(
    origin="JFK",                      # Required: IATA code(s)
    destination="NRT",                 # Required: IATA code(s)
    date_from="2026-06-01",           # Required: YYYY-MM-DD
    date_to="2026-06-30",             # Required: YYYY-MM-DD
    cabin=CabinClass.BUSINESS,        # Optional: Y/W/J/F (default J)
    source="united,aeroplan",         # Optional: program filter
    direct_only=False,                # Optional: nonstop only
    max_miles=100000,                 # Optional: mileage cap
    min_seats=1,                      # Optional: min seats (default 1)
    limit=50,                         # Optional: results limit (default 50, max 200)
    offset=0,                         # Optional: pagination offset
    response_format=ResponseFormat.JSON # Optional: JSON or Markdown
)

Returns: Paginated flight results with mileage, taxes, seats, airlines, equipment.

2. List Programs

ListProgramsInput(
    response_format=ResponseFormat.JSON
)

Returns: All 25 programs with:
- Total flights & routes
- Date range
- Cabin availability counts (Y/W/J/F)

3. Program Details

ProgramDetailInput(
    program="united",  # Required: program slug
    response_format=ResponseFormat.JSON
)

Returns: Deep stats for one program:
- Total availability
- Unique routes & airports
- Average & minimum mileage by cabin

4. Route Availability

RouteInput(
    origin="JFK",
    destination="NRT",
    cabin=CabinClass.BUSINESS,
    source=None,  # Optional: filter by program
    response_format=ResponseFormat.JSON
)

Returns: All dates for a route with mileage, taxes, seats per program.

5. Find Sweet Spots

SweetSpotInput(
    cabin=CabinClass.BUSINESS,
    origin=None,  # Optional
    destination=None,  # Optional
    limit=25,
    response_format=ResponseFormat.JSON
)

Returns: Best-value routes ranked by minimum mileage cost.

6. Transfer Partners

TransferInput(
    bank="chase",      # Optional: bank slug
    program="united",  # Optional: program slug
    response_format=ResponseFormat.JSON
)

Returns: Credit card → airline transfer mappings with:
- Transfer ratio (e.g., "1:1")
- Speed (e.g., "Instant", "1-2 days")

7. Market Stats

MarketStatsInput(
    response_format=ResponseFormat.JSON
)

Returns: Aggregate database stats:
- Total records, programs, routes
- Airport coverage
- Cabin availability breakdown

Input Validation

All inputs use Pydantic with validation:

- IATA codes: Must be exactly 3 alphabetic characters
- Dates: YYYY-MM-DD format only
- Cabin: Enum restricted to Y/W/J/F
- Limit: 1-200 results
- Offset: >= 0
- Min seats: 1-9

Invalid inputs raise ValidationError with detailed messages.

Response Formats

JSON (default)

Full structured response with pagination metadata:

{
  "total": 1234,
  "count": 50,
  "offset": 0,
  "has_more": true,
  "cabin": "J",
  "results": [
    {
      "id": "...",
      "source": "united",
      "origin": "JFK",
      "destination": "NRT",
      "date": "2026-06-15",
      "mileage": 75000,
      "taxes": 11.20,
      "seats": 2,
      "direct": true,
      "airlines": "United",
      "equipment": "B787",
      "updated_at": "2026-03-26T12:34:56"
    }
  ]
}

Markdown

Human-readable output with formatting:

```markdown