ULDA v6.0 — Live Database Walkthrough

Generated 2026-07-16 from live read-only introspection of leads_db. Sample rows are masked (names→initials, emails/phones redacted). Legacy public.leads is untouched.

1,064,189

golden identities

ULDA schemas

116

objects (tables/views/MVs)

primary keys

foreign keys

150

indexes

1 · The five schemas

Schema	Tables	Views	MViews	Purpose
ulda_events	10	1	0	Append-only event ledger (what HAPPENED — dispositions, comms, activity), monthly-partitioned.
ulda_gov	18	7	0	Governance: data-quality findings, match review, consent, lineage, decommission control plane.
ulda_master	43	3	0	Golden identity spine + mastered business entities (the source of truth for WHO a lead is).
ulda_ref	1	4	0	Reference data as zero-movement curated views (serviceability, geo) — shared, effective-dated.
ulda_serve	10	14	4	Serving/AI layer: Lead-360, feature store, scoring, pgvector search, metrics, reverse-ETL.

2 · How data flows

  SOURCES                RESOLUTION                 GOLDEN SPINE (ulda_master)
  public.leads  ─┐
  b2c           ─┼─► norm_email/phone/name ─► source_crosswalk ─► identity_registry
  ad_leads      ─┘     (blocking + det/prob      (source_id ↦        (master_lead_id = the
                        + pgvector match)          master_lead_id)     universal key)
                                                        │
        borderline ─► ulda_gov.match_review             ├─► person        (golden, survivorship)
        defects    ─► ulda_gov.dq_finding               ├─► contact_point (emails/phones)
                                                        ├─► lead / company / address
                                                        └─► consent  (TCPA gate)
                                                        │
  live writes ─► trg_leads_dualwrite ─► ulda_gov.change_outbox ─► drain worker
                                                        │
                                       ulda_serve: mv_lead_360 · features · score · pgvector · metrics
                                                        └─► reverse-ETL ─► operational apps

One master_lead_id ties together every source row, the golden person, all contact points, the lead state, and consent. Everything downstream keys off it.

3 · Core tables — structure & masked samples

ulda_master.identity_registry 1,064,189 rows

The registry — one row = one real person/lead. `master_lead_id` is the universal key everything hangs off.

Column	Type
master_lead_id PK	uuid	NOT NULL
status	text	NOT NULL
merged_into	uuid
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
primary_person_id	uuid
canonical_hash	text

PK: PRIMARY KEY (master_lead_id) · 2 FK · 2 idx

master_lead_id	status	primary_person_id	canonical_hash	created_at
cb10be81…	active	…	…	2026-07-15T13:33:25+02:00
93495702…	active	…	…	2026-07-16T14:18:13+02:00
000008eb…	active	bbbcfc2a…	874b94b20f…	2026-07-13T19:49:00+02:00
00000b61…	active	1f55d5c4…	d75e9493fb…	2026-07-13T19:49:00+02:00
00005ad6…	active	5b4ee03b…	5b67ddd372…	2026-07-13T19:49:00+02:00
0000753a…	active	5159e6ad…	28bd7afece…	2026-07-13T19:49:00+02:00

ulda_master.source_crosswalk 1,074,066 rows

Maps every source row (leads/b2c/ad_leads) to its master_lead_id. UNIQUE(source_system,source_id) prevents double-mastering.

Column	Type
crosswalk_id PK	bigint	NOT NULL
master_lead_id	uuid	NOT NULL
source_system	text	NOT NULL
source_id	text	NOT NULL
match_method	text
confidence	numeric
created_at	timestamp with time zone	NOT NULL

PK: PRIMARY KEY (crosswalk_id) · 1 FK · 3 idx

crosswalk_id	master_lead_id	source_system	source_id	match_method	confidence
1	1d775bc4…	leads	***024	deterministic	1.0
2	6c62b357…	leads	***899	deterministic	1.0
3	b9f3131c…	leads	***418	deterministic	1.0
4	54caaed3…	leads	***047	deterministic	1.0
5	bf4b8307…	leads	***159	deterministic	1.0
6	741ae347…	leads	***083	deterministic	1.0

ulda_master.person 1,064,187 rows

Golden person record (survivorship-picked), bitemporal (valid_from/valid_to/is_current).

Column	Type
person_id PK	uuid	NOT NULL
master_lead_id	uuid	NOT NULL
full_name	text
first_name	text
last_name	text
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
valid_from	timestamp with time zone
valid_to	timestamp with time zone
is_current	boolean	NOT NULL
system_from	timestamp with time zone	NOT NULL

PK: PRIMARY KEY (person_id) · 1 FK · 2 idx

