NearBuild: turning one council PDF into a searchable map

In August I want to know what people are planning to build near my house. Although this information is public in Auckland, but it is not easy to use. Auckland Council publish a PDF called "Recent resource consent applications":

• about 5 MB and more than 300 pages
• around 5,800 applications from the last six months
• republished roughly once a week
• no API

If you want to know what is happening on your street, you have to scroll the PDF and search by eye. So I built NearBuild: take this PDF, turn every row into a record, and let people search by address and see the results on a map. The hard part is not the idea. The hard part is keeping it correct every hour, without lying to the user when something goes wrong.

The architecture

I did not use a queue. A cron line plus a file lock is enough for one job per hour, and it is much more easier to debug at 2am.

Step 1: fetch the PDF like a browser

The first surprise is the council server. If you request the file without browser-like User-Agent, it returns HTTP 406. Even a plain HEAD request gets 406, so I cannot use HEAD as a health check. The fetch step does four things:

1. Send a normal GET with a descriptive user agent that still looks like a browser.
2. Use a 120-second timeout that also covers reading the body.
3. Check that the first bytes are %PDF-. If one day the council returns an HTML error page with status 200, I don't want to parse that as data.
4. Save the Last-Modified header, which tells me when the council actually published a new version, not only when I fetched it.

Step 2: parse a table that is not really a table

A PDF don't have rows and columns. It only has text items with x and y positions. I use pdfjs-dist to extract these positioned items, then I rebuild the table myself.

Rows. Every row has an application number, so it can be an anchor. Each regex match starts a new row, and the row's vertical band goes from the midpoint with the previous anchor to the midpoint with the next anchor.

// two to five capital letters, eight digits, optional suffix
const APPLICATION_NUMBER = /^[A-Z]{2,5}\d{8}(?:-[A-Z])?$/;

Columns. I use fixed x thresholds for local board, application number, date, type, address, suburb and description. At first I put each threshold at the column header position, and many cells went to wrong column. The reason is that the cells sit a little to the left of their header. So the final thresholds are the midpoints between column starts, and the reason is written in a code comment so the next person does not "fix" it back.

Bad rows. A row with a malformed number, a bad date or an empty required cell is skipped and recorded in skippedRows. Usually 22 to 28 rows per file are skipped, mostly because the suburb cell is empty. I chose to be strict:

A missing row is visible in the log. A wrong row is invisible, and more dangerous.

Step 3: normalise and give every record a stable identity

• Whitespace and control characters are collapsed.
• The application number is uppercased with spaces removed.
• The date is parsed strictly into an ISO date.
• The identity is sha256(sourceId + "|" + applicationNumber). It does not depend on row order or page number, so when the council adds rows at the top, nothing shifts.

One early bug here was painful. The date was parsed twice, once in the PDF parser and once in the normaliser. The second parse did not accept ISO dates, so every row was silently dropped. The fix is not clever: there is only one place that converts the date now, and the parser has a comment pointing to it.

Step 4: write safely, even when two runs overlap

This is the part I spend most of time on. The worker must be safe if it runs twice, if two runs overlap, or if an old slow run finishes after a new fast run. There are five guards.

1. Safety floor. Fewer than 100 parsed records means the run fails and nothing is written. A truncated download or a changed layout should never wipe the dataset.
2. One transaction per record, with an advisory lock on the source and record ID, so two runs cannot create two "lodged" events for one application.
3. Monotonic guard. If the stored fetched_at is newer than or equal to this run's, stop. An older run can never roll back newer data.
4. Upsert with change detection. Field-by-field comparison plus a content hash of a JSON snapshot with stable key order.
5. Unique event keys. Versions and events use unique constraints with on conflict do nothing, so replaying a file is harmless.

-- simplified from the worker code
select pg_advisory_xact_lock(hashtextextended('auckland:' || source_record_id, 0));

-- inside the same transaction, after the monotonic check:
insert into applications (...) values (...)
on conflict (source_id, source_record_id) do update set ...;

insert into events (event_key, ...)   -- source:record:eventType:contentHash
values (...) on conflict (event_key) do nothing;

I tested this by importing the same file twice in a row. The second import gave 0 inserts, 0 updates, 5,794 unchanged, and zero duplicate keys.

