How we count

Every number we publish is a database count with a unique key behind it. This page documents the actual mechanisms in our pipelines: how records are deduplicated, how record classes are kept separate, how every source load is gated before it reaches the live tables, and what we deliberately leave out of the totals.

How we deduplicate

Deduplication is enforced by unique database indexes, not by cleanup scripts. Each dataset has a composite key that exists from day one and is never dropped, so a duplicate row physically cannot land:

• Permits are keyed on (town, source_row_id). Ingest writes are upserts against that key, so re-pulling a feed, an amended record, or an overlapping delta window refreshes the row instead of creating a copy.
• Parcels are keyed on (state, county_fips, apn). Raw source rows land in a staging table first; the unique key is applied when rows are promoted to the live table, so overlapping county and state feeds collapse to one parcel.
• Contractor licenses are keyed on (state, source, license_number, license_type). Loads are idempotent upserts: re-running a state feed refreshes rows in place.

Rows without an identity are dropped before they are written: a parcel record with no assessor parcel number and a license record with no license number never enter the database at all.

How we separate record classes

Not every construction signal is a permit, and we refuse to blur the line. Every row in the permits dataset carries a record_class that separates true building permits from new_construction_parcel rows, which are new-construction signals derived from assessor parcel data in places where the permit record itself is not publicly available. Parcel-derived rows are never counted, exported, or sold as permits. When we say 45.5M+ permits, that count excludes them.

How we gate every source load

Every bulk source run is logged before it can affect the live dataset. The parcel and contractor pipelines each write a per-run gate log (parcel_source_log and contractor_source_log) recording how many rows the source was expected to yield and how many were actually staged or upserted.

• If the loaded count misses the expected count by more than the tolerance (5 percent by default), the run is marked gate_status=failed and is not eligible for promotion. A partial or broken pull never silently becomes part of the totals.
• When a source publishes no expected count, the gate log says so explicitly rather than inventing one.
• Parcels additionally flow through a staging table: staging accepts fast bulk loads, and only gate-passed sources are promoted through the unique index into the live table.

Field values are bounds-checked on the way in. Year-built values outside 1600 to next year, negative valuations, and implausible building sizes are stored as null rather than as fake data points.

How recency works

Freshness is per source, not a site-wide slogan. Each open-data source keeps its own high-water mark: the latest record date it has successfully delivered. The daily ingest cron reads that watermark and fetches only records newer than it, minus a five-day overlap so late edits and amended records at the source are picked up. The watermark only advances after a successful run, and it is never allowed to advance past today, so a source with bad future-dated rows cannot freeze its own delta window.

Sources that only republish weekly are pulled weekly; assessor rolls and archival vintages update on their own longer clocks. Every source writes its outcome to a health table, which is what the data freshness page displays, including the sources that are currently stale.

What we don't count

• Parcel-derived rows as permits. The record_class split above is absolute: new-construction signals inferred from parcel data are a separate product, never mixed into permit counts.
• Gate-failed loads. A source run that fails its staged-versus-expected check is excluded from promotion and from every published total.
• Rows without an identity. Records missing the key field that makes them deduplicable are dropped at ingest, not counted.
• Staged-but-unpromoted rows. Published totals come from the live tables behind their unique indexes, never from staging.

One definition worth being precise about: 3,309,148 is the count of raw license records across 37 states plus DC. One business holding an electrical and a plumbing license is two license records. After entity clustering, those records resolve to 2,888,145 unique contractor entities, 12.7 percent of rows were duplicate representations of a business already counted.

Clustering works in two passes. First, an exact license-number match within the same (state, source) pair, provided the business names on those records do not conflict. Second, an exact normalized-business-name match within the same state; when the name looks like a person rather than a company, that match additionally requires a matching 3-digit ZIP prefix, since person-like names collide far more often than company names do. The known limitation: two genuinely different companies that share the same name in the same state can be merged into one entity. Clustering is intentionally conservative, and the entity counts are republished as sources refresh.