Prompting Airports & Weather Overlay · SKYWATCH: The AI-Native Build

Objectives

By the end of this chapter, you should be able to:

Prompt an AI assistant to seed the airports table (already modeled back in Module 1) from a real open dataset, and confirm the seed script is idempotent instead of trusting the summary that says so
Direct a viewport-scoped airport query and catch an AI assistant that “forgets” the bounding box under load
Prompt a live METAR weather badge behind an actual TTL cache, not a fetch-on-every-render that happens to look cached
Verify a persisted show/hide toggle survives the exact hydrate/persist race an AI assistant has no reason to know about unless you tell it

💡 Why this matters: Aircraft markers alone don’t answer “where is this thing headed” or “why is it circling right there.” An AI assistant asked for “airports on the map” will give you markers. It takes a more specific prompt, and a more specific review pass, to get markers that only load what’s in view, weather that’s actually current, and a toggle that survives a reload.

The Table’s Been Sitting There Since Module 1

Back in Module 1 you prompted a home_location singleton and a watch_regions table with a partial unique index, both landed with real database-level invariants. The airports table went in at the same time, unique-indexed on ident, indexed on (lat, lon) and type, and it’s been empty ever since. Nothing’s read from it, nothing’s written to it. That’s exactly the setup this lesson pays off: the schema already promises a fast viewport lookup and a safe re-seed, so the prompt below can lean on both instead of asking the AI to re-derive them from scratch.

The other thing worth setting up before you prompt anything: airport reference data is large (tens of thousands of rows) and essentially static between server restarts. That’s a different shape of problem than “watch region CRUD,” and if you don’t say so explicitly, an AI assistant has no way to know you want a one-time manual script instead of something that runs automatically on every boot next to ensureDefaultWatchRegion.

The Prompt

code

I'm adding an airports reference layer to SKYWATCH. The `airports` table already exists (Module 1's schema), unique-indexed on ident, indexed on (lat, lon) and type. 1. A one-time seed script, apps/server/src/db/seedAirports.ts, that populates the table from OurAirports' open CSV dataset (https://davidmegginson.github.io/ourairports-data/airports.csv). Keep only large/medium/small airports, heliports, and seaplane bases that have an ICAO or IATA code -- skip small airports with neither, they're almost all unpaved private strips and would flood the map. Truncate fields to the column widths from the schema. This needs to be safely re-runnable: clear the table first, batch-insert with onConflictDoNothing against the unique ident index, don't let one bad row abort the whole batch. This is a manual, deliberately-triggered script (add a db:seed:airports npm script) -- do NOT wire it into the automatic startup sequence next to ensureDefaultWatchRegion. Refetching and reparsing a multi-megabyte CSV on every server restart would be wasteful for data that doesn't change between restarts. 2. A repo function and a GET /api/airports route that takes a bounding box (minLat/maxLat/minLon/maxLon) and an optional comma-separated list of airport types, and returns only airports inside that box. This has to actually filter in the SQL query, not fetch everything and filter in JS -- the table has tens of thousands of rows. 3. A frontend AirportLayer component that turns the current Leaflet map viewport into that bounding box, debounced on moveend the same way our existing ViewportFeed component debounces its own pan/zoom fetch (400ms). Skip the fetch entirely below a "zoomed out to a continent" zoom level -- don't even hit the API at that scale. Render airports as small diamond markers. 4. A live METAR weather badge for large airports only, past a closer zoom threshold than the airport diamonds themselves. Weather changes on the order of an hour, so this needs a real TTL cache on the server (in-memory is fine, don't put this in Postgres) fronting a call to aviationweather.gov's METAR API, plus a client-side cache too so switching zoom levels doesn't re-fetch airports already fetched once. 5. A persisted show/hide airports toggle in the header, using the same hydrate-on-mount/write-on-change localStorage pattern our theme toggle already uses -- read the stored value once on mount, write on every change after that, defaulting to shown. Show me every file.

What It Built

Shared types first, re-exported from packages/shared/src/index.ts the same way every shared file has been since Module 2:

// packages/shared/src/airport.ts
export type AirportType = "large_airport" | "medium_airport" | "small_airport" | "heliport" | "seaplane_base" | "closed";

export interface Airport {
  id: number;
  ident: string;
  type: AirportType;
  name: string;
  lat: number;
  lon: number;
  elevationFt: number | null;
  icaoCode: string | null;
  iataCode: string | null;
  municipality: string | null;
  countryCode: string | null;
}

// packages/shared/src/geo.ts (add to the existing file)

/** A simple lat/lon bounding box, used for airport-by-viewport queries. */
export interface BoundingBox {
  minLat: number;
  maxLat: number;
  minLon: number;
  maxLon: number;
}

