tas_councils/VERSIONS.md

Changelog

All notable changes to the TAS Councils scraping pipeline are recorded here. Entries are grouped by push/session in reverse-chronological order.

---

2026-04-14 — WAF Warmup, Scraper Rewrites & Bug Fixes

lib/http.rb — Full browser fingerprint headers

• Added Upgrade-Insecure-Requests: 1, Sec-Fetch-Dest, Sec-Fetch-Mode, Sec-Fetch-Site, Sec-Fetch-User, sec-ch-ua, sec-ch-ua-mobile, sec-ch-ua-platform to BASE_HEADERS — these are sent by all scrapers using Http.get/Http.request automatically
• Updated curl fallback to pass the same headers for consistency

scrapers/burnie.rb — Two bug fixes

• Fixed redirect loop: next inside Net::HTTP.start block only exits the block, not the while loop; fixed by setting a redirect_to variable inside the block and calling next on the outer loop
• Fixed URI::InvalidURIError on PDF URLs containing non-ASCII characters (e.g. en-dash – in filename): percent-encode non-ASCII chars in href before URI.join

scrapers/kingisland.rb — Complete rewrite

• Previously a stub that immediately exited; now implements homepage warmup + planning page fetch with browser fingerprint headers
• Parses WordPress accordion section (div#accordion-1-c4) for DA notices
• Extracts ref (DA YYYY/NN), address, description, on-notice date, and PDF link from structured paragraph text
• Falls back gracefully with a warning if the fetch fails or returns a Cloudflare challenge

scrapers/latrobe.rb — Complete rewrite

• Previous version targeted PlanBuild portal (incorrect — Latrobe is not on PlanBuild)
• Now scrapes https://www.latrobe.tas.gov.au/services/building-and-planning-services/planningapp directly
• Uses homepage warmup to bypass Cloudflare WAF
• Parses li.generic-list__item h3.generic-list__title a — link text format: L-DA007/2026 ADDRESS - DESCRIPTION (submissions by DATE)
• Note: site blocks Docker/datacenter IPs via Cloudflare JS challenge; scraper exits cleanly when blocked

scrapers/derwentvalley.rb — Complete rewrite

• Previous version found 0 links (CSS selectors didn't match; news listing used lgasa/squiz.cloud redirect chain)
• Now uses homepage warmup + browser headers to pass Cloudflare
• Fetches /home/latest-news?...=Public+Notice; for each news-listing__item link extracts the index_url parameter from the lgasa href, GETs lgasa-web.squiz.cloud/?a=ID (non-following), reads Location header to get the real DV detail page URL
• Fetches each detail page (with DV cookies) and parses the APP No / SITE / PROPOSAL table
• Extracts closing date from "no later than ... DATE" pattern (fixed regex to allow dots in "5.00pm")
• Note: site blocks Docker/datacenter IPs via Cloudflare JS challenge; scraper exits cleanly when blocked

scrapers/georgetown.rb — Fixed field name matching

• "Location" was not matched by /(Address|Property)/i — address was always empty, causing all rows to be skipped
• "Opening Date" was not matched by date received regex
• Added Location and Opening Date to the respective patterns
• Now also extracts applicant ("Applicant Name"), title_reference ("Title reference"), and on_notice_to ("Closing Date") into the upsert

scrapers/kingisland.rb (original stub) → replaced with full implementation (see above)

Docs

• CLAUDE.md: added WAF/Cloudflare handling section, warmup pattern guidance, template scraper recommendations, new common gotchas (non-ASCII PDF URLs, redirect-in-block bug)
• README.md: added WAF and Cloudflare Handling section; updated project structure tree to include scraper_helpers.rb and migrate.rb
• VERSIONS.md: this entry

---

2026-04-13 — Scraper Fixes & Audit

scrapers/planbuild.rb — rewrote to fix crash on first item:

• Added missing require "zlib", require "stringio", require_relative "../lib/log"
• fetch_detail now always returns a Hash (parsed.is_a?(Hash) ? parsed : {}); bare rescue {} replaced with rescue JSON::ParserError, Zlib::Error
• Removed debug puts — replaced with Log.debug/Log.info
• local_document_url now passes nil (not "") when no downloads — prevents COALESCE overwriting an existing URL with empty string
• Per-item rescue so one bad reference skips and logs rather than killing the run

scrapers/southernmidlands.rb — rewrote detail page parser:

• Detail pages use Location: / Proposal: paragraph format, not table rows — old table tr th/td selector found nothing, causing 0 saves
• New parser splits <br>-separated lines per paragraph, extracts Location/Proposal fields, handles multiple DAs per item page
• Removed redundant ALTER TABLE block (columns already in DB.ensure_table!)
• Added explicit require_relative "../lib/http", ../lib/db", "../lib/util"

Missing require_relative "../lib/log" — 20 scrapers fixed:

• break_oday, brighton, burnie, centralcoast, circularhead, clarence, derwentvalley, devonportcity, dorset, flinders_council, glenorchy, huonvalley, kentish, launcestoncity, meandervalley, northernmidlands, southernmidlands, waratah_wynyard, westcoast, westtamar
• Log.warn called in rescue blocks in all of these — without the require, the first error would raise NameError: uninitialized constant Log instead of logging

enrich_after_upsert! variable scope bugs — 4 scrapers fixed:

• flinders_council.rb: council_reference (undefined) → ref; folded separate UPDATE document_url into DB.upsert; removed redundant ALTER TABLE
• huonvalley.rb: council_reference/address (undefined) → r[:council_reference]/r[:address]; folded UPDATE document_url into upsert; removed redundant ALTER TABLE
• kentish.rb: council_reference/address (undefined) → r[:council_reference]/r[:address]; folded extras UPDATE into upsert
• westcoast.rb: address (undefined) → item[:address]; fixed upsert field names (on_notice → on_notice_to, on_notice_raw → on_notice_to_raw); fixed values referencing non-existent item keys; folded extras UPDATE into upsert

Redundant ALTER TABLE blocks removed from circularhead.rb and waratah_wynyard.rb — all columns already created by DB.ensure_table!

---

2026-04-13 — Code Quality Pass 3

Logging

• All 63 bare warn "..." calls across scrapers/*.rb replaced with Log.warn "scraper", "..." — structured logging now consistent throughout; stderr output is now filtered by LOG_LEVEL.

DB.upsert dynamic rewrite (lib/db.rb)

• Removed hardcoded 22-column array — upsert now derives columns from row.keys, so scrapers that pass scraper-specific columns (e.g. advertised_date, legal_description) are no longer silently ignored.
• Added SAFE_COLUMN_RE = /\A[a-z][a-z0-9_]*\z/ — each key is validated before interpolation into SQL; unsafe names raise ArgumentError rather than silently passing.
• Extracted write-once/merge semantics into UPSERT_ON_DUP constant (date_received, date_received_raw, document_url, local_document_url) — easier to audit and extend.
• Non-existent columns now raise Mysql2::Error (caught by scraper rescue) instead of silently being dropped, surfacing schema mismatches early.

---

2026-04-13 — Code Quality Pass 2

Security

• lib/http.rb curl fallback: replaced shell-interpolated backtick call with Open3.capture2 array form — eliminates shell injection risk from URL-derived ref/uri values.
• web/index.php: added validate_table_name() helper (enforces /\Ada_[a-z0-9_]+\z/) applied before every backtick-quoted table name interpolation (tableHasColumn, $stageT stages fetch, UNION SELECT builder).

Schema consolidation

• Removed Geocode.ensure_da_columns! from lib/geocode.rb — redundant, covered by DB.ensure_table! (new tables) and migration v1 (existing tables). Removed its call from tools/backfill_geocode.rb.
• Removed ensure_extra_columns! from lib/enrich.rb and all 10 scraper call-sites — same reasoning; was also using wrong column types (DOUBLE/VARCHAR(50)) vs canonical schema (DECIMAL(10,7)/TEXT).

Error handling

• 66 bare rescue => e replaced with rescue StandardError => e across all scrapers, lib, and tools — prevents accidental swallowing of SystemExit/SignalException.
• lib/enrich.rb: two warn calls replaced with Log.warn for structured logging; stale file header comment removed.

Removed

• Deleted scrapers/enrich.rb — stale duplicate with wrong require_relative paths, old broken COALESCE(NULLIF(?, '')) query, no main batch loop. Was picked up by run_all.sh's glob and failing every full run with LoadError.

Docs

• CLAUDE.md: corrected scraper pattern (removed ensure_extra_columns!(TABLE) step), updated geocode-backfill command, corrected schema-change guidance.
• README.md: removed stale tools/enrich.rb references; corrected enrichment/backfill examples and tools table; added link to VERSIONS.md.
• VERSIONS.md: created — changelog covering all changes from initial upload.

---

2026-04-13 — Code Quality & Bug Fixes

Bug fixes

• Fixed Mysql2::Error Unknown column '''' in 'SET' — MariaDB 10.11's prepared-statement parser mishandles string literals ('') inside NULLIF/IF expressions in SET clauses. Replaced COALESCE(NULLIF(?, ''), col) with COALESCE(?, col) passing nil when the value is empty (lib/enrich.rb).
• Fixed private method 'da_tables' called error in lib/migrate.rb — migration lambdas call Migrate.da_tables with an explicit receiver, which counts as a public call. Removed da_tables from private_class_method declaration.
• Fixed unmatched end / dangling rescue syntax error in scrapers/launcestoncity.rb introduced during a prior cleanup pass.
• Eliminated duplicate "Docs page had no usable links" warning (fired twice per DA) in scrapers/launcestoncity.rb.

Removed

• Deleted scrapers/enrich.rb — stale copy of lib/enrich.rb with wrong require_relative paths, old broken COALESCE(NULLIF(?, '')) query, and no main batch loop. Was being picked up by run_all.sh's scrapers/*.rb glob and failing every full run with a LoadError.

Docs

• Updated CLAUDE.md: corrected geocode-backfill command to use tools/backfill_geocode.rb, updated schema-change guidance to point to lib/migrate.rb.
• Updated README.md: removed stale tools/enrich.rb references, corrected enrichment/backfill examples, updated tools table.

---

2026-04-13 — Structure Updates (5f60868)

• General structural cleanup across scrapers.

---

2026-04-13 — Launceston City Scraper (3fc874c → bc3490f)

• Implemented scrapers/launcestoncity.rb for the Launceston eProperty portal (ASP.NET session-based site).
• Session cookie management (merge_set_cookie!) to maintain ASP.NET_SessionId across requests.
• Document listing via docget.asp with multi-variant URL probing (path-case and route-param variants).
• probe_common_docs fallback: constructs known PDF filenames from DA number when the document list page returns no links.
• PDF download to DOWNLOAD_DIR/launceston/<da_ref>/ when DOWNLOAD_ATTACHMENTS=1.
• Enriches each DA from the details page (applicant, received date, advertised date, legal description).

---

2026-04-13 — Structured Logging (c03bfae)

• Added lib/log.rb — Log.debug, Log.info, Log.warn, Log.error with LOG_LEVEL env filtering.
• Replaced puts/warn calls across lib/ with Log.* calls.
• Added LOG_LEVEL env var to docker-compose.yml (default: info).

---

2026-04-13 — Schema Migrations (0e4e035)

• Added lib/migrate.rb — lightweight sequential migration runner backed by a schema_migrations table.
• Migration v1: adds enrichment and geocode columns to all existing da_* tables.
• Migration v2: creates geo_cache table.
• run_all.sh now runs ruby /app/lib/migrate.rb before scrapers.

---

2026-04-13 — SQL Injection Hardening (f3c06ab)

• Added DB.validate_table_name! — enforces da_[a-z0-9_]+ pattern on every table name before interpolation into SQL.
• Applied DB.client.escape() on all remaining identifier interpolations.
• Applied validate_table_name! in lib/geocode.rb and lib/enrich.rb.

---

2026-04-12 — Initial Upload (ab11792)

• 28 council scrapers covering all Tasmanian councils.
• lib/db.rb — DB client, ensure_table!, upsert with write-once semantics.
• lib/http.rb — HTTP client with retries, cookie jar, 403/406 warmup, curl fallback.
• lib/geocode.rb — Google Maps geocoding with SHA1 cache in geo_cache.
• lib/enrich.rb — enrich_after_upsert! for per-row geocoding and property lookup.
• lib/util.rb — parse_aus_date, council/table name mappings.
• web/index.php — PHP search portal with dynamic UNION across all da_* tables.
• tools/backfill_geocode.rb — batch geocode backfill.
• tools/import_sqlites.rb — import from legacy SQLite exports.
• Docker Compose stack: MariaDB 10.11, Ruby 3.2 scraper, PHP/Apache web, Adminer.
• run_all.sh — discovers and runs scrapers with ONLY/SKIP filtering.
• entrypoint.sh — Docker entry with optional loop via SCRAPE_EVERY_MINUTES.