ciaovolo/PRD-reverse-scan.md

PRD: Reverse Scan — Country → Specific Airport(s)

Status: Ready for implementation Date: 2026-03-01 Target: Web App (api_server.py + scan_processor.py + database + frontend)

---

1. Problem

All scans today are fixed-origin, variable-destination:

"From BDS — which German airports can I fly to directly?"

The opposite question is equally common but unsupported:

"I want to fly to BRI or BDS — which German airport should I depart from?"

This is the natural perspective of a traveller based in Germany looking for the cheapest access point to Puglia, not a traveller already in Puglia wondering where to go.

---

2. Goal

Let users create a scan where:

• Origin = all airports in a country (e.g. DE)
• Destination = one or more specific airports (e.g. BRI, BDS)

The results table for a reverse scan shows one row per origin airport × destination pair, so the user can directly compare which German airport is cheapest for each destination.

---

3. User Stories

• As a user, I want to search DE → BRI,BDS so I can see which German airport has the cheapest direct flight to Puglia.
• As a user, I want to schedule a reverse scan to run weekly.
• As a user, I want to mix both directions in my scan history.
• As a user, I want the destination country in forward scans to be a dropdown, not free text.

---

4. Scope

In scope

• scan_mode field on scans and scheduled scans: "forward" (default) or "reverse"
• Reverse scan: origin = ISO country (dropdown), destinations = specific airport(s)
• Forward scan: destination country changed from text input to dropdown
• New GET /api/v1/countries endpoint (powers both dropdowns)
• routes table: add origin_airport column (nullable; populated for reverse scans)
• flights table: add origin_airport column (nullable; populated for reverse scans)
• ScanDetails routes table: show Origin column for reverse scans
• Scheduled scans: support scan_mode

Out of scope

• Multi-country origin (e.g. DE + IT → BRI)
• Round-trip reverse scans

---

5. Data Model Changes

5.1 scans table

ALTER TABLE scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward'
    CHECK (scan_mode IN ('forward', 'reverse'));

Column semantics by mode:

Column	Forward (existing)	Reverse (new)
origin	Single IATA (e.g. BDS)	ISO country code (e.g. DE)
country	ISO destination country OR comma-separated destination IATAs	Comma-separated destination IATAs (e.g. BRI,BDS)
scan_mode	forward	reverse

All existing rows default to forward. No data migration needed.

5.2 routes table

ALTER TABLE routes ADD COLUMN origin_airport TEXT;

• Forward scans: origin_airport = NULL (origin is always the scan-level origin IATA, already known)
• Reverse scans: origin_airport = the specific origin IATA for this route (e.g. BER, FRA)

The unique constraint on routes changes from (scan_id, destination) to (scan_id, COALESCE(origin_airport, ''), destination).

Reverse scan routes example for DE → BRI, BDS:

origin_airport	destination	flight_count	min_price
BER	BRI	14	€15
FMM	BRI	8	€17
DUS	BRI	6	€22
BER	BDS	3	€28
MUC	BDS	2	€34

5.3 flights table

ALTER TABLE flights ADD COLUMN origin_airport TEXT;

• Forward scans: origin_airport = NULL (origin is the scan-level origin IATA)
• Reverse scans: origin_airport = the specific IATA the flight departs from

5.4 scheduled_scans table

ALTER TABLE scheduled_scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward'
    CHECK (scan_mode IN ('forward', 'reverse'));

Same column semantics as scans. When the scheduler fires a reverse scheduled scan, it creates a reverse scan child.

---

6. New API Endpoint

GET /api/v1/countries

Returns the list of countries that have at least one airport in the database. Powers both the forward-mode destination country dropdown and the reverse-mode origin country dropdown.

Response:

{
  "data": [
    { "code": "DE", "name": "Germany", "airport_count": 23 },
    { "code": "IT", "name": "Italy", "airport_count": 41 },
    ...
  ]
}

Sorted alphabetically by name. Derived from data/airports_by_country.json at startup (same source as airport resolution). Rate limit: 100/min.

Country names are resolved from a static ISO → name mapping in api_server.py (same COUNTRY_NAME_TO_ISO dict already in airports.py, inverted).

---

7. Backend Changes

7.1 ScanRequest model

Add scan_mode: Literal['forward', 'reverse'] = 'forward'.

Root validator enforces:

Forward mode (unchanged):

• origin: required, 3-char IATA
• country OR destinations: required

Reverse mode:

• origin: required, 2-char ISO country code
• destinations: required, 1–10 IATAs
• country must be absent or null

7.2 scan_processor.py — process_scan()

Forward (unchanged):

fixed origin IATA × [airports resolved from destination country / destination IATAs]

Reverse (new):

[airports resolved from origin country] × fixed destination IATA(s)

Steps for reverse:

