ERP Development: Sync Your App and ERP With a Postgres Outbox

If your custom app needs to send orders or invoices to an ERP, don't call the ERP API from inside your request handler. Write each change to an outbox table in the same Postgres transaction as the business data. Then let a separate Node.js worker push those rows to the ERP, with retries, backoff and a dead-letter queue. This is the part of ERP development that decides whether you can sync records between a custom app and your ERP without losing any or posting them twice.

You may know the symptoms already. The ERP had a slow afternoon and some orders never arrived. Or the warehouse shipped one order and finance has two invoices for it. Nobody can say which records made it across without checking both systems by hand.

How do you sync records between a custom app and your ERP without losing any?

You make the database the single source of truth for what still needs to be sent. The outbox row and the order row commit together or not at all. A worker reads pending rows, sends them, and marks them done only after the ERP confirms.

The usual approach is a "dual write": save the order, then call the ERP. It fails in three ways.

• The call fails after your commit. The order exists in your app and nowhere else. Nothing remembers to try again.
• The call succeeds but the response times out. Your code thinks it failed and retries. The ERP now has two invoices.
• You make the call inside the transaction. Now a slow ERP holds your database locks open, and its bad day becomes your app's bad day.

The outbox fixes the first and third problems by design. Idempotency fixes the second, which we cover below.

What does a broken sync actually cost you?

The damage shows up downstream, in people's time. Customers get duplicate invoices and start doubting every one after that. Orders missing from the ERP never reach fulfilment, so they ship late or not at all.

Stock levels drift because the ERP never saw some sales. At month end, finance compares exports line by line to find the gaps. Worst of all, these failures are quiet. You usually hear about them from a customer, weeks later.

Do you need an outbox at all?

Not always. If you send a handful of records a day and someone checks them, or a nightly CSV import into the ERP already removes duplicates, keep it. An outbox adds a table, a worker and monitoring, and that is only worth it when records flow all day and people rely on them arriving.

If orders come in all day, the ERP has outages or rate limits, or a duplicate invoice causes a real problem, read on.

Step 1: create the outbox table

CREATE TABLE erp_outbox (
  id               bigserial PRIMARY KEY,
  aggregate        text NOT NULL,
  aggregate_id     text NOT NULL,
  event_type       text NOT NULL,
  payload          jsonb NOT NULL,
  idempotency_key  uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  status           text NOT NULL DEFAULT 'pending',
  attempts         int NOT NULL DEFAULT 0,
  next_attempt_at  timestamptz NOT NULL DEFAULT now(),
  last_error       text,
  created_at       timestamptz NOT NULL DEFAULT now()
)

Add an index so the worker's query stays fast as the table grows:

CREATE INDEX erp_outbox_pending ON erp_outbox (next_attempt_at) WHERE status = 'pending'

The status column has three values: pending, sent and dead. Rows marked dead are your dead-letter queue. They stay in the same table, where you can query them.

Step 2: write the business row and the outbox row together

