Everything below was applied to the live leads_db with no downtime, no restart, no data loss — and is fully reversible. warroom online throughout
Root cause: upstream B2B lists (via Instantly) put a full address in the state field. Fixed permanently at ingestion (geo-normalize.js guard in warroom) so it cannot recur, plus a batched, snapshot-reversible repair of history.
| Metric | Before | After |
|---|---|---|
| 2-letter USPS codes | 161,697 | 258,371 |
| Full-name states (e.g. texas) | 96,674 | 0 |
| Analytics buckets for Texas | texas + tx (split) | TX = 18,125 |
| Total leads (no loss) | 995,115 | 995,115 |
96,674 standardized (texas→TX, california→CA…). Non-states (cities in the state field) left untouched — not guessed. Serving layer (Lead-360 + analytics) refreshed and converged.
Unique Lead ID confirmed: identity_registry.master_lead_id — uuid, generated once, immutable, never reused (merges audited via merged_into). Four additive gap-fills added to match the business field list:
| New object | Type | Purpose |
|---|---|---|
| source_crosswalk.source_type | attribute | Source Type (cold_email / canvass / inbound / import …) |
| lead.follow_up_at | attribute | Follow-up / business date |
| ulda_master.enrichment | entity | Enrichment Type — per-provider lineage |
| ulda_master.address_serviceability | entity | Fiber Availability — per address × carrier × time |
New: ulda_stage ingestion (land_lead + ingest_interaction), partitioned interaction/enrichment, v_lead_journey_flat (1st–4th + last channel), and v_lead_master_flat — one row, all 36 fields. Channel mix: Email 5,750 · SMS 1,682 · Web 268 · Voice 238. Query one lead: SELECT * FROM ulda_serve.v_lead_master_flat WHERE unique_lead_id = '…'
These pages read the live schema — they already reflect every update above.