`db/migrations/022_firm_contact_enrich.sql`

23 lines
-- TK-10687: firm contact enrichment — every firm/business must have a phone (Steve 2026-08-18),
-- and every active broker inherits a callable number via the firm fallback. The DRE registry ships
-- name + license + mailing CITY only (no street, no phone, no email), so phone/email/address are
-- sourced externally at $0 (firm-site scrape + openclaw + local models + free web). This migration
-- adds the canonical firm-level contact columns + enrichment provenance/status so the enrichment
-- driver is idempotent and resumable, and so a "firms still missing a phone" work-queue is queryable.
-- firm.phone / firm.website / firm.street_address / firm.address_source already exist (migrations
-- 001 + 021); firm_contacts(kind email/phone/contact_url) is the multi-value backing store (002).
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.

ALTER TABLE firm
  ADD COLUMN IF NOT EXISTS phone_source        TEXT,          -- 'firm_site' | 'openclaw' | 'web_search' | 'dre_backfill' | 'redfin' | 'google_places_resolve'
  ADD COLUMN IF NOT EXISTS email               TEXT,          -- canonical firm email (promoted from firm_contacts for fast API)
  ADD COLUMN IF NOT EXISTS email_source        TEXT,
  ADD COLUMN IF NOT EXISTS contact_enriched_at TIMESTAMPTZ,   -- last time the enrichment driver touched this firm
  ADD COLUMN IF NOT EXISTS contact_attempts    INTEGER NOT NULL DEFAULT 0,  -- attempts so far (backoff / triage)
  ADD COLUMN IF NOT EXISTS phone_status        TEXT;          -- 'found' | 'no_site' | 'unreachable' | 'pending' (NULL = never attempted)

-- Work-queue index: "CA firms with an active broker still missing a phone" is the driver's target set.
CREATE INDEX IF NOT EXISTS idx_firm_phone_missing
  ON firm (license_state)
  WHERE coalesce(nullif(phone,''), '') = '';