domverse
77d2a46264
All checks were successful
Deploy / deploy (push) Successful in 30s
- DB schema: relaxed origin CHECK to >=2 chars, added scan_mode column to
scans and scheduled_scans, added origin_airport to routes and flights,
updated unique index to (scan_id, COALESCE(origin_airport,''), destination)
- Migrations: init_db.py recreates tables and adds columns via guarded ALTERs
- API: scan_mode field on ScanRequest/Scan; Route/Flight expose origin_airport;
GET /scans/{id}/flights accepts origin_airport filter; CreateScheduleRequest
and Schedule carry scan_mode; scheduler and run-now pass scan_mode through
- scan_processor: _write_route_incremental accepts origin_airport; process_scan
branches on scan_mode=reverse (country → airports × destinations × dates)
- Frontend: new CountrySelect component (populated from GET /api/v1/countries);
Scans page adds Direction toggle + CountrySelect for both modes; ScanDetails
shows Origin column for reverse scans and uses composite route keys; Re-run
preserves scan_mode
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
322 lines
10 KiB
Markdown
322 lines
10 KiB
Markdown
# 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
|
||
|
||
```sql
|
||
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
|
||
|
||
```sql
|
||
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
|
||
|
||
```sql
|
||
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
|
||
|
||
```sql
|
||
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:**
|
||
```json
|
||
{
|
||
"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`)
|
||
|
||
```typescript
|
||
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`)
|
||
|
||
```typescript
|
||
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):**
|
||
```json
|
||
{ "origin": "BDS", "country": "DE", "window_months": 6, "seat_class": "economy", "adults": 1 }
|
||
```
|
||
|
||
**Forward — by airports (existing, unchanged):**
|
||
```json
|
||
{ "origin": "BDS", "destinations": ["FRA", "MUC"], "window_months": 6, "seat_class": "economy", "adults": 1 }
|
||
```
|
||
|
||
**Reverse (new):**
|
||
```json
|
||
{ "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`:
|
||
|
||
```python
|
||
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.
|