`db/migrations/017_broker_website.sql`

26 lines
-- M-B4: agent (broker) OWN-website + contact discovery, mirroring firm_site (M-B2).
-- Every displayed RE agent must tie to a broker/firm and render phone + firm site +
-- the agent's OWN site + the listing page (linking policy update, Steve 2026-08-06).
-- The broker table shipped with phone/email columns but they are 100% empty and there
-- is no registry phone source, so BOTH the agent's website AND phone are recovered by
-- src/enrich/broker_website_discovery.ts ($0: free SERP scrape + local Ollama identity
-- verification; a follow-on resolve step crawls the found site for the phone).
-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.

ALTER TABLE broker
  ADD COLUMN IF NOT EXISTS website TEXT,                 -- the agent's OWN professional site (LLM-verified as theirs, not a namesake)
  ADD COLUMN IF NOT EXISTS website_status TEXT,          -- found | no_url | unverified | throttled  (NULL = not yet attempted)
  ADD COLUMN IF NOT EXISTS website_confidence REAL,      -- 0..1 local-LLM identity confidence for `website`
  ADD COLUMN IF NOT EXISTS website_discovered_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS phone_source TEXT;            -- provenance of broker.phone (site-extract | registry) once populated

-- Cheap "not yet attempted" lookup so the sweep advances down its priority queue.
-- MOVED OUT OF THIS FILE (TK-10535, Cody gate): a NON-concurrent CREATE INDEX inside the
-- runner's transaction takes an ACCESS EXCLUSIVE lock on the ~2M-row broker table, and on a
-- freshly-added `website_status` column the `IS NULL` predicate matches EVERY row (= a cold
-- full-table index build) — that would take the live RENTV desk dark for minutes mid-migration.
-- It is now built CONCURRENTLY, out-of-band, from docs/deploy-asset-class.md (same pattern as
-- the asset_class indexes). Left here as documentation only:
--   CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_website_todo
--     ON broker (id) WHERE website_status IS NULL;