allanninal.dev / bigcommerce

Field notes / Migration and housekeeping

Repair Migration and housekeeping

Backfill order metadata for matching

Before the ERP or marketplace integration was wired up, orders came in with no external_id, no external_merchant_id, and no external_source. Now a reconciliation job needs to join those old BigCommerce orders to ERP records and has nothing solid to join on. You cannot just PUT the missing fields back in, BigCommerce will not accept it. Here is why, and a small script that writes a safe reconciliation key somewhere BigCommerce will actually let you keep it.

Python and Node.js V2 orders REST API Safe by default (dry run)
The short answer

external_id and external_merchant_id are only reliably set when an order is created. BigCommerce treats external_merchant_id as write-once, so a later PUT /v2/orders/{id} trying to add or change it returns a 400, and support threads show external_id behaving the same way once the order already exists. Do not fight that. Instead, match each legacy order to its ERP record with your own logic, then write an idempotent tag like [RECON:ext_id=ERP-00219482;source=M-MIG;matched=2026-07-10] into staff_notes, a control panel only field that stays mutable for the life of the order. Skip anything already tagged and skip status_id 0, 5, and 6. Full code, tests, and a dry run guard are below.

The problem in plain words

An external_id or external_merchant_id is supposed to be the thread that ties a BigCommerce order back to whatever system actually owns the order: an ERP, a marketplace, a new order-management layer. That thread only gets tied when the order is created, because those fields are populated by whichever client submitted the order at POST time.

If the integration did not exist yet when an order was placed, nothing was there to set the field, so it was simply left null. That order shipped, got paid, maybe got refunded, and sits in BigCommerce today with no durable key back to the ERP. Once the integration finally goes live and someone tries to reconcile the backlog, they discover the fields cannot be patched in after the fact. The join has to fall back to customer email, order total, and date, which drifts and collides at any real scale.

Order created before integration existed external_id and external_merchant_id left null Later PUT tries to add the field ✕ 400 No durable key Reconciliation must fall back to email + total + date, unreliable at scale
The order's external key was never set, and BigCommerce will not let you set it after the fact, so reconciliation has nothing solid to join on.

Why it happens

This is not a bug, it is how BigCommerce scopes those fields. A few things make it show up in real stores:

The result is a stack of orders that need reconciling and no clean way to join them. See the citations at the end for the exact support threads and docs.

The key insight

Do not fight the write-once field. external_id and external_merchant_id are closed doors on an existing order, and trying to force them wastes time and produces 400s. staff_notes is the field BigCommerce actually leaves open, control panel only, always mutable. So the fix is not "restore the original field," it is "store the reconciliation key somewhere durable and idempotent," and let a human or a high-confidence match decide when that key gets written.

The fix, as a flow

We scan the pre-cutover date range, pull each order's full payload, and check whether it already has an external key. If it does not, we compare it against the ERP export to compute a candidate match and a confidence score. High confidence writes a real reconciliation tag. Low confidence writes an UNMATCHED tag instead of guessing. Either way, the write only ever touches staff_notes, and it is a dry run first.

Scan pre-cutover GET /v2/orders Skip status 0, 5, 6 and already tagged Compute candidate match vs ERP export Confidence >= 0.8? yes Write [RECON: ext_id=...] tag no [RECON:UNMATCHED]
Only staff_notes is written, and only when a candidate match clears the confidence bar. Everything else is skipped or flagged, guarded by DRY_RUN.

Build it step by step

1

Get a store hash and an access token

Create an API account in your BigCommerce control panel under Settings, API, Store-level API accounts, or reuse an existing one. It needs the Orders scope with modify access, since we still need to PUT staff_notes. Keep the store hash and token in environment variables, never in the file.

setup (shell)
pip install requests

export BIGCOMMERCE_STORE_HASH="abc123"
export BIGCOMMERCE_ACCESS_TOKEN="..."
export MIGRATION_CUTOFF="2025-01-01T00:00:00+00:00"
export CUTOVER_DATE="2025-06-01T00:00:00+00:00"
export MATCH_CONFIDENCE_THRESHOLD="0.8"
export DRY_RUN="true"   # start safe, change to false to write
setup (shell)
// Node 18+ has fetch built in, no dependencies needed

