HDB Resale & Rent — Singapore

Building flatlas.sg — a 35-year HDB price map, and the production bug that taught me to fail loud

30 Jun 2026 · by · postgis · maplibre · go · docker · etl · devops · singapore · side-project

I built an interactive map of every HDB resale flat sold in Singapore since 1990 — ~970,000 transactions, geocoded to the block, coloured by inflation-adjusted price, with a hedonic model that tries to explain why a given flat costs what it does. You pan, you zoom, the country resolves from a town choropleth into individual blocks into 3D buildings, all on one consistent price ramp. There’s a Sale/Rent toggle, school catchments, MRT lines, childcare centres.

It’s a fun consumer toy. But the part worth writing about isn’t the map — it’s everything around the map. This post is two stories: how the thing is built, and the afternoon a single deploy quietly broke production while every health check stayed green. The second story has a moral I kept relearning all day: fail loud, not silent.

The architecture: two channels, one database

The core design decision is a split that I’d recommend to anyone building a data-heavy map:

Tiles are for drawing; JSON is for interacting. They share one PostGIS database but they are different services with different performance characteristics, and keeping them separate kept both simple. The basemap (streets, labels) is a self-hosted Singapore .pmtiles file — not served by Martin, just a static byte-range file behind the web server.

The whole stack runs on a single VM via Docker Compose:

PostGIS (source of truth)
  ├── Martin       → vector tiles      (rendering channel)
  ├── Go API       → BBOX JSON         (interaction channel)
  └── Caddy        → TLS, single-origin, serves the SPA + proxies /api and /tiles

Frontend is React + MapLibre GL, built with Vite. Three zoom tiers — town choropleth below z12, block dots from z12–14, 3D extrusions above — all sharing one price→colour ramp so the visual language never changes as you zoom. The design ethos: monochrome everything, colour only on the price ramp. Amenity markers are plain white discs. The one exception is MRT line colours, because fighting Singaporeans on their MRT colours is a battle you lose.

The data pipeline

Everything starts at data.gov.sg: resale transactions back to 1990, rental approvals from 2021, CPI, land-use polygons, building footprints, childcare centres. The ETL is a chain of small Python scripts orchestrated by one refresh.sh:

  1. Download the source CSVs/GeoJSON (data.gov.sg’s blob CDN 403s the default python-urllib user-agent, so every downloader spoofs a browser UA — a fun half-hour to discover).
  2. Normalise with DuckDB — unify five eras of resale data with different lease formats, tag the price basis, compute CPI-deflated “real” prices.
  3. Geocode the ~10,000 unique blocks (not the million transactions) against OneMap, with a permanent cache. ~98.6% resolve; the rest are logged and skipped rather than forced to a wrong coordinate. OneMap will happily fuzzy-match “JALAN BATU” to “JALAN JAMBU BATU” and drop a Kallang block in Bukit Timah, so the geocoder validates the block number and road name before accepting a hit.
  4. Load into PostGIS, incrementally (only the last few months on a normal run), with a quarterly full reload to absorb restatements.
  5. Enrich — land-use features, building footprints, upgrading status, amenities.
  6. Fit a hedonic regression so the block panel can decompose a price into “unit size +X%, remaining lease −Y%, distance to MRT +Z%…”.

That hedonic model is the feature I’m proudest of and, as you’ll see, the one that blew up in the most instructive way.

Shipping: a deploy script that does only what changed

Early on, deploys were “SSH in, git pull, rebuild whatever I remember touching.” That doesn’t scale past the third 11pm deploy. So I wrote ops/deploy.sh: pull, diff HEAD against what was pulled, and do only the work that diff requires.

db/migrations/ changed  → run migrations
api/ changed            → rebuild + recreate the Go container
ops/Caddyfile changed   → validate + hot-reload Caddy (zero downtime)
web/ changed            → rebuild the frontend

A pure frontend change costs one npm run build and zero container churn, because Caddy bind-mounts the built web/dist and serves it live. Then I wired it to GitHub Actions: push to the deploy branch → an Action SSHes into the VM and runs deploy.sh. The SSH key on the box is locked to a forced command, so a leaked key can only trigger a deploy, never run an arbitrary shell. Push = deploy, with a blast radius of one command.

I layered in fail-safety, mostly because I’d been bitten by each failure mode at least once:

I felt very clever about all of this. Then the database reminded me that none of it matters if your data is wrong.

The incident: a deploy that broke nothing and everything

The symptom was simple. After a deploy, the map’s basemap and choropleth rendered fine, but clicking any block — or just letting the viewport load flat data — returned HTTP 500. The frontend showed an empty map of dots that wouldn’t open. /api/health returned 200. Every container was “healthy.” The deploy had reported success.

The API returned a useless body: query failed. No log line. The handler caught the error and threw it away:

if err != nil {
    http.Error(w, "query failed", http.StatusInternalServerError)  // the real error: /dev/null
    return
}

