CRM Software Development Services: Stop Duplicate Writes

We found the hardest CRM bugs were not failed requests. They were successful requests executed twice.

A client retried a POST /contacts request after a timeout. The first request had already committed. The retry created another contact because the API had no way to distinguish a retry from a new operation.

This becomes worse when CRM software development services connect PostgreSQL to email, billing, analytics, or notification systems. A single contact creation can become several writes across different systems.

This article focuses on one specific failure mode: duplicate CRM records caused by retries and unsafe database-to-event writes.

The implementation uses Node.js, PostgreSQL, an idempotency key, a database unique constraint, and a transactional outbox. The goal is not exactly-once delivery. The goal is to make repeated requests safe and make committed CRM changes observable to downstream systems.

1. Start with the duplicate-write failure

The first clue is usually not an application exception. It is a database containing two contacts with the same external identifier.

A naive handler often looks like this:

// Naive CRM Software Development Services pattern: the existence check and insert are separate operations.
const existing = await pool.query(
  'SELECT id FROM contacts WHERE external_id = $1',
  [externalId]
);

if (existing.rowCount === 0) {
  await pool.query(
    'INSERT INTO contacts (external_id, name) VALUES ($1, $2)',
    [externalId, name]
  );
}

Two concurrent requests can both observe zero rows before either insert commits.

The application appears correct during normal testing. Under retries or concurrency, both requests can pass the check.

The database must own this invariant.

2. Put idempotency in PostgreSQL

Once the failure is understood, the important design decision is where uniqueness lives.

We create a unique constraint on the identifier that represents the same CRM operation. PostgreSQL's ON CONFLICT can then turn a duplicate insert into a controlled result instead of another record.

-- The unique constraint makes duplicate external CRM identities impossible.
CREATE UNIQUE INDEX contacts_external_id_uidx
ON contacts (external_id);

The insert can now use PostgreSQL's conflict handling:

-- The conflict target is the database-level guarantee, not an application-side race check.
INSERT INTO contacts (external_id, name)
VALUES ($1, $2)
ON CONFLICT (external_id) DO UPDATE
SET name = EXCLUDED.name
RETURNING id, external_id, name;

This changes the failure model.

A retry no longer depends on whether the application happened to run its SELECT before another request. PostgreSQL resolves the conflict against its unique index.

For an operation where the original payload must never change, use DO NOTHING instead. The correct choice depends on the CRM operation's semantics.

3. Keep the CRM write and event write atomic

The unique constraint solves duplicate contacts. It does not solve the next failure.

Suppose the contact is committed and the application then publishes contact.created. If publishing fails, the database contains the contact but downstream systems never receive the event.

The reverse ordering has a different problem: the event can be published even though the database transaction later rolls back.

AWS documents this as the dual-write problem and recommends a transactional outbox when a database update must reliably produce an event.

We can represent the outbox with a normal PostgreSQL table:

-- The outbox stores the event beside the CRM record so both writes commit together.
CREATE TABLE crm_outbox (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  aggregate_id BIGINT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at TIMESTAMPTZ
);

The critical part is that the contact and outbox rows use the same database transaction.

With node-postgres, all statements in a transaction must use the same client. Its documentation explicitly warns against using pool.query() for multi-statement transactions.

// Node.js 22+ / node-postgres: the same client owns both the CRM write and outbox transaction.
const client = await pool.connect();