async function createOrder(pool, order) {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const { rows } = await client.query(
      'INSERT INTO orders (customer_id, status) VALUES ($1, $2) RETURNING id',
      [order.customerId, 'confirmed']
    )
    await client.query(
      `INSERT INTO erp_outbox (aggregate, aggregate_id, event_type, payload)
       VALUES ('order', $1, 'order.created', $2)`,
      [rows[0].id, JSON.stringify({ ...order, id: rows[0].id })]
    )
    await client.query('COMMIT')
    return rows[0].id
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

There is no network call here. The user gets a fast response whether or not the ERP is up.

Step 3: how does the worker claim rows safely?

It claims rows with FOR UPDATE SKIP LOCKED and pushes their next_attempt_at forward as a lease. Two workers never take the same row. If a worker crashes halfway, its rows come back on their own once the lease runs out.

async function claimBatch(pool) {
  const { rows } = await pool.query(`
    UPDATE erp_outbox SET next_attempt_at = now() + interval '2 minutes'
    WHERE id IN (
      SELECT id FROM erp_outbox
      WHERE status = 'pending' AND next_attempt_at <= now()
      ORDER BY id LIMIT 20
      FOR UPDATE SKIP LOCKED
    )
    RETURNING *`)
  return rows
}

The lease has to be longer than your HTTP timeout. If it isn't, a slow call can be claimed a second time while the first one is still running.

How do retries, backoff and the dead-letter queue work?

Each failure increases the wait before the next try, with some randomness added so retries don't all land at once. Errors that retrying won't fix, and rows that have used up their attempts, go to dead.

const MAX_ATTEMPTS = 8

function backoffMs(attempts) {
  const base = Math.min(2 ** attempts * 1000, 15 * 60 * 1000)
  return base / 2 + Math.random() * (base / 2)
}

async function sendToErp(row) {
  const res = await fetch(`${process.env.ERP_URL}/api/${row.aggregate}s`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Idempotency-Key': row.idempotency_key,
      Authorization: `Bearer ${process.env.ERP_TOKEN}`
    },
    body: JSON.stringify(row.payload),
    signal: AbortSignal.timeout(30000)
  })
  if (res.ok) return
  const err = new Error(`ERP ${res.status}: ${await res.text()}`)
  err.retryable = res.status === 429 || res.status >= 500
  throw err
}

async function processRow(pool, row) {
  try {
    await sendToErp(row)
    await pool.query(`UPDATE erp_outbox SET status = 'sent' WHERE id = $1`, [row.id])
  } catch (err) {
    const attempts = row.attempts + 1
    const dead = err.retryable === false || attempts >= MAX_ATTEMPTS
    await pool.query(
      `UPDATE erp_outbox
       SET attempts = $2, last_error = $3,
           status = CASE WHEN $4::boolean THEN 'dead' ELSE 'pending' END,
           next_attempt_at = now() + ($5 || ' milliseconds')::interval
       WHERE id = $1`,
      [row.id, attempts, String(err.message).slice(0, 2000), dead, Math.round(backoffMs(attempts))]
    )
  }
}

async function run(pool) {
  while (true) {
    const rows = await claimBatch(pool)
    for (const row of rows) await processRow(pool, row)
    if (rows.length === 0) await new Promise(r => setTimeout(r, 2000))
  }
}

Network errors and timeouts have no retryable flag, so they get retried. A 400 for a missing tax code goes straight to dead, because sending the same bad payload eight more times won't fix it.

To replay a dead row once the cause is fixed, set status = 'pending' and attempts = 0. Keep the same idempotency_key.

How do you stop the ERP from posting an invoice twice?

Give the ERP a key that stays the same on every retry, so a repeated request is recognised and ignored. The outbox's idempotency_key is that key. How you pass it depends on what your ERP supports.

• If the ERP accepts an idempotency header, send it, as shown above.
• If it supports upsert by external ID, use your outbox key or order number as that ID. NetSuite, for example, can upsert a record by its external ID.
• If it supports neither, store your reference in a custom field and look it up before you create the record. This is slower, but it is the only safe option.

One more rule: if the order of events matters for a record, for example order.updated must never arrive before order.created, claim only the oldest pending row for each aggregate_id.

What should you check before going live?

• Alert when any row is dead. Each dead row is an order or invoice the ERP doesn't know about.
• Alert on the age of the oldest pending row. A stuck worker produces no errors, just silence.
• Save the ERP's response body when a call fails. ERPs usually explain validation errors there.
• Kill the worker in the middle of a send during testing. That is exactly how duplicates happen, so prove your idempotency handles it.
• Delete old sent rows on a schedule. Otherwise the table grows forever.

If you're connecting your own app to an ERP and want a second opinion on the sync design before it goes live, Geminate Solutions can review your plan for free. You can see how we handle this kind of work in our custom development practice.