person_id	master_lead_id	name_masked	is_current	valid_from
53d3999f…	ca6c9265…		True	2026-07-14T08:38:19+02:00
1105e43d…	0343447e…	A. E.	True	2026-07-14T08:38:19+02:00
912dba1e…	07f4e31d…		True	2026-07-14T08:38:19+02:00
acba1e4b…	03f48434…		True	2026-07-14T08:38:19+02:00
3b289c72…	0098e241…	K. M.	True	2026-07-14T08:38:19+02:00
eb9de5fa…	02fd43d4…	E. G.	True	2026-07-14T08:38:19+02:00

ulda_master.contact_point 1,875,193 rows

All emails/phones per identity, normalized, primary-flagged.

Column	Type
contact_point_id PK	uuid	NOT NULL
master_lead_id	uuid	NOT NULL
kind	text	NOT NULL
value	text	NOT NULL
value_norm	text
is_primary	boolean
created_at	timestamp with time zone	NOT NULL
valid_from	timestamp with time zone
valid_to	timestamp with time zone
is_current	boolean	NOT NULL
system_from	timestamp with time zone	NOT NULL

PK: PRIMARY KEY (contact_point_id) · 1 FK · 3 idx

master_lead_id	kind	value_masked	is_primary	is_current
6a292b10…	email	j***@prol.ws	True	True
c7c7fadb…	email	y***@	True	True
0ae0d444…	email	n***@wisc.edu	True	True
012d258b…	email	r***@	True	True
0918b9f2…	email	g***@	True	True
0e132a07…	email	d***@mvn.com	True	True
0e80f352…	email	k***@cco.com	True	True
148820d9…	email	e***@	True	True

ulda_master.lead 1,064,187 rows

The lead/opportunity state per identity, bitemporal.

Column	Type
lead_id PK	uuid	NOT NULL
master_lead_id	uuid	NOT NULL
status	text
stage	text
source_system	text
created_at	timestamp with time zone	NOT NULL
updated_at	timestamp with time zone	NOT NULL
valid_from	timestamp with time zone
valid_to	timestamp with time zone
is_current	boolean	NOT NULL
system_from	timestamp with time zone	NOT NULL

PK: PRIMARY KEY (lead_id) · 1 FK · 2 idx

lead_id	master_lead_id	source_system	created_at
70286374…	1d775bc4…	leads	2026-07-14T08:50:08+02:00
a7cc020d…	6c62b357…	leads	2026-07-14T08:50:08+02:00
2399f935…	b9f3131c…	leads	2026-07-14T08:50:08+02:00
91d42e29…	54caaed3…	leads	2026-07-14T08:50:08+02:00
d71fb5db…	bf4b8307…	leads	2026-07-14T08:50:08+02:00
2ef6db6e…	741ae347…	leads	2026-07-14T08:50:08+02:00

4 · Full object inventory (per schema)

ulda_events

Object	Kind	Rows	Idx
disposition_event	part	—	2
disposition_event_2026_05	tbl	—	2
disposition_event_2026_06	tbl	≈5,639	2
disposition_event_2026_07	tbl	≈2,704	2
disposition_event_2026_08	tbl	—	2
disposition_event_2026_09	tbl	—	2
disposition_event_2026_10	tbl	—	2
disposition_event_2026_11	tbl	—	2
disposition_event_2026_12	tbl	—	2
disposition_event_2027_01	tbl	—	2
disposition_event_default	tbl	—	2
v_events_health	view	—	0

ulda_gov

Object	Kind	Rows	Idx	FK
attribute_lineage	tbl	—	1	0
catalog_dataset	tbl	—	1	0
change_outbox	tbl	≈6	2	0
consent_key	tbl	—	1	1
data_catalog	tbl	—	1	0
decommission_bake	tbl	—	1	0
decommission_log	tbl	—	1	0
decommission_registry	tbl	—	1	0
dq_finding	tbl	≈189,407	1	0
dualwrite_error	tbl	—	1	0
ir_audit	tbl	—	2	0
ir_run	tbl	—	1	0
legacy_dependency	tbl	—	2	0
llm_guardrail	tbl	—	1	0
match_review	tbl	≈302,159	1	0
pii_classification	tbl	—	1	0
recon_baseline	tbl	—	1	0
retention_policy	tbl	—	1	0
v_decommission_status	view	—	0	0
v_dependency_resolution_plan	view	—	0	0
v_ir_reconciliation	view	—	0	0
v_legacy_dependency_drift	view	—	0	0
v_pii_summary	view	—	0	0
v_registry_integrity	view	—	0	0
v_ulda_health	view	—	0	0

ulda_master

