Data Synchronization and Integration Consistency Questions

Requirements & constraints:

• Reduce duplicate contact creation across integration between marketing automation (MA) and CRM despite eventual consistency.
• Reconcile existing duplicates and prevent new ones.
• Low latency for writes, human review for ambiguous merges, auditability.

High-level architecture:

• Central Identity Resolution Service (IRS) + Deduplication Queue + Merge Worker + Manual Review UI + Source adapters for MA and CRM.

Data flow:

1. Source emits contact event -> event bus.
2. IRS receives, computes canonical candidate keys, checks index, enqueues create/update to Deduplication Queue.
3. Merge Worker applies deterministic rules; if score > high threshold => auto-merge; if between thresholds => create "possible duplicate" and route to Manual Review UI; if < low threshold => create new contact and index identity.
4. Post-merge, publish canonical id back to sources.

Identity resolution strategies:

• Deterministic keys: email (normalized), phone (E.164), external IDs.
• Probabilistic scoring: weighted features (name similarity, email domain, IP, company, behavioral signals) via a ML model updated periodically.
• Composite canonical id mapping (one-to-many historical IDs).

Merging workflows:

• Auto-merge for high-confidence matches; merge is idempotent, transactional, with change diffs logged.
• Soft-delete duplicates (link to canonical) rather than hard delete.
• Field-level merge policies (most-recent, confidence-weighted, source-priority).

Human-in-the-loop:

• Review UI shows side-by-side records, merge preview, suggested winner fields, risk score, undo.
• Alerts only for mid-confidence cases or when key fields conflict (e.g., different emails).
• Timeboxed SLA for reviewers; allow supervisory override.

Trade-offs:

• Strict uniqueness (aggressive merging) reduces duplicates but risks false merges hurting data quality/business logic.
• Conservative approach leaves duplicates but preserves safety; costs downstream (marketing noise).
• Use hybrid: deterministic blocking on high-precision keys (email/domain) + ML scoring for candidates, manual review for ambiguous. Monitor precision/recall, tune thresholds, keep audit logs and easy rollback.

Operational concerns:

• Index stores (Elasticsearch or DynamoDB) for fast lookups, CDC for source sync, replayable event logs, testing with shadowing to measure impact before rollout.

Requirements clarified:

• Near‑real‑time propagation to internal services + third‑party integrations
• Guaranteed delivery with retries
• Privacy/consent enforcement per user and destination
• Full audit trail of what changed and where it was delivered
• Support reconciliation for missed/failed deliveries

High-level design:

• Source of truth DB emits CDC events (Debezium/Kafka Connect) into a durable event bus (Kafka or Pulsar).
• A Profile Sync Service (consumers) subscribes to change-topic, enriches with consent/policy metadata from Consent Service, and writes normalized Change Events to a durable outbound queue per destination (internal or third‑party).
• Delivery Workers pick from per-destination queues and deliver via adapters (REST/webhook/SDK). Each delivery is recorded in a Delivery Log service and an Audit Store.

Key components and responsibilities:

1. CDC layer: reliable, ordered change stream, includes before/after image, timestamp, and version.
2. Consent & Policy Service: fast read cache (Redis) keyed by user to evaluate per-destination consent; used before enqueuing deliveries.
3. Profile Sync Service: transforms events to destination schema, filters by consent, and assigns idempotency keys (user_id + field_version + destination).
4. Durable outbound queues: per-destination partitioning for parallelism and backpressure handling.
5. Delivery Workers: retry with exponential backoff, circuit breaker, rate limiting per destination, and record each attempt to Delivery Log.
6. Audit Store: append-only store (immutable events) capturing change, destinations attempted, outcomes, timestamps, and payload hashes for verification.
7. Monitoring/Alerting: dashboards for lag, error rates, DLQ size.

Guaranteed delivery & retries:

• At-least-once semantics via durable queues and persistent offsets; idempotency keys on receivers ensure safe deduplication.
• Retry strategy: immediate retries for transient (5xx, network), exponential backoff with jitter, and move to DLQ after configurable attempts.
• Persistent Delivery Log stores attempt metadata; webhook responses saved to enable dispute resolution.

Privacy & consent enforcement:

• Enforce at sync service: consult Consent Service before enqueueing; redact or omit fields per destination policy; encrypt sensitive fields in transit and at rest.
• Support runtime revocation: a “revoke” CDC event triggers compensating deletes/notifications to destinations capable of honoring erasure requests; audit marks when erasure propagated.

Auditability:

• Store immutable Audit Records containing: change_id, user_id, before/after diff, fields sent, destination, payload hash, timestamp, delivery attempts, final status.
• Provide query APIs and exports for compliance, and signed event receipts for third parties.

Error handling & reconciliation:

• Classify errors: transient vs permanent. Transient => retry; Permanent (4xx/validation) => notify owner, optionally transform schema or disable destination for user.
• Reconciliation job: periodic compare between source snapshot and last successful Audit Record per destination. It computes diffs and enqueues corrective Change Events (full snapshot or diff) for any divergence.
• On startup/lag detection: run targeted backfill using CDC log retention or full snapshot+compare if CDC gap exceeded.

Trade-offs and considerations:

• Exactly-once is costly; use at-least-once + idempotency for practical guarantees.
• Per-destination queues increase isolation but more operational overhead.
• Ensure consent checks are fast and strongly consistent; consider versioned consent and conservative behavior (require explicit allow).

Metrics to monitor:

• End‑to‑end latency, delivery success rate, DLQ size, reconciliation delta count, consent violation attempts.

This design provides near‑real‑time propagation, resilient delivery with retries and DLQ, strict consent enforcement, and full auditability plus reconciliation mechanisms for eventual consistency.

The transactional outbox pattern ensures events are emitted reliably by writing them to an "outbox" table inside the same DB transaction that mutates application state; a separate process then reliably publishes outbox rows to the message broker.

Pattern summary:

• Inside a single DB transaction: update domain tables + insert a row into outbox (payload, type, aggregate id, created_at, status).
• Commit guarantees the event exists iff the state change committed (no lost events).

Example outbox schema:

CREATE TABLE outbox (
  id BIGSERIAL PRIMARY KEY,
  aggregate_type TEXT NOT NULL,
  aggregate_id UUID NOT NULL,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ,
  status SMALLINT NOT NULL DEFAULT 0 -- 0=pending,1=processing,2=sent,3=failed
);
CREATE INDEX ON outbox(status, occurred_at);

Publishing approaches:

• Polling/worker: lightweight. A background worker selects pending rows (e.g. FOR UPDATE SKIP LOCKED), marks them processing, publishes, then updates status and processed_at. Pros: simple, works with any DB. Cons: added latency and DB polling load; must tune batch size and backoff.
• CDC integration: use Debezium/DB logs to stream insert events from outbox table into Kafka, removing active polling. Pros: low-latency, scalable, exactly-once capabilities with Kafka Connect and transactions. Cons: more infra complexity, must handle schema evolution and CDC guarantees.

Ensuring idempotent consumers:

• Include an event-id (UUID) and aggregate-version or sequence in payload/header. Consumers should store processed event ids or last-seen sequence per aggregate.
• Consumer algorithm: check if event_id already processed (dedupe table keyed by event_id) or compare sequence > last_applied_sequence before applying. Use idempotent writes or upserts.
• Design events to be idempotent where possible (e.g., set state rather than increment).
• For at-least-once delivery (common), make consumer operations transactional: apply business change + insert processed-event record in same DB transaction to avoid partial processing.

Operational notes:

• Use SKIP LOCKED and limit to avoid hot rows when polling.
• Back-pressure: exponential backoff when broker unreachable; mark status=failed with retry counts.
• Monitor outbox lag (max(occurred_at) - min(processed_at)) and alert.

Trade-offs:

• Polling = easier to implement, good for smaller scale. CDC = lower latency and DB load at scale but higher infra complexity.
  This approach gives strong guarantees: event persistence tied to state change; reliability through retries and idempotent consumers.

Requirements & constraints:

• Mobile SDK must work offline, give responsive UX, guarantee eventual consistency, minimize data loss, support large datasets, handle schema evolution, and operate with intermittent connectivity and mobile resource limits.

High-level architecture:

• Client: local store (SQLite/Realm/LevelDB), write-ahead operation log (op-log), sync engine, conflict resolver, background sync scheduler, public SDK API (get/read/subscribe).
• Server: incremental sync endpoints (pull/changes & push/accept), reconciliation service, operation store, transform/CRDT merge logic, versioning, idempotency and audit logs.

Local caching strategies:

• Primary store: a normalized local DB (SQLite/Realm) with lightweight indices. Use TTLs and LRU for large caches.
• Two-layer cache: materialized views for UI (fast reads) + canonical local data for persistence.
• Keep a compact write-ahead op-log recording intents: {opId, entityType, entityId, clientVersion, baseVersion, mutation, timestamp}.