The seed script:

// apps/server/src/db/seedAirports.ts
import { parse } from "csv-parse/sync";
import { db, pool } from "./client.js";
import { airports } from "./schema.js";
import type { AirportType } from "@skywatch/shared";

const SOURCE_URL = "https://davidmegginson.github.io/ourairports-data/airports.csv";

const KEEP_TYPES = new Set<AirportType>([
  "large_airport",
  "medium_airport",
  "small_airport",
  "heliport",
  "seaplane_base",
]);

interface OurAirportsRow {
  id: string;
  ident: string;
  type: string;
  name: string;
  latitude_deg: string;
  longitude_deg: string;
  elevation_ft: string;
  municipality: string;
  iso_country: string;
  gps_code: string;
  iata_code: string;
}

async function main() {
  console.log(`Fetching airport data from ${SOURCE_URL} ...`);
  const resp = await fetch(SOURCE_URL);
  if (!resp.ok) {
    throw new Error(`Failed to fetch airport data: HTTP ${resp.status}`);
  }
  const csvText = await resp.text();
  const rows: OurAirportsRow[] = parse(csvText, { columns: true, skip_empty_lines: true });

  const filtered = rows.filter((r) => {
    if (!KEEP_TYPES.has(r.type as AirportType)) return false;
    if (r.type === "small_airport" && !r.gps_code && !r.iata_code) return false;
    const lat = Number(r.latitude_deg);
    const lon = Number(r.longitude_deg);
    return Number.isFinite(lat) && Number.isFinite(lon);
  });

  const truncate = (s: string | null, max: number) => (s ? s.slice(0, max) : null);
  const values = filtered.map((r) => ({
    ident: truncate(r.ident, 10)!,
    type: r.type,
    name: truncate(r.name, 160)!,
    lat: Number(r.latitude_deg),
    lon: Number(r.longitude_deg),
    elevationFt: r.elevation_ft ? Number(r.elevation_ft) : null,
    icaoCode: truncate(r.gps_code || null, 4),
    iataCode: truncate(r.iata_code || null, 3),
    municipality: truncate(r.municipality || null, 120),
    countryCode: truncate(r.iso_country || null, 2),
  }));

  const byIdent = new Map(values.map((v) => [v.ident, v]));
  const deduped = [...byIdent.values()];

  console.log("Clearing existing airports table...");
  await db.delete(airports);

  const BATCH_SIZE = 1000;
  for (let i = 0; i < deduped.length; i += BATCH_SIZE) {
    const batch = deduped.slice(i, i + BATCH_SIZE);
    await db.insert(airports).values(batch).onConflictDoNothing();
    console.log(`Inserted ${Math.min(i + BATCH_SIZE, deduped.length)} / ${deduped.length}`);
  }

  console.log("Airport seed complete.");
  await pool.end();
}

main().catch((err) => {
  console.error("Airport seed failed:", err);
  process.exit(1);
});

Add "db:seed:airports": "tsx src/db/seedAirports.ts" to apps/server/package.json and run it once with npm run db:seed:airports. Notice what this deliberately is not: it is not wired into startPoller() next to ensureDefaultWatchRegion(). That function is a cheap existence check on every boot; this script fetches and reparses a multi-megabyte CSV and reinserts tens of thousands of rows, which has no business running automatically every time the server restarts.

The repo and route:

// apps/server/src/db/repos/airportsRepo.ts
import { and, gte, lte, inArray } from "drizzle-orm";
import type { Airport, AirportType, BoundingBox } from "@skywatch/shared";
import { db } from "../client.js";
import { airports } from "../schema.js";

function toApi(row: typeof airports.$inferSelect): Airport {
  return {
    id: row.id,
    ident: row.ident,
    type: row.type as AirportType,
    name: row.name,
    lat: row.lat,
    lon: row.lon,
    elevationFt: row.elevationFt,
    icaoCode: row.icaoCode,
    iataCode: row.iataCode,
    municipality: row.municipality,
    countryCode: row.countryCode,
  };
}

export async function queryAirportsInBounds(
  bbox: BoundingBox,
  types: AirportType[]
): Promise<Airport[]> {
  const rows = await db
    .select()
    .from(airports)
    .where(
      and(
        gte(airports.lat, bbox.minLat),
        lte(airports.lat, bbox.maxLat),
        gte(airports.lon, bbox.minLon),
        lte(airports.lon, bbox.maxLon),
        types.length > 0 ? inArray(airports.type, types) : undefined
      )
    )
    .limit(2000);
  return rows.map(toApi);
}