Object	Kind	Rows	Idx	FK
account	tbl	≈440,940	1	2
address	tbl	440,940	3	0
agent	tbl	—	4	2
agent_territory	tbl	—	1	2
blocking_key	tbl	≈3,275,511	3	0
campaign	tbl	≈66	1	1
campaign_membership	tbl	≈454,731	2	2
case	tbl	—	3	5
channel	tbl	—	2	0
company	tbl	668,504	2	0
consent	tbl	8,077	4	3
contact_point	tbl	1,875,193	3	1
document	tbl	—	2	3
golden_field	tbl	≈6,965,040	1	0
golden_record_clean	view	—	0	0
golden_record_full	tbl	≈1,063,085	2	1
golden_record_legacy_deprecated	tbl	≈1,064,187	1	1
household	tbl	≈440,940	1	1
identity_key	tbl	≈1,064,187	1	0
identity_registry	tbl	1,064,189	2	2
interaction	tbl	—	3	5
ir_attr	tbl	≈1,074,306	3	0
ir_block_master	tbl	≈2,437,854	1	0
ir_eligible_block	tbl	≈228,227	1	0
ir_emb_scope	tbl	≈84,334	0	0
ir_master_attr	tbl	≈1,064,187	1	0
ir_master_key	tbl	≈1,064,187	1	0
ir_pair	tbl	≈1,571,455	0	0
ir_pair_scored	tbl	≈1,571,455	0	0
ir_signal	tbl	≈1,074,064	2	0
lead	tbl	1,064,187	2	1
match_candidate	tbl	—	3	0
merge_log	tbl	—	2	0
opportunity	tbl	—	4	6
order	tbl	—	3	3
order_line	tbl	—	3	2
person	tbl	1,064,187	2	1
person_address_residency	tbl	≈664,772	1	2
person_company_employment	tbl	≈912,081	2	2
product	tbl	—	2	0
source_crosswalk	tbl	1,074,066	3	1
survivorship_rule	tbl	—	1	0
team	tbl	—	2	2
territory	tbl	—	2	0
v_consent_gate	view	—	0	0
v_master_entities	view	—	0	0

ulda_ref

Object	Kind	Rows	Idx
reference_catalog	tbl	—	1
v_address_coverage_360	view	—	0
v_address_serviceability	view	—	0
v_fcc_coverage	view	—	0
v_geo_address_range	view	—	0

ulda_serve

Object	Kind	Rows	Idx	FK
consumer_cutover	tbl	—	1	0
consumer_cutover_log	tbl	—	1	0
lead_embedding	tbl	≈200	2	1
metric_catalog	tbl	—	1	0
model_registry	tbl	—	1	0
mv_lead_360	mv	≈1,064,188	6	0
mv_lead_features	mv	≈1,064,187	2	0
mv_lead_score	mv	≈1,064,187	2	0
mv_metrics	mv	≈12	1	0
mv_refresh_log	tbl	≈107	2	0
reverse_etl_run	tbl	—	1	1
reverse_etl_target	tbl	—	1	0
score_run	tbl	—	1	1
serving_sla	tbl	—	1	0
v_activation_360	view	—	0	0
v_activation_consented	view	—	0	0
v_activation_high_intent	view	—	0	0
v_analytics_by_source	view	—	0	0
v_analytics_by_state	view	—	0	0
v_analytics_by_tier	view	—	0	0
v_analytics_kpi	view	—	0	0
v_cutover_status	view	—	0	0
v_lead_360	view	—	0	0
v_lead_embedding_input	view	—	0	0
v_lead_llm_safe	view	—	0	0
v_lead_score	view	—	0	0
v_metrics	view	—	0	0
v_serving_health	view	—	0	0

5 · Relationships (foreign keys)

55 FKs enforce referential integrity across the model. Every master entity ultimately references identity_registry(master_lead_id).