export BIGCOMMERCE_STORE_HASH="abc123"
export BIGCOMMERCE_ACCESS_TOKEN="..."
export MIGRATION_CUTOFF="2025-01-01T00:00:00+00:00"
export CUTOVER_DATE="2025-06-01T00:00:00+00:00"
export MATCH_CONFIDENCE_THRESHOLD="0.8"
export DRY_RUN="true"   // start safe, change to false to write
2

Talk to the V2 orders REST API

Every call sends your token in the X-Auth-Token header against https://api.bigcommerce.com/stores/{store_hash}/. A small helper wraps GET and PUT so the rest of the script does not repeat headers and error handling.

step2.py
import os, requests

STORE_HASH = os.environ["BIGCOMMERCE_STORE_HASH"]
TOKEN = os.environ["BIGCOMMERCE_ACCESS_TOKEN"]
BASE = f"https://api.bigcommerce.com/stores/{STORE_HASH}/"
HEADERS = {"X-Auth-Token": TOKEN, "Accept": "application/json", "Content-Type": "application/json"}

def bc_get(path, params=None):
    r = requests.get(BASE + path, headers=HEADERS, params=params or {}, timeout=30)
    r.raise_for_status()
    return r.json()

def bc_put(path, body):
    r = requests.put(BASE + path, headers=HEADERS, json=body, timeout=30)
    r.raise_for_status()
    return r.json()
step2.js
const STORE_HASH = process.env.BIGCOMMERCE_STORE_HASH;
const TOKEN = process.env.BIGCOMMERCE_ACCESS_TOKEN;
const BASE = `https://api.bigcommerce.com/stores/${STORE_HASH}/`;
const HEADERS = { "X-Auth-Token": TOKEN, "Accept": "application/json", "Content-Type": "application/json" };

async function bcGet(path, params = {}) {
  const qs = new URLSearchParams(params).toString();
  const res = await fetch(BASE + path + (qs ? `?${qs}` : ""), { headers: HEADERS });
  if (!res.ok) throw new Error(`BigCommerce ${res.status}`);
  return res.json();
}

async function bcPut(path, body) {
  const res = await fetch(BASE + path, { method: "PUT", headers: HEADERS, body: JSON.stringify(body) });
  if (!res.ok) throw new Error(`BigCommerce ${res.status}`);
  return res.json();
}
3

Scan the pre-migration window and flag unmatched orders

List orders between the migration cutoff and the cutover date, sorted by id, then GET each one's full payload. An order needs backfill when external_id, external_merchant_id, and external_source are all missing or the source is not one of your expected integration tags, such as the M-MIG marker.

step3.py
EXPECTED_SOURCE_TAGS = {"M-MIG"}

def needs_backfill(order):
    if order.get("external_id"):
        return False
    if order.get("external_merchant_id"):
        return False
    source = order.get("external_source")
    if source and source in EXPECTED_SOURCE_TAGS:
        return False
    return True

def scan_candidate_orders(min_date_created, max_date_created):
    orders = bc_get("v2/orders", {
        "min_date_created": min_date_created,
        "max_date_created": max_date_created,
        "is_deleted": "false",
        "sort": "id",
        "limit": 250,
    })
    out = []
    for stub in orders:
        full = bc_get(f"v2/orders/{stub['id']}")
        if needs_backfill(full):
            out.append(full)
    return out
step3.js
const EXPECTED_SOURCE_TAGS = new Set(["M-MIG"]);

function needsBackfill(order) {
  if (order.external_id) return false;
  if (order.external_merchant_id) return false;
  const source = order.external_source;
  if (source && EXPECTED_SOURCE_TAGS.has(source)) return false;
  return true;
}

async function scanCandidateOrders(minDateCreated, maxDateCreated) {
  const orders = await bcGet("v2/orders", {
    min_date_created: minDateCreated,
    max_date_created: maxDateCreated,
    is_deleted: "false",
    sort: "id",
    limit: 250,
  });
  const out = [];
  for (const stub of orders) {
    const full = await bcGet(`v2/orders/${stub.id}`);
    if (needsBackfill(full)) out.push(full);
  }
  return out;
}
4

