4 Integration Engineering

An ERP never lives alone. Your apparel system must talk to Shopify (where the products sell), QuickBooks (where the money is counted), and big retailers who speak a forty-year-old language called EDI. This chapter teaches the one pipeline shape that makes every one of those conversations safe, and why a sync failure nobody notices is exactly as bad as corrupted data.

What you need to know first

This chapter is about connecting computer systems that belong to different companies. Before we can do that, you need a handful of building-block ideas. Read this section slowly; everything after it depends on these words.

Servers, HTTP, APIs, and JSON

A server is just a computer program that sits and waits for other programs to contact it. Your ERP has a server. Shopify has servers. QuickBooks has servers.

HTTP is the set of rules those programs use to talk over the internet. One program sends a request ("please give me order #123" or "please save this invoice") and the other sends back a response ("here it is" or "saved" or "no, and here is why"). Every response carries a status code — a three-digit number:

• 200 means "that worked."
• 401 means "I don't believe you are who you say you are."
• 429 means "you are asking too fast, slow down."
• 500 means "I broke, it's my fault."

A request also carries headers: small labeled sticky notes attached to the envelope, like "this message is JSON" or "here is my ID badge." The main content of a request or response is called the body, or the payload.

An API (Application Programming Interface) is the menu of requests a company's servers agree to answer. Shopify's API is the published list of questions and commands Shopify will accept from your program, with exact instructions for how to phrase each one. An endpoint is one specific web address on that menu — one item you can order.

JSON is the most common format for the data inside those requests and responses. It is plain text arranged with curly braces and quotes, like {"sku": "TEE-BLK-M", "qty": 24}. Humans can read it; programs can parse it. To parse means to read text and turn it into structured values a program can work with. The reverse, turning structured values back into text, is to serialize.

Webhooks, queues, and workers

A webhook flips the direction of the conversation. Normally you call Shopify. A webhook is Shopify calling you: when something happens over there (a customer places an order), their server immediately sends an HTTP request to a web address you gave them. The arrangement is: "Don't keep phoning us to ask if there's news. Give us your number and we'll call you."

A queue is a waiting line for work, stored so it cannot be lost. Instead of doing a job the moment it arrives, you write it down ("process this order payload") and a separate program — a worker — picks jobs off the line one at a time and does them. If the worker crashes, the job is still written down, so nothing is forgotten.

In this book our queue is a table in our Postgres database, which you already know from earlier chapters. The worker is usually started by a cron job: a timer that runs a piece of code on a schedule, like "every ten seconds" or "every night at 2 a.m." The name comes from an old Unix program; treat it as a synonym for "scheduled task."

Secrets, hashing, and encoding

Authentication is proving identity. Between companies this is usually done with a secret: a long random string that only the two of you know, like a shared password for programs. If a request arrives carrying proof that the sender knows the secret, you believe it. You will meet two proof mechanisms in this chapter: signatures (for webhooks) and tokens (for OAuth).

Two more words about secrets, because they get confused constantly. Hashing is a one-way math function: you put data in, you get a fixed-size fingerprint out, and there is no way to run it backwards. SHA-256 is a hash function. Encryption is two-way: you scramble data with a key and you can unscramble it with the key. Passwords get hashed (you never need them back). API tokens get encrypted (you absolutely need them back, to send them). Getting this backwards is a classic beginner mistake — you cannot "unhash" an access token, so if you hash one you have simply destroyed it.

Base64 is a way of writing raw bytes as ordinary text using 64 safe characters. Headers can only carry text, so binary things like signatures and encoded credentials get base64'd first. Base64 provides no security at all — anyone can decode it in a second. It exists to move raw bytes safely through channels that accept only text.

Race conditions, delivery guarantees, and two words from earlier chapters

A race condition is what happens when two pieces of code run at the same time and step on each other. Two workers both read "quantity is 24," both subtract 3, both write 21, and now you have sold 6 units but only counted 3. Chapter 2 taught you Postgres locking to prevent exactly this; you will use it again here.

Delivery guarantees come in three flavors, and knowing the vocabulary saves you weeks. At-most-once means a message might be lost but never duplicated. At-least-once means a message is never lost but might arrive twice. Exactly-once is what everyone wants and almost nobody delivers across a network. Nearly every integration you will ever build, Shopify webhooks included, is at-least-once. That single fact drives the entire design of this chapter: if duplicates are guaranteed, your code must be safe against duplicates.

Finally, remember from chapter 1 that our ERP is multi-tenant: one database serves many apparel brands, and every row carries a tenant_id saying which brand it belongs to. And remember from chapter 3 that a job is idempotent if running it twice is harmless — doing it a second time changes nothing. An idempotency key is the identifier you use to recognize "I have already done this one." Keep both words close; this whole chapter leans on them.

Why integrations are half the job

Here is a normal day for your ERP:

• A boutique buys three tees on the brand's Shopify store — that sale must lower your inventory and your ATS (available-to-sell, the quantity you can still promise to wholesale buyers).
• You send a wholesale invoice — that must appear in QuickBooks or the accountant's books are wrong.
• Nordstrom sends a purchase order — it arrives as an EDI (Electronic Data Interchange) document, and if you ship without acknowledging it correctly, Nordstrom fines the brand.

Every one of these is an integration: an ongoing, automated conversation between your system and someone else's.

Integrations fail constantly, for boring reasons: the other side is down for a minute, a network cable somewhere hiccups, they change a field name, your code has a bug for one weird order shape. Failures like that are normal, and you should plan for them. The dangerous case is the failure that happens silently. If a Shopify order never reaches your ERP, your ATS says you have 24 units when you really have 21. You will promise inventory you don't own. Nobody typed bad data; the data went bad on its own, invisibly.

Two outages nobody could see

Let me make that concrete, because "silent failure" sounds abstract until it has cost somebody money. A team I know shipped a routine Friday deploy. Their hosting platform swapped containers, and for about ninety seconds the webhook endpoint returned 502. Shopify did what Shopify does: it retried. But the retries also landed during the swap, and after the eighth consecutive failure Shopify deleted the webhook subscription entirely, which its documentation says it will do.

From that moment on, no order webhook ever arrived again. The endpoint was healthy. The logs were clean. There were no errors to see, because there were no requests to fail. Forty wholesale-adjacent orders never reached the warehouse. Nobody noticed for six days, and they only noticed then because a customer emailed asking where her package was.

The fix took twenty minutes. The apology emails, the expedited shipping, and the two oversold styles took three weeks.

Here is a second story with the opposite shape. A different team wrote a webhook handler that wrapped everything in try { … } catch { } — swallow the error, return 200, keep the logs quiet. It worked beautifully for months. Then a tenant added a new SKU (stock keeping unit) with a character their parser choked on. Every order containing that SKU threw, got swallowed, and returned a cheerful 200 OK.

Shopify marked those deliveries successful and never sent them again. The events did not go to a dead-letter queue; they went nowhere, because nothing had ever been written down. Reconstructing three weeks of missing orders required a paid data pull and two days of manual reconciliation.

Returning 200 when you have not durably stored the message is the single most expensive line of code in integration engineering.

The universal skeleton: one pipeline for every outside system

Shopify, QuickBooks, and EDI all fit the same pipeline shape. Learn it once and reuse it forever. The stages are:

1. Receive a message at a web address (an endpoint) on your server.
2. Verify that the message truly came from who it claims (check the signature).
3. Store the raw message in a queue table — untouched, exactly as received.
4. Reply "got it" immediately, before doing any real work.
5. Process later: a worker reads queued messages and applies them to your real tables, idempotently.
6. Retry failures with growing delays (called exponential backoff — wait 30 seconds, then 1 minute, then 2, then 4…).
7. Dead-letter anything that fails too many times: park it in a "could not deliver" state, like a mail tray for letters with no valid address, and
8. show it in a human-visible error inbox in your UI.

We store first and process later because the sender is impatient. Shopify, for example, gives you a one-second connection timeout and a five-second timeout for the whole request; miss it and the delivery counts as failed. Writing one row to a table takes milliseconds. Applying an order to inventory might take much longer: you have to look up SKUs, take locks, write ledger rows, maybe call a warehouse API. Splitting "receive" from "process" means you never lose a message just because processing it was hard.

