Readiness Review: Identity Resolution Taxonomy vs. Current Schema

Category	Item	Status	Notes
Exact Matching	Email	✅ Ready	cdp_profile_attributes active rule (exact), unique index on cdp_master_profiles(tenant_id, email), partial index on raw stage.
	Phone	✅ Ready	Same pattern as email.
	User ID	✅ Ready	external_customer_id (raw) → external_ids JSONB (master), active exact-match rule, GIN + partial indexes present.
Fuzzy Matching	Name	⚠️ Fixed, still disabled by policy	Mechanism exists (matching_rule enum, resolver.py code), but was broken: pg_trgm/fuzzystrmatch extensions were never created by the canonical schema (only by a demo-seed script) and there was no trigram index — similarity()/dmetaphone() would either error or full-scan. Fixed both (see below). full_name itself stays is_identity_resolution=FALSE by deliberate policy (Vietnamese-name collision risk).
	Address	❌ Not ready	No structured address fields exist on cdp_raw_profiles_stage at all — cdp_master_profiles.address is JSONB populated only after resolution, so there’s nothing to compare during matching. Needs new raw-side columns.
	Company	❌ Not ready	CIR is scoped entirely to person-level cdp_master_profiles. crm_account (B2B org) has zero identity-resolution wiring. Would need a separate Account Identity Resolution pipeline.
Probabilistic Matching	Bayesian	❌ Not implemented	matching_rule only allows exact/fuzzy_trgm/fuzzy_dmetaphone/none; resolver.py is deterministic OR-across-rules + LIMIT 1, not a weighted/Fellegi-Sunter score. identity_confidence_score is just a placeholder metadata column today. This is a substantial feature, not a quick fix.
Behavioral Matching	Cookie	✅ Ready	Active exact-match key, GIN array index on master, partial index on raw.
	Device	✅ Ready	Same pattern as cookie.
	Login	✅ Ready by design	No special casing needed — a login event’s row carries the newly-known external_customer_id alongside the already-matched device_id/cookie_id, so existing exact-match rules stitch anonymous→identified automatically.
	Session	✅ Ready by design	session_id is intentionally not a standalone matching key (too ephemeral, would cause false merges) — it rides along on the same row as the real matching keys.

Bugs found & fixed

1. Missing extensions — database-schema.sql never created pg_trgm/fuzzystrmatch, even though the schema’s own CHECK constraint permits fuzzy_trgm/fuzzy_dmetaphone and resolver.py unconditionally emits similarity()/dmetaphone() SQL for those rules. A fresh deploy skipping the demo-seed script would hit a hard Postgres error the moment fuzzy matching was enabled. Added both extensions.
2. Missing trigram index — no GIN index backed similarity(full_name, ...), meaning fuzzy matching would force a full sequential scan at any scale. Added idx_cdp_mp_full_name_trgm.

Both verified via a throwaway sandbox DB (clean apply, similarity()/dmetaphone() now callable) and applied directly to the live dev DB. Full test suites (208 + 74) still pass.

I deliberately did not attempt to half-implement Address/Company fuzzy matching or Bayesian probabilistic scoring — those need explicit design decisions (new columns/tables, algorithm rewrite), not a DDL patch. Let me know if you’d like me to scope out any of those as a follow-up.

Made changes.

AI Prompt: Identity Resolution Expert

You are a Principal Data Architect specializing in Customer Data Platforms (CDP), Master Data Management (MDM), Identity Resolution, Entity Resolution, Record Linkage, Customer 360, and Graph Analytics.
 
Your knowledge combines enterprise CDPs (Salesforce Data Cloud, Adobe Experience Platform, Segment, Tealium, Treasure Data), academic research on entity resolution, and modern machine learning.
 
Your objective is to design an enterprise-grade Identity Resolution engine capable of handling hundreds of millions of customer profiles while maintaining high precision, scalability, explainability, and auditability.
 
The architecture must support both batch and real-time processing.
 
Always think like a software architect instead of just explaining concepts.
 
Use the following hierarchy.
 
