`FRANKLIN-RUNBOOK.md`

58 lines
# Franklin County OH (39049) — parcel ingest & deploy runbook

Franklin is the ONE documented exception to the "re-run the adapter on the remote"
model. Its rich CAMA attributes live only inside the auditor's 603 MB monthly
**FileGeoDatabase (.gdb)**, which requires **GDAL/ogr2ogr** to read. GDAL is
installed on the local Mac (`/opt/homebrew/bin/ogr2ogr`, 3.13) but **NOT** on the
Kamatera prod box. So Franklin is parsed locally and its rows are pushed to prod.
(DTD verdict B, 2026-07-26; the C-dissent's guardrail = this exception must be
documented + reproducible, not a snowflake.)

## Why not the other sources
- The parcel POLYGON shapefile at `apps.franklincountyauditor.com/GIS_Shapefiles/YYYY/MM/*_Parcel_Polygons.zip`
  is **geometry-only** (`.dbf` = PARCELID, Shape_Length, Shape_Area). No attributes.
- The auditor's `www.franklincountyauditor.com` data pages are **WAF-walled** (403 to curl).
- The `.gdb`'s parcel polygon layer is *also* attribute-stripped; the rich data is in
  the **`TaxParcelSale`** point layer (159,988 sale rows / 122,047 distinct parcels).

## What we ingest
`TaxParcelSale`, deduped to the **latest sale per parcel** → 122,047 parcels with:
site address, owner (latest grantee), use class, res sqft, last sale date+price,
lat/lng (point reprojected to WGS84). **Sale window is a fixed 2020-01..2023-05**
range in the open layer — owner is "latest sale in window", not current-day; this
is recorded in the `parcel_source` note. No assessed value / year-built / beds-baths
in this layer. The ~341k geometry-only parcels are a documented full-coverage TODO.

## Refresh (run on a GDAL machine — local Mac)
```sh
cd ~/Projects/nationalrealestate
# option A: let the adapter download the latest monthly .gdb (~603 MB)
npm run ingest:parcels franklin
# option B: reuse an already-extracted .gdb (skips the 603 MB download)
FRANKLIN_GDB=/path/to/FCA_SDE_Web_Prod_MonthEnd.gdb npm run ingest:parcels franklin
```
The adapter fails loud if `ogr2ogr` is missing, and closes the `ingest_runs` row
`failed` on any error. Idempotent (upsert on `(county_fips, source_id)`).

## Deploy to prod (rows, not code re-run)
Prod has no GDAL, so push the parsed rows straight to remote Postgres:
```sh
# 1. dump just the Franklin rows from local
pg_dump -h /tmp -d usre --data-only --table=parcel \
  --where="county_fips='39049'" 2>/dev/null > /tmp/franklin_parcels.sql   # (see note)
# pg_dump has no --where; use a COPY instead:
psql -h /tmp -d usre -c "\copy (SELECT * FROM parcel WHERE county_fips='39049') TO '/tmp/franklin.csv' CSV"
# 2. ship + load on remote (delete-then-load for a clean rebuild of this county)
scp /tmp/franklin.csv root@45.61.58.125:/tmp/
ssh root@45.61.58.125 "cd /root/public-projects/nationalrealestate
  DB=\$(grep -E '^DATABASE_URL=' .env | cut -d= -f2- | tr -d '\"')
  psql \"\$DB\" -c \"DELETE FROM parcel WHERE county_fips='39049'\"
  psql \"\$DB\" -c \"\\copy parcel FROM '/tmp/franklin.csv' CSV\"
  # re-register the parcel_source row (data rows don't carry the registry)
  npx tsx -e \"import('./src/ingest/parcels/upsert.ts').then(m=>m.registerParcelSource('39049','franklin .gdb','...'))\" "
```
Also rsync the code (`src/ingest/parcels/franklin_oh.ts`, `engine.ts`) so the adapter
exists on prod for reference, even though it can't run there without GDAL.

**Cadence:** monthly, aligned to the auditor's month-end .gdb publish. Cost $0 (public records).