Table	Constraint
ulda_gov.consent_key	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.account	FOREIGN KEY (master_company_id) REFERENCES ulda_master.company(master_company_id)
ulda_master.account	FOREIGN KEY (household_id) REFERENCES ulda_master.household(household_id)
ulda_master.agent	FOREIGN KEY (territory_id) REFERENCES ulda_master.territory(territory_id)
ulda_master.agent	FOREIGN KEY (team_id) REFERENCES ulda_master.team(team_id)
ulda_master.agent_territory	FOREIGN KEY (agent_id) REFERENCES ulda_master.agent(agent_id) ON DELETE CASCADE
ulda_master.agent_territory	FOREIGN KEY (territory_id) REFERENCES ulda_master.territory(territory_id) ON DELETE CASCADE
ulda_master.campaign	FOREIGN KEY (channel_id) REFERENCES ulda_master.channel(channel_id)
ulda_master.campaign_membership	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.campaign_membership	FOREIGN KEY (campaign_id) REFERENCES ulda_master.campaign(campaign_id)
ulda_master.case	FOREIGN KEY (lead_id) REFERENCES ulda_master.lead(lead_id)
ulda_master.case	FOREIGN KEY (assigned_agent_id) REFERENCES ulda_master.agent(agent_id)
ulda_master.case	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.case	FOREIGN KEY (account_id) REFERENCES ulda_master.account(account_id)
ulda_master.case	FOREIGN KEY (channel_id) REFERENCES ulda_master.channel(channel_id)
ulda_master.consent	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.consent	FOREIGN KEY (channel_id) REFERENCES ulda_master.channel(channel_id)
ulda_master.consent	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.contact_point	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.document	FOREIGN KEY (account_id) REFERENCES ulda_master.account(account_id)
ulda_master.document	FOREIGN KEY (opportunity_id) REFERENCES ulda_master.opportunity(opportunity_id)
ulda_master.document	FOREIGN KEY (order_id) REFERENCES ulda_master."order"(order_id)
ulda_master.golden_record_full	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.golden_record_legacy_deprecated	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.household	FOREIGN KEY (master_address_id) REFERENCES ulda_master.address(address_id)
ulda_master.identity_registry	FOREIGN KEY (merged_into) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.identity_registry	FOREIGN KEY (primary_person_id) REFERENCES ulda_master.person(person_id)
ulda_master.interaction	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.interaction	FOREIGN KEY (campaign_id) REFERENCES ulda_master.campaign(campaign_id)
ulda_master.interaction	FOREIGN KEY (channel_id) REFERENCES ulda_master.channel(channel_id)
ulda_master.interaction	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.interaction	FOREIGN KEY (agent_id) REFERENCES ulda_master.agent(agent_id)
ulda_master.lead	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.opportunity	FOREIGN KEY (account_id) REFERENCES ulda_master.account(account_id)
ulda_master.opportunity	FOREIGN KEY (campaign_id) REFERENCES ulda_master.campaign(campaign_id)
ulda_master.opportunity	FOREIGN KEY (lead_id) REFERENCES ulda_master.lead(lead_id)
ulda_master.opportunity	FOREIGN KEY (owner_agent_id) REFERENCES ulda_master.agent(agent_id)
ulda_master.opportunity	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.opportunity	FOREIGN KEY (product_id) REFERENCES ulda_master.product(product_id)
ulda_master.order	FOREIGN KEY (agent_id) REFERENCES ulda_master.agent(agent_id)
ulda_master.order	FOREIGN KEY (account_id) REFERENCES ulda_master.account(account_id)
ulda_master.order	FOREIGN KEY (opportunity_id) REFERENCES ulda_master.opportunity(opportunity_id)
ulda_master.order_line	FOREIGN KEY (product_id) REFERENCES ulda_master.product(product_id)
ulda_master.order_line	FOREIGN KEY (order_id) REFERENCES ulda_master."order"(order_id) ON DELETE CASCADE
ulda_master.person	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.person_address_residency	FOREIGN KEY (address_id) REFERENCES ulda_master.address(address_id)
ulda_master.person_address_residency	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.person_company_employment	FOREIGN KEY (person_id) REFERENCES ulda_master.person(person_id)
ulda_master.person_company_employment	FOREIGN KEY (master_company_id) REFERENCES ulda_master.company(master_company_id)
ulda_master.source_crosswalk	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_master.team	FOREIGN KEY (manager_agent_id) REFERENCES ulda_master.agent(agent_id) ON DELETE SET NULL
ulda_master.team	FOREIGN KEY (parent_team_id) REFERENCES ulda_master.team(team_id)
ulda_serve.lead_embedding	FOREIGN KEY (master_lead_id) REFERENCES ulda_master.identity_registry(master_lead_id)
ulda_serve.reverse_etl_run	FOREIGN KEY (target) REFERENCES ulda_serve.reverse_etl_target(target)
ulda_serve.score_run	FOREIGN KEY (model_id) REFERENCES ulda_serve.model_registry(model_id)

6 · ULDA vs the original public.leads

Original · public.leads

1 flat table, ~995K rows, raw as-ingested
Same person = many unrelated rows
Mixed formats, defects inline, no dedup
No consent/TCPA gate, no governance
Direct table reads by apps
Untouched & authoritative — byte-for-byte preserved

New · ULDA golden layer

5 schemas, 116 objects, 1.06M deduplicated golden identities
One master_lead_id per real person
Normalized + survivorship-picked values, bitemporal history
Consent gate (8,077 blocked), DQ findings (189K), match review (302K)
Lead-360 + feature store + scoring + pgvector search
Additive & reversible — built alongside, legacy never modified

Key point: ULDA did not rewrite public.leads. It built a clean, mastered layer beside it — dedup ratio 1.0093 (1,074,066 source rows → 1,064,189 identities), email rate 98.35%, contactable 100%, all invariants 0/0/0.

Interactive explorer (search any lead, run read-only SQL): ULDA Live Explorer →