Skip to content

PHASE3_PLAN.md

Latest commit

 

History

History
171 lines (132 loc) · 8.42 KB

PHASE3_PLAN.md

File metadata and controls

171 lines (132 loc) · 8.42 KB
Raw

Phase 3 Plan — Historical Data Capture + DuckDB Analytics

Status: in development Date: 2026-05-05 Scope: Capture WMATA live data on a recurring schedule, persist to a local DuckDB file, expose basic exploration in the dashboard. Defer actual analytics (on-time performance, headway analysis) to Phase 4.

Goals

  1. Persistence — turn the dashboard's ephemeral live view into a durable historical record.
  2. Foundation for analytics — schema must support Phase 4 queries (on-time performance, headway, alert frequency by line/time).
  3. Teaching value — students see a real data-engineering loop: poll → normalize → store → query.
  4. Stay within free-tier limits — default capture cadence must respect 50,000 calls/day budget.

Non-Goals

  • Not building on-time performance metrics — that's Phase 4.
  • Not building a long-running production data pipeline — this is a teaching example.
  • Not distributed capture, queues, or schedulers — single process, run via cron or by hand.

Architecture

┌─────────────────┐     poll      ┌──────────────────┐
│  scripts/       │ ────────────▶ │  WMATA API       │
│  capture.py     │ ◀──────────── │                  │
│  (CLI entry)    │               └──────────────────┘
└────────┬────────┘
         │ uses
         ▼
┌────────────────────┐
│  wmata.capture     │   one_capture() — single iteration
│                    │   capture_loop() — periodic
└────────┬───────────┘
         │ writes
         ▼
┌────────────────────┐                ┌──────────────────┐
│  wmata.storage     │ ─── opens ───▶│  data/wmata.duckdb│
│  - init_schema()   │                │   (gitignored)    │
│  - insert_*(...)   │                └────────▲──────────┘
│  - query helpers   │                         │
└────────────────────┘                         │ reads
                                               │
                                       ┌───────┴──────────┐
                                       │  app/dashboard   │
                                       │  History tab     │
                                       └──────────────────┘

Schema

Single DuckDB file at data/wmata.duckdb (gitignored). Six tables:

Table Grain Key columns
predictions one row per train per capture captured_at, station_code, line, destination, min_raw, min_int
bus_arrivals one row per bus prediction per capture captured_at, stop_id, route_id, minutes, vehicle_id
rail_incidents one row per active rail alert per capture captured_at, incident_id, description, lines_affected
bus_incidents one row per active bus alert per capture captured_at, incident_id, description, routes_affected
elevator_outages one row per outage per capture captured_at, station_code, unit_name, unit_status
capture_runs one row per run started_at, finished_at, predictions_inserted, errors

Design choices:

  • No dedup at insert time. Every row is tagged with captured_at and we keep all observations. Phase 4 analytics aggregate as needed (e.g., distinct incident_id per hour). This keeps the capture loop simple and idempotent — re-running the same window produces extra rows, not corrupted state.
  • min_int precomputed — predictions store both the raw Min string ("ARR", "BRD", "5") and a derived min_int (NULL for ARR/BRD/---/numeric-failure). This makes Phase 4 wait-time queries trivial.
  • Append-only + indexed on captured_at — DuckDB doesn't need explicit indexes for this scale, but ordering inserts by capture time keeps zone-map pruning effective.

Capture Strategy

Default config (overridable via CLI flags):

Setting Default Rationale
Interval 120 sec Yields ~720 runs/day; well under rate budget
Rail stations A01, B01, C04 (Metro Center, Gallery Pl, Foggy Bottom) Small enough to fit budget; high-traffic for interesting data
Bus stops none by default Optional, students can add
Capture all alerts yes Cheap (one call each)
Capture all elevator outages yes One call per run

Per-run cost with defaults: 3 prediction calls + 1 rail-incidents + 1 bus-incidents + 1 elevator + 1 station-list (cached) ≈ 6 calls/run. Per day: 720 × 6 = 4,320 calls/day — comfortably under the 50,000 limit.

Two run modes:

# One iteration, then exit. Good for cron, testing, manual demo.
python scripts/capture.py --once

# Run forever, configurable interval (in seconds).
python scripts/capture.py --interval 120

Dashboard Integration

Add a fourth tab: 📈 History

Shows:

  • Capture status — total row counts per table, oldest/newest timestamp, last run summary
  • Recent captures — last 10 rows from capture_runs for at-a-glance health check
  • Arrival history at selected station — line chart of min_int over time for the sidebar-selected station, last N hours
  • Empty-state messaging — clear instructions if data/wmata.duckdb doesn't exist yet (point to the capture command)

This is exploratory — Phase 4 will add real on-time performance charts.

Testing

Add to tests/run_tests.py:

ID Level What Pass condition
H1 L1 init_schema() is idempotent Run twice, no error, all 6 tables exist
H2 L1 Insert + query roundtrip Insert sample rows; query returns same count
H3 L1 min_int derivation "ARR" → NULL, "5" → 5, "---" → NULL, "BRD" → NULL
H4 L2 one_capture() end-to-end Returns dict with row counts; tables grow
H5 L2 Empty database queries get_capture_stats() on empty DB returns sensible defaults, no exception

Tests use a temp DuckDB file (data/test_wmata.duckdb) and clean up after.

Files Created / Modified

File Action
wmata/storage.py NEW — DuckDB schema, insert helpers, query helpers
wmata/capture.py NEW — one_capture(), capture_loop()
scripts/capture.py NEW — CLI entry point with argparse
pyproject.toml MOD — add duckdb>=1.0
app/dashboard.py MOD — add 📈 History tab
tests/run_tests.py MOD — add H1–H5
tests/test_results.md MOD — add Iteration 4 results
action_log.md MOD — add Session 5 entry
lessons_learned.md MOD — add Phase 3 reflection
README.md MOD — update roadmap to "Phase 3 ✅"

Risks & Mitigations

Risk Mitigation
DuckDB lock contention if dashboard reads while capture writes DuckDB supports concurrent readers; the dashboard opens read-only
Long capture loops eat the API budget Default 120s interval; document the math; CLI logs running call count
Empty database breaks the History tab Each query in wmata.storage handles missing-table case gracefully
Tests pollute the real database Tests use a separate data/test_wmata.duckdb and delete it after
min_int parsing on edge cases Helper _parse_min(value) is unit-tested across all known WMATA states

Out-of-Scope (Phase 4 territory)

  • On-time performance per line / station / hour
  • Headway distribution by time-of-day
  • Alert duration analysis
  • Equity overlay (census + stations)
  • GTFS-RT integration

These will all build on the schema landed here.

Implementation Order

  1. Add duckdb to pyproject.toml, run uv sync
  2. Write wmata/storage.py (schema + helpers)
  3. Write wmata/capture.py (one_capture, capture_loop)
  4. Write scripts/capture.py (CLI)
  5. Add tests H1–H5 to tests/run_tests.py
  6. Run full test suite — expect 40/40
  7. Run capture once against live API — verify rows land
  8. Add History tab to app/dashboard.py
  9. Smoke-test the dashboard with the captured DB
  10. Update test_results, action_log, lessons_learned, README