try {
  await client.query('BEGIN');

  const contact = await client.query(
    `INSERT INTO contacts (external_id, name)
     VALUES ($1, $2)
     ON CONFLICT (external_id) DO UPDATE
     SET name = EXCLUDED.name
     RETURNING id, external_id, name`,
    [externalId, name]
  );

  await client.query(
    `INSERT INTO crm_outbox (event_type, aggregate_id, payload)
     VALUES ($1, $2, $3::jsonb)`,
    [
      'contact.created',
      contact.rows[0].id,
      JSON.stringify(contact.rows[0])
    ]
  );

  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

There is an important trade-off here.

The transaction guarantees that the contact and outbox record commit together. It does not guarantee that a downstream consumer processes an event exactly once.

AWS also notes that transactional-outbox consumers should be idempotent because duplicate events can still occur.

4. Make retries return the same operation

The database constraint protects the CRM record. The API should also understand retries.

For operations where clients can safely retry, require an idempotency key:

POST /contacts
Idempotency-Key: 7b7e7f4d-4c3a-4e12-9a2b-8d0d4f8b6a21

Store that key with the operation result.

A second request using the same key should return the existing result rather than executing the business operation again.

The important distinction is between two identifiers:

• external_id identifies the CRM entity.
• idempotency_key identifies the client's attempt to perform an operation.

They solve different problems.

A contact can have one external identity while receiving several legitimate updates. Conversely, the same create request can be retried several times while representing one operation.

5. We would not add a queue first

This is where the architecture decision matters.

A queue between the API and PostgreSQL may sound attractive because it separates the request from downstream work. But it does not automatically solve duplicate CRM writes.

If the application writes the contact, publishes a message, and crashes between those operations, the same dual-write problem remains.

The transactional outbox puts the durable handoff inside the database transaction. A worker can then claim unpublished rows and publish them.

That worker should assume delivery is at-least-once.

-- The worker only claims events that have not been published yet.
SELECT id, event_type, aggregate_id, payload
FROM crm_outbox
WHERE published_at IS NULL
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 100;

SKIP LOCKED is useful when multiple workers consume the same outbox table. Each worker can avoid waiting on rows currently locked by another worker.

After successful publication, the worker marks the event as published.

The downstream consumer still needs its own idempotency mechanism. That is intentional. Each boundary owns its duplicate-handling rule.

Real-World Application

The trade-off above is between simple synchronous code and explicit consistency boundaries. We hit this exact class of problem while designing an anonymized CRM integration workflow where API retries could repeat a contact operation.

Oodles initially treated the existence check as sufficient. It failed because two requests could perform the check concurrently, and the application had no database-enforced uniqueness boundary.

We changed the design to use a PostgreSQL unique index for the CRM identity and placed the contact write plus outbox insert inside one transaction. The downstream worker then became responsible for publishing and retrying events.

That measurement matters because duplicate prevention and throughput are different concerns. The architecture should be validated with the workload that actually matters: concurrent creates, client retries, worker restarts, database failovers, and downstream delivery failures.

Conclusion: Key Takeaways

• A SELECT followed by INSERT is not an idempotency guarantee. Concurrent requests can both pass the existence check.

• PostgreSQL should enforce CRM uniqueness. A unique index makes the invariant independent of application timing.

• The transactional outbox solves the database-to-event dual-write gap. The CRM record and its outbox event commit together.

• Exactly-once processing should not be assumed. Outbox consumers and downstream integrations should tolerate duplicate delivery.

• Idempotency keys and entity identifiers are different. One identifies an operation attempt; the other identifies the CRM entity.

If you have handled duplicate CRM writes differently, share the failure mode and constraint that drove your architecture. ContactUs

FAQ

1. What are CRM Software Development Services?

CRM Software Development Services involve designing and building customer relationship management systems around a company's sales, customer, support, marketing, and integration workflows. They can include custom CRM applications, third-party integrations, automation, APIs, reporting, and database architecture.

2. How do CRM systems prevent duplicate customer records?

A CRM should enforce uniqueness at the database level rather than relying only on application-side checks. For example, PostgreSQL can use a unique index on an external customer ID and ON CONFLICT to safely handle repeated requests.

3. What is an idempotency key in a CRM API?

An idempotency key identifies a specific API operation so that retrying the same request does not execute the operation multiple times. This is particularly useful when network timeouts make it unclear whether the original request reached the server.

4. Why use a transactional outbox with CRM integrations?

A transactional outbox keeps a CRM database change and its corresponding event in the same database transaction. This prevents a situation where the CRM record is committed but the event notification is lost because the application fails between two separate writes.

5. Can CRM integrations guarantee exactly-once event processing?

Not automatically. A transactional outbox provides a reliable handoff from the database to the event publisher, but downstream systems can still receive duplicate events. Consumers should therefore be designed to process repeated events safely using their own idempotency or deduplication mechanism.

Comments

[Mohith kumar]

"Successful requests that shouldn't have happened" is so true. Idempotency keys on every inbound write fixed most of it for us, especially for form submissions that arrive via retried webhooks. We designed chatform.in's webhooks (I work on it) around that: signed, with a stable ID per response so receivers can dedupe safely.