A Domain-Oriented Master Data & Customer Data Platform for Lead-360 on PostgreSQL
2026-07-16
The Unified Lead Dataset Architecture (ULDA) is an enterprise data platform that masters ~1.06 million deduplicated lead identities (from ~1.76 M raw source rows) into a single, governed, AI-ready system on a live production PostgreSQL database, with zero downtime during its construction.
Rather than a single wide “lead” table, ULDA is organised into four cooperating domains, each with a distinct change-velocity, storage strategy, and consumption pattern:
| Domain | Nature | Change velocity | Example objects |
|---|---|---|---|
| A — Master Data | Golden, mastered entities | Slow (SCD2) | identity_registry, person,
company, address |
| B — Activity / Event | Append-only history | High | interaction, enrichment,
order |
| C — Reference | Curated external facts | Medium | address_serviceability |
| D — Serving / CDP | Denormalised projections | Derived | mv_lead_360, v_lead_journey_flat,
lead_embedding |
This separation is the core design decision. It is the difference between a spreadsheet and a data platform: identity is mastered once and never duplicated; activity is preserved forever as immutable history; and consumers (CRM, dialer, analytics, AI) read fast, purpose-built projections. This document specifies each domain and component in full, recommends the physical deployment, provides seven architecture diagrams, and closes with a critical assessment.
ULDA applies a medallion + Master Data Management (MDM) + Customer Data Platform (CDP) pattern specialised for identity:
Because the four kinds of data have fundamentally different physics:
A single wide lead table (the intuitive design) fails on every enterprise axis:
| Concern | One big table | Four-domain model |
|---|---|---|
| Journey history | capped at fixed columns; overwrites | unlimited, immutable events |
| Normalisation | update/insert/delete anomalies | 3NF master + append-only events |
| NULL density | most columns null for most rows | data exists only where relevant |
| Indexing | one index strategy for all needs | per-domain optimal indexes |
| Partitioning | can’t partition identity by event time | events partitioned by time |
| Concurrency | every event UPDATEs the golden row | INSERT-only events, no contention |
| AI features | mixes stable + volatile → drift/leakage | clean per-domain feature store |
| Scale to 1B | row & index bloat | event volume scales independently |
ULDA implements the canonical MDM disciplines: a single
source of truth (identity_registry),
survivorship (golden record chooses the best value per
attribute with lineage), crosswalk
(source_crosswalk maps one golden ID to many source system
IDs), match/merge with audit (deterministic +
probabilistic resolution, reversible merges), and
stewardship (a governance schema holding data-quality
findings and a human match-review queue).
A CDP unifies customer data from many sources into a persistent,
single customer view and activates it to operational tools. ULDA’s
Serving domain is exactly this: mv_lead_360 is the unified
profile; v_lead_journey_flat is the cross-channel timeline;
the Reverse-ETL surface activates segments back to Warroom / SMS /
dialer under a consent gate.
Should ULDA be one database with many schemas, many databases, many servers, or a hybrid?
PostgreSQL instance (database: leads_db)
│
├── ulda_master — Master Data domain (A)
├── ulda_events — Activity / Event domain (B) [partitioned]
├── ulda_ref — Reference domain (C)
├── ulda_serve — Serving / CDP domain (D) [materialised views + pgvector]
└── ulda_gov — Governance (DQ, consent, lineage, match-review, audit)
(public.* — legacy operational tables, authoritative during transition)This is the deployment ULDA runs today, and it is the correct choice at this scale.
mv_lead_360 joins master + events + reference in a single
query. Across separate PostgreSQL databases those joins are impossible
without foreign-data wrappers (slow, fragile). Schemas give you one
transaction boundary, one query planner, and real foreign keys across
domains.ulda_master.identity_registry are only enforceable inside
one database. Separate databases would push integrity into application
code — a step backwards.GRANTs + distinct roles (ulda_web read-only,
ulda_writer, ulda_admin) provide isolation
without physical separation.A single well-provisioned node (here: 4 vCPU / 62 GB) comfortably serves ~1–2 M identities and their events. Sharding across servers adds distributed-transaction complexity that is not justified until a single node is genuinely saturated. The correct first scale-out step is a read replica (streaming replication) that offloads analytics/AI/serving reads from the OLTP primary — a hybrid that keeps one logical database while separating read and write load.
lead_embedding builds,
and heavy mv_* refreshes there.Principle: keep it one logical database until the workload — not the diagram — forces a split. Physical topology should follow measured load, not aesthetics.
flowchart LR
subgraph SRC[Sources]
A1[Apollo / Brokers / CSV]; A2[Instantly]; A3[SMS · Dialer]; A4[Warroom · D2D]
end
subgraph ING[Ingestion + CDC]
G[geo-normalize guard]; DW[dual-write trigger → change_outbox → drain worker]
end
subgraph MDM[Master / MDM]
IR[Identity Resolution<br/>deterministic + probabilistic]; SV[Golden survivorship]
end
subgraph CDP[Serving / CDP]
L3[Lead-360]; JR[Journey]; AN[Analytics]; AI[AI · pgvector]
end
subgraph CONS[Consumers]
WR[Warroom]; DL[Dialer]; RE[Reverse-ETL]; BI[BI / Reports]; LLM[AI agents]
end
SRC --> ING --> MDM --> CDP --> CONS
GOV[[ulda_gov · governance spine: DQ · consent · lineage · audit]]
GOV -.-> ING & MDM & CDPflowchart TB
subgraph NODE[PostgreSQL primary · leads_db]
M[(ulda_master)]; E[(ulda_events · partitioned)]; R[(ulda_ref)]; S[(ulda_serve · MV + vector)]; GV[(ulda_gov)]; P[(public · legacy)]
end
WAL[WAL archiving + PITR slot] --- NODE
REP[(Read replica · analytics/AI — future)] -. streaming .-> NODE
BK[Off-host encrypted backups] --- NODEflowchart TB
L0[L0 Sources] --> L1[L1 Bronze / landing · append-only]
L1 --> L2[L2 Identity Resolution · master_lead_id]
L2 --> L3[L3 Master / Silver · golden records + events]
L3 --> L4[L4 Serving / Gold · Lead-360 · analytics · AI]
GOV[Governance spine · lineage · DQ · consent · catalog] -.-> L1 & L2 & L3 & L4flowchart TB
subgraph A[A · Master Data]
ID[identity_registry]:::hub; PER[person]; CO[company]; ADDR[address]; CP[contact_point]; SRC[source_crosswalk]; AG[agent]; PROD[product]; CMP[campaign]; CH[channel]
end
subgraph B[B · Activity / Event]
INT[interaction]; ENR[enrichment]; OPP[opportunity]; ORD[order]; OL[order_line]; CON[consent / DNC]
end
subgraph C[C · Reference]
SERV[address_serviceability]
end
subgraph D[D · Serving / CDP]
L360[mv_lead_360]; JRN[v_lead_journey_flat]; SCORE[mv_lead_score]; VEC[lead_embedding]
end
ID --> PER & CO & SRC & INT & ENR & OPP & CON
ADDR --> SERV & CP
INT --> CH & AG & CMP
ORD --> OL --> PROD
A --> D
B --> D
C --> D
classDef hub fill:#2f6bff,color:#fff;erDiagram
identity_registry ||--o{ person : "1..*"
identity_registry ||--o{ source_crosswalk : "1..*"
identity_registry ||--o{ interaction : "1..*"
identity_registry ||--o{ enrichment : "1..*"
identity_registry ||--o{ opportunity : "1..*"
identity_registry ||--o{ consent : "1..*"
person ||--o{ person_company_employment : ""
company ||--o{ person_company_employment : ""
person ||--o{ person_address_residency : ""
address ||--o{ person_address_residency : ""
address ||--o{ address_serviceability : "1..*"
channel ||--o{ interaction : ""
agent ||--o{ interaction : ""
campaign ||--o{ interaction : ""
opportunity ||--o{ order : ""
order ||--o{ order_line : ""
product ||--o{ order_line : ""
identity_registry { uuid master_lead_id PK "immutable" text status uuid merged_into FK }
person { uuid person_id PK uuid master_lead_id FK text first_name text last_name }
source_crosswalk { bigint crosswalk_id PK uuid master_lead_id FK text source_system text source_id text source_type }
interaction { bigint interaction_id PK uuid master_lead_id FK int channel_id FK uuid agent_id FK timestamptz occurred_at }
enrichment { bigint enrichment_id PK uuid master_lead_id FK text enrichment_type text provider timestamptz occurred_at }
address_serviceability { bigint id PK uuid address_id FK text carrier bool fiber_available timestamptz checked_at }flowchart LR
S[Source row] --> N[Normalize + geo-guard]
N --> X{Match?}
X -- deterministic --> G[Attach to existing master_lead_id]
X -- probabilistic/borderline --> Q[match_review queue]
X -- no match --> C[Create new master_lead_id]
G & C --> SV[Survivorship → golden record]
SV --> EV[Append events: interaction / enrichment / order]
SV --> MV[Refresh serving: mv_lead_360 · journey · score · embedding]
MV --> ACT[Activate: Warroom · dialer · reverse-ETL · AI]
Q -. steward decision .-> SVflowchart TB
subgraph INPUTS[Golden + Events + Reference]
GR[golden_record_clean]; INT[interaction]; ENR[enrichment]; ORD[order]; SERV[address_serviceability]
end
GR --> F[mv_lead_360<br/>identity · geo · score]
INT --> J[v_lead_journey_flat<br/>1st–4th + last channel]
ENR --> LE[last-enrichment derive]
ORD --> LO[last-order derive]
SERV --> FB[fiber availability]
F & J & LE & LO & FB --> FLAT[v_lead_master_flat<br/>one row per lead · 34+ columns]
FLAT --> AI[LLM-safe view + pgvector] & EXP[CRM / export / BI]Components:
identity_registry,person,company,address,contact_point,source_crosswalk,agent,product,campaign,channel.
identity_registry), core entities (person,
company, address, contact_point),
dimensions (agent, product,
campaign, channel), and the
source_crosswalk bridge.identity_registry.master_lead_id
(or address_id); Serving projections read from it.identity_registry,
person, company, address,
contact_point, source_crosswalk,
agent, product, campaign,
channel (all master/dimension
tables).identity_registry(master_lead_id='7f3a…', status='active', primary_person_id='a91…')
→ person(first_name='Reginaldo', last_name='G.') →
source_crosswalk(source_system='instantly', source_id='998172', source_type='cold_email').merged_into set, audited, reversible.(source_system, source_id); deterministic before
probabilistic; human review for low-confidence merges; SCD2 with
valid_from/valid_to/is_current.GRANTs; column-level PII classification; masking for
non-privileged roles.golden_record_full.master_lead_id (uuid, gen_random_uuid()).
Exists so identity is generated once and never reused; merges recorded
via merged_into. Every other domain FKs to it. Apps treat
it as the lead ID; AI uses it as the join key for features.
Schema:
(master_lead_id uuid PK, status text, merged_into uuid FK, primary_person_id uuid, canonical_hash text, created_at, updated_at).(person_id uuid PK, master_lead_id uuid FK, first_name, last_name, full_name, valid_from, valid_to, is_current).(master_company_id uuid PK, legal_name, domain, industry, size_band, revenue_band, is_current).normalized_hash for dedup; the anchor
for serviceability.
(address_id uuid PK, line1, line2, city, state, postal_code, country, normalized_hash).(contact_point_id, master_lead_id FK, kind, value, status, source).(source_system, source_id, source_type) rows — the MDM
heart and dedup ledger.
(crosswalk_id bigint PK, master_lead_id FK, source_system, source_id, source_type, match_method, confidence).(agent_id uuid PK, full_name, email, role, team_id, territory_id, external_ids jsonb, status).(product_id PK, carrier, name, download_mbps, upload_mbps, monthly_price, term_months, offer_code).(campaign_id text PK, name, channel_id FK, kind, status, start_at, end_at).SMS, Email, Voice, Door, Web, Mail); one definition reused
by interaction + campaign.
(channel_id int PK, name, kind, provider).Components:
interaction,enrichment,opportunity,order,order_line,consent / DNC.
interaction
(touches), enrichment (data-provider events),
opportunity/order/order_line
(deal & revenue), consent/DNC (compliance state
changes).identity_registry; interactions FK to
channel/agent/campaign; order
lines FK to product. Serving derives “first/last/journey”
from these.interaction
(transaction, partitioned), enrichment
(transaction, partitioned), opportunity,
order, order_line, consent (all
transaction tables).interaction(master_lead_id='7f3a…', channel_id=1 /*SMS*/, agent_id='…', campaign_id='C2UUBR7', interaction_type='sms_out', occurred_at='2026-07-08T14:03Z', software_type='sms_platform').pg_partman auto-rollout; BRIN on time;
never mutate history — correct with compensating events.metadata → restricted; consent is a compliance-critical,
tamper-evident record.(master_lead_id, occurred_at DESC) for per-lead reads;
covering indexes for hot analytic slices.occurred_at). Every cross-channel touch. Exists to
hold the unbounded journey that the flat design could not. Relates to
identity + channel + agent + campaign. Apps read the timeline; AI
derives recency/frequency/sequence features; the journey view pivots the
first N + last touches from it. Adds agent_login +
software_type (dialer/warroom/sms_platform) for the
requested agent-activity trail. Schema:
(interaction_id bigint, master_lead_id uuid FK, channel_id int FK, agent_id uuid FK, campaign_id text FK, direction, interaction_type, disposition, agent_login, software_type, occurred_at timestamptz, metadata jsonb, PRIMARY KEY(interaction_id, occurred_at)) PARTITION BY RANGE(occurred_at).max(occurred_at) derivation.
(enrichment_id, master_lead_id FK, enrichment_type, provider, source_type, status, occurred_at, detail jsonb).(opportunity_id uuid PK, lead_id FK, person_id FK, account_id FK, product_id FK, owner_agent_id FK, campaign_id FK, stage, amount).(order_id uuid PK, account_id FK, opportunity_id FK, agent_id FK, status, total_amount, ordered_at, install_date).product. Normalises
multi-product orders.
(order_line_id PK, order_id FK, product_id FK, qty, unit_price).(consent_id, master_lead_id FK, person_id FK, channel_id FK, status, basis, effective_at).Component:
address_serviceability(AT&T, Kinetic, Spectrum, …).
address_id; temporal (checked_at);
multi-carrier rows; source-attributed; curated (validated before
trust).address_serviceability, reconciling the existing
ulda_ref.v_address_serviceability view + the
SARA/Kinetic/FCC checkers.(address, carrier, fiber_available, technology, max_download_mbps, checked_at, source).ulda_master.address; consumed by Serving to surface “Fiber
Availability” on Lead-360 and to prioritise dialer/canvass lists.address_serviceability
(reference table);
v_address_serviceability (standard
view).(address_id='…', carrier='att', fiber_available=true, technology='fiber', max_download_mbps=5000, source='sara', checked_at='2026-07-10').(address_id) and
(carrier, fiber_available).checked_at freshness; supersede rather than overwrite.address_id.address; consumed by Lead-360 and
lead routing. Apps show availability; AI can feature-encode
“fiber_available_att” etc.
(serviceability_id bigint PK, address_id uuid FK, carrier text, fiber_available bool, technology text, max_download_mbps int, checked_at timestamptz, source text, UNIQUE(address_id, carrier, checked_at)).Components:
mv_lead_360,v_lead_journey_flat,mv_lead_score,lead_embedding(pgvector).
CONCURRENTLY (no read
blocking); PII-masked for AI; consent-gated for activation.mv_lead_360
(profile), v_lead_journey_flat (timeline pivot),
mv_lead_score (scoring), lead_embedding
(semantic vectors), analytics MVs, activation views.mv_lead_360,
mv_lead_score, mv_analytics_*,
lead_embedding (materialised views / derived
tables); v_lead_journey_flat,
v_lead_master_flat, v_lead_llm_safe
(standard views).mv_lead_360(master_lead_id='7f3a…', name='Reginaldo G.', street='7602 riptide dr', state='TX', zip='77072', completeness=0.7, source_systems={instantly}, source_count=1).REFRESH MATERIALIZED VIEW CONCURRENTLY mv_lead_360 →
apps/AI read the fresh profile; nightly analytics refresh; batch
embedding on the replica.ulda_web read-only;
v_lead_llm_safe strips direct PII before prompts;
activation is consent-gated.CONCURRENTLY refresh; pre-warmed caches; read-replica
offload.lead_embedding; new activation destinations.golden_record_clean; refreshed CONCURRENTLY.
Apps render it; AI joins features to it. Example schema:
(master_lead_id uuid, name, first_name, last_name, company, email, phone, street, city, state, zip, completeness numeric, source_systems text[], source_count int).interaction into
1st/2nd/3rd/4th channel + date + campaign and
last channel/date via window functions — your requested
columns, computed not stored, uncapped. Apps/exports read it; analytics
measures multi-touch paths. Materialise if it becomes hot.model_registry,
score_run), so an ML model can replace the heuristic with
no consumer change. Apps prioritise by tier; AI consumes score as a
feature.semantic_search()). Exists to
power similarity, dedup assistance, and RAG. AI uses it directly; the
mass-embed runs on the replica/idle to protect the primary.
(master_lead_id uuid PK, embedding vector(384)) +
HNSW.v_lead_master_flat) joins mv_lead_360 ⋈
v_lead_journey_flat ⋈ last-enrichment ⋈ last-order ⋈
serviceability → every requested column from one read, always
correct.lead_embedding +
HNSW for semantic search; v_lead_llm_safe for prompt-safe
context; model_registry for swapping heuristic scoring with
trained models; append-only events are ideal ML training data.mv_analytics_by_state/tier/source,
KPI board) refreshed off the OLTP path.It exhibits the defining properties of enterprise data platforms: a single source of truth with immutable identity; complete lineage and auditability; separation of concerns by change-velocity; governed metrics and PII/consent controls; zero-downtime, reversible evolution; and independent scalability of identity vs activity vs serving. It is not a schema — it is a governed platform with an ingestion contract, a resolution engine, a serving layer, and a governance spine.
Traditional CRMs centre on a wide
Contact/Lead table with bolted-on activity
tables and app-enforced dedup. ULDA inverts this: identity is
resolved and mastered (not assumed), activity is
event-sourced (not last-value), serving is
materialised (not live-joined on every page), and governance is
first-class (not a spreadsheet). The result is better dedup,
real history, cleaner AI, and a genuine path to a billion rows.
interaction & enrichment
partitioned append-only now (they are empty — ideal
timing).interaction from
disposition_event + Warroom SMS/dialer/reply activity — the
one data task that lights up the journey/agent history.pg_partman) and retention.timezone attribute (derived from
state/ZIP) for quiet-hours dialing.household, account) — B2B and residential
rollups.ulda_gov.The four-domain design is the correct enterprise architecture for this problem. Implemented on one PostgreSQL database with five schemas and a future read replica, it is scalable to millions of leads today and to a billion with the documented partitioning/replica path — while remaining maintainable, AI-ready, and production-safe. The primary remaining work is data population (interaction backfill) and read scale-out (replica), not redesign.