Optimistic updates:

• Apply updates immediately to UI and local store, append op to op-log, mark rows as “pending”.
• Expose hooks to rollback or reconcile after server ack.
• Assign client-generated stable IDs (UUIDs) and monotonic client-version per entity to aid reconciliation.

Conflict resolution approaches:

• LWW (Last-Write-Wins): simple—store (lastWriteTimestamp, writerId). Best for low-conflict fields (e.g., lastSeen). Pros: simple, low overhead. Cons: data loss, clock dependency—use logical clocks if possible.
• Version vectors / Vector clocks: detect concurrent updates. Use for small set of writers and when detectability matters.
• CRDTs: use for replicated counters, sets, maps where merges should be commutative/associative/idempotent (e.g., presence, counters, collaborative lists with RGA/WOOT). Pros: no central coordinator; Cons: complexity, payload size.
• Operational Transforms (OT): for rich collaborative editing where intent must be preserved. Requires centralized sequencing or transformation server; complex to implement on mobile.
  Recommendation: default to hybrid:
  • Per-field resolution policy: metadata-driven schema declares resolution (LWW/logical-timestamp, merge, CRDT, custom server-side hook).
  • Use CRDTs for high-collaboration objects; use vector clocks to detect conflicts for domain objects and surface merges to user or server-side business rules.

Schema migration for local stores:

• Versioned schema with migration scripts in SDK. On SDK init, compare local schema version → run deterministic migrations (DDL + data transforms) within transaction; keep backward-compatible migrations where possible.
• Keep op-log compatible: include operation schema version; provide op-log migration pipeline to rewrite old ops when applying or during background compaction.
• Provide a safe fallback: if migration fails, export data for diagnostics and optionally rebuild from server via full sync.

Incremental sync endpoints & protocol:

• Pull endpoint: GET /sync/changes?since=checkpoint -> returns changes (entities + metadata: version, opId, tombstone) and latest checkpoint token.
• Push endpoint: POST /sync/push -> accept batch of client ops; respond with per-op result (accepted/merged/conflict/need-retry) and server-assigned versions.
• Checkpointing: use server-issued monotonically increasing sync tokens (logical clock / ledger index) rather than wall-clock.
• Support pagination, compression, and delta-encoding (send only changed fields).
• Provide snapshots/full download for initial bootstrap or recovery.

Server-side support to reconcile & replay:

• Operation ingestion pipeline: validate ops, deduplicate by opId, enforce idempotency, compute server version, apply merge strategy per-entity/field.
• Store authoritative versions and append-only change log (event-sourcing style) to enable replay for new clients or auditing.
• Conflict detection: compare client baseVersion vs server version; if server changed, apply merge policy:
  • Automatic merge (CRDT/merge functions) -> produce new version.
  • Deterministic server-side transform (e.g., merging list operations).
  • If ambiguous, mark as conflict and expose resolution API to client or escalate to business logic service for server-side resolution (notify user, offer merge UI).
• Provide a replay endpoint and snapshotting (GET /sync/snapshot?token=) to rebuild client state and to reconcile long-offline clients.
• Expose webhooks/events for downstream systems when conflicts require manual resolution.

Reliability, security & operational concerns:

• Ensure idempotency: client includes opId + clientId; server stores processed opIds with TTL.
• Authentication & authorization per-op; enforce field-level write rules server-side.
• Network & battery: exponential backoff, batching, background sync windows, opportunistic on Wi‑Fi, make sync resumable.
• Telemetry & observability: expose metrics on conflicts, sync durations, op failure rates; provide SDK hooks to log sync problems.
• Data privacy: encrypt sensitive local data (device keystore), secure transport (TLS), server-side encryption.

Developer ergonomics:

• Declarative model schema with per-field resolution policies; migration APIs; clear SDK callbacks for conflict/resolution UI; ability to register custom merge handlers.
• Testing: provide a sync simulator and unit tests for merge policies; CI checks for migration scripts.

Example flow:

1. User edits entity E offline -> SDK writes to local DB, app sees change, append op O1 (clientV=5).
2. Later, device syncs: POST /sync/push O1. Server sees serverVersion=6 (different) -> uses vector clock detect concurrency.
3. If policy LWW and client timestamp > server -> server accepts; else server applies merge (CRDT or custom), responds with new serverVersion=7.
4. SDK reconciles local state with server response: update local row, mark op as applied or resolve conflict UI if server flagged.

Trade-offs:

