How I Turned Canadian Open Government Data Into a Live Licence-Verification Site + API (Build Log, 2026)

Six weeks ago "verify a contractor licence in Canada" meant opening a different clunky government portal for every province. I wanted one search box. So I built one — a live site backed by 65,177 official contractor and home-builder licences, plus an API for the same data. This is the build log: the stack, the data pipeline, and the one bug that quietly cost me 98% of my Ontario records.

The problem

Canada has no single national registry of licensed contractors. Quebec has the RBQ. Ontario licenses new-home builders through the HCRA. Every province publishes its own data in its own format, on its own portal, with its own quirks. For a homeowner trying to check "is this person actually licensed before I pay them a deposit," that's a dead end. For a developer who wants the data programmatically, it's worse.

The goal: aggregate the official registries into one place, keep it free to search, and expose the same data as a clean API for people who want to build on top of it.

The stack

• Data collection: Apify actors, one per source, that scrape the open-data endpoints and normalize them. Quebec RBQ ships a daily bulk CSV (inside a 10.8 MB zip, ~924k rows that dedupe to ~54k active licences). Ontario HCRA has no bulk file — it's an internal JSON API behind the public registry.
• Site: Astro 5 in SSR mode.
• Hosting + data: Cloudflare Workers with a D1 database (SQLite at the edge). The free tier gives 100k reads/day, which is plenty for launch.
• Output: server-rendered profile pages with schema.org HomeAndConstructionBusiness markup, a chunked sitemap, and a JSON/CSV API.

The whole thing runs on free tiers. The interesting engineering wasn't the framework — it was getting clean, complete data out of registries that don't want to hand it to you in bulk.

The bug that ate my data: "755"

Ontario was supposed to be the strong half of the launch. Instead, for two days, my site showed exactly 755 Ontario builders. The real HCRA registry has tens of thousands. Something was silently dropping ~98% of the records.

Here's what was happening.

The HCRA registry exposes an internal search endpoint that takes a name term: /api/builders?builderName=<term>. There's no "give me everything" call, so to sweep the full registry you iterate the search across the alphabet (a, b, c, …) and union the results, deduping by account number.

My first version did two things wrong:

1. The sweep was a–z only. Company names starting with a digit — and in Ontario a huge number of numbered corporations are named things like 1000014268 Ontario Inc. — were never queried.
2. It enriched every single record with a detail call. For each builder found in the list, the scraper fired a second request to /api/buildersummary?id=<account> to pull full details. On a full sweep that's tens of thousands of sequential requests.

On Apify's free plan, a run that balloons into tens of thousands of sequential requests eventually hits the account's usage ceiling and gets Aborted mid-run. When a run aborts, whatever made it into the dataset so far is what you keep. In my case: 755 records. No error on the site. No crash. Just a quietly truncated dataset that looked fine.

That's the dangerous class of bug — not the one that throws, but the one that silently returns less.

The fix

Three changes:

1. Sweep a–z and 0–9. 36 prefixes instead of 26. This alone recovered the entire universe of numbered corporations.
2. Make detail enrichment optional. I added an enrich input flag: on for small keyword lookups (where the extra detail is cheap and useful), off for the full sweep. A full sweep is now 36 fast list requests — no per-record fan-out, no abort.
3. Filter deliberately, not accidentally. The full registry turned out to be 48,175 records once I could actually read all of it — but most of that is Expired (~34k). Instead of dumping everything, I publish an allowlist of meaningful statuses (Licensed, Licensed with Conditions, Revoked, Refused, Suspended, Cancelled, Unlicensed, and a few notice-of-proposal states). That's the 11,104 builders now live — the ones a homeowner would actually care about, including the scary ones.

Ontario went from 755 → 11,104. Lesson burned in permanently: on metered infrastructure, a job that grows unboundedly doesn't fail loudly — it fails by giving you less and calling it success. Now the full sweep is a fixed, predictable number of requests, and I sanity-check row counts against the registry's own totals before publishing.

The edge-cache red herring

One more debugging story, because it cost me an hour and the lesson is useful.

After updating D1, the bare URLs (/, /on) still showed old numbers, while URLs with a query string (/on?page=40) showed fresh data. Obvious conclusion: Cloudflare is edge-caching my SSR HTML. I almost ran a full cache purge.

It wasn't Cloudflare. HTML wasn't being cached at all (caching level Standard doesn't cache HTML by default). The stale numbers were coming from my own fetch tool's per-URL cache — I'd requested the bare URLs before the data update, and was reading those cached responses back. Real users, in a real browser, saw the correct numbers the entire time.

Lesson: when you're verifying a deploy, check the actual live site in a real browser (or bust the cache with ?v=), not through a tool that might be serving you a cached copy. "It's stale for me" is not the same as "it's stale for users."

What it looks like now

• 65,177 licences live in D1: 54,073 Quebec (RBQ) + 11,104 Ontario (HCRA).
• Search by business name or licence number, per-province listing pages, individual profile pages with structured data.
• A chunked sitemap (static routes + seven 10k chunks) so search engines can actually crawl 65k profile URLs.
• The same data available as JSON, CSV, or via an MCP endpoint for AI agents, through the underlying Apify actors.

You can try the site here: CheckContractors.ca. If you want the raw datasets for Quebec and Ontario, the contractor-licence actor on Apify gives you the same records programmatically, updated daily.

Takeaways for anyone building on open gov data

1. "No bulk export" doesn't mean "no data." An internal search API you can sweep is a bulk export in disguise — you just have to enumerate the key space (including digits).
2. Watch for silent truncation on metered platforms. Always compare your row count to the source's published total. A number that looks plausible is the easiest bug to miss.
3. Separate "collect everything" from "publish a subset." Pull the full registry once, then filter to what your users need with an explicit allowlist — don't let the collection step do your filtering by crashing.
4. Verify deploys in a real browser. Your tooling has caches too.

Building on open data is mostly a data-quality problem wearing an engineering costume. The framework is the easy part.

---

If this was useful, the live tool is CheckContractors.ca and the datasets are on Apify. Happy to answer questions about the pipeline in the comments.