Two more rules came from real problems:

• If the stored row already has coordinates and the incoming row does not, keep the stored coordinates. The PDF has no coordinates, so without this rule every hourly run would wipe all the geocoding.
• Records that disappear from the PDF are not deleted. The PDF is a rolling window, but old applications are still true history. That is why the sitemap has around 6,900 URLs while the current file only has about 5,700 rows.

Hourly, but honest about what hourly means

45 * * * *  run-weekly.sh --sync-only   # hourly: fetch, parse, write
15 20 * * 0 run-weekly.sh               # weekly: sync, geocode backfill, alerts

The script refuses to run as root, reads the exact image digests from the release manifest, and takes a non-blocking flock -n. If the last run is still going, the new one simply exits. A normal run takes 15 to 20 minutes, mostly parsing plus about 5,800 small transactions with a concurrency of 6.

The first hourly run was on 18 September. It insert 247 records, updated 3, and had 0 failures. Most other hourly runs do nothing, because the council republishes the file about once a week. When they published on 23 September, my job picked it up about 20 minutes later.

So "hourly" is how often I check, not how fresh the data is. The newest application in the file is usually about four days older than the file itself. I show this on the coverage page, because I think users deserve to know where the data ends.

A health status that does not lie

The public site shows the source as degraded when any of these is true:

• there has never been a successful sync
• the last attempt is newer than the last success
• the last success is more than 14 days old
• a run started more than 60 minutes ago and never finished

I found a funny bug in this through analytics. Six of twelve search_results events were marked source_degraded, and all of them happened between minute 45 and minute 56. The start of a run was updating last_attempt_at too early, so for about 15 minutes every hour the site told users the data was broken. Now only the end of a run updates that timestamp.

The most important UI rule: a failure must never look like "no applications near you". If the live database fails, the site falls back to a bundled snapshot of 250 records and labels it clearly as a partial snapshot.

Address search and the location problem

I don't store any LINZ data. Address search works like this:

1. The user types an address. The server cleans it (expands rd to road and st to street, removes "Auckland", "NZ" and postcodes, keeps at most 8 tokens) and queries the LINZ NZ Addresses service live.
2. The browser only receives the address ID and text, never coordinates.
3. When the user picks one, the server looks up that ID again and gets the point itself. Coordinates from the client are never trusted.
4. The search is a PostGIS ST_DWithin query with a 2 km radius.

Text search on application number, address and suburb uses ILIKE backed by trigram GIN indexes, not full-text search, because people type partial street names and application numbers.

The real limitation is that the PDF has no coordinates. New records arrive as "unlocated". A weekly job geocodes up to 100 rows with OpenStreetMap Nominatim, one request per second, newest first. Each candidate gets a score from 0 to 100:

A score of 55 is accepted. A score of 80 plus a road and house number match is called exact.

This is slow on purpose, and it means most records are still unlocated. I learned it by the hard way. A user watched 66 Titirangi Road in New Lynn and saw zero activity, not because nothing happened, but because nothing nearby had coordinates. After a careful backfill of 56 rows, the same watch showed 32 applications within 1 km, the nearest one 78.6 metres away. Unlocated records are still searchable by text, and the coverage page explains this limit.

Alerts: I prefer losing an email to sending two

A user can watch one address with a fixed 1 km radius and get a weekly email. Delivery is at-most-once by design:

queued + claimed:<lease>  →  queued + provider-attempted:<id>  →  sent | failed | skipped

Once a row is marked as attempted, a timeout does not trigger an automatic retry. For a beta product, sending the same alert twice to a resident feel worse than missing one, and I can see the failed ones in the admin page.

What I learned

• Idempotency is a habit, not a feature: stable IDs, advisory locks, a monotonic guard, unique event keys and a safety floor, in every layer.
• Simple ops is fine when it is honest: cron, flock, digest-pinned images, and a health model that says "degraded" instead of pretending.
• The product is only as good as its coverage statement. NearBuild is a partial beta built on one council file, and the site says that clearly on every page.

Today the project has 378 automated tests, plus SQL contract tests that start a real PostGIS container and apply every migration twice. It still has a backlog, like a watchdog for the whole run and more geocoding. But every hour it checks the council file, and when it doesn't know something, it says so.