Reconciler Orders and Grid Sync
Reserved order ids create unexplained numbering gaps
Finance pulls the order list, notices number 000000512 is missing, and asks what happened to it. Nothing happened to it. Magento reserved that number the moment a shopper reached checkout, then the shopper closed the tab, or the card was declined, or the order-place transaction rolled back, and the number was never attached to a real order. It will never be reused. Here is why Magento leaves these gaps on purpose, and a small script that finds every one of them and reports it instead of guessing.
Magento 2 and Adobe Commerce reserve an order increment_id on the quote, stored as reserved_order_id and backed by the sales_sequence / sequence_order_* tables, the instant checkout starts, long before payment succeeds or the order row is saved. If the customer abandons checkout, the gateway declines, or the order-place transaction rolls back, that reserved number is never attached to a real order and the sequence never hands it out again. It is a permanent, intentional gap, not a deleted or missing order. You confirm it over REST by pulling inactive quotes with a non null reserved_order_id from GET /rest/V1/carts/search, then checking GET /rest/V1/orders filtered on increment_id for a match. An empty items array means the id was reserved and never consumed. The safe response is a dry run guarded reconciliation report, never a rewrite of the sequence or the order numbering itself. Full code, tests, and a dry run guard are below.
The problem in plain words
Every Magento checkout starts with a quote, the working cart object that holds items, addresses, and totals while a customer shops. The moment that customer reaches the review or payment step, Magento assigns the quote a reserved_order_id, the exact increment_id the resulting order will carry if it saves successfully. That reservation is drawn from the same sales_sequence tables that back increment_id everywhere else in Magento, and it happens before the payment gateway is even called.
Most of the time this is invisible, because most checkouts succeed. Payment clears, the order saves, and the reserved number becomes the order's real increment_id. But plenty of checkouts do not finish. A shopper closes the tab after entering card details. A gateway declines the charge. The order-place transaction itself fails partway and rolls back. In every one of those cases, the quote already has its reserved_order_id, but no sales_order row ever gets created to claim it. The sequence does not know or care that the number went unused, so it never reissues it. The number is gone for good, and it looks exactly like a hole where an order used to be.
Why it happens
- A customer reaches the payment step, the quote gets its
reserved_order_id, and then the browser tab is closed or the session times out before the order is placed. - A payment gateway, PayPal is a frequently reported example, declines or errors out after Magento has already reserved the order number for that quote.
- The order-place transaction itself throws partway through, a validation failure, an inventory reservation conflict, or an unexpected exception, and the whole transaction rolls back, but the earlier quote reservation is untouched by that rollback.
- A multi-step or hosted checkout redirects the customer away and they never return, leaving an inactive quote holding a reserved number nobody will ever finish using.
This is a well documented, expected side effect of how reserved_order_id and the sales_sequence tables work, confirmed repeatedly on Magento's own forums and issue tracker, including reports of failed PayPal orders skipping a number. It is not data corruption and not evidence of a deleted order. See the citations at the end for the exact threads.
Increment ids are expected to look gapless, which is exactly why a missing one gets misread as loss. But reserved_order_id is assigned at the start of checkout, not at the end, so a gap only proves that a checkout was attempted and abandoned somewhere before the order saved. A script cannot fix this, because the only safe repair is administrative, not a rewrite. What it can do is confirm, for each candidate gap, that no order ever actually claimed that number, and hand back a clean list for a human to review or file away.
The fix, as a flow
We never touch sales_sequence or attempt to reissue a number. Instead we add a job that lists quotes carrying a reserved order id that are no longer active, confirms against the Orders API that nothing ever consumed that id, and reports every confirmed gap. Only when explicitly told to write, it marks the originating quote so it is excluded from future scans, never a call that mutates order numbering.
Build it step by step
Get an admin bearer token
Authenticate the same way as any other Magento REST client. Either call POST /rest/V1/integration/admin/token with an admin username and password, or create an integration and use its token directly. Keep the store URL and token in environment variables, never in the file.
pip install requests
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export PAGE_SIZE="200"
export DRY_RUN="true" # start safe, change to false to mark reviewed quotes// Node 18+ has fetch built in, no dependencies needed
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export PAGE_SIZE="200"
export DRY_RUN="true" // start safe, change to false to mark reviewed quotesTalk to the Magento REST API
Every call sends Authorization: Bearer <token> to a /rest/V1 route. A small helper wraps GET and PUT and raises on a non 200 response, since Magento returns structured error bodies worth surfacing as is.
import os, requests
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
def magento_get(path, params=None):
r = requests.get(
f"{MAGENTO_URL}/rest/V1{path}",
params=params or {},
headers={"Authorization": f"Bearer {TOKEN}"},
timeout=30,
)
r.raise_for_status()
return r.json()
def magento_put(path, payload):
r = requests.put(
f"{MAGENTO_URL}/rest/V1{path}",
json=payload,
headers={"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"},
timeout=30,
)
r.raise_for_status()
return r.json()const MAGENTO_URL = (process.env.MAGENTO_URL || "").replace(/\/$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN;
async function magentoGet(path, params = {}) {
const url = new URL(`${MAGENTO_URL}/rest/V1${path}`);
for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);
const res = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function magentoPut(path, payload) {
const res = await fetch(`${MAGENTO_URL}/rest/V1${path}`, {
method: "PUT",
headers: { Authorization: `Bearer ${TOKEN}`, "Content-Type": "application/json" },
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}Page candidate reserved-but-unplaced quotes
Call GET /rest/V1/carts/search filtered for a non null reserved_order_id and is_active equal to 0, paginating with searchCriteria[pageSize] and currentPage. Every quote in this set already has a reserved number and is no longer a live cart, so it is a candidate gap until proven otherwise.
def candidate_quotes(page_size=200):
current_page = 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "reserved_order_id",
"searchCriteria[filterGroups][0][filters][0][conditionType]": "notnull",
"searchCriteria[filterGroups][1][filters][0][field]": "is_active",
"searchCriteria[filterGroups][1][filters][0][value]": 0,
"searchCriteria[pageSize]": page_size,
"searchCriteria[currentPage]": current_page,
}
data = magento_get("/carts/search", params)
for item in data["items"]:
yield item
if current_page * page_size >= data["total_count"]:
return
current_page += 1async function* candidateQuotes(pageSize = 200) {
let currentPage = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "reserved_order_id",
"searchCriteria[filterGroups][0][filters][0][conditionType]": "notnull",
"searchCriteria[filterGroups][1][filters][0][field]": "is_active",
"searchCriteria[filterGroups][1][filters][0][value]": 0,
"searchCriteria[pageSize]": pageSize,
"searchCriteria[currentPage]": currentPage,
};
const data = await magentoGet("/carts/search", params);
for (const item of data.items) yield item;
if (currentPage * pageSize >= data.total_count) return;
currentPage += 1;
}
}Decide, with one pure function
Keep the classification in its own function that takes a plain quote and the matching orders already fetched for it, with no I/O of its own. That makes it trivial to test with fixture arrays, as we do later. A quote is consumed when some matching order's incrementId equals the quote's reservedOrderId. Otherwise, a still active quote is pending_checkout, a live cart that has not failed yet, not a gap. Only an inactive quote whose id was never consumed is an orphaned_gap.
def classify_reserved_order_gap(quote, matching_orders):
if any(o["incrementId"] == quote["reservedOrderId"] for o in matching_orders):
status = "consumed"
elif quote["isActive"]:
status = "pending_checkout"
else:
status = "orphaned_gap"
return {"status": status, "reservedOrderId": quote["reservedOrderId"]}export function classifyReservedOrderGap(quote, matchingOrders) {
let status;
if (matchingOrders.some((o) => o.incrementId === quote.reservedOrderId)) {
status = "consumed";
} else if (quote.isActive) {
status = "pending_checkout";
} else {
status = "orphaned_gap";
}
return { status, reservedOrderId: quote.reservedOrderId };
}Confirm each candidate against the Orders API, then report only
For every candidate quote, call GET /rest/V1/orders filtered on increment_id equal to the quote's reserved_order_id. An empty items array is what feeds matchingOrders as an empty list into the classifier above. Report every confirmed orphaned_gap with the reserved id, the quote id, the customer, and updated_at. Only when DRY_RUN=false is explicitly set, PUT /rest/V1/carts/{cartId} to set is_active=0 on that quote so future scans skip it. Nothing here ever calls an endpoint that mutates sales_sequence or order numbering.
def orders_matching_increment_id(increment_id):
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "increment_id",
"searchCriteria[filterGroups][0][filters][0][value]": increment_id,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
}
data = magento_get("/orders", params)
return [{"incrementId": item["increment_id"]} for item in data["items"]]
def mark_quote_reviewed(cart_id):
payload = {"quote": {"id": cart_id, "is_active": False}}
return magento_put(f"/carts/{cart_id}", payload)async function ordersMatchingIncrementId(incrementId) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "increment_id",
"searchCriteria[filterGroups][0][filters][0][value]": incrementId,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
};
const data = await magentoGet("/orders", params);
return data.items.map((item) => ({ incrementId: item.increment_id }));
}
async function markQuoteReviewed(cartId) {
const payload = { quote: { id: cartId, is_active: false } };
return magentoPut(`/carts/${cartId}`, payload);
}Wire it together with a dry run guard
The loop pages every candidate quote, confirms it against the Orders API, classifies it with the pure function, and prints one report line per orphaned_gap with the reserved id, quote id, customer, and last update time. Leave DRY_RUN on for the first runs so nothing is written, just reported. Only flip it to false once you have reviewed the report and are ready to mark those quotes so they stop showing up in future scans. Run it on a schedule that fits your checkout volume, for example nightly.
This script never rewrites sales_sequence or reissues a skipped increment_id. With DRY_RUN=true, the default, it only prints the reconciliation report. With DRY_RUN=false, it additionally sets is_active=0 on the originating quote, never anything that touches order numbering. Re-pointing the sequence forward or reusing a reserved id is unsafe and unsupported, and this script will not do either.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, pages every candidate quote, confirms and classifies each one with the pure function, prints a full reconciliation report, and only marks reviewed quotes when explicitly told to write. It is safe to run again and again because it never touches the sequence.
"""Find and report Magento 2 reserved order ids that created a permanent numbering gap.
Magento reserves an order increment_id on the quote, through reserved_order_id
backed by the sales_sequence tables, the moment checkout begins, before payment
succeeds or the order actually saves. If checkout is abandoned, the gateway
declines, or the order-place transaction rolls back, that reserved id is never
attached to a real order and the sequence never reuses it. This never rewrites
the sequence or reissues a number. It pages inactive quotes carrying a reserved
order id, confirms against the Orders API that no order ever claimed it,
classifies each with a pure function, always reports orphaned gaps, and only
when DRY_RUN is explicitly false marks the originating quote inactive so it is
excluded from future scans. Run on a schedule. Safe to run again and again.
"""
import os
import logging
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("reconcile_reserved_order_ids")
MAGENTO_URL = os.environ.get("MAGENTO_URL", "https://demo.example.com").rstrip("/")
TOKEN = os.environ.get("MAGENTO_ADMIN_TOKEN", "token_dummy")
PAGE_SIZE = int(os.environ.get("PAGE_SIZE", "200"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
def magento_get(path, params=None):
r = requests.get(
f"{MAGENTO_URL}/rest/V1{path}",
params=params or {},
headers={"Authorization": f"Bearer {TOKEN}"},
timeout=30,
)
r.raise_for_status()
return r.json()
def magento_put(path, payload):
r = requests.put(
f"{MAGENTO_URL}/rest/V1{path}",
json=payload,
headers={"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"},
timeout=30,
)
r.raise_for_status()
return r.json()
def candidate_quotes(page_size=200):
current_page = 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "reserved_order_id",
"searchCriteria[filterGroups][0][filters][0][conditionType]": "notnull",
"searchCriteria[filterGroups][1][filters][0][field]": "is_active",
"searchCriteria[filterGroups][1][filters][0][value]": 0,
"searchCriteria[pageSize]": page_size,
"searchCriteria[currentPage]": current_page,
}
data = magento_get("/carts/search", params)
for item in data["items"]:
yield item
if current_page * page_size >= data["total_count"]:
return
current_page += 1
def normalize_quote(item):
return {
"cartId": item.get("id"),
"reservedOrderId": item.get("reserved_order_id"),
"isActive": bool(item.get("is_active")),
"updatedAt": item.get("updated_at"),
"customerEmail": (item.get("customer") or {}).get("email"),
}
def orders_matching_increment_id(increment_id):
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "increment_id",
"searchCriteria[filterGroups][0][filters][0][value]": increment_id,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
}
data = magento_get("/orders", params)
return [{"incrementId": item["increment_id"]} for item in data["items"]]
def classify_reserved_order_gap(quote, matching_orders):
if any(o["incrementId"] == quote["reservedOrderId"] for o in matching_orders):
status = "consumed"
elif quote["isActive"]:
status = "pending_checkout"
else:
status = "orphaned_gap"
return {"status": status, "reservedOrderId": quote["reservedOrderId"]}
def mark_quote_reviewed(cart_id):
payload = {"quote": {"id": cart_id, "is_active": False}}
return magento_put(f"/carts/{cart_id}", payload)
def run():
gaps = []
scanned = 0
for raw in candidate_quotes(PAGE_SIZE):
quote = normalize_quote(raw)
if not quote["reservedOrderId"]:
continue
scanned += 1
matching_orders = orders_matching_increment_id(quote["reservedOrderId"])
result = classify_reserved_order_gap(quote, matching_orders)
if result["status"] == "orphaned_gap":
gaps.append(quote)
if not gaps:
log.info("Done. Scanned %d quote(s). 0 orphaned reserved id gap(s) found.", scanned)
return
for quote in gaps:
log.warning(
"reserved_order_id %s orphaned. cart_id=%s customer=%s updated_at=%s",
quote["reservedOrderId"], quote["cartId"], quote["customerEmail"], quote["updatedAt"],
)
if not DRY_RUN:
mark_quote_reviewed(quote["cartId"])
log.info(
"Done. Scanned %d quote(s). %d orphaned reserved id gap(s) %s.",
scanned, len(gaps), "to mark reviewed" if DRY_RUN else "marked reviewed",
)
if __name__ == "__main__":
run()/**
* Find and report Magento 2 reserved order ids that created a permanent numbering gap.
*
* Magento reserves an order increment_id on the quote, through reserved_order_id
* backed by the sales_sequence tables, the moment checkout begins, before payment
* succeeds or the order actually saves. If checkout is abandoned, the gateway
* declines, or the order-place transaction rolls back, that reserved id is never
* attached to a real order and the sequence never reuses it. This never rewrites
* the sequence or reissues a number. It pages inactive quotes carrying a reserved
* order id, confirms against the Orders API that no order ever claimed it,
* classifies each with a pure function, always reports orphaned gaps, and only
* when DRY_RUN is explicitly false marks the originating quote inactive so it is
* excluded from future scans. Run on a schedule.
*
* Guide: https://www.allanninal.dev/magento/reserved-order-id-numbering-gaps/
*/
import { pathToFileURL } from "node:url";
const MAGENTO_URL = (process.env.MAGENTO_URL || "https://demo.example.com").replace(/\/$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN || "token_dummy";
const PAGE_SIZE = Number(process.env.PAGE_SIZE || 200);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
export function classifyReservedOrderGap(quote, matchingOrders) {
let status;
if (matchingOrders.some((o) => o.incrementId === quote.reservedOrderId)) {
status = "consumed";
} else if (quote.isActive) {
status = "pending_checkout";
} else {
status = "orphaned_gap";
}
return { status, reservedOrderId: quote.reservedOrderId };
}
async function magentoGet(path, params = {}) {
const url = new URL(`${MAGENTO_URL}/rest/V1${path}`);
for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);
const res = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function magentoPut(path, payload) {
const res = await fetch(`${MAGENTO_URL}/rest/V1${path}`, {
method: "PUT",
headers: { Authorization: `Bearer ${TOKEN}`, "Content-Type": "application/json" },
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function* candidateQuotes(pageSize = 200) {
let currentPage = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "reserved_order_id",
"searchCriteria[filterGroups][0][filters][0][conditionType]": "notnull",
"searchCriteria[filterGroups][1][filters][0][field]": "is_active",
"searchCriteria[filterGroups][1][filters][0][value]": 0,
"searchCriteria[pageSize]": pageSize,
"searchCriteria[currentPage]": currentPage,
};
const data = await magentoGet("/carts/search", params);
for (const item of data.items) yield item;
if (currentPage * pageSize >= data.total_count) return;
currentPage += 1;
}
}
function normalizeQuote(item) {
return {
cartId: item.id,
reservedOrderId: item.reserved_order_id,
isActive: Boolean(item.is_active),
updatedAt: item.updated_at,
customerEmail: item.customer && item.customer.email,
};
}
async function ordersMatchingIncrementId(incrementId) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "increment_id",
"searchCriteria[filterGroups][0][filters][0][value]": incrementId,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
};
const data = await magentoGet("/orders", params);
return data.items.map((item) => ({ incrementId: item.increment_id }));
}
async function markQuoteReviewed(cartId) {
const payload = { quote: { id: cartId, is_active: false } };
return magentoPut(`/carts/${cartId}`, payload);
}
export async function run() {
const gaps = [];
let scanned = 0;
for await (const raw of candidateQuotes(PAGE_SIZE)) {
const quote = normalizeQuote(raw);
if (!quote.reservedOrderId) continue;
scanned++;
const matchingOrders = await ordersMatchingIncrementId(quote.reservedOrderId);
const result = classifyReservedOrderGap(quote, matchingOrders);
if (result.status === "orphaned_gap") gaps.push(quote);
}
if (gaps.length === 0) {
console.log(`Done. Scanned ${scanned} quote(s). 0 orphaned reserved id gap(s) found.`);
return;
}
for (const quote of gaps) {
console.warn(
`reserved_order_id ${quote.reservedOrderId} orphaned. cart_id=${quote.cartId} customer=${quote.customerEmail} updated_at=${quote.updatedAt}`
);
if (!DRY_RUN) await markQuoteReviewed(quote.cartId);
}
console.log(
`Done. Scanned ${scanned} quote(s). ${gaps.length} orphaned reserved id gap(s) ${DRY_RUN ? "to mark reviewed" : "marked reviewed"}.`
);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The classification rule is the part most worth testing, because it decides whether a candidate quote gets reported as a genuine gap. Because we kept classify_reserved_order_gap pure, the test needs no network and no Magento store. It just feeds in plain fixture quotes and order matches and checks the answer.
from reconcile_reserved_order_ids import classify_reserved_order_gap
def quote(**over):
base = {"reservedOrderId": "000000512", "isActive": False, "updatedAt": "2026-07-01T10:00:00Z"}
base.update(over)
return base
def test_consumed_when_matching_order_exists():
result = classify_reserved_order_gap(quote(), [{"incrementId": "000000512"}])
assert result["status"] == "consumed"
def test_orphaned_gap_when_inactive_and_no_match():
result = classify_reserved_order_gap(quote(), [])
assert result["status"] == "orphaned_gap"
def test_pending_checkout_when_still_active_and_no_match():
result = classify_reserved_order_gap(quote(isActive=True), [])
assert result["status"] == "pending_checkout"
def test_consumed_takes_priority_over_active_flag():
result = classify_reserved_order_gap(quote(isActive=True), [{"incrementId": "000000512"}])
assert result["status"] == "consumed"
def test_unrelated_order_match_does_not_count_as_consumed():
result = classify_reserved_order_gap(quote(), [{"incrementId": "000000999"}])
assert result["status"] == "orphaned_gap"
def test_result_carries_the_reserved_order_id():
result = classify_reserved_order_gap(quote(reservedOrderId="000000777"), [])
assert result["reservedOrderId"] == "000000777"
def test_empty_matching_orders_list_is_handled():
result = classify_reserved_order_gap(quote(isActive=False), [])
assert result["status"] == "orphaned_gap"import { test } from "node:test";
import assert from "node:assert/strict";
import { classifyReservedOrderGap } from "./reconcile-reserved-order-ids.js";
const quote = (over = {}) => ({
reservedOrderId: "000000512",
isActive: false,
updatedAt: "2026-07-01T10:00:00Z",
...over,
});
test("consumed when matching order exists", () => {
const result = classifyReservedOrderGap(quote(), [{ incrementId: "000000512" }]);
assert.equal(result.status, "consumed");
});
test("orphaned gap when inactive and no match", () => {
const result = classifyReservedOrderGap(quote(), []);
assert.equal(result.status, "orphaned_gap");
});
test("pending checkout when still active and no match", () => {
const result = classifyReservedOrderGap(quote({ isActive: true }), []);
assert.equal(result.status, "pending_checkout");
});
test("consumed takes priority over active flag", () => {
const result = classifyReservedOrderGap(quote({ isActive: true }), [{ incrementId: "000000512" }]);
assert.equal(result.status, "consumed");
});
test("unrelated order match does not count as consumed", () => {
const result = classifyReservedOrderGap(quote(), [{ incrementId: "000000999" }]);
assert.equal(result.status, "orphaned_gap");
});
test("result carries the reserved order id", () => {
const result = classifyReservedOrderGap(quote({ reservedOrderId: "000000777" }), []);
assert.equal(result.reservedOrderId, "000000777");
});
test("empty matching orders list is handled", () => {
const result = classifyReservedOrderGap(quote({ isActive: false }), []);
assert.equal(result.status, "orphaned_gap");
});Case studies
Finance flags "missing" invoices every week
A mid-size storefront ran a monthly reconciliation where finance compared the order grid against expected sequential invoice numbers. Every month, a handful of numbers were missing, and finance opened a ticket assuming orders had been deleted or the database was corrupted.
Running the reconciliation report against /rest/V1/carts/search and /rest/V1/orders showed every missing number traced back to an inactive quote whose PayPal payment had declined after the number was already reserved. The report gave finance a clean explanation with the customer and timestamp attached, and the monthly ticket stopped being filed.
A support agent almost refunded the wrong customer
A customer emailed asking about "order 000000388," a number they had seen in a browser tab during a failed checkout attempt weeks earlier. A support agent nearly issued a refund search against that number before realizing no order with that increment_id had ever existed.
A nightly run of the script had already logged that exact reserved id as an orphaned gap, tied to an abandoned quote from that customer's email address on that date. The agent used the report to explain the gap directly instead of escalating it as a data problem.
After this runs on a schedule, a missing order number is a lookup away from a clear answer instead of an open question. The report carries the reserved id, the originating quote, the customer, and when the checkout was last touched, and the only write this script ever makes, when explicitly enabled, is marking that quote reviewed so it does not resurface in the next scan. The sequence itself is never touched, so there is no risk of a future collision from a well meaning renumbering attempt.
FAQ
Why does my Magento order numbering skip numbers?
Magento reserves an order increment_id on the quote the moment checkout begins, through reserved_order_id backed by the sales_sequence tables, well before payment succeeds or the order actually saves. If the customer abandons checkout, the payment gateway declines, or the order-place transaction rolls back, that reserved id is never attached to a real order row, and the sequence never reuses it. The number is permanently skipped by design, not deleted or corrupted.
How do I confirm a gap is a genuine reserved-but-unused order id?
Pull quotes with a non null reserved_order_id and is_active set to 0 from GET /rest/V1/carts/search, then for each reserved_order_id check GET /rest/V1/orders filtered by increment_id equal to that value. An empty items array confirms no order ever consumed that number, so it is a genuine orphaned gap rather than a missing or renamed order.
Can I safely renumber or reuse a skipped Magento order id?
No. Re-pointing the sales_sequence next value forward risks a future collision, and reusing a reserved increment_id is unsupported. The safe action is a dry run guarded reconciliation report, and only when explicitly confirmed, marking the originating quote inactive or noting it so future scans skip it, never a call that mutates the sequence or order numbering itself.
Related field notes
Citations
On the problem:
- Magento Forums: at which moment is a order_id created. community.magento.com at-which-moment-is-a-order-id-created
- Magento Forums: order id not progressive. community.magento.com order-id-not-progressive
- GitHub Issue: PayPal some orders fail with main.CRITICAL, wrong order id (increment id gap). github.com/magento/magento2/issues/15427
On the solution:
- Adobe Commerce: search using REST endpoints. developer.adobe.com/commerce/webapi/rest/use-rest/performing-searches
- Adobe Commerce: REST API reference. developer.adobe.com/commerce/webapi/rest/reference
- Adobe Commerce: order processing tutorial. developer.adobe.com/commerce/webapi/rest/tutorials/orders
Stuck on a tricky one?
If you have a problem in Magento 2 or Adobe Commerce orders, catalog data, cron, or inventory 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 settle a "missing order" mystery?
If this saved you a confusing finance ticket or a wrong assumption about data loss, 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