← back to Nationalrealestate
db/migrations/018_ca_contractors.sql
125 lines
-- 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;