The Operational Cost of Disjointed Data: Unifying Legacy CRMs

In mid-sized B2B enterprises—especially within logistics, legal, and executive recruitment sectors—operational velocity is governed by data availability. Yet, as companies scale through mergers or decentralised software adoption, they inevitably accumulate multiple CRM platforms (e.g., Salesforce for enterprise accounts, HubSpot for marketing, and legacy databases like SQL Server or file-based spreadsheets for operational delivery).

This fragmentation results in Disjointed Data. Information is trapped in regional or functional silos. A contact update in HubSpot does not propagate to Salesforce. A logistics order fulfilled in an internal database fails to trigger a customer notice. Administrators resort to "double-data entry," spending hours manually copying entries between tabs.

At best, this system leads to wasted administrative hours; at worst, it causes critical contract errors, lost client histories, and database record duplication. This engineering brief analyses the mechanics of data fragmentation, explains why standard off-the-shelf integration tools (like Zapier or Make) fail at enterprise scale, and presents an architectural pattern for custom database normalisation.

1. The Financial and Operational Toll of Silos

To understand the necessity of unified integrations, we must measure the exact cost of fragmented operations.

For an executive recruitment or logistics firm processing 2,000 candidate records or shipping orders per month across three disjointed platforms:

• Administrative Overhead: Staff spend an average of 4.5 minutes per record validating and manually duplicating contact details. This amounts to 150 hours per month of pure data replication, equivalent to £36,000 per year in labour costs.
• Record Decay: Data decays at approximately 2% per month as companies change names, candidates change roles, or emails change. Without automatic synchronisation, the databases diverge, resulting in duplicate records and broken marketing paths.
• Lost Revenue: High-value candidates or shipment alerts are delayed or ignored due to outdated contact information, leading to client attrition.

Comparative Failure Model: Zapier vs. Custom API Normalisation

Many businesses attempt to bridge these silos using off-the-shelf integration platforms like Zapier. While useful for simple triggers, these platforms quickly break under enterprise workloads:

| Operational Dimension | Off-the-Shelf Zapier Integration | Custom API Normalisation Layer | | :--- | :--- | :--- | | Concurrency Support | Poor. Lacks native support for distributed locking, leading to race conditions. | Complete. Uses Postgres advisory locks or database-level transaction limits. | | Data Ordering | Out of Order. No FIFO (First-In, First-Out) guarantee, causing newer updates to be overwritten by older ones. | Strict FIFO. Uses queuing mechanisms (e.g., RabbitMQ or Postgres outbox queues). | | Error Recovery | Binary. Either passes or halts the zap, requiring manual retry. | Transactional. Auto-retry with exponential backoff and dead-letter queue isolation. | | Data Mapping & Deduplication | Basic string matching only. | Advanced. Fuzzy-matching logic, email normalisation, and corporate domain extraction. | | Rate-Limit Resiliency | Prone to API exhaustion blocks on destination systems. | Smart throttling. Buffers requests dynamically to match API target restrictions. |

2. The Mechanics of the Integration Deficit

To illustrate why simple webhook integrations fail, consider a common race condition: The Double Update.

Candidate record edited in Salesforce (Time: 0.0s) ──> Webhook Dispatched ──> Zapier Processes (Delay: 1.5s)
                                                                                  |
Candidate record edited in HubSpot (Time: 0.5s) ────> Webhook Dispatched ──> Zapier Processes (Delay: 0.2s)

If the HubSpot webhook is processed faster than the Salesforce webhook, the final state of the database will be the older Salesforce edit, overwriting the more recent HubSpot update.

Without a custom integration layer that parses record timestamps, checks lock state, and executes updates inside a database transaction, data degradation is mathematically guaranteed.

3. Architecture of a Custom Unification Bridge

A production-ready integration bridge requires a middle normalisation layer that decouples inbound events from data writes. The system is designed as a stateless broker:

[Inbound Webhooks] ──> [Ingestion Route] ──> [Queue: transactionalOutbox] ──> [Worker: Normalise & Deduplicate] ──> [Write to CRMs]

1. Ingestion Engine: Receives payload data from Salesforce, HubSpot, or SQL Server database triggers, verifies signatures, and enqueues a raw event record.
2. First-In, First-Out Queue: Records are queued with timestamps to enforce correct execution order.
3. Normalisation Worker: Runs parsing rules to clean strings, remove formatting errors (e.g., phone numbers normalised to E.164 format), and resolve parent corporate domains.
4. Deduplication Engine: Verifies target IDs or matches email hashes to prevent duplicate entries.
5. Distribution Dispatcher: Writes to target platforms using pooled, rate-limited HTTP queries.