Here are the tables the skeleton needs. (SQL, you'll recall, is the language for talking to our Postgres database; create table defines a new kind of record.)

-- One row per outside account a tenant has connected.
create table integration_connection (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references tenant(id),
  provider            text not null check (provider in ('shopify','quickbooks','edi','warehouse')),
  external_account_id text not null,   -- e.g. 'acme-apparel.myshopify.com'
  access_token_enc    bytea,           -- encrypted; never store tokens as plain text
  refresh_token_enc   bytea,
  access_token_expires_at timestamptz,
  webhook_secret_enc  bytea,           -- the shared secret we verify signatures with
  scopes              text[],          -- what the tenant actually granted us
  watermark           timestamptz,     -- polling bookmark; see the polling section
  status              text not null default 'active',
                      -- active | needs_reauth | disabled
  status_reason       text,
  last_success_at     timestamptz,
  created_at          timestamptz not null default now(),
  unique (tenant_id, provider, external_account_id)
);

Reading it row by row:

• tenant_id says which brand owns this connection, and references tenant(id) — a foreign key — tells Postgres to refuse any value that isn't a real tenant.
• The check on provider means Postgres itself rejects a typo like 'shopfiy'; constraints are free bug-catchers.
• external_account_id is how they name the account — a myshopify domain, an Intuit realm ID, an EDI partner code.
• The _enc columns hold credentials in encrypted form; bytea means "raw bytes," which is what encrypted data looks like.
• scopes records the permissions the tenant granted, so you can say "you never gave us permission to read fulfillments" instead of failing mysteriously.
• status is the connection's health: active, needs_reauth (the tenant must click reconnect), or disabled.
• And last_success_at is quietly one of the most valuable columns here — a connection whose last success was nine days ago is broken, even if nothing ever threw an error.

The inbound_event queue table

Now the queue table itself. Many teams call this table webhook_events; I call it inbound_event because polling and file drops land here too, not just webhooks. The name doesn't matter. The columns do.

-- Raw messages we RECEIVE. Stored first, processed later.
create table inbound_event (
  id              uuid primary key default gen_random_uuid(),
  tenant_id       uuid not null references tenant(id),
  connection_id   uuid references integration_connection(id),
  provider        text not null,
  external_id     text not null,        -- the sender's unique ID for this message
  topic           text not null,        -- what kind of event, e.g. 'orders/create'
  api_version     text,                 -- e.g. '2026-01' — the payload's schema
  raw_body        text not null,        -- the EXACT bytes we verified the signature over
  payload         jsonb not null,       -- the same thing, parsed, for querying
  headers         jsonb not null default '{}'::jsonb,
  signature_ok    boolean not null default false,
  occurred_at     timestamptz,          -- when it happened on THEIR side
  received_at     timestamptz not null default now(),  -- when it hit OUR door
  status          text not null default 'pending',
                  -- pending -> processing -> done | dead
  attempts        int not null default 0,
  next_attempt_at timestamptz not null default now(),
  locked_at       timestamptz,          -- when a worker claimed it (stuck-job detection)
  last_error      text,
  processed_at    timestamptz,
  unique (provider, external_id)        -- the dedupe lock: same message twice = one row
);

create index inbound_event_ready_idx
  on inbound_event (next_attempt_at) where status = 'pending';

create index inbound_event_tenant_idx
  on inbound_event (tenant_id, received_at desc);

create index inbound_event_stuck_idx
  on inbound_event (locked_at) where status = 'processing';

That is a lot of columns, so here is every one of them with its job spelled out. Read this table once now and refer back to it whenever a later code sample touches a column.

Then the indexes. inbound_event_ready_idx is a partial index — it only includes rows where status = 'pending'. The queue table will grow to millions of rows, of which perhaps twelve are pending at any moment; indexing only the pending ones keeps the worker's "find me the next job" query instant forever. inbound_event_tenant_idx powers the per-tenant activity feed in the UI. inbound_event_stuck_idx exists purely so a janitor job can ask "which rows have been processing for more than five minutes?" and reset them, which is how you recover from a worker that was killed mid-job.

The sync_issue error-inbox table

And the error inbox table, which gets a full section of its own later in this chapter:

-- The human-visible error inbox.
create table sync_issue (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references tenant(id),
  provider         text not null,
  kind             text not null,   -- 'dead_letter' | 'drift' | 'ack_overdue' | ...
  severity         text not null default 'error',
                                    -- critical | error | warning | info
  summary          text not null,   -- one sentence a human can act on
  detail           jsonb,
  fingerprint      text not null,   -- groups repeats of the same problem
  occurrences      int not null default 1,
  first_seen_at    timestamptz not null default now(),
  last_seen_at     timestamptz not null default now(),
  inbound_event_id uuid references inbound_event(id),
  resolved_at      timestamptz,
  resolved_by      uuid references app_user(id),
  created_at       timestamptz not null default now()
);

create unique index sync_issue_open_fingerprint_idx
  on sync_issue (tenant_id, fingerprint) where resolved_at is null;

Walking through the pieces:

• kind is the machine-readable category the UI switches on to decide which "fix it" button to show.
• severity ranks urgency.
• summary is the sentence a non-engineer reads.
• fingerprint is a stable hash of "what kind of problem, about what thing" — for example unknown_sku:TEE-NVY-XS.

The partial unique index below is the important trick: it enforces "at most one open issue per fingerprint per tenant." Without it, an unmapped SKU on a busy store produces 4,000 identical error rows in an hour and the inbox becomes useless noise. With it, you get one row whose occurrences counter ticks up to 4,000 — same information, one line, still actionable.

Notice it must be written as a separate create unique index … where rather than an inline unique (…) constraint, because Postgres only supports the where clause (the "partial" part) on indexes.

Receiving a webhook and checking its seal

Anyone on the internet can send an HTTP request to your endpoint. Your webhook URL is not a secret — it travels through logs, proxies, browser dev tools, and the vendor's dashboard. Assume attackers know it. So Shopify seals every webhook with an HMAC signature.

HMAC stands for Hash-based Message Authentication Code, and it works like a wax seal made from a shared secret. Shopify feeds two things into a math function (SHA-256): the exact bytes of the message, and your app's secret. Out comes a short fingerprint.

They put that fingerprint in a header called X-Shopify-Hmac-SHA256, base64-encoded. You run the same math on the body you received, using the same secret you hold. Same fingerprint means two things at once: the message really came from someone who knows the secret (authenticity), and not one byte of it changed in transit (integrity). Different fingerprint means reject.

Here is the receiving endpoint, written as a Next.js route handler (a file that answers HTTP requests at one address of your app):

// app/api/webhooks/shopify/route.ts
import { createHmac, timingSafeEqual } from "node:crypto";
import { sql } from "@/lib/db";

export async function POST(req: Request) {
  // 1. Read the body as RAW TEXT. Do not parse it first.
  const rawBody = await req.text();

  // 2. Recompute the seal and compare it in constant time.
  const theirSeal = req.headers.get("x-shopify-hmac-sha256") ?? "";
  const ourSeal = createHmac("sha256", process.env.SHOPIFY_CLIENT_SECRET!)
    .update(rawBody, "utf8")
    .digest("base64");
  const a = Buffer.from(ourSeal, "utf8");
  const b = Buffer.from(theirSeal, "utf8");
  if (a.length !== b.length || !timingSafeEqual(a, b)) {
    return new Response("invalid signature", { status: 401 });
  }

  // 3. Identify the message and which store sent it.
  const webhookId  = req.headers.get("x-shopify-webhook-id")!;
  const topic      = req.headers.get("x-shopify-topic")!;
  const shop       = req.headers.get("x-shopify-shop-domain")!;
  const apiVersion = req.headers.get("x-shopify-api-version");
  const triggered  = req.headers.get("x-shopify-triggered-at");

  // 4. Store it. Duplicates are silently absorbed by the unique constraint.
  const inserted = await sql`
    insert into inbound_event (
      tenant_id, connection_id, provider, external_id, topic,
      api_version, raw_body, payload, headers, signature_ok, occurred_at
    )
    select c.tenant_id, c.id, 'shopify', ${webhookId}, ${topic},
           ${apiVersion}, ${rawBody}, ${rawBody}::jsonb,
           ${JSON.stringify(Object.fromEntries(req.headers))}::jsonb,
           true, ${triggered}
    from integration_connection c
    where c.provider = 'shopify'
      and c.external_account_id = ${shop}
      and c.status <> 'disabled'
    on conflict (provider, external_id) do nothing
    returning id`;

  if (inserted.length === 0) {
    // Either a duplicate delivery (fine) or an unknown shop (not fine).
    // Both are safe to 200; the unknown-shop case is caught by a nightly audit.
  }

  // 5. Say "got it" fast. Real work happens later, elsewhere.
  return new Response("ok", { status: 200 });
}

Walking the handler, step by step

Line by line, slowly, because every line here is load-bearing.

Step 1. await req.text() reads the body as the exact characters that arrived, with no interpretation. Not req.json(). Reading the raw text first is a correctness requirement, and the callout below explains why.

Step 2. createHmac("sha256", secret) starts the math function primed with our shared secret. .update(rawBody, "utf8") feeds it the message. .digest("base64") finishes the computation and hands back the fingerprint as base64 text, which is the same encoding Shopify uses in the header, so we are comparing like with like.

Then the comparison. We turn both strings into Buffers (Node's raw byte arrays), check that they are the same length, and hand them to timingSafeEqual.

The length check is not optional: Node's timingSafeEqual throws if the two buffers have different byte lengths, so an attacker sending a one-character header would crash your handler with a 500 instead of getting a clean 401. Checking length first turns that into a normal rejection. If the seals don't match we answer 401 ("I don't believe you") and stop, having done zero work and touched zero tables.

Step 3. We pull the sticky-note headers: the delivery's unique ID (our dedupe key), the topic, which store sent it, the API version the payload conforms to, and when the underlying event was triggered on Shopify's side. Note the header names are written lowercase — HTTP header names are case-insensitive, and the standard Headers object normalizes them to lowercase, so get("X-Shopify-Topic") and get("x-shopify-topic") both work. Writing them lowercase makes that explicit.

Step 4. One insert that does three jobs at once. The select … from integration_connection form means we insert only if we can find a connection row for that shop domain, and it fetches the tenant_id in the same breath. This is how a single endpoint serves every tenant: the shop domain in the header is the lookup key that routes the message to the right brand. The status <> 'disabled' filter means a tenant who offboarded stops accepting webhooks without you having to remember to unsubscribe.

on conflict (provider, external_id) do nothing means: if a row with this webhook ID already exists, skip quietly and return no rows. That is the entire duplicate defense, and it lives in the database where it cannot be forgotten. returning id tells us whether anything was actually inserted.

Step 5. Answer 200. Total elapsed time: a few milliseconds, one round trip to Postgres. No SKU lookups, no inventory math, no calls to other services. Nothing that could take five seconds.

Why a naive === is a real vulnerability

Beginners look at timingSafeEqual and reasonably ask: why not if (ourSeal !== theirSeal) return 401? It's shorter and it's obviously correct. The answer is one of the most unsettling ideas in applied security: the comparison itself leaks the answer.

String comparison works by checking byte 0. If the bytes differ it stops immediately and returns false — no point looking further. If they match it checks byte 1, and so on. This short-circuiting makes comparison fast. It also means the comparison takes longer when more leading bytes match.

Now put on the attacker's hat. You send a fake payload with a guessed signature starting A.... The server fails at byte 0 and responds in 8.031 milliseconds. B...: 8.029 ms. C...: 8.047 ms. That last one took a hair longer — it matched the first byte, so the loop ran one iteration further before giving up.

The difference is nanoseconds, buried in network noise. But you are a computer: send each guess ten thousand times, average the results, and the noise cancels while the signal does not. Now you know byte 0. Repeat for byte 1. Then byte 2.

A base64 SHA-256 signature is 44 characters drawn from an alphabet of 64. Guessing the whole value means searching the 2²⁵⁶ possible SHA-256 digests, a search no computer will ever finish. Guessing it one character at a time means at most 64 × 44 = 2,816 tries — a laptop and an afternoon.

Your integration's security collapsed from astronomically hard to trivially easy because of a !==. This class of attack is called a timing side-channel, and it has broken real shipped software repeatedly for two decades.

timingSafeEqual fixes it by always comparing every byte, accumulating differences with bitwise operations instead of returning early. Its runtime depends only on the length of the inputs, never their contents, so there is no gradient for the attacker to climb. Since length still leaks, the strictly-correct pattern for variable-length inputs is to hash both sides first and compare the two 32-byte digests:

import { createHash, createHmac, timingSafeEqual } from "node:crypto";

/** Constant-time comparison of two strings of any length. */
export function safeEqual(x: string, y: string): boolean {
  const hx = createHash("sha256").update(x, "utf8").digest();
  const hy = createHash("sha256").update(y, "utf8").digest();
  return timingSafeEqual(hx, hy);   // both are always exactly 32 bytes
}

/** Verify a Shopify webhook. Returns true only for genuine, unmodified bodies. */
export function verifyShopifyHmac(rawBody: string, header: string, secret: string) {
  const expected = createHmac("sha256", secret)
    .update(rawBody, "utf8")
    .digest("base64");
  return safeEqual(expected, header);
}

safeEqual hashes each input with SHA-256, producing two buffers that are always exactly 32 bytes no matter what went in. Because the lengths are now guaranteed identical, timingSafeEqual can never throw, and because SHA-256 is one-way, the digests reveal nothing about the inputs. Identical inputs give identical digests; different inputs give different ones (finding a collision — two different inputs that hash to the same digest — is computationally infeasible, which is what makes the substitution safe).

verifyShopifyHmac wraps the whole ritual in one honest function name. Write one of these per provider, never inline the comparison, and never log the expected value.

Replay attacks, and the window you have to worry about

A valid signature proves a message is authentic and unmodified. It does not prove it is fresh. Suppose an attacker captures one genuine webhook — from a leaked log file, a compromised proxy, a debugging tool left on in production. That request has a perfectly valid signature, because the signature is over the body and the body hasn't changed. They can send it again. And again. Ten thousand times. This is a replay attack, and if your handler is not idempotent, "refund created" replayed a thousand times is a very bad afternoon.

Defense one: the dedupe key. Our unique (provider, external_id) constraint means the second copy of a captured webhook cannot be inserted, for as long as we keep the row. A replay becomes a no-op. Its limitation is retention — if you purge inbound_event after 90 days, a replay of a 91-day-old message is accepted as new. Purge to cold storage, or keep a slim permanent table of seen IDs.

Defense two: a freshness window. Some providers sign a timestamp along with the body specifically so you can reject old messages. Stripe is the canonical example: the header carries a timestamp and an HMAC computed over timestamp + "." + body, and you reject anything outside a tolerance window (five minutes is the common default). Shopify sends X-Shopify-Triggered-At but does not include it in the signed material, so an attacker could edit it freely. Treat that header as information only; it protects nothing. Know which situation you are in, because relying on an unsigned timestamp gives you a false sense of safety.

Defense three: idempotent handlers. Even if a replay slips through both nets, a handler written the way chapter 3 taught — keyed writes, on conflict do nothing, ledger entries with natural unique keys — turns a duplicate into a no-op at the business layer. Defense in depth means all three, not one.

const REPLAY_TOLERANCE_MS = 5 * 60 * 1000;  // 5 minutes

/**
 * For providers that SIGN a timestamp (e.g. Stripe-style headers).
 * Rejects messages that are too old to be a legitimate delivery or retry.
 */
export function withinFreshnessWindow(signedTimestampSeconds: string): boolean {
  const sentAtMs = Number(signedTimestampSeconds) * 1000;
  if (!Number.isFinite(sentAtMs)) return false;      // garbage in, reject
  const skewMs = Math.abs(Date.now() - sentAtMs);
  return skewMs <= REPLAY_TOLERANCE_MS;
}

We convert their timestamp from seconds (the usual unit in signature headers) to milliseconds (JavaScript's unit). Number.isFinite guards against a header that is missing, empty, or the string "abc" — all of which would produce NaN, and NaN comparisons are always false in confusing ways, so we reject explicitly instead.

Then we take the absolute difference between now and then, not a one-sided one, because clock skew can put their timestamp slightly in the future and you don't want to reject legitimate traffic over a two-second clock drift. Five minutes is generous enough to survive normal retry delays and tight enough that a captured request goes stale before an attacker finishes their coffee.

The three answers you can give, and what each one costs

Your webhook endpoint has exactly three meaningful replies, and each one starts a different chain of consequences at the sender. Beginners treat the status code as a formality. It is a control signal that reprograms the other company's retry machinery.

Sit with the middle row for a moment. It is worse than the bottom row, and it looks better. A 500 is loud: it shows up in your error tracker, it triggers alerts, Shopify emails you. A wrongly-returned 200 produces a green dashboard and a hole in your data.

The correct rule: return 200 if and only if the database transaction that inserted the row committed. (A transaction is a group of database statements that all succeed together or all fail together.) If the insert throws, let it throw — a 500 and a retry is the outcome you want.

The bottom row deserves attention too, because "the subscription is deleted after 8 consecutive failures" is the mechanism behind the six-day outage story at the start of this chapter. Two protections follow directly from it. First, treat a run of webhook failures as a page-someone incident, not a log line — you have four hours. Second, have a daily job that lists your active webhook subscriptions via the API and compares them against the set you expect, re-creating any that vanished. It is twenty lines of code and it converts a silent catastrophic failure into a self-healing annoyance.

The processor: retries, backoff, and the dead-letter tray

A separate worker — a scheduled job (for example a cron-triggered function) that runs every few seconds — drains the queue:

const MAX_ATTEMPTS = 8;
const BASE_DELAY_S = 30;

export async function runProcessorOnce() {
  // Claim exactly one ready event, so parallel workers never grab the same row.
  const [event] = await sql`
    update inbound_event
    set status = 'processing',
        attempts = attempts + 1,
        locked_at = now()
    where id = (
      select id from inbound_event
      where status = 'pending' and next_attempt_at <= now()
      order by received_at
      limit 1
      for update skip locked
    )
    returning *`;
  if (!event) return; // nothing to do

  try {
    await handleEvent(event);   // per-topic business logic; MUST be idempotent
    await sql`update inbound_event
              set status = 'done', processed_at = now(), locked_at = null
              where id = ${event.id}`;
  } catch (err) {
    const msg = String(err).slice(0, 500);
    if (err instanceof PermanentError || event.attempts >= MAX_ATTEMPTS) {
      await sql`update inbound_event
                set status = 'dead', last_error = ${msg}, locked_at = null
                where id = ${event.id}`;
      await raiseIssue({
        tenantId: event.tenant_id,
        provider: event.provider,
        kind: "dead_letter",
        severity: "error",
        fingerprint: `dead:${event.provider}:${event.topic}:${classify(msg)}`,
        summary: `${event.topic} failed permanently: ${msg}`,
        inboundEventId: event.id,
      });
    } else {
      const backoffS = BASE_DELAY_S * 2 ** (event.attempts - 1); // 30s,1m,2m,4m...
      const jitterS  = Math.floor(Math.random() * backoffS * 0.2);
      await sql`update inbound_event
                set status = 'pending',
                    next_attempt_at = now() + make_interval(secs => ${backoffS + jitterS}),
                    last_error = ${msg},
                    locked_at = null
                where id = ${event.id}`;
    }
  }
}

The first statement is clever, so take it apart from the inside out. The inner select finds one pending row whose retry clock has passed, oldest first. for update skip locked tells Postgres two things: "lock the row I pick," and "if another transaction already locked a candidate, don't wait — skip past it." That second half is what lets ten workers run simultaneously without ever colliding or blocking each other.

The outer update flips the row to processing, increments attempts, stamps locked_at, and returning * hands the row back to our code — all in one atomic statement, with no instant where another worker could see the row as available.

Then handleEvent does the real work. For orders/create that means inserting the order and decrementing ATS using the idempotent patterns from chapter 3, because retries mean the same event will be handled more than once in its lifetime. On success we mark it done, stamp processed_at, and clear the lock.

The catch block is where judgment lives, and note that it tests two conditions. attempts >= MAX_ATTEMPTS is the obvious one. err instanceof PermanentError is the important one: some failures will never succeed no matter how often you retry. A SKU that doesn't exist in this tenant's catalog is permanent. A QuickBooks validation rejection is permanent. A 401 from a revoked token is permanent.

Retrying those eight times over four hours wastes four hours and buries the signal. Classify errors into transient (network blips, 429, 503, deadlocks — retry) and permanent (validation, missing references, auth revoked — dead-letter at once), and your error inbox becomes useful in seconds instead of hours.

The retry branch computes BASE_DELAY_S * 2 ** (attempts - 1) — 30s, 1m, 2m, 4m, 8m, 16m, 32m — the "exponential" in exponential backoff. Doubling gives the other side room to recover rather than treating a struggling server as a punching bag.

Then we add jitter, a small random extra delay, for a specific reason. If a vendor has a 30-second outage, every event that failed during it now has an identical retry time, and all of them fire in the same millisecond — a self-inflicted stampede that can knock the recovering service straight back over. That's the thundering herd. Jitter smears the retries across a window and dissolves it: four characters of code, and the difference between a graceful recovery and a second outage.

The janitor that rescues orphaned jobs

One piece the code implies but doesn't show: the janitor. If a worker is killed mid-job — a deploy, an out-of-memory kill, a serverless timeout — its row stays processing forever, because nothing is alive to update it. That is what locked_at and inbound_event_stuck_idx are for:

-- Runs every minute. Rescues events orphaned by a dead worker.
update inbound_event
set status = 'pending',
    locked_at = null,
    next_attempt_at = now() + interval '10 seconds',
    last_error = coalesce(last_error, '') || ' [reclaimed after worker timeout]'
where status = 'processing'
  and locked_at < now() - interval '5 minutes';

The logic: any row that has been claimed for longer than five minutes is presumed abandoned, because no legitimate handler should take that long. We push it back to pending with a short delay so it gets picked up promptly, and we append a note to last_error so that when a human eventually reads it, the word "reclaimed" tells them a worker died rather than the payload being bad.

Note that attempts was already incremented when the row was claimed, so an event that repeatedly kills workers will still eventually dead-letter instead of looping forever. That is deliberate: an event that reliably crashes your process is exactly the kind of thing a human needs to see.

Shopify: selling your styles online

Shopify hosts your customers' online stores. For our ERP it is both a source of truth (orders, customers) and a destination (we push inventory levels and product data). Two things to know before writing a line of code.

First, which API. Shopify historically offered two styles: REST, where each kind of thing has its own address (/orders.json, /products.json) and you get a fixed response shape, and GraphQL, where there is one address and you send a small text query describing exactly which fields you want back. Shopify has decided this for you. Its documentation states that "the REST Admin API is a legacy API as of October 1, 2024," and that "starting April 1, 2025, all new public apps must be built exclusively with the GraphQL Admin API." Build on the GraphQL Admin API.

The calculated query cost model, worked out by hand

Every API sets a speed limit so one greedy client can't overload it. Most APIs count requests: "100 requests per minute." Shopify's GraphQL limit can't work that way, because one GraphQL request might fetch a single field or might fetch fifty orders with all their line items. So Shopify counts calculated query cost in points.

It works like a water tank with a hole in the bottom. Each query you send scoops points out of the tank. Meanwhile the tank refills at a steady restore rate, forever. If you scoop slowly you never run dry. If you scoop faster than the refill, the tank empties and Shopify stops serving you until it fills back up. Computer scientists call this a leaky bucket or a token bucket; the plumbing analogy is exact.

The restore rates, from Shopify's rate-limit documentation as of July 2026:

A caution about that third column. Shopify publishes the restore rates but does not publish a table of bucket sizes. In the sample responses in its own documentation the capacity works out at twenty times the restore rate, which is where these figures come from, so treat them as a guide rather than a contract. The number you can actually trust arrives in every response as maximumAvailable, described in a moment. Read it instead of assuming.

And one hard rule that applies on every plan, no exceptions: a single query may not exceed a cost of 1,000 points. Even an enterprise merchant with a 40,000-point bucket cannot run a 1,001-point query. This trips people up because it feels like a bucket problem and it isn't — it's a per-query ceiling, checked before the bucket is even consulted.

Now the computation itself. Shopify assigns cost by the return type of each field you request. Three words first:

• A scalar is a single plain value — a number, a piece of text, a yes/no.
• An object is a nested thing that has fields of its own.
• A connection is a list you page through, like "the first 50 orders."

Costing a real order-sync query

Now cost a real query — the one an ERP actually runs, pulling recently-updated orders for reconciliation. GraphQL wraps paged lists in a standard shape, so learn the four words now:

• edges is the list of results.
• Each node is one real record.
• Each cursor is a bookmark pointing at that record, which you hand back to ask for the next page.
• And pageInfo tells you whether more results are waiting.

query OrderSync($cursor: String) {
  orders(first: 50, after: $cursor, query: "updated_at:>2026-07-01") {
    edges {
      cursor
      node {
        id                        # scalar  -> 0
        name                      # scalar  -> 0
        createdAt                 # scalar  -> 0
        updatedAt                 # scalar  -> 0
        displayFinancialStatus    # enum    -> 0
        displayFulfillmentStatus  # enum    -> 0
        totalPriceSet {           # object  -> 1
          shopMoney {             # object  -> 1
            amount currencyCode   # scalars -> 0
          }
        }
        customer {                # object  -> 1
          id email                # scalars -> 0
        }
        lineItems(first: 20) {    # connection -> ~2 + 20 x 1
          edges { node {
            id sku quantity     # scalars -> 0 (each node is an object -> 1)
          } }
        }
      }
    }
    pageInfo { hasNextPage endCursor }   # object -> 1
  }
}

Now the arithmetic, from the inside out. Every scalar and enum is free, so all those id, name, sku, quantity fields contribute nothing — that's the first pleasant surprise, and it means you should never hesitate to ask for more scalar fields. What costs money is structure.

Inside one order: totalPriceSet is an object (1), shopMoney nested inside it is another object (1), customer is an object (1). The lineItems connection is sized by its first: 20 — a connection costs a small fixed amount for itself plus the per-node cost times the page size, and each line-item node is an object, so call it roughly 2 + 20 = 22. Add the order node itself as an object (1) and one order costs about 26 points.

Now the outer connection: orders(first: 50). That is roughly 2 + (50 × 26) = 1,302, plus 1 more for the pageInfo object, so about 1,303 points. Shopify rejects that query outright rather than throttling it, on every plan, because it exceeds the 1,000-point single-query ceiling. A Plus merchant with a 20,000-point bucket gets exactly the same refusal as the smallest store.

The fix is to shrink the page sizes, and notice how sharply the cost falls, because the two multiply:

Here is what that block actually looks like coming back on the wire:

{
  "data": { "orders": { "edges": [ /* ... */ ] } },
  "extensions": {
    "cost": {
      "requestedQueryCost": 403,
      "actualQueryCost": 62,
      "throttleStatus": {
        "maximumAvailable": 2000.0,
        "currentlyAvailable": 1938.0,
        "restoreRate": 100.0
      }
    }
  }
}

Read it as a fuel gauge. Shopify budgeted 403 points for this query, then discovered the store only had a few matching orders and charged 62. The tank holds 2,000, currently has 1,938 left, and refills at 100 per second — meaning it will be completely full again in under a second. If currentlyAvailable were 90 and your next query needs 403, you know precisely how long to wait: (403 − 90) ÷ 100 = 3.13 seconds. No guessing, no blind sleeps.

A throttle-aware client you can actually ship

type ThrottleStatus = {
  maximumAvailable: number;    // tank capacity
  currentlyAvailable: number;  // points left right now
  restoreRate: number;         // points refilled per second
};

// Remembered per shop between calls, so we can pace BEFORE we get punished.
const gauges = new Map<string, ThrottleStatus>();

export async function shopifyQuery<T>(
  shop: string, accessToken: string, query: string,
  variables?: object, estimatedCost = 200
): Promise<T> {
  for (let attempt = 1; attempt <= 5; attempt++) {
    // A. Proactive pacing: if we already know the tank is low, wait first.
    const known = gauges.get(shop);
    if (known && known.currentlyAvailable < estimatedCost) {
      const deficit = estimatedCost - known.currentlyAvailable;
      await sleep(Math.ceil(deficit / known.restoreRate) * 1000 + 100);
    }

    const res = await fetch(`https://${shop}/admin/api/2026-01/graphql.json`, {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "X-Shopify-Access-Token": accessToken,
      },
      body: JSON.stringify({ query, variables }),
    });

    // B. Transport-level throttling (rare on GraphQL, standard on REST).
    if (res.status === 429) {
      const retryAfter = Number(res.headers.get("retry-after") ?? "2");
      await sleep(retryAfter * 1000);
      continue;
    }
    if (res.status >= 500) {                 // their problem; back off and retry
      await sleep(1000 * 2 ** (attempt - 1));
      continue;
    }

    const body = await res.json();

    // C. Update the gauge from EVERY response, throttled or not.
    const cost = body.extensions?.cost;
    if (cost?.throttleStatus) gauges.set(shop, cost.throttleStatus);

    // D. GraphQL-level throttling arrives as HTTP 200 with an error object.
    const throttled = body.errors?.some(
      (e: any) => e.extensions?.code === "THROTTLED");
    if (!throttled) {
      if (body.errors?.length) {
        throw classifyShopifyError(body.errors);  // permanent vs transient
      }
      return body.data as T;
    }

    // E. Wait exactly long enough for the tank to refill, then retry.
    const t: ThrottleStatus = cost.throttleStatus;
    const needed  = cost.requestedQueryCost ?? estimatedCost;
    const deficit = Math.max(0, needed - t.currentlyAvailable);
    await sleep(Math.ceil(deficit / t.restoreRate) * 1000 + 250);
  }
  throw new Error(`Shopify still throttling ${shop} after 5 attempts`);
}

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

Section by section. The gauges map remembers the last known throttle status per shop, and it is the most valuable line in the function: it lets step A slow down before being refused rather than after. A reactive-only client sends a doomed query, waits for a rejection, then waits again for refill — two round trips to learn something the previous response already told it. Step A sleeps for exactly the shortfall divided by the refill rate plus a 100 ms cushion; on an idle store it never fires.

Note the API version 2026-01 baked into the fetch URL. Shopify releases a new version every quarter and supports each one for at least a year, so this string is a deliberate, greppable choice. Pinning it means a schema change can never surprise you at 3 a.m.; you upgrade on your own schedule, in a branch, with tests. The X-Shopify-Access-Token header is our ID badge for this store — the OAuth section explains where it comes from.

Step B handles transport errors. GraphQL usually signals throttling inside a 200, but you can still meet a real HTTP 429 through proxies or on REST endpoints; the Retry-After header tells you exactly how long to wait, so respect it rather than guessing. A 5xx is their infrastructure failing, transient by definition, so we back off and retry. Step C updates the gauge from every response, successful or not; a fuel gauge is only useful if it is current.

Step D is the part that surprises everyone: a throttled GraphQL request comes back as HTTP 200 OK with an errors array containing extensions.code === "THROTTLED". If you only check res.ok, you will treat a throttle as a success and read undefined out of body.data. Errors that are not throttles go to classifyShopifyError, which sorts transient from permanent — the same classification the processor uses.

Step E does the arithmetic: how many points short are we (deficit), how many seconds until the tank refills that much (deficit / restoreRate), wait, loop. This is why you should never write await sleep(5000) in a Shopify client.

Bulk Operations: syncing a whole catalog

An apparel brand's catalog is large: 200 styles × 8 colorways × 6 sizes is nearly 10,000 SKUs. Fetching that through normal queries means paging through results a chunk at a time while burning rate-limit points for minutes. Worse, a long paginated crawl is fragile — page 340 of 400 fails, and now you have to decide whether to restart or resume.

Shopify's answer is the Bulk Operations API: you hand Shopify a query and say "run this over your whole dataset on your own time." Shopify runs it in the background, no rate-limit cost to you while it runs, then gives you a link to download the full result as a file. It is the difference between asking a librarian 10,000 questions and asking them to mail you a photocopy of the whole catalog.

You start one with a mutation:

mutation StartCatalogSync {
  bulkOperationRunQuery(query: """
    {
      products(query: "status:active") {
        edges {
          node {
            id
            handle
            title
            updatedAt
            variants {
              edges {
                node {
                  id
                  sku
                  inventoryQuantity
                  selectedOptions { name value }
                }
              }
            }
          }
        }
      }
    }
  """) {
    bulkOperation { id status createdAt }
    userErrors { field message }
  }
}

The triple-quoted block in the middle is itself a GraphQL query — "every active product, and for each one its variants with SKU, inventory count, and options like Color and Size." Two details make it different from a normal query. First, notice there are no first: arguments on the connections: bulk queries fetch everything, which is the whole point. Second, the outer bulkOperationRunQuery mutation does not return your data; it returns a job ID and a status (initially CREATED) and then hangs up.

Always read userErrors — GraphQL mutations report business-rule failures there while still returning HTTP 200, so a mutation can "succeed" and have done nothing at all.

The limits worth memorizing, because you will hit them: a bulk query may nest connections at most two levels deep and may contain at most five connections total. As of API version 2026-01, each app can run up to five bulk query operations per shop simultaneously; before that, it was one operation of each type per shop at a time. So on older versions, "start a bulk sync" must first check whether one is already running.

Now the full lifecycle in code:

import { createInterface } from "node:readline";
import { Readable } from "node:stream";

type BulkOp = {
  id: string;
  status: "CREATED" | "RUNNING" | "COMPLETED" | "FAILED" | "CANCELED";
  errorCode: string | null;
  objectCount: string;
  url: string | null;
};

/** 1. Kick off the job. */
export async function startBulkProductSync(shop: string, token: string) {
  const r = await shopifyQuery<any>(shop, token, START_CATALOG_SYNC_MUTATION);
  const errs = r.bulkOperationRunQuery.userErrors;
  if (errs.length) throw new PermanentError(JSON.stringify(errs));
  return r.bulkOperationRunQuery.bulkOperation.id as string;
}

/** 2. Ask how it's going. Cheap query; safe to call on a timer. */
export async function pollBulkOp(shop: string, token: string, id: string) {
  const r = await shopifyQuery<any>(shop, token, `
    query($id: ID!) {
      node(id: $id) {
        ... on BulkOperation {
          id status errorCode objectCount url
        }
      }
    }`, { id });
  return r.node as BulkOp;
}

/** 3. Download and stream-parse the JSONL result. */
export async function consumeBulkResult(
  url: string,
  onRecord: (obj: any) => Promise<void>
) {
  const res = await fetch(url);
  if (!res.ok) throw new Error(`bulk download failed: ${res.status}`);

  const lines = createInterface({
    input: Readable.fromWeb(res.body as any),
    crlfDelay: Infinity,
  });

  let n = 0;
  for await (const line of lines) {
    if (!line.trim()) continue;      // JSONL files end with a newline
    await onRecord(JSON.parse(line));
    n++;
  }
  return n;
}

Walking through the three functions. startBulkProductSync sends the mutation and immediately inspects userErrors. If Shopify rejected the query shape — too many connections, nesting too deep, a field you lack permission for — it says so here, and retrying will not change its mind, so we throw a PermanentError that the processor dead-letters straight into the error inbox. Otherwise we return the operation's id and store it on our sync-run row.

pollBulkOp fetches the operation's current state. The ... on BulkOperation syntax is a GraphQL inline fragment: node(id:) can return many types, so this says "if what came back is a BulkOperation, give me these fields." objectCount is a running total of objects processed — a genuinely nice progress bar. errorCode explains a FAILED status: ACCESS_DENIED (insufficient scopes), TIMEOUT (query too heavy), INTERNAL_SERVER_ERROR. On API 2026-01 and later you query bulkOperation(id:) directly; the older currentBulkOperation is deprecated, which makes sense once five can run at once — "current" stops being meaningful.

consumeBulkResult is the part beginners get wrong. The naive version is const text = await res.text(); text.split("\n").map(JSON.parse), which works beautifully on a 40-product test store and falls over on a real catalog, because it loads the whole file into memory twice — once as a giant string, once as an array. A 10,000-SKU export can be hundreds of megabytes.

Instead we treat the response as a stream: Readable.fromWeb adapts the web-standard body to a Node stream, createInterface chops it into lines, and for await hands us one line at a time. Memory stays flat no matter how big the file is — a conveyor belt rather than a warehouse. crlfDelay: Infinity handles Windows line endings, and the !line.trim() guard skips the trailing blank line JSONL files end with.

How the JSONL result file is flattened

JSONL means JSON Lines: one complete JSON object per line, no wrapping array, no commas — a format that exists precisely so it can be streamed. But since a flat file cannot nest, Shopify flattens your nested query. Variants do not appear inside their products; they appear as their own lines, each carrying a __parentId pointing back at the product they belong to. Here are four records, wrapped so they fit the page — in the real file each record is one long line with no line breaks inside it:

{"id":"gid://shopify/Product/811","handle":"classic-tee",
   "title":"Classic Tee","updatedAt":"2026-07-14T09:12:00Z"}
{"id":"gid://shopify/ProductVariant/44551","sku":"TEE-BLK-M",
   "inventoryQuantity":21,
   "selectedOptions":[{"name":"Color","value":"Black"},
                      {"name":"Size","value":"M"}],
   "__parentId":"gid://shopify/Product/811"}
{"id":"gid://shopify/ProductVariant/44552","sku":"TEE-BLK-L",
   "inventoryQuantity":8,
   "selectedOptions":[{"name":"Color","value":"Black"},
                      {"name":"Size","value":"L"}],
   "__parentId":"gid://shopify/Product/811"}
{"id":"gid://shopify/Product/812","handle":"crew-hoodie",
   "title":"Crew Hoodie","updatedAt":"2026-07-14T09:12:00Z"}

Record 1 is a product. Records 2 and 3 are variants of that product — you can tell because their __parentId equals record 1's id. Record 4 is the next product, and the pattern repeats. The gid://shopify/Product/811 format is a global ID: Shopify's GraphQL identifiers encode the type as well as the number, which is handy because you can tell what a thing is from its ID alone.

Shopify guarantees the ordering. Its documentation says "the order of each connection type is preserved and all nested connections appear after their parents in the file," which means you can assemble records with a simple running buffer instead of a second pass:

export async function importCatalog(tenantId: string, url: string) {
  let current: { product: any; variants: any[] } | null = null;
  let productsSeen = 0;

  const flush = async () => {
    if (!current) return;
    await upsertStyleAndSkus(tenantId, current.product, current.variants);
    productsSeen++;
    current = null;
  };

  await consumeBulkResult(url, async (obj) => {
    if (!obj.__parentId) {
      await flush();                       // finish the previous product
      current = { product: obj, variants: [] };
    } else if (current && obj.__parentId === current.product.id) {
      current.variants.push(obj);
    } else {
      // Out-of-order child: should not happen, but never silently drop data.
      await recordOrphan(tenantId, obj);
    }
  });

  await flush();                           // don't forget the last one
  return productsSeen;
}

The logic in plain English. We keep one product "in hand" at a time in current. When a line has no __parentId, it's a new product: we first flush the previous one (writing it and its collected variants to our database in one transaction), then start holding the new one. When a line has a __parentId matching the product in hand, it's one of that product's variants, so we push it onto the list.

The else branch is the defensive one: if a child arrives whose parent we aren't holding, we don't crash and we don't silently discard it — we record it as an orphan so a human can see that our assumption broke.

And the final flush() after the loop is the classic off-by-one: without it, the last product in the file is silently dropped, which is a bug that takes about two hours to find because everything looks fine except one style is always missing.

Knowing when the bulk job finished

Finally, you learn that the job finished in one of two ways. Polling: call pollBulkOp every 10–30 seconds. Simple, works everywhere, wastes a little quota. The webhook: subscribe to the bulk_operations/finish topic and Shopify calls you when any bulk operation on the store completes, with the operation's ID and status.

That is the recommended approach, and, pleasingly, it arrives through the exact same webhook endpoint, gets stored in the same inbound_event table, and is processed by the same worker as everything else. The download URL you get back is signed and expires after one week, so fetch it promptly; don't store the URL as if it were permanent.

Webhook topics that actually matter to an ERP

Shopify offers hundreds of webhook topics. Subscribing to all of them is a rookie move — you drown your queue in events nobody consumes. Here are the ones an apparel ERP genuinely needs, and what each one should do:

The inventory_levels/update echo loop deserves a warning of its own, because it is a genuinely nasty bug. Your ERP pushes "TEE-BLK-M is now 21" to Shopify. Shopify fires inventory_levels/update. Your handler receives it and writes 21 to your database, and your outbox (the table from chapter 3 that queues up changes waiting to be pushed outward) notices the change and pushes it to Shopify. Which fires another webhook.

If the two systems ever disagree by even one unit for any reason, this ping-pongs forever, burning rate limit and filling your queue.

The fix is to make the handler a genuine no-op when the value already matches, and to record "we set this value at time T" so an echo arriving within a few seconds of your own write is recognized and dropped.

Webhooks arrive at-least-once, and sometimes never

Shopify's webhook delivery is at-least-once: they guarantee to try, and to retry on failure, which mathematically means you will sometimes receive the same event twice. That is exactly why our queue table deduplicates on X-Shopify-Webhook-Id. The delivery contract, precisely:

• your endpoint must respond 200 within 5 seconds (with a 1-second connection timeout);
• anything else, including redirects, counts as failure;
• Shopify then retries 8 times over 4 hours;
• and after 8 consecutive failed deliveries an API-created subscription is deleted, with warning emails to your emergency developer contact.

Shopify's own documentation goes further, saying in effect: do not trust us. Their best-practices guide states plainly that webhook delivery isn't always guaranteed and that your app can miss or mishandle events for other reasons, such as handler failures or downtime, and it recommends that your app shouldn't rely on receiving data from webhooks, but should run reconciliation jobs to periodically fetch data from Shopify so it stays consistent. When the vendor tells you their delivery is unreliable, believe them and design accordingly. A webhook-only integration will drift.

Webhooks also arrive out of order. Two events fired 50 milliseconds apart can be delivered in either sequence, because they take independent network paths and one may be retried. The practical consequence: an orders/updated reflecting the order's state at 10:05 can land after one reflecting 10:06, and a naive handler will happily overwrite newer data with older. This is why occurred_at is in our schema. The defense is a version guard on every write:

-- Apply an order update ONLY if it is newer than what we already have.
insert into sales_order (tenant_id, external_id, source_updated_at, status, total_cents)
values ($1, $2, $3, $4, $5)
on conflict (tenant_id, external_id) do update
set status            = excluded.status,
    total_cents       = excluded.total_cents,
    source_updated_at = excluded.source_updated_at
where sales_order.source_updated_at < excluded.source_updated_at;

Reading the SQL: we try to insert the order. If a row already exists for this tenant and external ID, the do update branch runs, but only for rows matching the trailing where clause. excluded is Postgres's name for "the row we were trying to insert." So the condition reads: update only if the incoming version's timestamp is strictly newer than the stored one. An out-of-order older event matches nothing, updates nothing, and is discarded — silently and correctly. This one where clause eliminates an entire category of ghost bug where canceled orders resurrect themselves.

Reconciliation, and the drift table

Drift deserves its own table rather than being crammed into sync_issue, because drift has structure you want to query: what disagreed, what each side said, and how long it has been disagreeing.

create table integration_drift (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references tenant(id),
  provider      text not null,
  entity_type   text not null,     -- 'inventory_level' | 'order' | 'product'
  entity_key    text not null,     -- 'TEE-BLK-M@DC-1' or '#1042'
  field         text not null,     -- 'available' | 'financial_status' | 'total'
  ours          jsonb not null,
  theirs        jsonb not null,
  magnitude     numeric,           -- signed difference when numeric; null otherwise
  first_seen_at timestamptz not null default now(),
  last_seen_at  timestamptz not null default now(),
  occurrences   int not null default 1,
  resolution    text,              -- 'accepted_theirs' | 'accepted_ours' | 'explained'
  resolved_at   timestamptz,
  resolved_by   uuid references app_user(id)
);

-- At most ONE open drift row per (thing, field). Repeats bump the counter.
create unique index integration_drift_open_idx
  on integration_drift (tenant_id, provider, entity_type, entity_key, field)
  where resolved_at is null;

Column notes:

• entity_key is a human-readable identifier — deliberately not a UUID, because this table is read by warehouse managers, not engineers.
• ours and theirs are JSON so the same table can hold a number, a string status, or a whole nested object.
• magnitude exists so you can sort by "worst first": a one-unit discrepancy on a slow-moving style is noise, a 400-unit discrepancy is an emergency, and a flat list treats them identically.
• first_seen_at versus last_seen_at answers the single most diagnostic question about drift — is this new, or has it been wrong for a month? A drift that appeared once and never recurred is probably a race during a write. A drift that has recurred for eleven consecutive nights is a systematic bug.
• And the partial unique index does the same de-noising job as in sync_issue: one open row per disagreement, with a counter, instead of thirty identical rows.

The nightly reconciliation job, end to end

Here is the job itself, written out end to end:

const OVERLAP_MINUTES = 60;

export async function reconcileShopifyInventory(conn: IntegrationConnection) {
  const token = await getShopifyToken(conn.id);
  const since = new Date(Date.now() - 24 * 60 * 60 * 1000
                                   - OVERLAP_MINUTES * 60 * 1000);
  let cursor: string | null = null;
  let checked = 0, drifted = 0;

  do {
    // 1. Ask Shopify for the truth, one modest page at a time.
    const page: any = await shopifyQuery(conn.external_account_id, token, `
      query($cursor: String, $q: String!) {
        productVariants(first: 25, after: $cursor, query: $q) {
          edges {
            cursor
            node {
              sku
              inventoryItem {
                inventoryLevels(first: 5) {
                  edges { node {
                    location { id name }
                    quantities(names: ["available"]) { name quantity }
                  } }
                }
              }
            }
          }
          pageInfo { hasNextPage endCursor }
        }
      }`,
      { cursor, q: `updated_at:>'${since.toISOString()}'` },
      500 // estimated cost, for proactive pacing
    );

    for (const { node } of page.productVariants.edges) {
      if (!node.sku) continue;                       // unsku'd variants are not ours
      for (const lvl of node.inventoryItem.inventoryLevels.edges) {
        const locationName = lvl.node.location.name;
        const theirQty = lvl.node.quantities
          .find((q: any) => q.name === "available")?.quantity ?? 0;

        // 2. What do WE think?
        const [row] = await sql`
          select on_hand from inventory_position
          where tenant_id = ${conn.tenant_id}
            and sku = ${node.sku}
            and location_name = ${locationName}`;
        const ourQty = row?.on_hand ?? 0;
        checked++;

        if (ourQty === theirQty) {
          // 3a. Agreement closes any previously open drift for this key.
          await sql`
            update integration_drift
            set resolved_at = now(), resolution = 'explained'
            where tenant_id = ${conn.tenant_id} and provider = 'shopify'
              and entity_type = 'inventory_level'
              and entity_key = ${`${node.sku}@${locationName}`}
              and field = 'available' and resolved_at is null`;
          continue;
        }

        // 3b. Disagreement: record it, or bump the existing record.
        drifted++;
        await sql`
          insert into integration_drift (
            tenant_id, provider, entity_type, entity_key, field,
            ours, theirs, magnitude)
          values (
            ${conn.tenant_id}, 'shopify', 'inventory_level',
            ${`${node.sku}@${locationName}`}, 'available',
            ${JSON.stringify(ourQty)}::jsonb,
            ${JSON.stringify(theirQty)}::jsonb,
            ${theirQty - ourQty})
          on conflict (tenant_id, provider, entity_type, entity_key, field)
            where resolved_at is null
          do update set
            theirs       = excluded.theirs,
            ours         = excluded.ours,
            magnitude    = excluded.magnitude,
            last_seen_at = now(),
            occurrences  = integration_drift.occurrences + 1`;
      }
    }

    cursor = page.productVariants.pageInfo.hasNextPage
      ? page.productVariants.pageInfo.endCursor
      : null;
  } while (cursor);

  // 4. One summary issue if the drift is bad enough to warrant attention.
  if (drifted > 0) {
    await raiseIssue({
      tenantId: conn.tenant_id,
      provider: "shopify",
      kind: "drift",
      severity: drifted > checked * 0.02 ? "critical" : "warning",
      fingerprint: "drift:shopify:inventory",
      summary: `${drifted} of ${checked} inventory levels disagree with Shopify`,
    });
  }
  await sql`update integration_connection
            set last_success_at = now() where id = ${conn.id}`;
}

Start with the overlap: since is 24 hours ago minus another hour, deliberate redundancy against clock disagreement and records committed with timestamps a moment in the past. Re-checking an hour of already-checked data is nearly free; missing a record forever is not.

Step 1 — ask Shopify. We page through variants updated in the window, 25 at a time, requesting each one's inventory levels per location. The small page size is the cost model biting again: each variant carries a nested inventoryLevels connection, so one variant costs roughly 19 points rather than 1, and 25 of them lands near 480. Ask for 100 and you sail past the 1,000-point ceiling and get nothing back.

Note quantities(names: ["available"]): Shopify's inventory model exposes several named quantities (available, committed, incoming, on hand), and "available" is what a shopper can actually buy. We pass an estimated cost of 500 so proactive pacing knows this is a heavy query.

Step 2 — ask ourselves. A direct read of our own inventory_position. In production you would batch these one query per page rather than one per level; it is written per-level here for readability, which is a trade-off worth making consciously.

Step 3a — agreement heals. Easy to forget, and important: if the two sides now agree, any previously-open drift row for that key closes automatically. Without this the drift list only grows, engineers stop reading it, and the mechanism dies of noise. Anything that raises an alert must also know how to clear it.

Step 3b — disagreement records. The insert … on conflict … do update is an upsert against the partial unique index: the first sighting inserts a row, every subsequent night refreshes the values, bumps last_seen_at, and increments occurrences. The on conflict clause repeats the index's where resolved_at is null predicate because Postgres needs to know which partial index you mean. magnitude is signed (theirs − ours), so positive means Shopify thinks there is more stock than we do — the first thing a warehouse manager looks at.

Step 4 — one summary, severity by proportion. Three drifting SKUs out of 4,000 is routine. Three hundred out of 4,000 means something structural broke — a location remapped, a bulk import run twice, and should page a human. Scaling severity by percentage rather than raw count keeps the alert meaningful as a tenant grows. Finally we stamp last_success_at so the "this connection has gone quiet" monitor stays satisfied.

Webhooks, polling, or bulk: choosing per job

You now have three ways to get data from the same system, and each one suits a different job.

A healthy Shopify integration uses all three: webhooks for speed, reconciliation polling for trust, bulk for scale. If you only build one, build reconciliation — it is the one that tells you the other two are broken.

A detour you can't skip: OAuth2, or borrowing keys safely

How does your ERP get permission to read a tenant's QuickBooks company or Shopify store in the first place? Nobody should ever type their QuickBooks password into your app. If they did, you would be storing a credential that opens everything — banking, payroll, tax filings — with no way to limit it and no way for them to revoke it without changing their password everywhere.

The industry's answer is OAuth2. The analogy is a valet key. The tenant doesn't give you the master key to their car; they ask the manufacturer to cut a limited key that opens only the doors you need, that stops working after a while, and that they can cancel any time from their own dashboard without affecting anything else.

The authorization code flow, step by numbered step

The flow you'll implement is called the authorization code flow. Every OAuth2 provider you will ever meet — Shopify, Intuit, Google, Xero — implements this same dance. Learn it once.

1. The user clicks "Connect QuickBooks" in your app. This is the only part they experience as a button.
2. Your server generates a state value — a long random string, and stores it, tied to this user's session, with a short expiry. We'll come back to why in a moment.
3. Your server redirects the browser to the provider's authorization endpoint, with your app's identity and your requests attached as URL parameters.
4. The user logs in on the provider's own site. Their password is typed into intuit.com, never into your domain. You never see it, never store it, and can never leak it.
5. The provider shows a consent screen: "Acme ERP wants to read and write your accounting data." The user clicks Allow (or Deny, and you must handle that).
6. The provider redirects the browser back to your redirect_uri, carrying two things in the query string: a short-lived, single-use authorization code, and the same state you sent in step 2. For Intuit it also carries a realmId identifying which QuickBooks company was chosen.
7. Your server checks that state matches what it stored in step 2. If it doesn't, abort immediately — no exceptions, no "probably fine."
8. Your server exchanges the code for tokens with a direct, server-to-server POST to the provider's token endpoint. This request carries your app's own client ID and client secret. It never touches the browser.
9. The provider returns an access token and a refresh token. The access token is the valet key: you attach it to API requests as a header (Authorization: Bearer <token>). It expires quickly — 60 minutes at Intuit. The refresh token is a longer-lived voucher for getting fresh access tokens without bothering the user again.
10. You encrypt both and store them in integration_connection, on a row scoped to this tenant. That per-tenant row is what makes a multi-tenant integration possible at all.

Step 8 exists instead of the obvious shortcut, where the provider redirects back with the access token directly and saves a round trip. The redirect goes through the user's browser, and anything in a browser URL is visible: it lands in browser history, in server access logs, in the Referer header sent to the next site the user visits, and on the screen of anyone standing behind them.

So the redirect carries only a code that is useless on its own — it can be redeemed exactly once, expires in minutes, and only by someone who also holds your client secret. The token itself travels over a private server-to-server channel that no browser ever sees. That split is the entire security architecture of OAuth2 in one sentence.

Here is what each parameter in step 3 is actually for:

The state parameter, and the attack it stops

CSRF stands for Cross-Site Request Forgery: tricking a logged-in user's browser into making a request the user didn't intend. In the OAuth context the attack is subtle and worth understanding properly, because "add a random string" sounds like a formality until you see what it prevents.

The attacker starts the OAuth flow themselves, against their own QuickBooks company. They get as far as step 6 and grab the authorization code out of the redirect, but they don't complete the flow. Instead they craft a link containing that code and get your logged-in customer to click it. Your server receives a callback with a valid code, exchanges it, and stores the resulting tokens on your customer's tenant row. Now your customer's ERP is happily syncing invoices into the attacker's QuickBooks company. Every invoice your customer creates, the attacker can read. Nothing looked broken; the integration reported "connected."

The state parameter kills this. You generate a random value, store it against the session that initiated the flow, and send it along. The provider echoes it back verbatim. On callback you check that the returned state matches one you issued to this session, and that it hasn't been used already. The attacker's code arrives with either no state or a state you never issued, and you reject it. One comparison, entire attack class gone.

import { randomBytes } from "node:crypto";

/** Step 2–3: start the flow. */
export async function beginQboConnect(tenantId: string, userId: string) {
  const state = randomBytes(32).toString("base64url");

  // Bind the state to WHO started it and WHICH tenant it is for, with an expiry.
  await sql`
    insert into oauth_state (state, tenant_id, user_id, provider, expires_at)
    values (${state}, ${tenantId}, ${userId}, 'quickbooks',
            now() + interval '10 minutes')`;

  const url = new URL("https://appcenter.intuit.com/connect/oauth2");
  url.searchParams.set("client_id",     process.env.QBO_CLIENT_ID!);
  url.searchParams.set("response_type", "code");
  url.searchParams.set("scope",         "com.intuit.quickbooks.accounting");
  url.searchParams.set("redirect_uri",  process.env.QBO_REDIRECT_URI!);
  url.searchParams.set("state",         state);
  return url.toString();       // redirect the browser here
}

/** Step 6–10: handle the callback. */
export async function completeQboConnect(req: Request) {
  const params  = new URL(req.url).searchParams;
  const code    = params.get("code");
  const state   = params.get("state");
  const realmId = params.get("realmId");
  const error   = params.get("error");

  if (error) return fail(`user declined or provider error: ${error}`);
  if (!code || !state || !realmId) return fail("missing callback parameters");

  // Consume the state ONCE. If it's absent, expired, or already used, stop.
  const [claim] = await sql`
    delete from oauth_state
    where state = ${state} and provider = 'quickbooks' and expires_at > now()
    returning tenant_id, user_id`;
  if (!claim) return fail("invalid or expired state — possible CSRF");

  const tokens = await exchangeCodeForTokens(code);

  await sql`
    insert into integration_connection (
      tenant_id, provider, external_account_id,
      access_token_enc, refresh_token_enc, access_token_expires_at,
      scopes, status)
    values (
      ${claim.tenant_id}, 'quickbooks', ${realmId},
      ${encrypt(tokens.access_token)}, ${encrypt(tokens.refresh_token)},
      now() + make_interval(secs => ${tokens.expires_in - 120}),
      ${["com.intuit.quickbooks.accounting"]}, 'active')
    on conflict (tenant_id, provider, external_account_id) do update
    set access_token_enc  = excluded.access_token_enc,
        refresh_token_enc = excluded.refresh_token_enc,
        access_token_expires_at = excluded.access_token_expires_at,
        status = 'active', status_reason = null`;
}

The details that matter. randomBytes(32) gives 256 bits of cryptographically secure randomness — never use Math.random() for anything security-related, as it is predictable by design. base64url makes the result safe to drop into a URL without escaping. The oauth_state row binds three things: the random value, the tenant, and the user. On callback we learn the tenant from our own database, never from anything the browser sent — that is the whole point, since the untrusted redirect must not get to choose which tenant gets connected.

Notice the callback consumes the state with delete … returning rather than select. That is a single-statement, single-use claim: even if two callbacks race, exactly one gets rows back. A select then delete would leave a window where both succeed. The expires_at > now() condition kills any state left dangling.

The error parameter handles the user clicking "Deny" — a completely normal thing to do that crashes an alarming number of shipped integrations; answer with a polite "no problem, here's the Connect button again," not a stack trace. And the final upsert is keyed by (tenant, provider, realm), so reconnecting the same company updates in place and flips status back to active, which is what makes the "Reconnect" button in your error inbox actually resolve the problem.

Storing tokens: encrypted, per tenant, never in a log

An access token is a bearer credential, and "bearer" is literal: whoever bears it, uses it. No second factor, no signature, no binding to your IP. A leaked token is a leaked company's accounting system.

So: encrypt at rest, using envelope encryption. You hold one master key in your secrets manager (never in the database, never in the repo). For each token you generate a fresh random data key, encrypt the token with it using an authenticated cipher like AES-256-GCM, then encrypt the data key with the master key and store both. "Authenticated" matters: GCM produces an authentication tag alongside the ciphertext, so if one byte of stored ciphertext is altered, decryption fails loudly instead of returning garbage.

Practical rules that prevent the common leaks:

• Never log a token. Not at debug level, not "temporarily." Write a redacting serializer and use it everywhere, so that console.log(conn) prints access_token_enc: '[redacted]' by construction rather than by discipline.
• Never send a token to the browser. All provider calls happen server-side. If a token ever reaches client JavaScript, assume it is public.
• Never put a token in a URL. Headers only. URLs land in access logs on every hop.
• Scope database access. The columns holding encrypted tokens should be readable only by the service role that needs them — not by the role your API uses for ordinary queries.
• Plan for key rotation. Store a key_version alongside each ciphertext so you can introduce a new master key and re-encrypt lazily rather than in one terrifying migration.

Refresh-token rotation, and the "already used" failure

Intuit adds a twist that catches nearly everyone: refresh-token rotation. When you redeem a refresh token, the response contains a new refresh token, and you must store the latest one. Intuit's own SDK spells out the rule — "always use the refresh token returned in the most recent token_endpoint response," because "your previous refresh tokens expire 24 hours after you receive a new one." Access tokens are short-lived by comparison: Intuit documents them as valid for 3600 seconds, one hour. A refresh token that goes unused for long enough also expires, and then the tenant has to reconnect by hand.

The classic failure follows directly. Two workers hit the refresh path at the same moment. Both read the same refresh token R1. Both call the token endpoint. Worker A gets back R2 and writes it. Worker B — whose call was slower — gets an error, or gets R3 and writes it over R2.

Either way the database now holds a refresh token that the provider does not consider current. The next refresh fails with invalid_grant, and the tenant's accounting sync is dead until a human reconnects.

Intuit's 24-hour grace on the superseded token usually delays that failure by a day, so the error surfaces long after the race that caused it. The message is terse, the cause is a day old, and that gap is why this bug eats entire afternoons.

The fix is a single-flight rule: at most one refresh in progress per connection, enforced by a database lock.

export async function withQboAccessToken<T>(
  connectionId: string,
  fn: (accessToken: string, realmId: string) => Promise<T>
): Promise<T> {
  const conn = await sql.begin(async (tx) => {
    // Lock this connection row. A second worker arriving here WAITS.
    const [c] = await tx`
      select * from integration_connection
      where id = ${connectionId} for update`;

    if (c.status === 'needs_reauth') {
      throw new PermanentError("connection requires the tenant to reconnect");
    }

    // Still fresh (with 60s of slack)? Use it as-is.
    if (c.access_token_expires_at > new Date(Date.now() + 60_000)) return c;

    const res = await fetch(
      "https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer", {
      method: "POST",
      headers: {
        Authorization: "Basic " + Buffer.from(
          `${process.env.QBO_CLIENT_ID}:${process.env.QBO_CLIENT_SECRET}`
        ).toString("base64"),
        "Content-Type": "application/x-www-form-urlencoded",
      },
      body: new URLSearchParams({
        grant_type: "refresh_token",
        refresh_token: decrypt(c.refresh_token_enc),
      }),
    });

    if (!res.ok) {
      const err = await res.json().catch(() => ({}));
      if (err.error === "invalid_grant") {
        // The voucher is permanently dead. Stop retrying; ask the human.
        await tx`
          update integration_connection
          set status = 'needs_reauth',
              status_reason = 'refresh token rejected (invalid_grant)'
          where id = ${connectionId}`;
        await raiseIssue({
          tenantId: c.tenant_id, provider: "quickbooks",
          kind: "auth_revoked", severity: "critical",
          fingerprint: `auth:quickbooks:${connectionId}`,
          summary: "QuickBooks disconnected — an admin must reconnect the company",
        });
        throw new PermanentError("QBO refresh token invalid; reconnect required");
      }
      throw new Error(`QBO token refresh failed: ${res.status}`); // transient
    }

    const t = await res.json();

    // CRITICAL: persist the NEW refresh token in the same transaction.
    const [updated] = await tx`
      update integration_connection
      set access_token_enc  = ${encrypt(t.access_token)},
          refresh_token_enc = ${encrypt(t.refresh_token)},
          access_token_expires_at =
            now() + make_interval(secs => ${t.expires_in - 120})
      where id = ${connectionId}
      returning *`;
    return updated;
  });
  return fn(decrypt(conn.access_token_enc), conn.external_account_id);
}

Step through it; the function is small but every line does security or correctness work.

sql.begin opens a database transaction — a group of statements that succeed or fail together. Inside it, select … for update locks the connection's row. If two workers call this at once, the second pauses at that line until the first commits, then re-reads, finds an already-fresh token, and skips the refresh entirely. That is single-flight: two callers, one network refresh, no lost rotation. Postgres row locks are exactly the mutual-exclusion primitive we need, and we already have a Postgres.

The needs_reauth check at the top is a circuit breaker: a switch that trips once something is known to be broken, so nothing keeps trying it. Once a connection is known broken, every subsequent call fails instantly instead of burning a round trip to relearn the same bad news — the difference between a broken connection costing you nothing and one generating 40,000 failed API calls a day.

The freshness check keeps 60 seconds of slack so a token cannot expire between checking and using it, and the stored expiry shaves two further minutes off what Intuit told us. Both are cheap insurance against clock skew.

The refresh call uses HTTP Basic authentication for the app's own identity: client ID and secret joined with a colon, base64-encoded. That proves which app is asking. The form body — grant_type=refresh_token plus the voucher — proves which tenant's permission is being renewed. Two different identities in one request, which is normal in OAuth and worth noticing.

When invalid_grant means stop, not retry

The error branch is where most implementations are lazy and most outages begin. invalid_grant is not transient. It means the voucher is dead: the user revoked access, the token was already rotated away, the token sat unused until it expired, or an admin disconnected the app. Retrying is pointless and hides the problem behind noise. So we mark the connection needs_reauth, raise a critical issue with a clear instruction, and throw a PermanentError so the processor dead-letters immediately. Any other non-OK status is genuinely transient and gets a plain Error to retry.

The update statement saves both new tokens before the lock releases. That is the rotation-safe moment the whole function exists for: the new refresh token is written inside the same transaction that holds the lock, so there is no instant where another worker could read a stale one. Finally your work function runs with a guaranteed-fresh access token and the realmId (Intuit's word for "which QuickBooks company").

QuickBooks Online: keeping the books

QuickBooks Online (QBO) is where your tenants' accountants live. Your ERP is the operational truth (orders, shipments); QBO is the financial truth (revenue, receivables, tax). Those two truths must agree at month end, and when they don't, somebody's accountant sends an email with the word "reconcile" in it.

The mapping table: your objects to their entities

The integration's job is mapping between the two worlds. Get this table right on paper before you write any code, because a bad mapping is very expensive to undo once real financial data exists.

Two rows in that table are load-bearing and beginners get both wrong. The order → nothing row: an order is only a promise to buy. Revenue is recognized when the goods ship and an invoice exists. If you push orders into QBO as invoices, every canceled order becomes phantom revenue that the accountant has to hunt down and void. The tax row: sales tax involves jurisdiction rules that change quarterly. QBO already solved it. Your job is to send the address and the line items and let their engine compute; sending your own number guarantees a mismatch.

SyncToken: optimistic locking, worked through

QBO's write model has a rule you must respect. Every entity carries a SyncToken — a version number that increments on every edit, like the little "edition" number inside a library book. When you update an entity, you must send back the SyncToken you read. If someone else edited it in between, the token you hold is stale, and QBO rejects your write.

This is optimistic locking. The contrast is pessimistic locking, which is what select … for update does inside your own Postgres: you take a lock, everyone else waits, nobody can conflict. That works within one database. It cannot work across the internet — QBO is not going to hold a lock open while your code thinks, because a crashed client would freeze an accountant's invoice forever. So instead you proceed optimistically, and the server tells you if you lost the race.

Here is what the race looks like in a real day:

10:04:00  Your ERP reads Invoice 1042.  SyncToken = "3"
10:04:01  The accountant, in the QBO web UI, adds a memo to Invoice 1042.
          QBO stores it.                SyncToken = "4"
10:04:02  Your ERP sends its update with SyncToken "3".
          QBO: "That's the old edition." -> rejected, error code 5010.

Reading the timeline: you fetched the invoice and QBO told you its edition number was 3. One second later the accountant edited the same invoice in their browser, so QBO moved it to edition 4. Your update still carries edition 3, so QBO refuses it and returns a stale-object fault. Intuit's error-code list is the reference for that fault; at the time of writing it is code 5010.

There is no force-override. The only correct response is read again, then try again:

const QBO_BASE = process.env.QBO_ENV === "production"
  ? "https://quickbooks.api.intuit.com"
  : "https://sandbox-quickbooks.api.intuit.com";

export async function updateQboInvoice(
  accessToken: string, realmId: string,
  invoiceId: string, patch: Partial<QboInvoice>
) {
  for (let attempt = 1; attempt <= 3; attempt++) {
    // 1. Read the CURRENT version — never cache SyncTokens.
    const current = await qboGet(accessToken, realmId, `invoice/${invoiceId}`);

    // 2. Write back the full object + the token we just read.
    const res = await fetch(
      `${QBO_BASE}/v3/company/${realmId}/invoice?minorversion=75`, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "Content-Type": "application/json",
        Accept: "application/json",
      },
      body: JSON.stringify({
        ...current.Invoice,          // everything QBO currently has
        ...patch,                    // our changes layered on top
        Id: invoiceId,
        SyncToken: current.Invoice.SyncToken,
      }),
    });
    if (res.ok) return (await res.json()).Invoice;

    const err = await res.json();
    const stale = err?.Fault?.Error?.some((e: any) => e.code === "5010");
    if (!stale) throw classifyQboError(err);

    // Stale token: someone edited the invoice since our read.
    // Loop: re-read (getting the new SyncToken AND their changes), re-apply, resend.
    await sleep(150 * attempt);
  }
  throw new Error(`Invoice ${invoiceId} kept changing; giving up after 3 tries`);
}

Line by line. The base URL switches between sandbox and production from one environment variable, not a string scattered through twenty call sites. Step 1 reads the entity immediately before writing — not optional, and not a performance problem worth optimizing away. Never store a SyncToken in your database: the moment you persist one it starts going stale, and a token cached for an hour produces a 5010 on every write.

Step 2 is an HTTP POST of the full object, because QBO updates replace the whole entity. The spread order matters enormously: ...current.Invoice lays down everything QBO currently believes, then ...patch lays our changes over the top. Reverse those two lines and your patch is overwritten by the stale server copy — a bug that produces "my update did nothing" with no error at all. We then pin Id and SyncToken explicitly so nothing in patch can clobber them.

The retry loop catches error code 5010. When we loop, the fresh read in step 1 pulls in the other editor's changes too, and our patch is re-applied on top, so the accountant's memo survives and our change lands. That is why the read must be inside the loop, not above it.

A small increasing sleep stops the two systems from bouncing off each other forever. Three attempts is plenty; beyond that a human should look rather than a robot fighting a robot.

Any error that isn't 5010 goes to classifyQboError, which sorts faults into transient (throttling, 5xx) and permanent (validation failures, unknown references, duplicate document numbers) so permanent ones dead-letter straight into the error inbox instead of retrying into the void.

Minor versions, sandbox, and other ways to be surprised

The minorversion=75 query parameter pins the response schema. Intuit ships incremental schema changes as numbered minor versions; requesting one says "give me this exact shape." Intuit retires old minor versions over time, so check their minor-versions page for the current number before you pin one, and record in a comment the date you chose it. Pin it even when the number you want happens to be the default. Explicit pinning documents intent, makes a future upgrade a one-line greppable diff, and tells a reader which schema your parser was written against. Unpinned API calls are a bet that nothing will ever change.

Sandbox versus production. Intuit gives you a free sandbox: a fake company with fake data, at a different base URL, with different credentials. Build there; every developer should have one and CI should run against one. But know the trap — sandbox is almost production, and the differences bite where it hurts. Feature flags differ (automated sales tax may be off), regional settings differ, enabled account types differ, and data volumes are toy-sized. Build in sandbox, then rehearse end to end against a real trial company before a tenant's real books are involved. "It worked in sandbox" has preceded a great many accounting incidents.

Other limits. QBO caps how many requests one company can send per minute and how many you may have in flight at once. Cross either line and you get HTTP 429 with a ThrottleExceeded fault, which your backoff skeleton already handles. Read the current numbers off Intuit's own limits page rather than hardcoding anything you read in a book; they change.

The Batch endpoint bundles many operations into a single request and carries its own separate throttle, and it is how a 300-invoice month-end push finishes quickly instead of crawling. QBO queries use a SQL-ish dialect with its own quirks too: a capped page size, no join, a limited set of where operators, fussy string escaping. Treat it as "SQL-shaped," and page explicitly.

Finally, all writes should flow through your outbox from chapter 3, so a QBO outage never blocks invoice creation in your own system. The invoice exists in your ERP the moment the shipment is confirmed; the outbox delivers it when Intuit is available. Your users never wait on someone else's uptime.

The classic duplicate-invoice mismatch, and how it happens

Here is the failure that will eventually find you, told as it actually unfolds.

It is the fourth business day of the month. The brand's bookkeeper is closing June. Your ERP's shipped-revenue report says $486,200. QuickBooks says $511,750. The difference is $25,550 and nobody can explain it. The bookkeeper does not trust the ERP anymore, which is a much bigger problem than $25,550.

The cause traces back to one afternoon three weeks earlier. Intuit had a slow period. Your outbox worker sent an invoice create, waited 30 seconds, and timed out. The worker did the natural thing: it retried. But the first request had succeeded at Intuit's end — the response just never made it back through the network. So the retry created a second, identical invoice. Two invoices, one shipment, one order. It happened to 17 orders that afternoon.

Three defenses against the duplicate

Three defenses, all of which you should implement:

1. A deterministic external key. Set DocNumber on the invoice to a value derived from your own data — INV-{order_number}. QBO can be configured to enforce unique document numbers, in which case the duplicate create is rejected outright with a "duplicate document number" fault. That fault is now a success signal in disguise: it proves the first attempt landed. Catch it, look up the existing invoice, store its ID, and move on.
2. Read-before-write on retry. Before creating anything, query QBO for an existing entity with your deterministic key. If it exists, adopt it instead of creating a twin.
3. Record the mapping in the same transaction as the outbox entry. Chapter 3's idempotency key belongs here: one outbox row per (order, action), and once you have written back the QBO ID, that row is complete and can never fire again.

export async function pushInvoice(conn: IntegrationConnection, orderId: string) {
  const order = await loadOrderForInvoicing(conn.tenant_id, orderId);
  const docNumber = `INV-${order.number}`;            // deterministic, our data

  return withQboAccessToken(conn.id, async (token, realmId) => {
    // 1. Already mapped locally? Then we are certainly done.
    const [existing] = await sql`
      select qbo_invoice_id from invoice
      where tenant_id = ${conn.tenant_id} and order_id = ${orderId}
        and qbo_invoice_id is not null`;
    if (existing) return existing.qbo_invoice_id;

    // 2. Not mapped locally — but did a timed-out attempt already create it?
    const found = await qboQuery(token, realmId,
      `select Id, SyncToken from Invoice where DocNumber = '${docNumber}'`);
    if (found.length > 0) {
      await recordMapping(conn.tenant_id, orderId, found[0].Id);
      return found[0].Id;                              // adopt, don't duplicate
    }

    // 3. Genuinely new. Create it.
    try {
      const created = await qboPost(token, realmId, "invoice", {
        DocNumber: docNumber,
        CustomerRef: { value: order.qboCustomerId },
        TxnDate: order.shippedOn,
        SalesTermRef: { value: order.qboTermsId },
        Line: order.lines.map((l) => ({
          DetailType: "SalesItemLineDetail",
          Amount: l.qty * l.unitPrice,
          SalesItemLineDetail: {
            ItemRef: { value: l.qboItemId },
            Qty: l.qty,
            UnitPrice: l.unitPrice,
          },
        })),
      });
      await recordMapping(conn.tenant_id, orderId, created.Id);
      return created.Id;
    } catch (e) {
      // 4. "Duplicate document number" means attempt #1 actually succeeded.
      if (isDuplicateDocNumber(e)) {
        const again = await qboQuery(token, realmId,
          `select Id from Invoice where DocNumber = '${docNumber}'`);
        if (again.length > 0) {
          await recordMapping(conn.tenant_id, orderId, again[0].Id);
          return again[0].Id;
        }
      }
      throw e;
    }
  });
}

Step 1 is the cheap local check: if we already recorded a QBO invoice ID for this order, there is nothing to do, and we never touch the network. Step 2 is the one that saves you from the timeout story: we ask QBO directly whether an invoice with our deterministic DocNumber already exists. If it does, a previous attempt succeeded and we simply adopt it — record the mapping and return.

Step 3 builds the actual invoice; note that every reference (CustomerRef, ItemRef, SalesTermRef) is a QBO ID we stored earlier during mapping, and that Amount per line must equal quantity times price or QBO rejects the whole document.

Step 4 is the belt-and-braces branch: even with steps 1 and 2, two workers could race between the check and the create. If QBO comes back complaining about a duplicate document number, treat that complaint as proof the invoice already exists. We look it up, record the mapping, and return it.

The operation has become genuinely idempotent: run it fifty times, get one invoice.

Polling: when the other side never calls

Plenty of systems your tenants will demand — 3PL warehouses (third-party logistics: companies that store and ship goods for the brand), niche B2B marketplaces, legacy order portals — have APIs but no webhooks at all. They will never call you; you must call them on a schedule and ask "what changed since I last looked?" That is polling, and doing it correctly needs three ideas:

• A watermark is your saved bookmark: the timestamp of the newest change you have fully processed. Each poll asks for records updated after the watermark, then advances it.
• A cursor handles big answers: when there are 5,000 changed records, the API hands you a page plus an opaque "resume here" string; you loop until there are no more pages.
• And an overlap window defends against sloppy clocks: you deliberately re-ask for a few minutes before your watermark.

const OVERLAP_MS = 5 * 60 * 1000; // re-scan the last 5 minutes every time

export async function pollWarehouseShipments(conn: IntegrationConnection) {
  const since = new Date(conn.watermark.getTime() - OVERLAP_MS);
  let cursor: string | undefined;
  let newest = conn.watermark;

  do {
    const page = await warehouseApi.listShipments({
      updatedSince: since.toISOString(),
      cursor,
      limit: 100,
    });
    for (const s of page.shipments) {
      await sql`
        insert into inbound_event
          (tenant_id, connection_id, provider, external_id, topic,
           raw_body, payload, occurred_at)
        values (${conn.tenant_id}, ${conn.id}, 'warehouse',
                ${`${s.id}:${s.updatedAt}`},   -- id+version = natural dedupe key
                'shipment.updated',
                ${JSON.stringify(s)}, ${JSON.stringify(s)}::jsonb,
                ${s.updatedAt})
        on conflict (provider, external_id) do nothing`;
      const u = new Date(s.updatedAt);
      if (u > newest) newest = u;
    }
    cursor = page.nextCursor;
  } while (cursor);

  // Advance the bookmark only after every page landed safely.
  await sql`update integration_connection
            set watermark = ${newest}, last_success_at = now()
            where id = ${conn.id}`;
}

Walkthrough: since is the watermark minus the overlap — the "rewind five minutes" move. The do…while loop fetches page after page, following nextCursor until the API says there's nothing more; a do…while rather than a while because we always want at least one request. Each shipment goes into the same inbound_event queue our webhooks use — polling and webhooks converge on one processing path, one retry policy, one error inbox. That convergence is the real payoff of the universal skeleton: adding a fourth or fifth provider costs you a poller and a handler, not a new subsystem.

The dedupe key is id + updatedAt, and that composite is doing something clever. The same version of a shipment inserts only once, so overlap re-fetches are absorbed. But a genuinely newer update — same ID, later timestamp — produces a different key and therefore a new event, which is exactly right: it is new information and must be processed. Compare that to using the bare id as the key, which would silently drop every update after the first.

Only after the whole sweep succeeds do we advance the watermark. This ordering is deliberate. If the job crashes on page 30 of 40, the watermark is unchanged, so the next run simply re-covers the same ground, and the unique constraint absorbs every repeat. The alternative, advancing the watermark per page, is faster and loses data the first time a page fails after the advance. Idempotency is what turns "sloppy but safe" into a design principle: because repeats cost nothing, you are free to choose the crash-safe ordering every time.

EDI: the retail world's oldest data language

Now the deep end. When a brand lands Nordstrom or Dillard's, the retailer will not call your API and does not care that you have GraphQL. They require EDI — Electronic Data Interchange — a way of exchanging business documents that predates the web by more than a decade.

The mental model: two companies agreeing to exchange strictly formatted paper forms, except the forms are text files and the fax machine is a network. That framing explains almost every strange thing about EDI. Forms have boxes in a fixed order. Forms have a cover sheet and a mailing envelope. Forms come back stamped "received." Nobody redesigns the form because you find it inconvenient.

The dominant format in North American retail is ANSI X12: a standard defining hundreds of numbered document types called transaction sets, each a rigid sequence of segments (lines, each starting with a short code like PO1) made of elements (fields separated by a delimiter, conventionally *). Documents travel inside nested envelopes and every envelope carries control numbers — sequential IDs that let both sides detect a missing or duplicate document.

What an X12 document physically looks like

Here is a simplified purchase order (PO), an 850, shaped exactly as one would arrive as a text file. Every name, address, and number in it is invented. One display note: the opening ISA line is a single unbroken segment of 106 characters, too wide for this page, so it is shown wrapped across two lines. The indentation at the start of the second line is not part of the file.

ISA*00*          *00*          *ZZ*NORDSTROM      *ZZ*
     ACMEAPPAREL    *260715*1200*U*00401*000000101*0*P*>~
GS*PO*NORDSTROM*ACMEAPPAREL*20260715*1200*101*X*004010~
ST*850*0001~
BEG*00*SA*4520019283**20260715~
REF*DP*0599~
DTM*002*20260801~
DTM*064*20260725~
N1*BY*NORDSTROM INC*92*NORD01~
N1*ST*NORDSTROM DC 599*92*0599~
N3*4300 PORT OF TACOMA RD~
N4*TACOMA*WA*98424*US~
PO1*1*24*EA*14.50**SK*TEE-BLK-M*UP*889012345678~
PID*F****CLASSIC TEE BLACK MEDIUM~
PO1*2*12*EA*14.50**SK*TEE-BLK-L*UP*889012345685~
PID*F****CLASSIC TEE BLACK LARGE~
CTT*2*36~
SE*15*0001~
GE*1*101~
IEA*1*000000101~

Dense and unfriendly on first sight. Decode it segment by segment, because once you can read one of these you can read all of them.

Four structural ideas behind X12

Four structural ideas are worth extracting from that table, because they are the ideas EDI is actually built on.

1. Envelopes nest, three deep. ISA wraps GS wraps ST. Mailbag, folder, form. Each level has a matching trailer (IEA, GE, SE) carrying a count and the same control number as its header. A file whose SE count disagrees with the actual number of segments is malformed and will be rejected.

2. The delimiters are declared in the file itself. The ISA segment is fixed-width — exactly 105 characters plus its segment terminator, 106 in all — precisely so a parser can find the delimiters by position before it knows anything else. Count the characters in the sample above and you will land on 106. The character right after ISA is the element separator. The last character is the segment terminator. Trading partners really do use different ones, so never hardcode * and ~; read them off the ISA.

3. Meaning comes from position and qualifier codes, not names. There are no field names anywhere. DTM*002 and DTM*064 are the same segment type meaning two completely different dates, distinguished only by a three-digit code. This is why you need a specification document per partner, and why an EDI platform's machine-readable guides are worth paying for.

4. Control numbers are the anti-duplicate mechanism. They are sequential per sender. If you receive interchange 101 and then 103, you know 102 is missing and must ask for it. If you receive 101 twice, the second is a duplicate. This is the same job our unique (provider, external_id) constraint does. The committee that standardized it, ASC X12, was chartered in 1979, and the idea still works.

The retail conversation, as a sequence

Retail EDI is a choreographed conversation. Each document has a number, a direction, and a meaning:

Now the actual sequence for one order, with the clock running. Read it as a phone call where every sentence must be answered:

Day 0  09:00  850  Nordstrom --> You
                   Purchase order 4520019283.
Day 0  09:02  997  You --> Nordstrom
                   "850 received, syntax OK." Due within hours.
Day 0  14:30  855  You --> Nordstrom
                   Line 1: accepted, all 24 (IA).
                   Line 2: accepted at 6 of 12 (IQ).
                   Due within 24 hours.
Day 0  14:31  997  Nordstrom --> You
                   "855 received, syntax OK."
Day 5  16:00  856  You --> Nordstrom
                   Advance ship notice: 1 pallet, 3 cartons,
                   SSCC labels, contents of each carton.
                   Due BEFORE the truck departs.
Day 5  16:02  997  Nordstrom --> You
                   "856 received, syntax OK."
Day 7  08:00  --   The truck reaches DC 599 and cartons are scanned.
Day 7  10:00  810  You --> Nordstrom
                   Invoice for what actually shipped.
Day 7  10:03  997  Nordstrom --> You
                   "810 received, syntax OK."
Day 67 --     820  Nordstrom --> You
                   Payment on Net 60 terms, less any deductions.

Read each pair of lines together: the first says when the document moved, which number it is, and which way it went; the second says what it meant. The 997s are the small receipts that keep the conversation honest.

What each document actually commits you to

Who sends what, and what each acknowledgment actually means:

The 850 is a demand. It arrives whether you are ready or not, and you cannot negotiate with it before it lands. Your ERP must turn it into a wholesale order, validate the SKUs against your catalog, check ATS, and decide what you can actually commit to.

The 997 acknowledges syntax only. This is the single most misunderstood document in EDI. The X12 standard is explicit that the functional acknowledgment reports the results of syntactical analysis and does not cover the semantic meaning of the information encoded. So a 997 means: "the envelopes matched, the segments were in a legal order, the element types were right."

It does not mean the retailer agrees, will pay, or even read it. A retailer can 997-accept your 856 and still reject the shipment at the dock. Conversely, a missing or rejected 997 is a five-alarm signal: your document did not land, and everything downstream of it is now fiction.

Inside a 997 the detail lives in a few segments:

• AK1 opens the response for one functional group;
• AK2 begins the response for one transaction set inside it;
• AK3 reports an error in a specific segment;
• AK4 reports an error in a specific data element;
• AK5 gives the verdict for that transaction set (accepted, accepted with errors, or rejected);
• and AK9 gives the verdict for the whole group plus counts of how many transaction sets were included, received, and accepted.

When a 997 comes back rejected, AK3 and AK4 are where you find out which segment and which element you got wrong. Log them verbatim into sync_issue.detail; you will need them when you call the partner's EDI coordinator.

The 855 is your business answer. Each line carries an acknowledgment code: IA (item accepted), IR (item rejected), IQ (item accepted at a different quantity), IB (accepted but backordered), IC (accepted with changes). This is where you tell the truth about what you can ship. Under-promising here is vastly cheaper than over-promising and short-shipping later — the 855 is your one free chance to say no.

The 810 must match the 856, which must match the truck. Bill for what you shipped, not what was ordered. A discrepancy between the 810 and what the distribution center actually received triggers a deduction — money the retailer simply subtracts from your payment. Recovering one takes letters, phone calls, and weeks; no code change gets it back.

The 856 and why it must match the physical cartons exactly

The Advance Ship Notice is the document that separates EDI-competent brands from the ones who get fined. Understand what it is for: it lets the retailer's distribution center receive your shipment without opening a single carton.

Here is the physical process. Your truck arrives. A worker scans a barcode label on each carton — an SSCC-18 (Serial Shipping Container Code, an 18-digit globally unique carton ID) printed on a GS1-128 label, GS1-128 being the barcode format retail supply chains standardized on. The scanner looks up that number in the ASN you sent days earlier and instantly knows: this carton belongs to PO 4520019283, department 0599, and contains 12 units of TEE-BLK-M and 6 of TEE-BLK-L.

The carton is routed, received, and the retailer's inventory updates — in about four seconds, without a human reading a packing slip. That speed is the entire business case for EDI, and it depends completely on the ASN being exactly true.

The 856 expresses this with an HL loop — a hierarchy of nested levels, each HL segment declaring its own ID, its parent's ID, and its level type:

ST*856*0001~                          One 856 starts here
BSN*00*ASN00042*20260720*1600*0001~   Shipment ID ASN00042
HL*1**S~                              Level 1: SHIPMENT (no parent)
TD1*CTN25*3~                          3 cartons in this shipment
TD5**2*UPSN*M*UPS GROUND~             Carrier and service level
REF*BM*887766554~                     Bill of lading number
HL*2*1*O~                             Level 2: ORDER, parent = 1
PRF*4520019283~                       ...against PO 4520019283
HL*3*2*P~                             Level 3: PACK (carton), parent = 2
MAN*GM*008890123000000012~            SSCC-18 from the carton label
HL*4*3*I~                             Level 4: ITEM, parent = 3
LIN**SK*TEE-BLK-M*UP*889012345678~    Which SKU
SN1**12*EA~                           12 units IN THIS CARTON
CTT*4~                                4 HL segments above
SE*15*0001~                           15 segments, ST through SE

Only the first of the three cartons is shown, so this excerpt holds four HL nodes. A complete file would repeat the HL…P block, with its own MAN, LIN, and SN1 segments, for cartons two and three, and the CTT and SE counts would grow to match.

Reading the hierarchy: HL*1**S means "hierarchy node 1, no parent, type Shipment." HL*2*1*O means "node 2, parent is node 1, type Order." HL*3*2*P is a Pack (carton) belonging to that order, and HL*4*3*I is an Item inside that carton. Each carton gets its own HL…P node with its own MAN segment carrying the SSCC-18, and each SKU inside that carton gets an HL…I node with a LIN (which SKU) and an SN1 (how many, in this carton specifically).

BSN is the beginning segment carrying the shipment's own identifier and timestamp. TD1 and TD5 carry carton count and carrier routing. CTT counts the HL segments as a checksum, and SE counts every segment from ST to SE inclusive — fifteen here.

One partner-specific detail to check: the SSCC is eighteen digits, but some retailers want it in the MAN segment with the two-digit "00" prefix that appears in the barcode, making twenty characters. Their routing guide will say which.

Notice what this structure demands: your ERP must know which physical units went into which physical box. "We shipped 24 mediums" is not enough detail. The ASN needs "carton …123 holds 12 mediums; carton …124 holds 12 mediums and 6 larges." If your warehouse process cannot produce carton-level content data, you cannot produce a valid ASN, and no amount of clever code will invent it. This is the point where an EDI project stops being a software problem and becomes a warehouse-process problem, and it is worth telling your customer that on day one.

Stedi and Orderful: outsourcing the grammar, keeping the responsibility

You should not write an X12 parser. That sentence deserves emphasis because writing one is a fun weekend and a five-year maintenance burden. The grammar is only half the problem; the other half is that every retailer implements the standard slightly differently, and those differences are documented in PDFs that change without notice.

Modern EDI platforms — Stedi and Orderful are the developer-centric ones, sit between you and the retailer. They hold the connections to trading partners, including the old value-added networks, private mail systems that have carried EDI files between companies since the 1980s, and the SFTP servers (secure file drop-boxes) that many partners still use — validate documents against each retailer's specific requirements (Stedi encodes these as machine-readable "guides," because Nordstrom's 850 and Macy's 850 differ in maddening small ways), translate X12 into JSON, and deliver it to you — by webhook.

Suddenly this is familiar territory. An EDI 850 arrives at your server as an authenticated JSON webhook, flows into inbound_event, gets processed idempotently into a wholesale order, with retries and an error inbox. Outbound, you POST JSON (an 855, an 856) to their API and the platform emits compliant X12. Orderful even auto-sends the routine 997s for documents you receive. EDI stops being an exotic subsystem and becomes just another async, must-not-silently-fail integration on the same skeleton you have already built.

What the platform cannot do for you is care whether the conversation completed. That is domain logic, and it lives in your database.

Acknowledgment tracking as a state machine

Every document you send is a promise that must be answered. Model that explicitly as a state machine: a small set of named states, and rules for which transitions are allowed. Writing it down as states rather than scattered booleans is what makes "is this shipment acknowledged?" a query instead of an investigation.

Note the two failure states are genuinely different and need different human responses. rejected means "they got it and it was wrong" — you have their error detail, you can fix the data, resend, and the loop closes. overdue means "we heard nothing" — the far scarier case, because you do not know whether the document is sitting in a queue, was silently dropped, or is fine and their 997 is late. That ambiguity is why overdue must escalate to a phone call, not a retry.

create table edi_document (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references tenant(id),
  partner        text not null,          -- 'nordstrom'
  direction      text not null,          -- 'inbound' | 'outbound'
  doc_type       text not null,          -- '850' | '855' | '856' | '810' | '997'
  control_number text not null,          -- from the ST/GS envelope
  business_ref   text,                   -- our order id / shipment id / invoice id
  related_doc_id uuid references edi_document(id),  -- the 850 this 855 answers
  status         text not null default 'queued',
                 -- queued -> sent -> ack_pending -> accepted | rejected | overdue
                 -- queued -> failed_send
  ack_due_at     timestamptz,            -- when a 997 becomes late
  acked_at       timestamptz,
  ack_detail     jsonb,                  -- AK3/AK4/AK5 errors, verbatim
  payload        jsonb not null,
  created_at     timestamptz not null default now(),
  unique (tenant_id, partner, direction, doc_type, control_number)
);

create index edi_document_ack_watch_idx
  on edi_document (ack_due_at) where status = 'ack_pending';

Column notes:

• control_number plus the unique constraint is the same duplicate shield as everywhere else in this chapter, now in EDI clothing — the same file re-delivered by a VAN cannot be ingested twice.
• related_doc_id makes the conversation a linked chain, so one query can render "PO 4520019283: 850 in ✓, 855 out ✓ accepted, 856 out ⚠ overdue, 810 not yet sent" as a timeline in the UI.
• ack_detail stores the 997's error segments verbatim, because when you call the partner's coordinator the first thing they ask is "what did the 997 say?"
• And the partial index makes the hourly overdue sweep a single fast lookup no matter how many million documents have accumulated.

// Runs hourly. An outbound document with no 997 after its deadline is an incident.
export async function flagOverdueAcks() {
  const overdue = await sql`
    update edi_document
    set status = 'overdue'
    where direction = 'outbound'
      and status = 'ack_pending'
      and ack_due_at < now()
    returning *`;

  for (const doc of overdue) {
    await raiseIssue({
      tenantId: doc.tenant_id,
      provider: "edi",
      kind: "ack_overdue",
      // An unacknowledged ASN is money; an unacknowledged invoice is slower money.
      severity: doc.doc_type === "856" ? "critical" : "error",
      fingerprint: `ack_overdue:${doc.partner}:${doc.doc_type}:${doc.control_number}`,
      summary:
        `No 997 from ${doc.partner} for our ${doc.doc_type} ` +
        `#${doc.control_number} (order ${doc.business_ref}) — ` +
        `due ${doc.ack_due_at.toISOString()}`,
      detail: { ediDocumentId: doc.id, businessRef: doc.business_ref },
    });
  }
  return overdue.length;
}

How it works. The update … returning * does two jobs in one statement: it flips every overdue row to overdue and hands those rows back to our code, so we can file one error-inbox entry each. Doing it as one statement rather than select-then-update means two concurrent runs of this job cannot both claim the same row and file duplicate issues.

The severity branch encodes real business judgment: an unacknowledged ASN means a truck is about to arrive at a dock that does not know it is coming, which is a chargeback in progress, critical. An unacknowledged invoice means you will be paid late, bad, not urgent. The fingerprint includes the control number so each specific document gets exactly one open issue.

Closing the loop with the inbound 997

The counterpart is the inbound 997 handler, which closes the loop:

export async function handle997(event: InboundEvent) {
  const ack = event.payload;   // platform-normalized JSON of the 997

  for (const ts of ack.transactionSets) {
    // ts.acknowledgedControlNumber is the ST02 of the document being answered.
    const accepted = ts.acknowledgmentCode === "A";  // A = accepted
    const partial  = ts.acknowledgmentCode === "E";  // E = accepted with errors

    const [doc] = await sql`
      update edi_document
      set status     = ${accepted || partial ? "accepted" : "rejected"},
          acked_at   = now(),
          ack_detail = ${JSON.stringify(ts)}::jsonb
      where tenant_id = ${event.tenant_id}
        and partner = ${ack.partner}
        and direction = 'outbound'
        and control_number = ${ts.acknowledgedControlNumber}
        and status in ('ack_pending', 'overdue')
      returning *`;

    if (!doc) {
      // A 997 for a document we have no record of. Never ignore this.
      await raiseIssue({
        tenantId: event.tenant_id, provider: "edi",
        kind: "orphan_ack", severity: "warning",
        fingerprint: `orphan_ack:${ack.partner}:${ts.acknowledgedControlNumber}`,
        summary: `997 from ${ack.partner} references unknown control number ` +
                 `${ts.acknowledgedControlNumber}`,
      });
      continue;
    }

    if (!accepted) {
      await raiseIssue({
        tenantId: event.tenant_id, provider: "edi",
        kind: "ack_rejected", severity: "critical",
        fingerprint: `ack_rejected:${doc.partner}:${doc.control_number}`,
        summary: `${doc.partner} REJECTED our ${doc.doc_type} ` +
                 `#${doc.control_number} (order ${doc.business_ref})`,
        detail: ts,   // AK3/AK4 segment and element errors, verbatim
      });
    } else {
      // Late-but-arrived 997s should clear the overdue alarm we raised earlier.
      await resolveIssue(event.tenant_id,
        `ack_overdue:${doc.partner}:${doc.doc_type}:${doc.control_number}`);
    }
  }
}

One 997 can acknowledge many transaction sets at once, so we loop. The acknowledgment code carries the verdict — A for accepted, E for accepted with errors (the partner has your document and will process it, but tell your team something is sloppy), and anything else meaning rejected. We update the matching outbound document by its control number, and the status in ('ack_pending','overdue') filter makes the handler idempotent: a duplicate 997 finds no row in a waiting state and does nothing.

The if (!doc) branch is the one people skip, and it is diagnostic gold. A 997 referencing a control number you have never sent means one of three things, all of which you want to know: your control-number sequence is out of step with theirs, a document was sent by something other than your ERP, or you are receiving another company's traffic because of a partner-configuration error. Raise it as a warning; do not swallow it.

Finally, note the resolveIssue call on the happy path. A 997 that arrives late, after your hourly sweep already cried wolf, should silently close the alarm it triggered. Every alerting mechanism in this chapter has a matching de-alerting mechanism. An error inbox that only fills up is an error inbox nobody opens, and an error inbox nobody opens is worse than none at all, because it creates the illusion of monitoring.

Ship this, and the question "did Nordstrom actually accept our ASN?" has an answer on a screen — instead of in a chargeback letter six weeks later.

Testing integrations you don't control

Everything so far assumed you can write the code. The harder question is how you convince yourself it works, when the systems it talks to belong to other companies, cost money to call, rate-limit you, and are down on Sunday nights for maintenance you weren't told about.

You cannot test an integration by calling the real thing from your test suite. That approach fails in every direction at once:

• tests become slow (network round trips),
• flaky (their uptime is now your test suite's uptime),
• non-deterministic (yesterday's data differs from today's),
• destructive (you just created 400 test invoices in a real QuickBooks company),
• and unrunnable offline.

A test that fails for reasons unrelated to your code trains your team to ignore failing tests, which is the worst possible outcome.

The answer is four layers, each catching a different class of bug. Build all four; they are not alternatives.

Layer 1: recorded fixtures

A fixture is a saved copy of a real response, stored as a file in your repository. You call the real API once, by hand, capture exactly what came back, scrub the secrets out, and commit it. From then on your tests replay the recording instead of making a network call.

This style of testing is often called VCR, after the original Ruby library — the metaphor being a video cassette recorder: record once, play back forever. In the JavaScript world the equivalent tools are nock and @pollyjs, but you do not need a library to start. A directory of JSON files and a fake fetch will take you a long way.

// test/fixtures.ts
import { readFileSync, readdirSync } from "node:fs";
import { join } from "node:path";

const DIR = join(__dirname, "fixtures");

export function fixture(name: string) {
  return JSON.parse(readFileSync(join(DIR, `${name}.json`), "utf8"));
}

/** Replace global fetch with a recording player for the duration of a test. */
export function playFixtures(routes: Record<string, string>) {
  const real = globalThis.fetch;
  globalThis.fetch = (async (input: any, init: any) => {
    const url = typeof input === "string" ? input : input.url;
    const key = Object.keys(routes).find((pattern) => url.includes(pattern));
    if (!key) throw new Error(`No fixture registered for ${url}`);
    const rec = fixture(routes[key]);
    return new Response(JSON.stringify(rec.body), {
      status: rec.status,
      headers: rec.headers,
    });
  }) as any;
  return () => { globalThis.fetch = real; };   // call this in afterEach
}

Line by line: fixture loads one recording by name from a fixtures directory. playFixtures takes a map from "URL substring" to "fixture name," swaps out the global fetch for a function that looks up the matching recording, and returns a restore function you call in your test's cleanup. The throw on an unmatched URL is deliberate and important: a test that quietly makes a real network call because you forgot a fixture is a test that will pass in CI and fail at 2 a.m. Failing loudly on unregistered URLs makes the network boundary explicit.

Two disciplines make fixtures trustworthy rather than misleading. Scrub secrets on capture, not later — access tokens, shop domains, customer emails, and real order numbers should be replaced with obvious fakes before the file is committed, and your pre-commit hook should reject anything matching a token pattern.

Re-record on a schedule. A fixture recorded in 2025 tests your code against a 2025 API forever, which means your suite goes green while production breaks. Keep a single script that re-records every fixture against sandbox, run it monthly, and read the diff — that diff is the vendor's changelog, expressed in the only terms that matter to you.

Layer 2: sandbox accounts

Every serious provider offers a free practice environment. Intuit gives you sandbox companies at a separate base URL. Shopify gives you development stores that behave like real stores but cannot take real payments. Stedi and Orderful give you test trading partners that will exchange documents with you and even send 997s back.

Sandboxes catch the class of bug fixtures cannot: bugs in your sequence of calls, in your auth flow, in your assumptions about what a write actually does. A fixture proves you can parse an invoice response. Only a sandbox proves that creating an invoice, then a payment, then linking them, leaves the invoice marked paid.

Run sandbox tests in a separate suite from your unit tests — nightly, not on every commit, because they are slow and their failures are sometimes the vendor's fault. And treat a sandbox failure as a real signal even when it's intermittent: intermittent is what production looks like from the inside.

Know the trap, though, and repeat it to yourself before every launch: sandbox is almost production. Feature flags differ. Regional settings differ. Rate limits are often more generous. Data volumes are toy-sized, so the query that returns 40 rows in sandbox returns 40,000 in production and times out. Sandbox proves your code is correct; it does not prove your code is fast enough or that the merchant's configuration matches your assumptions.

Layer 3: contract tests

A contract test asserts the shape of the data you depend on. It checks structure and ignores the particular values. "The order response has a totalPriceSet.shopMoney.amount that parses as a decimal." "Every line item has a sku field, which may be null." "The 997 payload has a transactionSets array whose members carry an acknowledgmentCode."

This matters separately from fixtures because fixtures test your code against a frozen world, and the whole risk of integration work is that the world moves. A contract test run against the live sandbox answers a different question: does the vendor still return what my parser expects? When Shopify renames a field or starts returning null where it never did, the contract test fails in your nightly run — days before a customer notices, and with a message that names the field.

import { z } from "zod";

// The contract: exactly the fields our importer depends on, and nothing else.
export const ShopifyVariantContract = z.object({
  id: z.string().startsWith("gid://shopify/ProductVariant/"),
  sku: z.string().nullable(),                 // Shopify allows blank SKUs
  inventoryQuantity: z.number().int(),
  selectedOptions: z.array(z.object({
    name: z.string(),
    value: z.string(),
  })).min(1),
});

test("live sandbox still matches our variant contract", async () => {
  const data = await shopifyQuery(DEV_STORE, DEV_TOKEN, VARIANT_PROBE_QUERY);
  const nodes = data.productVariants.edges.map((e: any) => e.node);
  expect(nodes.length).toBeGreaterThan(0);    // an empty store proves nothing
  for (const node of nodes) {
    ShopifyVariantContract.parse(node);       // throws with the exact bad path
  }
});

zod is a schema library — you declare the shape you expect and it validates a value against it, throwing an error that names the exact failing path (like selectedOptions.0.value: expected string, received null). The contract declares only the fields our importer actually reads, which is the point: a contract over fields you don't use generates false alarms every time the vendor adds something.

nullable() on sku encodes a real fact about Shopify, variants can have no SKU, and forces our importer to handle it rather than crashing in production. The expect(nodes.length).toBeGreaterThan(0) guard catches the silliest failure mode of all: a contract test that passes because it validated zero records.

Layer 4: simulating failure on purpose

The first three layers test the happy path in various disguises. The bugs that cost you money live in the unhappy paths, and those never occur in a sandbox because sandboxes are healthy. So you must manufacture failure deliberately.

Here are the six failures worth writing explicit tests for, what each one is really testing, and the bug it catches:

The out-of-order test is the one teams skip and the one that most rewards writing, because the bug it catches is invisible in review. Here it is written out:

test("an older order update never overwrites a newer one", async () => {
  const tenant = await makeTenant();
  const shop   = await connectShop(tenant, "acme.myshopify.com");

  const newer = orderPayload({ id: 1042, updatedAt: "2026-07-15T10:06:00Z",
                               status: "cancelled", total: 0 });
  const older = orderPayload({ id: 1042, updatedAt: "2026-07-15T10:05:00Z",
                               status: "open",      total: 12000 });

  // Deliberately deliver them backwards, as the network really can.
  await postSignedWebhook("orders/updated", newer, { webhookId: "wh-2" });
  await postSignedWebhook("orders/updated", older, { webhookId: "wh-1" });
  await drainQueue();

  const order = await getOrder(tenant, 1042);
  expect(order.status).toBe("cancelled");        // newer state survived
  expect(order.total_cents).toBe(0);
  expect(order.source_updated_at.toISOString()).toBe("2026-07-15T10:06:00.000Z");

  // Both events were accepted and processed; neither errored.
  const events = await listEvents(tenant);
  expect(events).toHaveLength(2);
  expect(events.every((e) => e.status === "done")).toBe(true);
});

We build two versions of the same order — one canceled at 10:06, one open at 10:05, and deliver the newer one first, which is exactly what a retried webhook does in production. Note the distinct webhookId values, because these really are two different deliveries and must both be stored; if you gave them the same ID, the test would pass for the wrong reason (dedupe, not ordering).

After draining the queue we assert the surviving state is the newer one. Then the second half of the test, which is the part people leave out: both events must be done, not dead.

Correctly ignoring stale data counts as a success. A handler that throws on out-of-order data would pass the first three assertions and still be wrong, because it would fill your error inbox with noise on every busy store.

One more testing habit worth building: a chaos toggle in your development environment. An environment variable like CHAOS_FAILURE_RATE=0.1 that makes your integration client randomly fail one call in ten with a realistic error. Run your dev environment that way for a week. Every silent-failure bug in your code surfaces within days, in an environment where it costs nothing, instead of in production where it costs six days and a customer.

The error inbox: the feature you sell

We have referred to the error inbox in every section. Now build it properly, because it is the difference between an ERP that customers trust and one they check by hand.

Start with an instinct you have to unlearn. Every engineer's reflex is that errors are shameful, evidence the software is bad, and should be hidden from users, logged quietly, and fixed before anyone notices. That reflex is wrong for integrations, and understanding why changes how you build.

Most integration errors are not your bugs. A SKU exists in Shopify that was never set up in the ERP. A retailer sent a PO for a discontinued style. QuickBooks rejected an invoice because the customer record was deleted by the bookkeeper. An address is missing a postal code. You cannot fix any of those from a code editor. Only the customer can fix them, and they can only fix what they can see.

So treat the error inbox as a product surface in its own right. It is where your software says: here is exactly what I could not do, exactly why, and exactly what you can do about it. Customers who have used ERPs before will recognize this screen immediately, because the alternative, discovering the problem three weeks later in a report, is the thing they hate most about the system they are replacing. Sell it in the demo. Put it in the navigation, not in a settings sub-page.

The schema, and the fields that make it usable

-- The full error inbox, extending the sketch from earlier in the chapter.
create table sync_issue (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references tenant(id),
  provider         text not null,          -- 'shopify' | 'quickbooks' | 'edi' | ...
  connection_id    uuid references integration_connection(id),
  kind             text not null,          -- the machine-readable class
  severity         text not null default 'error',
  summary          text not null,          -- ONE sentence, no jargon, names the thing
  detail           jsonb,                  -- raw evidence for support/engineering
  suggested_action text,                   -- what the human should do
  action_url       text,                   -- deep link to where they do it
  fingerprint      text not null,          -- groups repeats
  occurrences      int not null default 1,
  first_seen_at    timestamptz not null default now(),
  last_seen_at     timestamptz not null default now(),
  inbound_event_id uuid references inbound_event(id),
  entity_ref       text,                   -- 'order #1042', 'SKU TEE-NVY-XS'
  resolved_at      timestamptz,
  resolved_by      uuid references app_user(id),
  resolution       text,                   -- 'fixed' | 'retried' | 'dismissed' | 'auto'
  created_at       timestamptz not null default now()
);

create unique index sync_issue_open_fingerprint_idx
  on sync_issue (tenant_id, fingerprint) where resolved_at is null;

create index sync_issue_inbox_idx
  on sync_issue (tenant_id, severity, last_seen_at desc) where resolved_at is null;

The columns that separate a useful inbox from a useless one:

summary must be one sentence, written for a warehouse manager, naming the specific thing. "Shopify order #1042 references SKU TEE-NVY-XS, which does not exist in your catalog" is useful. "Error processing inbound event: NotFoundError" is not — it names no thing, suggests no action, and forces the reader to file a support ticket. Write these strings as carefully as you write marketing copy, because they are the product at the moment a customer needs it most.

suggested_action and action_url turn a notification into a workflow. "Create SKU TEE-NVY-XS or map it to an existing style" plus a link straight to the SKU-creation form with the code pre-filled. The measure of a good error inbox is how many issues a non-technical user can close without contacting you.

entity_ref is the human-readable name of the thing involved, so the list can be scanned and searched by people who think in order numbers and SKUs rather than UUIDs.

fingerprint with the partial unique index is what keeps the inbox readable, as discussed earlier: one open row per distinct problem, with occurrences counting repeats. The first_seen_at/last_seen_at pair then answers the question that determines urgency — is this a new problem, or an old one getting worse?

resolution records how it ended, which is the data you need to improve the system. If 60% of your issues are resolved as dismissed, you are generating noise and should raise your thresholds. If a particular kind is always resolved by clicking retry, automate the retry and stop bothering the human.

Severity levels, and what each one means operationally

Every severity level must map to a defined response. Skip that step and the levels degenerate into everyone marking everything urgent.

The discipline that makes this work: a severity must be earned by a defined response. Before you mark something critical, write down what the customer is supposed to do in the next ten minutes. If you can't, it isn't critical. Alert fatigue comes from a bad severity mapping. The people who stop reading the alerts are behaving reasonably.

Error classes and the fix each one needs

Every issue your system can raise should belong to a named class with a known human remedy. Here is the working set for an apparel ERP:

Notice how many rows say "then click Retry." That button is the most useful control in the whole feature, and it is trivial to build because of decisions you already made: the raw payload is stored, so retry means setting the inbound_event back to pending with attempts = 0. The handler is idempotent, so a retry that turns out to be unnecessary is harmless. The entire user-facing "fix it" workflow is three lines of SQL, purchased by the architecture in the first section of this chapter.

/** Raise or bump an issue. Safe to call on every occurrence. */
export async function raiseIssue(i: {
  tenantId: string; provider: string; kind: string; severity: Severity;
  fingerprint: string; summary: string; detail?: unknown;
  suggestedAction?: string; actionUrl?: string;
  entityRef?: string; inboundEventId?: string;
}) {
  await sql`
    insert into sync_issue (
      tenant_id, provider, kind, severity, summary, detail,
      suggested_action, action_url, fingerprint, entity_ref, inbound_event_id)
    values (
      ${i.tenantId}, ${i.provider}, ${i.kind}, ${i.severity}, ${i.summary},
      ${JSON.stringify(i.detail ?? null)}::jsonb,
      ${i.suggestedAction ?? null}, ${i.actionUrl ?? null},
      ${i.fingerprint}, ${i.entityRef ?? null}, ${i.inboundEventId ?? null})
    on conflict (tenant_id, fingerprint) where resolved_at is null
    do update set
      occurrences  = sync_issue.occurrences + 1,
      last_seen_at = now(),
      summary      = excluded.summary,
      detail       = excluded.detail,
      -- Severity only ever ratchets UP while an issue stays open.
      severity     = case
                       when severity_rank(excluded.severity)
                          > severity_rank(sync_issue.severity)
                       then excluded.severity else sync_issue.severity
                     end`;
}

/** Close an issue when the underlying condition clears. */
export async function resolveIssue(
  tenantId: string, fingerprint: string, resolution = "auto"
) {
  await sql`
    update sync_issue
    set resolved_at = now(), resolution = ${resolution}
    where tenant_id = ${tenantId} and fingerprint = ${fingerprint}
      and resolved_at is null`;
}

Reading raiseIssue: it is an upsert against the partial unique index on open issues. First occurrence inserts a row. Every later occurrence of the same fingerprint bumps the counter, refreshes last_seen_at, and updates the summary and detail to the most recent facts, because a stale summary describing the first of 4,000 failures is misleading.

severity_rank is a tiny SQL function you write yourself; it turns info, warning, error, and critical into 1, 2, 3, and 4 so they can be compared. The severity case expression around it is a small piece of hard-won wisdom: severity ratchets upward only. If a problem was once critical, a later benign occurrence should not quietly downgrade it while it is still unresolved, or a serious issue can flicker off a dashboard nobody was watching at the right moment.

resolveIssue is the mirror, and every alert path in your system needs one. The rule to internalize: anything that can raise must know how to clear. The reconciliation job clears drift when the numbers agree. The 997 handler clears the overdue alarm when a late acknowledgment arrives. The connection monitor clears staleness on the next successful sweep. Without this, the inbox only ever grows, users learn it is not worth reading, and you have built an expensive way to be ignored.

Field notes & further reading

Everything above reflects the APIs as they stand in mid-2026; integration docs move, so keep these bookmarked:

• Shopify API rate limits — the calculated-cost model (scalars and enums free, objects 1, mutations 10), the per-plan restore rates of 100 / 200 / 1,000 / 2,000 points per second, and the rule that "a single query may not exceed a cost of 1,000 points, regardless of plan limits."
• Shopify Bulk Operations queries — bulkOperationRunQuery, JSONL results, __parentId, the guarantee that "all nested connections appear after their parents in the file," the two-level / five-connection nesting limits, the one-week signed URL expiry, and the five-concurrent-operations change in API 2026-01.
• Shopify webhook best practices — HMAC verification, deduplication by X-Shopify-Webhook-Id, and Shopify's own instruction not to rely on webhook delivery but to run reconciliation jobs.
• Shopify HTTPS webhook delivery — the exact contract: one-second connection timeout, five-second total timeout, 200 required, 8 retries over 4 hours, and automatic subscription deletion after 8 consecutive failures.
• Intuit: Set up OAuth 2.0 — the authorization-code flow, token lifetimes, and refresh-token rotation for QuickBooks Online.
• Intuit's own OAuth client for Node — the clearest statement of the token rules: access tokens "valid for 3600 seconds (one hour)," and "always use the refresh token returned in the most recent token_endpoint response," since "your previous refresh tokens expire 24 hours after you receive a new one."
• Node.js crypto documentation — createHmac and timingSafeEqual, including the requirement that both buffers be the same byte length and the reasoning behind constant-time comparison.
• Stedi X12 850 reference — free, readable, segment-by-segment documentation of the purchase order: BEG, REF, DTM, the N1 loop, PO1, PID, CTT, SE.
• Stedi X12 856 reference — the Advance Ship Notice: the HL hierarchy (Shipment / Order / Pack / Item), BSN, TD1/TD5, MAN for carton SSCC-18 marks, LIN/SN1, and CTT.
• Stedi X12 997 reference — the Functional Acknowledgment: AK1 through AK9, and the standard's explicit statement that it reports "the results of the syntactical analysis of the electronically encoded documents" and "does not cover the semantic meaning of the information encoded in the transaction sets."
• Stedi X12 element 668 (Line Item Status Code) — the acknowledgment codes you put on each 855 line: IA Item Accepted, IB Item Backordered, IC Item Accepted - Changes Made, IQ Item Accepted - Quantity Changed, IR Item Rejected.
• Stripe webhooks — the clearest published example of a signed timestamp: the signature covers the timestamp, a dot, and the body, and Stripe's libraries "have a default tolerance of 5 minutes."
• Orderful: acknowledging transactions — how 997s and JSON-equivalent acknowledgments work on a modern EDI platform.