Repair Migration and housekeeping
Backfill external id for reconciliation
A finance or ops export from the old system lists orders by their legacy id. In Medusa, that same order has no idea it ever had one. Nobody wrote it down, or the flow that created the order never threaded it through, and now the two systems cannot be joined without a person opening spreadsheets side by side. Here is why Medusa v2 has nowhere built in to keep that mapping, and a script that finds the gap and fills it in only when it is sure.
Medusa v2's Order module has no first-class external_id column. The official ERP integration recipe recommends storing the external or legacy identifier under metadata.external_id, a generic JSONB field, instead of a structured one. Orders created before an integration existed, imported by a seed script, or created through a flow that dropped metadata along the way, end up with metadata null or missing that key. Run a script that lists orders missing metadata.external_id, matches each one against a legacy export by display_id or by email, total, and created_at, and only applies the id when exactly one legacy row matches. Anything ambiguous or with no match is flagged for a human, never guessed. Full code, tests, and a dry run guard are below.
The problem in plain words
Medusa's Order module was not built with a slot for a foreign system's id. It has a rich internal id, order_id, things like order_01H..., but that id means nothing to the ERP, the old storefront, or the accounting export sitting in a spreadsheet somewhere. The only place Medusa gives you to store an outside identifier is metadata, a generic JSONB bucket for arbitrary custom data.
That works fine going forward, as long as every order-creation path remembers to write metadata.external_id at the moment the order is created. But plenty of orders were not created that way. They came from before the integration existed, from a one-off seed script during a migration, or from a flow that quietly dropped the metadata on the way, a pattern GitHub issues #7398 and #5764 both describe as easy to trigger, since metadata can be lost on a query or on an update that does not resend it. Once that happens, there is no unique constraint or migration path in Medusa tying the internal order back to the legacy id. The mapping is just gone, and the only way back is to guess from context, not to look it up.
Why it happens
None of this is a bug in the sense of broken math. Medusa is doing exactly what its schema allows. A few common ways stores end up with orders missing metadata.external_id:
- Orders that existed in the old system and were migrated or re-created in Medusa before the ERP or accounting integration was wired up, so nothing ever wrote the key.
- Bulk imports and seed scripts used during a store migration that built orders quickly and skipped the metadata step entirely.
- Order-creation flows that call an update somewhere downstream without resending the full metadata object, which Medusa v2 issues such as #7398 and #5764 show can silently drop or fail to persist custom keys.
- Draft orders or manually entered orders created directly in the Admin, where there was no automated path to stamp the external id in the first place.
Because there is no unique constraint or migration path linking Medusa's internal order_id to any legacy identifier, once the mapping is missing it cannot be looked up. It can only be reconstructed by matching against an external export, and that has to be done carefully. See the citations at the end for the exact issues and docs.
Recovering a lost id is fundamentally a guess unless the match is unambiguous. So the safe pattern is not "assign every unmatched order the closest legacy row." It is "assign only the orders where exactly one legacy row matches, and flag everything else for a human." A wrong external_id is worse than a missing one, because it silently points cross-system reconciliation at the wrong record.
The fix, as a flow
We do not touch orders that already have metadata.external_id. The job lists orders missing it, builds a lookup from a legacy export keyed by display_id when available or a fuzzy key otherwise, and only for orders with exactly one candidate match does it write the id back, re-sending the full metadata object so nothing else gets dropped. Ambiguous and no-match orders go to a report instead of a guess.
Build it step by step
Authenticate against the Admin API
Exchange the admin email and password for a JWT at /auth/user/emailpass, then send it as a Bearer token on every /admin/* call. Keep the backend URL and credentials in environment variables, never in the file.
pip install requests
export MEDUSA_BACKEND_URL="http://localhost:9000"
export MEDUSA_ADMIN_EMAIL="admin@example.com"
export MEDUSA_ADMIN_PASSWORD="supersecret"
export LEGACY_EXPORT_PATH="legacy_orders.csv"
export DRY_RUN="true" # start safe, change to false to writenpm install @medusajs/js-sdk
export MEDUSA_BACKEND_URL="http://localhost:9000"
export MEDUSA_ADMIN_EMAIL="admin@example.com"
export MEDUSA_ADMIN_PASSWORD="supersecret"
export LEGACY_EXPORT_PATH="legacy_orders.csv"
export DRY_RUN="true" // start safe, change to false to writeList orders and find the ones missing the key
Ask for orders with metadata expanded, paging with limit and offset. Medusa's fields param cannot filter inside a JSONB column, so the presence check for metadata.external_id has to happen client-side, after the page comes back.
import os, requests
BACKEND_URL = os.environ["MEDUSA_BACKEND_URL"]
def admin_get(token, path, params=None):
r = requests.get(
f"{BACKEND_URL}{path}",
headers={"Authorization": f"Bearer {token}"},
params=params or {},
timeout=30,
)
r.raise_for_status()
return r.json()
def list_orders_missing_external_id(token):
missing = []
offset = 0
limit = 200
while True:
data = admin_get(token, "/admin/orders", {
"fields": "id,display_id,created_at,email,*metadata",
"limit": limit,
"offset": offset,
})
for order in data["orders"]:
metadata = order.get("metadata") or {}
if not metadata.get("external_id"):
missing.append(order)
offset += limit
if offset >= data["count"]:
return missingimport Medusa from "@medusajs/js-sdk";
const sdk = new Medusa({ baseUrl: process.env.MEDUSA_BACKEND_URL, auth: { type: "jwt" } });
async function listOrdersMissingExternalId() {
const missing = [];
let offset = 0;
const limit = 200;
while (true) {
const { orders, count } = await sdk.admin.order.list({
fields: "id,display_id,created_at,email,*metadata",
limit,
offset,
});
for (const order of orders) {
const metadata = order.metadata || {};
if (!metadata.external_id) missing.push(order);
}
offset += limit;
if (offset >= count) return missing;
}
}Load the legacy export and build a lookup map
Read the old system's export, typically a CSV, into a list of candidate rows. Each row carries a legacy id and whatever join keys the export has: display_id when the old system kept the same sequential order number, or email, total, and created_at as a fuzzy fallback.
import csv
def load_legacy_candidates(path):
"""Reads a CSV with columns: legacy_id, display_id, email, total, created_at.
display_id, total, and created_at may be blank."""
candidates = []
with open(path, newline="", encoding="utf-8") as f:
for row in csv.DictReader(f):
candidates.append({
"legacyId": row["legacy_id"],
"display_id": int(row["display_id"]) if row.get("display_id") else None,
"email": row.get("email") or None,
"total": float(row["total"]) if row.get("total") else None,
"created_at": row.get("created_at") or None,
})
return candidatesimport { readFileSync } from "node:fs";
/**
* Reads a CSV with columns: legacy_id, display_id, email, total, created_at.
* display_id, total, and created_at may be blank.
*/
export function loadLegacyCandidates(path) {
const [header, ...rows] = readFileSync(path, "utf-8").trim().split("\n");
const cols = header.split(",");
return rows.filter(Boolean).map((line) => {
const cells = line.split(",");
const row = Object.fromEntries(cols.map((c, i) => [c, cells[i]]));
return {
legacyId: row.legacy_id,
display_id: row.display_id ? Number(row.display_id) : null,
email: row.email || null,
total: row.total ? Number(row.total) : null,
created_at: row.created_at || null,
};
});
}Decide, with one pure function
Keep the matching logic in its own function that takes an order and the full list of legacy candidates and returns a plain decision, never touching the network. It skips orders that already have an id, matches on display_id when present or on email, total within an epsilon, and created_at within a day window otherwise, and only ever applies when exactly one candidate matches.
import datetime
TOTAL_EPSILON = 0.01
DAY_WINDOW_SECONDS = 86400
def _parse_epoch(iso):
if not iso:
return None
return datetime.datetime.fromisoformat(iso.replace("Z", "+00:00")).timestamp()
def _matches_fuzzy(order, candidate):
if not (order.get("email") and candidate.get("email")):
return False
if order["email"].strip().lower() != candidate["email"].strip().lower():
return False
if candidate.get("total") is None or order.get("total") is None:
return False
if abs(candidate["total"] - order["total"]) > TOTAL_EPSILON:
return False
order_epoch = _parse_epoch(order.get("created_at"))
candidate_epoch = _parse_epoch(candidate.get("created_at"))
if order_epoch is None or candidate_epoch is None:
return False
return abs(order_epoch - candidate_epoch) <= DAY_WINDOW_SECONDS
def decide_external_id_backfill(order, legacy_candidates):
"""Pure decision function. No I/O.
order: {"id": str, "display_id": int, "metadata": dict | None,
"created_at": str, "email": str | None, "total": float | None}
legacy_candidates: [{"legacyId": str, "display_id": int | None,
"email": str | None, "total": float | None,
"created_at": str | None}]
Returns {"action", "external_id", "reason"} where action is one of
"skip_has_id" | "apply" | "flag_ambiguous" | "flag_no_match".
"""
existing = (order.get("metadata") or {}).get("external_id")
if isinstance(existing, str) and existing.strip():
return {"action": "skip_has_id", "reason": "metadata.external_id already set"}
if order.get("display_id") is not None:
matches = [
c for c in legacy_candidates
if c.get("display_id") is not None and c["display_id"] == order["display_id"]
]
else:
matches = [c for c in legacy_candidates if _matches_fuzzy(order, c)]
if len(matches) == 1:
return {
"action": "apply",
"external_id": matches[0]["legacyId"],
"reason": "exactly one legacy candidate matched",
}
if len(matches) == 0:
return {"action": "flag_no_match", "reason": "no legacy candidate matched"}
return {
"action": "flag_ambiguous",
"reason": f"{len(matches)} legacy candidates matched, refusing to guess",
}const TOTAL_EPSILON = 0.01;
const DAY_WINDOW_MS = 86400 * 1000;
function matchesFuzzy(order, candidate) {
if (!(order.email && candidate.email)) return false;
if (order.email.trim().toLowerCase() !== candidate.email.trim().toLowerCase()) return false;
if (candidate.total == null || order.total == null) return false;
if (Math.abs(candidate.total - order.total) > TOTAL_EPSILON) return false;
const orderMs = order.created_at ? Date.parse(order.created_at) : NaN;
const candidateMs = candidate.created_at ? Date.parse(candidate.created_at) : NaN;
if (Number.isNaN(orderMs) || Number.isNaN(candidateMs)) return false;
return Math.abs(orderMs - candidateMs) <= DAY_WINDOW_MS;
}
/**
* Pure decision function. No I/O.
* @returns {{ action: "skip_has_id"|"apply"|"flag_ambiguous"|"flag_no_match",
* external_id?: string, reason: string }}
*/
export function decideExternalIdBackfill(order, legacyCandidates) {
const existing = (order.metadata || {}).external_id;
if (typeof existing === "string" && existing.trim()) {
return { action: "skip_has_id", reason: "metadata.external_id already set" };
}
let matches;
if (order.display_id != null) {
matches = legacyCandidates.filter(
(c) => c.display_id != null && c.display_id === order.display_id
);
} else {
matches = legacyCandidates.filter((c) => matchesFuzzy(order, c));
}
if (matches.length === 1) {
return {
action: "apply",
external_id: matches[0].legacyId,
reason: "exactly one legacy candidate matched",
};
}
if (matches.length === 0) {
return { action: "flag_no_match", reason: "no legacy candidate matched" };
}
return {
action: "flag_ambiguous",
reason: `${matches.length} legacy candidates matched, refusing to guess`,
};
}Apply the id by resending the full metadata object
When the action is apply, update the order with the complete existing metadata spread plus external_id. Medusa v2 replaces nested metadata on update rather than merging it, so sending only { external_id } would silently wipe out any other keys already stored there.
def admin_post(token, path, json_body):
r = requests.post(
f"{BACKEND_URL}{path}",
headers={"Authorization": f"Bearer {token}"},
json=json_body,
timeout=30,
)
r.raise_for_status()
return r.json()
def apply_external_id(token, order, external_id):
existing_metadata = order.get("metadata") or {}
return admin_post(token, f"/admin/orders/{order['id']}", {
"metadata": {**existing_metadata, "external_id": external_id},
})async function applyExternalId(order, externalId) {
const existingMetadata = order.metadata || {};
return sdk.admin.order.update(order.id, {
metadata: { ...existingMetadata, external_id: externalId },
});
}Wire it together with a dry run guard
The loop ties every piece together. Notice the dry run guard. On the first run, leave DRY_RUN on so the script only prints a CSV of {medusa_order_id, display_id, external_id, action} for human sign-off. Read it, agree with it, then switch it off to let it write. Orders in flag_ambiguous or flag_no_match are never written automatically, they go to the same report for manual reconciliation.
Always start with DRY_RUN=true. Only apply orders classified apply, the ones with exactly one legacy candidate. Never guess between multiple matches, and never invent an id for an order with no match at all, since a wrong external_id would silently corrupt cross-system reconciliation, which is worse than leaving the order unmapped.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only ever writes an id when the match against the legacy export was unambiguous.
View this code on GitHub Full runnable folder with tests in the medusa-fixes repo.
"""Backfill metadata.external_id on Medusa orders for cross-system reconciliation.
Medusa v2's Order module has no first-class external_id column. The official
ERP integration recipe stores it under metadata.external_id, a generic JSONB
field, instead of a structured one. Orders created before an integration
existed, imported by a seed script, or created through a flow that dropped
metadata along the way, end up with metadata null or missing that key, and
there is no built-in mechanism to recover the mapping once it is lost.
This lists orders missing metadata.external_id, matches each one against a
legacy CSV export by display_id when available or by email, total, and
created_at otherwise, and only applies the id when exactly one legacy row
matches. Orders with zero or multiple matches are flagged for manual
reconciliation, never guessed. Metadata is always fully resent on update,
since Medusa v2 replaces nested metadata rather than merging it.
Run once with DRY_RUN=true for a CSV report before flipping to false.
"""
import os
import csv
import logging
import datetime
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("backfill_external_id")
BACKEND_URL = os.environ["MEDUSA_BACKEND_URL"]
ADMIN_EMAIL = os.environ["MEDUSA_ADMIN_EMAIL"]
ADMIN_PASSWORD = os.environ["MEDUSA_ADMIN_PASSWORD"]
LEGACY_EXPORT_PATH = os.environ.get("LEGACY_EXPORT_PATH", "legacy_orders.csv")
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
TOTAL_EPSILON = 0.01
DAY_WINDOW_SECONDS = 86400
ORDER_FIELDS = "id,display_id,created_at,email,*metadata"
def get_admin_token():
r = requests.post(
f"{BACKEND_URL}/auth/user/emailpass",
json={"email": ADMIN_EMAIL, "password": ADMIN_PASSWORD},
timeout=30,
)
r.raise_for_status()
return r.json()["token"]
def admin_get(token, path, params=None):
r = requests.get(
f"{BACKEND_URL}{path}",
headers={"Authorization": f"Bearer {token}"},
params=params or {},
timeout=30,
)
r.raise_for_status()
return r.json()
def admin_post(token, path, json_body):
r = requests.post(
f"{BACKEND_URL}{path}",
headers={"Authorization": f"Bearer {token}"},
json=json_body,
timeout=30,
)
r.raise_for_status()
return r.json()
def list_orders_missing_external_id(token):
missing = []
offset = 0
limit = 200
while True:
data = admin_get(token, "/admin/orders", {
"fields": ORDER_FIELDS,
"limit": limit,
"offset": offset,
})
for order in data["orders"]:
metadata = order.get("metadata") or {}
if not metadata.get("external_id"):
missing.append(order)
offset += limit
if offset >= data["count"]:
return missing
def load_legacy_candidates(path):
candidates = []
with open(path, newline="", encoding="utf-8") as f:
for row in csv.DictReader(f):
candidates.append({
"legacyId": row["legacy_id"],
"display_id": int(row["display_id"]) if row.get("display_id") else None,
"email": row.get("email") or None,
"total": float(row["total"]) if row.get("total") else None,
"created_at": row.get("created_at") or None,
})
return candidates
def _parse_epoch(iso):
if not iso:
return None
return datetime.datetime.fromisoformat(iso.replace("Z", "+00:00")).timestamp()
def _matches_fuzzy(order, candidate):
if not (order.get("email") and candidate.get("email")):
return False
if order["email"].strip().lower() != candidate["email"].strip().lower():
return False
if candidate.get("total") is None or order.get("total") is None:
return False
if abs(candidate["total"] - order["total"]) > TOTAL_EPSILON:
return False
order_epoch = _parse_epoch(order.get("created_at"))
candidate_epoch = _parse_epoch(candidate.get("created_at"))
if order_epoch is None or candidate_epoch is None:
return False
return abs(order_epoch - candidate_epoch) <= DAY_WINDOW_SECONDS
def decide_external_id_backfill(order, legacy_candidates):
"""Pure decision function. No I/O.
order: {"id": str, "display_id": int, "metadata": dict | None,
"created_at": str, "email": str | None, "total": float | None}
legacy_candidates: [{"legacyId": str, "display_id": int | None,
"email": str | None, "total": float | None,
"created_at": str | None}]
Returns {"action", "external_id", "reason"} where action is one of
"skip_has_id" | "apply" | "flag_ambiguous" | "flag_no_match".
"""
existing = (order.get("metadata") or {}).get("external_id")
if isinstance(existing, str) and existing.strip():
return {"action": "skip_has_id", "reason": "metadata.external_id already set"}
if order.get("display_id") is not None:
matches = [
c for c in legacy_candidates
if c.get("display_id") is not None and c["display_id"] == order["display_id"]
]
else:
matches = [c for c in legacy_candidates if _matches_fuzzy(order, c)]
if len(matches) == 1:
return {
"action": "apply",
"external_id": matches[0]["legacyId"],
"reason": "exactly one legacy candidate matched",
}
if len(matches) == 0:
return {"action": "flag_no_match", "reason": "no legacy candidate matched"}
return {
"action": "flag_ambiguous",
"reason": f"{len(matches)} legacy candidates matched, refusing to guess",
}
def apply_external_id(token, order, external_id):
existing_metadata = order.get("metadata") or {}
return admin_post(token, f"/admin/orders/{order['id']}", {
"metadata": {**existing_metadata, "external_id": external_id},
})
def run():
token = get_admin_token()
legacy_candidates = load_legacy_candidates(LEGACY_EXPORT_PATH)
orders = list_orders_missing_external_id(token)
rows = [("medusa_order_id", "display_id", "external_id", "action")]
applied = 0
flagged = 0
for order in orders:
outcome = decide_external_id_backfill(order, legacy_candidates)
action = outcome["action"]
if action == "skip_has_id":
continue
if action == "apply":
external_id = outcome["external_id"]
log.info(
"Order %s matched legacy id %s. %s",
order.get("display_id") or order["id"], external_id,
"would apply" if DRY_RUN else "applying",
)
if not DRY_RUN:
apply_external_id(token, order, external_id)
rows.append((order["id"], order.get("display_id"), external_id, "apply"))
applied += 1
else:
log.warning(
"Order %s %s: %s",
order.get("display_id") or order["id"], action, outcome["reason"],
)
rows.append((order["id"], order.get("display_id"), "", action))
flagged += 1
for row in rows:
print(",".join(str(v) if v is not None else "" for v in row))
log.info(
"Done. %d order(s) %s, %d order(s) flagged for manual reconciliation.",
applied, "to backfill" if DRY_RUN else "backfilled", flagged,
)
if __name__ == "__main__":
run()/**
* Backfill metadata.external_id on Medusa orders for cross-system reconciliation.
*
* Medusa v2's Order module has no first-class external_id column. The official
* ERP integration recipe stores it under metadata.external_id, a generic JSONB
* field, instead of a structured one. Orders created before an integration
* existed, imported by a seed script, or created through a flow that dropped
* metadata along the way, end up with metadata null or missing that key, and
* there is no built-in mechanism to recover the mapping once it is lost.
*
* This lists orders missing metadata.external_id, matches each one against a
* legacy CSV export by display_id when available or by email, total, and
* created_at otherwise, and only applies the id when exactly one legacy row
* matches. Orders with zero or multiple matches are flagged for manual
* reconciliation, never guessed. Metadata is always fully resent on update,
* since Medusa v2 replaces nested metadata rather than merging it.
* Run once with DRY_RUN=true for a CSV report before flipping to false.
*
* Guide: https://www.allanninal.dev/medusa/backfill-external-id-for-reconciliation/
*/
import { pathToFileURL } from "node:url";
import { readFileSync } from "node:fs";
const BACKEND_URL = process.env.MEDUSA_BACKEND_URL || "http://localhost:9000";
const ADMIN_EMAIL = process.env.MEDUSA_ADMIN_EMAIL || "admin@example.com";
const ADMIN_PASSWORD = process.env.MEDUSA_ADMIN_PASSWORD || "supersecret";
const LEGACY_EXPORT_PATH = process.env.LEGACY_EXPORT_PATH || "legacy_orders.csv";
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const TOTAL_EPSILON = 0.01;
const DAY_WINDOW_MS = 86400 * 1000;
const ORDER_FIELDS = "id,display_id,created_at,email,*metadata";
function matchesFuzzy(order, candidate) {
if (!(order.email && candidate.email)) return false;
if (order.email.trim().toLowerCase() !== candidate.email.trim().toLowerCase()) return false;
if (candidate.total == null || order.total == null) return false;
if (Math.abs(candidate.total - order.total) > TOTAL_EPSILON) return false;
const orderMs = order.created_at ? Date.parse(order.created_at) : NaN;
const candidateMs = candidate.created_at ? Date.parse(candidate.created_at) : NaN;
if (Number.isNaN(orderMs) || Number.isNaN(candidateMs)) return false;
return Math.abs(orderMs - candidateMs) <= DAY_WINDOW_MS;
}
/**
* Pure decision function. No I/O.
* @returns {{ action: "skip_has_id"|"apply"|"flag_ambiguous"|"flag_no_match",
* external_id?: string, reason: string }}
*/
export function decideExternalIdBackfill(order, legacyCandidates) {
const existing = (order.metadata || {}).external_id;
if (typeof existing === "string" && existing.trim()) {
return { action: "skip_has_id", reason: "metadata.external_id already set" };
}
let matches;
if (order.display_id != null) {
matches = legacyCandidates.filter(
(c) => c.display_id != null && c.display_id === order.display_id
);
} else {
matches = legacyCandidates.filter((c) => matchesFuzzy(order, c));
}
if (matches.length === 1) {
return {
action: "apply",
external_id: matches[0].legacyId,
reason: "exactly one legacy candidate matched",
};
}
if (matches.length === 0) {
return { action: "flag_no_match", reason: "no legacy candidate matched" };
}
return {
action: "flag_ambiguous",
reason: `${matches.length} legacy candidates matched, refusing to guess`,
};
}
export function loadLegacyCandidates(path) {
const [header, ...rows] = readFileSync(path, "utf-8").trim().split("\n");
const cols = header.split(",");
return rows.filter(Boolean).map((line) => {
const cells = line.split(",");
const row = Object.fromEntries(cols.map((c, i) => [c, cells[i]]));
return {
legacyId: row.legacy_id,
display_id: row.display_id ? Number(row.display_id) : null,
email: row.email || null,
total: row.total ? Number(row.total) : null,
created_at: row.created_at || null,
};
});
}
async function getAdminToken() {
const res = await fetch(`${BACKEND_URL}/auth/user/emailpass`, {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({ email: ADMIN_EMAIL, password: ADMIN_PASSWORD }),
});
if (!res.ok) throw new Error(`Medusa auth ${res.status}`);
const body = await res.json();
return body.token;
}
async function adminGet(token, path, params = {}) {
const url = new URL(`${BACKEND_URL}${path}`);
for (const [key, value] of Object.entries(params)) {
url.searchParams.set(key, value);
}
const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
if (!res.ok) throw new Error(`Medusa ${res.status} on GET ${path}`);
return res.json();
}
async function adminPost(token, path, jsonBody) {
const res = await fetch(`${BACKEND_URL}${path}`, {
method: "POST",
headers: {
Authorization: `Bearer ${token}`,
"Content-Type": "application/json",
},
body: JSON.stringify(jsonBody),
});
if (!res.ok) throw new Error(`Medusa ${res.status} on POST ${path}`);
return res.json();
}
async function listOrdersMissingExternalId(token) {
const missing = [];
let offset = 0;
const limit = 200;
while (true) {
const data = await adminGet(token, "/admin/orders", {
fields: ORDER_FIELDS,
limit,
offset,
});
for (const order of data.orders) {
const metadata = order.metadata || {};
if (!metadata.external_id) missing.push(order);
}
offset += limit;
if (offset >= data.count) return missing;
}
}
async function applyExternalId(token, order, externalId) {
const existingMetadata = order.metadata || {};
return adminPost(token, `/admin/orders/${order.id}`, {
metadata: { ...existingMetadata, external_id: externalId },
});
}
export async function run() {
const token = await getAdminToken();
const legacyCandidates = loadLegacyCandidates(LEGACY_EXPORT_PATH);
const orders = await listOrdersMissingExternalId(token);
const rows = [["medusa_order_id", "display_id", "external_id", "action"]];
let applied = 0;
let flagged = 0;
for (const order of orders) {
const outcome = decideExternalIdBackfill(order, legacyCandidates);
const action = outcome.action;
if (action === "skip_has_id") continue;
if (action === "apply") {
const externalId = outcome.external_id;
console.log(
`Order ${order.display_id || order.id} matched legacy id ${externalId}. ${DRY_RUN ? "would apply" : "applying"}`
);
if (!DRY_RUN) await applyExternalId(token, order, externalId);
rows.push([order.id, order.display_id ?? "", externalId, "apply"]);
applied++;
} else {
console.warn(`Order ${order.display_id || order.id} ${action}: ${outcome.reason}`);
rows.push([order.id, order.display_id ?? "", "", action]);
flagged++;
}
}
for (const row of rows) console.log(row.join(","));
console.log(
`Done. ${applied} order(s) ${DRY_RUN ? "to backfill" : "backfilled"}, ${flagged} order(s) flagged for manual reconciliation.`
);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
decide_external_id_backfill is the part most worth testing, because it decides which orders get written and which get flagged. It is pure, so the test needs no network and no Medusa backend. It just feeds in plain fixture orders and candidate lists and checks the answer.
from backfill_external_id import decide_external_id_backfill
def order(**over):
base = {
"id": "order_1",
"display_id": 1042,
"metadata": None,
"created_at": "2026-07-01T00:00:00Z",
"email": "buyer@example.com",
"total": 150.0,
}
base.update(over)
return base
def candidate(**over):
base = {
"legacyId": "LEG-1042",
"display_id": 1042,
"email": "buyer@example.com",
"total": 150.0,
"created_at": "2026-07-01T00:00:00Z",
}
base.update(over)
return base
def test_skip_when_already_has_external_id():
o = order(metadata={"external_id": "LEG-9999"})
result = decide_external_id_backfill(o, [candidate()])
assert result["action"] == "skip_has_id"
def test_apply_on_exact_display_id_match():
result = decide_external_id_backfill(order(), [candidate()])
assert result["action"] == "apply"
assert result["external_id"] == "LEG-1042"
def test_flag_no_match_when_display_id_absent_from_export():
result = decide_external_id_backfill(order(), [candidate(display_id=9999)])
assert result["action"] == "flag_no_match"
def test_flag_ambiguous_when_multiple_display_id_matches():
result = decide_external_id_backfill(
order(), [candidate(legacyId="LEG-A"), candidate(legacyId="LEG-B")]
)
assert result["action"] == "flag_ambiguous"
def test_falls_back_to_fuzzy_match_without_display_id():
o = order(display_id=None)
result = decide_external_id_backfill(o, [candidate(display_id=None)])
assert result["action"] == "apply"
assert result["external_id"] == "LEG-1042"
def test_fuzzy_match_rejects_total_outside_epsilon():
o = order(display_id=None)
result = decide_external_id_backfill(o, [candidate(display_id=None, total=200.0)])
assert result["action"] == "flag_no_match"
def test_fuzzy_match_rejects_created_at_outside_day_window():
o = order(display_id=None)
result = decide_external_id_backfill(
o, [candidate(display_id=None, created_at="2026-07-10T00:00:00Z")]
)
assert result["action"] == "flag_no_match"
def test_empty_string_external_id_is_treated_as_missing():
o = order(metadata={"external_id": ""})
result = decide_external_id_backfill(o, [candidate()])
assert result["action"] == "apply"import { test } from "node:test";
import assert from "node:assert/strict";
import { decideExternalIdBackfill } from "./backfill-external-id.js";
const order = (over = {}) => ({
id: "order_1",
display_id: 1042,
metadata: null,
created_at: "2026-07-01T00:00:00Z",
email: "buyer@example.com",
total: 150.0,
...over,
});
const candidate = (over = {}) => ({
legacyId: "LEG-1042",
display_id: 1042,
email: "buyer@example.com",
total: 150.0,
created_at: "2026-07-01T00:00:00Z",
...over,
});
test("skip when already has external_id", () => {
const o = order({ metadata: { external_id: "LEG-9999" } });
const result = decideExternalIdBackfill(o, [candidate()]);
assert.equal(result.action, "skip_has_id");
});
test("apply on exact display_id match", () => {
const result = decideExternalIdBackfill(order(), [candidate()]);
assert.equal(result.action, "apply");
assert.equal(result.external_id, "LEG-1042");
});
test("flag no match when display_id absent from export", () => {
const result = decideExternalIdBackfill(order(), [candidate({ display_id: 9999 })]);
assert.equal(result.action, "flag_no_match");
});
test("flag ambiguous when multiple display_id matches", () => {
const result = decideExternalIdBackfill(order(), [
candidate({ legacyId: "LEG-A" }),
candidate({ legacyId: "LEG-B" }),
]);
assert.equal(result.action, "flag_ambiguous");
});
test("falls back to fuzzy match without display_id", () => {
const o = order({ display_id: null });
const result = decideExternalIdBackfill(o, [candidate({ display_id: null })]);
assert.equal(result.action, "apply");
assert.equal(result.external_id, "LEG-1042");
});
test("fuzzy match rejects total outside epsilon", () => {
const o = order({ display_id: null });
const result = decideExternalIdBackfill(o, [candidate({ display_id: null, total: 200.0 })]);
assert.equal(result.action, "flag_no_match");
});
test("fuzzy match rejects created_at outside day window", () => {
const o = order({ display_id: null });
const result = decideExternalIdBackfill(o, [
candidate({ display_id: null, created_at: "2026-07-10T00:00:00Z" }),
]);
assert.equal(result.action, "flag_no_match");
});
test("empty string external_id is treated as missing", () => {
const o = order({ metadata: { external_id: "" } });
const result = decideExternalIdBackfill(o, [candidate()]);
assert.equal(result.action, "apply");
});Case studies
Two thousand orders with nowhere to send remittance
A B2B seller moved to Medusa a full year before wiring up their ERP. By the time the ERP integration went live, roughly two thousand historical orders had no metadata.external_id, and the finance team's remittance export could not find a single one of them by name.
Running the backfill script in dry run against the ERP's own historical export matched almost all of them cleanly by display_id, since the old system had preserved the same sequential numbers. The handful that came back flag_ambiguous, mostly orders placed the same day by the same wholesale account, went to the finance team to resolve by hand instead of being guessed.
A silent update wiped the id for a batch of orders
A custom fulfillment integration updated a batch of orders after the fact and, following the same pattern GitHub issue #5764 describes, sent a metadata payload that did not include the existing external_id. Medusa's replace-not-merge behavior meant the key was gone the next time anyone looked.
Because the store still had the original ERP export on hand, the backfill script matched the affected orders back to their legacy ids using email, total, and created_at, since display_id alone was not distinct enough for a couple of same-day orders. Those went to the flagged report, and the rest applied cleanly on the second run once DRY_RUN was turned off.
After this runs once, every order Medusa can confidently tie to a legacy row carries metadata.external_id, and the ERP or accounting export can join against Medusa without a person cross-referencing spreadsheets by hand. Orders with an ambiguous or missing match stay unmapped rather than wrong, and the CSV report gives a human exactly what they need to resolve the rest. No other metadata key gets lost along the way, since every write resends the full object.
FAQ
Why do some Medusa orders have no metadata.external_id?
Medusa v2's Order module has no first-class external_id column. The official ERP integration recipe stores the external identifier under metadata.external_id, a generic JSONB field, instead of a structured column. Orders created before an integration existed, imported by a seed script, or created through a flow that did not thread metadata through, end up with metadata null or missing that key, and there is no built-in mechanism to recover the mapping once it is lost.
Is it safe to backfill external_id with a script?
Yes, when the script only writes an unambiguous, high-confidence match found against a legacy export, gates every write behind a DRY_RUN flag, and re-sends the full existing metadata object rather than a partial one. Medusa v2 replaces nested metadata on update instead of merging it, so a partial payload would silently delete other metadata keys. Orders with more than one candidate match, or none, are never written automatically.
How do you match a Medusa order to its legacy external id?
Medusa's internal order_id has no relationship to a legacy system's id, so detection needs a side channel, typically a CSV export from the old system. Build a map of legacy rows keyed by display_id when the export has it, or by email, total, and created_at as a fuzzy key otherwise. Then, for every Medusa order missing metadata.external_id, look up candidates and only apply the ones with exactly one match.
Related field notes
Citations
On the problem:
- Bug: Metadata not included with Order query. Medusa GitHub Issue #7398. github.com/medusajs/medusa/issues/7398
- Bug: Can't add metadata when making HTTP request to update Order. Medusa GitHub Issue #5764. github.com/medusajs/medusa/issues/5764
- Medusa Documentation: Integrate ERP with Medusa. docs.medusajs.com/resources/recipes/erp
On the solution:
- Medusa Documentation: Integrate ERP with Medusa. docs.medusajs.com/resources/recipes/erp
- Medusa Documentation: Admin API Reference. docs.medusajs.com/api/admin
- Medusa Documentation: Order Management System (OMS) Recipe. docs.medusajs.com/resources/recipes/oms
Stuck on a tricky one?
If you have a problem in Medusa storefront access, pricing, inventory, orders, promotions, or workflows that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this square up your reconciliation?
If this saved you a spreadsheet full of manual lookups, 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