4. Technical Implementation: The Normalisation Pipeline

Below is a custom TypeScript pipeline using Drizzle ORM and Postgres database locks to normalise and synchronise records safely.

The Database Schema

We define the tables to track sync status and hold normalised company domains in db/schema.ts:

import { pgTable, serial, text, timestamp, integer, uniqueIndex } from 'drizzle-orm/pg-core';

export const customers = pgTable('customers', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  fullName: text('full_name').notNull(),
  phoneNormalised: text('phone_normalised'),
  companyDomain: text('company_domain'),
  salesforceId: text('salesforce_id'),
  hubspotId: text('hubspot_id'),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

The Synchronisation and Deduplication Action

The synchronisation service below processes incoming contact changes, extracts domains, normalises values, and updates the databases securely using transactions:

'use server';

import { getDb } from '@/db';
import { customers } from '@/db/schema';
import { eq, or } from 'drizzle-orm';
import { logger } from '@/app/utils/logger';

interface InboundContact {
  email: string;
  name: string;
  phoneRaw?: string;
  salesforceId?: string;
  hubspotId?: string;
  updatedAt: string; // ISO String from source
}

// Normalises phone numbers to standard format (E.164 representation)
function normalisePhone(phone?: string): string | null {
  if (!phone) return null;
  const digits = phone.replace(/\D/g, '');
  if (digits.length === 10) return `+1${digits}`;
  if (digits.startsWith('44') && digits.length === 12) return `+${digits}`;
  return `+${digits}`;
}

export async function syncInboundContact(contact: InboundContact) {
  const db = getDb();
  const domain = contact.email.split('@')[1]?.toLowerCase() ?? null;
  const phone = normalisePhone(contact.phoneRaw);
  const sourceUpdatedAt = new Date(contact.updatedAt);

  try {
    // Execute updates inside an isolated transaction to prevent race conditions
    return await db.transaction(async (tx) => {
      // Step 1: Check if record already exists by email or external ID
      const existing = await tx
        .select()
        .from(customers)
        .where(
          or(
            eq(customers.email, contact.email),
            contact.salesforceId ? eq(customers.salesforceId, contact.salesforceId) : undefined,
            contact.hubspotId ? eq(customers.hubspotId, contact.hubspotId) : undefined
          )
        )
        .limit(1);

      const record = existing[0];

      if (!record) {
        // Create new unified customer record
        await tx.insert(customers).values({
          email: contact.email,
          fullName: contact.name,
          phoneNormalised: phone,
          companyDomain: domain,
          salesforceId: contact.salesforceId ?? null,
          hubspotId: contact.hubspotId ?? null,
          updatedAt: sourceUpdatedAt,
        });
        logger.info('Unified contact record created', 'syncContact', { email: contact.email });
        return { success: true, action: 'created' };
      }

      // Step 2: Out-of-order check (avoid overwriting newer data with old payloads)
      if (record.updatedAt.getTime() >= sourceUpdatedAt.getTime()) {
        logger.warn('Skipped stale contact update', 'syncContact', { email: contact.email });
        return { success: true, action: 'skipped_stale' };
      }

      // Step 3: Merge and normalise values
      await tx
        .update(customers)
        .set({
          fullName: contact.name || record.fullName,
          phoneNormalised: phone || record.phoneNormalised,
          salesforceId: contact.salesforceId || record.salesforceId,
          hubspotId: contact.hubspotId || record.hubspotId,
          updatedAt: sourceUpdatedAt,
        })
        .where(eq(customers.id, record.id));

      logger.info('Contact record synchronised successfully', 'syncContact', { email: contact.email });
      return { success: true, action: 'updated' };
    });
  } catch (err) {
    logger.error('Failed to synchronise contact payload', 'syncInboundContact', { error: String(err), email: contact.email });
    return { success: false, error: 'Database transaction error.' };
  }
}

5. Security & Isolation: Zero-Bypass Architecture

A custom unification bridge provides significant security advantages over generic integrations:

1. Least-Privilege API Isolation: Third-party CRMs do not access your central database directly. The integration layer acts as a strict firewall, sanitising data at the boundary.
2. Encrypted Payloads: Communications between legacy databases and the Edge middleware are fully encrypted via TLS 1.3, mitigating intercept vulnerabilities.
3. SOC 2 Audit Compliance: All synchronisation attempts, successes, and failures are recorded in the central auditLogs table, creating an immutable timeline of customer data updates.

For mid-sized B2B companies looking to scale operations, eliminating data silos is a critical prerequisite. Automating this integration with type safety and transactional boundaries preserves database integrity while freeing admin resources for higher-value work.