Decide, with one pure function

The decision belongs in its own function that takes an order, a candidate match (or none), and the current time, and returns an action. No network, no side effects, which makes it trivial to test. It skips incomplete or voided orders, skips orders that are already tagged or already have an external key, writes a real reconciliation tag when the match is confident, and otherwise writes an UNMATCHED tag rather than guessing.

decide.py
VOID_STATUS_IDS = {0, 5, 6}  # Incomplete, Cancelled, Declined
CONFIDENCE_THRESHOLD = 0.8

def decide_backfill_action(order, candidate_match, now_iso):
    if order["status_id"] in VOID_STATUS_IDS:
        return {"action": "skip", "reason": "incomplete_or_voided"}

    existing_notes = order.get("staff_notes") or ""
    if "[RECON:" in existing_notes:
        return {"action": "skip", "reason": "already_tagged"}

    if order.get("external_id") or order.get("external_merchant_id"):
        return {"action": "skip", "reason": "already_has_external_key"}

    if candidate_match is None or candidate_match.get("confidence", 0) < CONFIDENCE_THRESHOLD:
        return {
            "action": "flag_unmatched",
            "new_staff_notes": existing_notes + f"\n[RECON:UNMATCHED;checked={now_iso}]",
        }

    return {
        "action": "write_staff_notes",
        "new_staff_notes": existing_notes
        + f"\n[RECON:ext_id={candidate_match['external_id']};"
        + f"source={candidate_match.get('source', 'M-MIG')};matched={now_iso}]",
    }
decide.js
const VOID_STATUS_IDS = new Set([0, 5, 6]); // Incomplete, Cancelled, Declined
const CONFIDENCE_THRESHOLD = 0.8;

export function decideBackfillAction(order, candidateMatch, nowIso) {
  if (VOID_STATUS_IDS.has(order.status_id)) {
    return { action: "skip", reason: "incomplete_or_voided" };
  }

  const existingNotes = order.staff_notes || "";
  if (existingNotes.includes("[RECON:")) {
    return { action: "skip", reason: "already_tagged" };
  }

  if (order.external_id || order.external_merchant_id) {
    return { action: "skip", reason: "already_has_external_key" };
  }

  if (!candidateMatch || (candidateMatch.confidence || 0) < CONFIDENCE_THRESHOLD) {
    return {
      action: "flag_unmatched",
      new_staff_notes: `${existingNotes}\n[RECON:UNMATCHED;checked=${nowIso}]`,
    };
  }

  return {
    action: "write_staff_notes",
    new_staff_notes:
      `${existingNotes}\n[RECON:ext_id=${candidateMatch.external_id};` +
      `source=${candidateMatch.source || "M-MIG"};matched=${nowIso}]`,
  };
}
5

Write only staff_notes, guarded by a re-GET