1. Read scan.origin as ISO country code → resolve airport list via airports.py
2. Read scan.country as comma-separated destination IATAs
3. Build pairs: [(orig, dest) for orig in origin_airports for dest in destination_iatas]
4. For each pair: call search_multiple_routes(orig, dest) as usual
5. Save route with origin_airport=orig, destination=dest
6. Save each flight with origin_airport=orig

total_routes = len(origin_airports) × len(destination_iatas).

7.3 GET /scans/{id}/routes response

Add origin_airport: str | null to the Route response model. Frontend uses this to show/hide the Origin column.

7.4 GET /scans/{id}/flights response

Add origin_airport: str | null to the Flight response model.

7.5 Scheduled scans

ScheduleRequest and Schedule models gain scan_mode. The scheduler's _fire_due_scans() function passes scan_mode when inserting child scans into the scans table.

---

8. Frontend Changes

8.1 New countriesApi client (api.ts)

export interface Country { code: string; name: string; airport_count: number; }

export const countriesApi = {
  list: () => api.get<{ data: Country[] }>('/countries'),
};

8.2 New CountrySelect component

A standard <select> dropdown, populated from GET /api/v1/countries on mount. Shows "Germany (DE)" style labels, emits the ISO code. Reused in two places:

• Forward scan form: destination country field (replaces current text input)
• Reverse scan form: origin country field

8.3 Scan creation form (/scans page)

Add a Direction toggle at the top of the form (same segmented button style as "By Country / By Airports"):

[ → Forward ]  [ ← Reverse ]

Forward mode (current layout, destination country now uses CountrySelect):

Origin airport:       [AirportSearch]
Destination:          [By Country ▼] [By Airports]
  → Country:          [CountrySelect dropdown]
  → Airports:         [comma-sep IATA input]

Reverse mode (new):

Origin country:       [CountrySelect dropdown]
Destination airports: [AirportSearch / comma-sep IATA input]

8.4 ScanDetails — routes table

For reverse scans (scan_mode === 'reverse'), prepend an Origin column to the routes table:

Origin	Destination	Flights	Airlines	Min	Avg	Max
BER Berlin	BRI Bari	14	Ryanair	€15	€22	€45
FMM Memmingen	BRI Bari	8	Ryanair	€17	€25	€50

The existing click-to-expand flights sub-table still works — shows individual flight dates/times/prices for that specific origin→destination pair.

For forward scans: routes table unchanged (no Origin column).

8.5 ScanDetails — header

Forward: BDS → DE
Reverse: DE → BRI, BDS

8.6 CreateScanRequest type update (api.ts)

export interface CreateScanRequest {
  scan_mode?: 'forward' | 'reverse';
  origin: string;           // IATA (forward) or ISO country code (reverse)
  country?: string;         // forward only
  destinations?: string[];  // forward (by airports) or reverse
  window_months?: number;
  seat_class?: 'economy' | 'premium' | 'business' | 'first';
  adults?: number;
}

---

9. API Contract

POST /api/v1/scans

Forward — by country (existing, unchanged):

{ "origin": "BDS", "country": "DE", "window_months": 6, "seat_class": "economy", "adults": 1 }

Forward — by airports (existing, unchanged):

{ "origin": "BDS", "destinations": ["FRA", "MUC"], "window_months": 6, "seat_class": "economy", "adults": 1 }

Reverse (new):

{ "scan_mode": "reverse", "origin": "DE", "destinations": ["BRI", "BDS"], "window_months": 6, "seat_class": "economy", "adults": 1 }

---

10. Migration (init_db.py)

Four ALTER TABLE … ADD COLUMN statements, each guarded by try/except OperationalError:

migrations = [
    "ALTER TABLE scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward' CHECK (scan_mode IN ('forward', 'reverse'))",
    "ALTER TABLE routes ADD COLUMN origin_airport TEXT",
    "ALTER TABLE flights ADD COLUMN origin_airport TEXT",
    "ALTER TABLE scheduled_scans ADD COLUMN scan_mode TEXT NOT NULL DEFAULT 'forward' CHECK (scan_mode IN ('forward', 'reverse'))",
]
for sql in migrations:
    try:
        conn.execute(sql)
    except sqlite3.OperationalError:
        pass  # column already exists
conn.commit()

---

11. Acceptance Criteria

1. GET /api/v1/countries returns a sorted list of countries with airport counts.
2. Forward scan destination country field is a dropdown populated from that endpoint.
3. A reverse scan DE → BRI, BDS can be created via the form and the API.
4. The processor iterates all German airports × [BRI, BDS], storing origin_airport on each route and flight row.
5. ScanDetails for a reverse scan shows the Origin column in the routes table and DE → BRI, BDS in the header.
6. Scheduled scans accept scan_mode; the scheduler passes it through to child scans.
7. All existing forward scans continue to work — no regressions.
8. All four DB columns default correctly after migration with no data loss.