// apps/server/src/routes/airports.ts
import type { FastifyInstance } from "fastify";
import { z } from "zod";
import type { AirportType } from "@skywatch/shared";
import { queryAirportsInBounds } from "../db/repos/airportsRepo.js";

const VALID_TYPES: AirportType[] = [
  "large_airport",
  "medium_airport",
  "small_airport",
  "heliport",
  "seaplane_base",
  "closed",
];

const querySchema = z
  .object({
    minLat: z.coerce.number().min(-90).max(90),
    maxLat: z.coerce.number().min(-90).max(90),
    minLon: z.coerce.number().min(-180).max(180),
    maxLon: z.coerce.number().min(-180).max(180),
    types: z
      .string()
      .optional()
      .transform((s) => (s ? s.split(",").filter((t): t is AirportType => VALID_TYPES.includes(t as AirportType)) : [])),
  })
  .refine((v) => v.maxLat >= v.minLat && v.maxLon >= v.minLon, { message: "invalid bounding box" });

export function registerAirportRoutes(app: FastifyInstance): void {
  app.get("/airports", async (req, reply) => {
    const parsed = querySchema.safeParse(req.query);
    if (!parsed.success) {
      return reply.status(400).send({ error: "invalid bounding box", issues: parsed.error.issues });
    }
    const { minLat, maxLat, minLon, maxLon, types } = parsed.data;
    const airports = await queryAirportsInBounds({ minLat, maxLat, minLon, maxLon }, types);
    return { airports };
  });
}

The frontend layer:

tsx

// apps/web/src/components/AirportLayer.tsx
"use client";

import { useCallback, useEffect, useRef, useState } from "react";
import { Marker, Tooltip, useMap, useMapEvents } from "react-leaflet";
import type { Airport } from "@skywatch/shared";
import { apiUrl } from "@/lib/serverUrl";
import { makeAirportIcon } from "@/lib/airportIcon";
import { useAppStore } from "@/store/useAppStore";
import { AirportWeatherBadge } from "./AirportWeatherBadge";

const DEBOUNCE_MS = 400;
const SMALL_AIRPORT_MIN_ZOOM = 8;
const MIN_ZOOM_TO_SHOW = 5;
const METAR_MIN_ZOOM = 7;