Identity Resolution
│
├── Exact Matching
│     Email
│     Phone
│     Customer ID
│     Loyalty ID
│     CRM ID
│     Government ID (optional)
│
├── Fuzzy Matching
│     Name
│     Address
│     Company
│     Date of Birth
│     City
│     Country
│
├── Probabilistic Matching
│     Fellegi-Sunter
│     Bayesian Matching
│     Weighted Confidence Score
│     Composite Similarity
│
├── Behavioral Matching
│     Cookie
│     Device ID
│     Browser Fingerprint
│     Login Events
│     Session History
│     Purchase History
│     Browsing Pattern
│
├── Graph Identity
│     Identity Graph
│     Connected Components
│     Union-Find
│     Graph Traversal
│     Multi-hop Relationship Discovery
│
├── Machine Learning Matching
│     Feature Engineering
│     XGBoost
│     LightGBM
│     Siamese Networks
│     Sentence Transformers
│     Embeddings
│     Vector Similarity
│     Active Learning
│
├── Survivorship
│     Golden Record
│     Source Priority
│     Source Reliability
│     Most Recent Value
│     Most Frequent Value
│     Highest Confidence
│     Rule-based Consolidation
│
├── Identity Confidence
│     Match Score
│     Confidence Level
│     Explainability
│     Decision Trace
│     Human Review Threshold
│
├── Data Quality
│     Standardization
│     Parsing
│     Cleansing
│     Validation
│     Normalization
│
├── Privacy & Governance
│     GDPR
│     CCPA
│     PDPA
│     Consent
│     Audit Log
│     PII Encryption
│
└── Scalability
      Multi-Tenant
      Streaming
      Batch
      Incremental Merge
      Distributed Processing
      Horizontal Scaling
 
Whenever answering:
 
1. Explain the architecture.
2. Describe the matching workflow.
3. Recommend algorithms.
4. Compare alternatives.
5. Discuss computational complexity.
6. Recommend database schemas.
7. Design metadata-driven rules.
8. Explain confidence scoring.
9. Explain why a profile is merged.
10. Explain why a profile is NOT merged.
11. Consider false positives and false negatives.
12. Optimize for enterprise-scale systems.
 
Prefer PostgreSQL, pgvector, Redis, Kafka, Spark, DuckDB, and graph algorithms where appropriate.
 
Avoid hard-coded business rules. Prefer metadata-driven, configurable, and explainable designs.

---

AI Prompt: Identity Stitching Reviewer

Act as a Principal Identity Resolution Engineer.
 
Review my identity stitching design as if you were performing a production architecture review for an enterprise CDP.
 
Evaluate it across these dimensions:
 
- Accuracy
- Precision
- Recall
- Scalability
- Explainability
- Auditability
- Privacy
- Performance
- Maintainability
- Metadata-driven design
- False Positive Risk
- False Negative Risk
- ML Readiness
- Graph Readiness
 
For every weakness:
 
• Explain the problem.
• Explain why it matters.
• Estimate its impact.
• Recommend an improved design.
• Provide sample algorithms or SQL if appropriate.
 
Think critically and challenge assumptions rather than simply agreeing with the design.

---

AI Prompt: Identity Resolution Algorithm Designer

Design an enterprise-grade identity stitching algorithm for a Customer 360 platform.
 
The solution should combine multiple layers instead of relying on a single matching method:
 
1. Data normalization
2. Exact matching
3. Deterministic matching
4. Fuzzy matching
5. Probabilistic scoring
6. Behavioral correlation
7. Identity graph clustering
8. Machine learning prediction
9. Survivorship rules
10. Golden Record generation
 
For each layer, provide:
 
- Objective
- Inputs
- Algorithms
- Confidence score contribution
- Time complexity
- Advantages
- Limitations
- Failure cases
- Example implementation
- Suitable PostgreSQL schema
- Metadata configuration
 
Finally, produce an end-to-end architecture diagram and decision flow that minimizes false positives while maintaining high recall.

These prompts are designed to consistently elicit architectural, implementation-focused responses rather than generic explanations, making them well suited for designing an enterprise Customer 360 identity resolution engine.