Arseni FilonData product
A contractor license lookup for two states
- Live site
- llcsearchwa.com
The problem
Before hiring a contractor, a homeowner is told to check the license. Washington registers contractors through Labor and Industries, and Oregon licenses them through the Construction Contractors Board. Each state has its own lookup, with its own rules for how a name has to be typed. A contractor who works both sides of the Columbia holds one license in each state, in two systems, and the two records do not always carry the same business name.
How it runs
Goes in
- Washington L&I registrations, 75,165 of them
- Oregon CCB licenses, 45,269 of them
What happens
- A refresh script replaces the Washington rows while the table stays servable
- City spellings are cleaned on the way in
- Indexes are built on the expression each page address is built from
- One search reads both registries
Comes out
- A page for every record, with a link to the agency
- Ways in by city, trade, ZIP code, and letter
- A record its contractor can claim from an email link
What I built
One search across both agencies
The lookup holds 120,434 records. 75,165 are Washington L&I registrations and 45,269 are Oregon CCB licenses. A search by business name, license number, or city reads both at once. A search for a roofer who works both sides of the Columbia returns two records, the Oregon license and the Washington registration, side by side.
A page for every record
Every record has its own page with the license number, the classification, the dates, and the status. The status reads Not expired, with the date the registration runs to, because a copy of a state file cannot show a suspension. The page says so and links to the agency, where the current status can be confirmed.
Ways in besides search
The registry can be browsed by city, by trade, by ZIP code, and by letter, across 3,949 cities and towns and 99 trade classifications. One page lists contractors licensed in both states. A statistics page counts the registry by decade and by business structure, and every figure on it is computed from the same records.
Claiming a record
A contractor can claim his own record. He signs in from a link sent to his email, with no password. What he adds, a description and photos, waits as pending until it is reviewed. The state’s data on the page cannot be edited by anyone.
How it is built
The Washington records come from the state’s open data. A refresh script pulls every active registration and replaces the Washington rows, in an order that keeps the table servable the whole time. A claimed record keeps its flag.
The states publish the same city several ways, and each spelling would become its own page. The spellings are cleaned on the way in. A state name is stripped only when it names the registry that issued the license, so an out of state address keeps its own.
City and trade pages are found through indexes built on the same expression the page address is built from. The two are kept identical in the database and in the code, so a city with an apostrophe in its name is found, and no query reads all 120,000 rows.
Sign-in, sessions, and the claims live in the same edge database as the registry, with an audit log. The lookup runs on its own domain from the same code as my contractor site, routed by host name.
The result
One search returns a contractor’s Washington registration and his Oregon license together.
Screens