Lesson zero: an error you swallow is an error you will spend an hour rediscovering. My first fix wasn’t a fix at all — it was adding log.Printf("%v", err) so the database could finally tell me what it thought.

When it did, the chain of causes was almost comically deep.

Cause 1: the migration baseline trap

The Go API had been rebuilt from a branch where the BBOX query selects a bunch of block-attribute columns — year_completed, total_dwelling_units, plot ratio, commercial-nearby, and so on — added across migrations 025–028. The query referenced columns that did not exist in the live database. column "gpr" does not exist.

But the migrations were recorded as applied. How?

My migration runner, migrate.sh, had a “baseline” mode for the common case: a fresh DB where Docker’s initdb had already run every migration on first boot. On its first run against such a DB, it marks every migration file as applied without running them — because they already ran, at boot. Sensible.

The trap: that logic assumes the database contains the schema for every current migration file. But this DB had been first-booted months earlier, at a commit that only had migrations up to ~023. Migrations 025–028 were added later. So when migrate.sh ran its first baseline, it cheerfully marked 025–028 as “applied” — for a schema that had never been created.

The migration table said everything was applied. The columns didn’t exist. The API queried them. 500.

Nothing in the deploy noticed, because a deploy’s health checks verify that services serve, not that the data is complete. The single most expensive bug of the project was a script silently asserting success.

The red herring

While debugging, I kept reproducing the 500 with curl and a hand-typed bounding box — and the error I got back, once logging was in, was:

invalid input syntax for type date: "1990-01"

I chased this for a while before realising it was my own malformed test query — the column is a DATE, I’d sent 1990-01 instead of 1990-01-01. The real frontend sent valid dates. So for a stretch I was debugging a bug that only existed in my curl, layered on top of the real bug underneath. Reproduce with the real client’s inputs, not your own approximation of them.

The fix, and the principle

Three changes, all variations on the same theme:

  1. An initdb hook that records exactly which migration files initdb applied, into the migration history table. Now a fresh DB starts with an accurate record and migrate.sh never has to guess.
  2. migrate.sh refuses to silently baseline. On a populated-but-unrecorded DB it now errors with instructions and requires an explicit BASELINE=1. A silent assumption became a loud stop.
  3. Atomic migration batches — all pending migrations apply in one transaction, so a failure rolls the whole batch back instead of leaving a half-migrated schema.

The second blow-up: a cascade of silence

Fixing the schema surfaced the next layer. Some features were still missing on production but present locally. Same root shape — new schema, stale data — but a different cause.

The monthly refresh.sh had fired a burst of downloads at data.gov.sg and got rate-limited: HTTP 429. The land-use and building-footprint downloaders had no retry, so they failed instantly. Each failure was a one-line || echo "[warn] …" buried in a 200-line log, and the script carried on. The deploy “succeeded.”

Then the cascade. With land-use never loaded, every block’s gpr (plot ratio) was NULL. The hedonic model imputes missing gpr to the column median — but the median of an all-NULL column is itself NaN. fillna(NaN) is a no-op. The next line, dropna(), dutifully dropped every single row. The model then tried to fit on zero transactions and died:

HEDONIC FIT FAILED: With n_samples=0 ... the resulting train set will be empty.
fitting on 0 transactions since 2021-01-01

A rate limit on a 190 MB file download had, four steps downstream, produced a regression with no data. And every link in that chain had failed quietly.

The fixes write themselves once you say them out loud:

⚠️  REFRESH INCOMPLETE — 2 step(s) failed:
      - landuse — land-use hedonic features not refreshed
      - building_footprints — 3D footprints not refreshed

…and exits non-zero. A partial refresh can no longer masquerade as a complete one.

There was even a tidy little coda: re-running a single ETL step by hand failed with password authentication failed for user "hdb", because a bare python etl/step.py never loads the .env that refresh.sh sources for you. One more silent assumption, one more small wrapper script (ops/etl-run.sh) to make the supported path obvious.

What I’d tell past me

Most of the engineering I was proud of — the diff-aware deploy, the auto-rollback, the zero-downtime config reload — worked exactly as designed and was never the problem. The problems were all the places something failed and said nothing:

None of these were hard bugs. Each was a silent bug, and silence is what turned a five-minute fix into an afternoon. The single highest-leverage change across the whole project wasn’t a feature or an optimisation — it was making every layer fail loudly: log the real error, refuse the dangerous default, summarise what didn’t happen, exit non-zero when the work is incomplete.

Health checks tell you the server is up. They don’t tell you the data is right. Build the thing that screams when it isn’t.

flatlas.sg is built with PostGIS, Martin, Go, React + MapLibre GL, and Caddy, on a single VM. Data from data.gov.sg, geocoded via OneMap. If you’re building something similar and want to compare notes, I’m happy to talk shop.

Explore the price map →