• LWW simplicity vs CRDT correctness/size; choose based on domain: prefer CRDTs only for data actually needing commutative merges.
• Client complexity vs server authority: pushing more logic to server simplifies SDK but increases server load and latency; hybrid approach balances responsiveness and correctness.

This design gives an SDK that is fast and responsive offline, supports deterministic reconciliation, and provides extensibility for domain-specific conflict resolution while maintaining operational safety (idempotency, migrations, telemetry).

Requirements & constraints:

• Use deterministic matches where available (hashed emails, login IDs, 3P IDs). Fall back to probabilistic matching (device fingerprints, IP+UA, behavioral signals).
• Low false-merge risk (prefer precision for identity merges affecting personalization/CRM), configurable by use-case.
• Respect privacy/regulatory requirements (consent, data minimization, retention, hashing/salting, DPIA).

Approach (algorithmic steps):

1. Ingest raw events into staging with metadata: timestamp, hashed identifiers, device fingerprint, IP, UA, event type.
2. Deterministic join layer (authoritative): if any strong deterministic ID (hashed email, userID, authenticated cookie) shares across records → immediate merge into canonical ID (canonical_id_v1).
3. Graph construction for probabilistic layer: build bipartite graph nodes = device signatures, IP+UA bins, cookie IDs, behavioral clusters. Edges weighted by co-occurrence frequency and recency.
4. Feature vector per candidate pair: overlap_count, co-visit_rate, time_decay_score, device_fingerprint_similarity, geo_consistency, behavioral_similarity (ML embedding cosine), last_seen_delta.
5. Scoring model: logistic regression or gradient-boosted tree trained on labeled pairs (from deterministic joins and human-reviewed samples) to produce probability p(match).
6. Thresholding & business rules:
   • High-confidence promote to canonical ID if p >= T_high (e.g., 0.95) OR deterministic link present.
   • Medium-confidence (T_mid ≤ p < T_high) → put into "probable group" for downstream systems that accept lower precision (analytics, personalization A/B), but do NOT push to CRM.
   • Low-confidence discard (p < T_mid).
   • Allow manual override and safe-unmerge process.

Threshold selection, precision/recall:

• Choose thresholds via ROC/Precision-Recall curves using validation set. For CRM use-case prioritize precision ≥ 99% even if recall lower. For analytics prioritize F1 or recall with precision constraint.
• Measure: precision@T, recall@T, AUC-PR, false merge rate (FMR), false split rate (FSR). Monitor drift: periodic re-evaluation with fresh labels.

Evaluation & validation:

• Create ground truth from deterministic joins, email+login confirmed devices, and manual labeling (stratified sampling by score).
• Run backtests on historical data: simulate merges, check downstream impacts (conversion lift, open rates).
• Ongoing sampling: flag a small % of merges for human audit; compute monthly FMR.

Privacy & regulatory implications:

• Minimize PII: store only hashed/salted identifiers; separate lookup table for reversible links only if necessary with strict access controls.
• Consent: obey consent flags (do not stitch when user opted out).
• Data retention & DPIA: keep raw fingerprints short-lived (TTL), aggregate persistent canonical IDs with minimal metadata, log merges for audit, allow right to be forgotten (ability to unlink/remove canonical record).
• Security: encryption at rest/in transit, role-based access, anomaly detection for mass merging.
• Documentation: publish privacy-preserving description and data uses in policy.

Serving resolved ID to downstream:

• Provide two outputs:
  1. Real-time API (low-latency): given event identifiers, return canonical_id + confidence_score + tags (allowList for CRM). Implement cache with TTL and fallback to async batch.
  2. Batch export: daily join table with canonical_id, member identifiers, provenance, confidence, last_updated.
• API design: include provenance metadata (deterministic vs probabilistic, model version, p_score) and consent flag. Consumers must declare required precision; system enforces (e.g., CRM rejects probablistic below T_high).
• Versioning & rollback: include model/version metadata with each merge; support safe-unmerge and reprocessing when model changes.

Operational & engineering considerations:

• Scale graph computations by sharding by hashed namespace and using streaming approximate joins (HyperLogLog, locality-sensitive hashing) and incremental graph updates.
• Monitoring: metrics (merge rate, API latency, FMR, requests by consumer, opt-out violations), alerts on sudden merge spikes.
• Testing: unit tests for deterministic rules, integration tests with synthetic joins, canary rollout for model changes.

This design balances precision, scalability, and privacy; thresholds and model choice are tuned per downstream use-case with strong governance and auditability.