← back to Nationalrealestate
ca_contractors: CSLB licensed-contractor registry schema (TK-10488) — shared coordination layer for the RE builds
1bdb0d9760d949011941b4ea5c65d9c53b26e819 · 2026-08-12 10:05:06 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/018_ca_contractors.sql
Diff
commit 1bdb0d9760d949011941b4ea5c65d9c53b26e819
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 12 10:05:06 2026 -0700
ca_contractors: CSLB licensed-contractor registry schema (TK-10488) — shared coordination layer for the RE builds
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/migrations/018_ca_contractors.sql | 124 +++++++++++++++++++++++++++++++++++
1 file changed, 124 insertions(+)
diff --git a/db/migrations/018_ca_contractors.sql b/db/migrations/018_ca_contractors.sql
new file mode 100644
index 0000000..7a2899e
--- /dev/null
+++ b/db/migrations/018_ca_contractors.sql
@@ -0,0 +1,124 @@
+-- TK-10488: CA CSLB licensed-contractor registry — the SHARED coordination layer read by
+-- all four RE builds (usre engine, CRCP/Frank, HomesOnSpec, RENTV). Source = CSLB public
+-- "Master List of California Licensed Contractors" (BPC §27 public-record disclosure),
+-- ingested PRIVATELY into the usre engine; any customer-facing display is Steve-gated.
+--
+-- Mirrors the broker/firm registry idioms already in this DB: TEXT-heavy, (source,source_id)
+-- UNIQUE, normalized_name index, TIMESTAMPTZ created_at. A CSLB license holds MANY
+-- classifications (A + B + C-10 ...), so classifications are BOTH a TEXT[] on the row
+-- (fast GIN filter) AND a normalized child table (joins/counts). No PostGIS in this DB, so
+-- geo is county/city/zip + nullable lat/lng NUMERIC for later haversine matching.
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+
+-- ── Classification lookup (CSLB code -> trade) ──────────────────────────────────────────
+CREATE TABLE IF NOT EXISTS ca_contractor_class_ref (
+ code TEXT PRIMARY KEY, -- 'A' | 'B' | 'B-2' | 'C-10' | 'ASB' | 'HAZ' | 'D-xx'
+ title TEXT NOT NULL, -- human trade name
+ kind TEXT NOT NULL -- 'engineering' | 'building' | 'specialty' | 'certification'
+);
+
+-- ── Contractor registry (one row per CSLB license) ──────────────────────────────────────
+CREATE TABLE IF NOT EXISTS ca_contractors (
+ id BIGSERIAL PRIMARY KEY,
+ license_no TEXT NOT NULL, -- CSLB license number
+ business_name TEXT NOT NULL,
+ normalized_name TEXT, -- lowercased/collapsed for match/joins
+ address TEXT,
+ city TEXT,
+ county TEXT, -- CSLB business county (matching key)
+ state_code TEXT DEFAULT 'CA',
+ zip TEXT,
+ phone TEXT,
+ business_type TEXT, -- sole owner | corp | partnership | LLC
+ license_status TEXT, -- Active | Inactive | Expired | Suspended
+ issue_date DATE,
+ reissue_date DATE,
+ expire_date DATE,
+ primary_class TEXT, -- denormalized convenience (first/primary code)
+ classifications TEXT[] DEFAULT '{}', -- all held codes, for GIN filtering
+ cb_bond_company TEXT, -- contractor's bond carrier
+ cb_bond_amount NUMERIC,
+ wc_status TEXT, -- workers' comp coverage status
+ wc_insurance_co TEXT,
+ wc_policy_no TEXT,
+ wc_effective_date DATE,
+ wc_expire_date DATE,
+ lat NUMERIC, -- geocoded later (no PostGIS); haversine
+ lng NUMERIC,
+ region_id INTEGER REFERENCES region(id), -- align to usre region layer when known
+ raw JSONB, -- original CSLB row for anything unmodeled
+ source TEXT NOT NULL DEFAULT 'cslb',
+ source_id TEXT NOT NULL, -- = license_no
+ created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ CONSTRAINT ca_contractors_source_source_id_key UNIQUE (source, source_id)
+);
+
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_norm ON ca_contractors (normalized_name);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_county ON ca_contractors (county);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_city ON ca_contractors (city);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_zip ON ca_contractors (zip);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_status ON ca_contractors (license_status);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_primary ON ca_contractors (primary_class);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_class ON ca_contractors USING GIN (classifications);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_license ON ca_contractors (license_no);
+
+-- ── Normalized M:N classification child (a license -> many trades) ──────────────────────
+CREATE TABLE IF NOT EXISTS ca_contractor_classification (
+ contractor_id BIGINT NOT NULL REFERENCES ca_contractors(id) ON DELETE CASCADE,
+ code TEXT NOT NULL, -- soft ref to ca_contractor_class_ref(code); D-xx sub-codes may exceed the seed
+ PRIMARY KEY (contractor_id, code)
+);
+CREATE INDEX IF NOT EXISTS idx_ccc_code ON ca_contractor_classification (code);
+
+-- ── Seed the canonical CSLB classification list ─────────────────────────────────────────
+INSERT INTO ca_contractor_class_ref (code, title, kind) VALUES
+ ('A', 'General Engineering', 'engineering'),
+ ('B', 'General Building', 'building'),
+ ('B-2', 'Residential Remodeling', 'building'),
+ ('C-2', 'Insulation and Acoustical', 'specialty'),
+ ('C-4', 'Boiler, Hot Water Heating and Steam Fitting', 'specialty'),
+ ('C-5', 'Framing and Rough Carpentry', 'specialty'),
+ ('C-6', 'Cabinet, Millwork and Finish Carpentry', 'specialty'),
+ ('C-7', 'Low Voltage Systems', 'specialty'),
+ ('C-8', 'Concrete', 'specialty'),
+ ('C-9', 'Drywall', 'specialty'),
+ ('C-10', 'Electrical', 'specialty'),
+ ('C-11', 'Elevator', 'specialty'),
+ ('C-12', 'Earthwork and Paving', 'specialty'),
+ ('C-13', 'Fencing', 'specialty'),
+ ('C-15', 'Flooring and Floor Covering', 'specialty'),
+ ('C-16', 'Fire Protection', 'specialty'),
+ ('C-17', 'Glazing', 'specialty'),
+ ('C-20', 'Warm-Air Heating, Ventilating and Air-Conditioning (HVAC)', 'specialty'),
+ ('C-21', 'Building Moving/Demolition', 'specialty'),
+ ('C-22', 'Asbestos Abatement', 'specialty'),
+ ('C-23', 'Ornamental Metal', 'specialty'),
+ ('C-27', 'Landscaping', 'specialty'),
+ ('C-28', 'Lock and Security Equipment', 'specialty'),
+ ('C-29', 'Masonry', 'specialty'),
+ ('C-31', 'Construction Zone Traffic Control', 'specialty'),
+ ('C-32', 'Parking and Highway Improvement', 'specialty'),
+ ('C-33', 'Painting and Decorating', 'specialty'),
+ ('C-34', 'Pipeline', 'specialty'),
+ ('C-35', 'Lathing and Plastering', 'specialty'),
+ ('C-36', 'Plumbing', 'specialty'),
+ ('C-38', 'Refrigeration', 'specialty'),
+ ('C-39', 'Roofing', 'specialty'),
+ ('C-42', 'Sanitation System', 'specialty'),
+ ('C-43', 'Sheet Metal', 'specialty'),
+ ('C-45', 'Sign', 'specialty'),
+ ('C-46', 'Solar', 'specialty'),
+ ('C-47', 'General Manufactured Housing', 'specialty'),
+ ('C-49', 'Tree Service', 'specialty'),
+ ('C-50', 'Reinforcing Steel', 'specialty'),
+ ('C-51', 'Structural Steel', 'specialty'),
+ ('C-53', 'Swimming Pool', 'specialty'),
+ ('C-54', 'Ceramic and Mosaic Tile', 'specialty'),
+ ('C-55', 'Water Conditioning', 'specialty'),
+ ('C-57', 'Well Drilling', 'specialty'),
+ ('C-60', 'Welding', 'specialty'),
+ ('C-61', 'Limited Specialty (D-subcategories)', 'specialty'),
+ ('ASB', 'Asbestos Certification', 'certification'),
+ ('HAZ', 'Hazardous Substance Removal Certification', 'certification')
+ON CONFLICT (code) DO NOTHING;
← 504ae66 usre deals: facet deselect uses undefined (not null) to matc
·
back to Nationalrealestate
·
usre: shared CSLB contractor API (search/detail/match) + int c9daafc →