GTM Galaxy

Article

The Millisecond Collision: Why Native CRM Deduplication Fails Under Concurrent Webhook Bursts

Claudine Baumbach / / Pacific

Native CRM deduplication is a conditional truth. You configure your Matching Rules in Salesforce or Unique Properties in HubSpot, run a manual test, and the system blocks the duplicate as expected. But when a high-intent webinar ends or a partner sync triggers a burst of data, you find four identical Lead records created at the exact same second.

This isn't a bug in the CRM application logic. It is a fundamental consequence of database physics—specifically, how systems handle concurrent transactions under standard isolation levels. When identical events arrive in a millisecond burst, the native safeguards are technically unable to see the collision until it has already happened.

The Anatomy of a Millisecond Collision

Most enterprise CRMs, including Salesforce, operate under a READ COMMITTED isolation level. In this mode, a database transaction only sees data that has already been committed. It is blind to "in-flight" records being written by other simultaneous processes.

Imagine two webhooks, Request A and Request B, hitting your CRM API within 10 milliseconds of each other. Both represent the same person signing up for a demo.

  1. Request A begins. The CRM executes the deduplication logic: "Does user@example.com exist?" The database returns FALSE. Request A proceeds to the next step: preparing the INSERT.
  2. Request B begins. It asks the same question: "Does user@example.com exist?" Because Request A has not finished its transaction, the record is not yet committed. To Request B, the database is still empty. It also returns FALSE.
  3. The Result: Both transactions believe the coast is clear. Both proceed to insert. By the time they commit, you have two records with identical data and IDs generated milliseconds apart.

The Window of Failure

In a standard CRM, a write operation is rarely just an insert. It involves looking up related accounts, running Apex triggers, firing HubSpot Workflows, and evaluating assignment rules. This entire transaction can take 200ms to 800ms.

In GTM engineering, 800ms is a massive window. If you are using a high-concurrency platform like n8n or an AWS Lambda function to push data, you can fire dozens of requests in that timeframe. The more complex your internal CRM automation, the longer the transaction stays open, and the wider the window for duplicate collisions.

Salesforce vs. HubSpot: Different Failures

Salesforce handles this via Duplicate Rules. When set to "Block," the API throws a DUPLICATES_DETECTED error. However, under concurrency, the matching engine fails to find a match because the first record hasn't been committed yet. The rules simply don't fire.

HubSpot approaches this with unique property constraints. If you mark a property as unique, HubSpot attempts to enforce this at the database level. While this prevents the duplicate, it creates a different problem: a 409 Conflict error. If your webhook handler isn't built to catch and handle these errors defensively, that lead data is lost. You’ve traded a duplicate for a silent failure.

The Fix: Upstream Synchronization with PostgreSQL

To solve this, you must move the deduplication logic upstream of the CRM. By using a processing layer backed by PostgreSQL, you can implement advisory locks to serialize processing for specific entities without bottlenecking your entire pipeline.

An advisory lock is a purely logical lock. You don't lock a table; you lock a specific value (like a hashed email address). We use pg_advisory_xact_lock, which automatically releases the lock when the transaction completes.

Your ingestion logic should look like this:

  1. Receive Webhook: Get the payload for user@example.com.
  2. Generate a Lock Key: Create a deterministic hash of the email.
    SELECT pg_advisory_xact_lock(hashtext('user@example.com')::bigint);
  3. Check and Write: While holding that lock, check your local database or the CRM for the record. If it doesn't exist, create it.
  4. Commit: Once the transaction commits, the lock is released for the next process.

If Request B hits while Request A is still talking to the CRM, Request B will sit and wait at the pg_advisory_xact_lock line. It won't fail; it just waits. By the time it acquires the lock, Request A has finished, the record exists in the CRM, and Request B's check will correctly identify the existing record.

Trade-offs and Alternatives

Adding an upstream locking layer introduces infrastructure overhead. You are adding a database round-trip and a small amount of latency to every inbound event.

If you aren't ready to manage a PostgreSQL instance for locking, consider these alternatives:

  • Deterministic Serialization: Use a message queue like AWS SQS with a Message Group ID set to the email address. This forces all events for that email into a single-threaded queue, effectively removing concurrency.
  • Idempotency Keys: If your CRM or middleware supports it, pass a client-side generated UUID as an idempotency key. However, native support for this in CRM APIs is often inconsistent across different objects.
  • The "Soft Failure" Pattern: Lean into HubSpot's 409 errors or Salesforce's strict unique External IDs. Write your middleware to treat a duplicate error as a "success" (e.g., catching the error and performing an update instead of an insert).

For low-volume pipelines, a nightly batch deduplication job is often simpler. But if you are running high-velocity GTM systems where duplicates break attribution or trigger multiple sales notifications, you cannot rely on the CRM's native rules. You have to control the flow at the millisecond level.

— C.B.