AHJ Jurisdiction Backfill (2026-08-10)
Filled the jurisdiction gap behind the firm’s served-AHJ count: 4,487 Supabase projects with Unknown/state-only jurisdiction and 5,296 PMS-archive records with empty jurisdiction were derived from their site addresses.
Method
- Cross-fill (2,360 records) — dual-entry era means most jobs exist in both systems; a normalized permit/address match against a sibling record with a usable jurisdiction inherits its canonical AHJ. Conflicting matches fell through to geocoding. Cross-fill vs geocode agreement in the dry run: 30/30.
- Geocode (7,418 records, ~2,850 unique addresses) — Google Geocoding API (key in /opt/unlikely-api/.env), per-state AHJ rules: VA independent cities via missing/City-suffixed admin_area_level_2 (Arlington special-cased — Google omits admin2 for e.g. 22213 but Arlington has no incorporated cities), MD county unless a known self-permitting municipality, CT towns, DC. Cache at /home/claude/ahj-backfill/geocode_cache.json.
Where results live
- Supabase: written via
supabase_write_service.update_project(raw_data s2bjkoaa + denormalized column in sync). Script:scripts/backfill_jurisdiction_ahj.py(entirely-api d169e25). 4,485 written; 2 rows remain (unresolvable addresses). - PMS archive (read-only): sidecar
/data/firm-stats/pms_ahj_backfill.json({record_id: canonical AHJ}, 5,293 entries) consumed by/opt/scripts/firm-stats-update.py(commit 67a7560).
Outcome
- Jurisdictions served 62 → 71; live on unlikely.engineer + both unlikely.place editions via the stats patcher.
- 9 new authorities: Queen Anne’s County MD, Kent County MD, Hampshire County WV, Berkeley County WV, City of Manassas Park VA, Frederick County VA, Culpeper County VA, District of Columbia, Allegheny County PA.
- Excluded: 2 Tacoma-WA rows (address has no house number — unreliable), 3 unresolvable addresses. See /home/claude/ahj-backfill/needs_review.json.
Audit / undo
Full before/after in /home/claude/ahj-backfill/supabase_updates.json
(smartsuite_id, old, new) — every old value was Unknown/empty/state-only, so a
revert is a mechanical replay of old.
Connections
- feeds served-count to: firm-stats-update