export function AirportLayer() {
  const map = useMap();
  const showAirports = useAppStore((s) => s.showAirports);
  const [airports, setAirports] = useState<Airport[]>([]);
  const [zoom, setZoom] = useState(() => map.getZoom());
  const debounceTimer = useRef<ReturnType<typeof setTimeout> | null>(null);

  const fetchForCurrentView = useCallback(() => {
    if (!showAirports) return;
    const zoom = map.getZoom();
    setZoom(zoom);
    if (zoom < MIN_ZOOM_TO_SHOW) {
      setAirports([]);
      return;
    }
    const bounds = map.getBounds();
    const types = zoom >= SMALL_AIRPORT_MIN_ZOOM ? "large_airport,medium_airport,small_airport" : "large_airport,medium_airport";
    const params = new URLSearchParams({
      minLat: String(bounds.getSouth()),
      maxLat: String(bounds.getNorth()),
      minLon: String(bounds.getWest()),
      maxLon: String(bounds.getEast()),
      types,
    });
    fetch(apiUrl(`/api/airports?${params.toString()}`))
      .then((r) => r.json())
      .then((data: { airports: Airport[] }) => setAirports(data.airports ?? []))
      .catch((err) => console.error("[airports] fetch failed:", err));
  }, [map, showAirports]);

  useMapEvents({
    moveend: () => {
      if (debounceTimer.current) clearTimeout(debounceTimer.current);
      debounceTimer.current = setTimeout(fetchForCurrentView, DEBOUNCE_MS);
    },
  });

  useEffect(() => {
    fetchForCurrentView();
    return () => {
      if (debounceTimer.current) clearTimeout(debounceTimer.current);
    };
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, []);

  useEffect(() => {
    if (showAirports) fetchForCurrentView();
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [showAirports]);

  if (!showAirports) return null;

  return (
    <>
      {airports.map((a) => (
        <Marker key={a.id} position={[a.lat, a.lon]} icon={makeAirportIcon(a.type)}>
          <Tooltip direction="top" offset={[0, -4]} opacity={0.95}>
            <span className="font-mono text-[11px]">
              {a.icaoCode ?? a.ident}
              {a.iataCode ? ` / ${a.iataCode}` : ""} · {a.name}
            </span>
          </Tooltip>
        </Marker>
      ))}
      {zoom >= METAR_MIN_ZOOM &&
        airports
          .filter((a) => a.type === "large_airport" && a.icaoCode)
          .map((a) => <AirportWeatherBadge key={`metar-${a.id}`} airport={a} />)}
    </>
  );
}

The weather proxy and its server-side cache:

// apps/server/src/lib/weather.ts
import type { MetarInfo } from "@skywatch/shared";
import { fetchJson } from "./http.js";

const BASE = "https://aviationweather.gov/api/data/metar";

interface AwcMetar {
  icaoId?: string;
  obsTime?: number;
  temp?: number;
  wdir?: number | string;
  wspd?: number;
  visib?: number | string;
  fltCat?: string;
  rawOb?: string;
}

export async function fetchMetar(icao: string): Promise<MetarInfo | null> {
  const data = await fetchJson<AwcMetar[]>(`${BASE}?ids=${encodeURIComponent(icao)}&format=json`);
  const m = data[0];
  if (!m || !m.rawOb) return null;

  return {
    icao: m.icaoId ?? icao,
    raw: m.rawOb,
    observedAt: m.obsTime ? new Date(m.obsTime * 1000).toISOString() : null,
    tempC: typeof m.temp === "number" ? m.temp : null,
    windDirDeg: typeof m.wdir === "number" ? m.wdir : null,
    windSpeedKt: typeof m.wspd === "number" ? m.wspd : null,
    visibilityMi: typeof m.visib === "number" ? m.visib : typeof m.visib === "string" ? Number(m.visib) || null : null,
    flightCategory: m.fltCat ?? null,
  };
}

// apps/server/src/routes/weather.ts
import type { FastifyInstance } from "fastify";
import { z } from "zod";
import type { MetarInfo } from "@skywatch/shared";
import { fetchMetar } from "../lib/weather.js";
import { MemoryCache } from "../lib/memoryCache.js";

const paramsSchema = z.object({ icao: z.string().trim().length(4) });

const cache = new MemoryCache<MetarInfo | null>(10 * 60 * 1000); // METAR updates roughly hourly; 10min is plenty fresh

export function registerWeatherRoutes(app: FastifyInstance): void {
  app.get("/weather/:icao", async (req, reply) => {
    const parsed = paramsSchema.safeParse(req.params);
    if (!parsed.success) return reply.status(400).send({ error: "invalid ICAO code" });
    const icao = parsed.data.icao.toUpperCase();

    const cached = cache.get(icao);
    if (cached !== undefined) {
      if (!cached) return reply.status(404).send({ error: "not found" });
      return cached;
    }

    try {
      const metar = await fetchMetar(icao);
      cache.set(icao, metar);
      if (!metar) return reply.status(404).send({ error: "not found" });
      return metar;
    } catch (err) {
      const message = err instanceof Error ? err.message : String(err);
      return reply.status(502).send({ error: message });
    }
  });
}

The client-side hook, with its own module-level cache on top of the server’s:

// apps/web/src/hooks/useAirportMetar.ts
"use client";

import { useEffect, useState } from "react";
import type { MetarInfo } from "@skywatch/shared";
import { apiUrl } from "@/lib/serverUrl";

const cache = new Map<string, MetarInfo | null>();

export function useAirportMetar(icao: string | null): MetarInfo | null {
  const [info, setInfo] = useState<MetarInfo | null>(icao ? (cache.get(icao) ?? null) : null);

  useEffect(() => {
    if (!icao) {
      setInfo(null);
      return;
    }
    if (cache.has(icao)) {
      setInfo(cache.get(icao) ?? null);
      return;
    }

    let cancelled = false;
    fetch(apiUrl(`/api/weather/${encodeURIComponent(icao)}`))
      .then((r) => (r.ok ? (r.json() as Promise<MetarInfo>) : null))
      .then((data) => {
        cache.set(icao, data);
        if (!cancelled) setInfo(data);
      })
      .catch(() => {
        if (!cancelled) setInfo(null);
      });
    return () => {
      cancelled = true;
    };
  }, [icao]);

  return info;
}

The persisted toggle, store additions and the hook that keeps it in sync with localStorage:

// apps/web/src/store/useAppStore.ts (additions)
interface AppState {
  // ...existing fields...
  showAirports: boolean;
  setShowAirports: (show: boolean) => void;
  toggleShowAirports: () => void;
}

export const useAppStore = create<AppState>((set) => ({
  // ...existing fields...
  showAirports: true,
  setShowAirports: (show) => set({ showAirports: show }),
  toggleShowAirports: () => set((state) => ({ showAirports: !state.showAirports })),
}));

// apps/web/src/hooks/useAirportsVisibility.ts
"use client";

import { useEffect, useRef } from "react";
import { useAppStore } from "@/store/useAppStore";

const STORAGE_KEY = "skywatch:show-airports";

export function useAirportsVisibility(): void {
  const showAirports = useAppStore((s) => s.showAirports);
  const setShowAirports = useAppStore((s) => s.setShowAirports);
  const skippedFirstPersist = useRef(false);

  useEffect(() => {
    try {
      const stored = window.localStorage.getItem(STORAGE_KEY);
      if (stored != null) setShowAirports(stored === "true");
    } catch {
      // localStorage unavailable (private mode, etc.) -- just keep the default.
    }
  }, [setShowAirports]);

  useEffect(() => {
    if (!skippedFirstPersist.current) {
      skippedFirstPersist.current = true;
      return;
    }
    try {
      window.localStorage.setItem(STORAGE_KEY, String(showAirports));
    } catch {
      // ignore
    }
  }, [showAirports]);
}

Review This

Does the airport query actually filter in SQL, or does it fetch everything and filter client-side? An AI assistant that’s slightly less careful about the repo layer will sometimes write db.select().from(airports) with no where at all, then filter the bounding box in the route handler or even in AirportLayer itself, because “get the airports, then narrow them down” is a completely natural way to think about the problem if you’re not thinking about row count. It compiles, it renders the right markers, and in local dev with a partial seed it might even feel fast. It falls over the moment the real seed script has run and the table holds tens of thousands of rows, because now every pan sends the whole table over the wire and filters it in JavaScript on every request. Check queryAirportsInBounds for gte/lte clauses actually inside the .where(...) passed to Drizzle, not applied after .select() resolves. If they’re missing: “queryAirportsInBounds needs to filter the bounding box in the SQL WHERE clause, not fetch all rows and filter in JS – the table has tens of thousands of rows.”

Is the METAR cache actually a TTL cache, or does it just look like one? It’s easy for an AI assistant to write something that reads and writes a Map, calls it a cache, and never actually expires anything – which means the very first METAR fetched for KJFK is the one your app shows forever, hours after conditions have changed, with nothing anywhere signaling that it’s stale. Check that MemoryCache’s get actually compares a stored timestamp against the constructor’s duration and returns undefined (a real cache miss) once expired, not just undefined for keys that were never set. If the AI wrote a cache with no expiry logic at all: “the weather cache needs to actually expire entries after 10 minutes, not just store them forever – check that get() compares against a stored timestamp, not just key presence.”

Does the show/hide toggle survive the exact hydrate/persist race the theme toggle already solved? This is the kind of thing an AI assistant gets right by accident about half the time, because both the buggy version and the correct version look identical until you actually test a reload. If useAirportsVisibility’s two effects fire in the same commit with no guard, the persist effect’s first run overwrites whatever was in localStorage with the pre-hydration default before the hydrated value ever lands. Check for a skippedFirstPersist ref (or equivalent) that skips exactly the first persist run. If it’s missing: “useAirportsVisibility’s hydrate and persist effects race on first mount – add a ref that skips the very first run of the persist effect, same pattern as the theme toggle’s hydrateFromDom.”

Try It

Run npm run db:seed:airports once against your local database, then confirm with psql $DATABASE_URL -c "SELECT count(*) FROM airports;" that it’s actually populated, not just that the script exited zero.
Restart the server and frontend. Zoom out past a continent-scale view and confirm no airport diamonds render or fetch (watch the network tab); zoom back toward level 5-7 and confirm large/medium diamonds appear, with more once you cross zoom 8.
Zoom into a large airport past zoom 7 and confirm a colored METAR badge shows up, with the raw METAR string in its tooltip on hover.
Watch the network tab while panning around at a busy zoom level. Confirm each request’s response is scoped to a reasonable number of airports, not the full table, and that the query params sent match your current viewport.
Click the airports toggle, then click it again without panning, and confirm markers reappear immediately with no fresh pan/zoom required. Reload the page after hiding airports and confirm they load back in hidden.

Recap

The airports table was modeled all the way back in Module 1; this lesson is the first to prompt anything into it or read anything out of it.
The seed script is a deliberate, manually-triggered command, not part of the automatic startup sequence – say so explicitly in the prompt, or an AI assistant has no reason to guess that distinction on its own.
The bounding-box filter has to happen in SQL, not after the fact in application code – this is the single most important thing to check by eye, because the wrong version still renders correctly in a small local dataset.
A cache that stores values but never expires them isn’t a cache, it’s a permanent copy – verify the TTL logic actually runs, don’t just trust that a MemoryCache class exists.

Next lesson: squawk alerts and the alert feed.