trails/openspec/changes/archive/2026-07-13-poi-index/tasks.md

1. Category selectors (single source of truth)

• 1.1 Refactor packages/map-core/src/poi.ts: replace Overpass QL query strings with structured selectors: Array<{key, value}> per category; update poi.test.ts
• 1.2 Add a helper that renders the selectors as an osmium tag-filter expression list (used by the pipeline; unit-tested against the nine categories)

2. Schema

• 2.1 Add planner.pois to packages/db (osm_type, osm_id, category, name, geom Point 4326, tags jsonb, imported_at; PK (osm_type, osm_id, category); GiST on geom, btree on category) + migration
• 2.2 pnpm db:push locally and verify bbox+category query plans use the indexes (verified: serving query drives off pois_geom_idx for the bbox; pois_category_idx used for category filter; PK created as pois_pkey)

3. Extract pipeline (BRouter host)

• 3.1 Create infrastructure/brouter-host/poi-extract/: script that downloads the configured PBF (POI_PBF_URL, planet by default), runs osmium tags-filter + osmium export (centroids) into gzipped NDJSON, writes a manifest (sha256 + timestamp + row count), and cleans up working files (NDJSON is {osm_type, osm_id, name, lat, lon, tags}; the importer derives categories from tags via map-core, so selector logic isn't duplicated on the Node-free host)
• 3.2 Publish artifact + manifest via the existing Caddy sidecar at a vSwitch-only address (/poi/* on :17777, same X-BRouter-Auth gate) (public-unreachability follows from the existing vSwitch-only bind + UFW; verify on the host)
• 3.3 Add a monthly systemd timer under the trails user; document disk/bandwidth footprint in infrastructure/brouter-host/poi-extract/README.md
• 3.4 Dry-run on a small Geofabrik extract first; then a full planet run — record row counts per category and artifact size (Malta smoke 2947 rows; Germany 733,986 → 41 MB; planet 8,623,453 rows → 468 MB gz, filtered PBF 619 MB, source planet.osm.org)

4. Import job (flagship)

• 4.1 Create the import script: fetch manifest + artifact over the vSwitch, verify checksum, COPY into planner.pois_staging, build indexes, atomic rename swap in one transaction
• 4.2 Implement the 70% row-count guard with a --bootstrap override; abort loudly (log + metric) when tripped
• 4.3 Schedule via systemd timer (poi-import.timer, offset a day after the extract); wire into cd-infra.yml deploy sources (infrastructure/scripts added to the SCP list)
• 4.4 First production import with --bootstrap (germany), then planet re-import (guard germany→planet passed): live planner.pois = 8,434,518 rows across all nine categories; spot-checked Berlin/Fehmarn/Tokyo/NYC viewports return expected POIs. (Two import-script bugs found + fixed in prod: \i selectors path inside the postgres container → stream via stdin (#563); multi-selector same-category PK collision → DISTINCT ON (#564).)

5. Serving route

• 5.1 Create apps/planner/app/routes/api.pois.ts (GET bbox + categories): same-origin + session checks, 120/IP/min rate limit (no DB query on rejection), 100-result cap, short Cache-Control; register in apps/planner/app/routes.ts
• 5.2 Unit tests: happy path, invalid bbox/categories 400, 429 path, empty-table graceful response

6. Client switch

• 6.1 Rewrite apps/planner/app/lib/overpass.ts as the /api/pois client (keep Poi shape, quantizeBbox, error mapping; drop QL building); rename file to pois.ts and update imports (use-pois, use-nearby-pois, snap-to-poi)
• 6.2 Update client tests; verify the existing rate-limit banner and unavailable message fire on 429/5xx from /api/pois
• 6.3 Delete apps/planner/app/routes/api.overpass.ts and its tests; remove the route registration and the private.coffee configuration (OVERPASS_URLS kept for the Journal surface backfill; removed from the planner service in both compose files)

7. Observability

• 7.1 Add poi_index_rows{category}, poi_index_age_seconds, import outcome, and poi_api_requests_total{status} metrics; remove overpass_upstream_* metrics from metrics.server.ts (planner exposes rows/age via scrape-time DB collect + poi_api_requests_total; import outcome is emitted by the import job via node_exporter textfile — see Task 4)
• 7.2 Update the Grafana planner dashboard: replace Overpass upstream panels with index freshness + serving panels; add the stale-index alert (~6 weeks) (poi-index-stale in alerts.yml)

8. Docs & cleanup

• 8.1 Update docs/architecture.md (POI data flow) and the privacy documentation (Overpass now Journal-surface-backfill-only; Planner POIs served same-origin)
• 8.2 Document the self-hoster story: optional pipeline, regional extract URL, graceful behavior with an empty index (poi-extract README)
• 8.3 Update docs/ideas/self-host-overpass/README.md: superseded by poi-index, with the revive-criteria note kept for the QL-compatibility case (+ roadmap pointer)

9. Verification

• 9.1 E2E: POI overlay flow (enable category → markers appear) — e2e/planner-overlays.test.ts + nearby-snap in planner-routing.test.ts now mock /api/pois (network-mock is the house pattern; a DB-seeded variant is possible but the route's SQL path is covered by api.pois.test.ts)
• 9.2 Run pnpm typecheck && pnpm lint && pnpm test && pnpm test:e2e (typecheck + lint + unit green; full planner e2e project green incl. all three POI tests. Journal e2e specs need E2E=true + the trails_e2e scratch DB — unrelated to this planner-only change; CI runs them with that harness.)
• 9.3 Post-cutover production check: verified via the cmux browser on a live session (Brandenburg) — POI markers + clusters render incl. camping tents; /api/pois returns data for busy (Berlin/Tokyo/NYC) and rural (Fehmarn) viewports; all nine categories populated. (Remaining manual op step: enable the two monthly systemd timers — poi-extract.timer on the BRouter host, poi-import.timer on the flagship — a persistence change to apply by hand; see the poi-extract + scripts READMEs.)