Right before writing, re-GET the order to confirm staff_notes still does not contain a [RECON: block. That closes the race where two runs process the same backlog. Then PUT only the staff_notes field, never external_id or external_merchant_id, since those are the fields BigCommerce will reject or silently ignore.

apply.py
def apply_staff_notes(order_id, new_staff_notes):
    fresh = bc_get(f"v2/orders/{order_id}")
    if "[RECON:" in (fresh.get("staff_notes") or ""):
        return  # another run already tagged it, idempotent no-op
    return bc_put(f"v2/orders/{order_id}", {"staff_notes": new_staff_notes})
apply.js
async function applyStaffNotes(orderId, newStaffNotes) {
  const fresh = await bcGet(`v2/orders/${orderId}`);
  if ((fresh.staff_notes || "").includes("[RECON:")) {
    return; // another run already tagged it, idempotent no-op
  }
  return bcPut(`v2/orders/${orderId}`, { staff_notes: newStaffNotes });
}
6

Wire it together with a dry run guard

The loop matches each candidate order against your ERP export, runs decide_backfill_action, and logs what it would do. On the first few runs, leave DRY_RUN on and read the log. Only flip it off once you agree with the matches, and export anything unmatched to a CSV for a human to look at instead of guessing.

Run it safe

Never attempt to write external_id or external_merchant_id on an existing order, BigCommerce will reject or ignore it. Always start with DRY_RUN=true, and only let a match write a real reconciliation tag when its confidence clears the threshold. Anything lower gets an UNMATCHED tag or a row in the manual review CSV, not a guess.

The full code

Here is the complete script in one file for each language. It reads settings from the environment, scans the pre-cutover window, applies the pure decision function, and only ever writes staff_notes, respecting the dry run flag and the idempotency check every time.

View this code on GitHub Full runnable folder with tests in the bigcommerce-fixes repo.

backfill_order_metadata.py
"""Backfill a reconciliation key onto legacy BigCommerce orders, safely.

Orders placed before an ERP, marketplace, or order-management integration was
wired up were created without external_id, external_merchant_id, or
external_source, because those fields are only populated by whichever client
submits the order at creation time. BigCommerce treats external_merchant_id as
write-once (a PUT to change it returns a 400) and external_id behaves the same
way once the order already exists, so those fields cannot be safely rewritten
after the fact. This scans the pre-cutover window, matches each unmatched order
against an external export, and writes an idempotent reconciliation tag into
staff_notes instead, which stays mutable for the life of the order.
Run on a schedule or once per migration batch. Safe to run again and again.

Guide: https://www.allanninal.dev/bigcommerce/backfill-order-metadata-for-matching/
"""
import os
import logging
from datetime import datetime, timezone
import requests

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("backfill_order_metadata")

STORE_HASH = os.environ["BIGCOMMERCE_STORE_HASH"]
TOKEN = os.environ["BIGCOMMERCE_ACCESS_TOKEN"]
BASE = f"https://api.bigcommerce.com/stores/{STORE_HASH}/"
HEADERS = {"X-Auth-Token": TOKEN, "Accept": "application/json", "Content-Type": "application/json"}

MIGRATION_CUTOFF = os.environ.get("MIGRATION_CUTOFF", "2025-01-01T00:00:00+00:00")
CUTOVER_DATE = os.environ.get("CUTOVER_DATE", "2025-06-01T00:00:00+00:00")
MATCH_CONFIDENCE_THRESHOLD = float(os.environ.get("MATCH_CONFIDENCE_THRESHOLD", "0.8"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

VOID_STATUS_IDS = {0, 5, 6}  # Incomplete, Cancelled, Declined
EXPECTED_SOURCE_TAGS = {"M-MIG"}


def bc_get(path, params=None):
    r = requests.get(BASE + path, headers=HEADERS, params=params or {}, timeout=30)
    r.raise_for_status()
    return r.json()


def bc_put(path, body):
    r = requests.put(BASE + path, headers=HEADERS, json=body, timeout=30)
    r.raise_for_status()
    return r.json()


def needs_backfill(order):
    if order.get("external_id"):
        return False
    if order.get("external_merchant_id"):
        return False
    source = order.get("external_source")
    if source and source in EXPECTED_SOURCE_TAGS:
        return False
    return True


def decide_backfill_action(order, candidate_match, now_iso):
    """Pure decision logic. No I/O, fully testable with plain dicts."""
    if order["status_id"] in VOID_STATUS_IDS:
        return {"action": "skip", "reason": "incomplete_or_voided"}

    existing_notes = order.get("staff_notes") or ""
    if "[RECON:" in existing_notes:
        return {"action": "skip", "reason": "already_tagged"}

    if order.get("external_id") or order.get("external_merchant_id"):
        return {"action": "skip", "reason": "already_has_external_key"}

    if candidate_match is None or candidate_match.get("confidence", 0) < MATCH_CONFIDENCE_THRESHOLD:
        return {
            "action": "flag_unmatched",
            "new_staff_notes": existing_notes + f"\n[RECON:UNMATCHED;checked={now_iso}]",
        }

    return {
        "action": "write_staff_notes",
        "new_staff_notes": existing_notes
        + f"\n[RECON:ext_id={candidate_match['external_id']};"
        + f"source={candidate_match.get('source', 'M-MIG')};matched={now_iso}]",
    }


def scan_candidate_orders():
    orders = bc_get("v2/orders", {
        "min_date_created": MIGRATION_CUTOFF,
        "max_date_created": CUTOVER_DATE,
        "is_deleted": "false",
        "sort": "id",
        "limit": 250,
    })
    out = []
    for stub in orders:
        full = bc_get(f"v2/orders/{stub['id']}")
        if needs_backfill(full):
            out.append(full)
    return out


def find_candidate_match(order, erp_export):
    """Match by customer_id, billing email, total, and a date tolerance window.
    Replace with your real ERP export lookup. Returns None or a dict with
    external_id, source, and confidence.
    """
    return erp_export.get(order["id"])


def apply_staff_notes(order_id, new_staff_notes):
    fresh = bc_get(f"v2/orders/{order_id}")
    if "[RECON:" in (fresh.get("staff_notes") or ""):
        return  # another run already tagged it, idempotent no-op
    return bc_put(f"v2/orders/{order_id}", {"staff_notes": new_staff_notes})


def run(erp_export=None):
    erp_export = erp_export or {}
    now_iso = datetime.now(timezone.utc).isoformat()

    written = 0
    unmatched = 0
    skipped = 0
    for order in scan_candidate_orders():
        candidate_match = find_candidate_match(order, erp_export)
        decision = decide_backfill_action(order, candidate_match, now_iso)

        if decision["action"] == "skip":
            skipped += 1
            continue

        log.info(
            "Order %s -> %s. %s",
            order["id"], decision["action"], "would write" if DRY_RUN else "writing",
        )
        if not DRY_RUN:
            apply_staff_notes(order["id"], decision["new_staff_notes"])

        if decision["action"] == "write_staff_notes":
            written += 1
        else:
            unmatched += 1

    log.info(
        "Done. %d order(s) %s, %d flagged unmatched, %d skipped.",
        written, "to reconcile" if DRY_RUN else "reconciled", unmatched, skipped,
    )


if __name__ == "__main__":
    run()
backfill-order-metadata.js
/**
 * Backfill a reconciliation key onto legacy BigCommerce orders, safely.
 *
 * Orders placed before an ERP, marketplace, or order-management integration was
 * wired up were created without external_id, external_merchant_id, or
 * external_source, because those fields are only populated by whichever client
 * submits the order at creation time. BigCommerce treats external_merchant_id as
 * write-once (a PUT to change it returns a 400) and external_id behaves the same
 * way once the order already exists, so those fields cannot be safely rewritten
 * after the fact. This scans the pre-cutover window, matches each unmatched order
 * against an external export, and writes an idempotent reconciliation tag into
 * staff_notes instead, which stays mutable for the life of the order.
 * Run on a schedule or once per migration batch. Safe to run again and again.
 *
 * Guide: https://www.allanninal.dev/bigcommerce/backfill-order-metadata-for-matching/
 */
import { pathToFileURL } from "node:url";

const STORE_HASH = process.env.BIGCOMMERCE_STORE_HASH || "example-store-hash";
const TOKEN = process.env.BIGCOMMERCE_ACCESS_TOKEN || "dummy-token";
const BASE = `https://api.bigcommerce.com/stores/${STORE_HASH}/`;
const HEADERS = { "X-Auth-Token": TOKEN, "Accept": "application/json", "Content-Type": "application/json" };

const MIGRATION_CUTOFF = process.env.MIGRATION_CUTOFF || "2025-01-01T00:00:00+00:00";
const CUTOVER_DATE = process.env.CUTOVER_DATE || "2025-06-01T00:00:00+00:00";
const MATCH_CONFIDENCE_THRESHOLD = Number(process.env.MATCH_CONFIDENCE_THRESHOLD || 0.8);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

const VOID_STATUS_IDS = new Set([0, 5, 6]); // Incomplete, Cancelled, Declined
const EXPECTED_SOURCE_TAGS = new Set(["M-MIG"]);

export function needsBackfill(order) {
  if (order.external_id) return false;
  if (order.external_merchant_id) return false;
  const source = order.external_source;
  if (source && EXPECTED_SOURCE_TAGS.has(source)) return false;
  return true;
}

export function decideBackfillAction(order, candidateMatch, nowIso) {
  if (VOID_STATUS_IDS.has(order.status_id)) {
    return { action: "skip", reason: "incomplete_or_voided" };
  }

  const existingNotes = order.staff_notes || "";
  if (existingNotes.includes("[RECON:")) {
    return { action: "skip", reason: "already_tagged" };
  }

  if (order.external_id || order.external_merchant_id) {
    return { action: "skip", reason: "already_has_external_key" };
  }

  if (!candidateMatch || (candidateMatch.confidence || 0) < MATCH_CONFIDENCE_THRESHOLD) {
    return {
      action: "flag_unmatched",
      new_staff_notes: `${existingNotes}\n[RECON:UNMATCHED;checked=${nowIso}]`,
    };
  }

  return {
    action: "write_staff_notes",
    new_staff_notes:
      `${existingNotes}\n[RECON:ext_id=${candidateMatch.external_id};` +
      `source=${candidateMatch.source || "M-MIG"};matched=${nowIso}]`,
  };
}

async function bcGet(path, params = {}) {
  const qs = new URLSearchParams(params).toString();
  const res = await fetch(BASE + path + (qs ? `?${qs}` : ""), { headers: HEADERS });
  if (!res.ok) throw new Error(`BigCommerce ${res.status}`);
  return res.json();
}

async function bcPut(path, body) {
  const res = await fetch(BASE + path, { method: "PUT", headers: HEADERS, body: JSON.stringify(body) });
  if (!res.ok) throw new Error(`BigCommerce ${res.status}`);
  return res.json();
}

async function scanCandidateOrders() {
  const orders = await bcGet("v2/orders", {
    min_date_created: MIGRATION_CUTOFF,
    max_date_created: CUTOVER_DATE,
    is_deleted: "false",
    sort: "id",
    limit: 250,
  });
  const out = [];
  for (const stub of orders) {
    const full = await bcGet(`v2/orders/${stub.id}`);
    if (needsBackfill(full)) out.push(full);
  }
  return out;
}

function findCandidateMatch(order, erpExport) {
  return erpExport[order.id] || null;
}

async function applyStaffNotes(orderId, newStaffNotes) {
  const fresh = await bcGet(`v2/orders/${orderId}`);
  if ((fresh.staff_notes || "").includes("[RECON:")) {
    return; // another run already tagged it, idempotent no-op
  }
  return bcPut(`v2/orders/${orderId}`, { staff_notes: newStaffNotes });
}

export async function run(erpExport = {}) {
  const nowIso = new Date().toISOString();

  let written = 0;
  let unmatched = 0;
  let skipped = 0;
  for (const order of await scanCandidateOrders()) {
    const candidateMatch = findCandidateMatch(order, erpExport);
    const decision = decideBackfillAction(order, candidateMatch, nowIso);

    if (decision.action === "skip") {
      skipped++;
      continue;
    }

    console.log(`Order ${order.id} -> ${decision.action}. ${DRY_RUN ? "would write" : "writing"}`);
    if (!DRY_RUN) await applyStaffNotes(order.id, decision.new_staff_notes);

    if (decision.action === "write_staff_notes") written++;
    else unmatched++;
  }

  console.log(`Done. ${written} order(s) ${DRY_RUN ? "to reconcile" : "reconciled"}, ${unmatched} flagged unmatched, ${skipped} skipped.`);
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((err) => { console.error(err); process.exit(1); });
}

Add a test

The decision rule is the part most worth testing, because it decides whether an order gets a real reconciliation key, an UNMATCHED flag, or is left alone. Because decide_backfill_action is pure, the test needs no network and no BigCommerce store. It just feeds in plain dicts and checks the answer.

test_backfill.py
from backfill_order_metadata import decide_backfill_action

NOW = "2026-07-10T00:00:00+00:00"


def order(**over):
    base = {"status_id": 10, "staff_notes": "", "external_id": "", "external_merchant_id": ""}
    base.update(over)
    return base


def test_skips_incomplete_order():
    result = decide_backfill_action(order(status_id=0), None, NOW)
    assert result == {"action": "skip", "reason": "incomplete_or_voided"}


def test_skips_cancelled_order():
    result = decide_backfill_action(order(status_id=5), {"external_id": "X", "confidence": 0.9}, NOW)
    assert result["action"] == "skip"
    assert result["reason"] == "incomplete_or_voided"


def test_skips_already_tagged_order():
    result = decide_backfill_action(order(staff_notes="prior note\n[RECON:UNMATCHED;checked=x]"), None, NOW)
    assert result == {"action": "skip", "reason": "already_tagged"}


def test_skips_order_with_existing_external_key():
    result = decide_backfill_action(order(external_id="ERP-1"), {"external_id": "ERP-1", "confidence": 0.95}, NOW)
    assert result == {"action": "skip", "reason": "already_has_external_key"}


def test_flags_unmatched_when_no_candidate():
    result = decide_backfill_action(order(), None, NOW)
    assert result["action"] == "flag_unmatched"
    assert result["new_staff_notes"] == f"\n[RECON:UNMATCHED;checked={NOW}]"


def test_flags_unmatched_when_low_confidence():
    result = decide_backfill_action(order(), {"external_id": "ERP-1", "confidence": 0.4}, NOW)
    assert result["action"] == "flag_unmatched"


def test_writes_staff_notes_when_confident():
    candidate = {"external_id": "ERP-00219482", "source": "M-MIG", "confidence": 0.92}
    result = decide_backfill_action(order(), candidate, NOW)
    assert result["action"] == "write_staff_notes"
    assert result["new_staff_notes"] == f"\n[RECON:ext_id=ERP-00219482;source=M-MIG;matched={NOW}]"


def test_appends_rather_than_overwrites_existing_notes():
    candidate = {"external_id": "ERP-9", "source": "M-MIG", "confidence": 0.85}
    result = decide_backfill_action(order(staff_notes="called customer 2026-01-02"), candidate, NOW)
    assert result["new_staff_notes"].startswith("called customer 2026-01-02\n[RECON:")
backfill.test.js
import { test } from "node:test";
import assert from "node:assert/strict";
import { decideBackfillAction } from "./backfill-order-metadata.js";

const NOW = "2026-07-10T00:00:00.000Z";

const order = (over = {}) => ({
  status_id: 10, staff_notes: "", external_id: "", external_merchant_id: "", ...over,
});

test("skips incomplete order", () => {
  const result = decideBackfillAction(order({ status_id: 0 }), null, NOW);
  assert.deepEqual(result, { action: "skip", reason: "incomplete_or_voided" });
});

test("skips cancelled order even with a confident match", () => {
  const result = decideBackfillAction(order({ status_id: 5 }), { external_id: "X", confidence: 0.9 }, NOW);
  assert.equal(result.action, "skip");
  assert.equal(result.reason, "incomplete_or_voided");
});

test("skips already tagged order", () => {
  const result = decideBackfillAction(order({ staff_notes: "prior note\n[RECON:UNMATCHED;checked=x]" }), null, NOW);
  assert.deepEqual(result, { action: "skip", reason: "already_tagged" });
});

test("skips order with existing external key", () => {
  const result = decideBackfillAction(order({ external_id: "ERP-1" }), { external_id: "ERP-1", confidence: 0.95 }, NOW);
  assert.deepEqual(result, { action: "skip", reason: "already_has_external_key" });
});

test("flags unmatched when no candidate", () => {
  const result = decideBackfillAction(order(), null, NOW);
  assert.equal(result.action, "flag_unmatched");
  assert.equal(result.new_staff_notes, `\n[RECON:UNMATCHED;checked=${NOW}]`);
});

test("flags unmatched when confidence is low", () => {
  const result = decideBackfillAction(order(), { external_id: "ERP-1", confidence: 0.4 }, NOW);
  assert.equal(result.action, "flag_unmatched");
});

test("writes staff notes when confident", () => {
  const candidate = { external_id: "ERP-00219482", source: "M-MIG", confidence: 0.92 };
  const result = decideBackfillAction(order(), candidate, NOW);
  assert.equal(result.action, "write_staff_notes");
  assert.equal(result.new_staff_notes, `\n[RECON:ext_id=ERP-00219482;source=M-MIG;matched=${NOW}]`);
});

test("appends rather than overwrites existing notes", () => {
  const candidate = { external_id: "ERP-9", source: "M-MIG", confidence: 0.85 };
  const result = decideBackfillAction(order({ staff_notes: "called customer 2026-01-02" }), candidate, NOW);
  assert.ok(result.new_staff_notes.startsWith("called customer 2026-01-02\n[RECON:"));
});

Case studies

ERP go-live

Three years of orders with nothing to join on

A hardware distributor connected an ERP integration in year four of running BigCommerce. Every order from the first three years had no external_id, and finance wanted every one of them reconciled against the ERP's own order ledger for an audit.

The team ran the scan across the whole pre-cutover window, matched by customer_id and total within a two day window, and let the script write staff_notes tags at high confidence. About 6% came back low confidence and landed in a manual review CSV instead of getting a guessed key, which is exactly what the auditors wanted to see.

Marketplace connector

A failed PUT taught them not to fight external_id

A team building a new marketplace connector assumed they could just PUT external_id onto the old orders once the connector was live. The first attempt came back with a 400 on external_merchant_id, and a few external_id writes were silently ignored on others.

Once they stopped trying to rewrite those fields and switched to a staff_notes tag, the backlog cleared in one afternoon, and the re-GET idempotency check meant re-running the job after a partial failure never double tagged anything.

What good looks like

After this runs, every legacy order either carries a durable [RECON: key your reconciliation job can grep for, or an honest UNMATCHED flag that routes to a human instead of a guess. Nothing fought BigCommerce's write-once fields, nothing got tagged twice, and abandoned or voided orders never picked up a key they did not need.

FAQ

Can I set external_id or external_merchant_id on an order that already exists?

Not reliably. BigCommerce treats external_merchant_id as write-once, so a PUT that tries to change it after it is first set returns a 400. Support threads also show external_id being ignored or rejected when a PUT tries to add it after order creation, because both fields are meant to be set by the client that submits the order at POST time.

What should I write to legacy orders instead of external_id?

Write a structured tag into staff_notes, a control panel only field that stays mutable for the life of the order. A tag like [RECON:ext_id=ERP-00219482;source=M-MIG;matched=2026-07-10] gives your reconciliation job a durable, greppable key without touching the fields BigCommerce will not let you rewrite.

How do I avoid tagging an order twice or tagging an abandoned order?

Re-GET the order right before you write and skip it if staff_notes already contains a [RECON: block, which makes the job idempotent. Also skip any order whose status_id is 0 (Incomplete), 5 (Cancelled), or 6 (Declined), since those were never real sales and should not carry a reconciliation key.

Related field notes

Browse all BigCommerce field notes Shopify order transactions that do not add up to the total Shopify orders stuck Unpaid after a payment made outside checkout

Citations

On the problem:

  1. BigCommerce Support: update external_id on order using V2 Orders API. support.bigcommerce.com update external_id on order
  2. BigCommerce Support: update the external_id field through the orders V2 API. support.bigcommerce.com update the external_id field
  3. BigCommerce Developer Center: Orders Overview. developer.bigcommerce.com/docs/store-operations/orders

On the solution:

  1. BigCommerce Docs: Update Order, REST Management API reference. docs.bigcommerce.com update-order
  2. BigCommerce Docs: Get Order, REST Management API reference. docs.bigcommerce.com get-order
  3. BigCommerce Docs: List Orders, REST Management API reference. docs.bigcommerce.com get-orders

Stuck on a tricky one?

If you have a problem in BigCommerce orders, payments, catalog, or store migrations that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this clear your reconciliation backlog?

If this saved you from fighting a write-once field or guessing at a match, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all BigCommerce field notes