Field notes / Number Ranges and Documents

Diagnostic Number Ranges and Documents

Number range hitting its maximum value stalls generation

An order number range has been quietly counting up for years, and one day new orders start failing with a raw database error instead of a normal Shopware message. Nothing in the Admin warned anyone this was coming. Here is why a number range's counter can outgrow its own pattern or its own database column, and a small script that tells you exactly how much headroom every range has left before it happens to you.

Python and Node.js Shopware Admin API Read only by default

The short answer

Shopware keeps each number range's counter as lastValue in the number_range_state table, and a merchant can set any start value on the parent number-range entity with no check against the pattern's numeric capacity or the column's own ceiling. NumberRangeValueGenerator just keeps incrementing lastValue, and once it passes a zero-padded pattern's maximum, such as 999999 for {n,6}, or the database integer column's limit, the raw SQL increment throws SQLSTATE[22003] Numeric value out of range and order or document creation fails with a 500. This is a known, still-open gap in Shopware (issue #12002). List every number range and its live lastValue, compute how much headroom is left against the pattern's padding and the column max, and flag anything getting close, before it becomes an outage. Full code and tests below.

The problem in plain words

Every number range in Shopware, whether it names orders, invoices, credit notes, or customers, has a pattern like order{n,6} and a counter that starts at some start value and only ever goes up. That counter is stored separately from the range's configuration, in a row of its own in number_range_state, under the field lastValue. Every time Shopware needs a new number, NumberRangeValueGenerator reads that value, increments it, and writes it back.

Nothing in that path checks whether the new value still fits. If the pattern zero-pads to six digits, the practical ceiling is 999999, no matter how large the underlying database column is. If the pattern is unpadded, the real ceiling is whatever the column can hold, an INT tops out at 2147483647 and a BIGINT far higher, but Shopware never validates the configured start or the pattern against either limit. The counter just climbs until one of them is crossed, and then the increment itself throws a database error instead of a friendly Shopware exception.

Nothing ties the configured start or pattern to the column's real capacity. lastValue climbs until it crosses one of the two ceilings, and the increment itself fails.

Why it happens

The parent number-range entity lets a merchant set any start value with no validation against the pattern's numeric capacity or the underlying database column's real integer ceiling.
The live counter lives separately, as lastValue in number_range_state, and NumberRangeValueGenerator simply increments it on every new order, invoice, credit note, or customer number, with no upper-bound check anywhere in that path.
A zero-padded pattern like {n,6} caps the practical maximum at 999999 long before the database column itself would ever complain, so a range can fail even though its raw column has plenty of room left.
An unpadded pattern like {n} has no such soft ceiling, so it grows until it hits the column's actual limit, 2147483647 for an INT or a much larger figure for a BIGINT.
When the increment finally crosses either ceiling, the failure surfaces as a raw SQLSTATE[22003] Numeric value out of range from the database driver, not a catchable Shopware domain exception, so it reaches the customer or the admin as a plain 500.
This is documented and still unresolved in Shopware's own tracker as issue #12002, which notes the team has not yet decided between throwing a proper exception, warning admins in the UI, or removing user-settable start values entirely.

The key insight

There are two different ceilings, and a range can hit either one first. The pattern's zero-pad width sets a soft, cosmetic ceiling, and the database column sets a hard, physical one. A six-digit padded order number range can run out at 999999 while its INT column could still hold billions more. Checking only the database schema misses that, and checking only the pattern misses ranges that are unpadded and headed for the column limit instead. You have to compute headroom against both and report whichever is smaller.

The fix, as a flow

We do not touch lastValue or start. We authenticate to the Admin API, list every number-range record and its matching number-range-state row (they share the same id), work out the effective ceiling for each one from its pattern and its column type, and report anything running low on headroom. A preview-pattern call cross-checks what the very next number would actually render as, without writing anything.

Nothing is written to lastValue or start. The script only ever reads, computes headroom, and reports the ranges that need a human decision.

Build it step by step

Get Admin API credentials

Create an Integration under Settings, System, Integrations in the Shopware Admin, and copy its access key id and secret access key. Keep the shop URL and both credentials in environment variables. This script only reads by default, but DRY_RUN still gates the one optional write it supports, widening a pattern's zero-pad width.

setup (shell)

pip install requests

export SHOPWARE_URL="https://your-shop.example.com"
export SHOPWARE_CLIENT_ID="SWIAxxxxxxxxxxxxxxxxxxxxxxxx"
export SHOPWARE_CLIENT_SECRET="your-secret-access-key"
export DRY_RUN="true"   # start safe, change to false only to widen a pattern's padding

setup (shell)

// Node 18+ has fetch built in, no dependencies needed

export SHOPWARE_URL="https://your-shop.example.com"
export SHOPWARE_CLIENT_ID="SWIAxxxxxxxxxxxxxxxxxxxxxxxx"
export SHOPWARE_CLIENT_SECRET="your-secret-access-key"
export DRY_RUN="true"   // start safe, change to false only to widen a pattern's padding

Authenticate and build a small API helper

Exchange the client id and secret for a bearer token at POST /api/oauth/token, then send that token as Authorization: Bearer <token> with Accept: application/json on every following call. A tiny helper wraps the token fetch and a generic authorized request.

step2.py

import os, requests

SHOPWARE_URL = os.environ["SHOPWARE_URL"].rstrip("/")
CLIENT_ID = os.environ["SHOPWARE_CLIENT_ID"]
CLIENT_SECRET = os.environ["SHOPWARE_CLIENT_SECRET"]

def get_access_token():
    r = requests.post(
        f"{SHOPWARE_URL}/api/oauth/token",
        json={
            "grant_type": "client_credentials",
            "client_id": CLIENT_ID,
            "client_secret": CLIENT_SECRET,
        },
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["access_token"]

def api(method, path, token, json_body=None):
    r = requests.request(
        method, f"{SHOPWARE_URL}{path}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json"},
        json=json_body, timeout=30,
    )
    r.raise_for_status()
    return r.json() if r.content else None

step2.js

const SHOPWARE_URL = (process.env.SHOPWARE_URL || "").replace(/\/$/, "");
const CLIENT_ID = process.env.SHOPWARE_CLIENT_ID;
const CLIENT_SECRET = process.env.SHOPWARE_CLIENT_SECRET;

async function getAccessToken() {
  const res = await fetch(`${SHOPWARE_URL}/api/oauth/token`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      grant_type: "client_credentials",
      client_id: CLIENT_ID,
      client_secret: CLIENT_SECRET,
    }),
  });
  if (!res.ok) throw new Error(`Shopware auth ${res.status}`);
  const body = await res.json();
  return body.access_token;
}

async function api(method, path, token, jsonBody) {
  const res = await fetch(`${SHOPWARE_URL}${path}`, {
    method,
    headers: {
      Authorization: `Bearer ${token}`,
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: jsonBody ? JSON.stringify(jsonBody) : undefined,
  });
  if (!res.ok) throw new Error(`Shopware ${method} ${path} ${res.status}`);
  return res.status === 204 ? null : res.json();
}

List every number range and its live counter

Search number-range for id, type, pattern, start, and global. Since number-range-state shares the same id as its parent range, look up each range's lastValue with a simple equals filter on id.

step3.py

def fetch_number_ranges(token):
    body = {
        "page": 1,
        "limit": 500,
        "associations": {},
        "sort": [{"field": "name", "order": "ASC"}],
        "total-count-mode": 1,
    }
    data = api("POST", "/api/search/number-range", token, body)
    return [
        {
            "id": row["id"],
            "name": row.get("name"),
            "type": (row.get("type") or {}).get("technicalName") if isinstance(row.get("type"), dict) else row.get("type"),
            "pattern": row["pattern"],
            "start": row["start"],
            "global": row.get("global", False),
        }
        for row in data["data"]
    ]


def fetch_last_value(token, number_range_id):
    body = {
        "page": 1,
        "limit": 1,
        "filter": [{"type": "equals", "field": "id", "value": number_range_id}],
    }
    data = api("POST", "/api/search/number-range-state", token, body)
    rows = data["data"]
    return rows[0]["lastValue"] if rows else None

step3.js

async function fetchNumberRanges(token) {
  const body = {
    page: 1,
    limit: 500,
    associations: {},
    sort: [{ field: "name", order: "ASC" }],
    "total-count-mode": 1,
  };
  const data = await api("POST", "/api/search/number-range", token, body);
  return data.data.map((row) => ({
    id: row.id,
    name: row.name,
    type: typeof row.type === "object" && row.type ? row.type.technicalName : row.type,
    pattern: row.pattern,
    start: row.start,
    global: row.global || false,
  }));
}

async function fetchLastValue(token, numberRangeId) {
  const body = {
    page: 1,
    limit: 1,
    filter: [{ type: "equals", field: "id", value: numberRangeId }],
  };
  const data = await api("POST", "/api/search/number-range-state", token, body);
  return data.data.length ? data.data[0].lastValue : null;
}

Decide, with one pure function

The decision lives in a pure function that takes a pattern string, the live lastValue, and the database column's own max, and returns a status of ok, warn, or critical. It parses the {n} placeholder out of the pattern, so a zero-padded pattern like {n,6} gets an effective ceiling of 999999 even though the column could hold far more, and an unpadded pattern falls back to the column max. Being pure, no I/O and no API calls, means it is trivial to unit test with plain fixtures.

decide.py

import re

PLACEHOLDER = re.compile(r"\{n(?:,(\d+))?\}")

def evaluate_number_range_headroom(pattern, last_value, column_max_value, safety_margin_percent=0.05):
    match = PLACEHOLDER.search(pattern or "")
    if match and match.group(1):
        width = int(match.group(1))
        effective_ceiling = (10 ** width) - 1
    else:
        effective_ceiling = column_max_value

    remaining = effective_ceiling - last_value
    remaining_percent = (remaining / effective_ceiling) if effective_ceiling else 0

    if remaining_percent <= safety_margin_percent or remaining <= 0:
        status = "critical"
    elif remaining_percent <= safety_margin_percent * 4:
        status = "warn"
    else:
        status = "ok"

    return {
        "status": status,
        "effectiveCeiling": effective_ceiling,
        "remaining": remaining,
        "remainingPercent": remaining_percent,
    }

decide.js

const PLACEHOLDER = /\{n(?:,(\d+))?\}/;

export function evaluateNumberRangeHeadroom(pattern, lastValue, columnMaxValue, safetyMarginPercent = 0.05) {
  const match = PLACEHOLDER.exec(pattern || "");
  let effectiveCeiling;
  if (match && match[1]) {
    const width = parseInt(match[1], 10);
    effectiveCeiling = Math.pow(10, width) - 1;
  } else {
    effectiveCeiling = columnMaxValue;
  }

  const remaining = effectiveCeiling - lastValue;
  const remainingPercent = effectiveCeiling ? remaining / effectiveCeiling : 0;

  let status;
  if (remainingPercent <= safetyMarginPercent || remaining <= 0) {
    status = "critical";
  } else if (remainingPercent <= safetyMarginPercent * 4) {
    status = "warn";
  } else {
    status = "ok";
  }

  return { status, effectiveCeiling, remaining, remainingPercent };
}

Cross-check with a non-mutating preview

Before trusting the math, call POST /api/_action/number-range/{numberRangeId}/preview-pattern to see exactly what Shopware would render as the next number, without incrementing lastValue. If the preview already looks wrong, truncated, or repeating digits, that confirms the range is at or past its practical ceiling.

apply.py

def preview_next_number(token, number_range_id, pattern, start):
    body = {"pattern": pattern, "start": start}
    result = api("POST", f"/api/_action/number-range/{number_range_id}/preview-pattern", token, body)
    return result.get("number") if result else None


def widen_pattern_padding(token, number_range_id, new_pattern):
    # Non-destructive: only ever raises the zero-pad width, e.g. '{n,6}' -> '{n,8}'.
    # Never touches lastValue or start. Requires DRY_RUN=false and human approval.
    api("PATCH", f"/api/number-range/{number_range_id}", token, {"pattern": new_pattern})

apply.js

async function previewNextNumber(token, numberRangeId, pattern, start) {
  const body = { pattern, start };
  const result = await api("POST", `/api/_action/number-range/${numberRangeId}/preview-pattern`, token, body);
  return result ? result.number : null;
}

async function widenPatternPadding(token, numberRangeId, newPattern) {
  // Non-destructive: only ever raises the zero-pad width, e.g. '{n,6}' -> '{n,8}'.
  // Never touches lastValue or start. Requires DRY_RUN=false and human approval.
  await api("PATCH", `/api/number-range/${numberRangeId}`, token, { pattern: newPattern });
}

Wire it together as a read only report

Loop over every number range, fetch its lastValue, run evaluate_number_range_headroom, and log a report row for anything warn or critical. Never PATCH lastValue or start, and never reset number-range-state. The only optional write, widening a pattern's zero-pad width, is skipped entirely unless DRY_RUN=false is explicitly set, and even then it only ever increases capacity, it never renumbers anything already issued.

Run it safe

This script reports, it does not repair. Renumbering or resetting a number range's lastValue or start is unsafe to automate, because order, invoice, and credit note numbers must stay unique and sequential for audit and tax compliance, and a wrong write could collide with a number already issued. Keep DRY_RUN=true and only flip it for the one safe write this script supports, widening a pattern's padding, after a human has reviewed the report.

The full code

Here is the complete script in one file for each language. It reads every number range and its live counter, computes headroom with the pure function, logs a report for anything close to its ceiling, and never writes to lastValue or start.

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

number_range_max_value_overflow.py

"""Flag Shopware 6 number ranges running low on headroom before generation fails.

Shopware stores each number range's counter as lastValue in the
number_range_state table, and a merchant can set any start value on the
parent number-range entity with no check against the pattern's numeric
capacity or the database column's own ceiling. NumberRangeValueGenerator
just keeps incrementing lastValue, and once it crosses a zero-padded
pattern's width, e.g. 999999 for '{n,6}', or the column's own max, the raw
SQL increment throws SQLSTATE[22003] Numeric value out of range and order
or document creation fails with a 500 (shopware/shopware#12002).

This script only reads number-range and number-range-state, computes
headroom, and reports. It never writes lastValue or start. The only
optional write is widening a pattern's zero-pad width, which strictly
increases capacity and is gated behind DRY_RUN and human approval.
Run on a schedule. Safe to run again and again.
"""
import os
import re
import logging
import requests

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

SHOPWARE_URL = os.environ.get("SHOPWARE_URL", "https://demo.example.com").rstrip("/")
CLIENT_ID = os.environ.get("SHOPWARE_CLIENT_ID", "SWIAdummy")
CLIENT_SECRET = os.environ.get("SHOPWARE_CLIENT_SECRET", "secret_dummy")
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
SAFETY_MARGIN_PERCENT = float(os.environ.get("SAFETY_MARGIN_PERCENT", "0.05"))

# Database column ceilings, used only when a pattern has no zero-pad width.
INT_COLUMN_MAX = 2147483647
BIGINT_COLUMN_MAX = 9223372036854775807
DEFAULT_COLUMN_MAX_VALUE = int(os.environ.get("NUMBER_RANGE_COLUMN_MAX", str(INT_COLUMN_MAX)))

PLACEHOLDER = re.compile(r"\{n(?:,(\d+))?\}")


def evaluate_number_range_headroom(pattern, last_value, column_max_value, safety_margin_percent=0.05):
    match = PLACEHOLDER.search(pattern or "")
    if match and match.group(1):
        width = int(match.group(1))
        effective_ceiling = (10 ** width) - 1
    else:
        effective_ceiling = column_max_value

    remaining = effective_ceiling - last_value
    remaining_percent = (remaining / effective_ceiling) if effective_ceiling else 0

    if remaining_percent <= safety_margin_percent or remaining <= 0:
        status = "critical"
    elif remaining_percent <= safety_margin_percent * 4:
        status = "warn"
    else:
        status = "ok"

    return {
        "status": status,
        "effectiveCeiling": effective_ceiling,
        "remaining": remaining,
        "remainingPercent": remaining_percent,
    }


def get_access_token():
    r = requests.post(
        f"{SHOPWARE_URL}/api/oauth/token",
        json={
            "grant_type": "client_credentials",
            "client_id": CLIENT_ID,
            "client_secret": CLIENT_SECRET,
        },
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["access_token"]


def api(method, path, token, json_body=None):
    r = requests.request(
        method, f"{SHOPWARE_URL}{path}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json"},
        json=json_body, timeout=30,
    )
    r.raise_for_status()
    return r.json() if r.content else None


def fetch_number_ranges(token):
    body = {
        "page": 1,
        "limit": 500,
        "associations": {},
        "sort": [{"field": "name", "order": "ASC"}],
        "total-count-mode": 1,
    }
    data = api("POST", "/api/search/number-range", token, body)
    return [
        {
            "id": row["id"],
            "name": row.get("name"),
            "type": (row.get("type") or {}).get("technicalName") if isinstance(row.get("type"), dict) else row.get("type"),
            "pattern": row["pattern"],
            "start": row["start"],
            "global": row.get("global", False),
        }
        for row in data["data"]
    ]


def fetch_last_value(token, number_range_id):
    body = {
        "page": 1,
        "limit": 1,
        "filter": [{"type": "equals", "field": "id", "value": number_range_id}],
    }
    data = api("POST", "/api/search/number-range-state", token, body)
    rows = data["data"]
    return rows[0]["lastValue"] if rows else None


def preview_next_number(token, number_range_id, pattern, start):
    body = {"pattern": pattern, "start": start}
    result = api("POST", f"/api/_action/number-range/{number_range_id}/preview-pattern", token, body)
    return result.get("number") if result else None


def widen_pattern_padding(token, number_range_id, new_pattern):
    # Non-destructive: only ever raises the zero-pad width, e.g. '{n,6}' -> '{n,8}'.
    # Never touches lastValue or start. Requires DRY_RUN=false and human approval.
    api("PATCH", f"/api/number-range/{number_range_id}", token, {"pattern": new_pattern})


def run():
    token = get_access_token()
    ranges = fetch_number_ranges(token)

    flagged = 0
    for number_range in ranges:
        last_value = fetch_last_value(token, number_range["id"])
        if last_value is None:
            continue

        result = evaluate_number_range_headroom(
            number_range["pattern"], last_value, DEFAULT_COLUMN_MAX_VALUE, SAFETY_MARGIN_PERCENT
        )
        if result["status"] == "ok":
            continue

        flagged += 1
        log.warning(
            "Number range %s (%s) pattern=%s lastValue=%s status=%s effectiveCeiling=%s remaining=%s (%.1f%% left)",
            number_range["name"], number_range["type"], number_range["pattern"], last_value,
            result["status"], result["effectiveCeiling"], result["remaining"], result["remainingPercent"] * 100,
        )

        # Read only report entry. No write happens here unless a human explicitly
        # runs widen_pattern_padding() with DRY_RUN=false after reviewing this line.
        if not DRY_RUN:
            log.info("DRY_RUN is false, but widening a pattern requires an explicit new pattern per range; "
                      "this script still will not guess one automatically for %s.", number_range["name"])

    log.info("Done. %d number range(s) need attention.", flagged)


if __name__ == "__main__":
    run()

number-range-max-value-overflow.js

/**
 * Flag Shopware 6 number ranges running low on headroom before generation fails.
 *
 * Shopware stores each number range's counter as lastValue in the
 * number_range_state table, and a merchant can set any start value on the
 * parent number-range entity with no check against the pattern's numeric
 * capacity or the database column's own ceiling. NumberRangeValueGenerator
 * just keeps incrementing lastValue, and once it crosses a zero-padded
 * pattern's width, e.g. 999999 for '{n,6}', or the column's own max, the raw
 * SQL increment throws SQLSTATE[22003] Numeric value out of range and order
 * or document creation fails with a 500 (shopware/shopware#12002).
 *
 * This script only reads number-range and number-range-state, computes
 * headroom, and reports. It never writes lastValue or start. The only
 * optional write is widening a pattern's zero-pad width, which strictly
 * increases capacity and is gated behind DRY_RUN and human approval.
 * Run on a schedule. Safe to run again and again.
 *
 * Guide: https://www.allanninal.dev/shopware/number-range-max-value-overflow/
 */
import { pathToFileURL } from "node:url";

const SHOPWARE_URL = (process.env.SHOPWARE_URL || "https://demo.example.com").replace(/\/$/, "");
const CLIENT_ID = process.env.SHOPWARE_CLIENT_ID || "SWIAdummy";
const CLIENT_SECRET = process.env.SHOPWARE_CLIENT_SECRET || "secret_dummy";
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const SAFETY_MARGIN_PERCENT = Number(process.env.SAFETY_MARGIN_PERCENT || 0.05);

// Database column ceilings, used only when a pattern has no zero-pad width.
const INT_COLUMN_MAX = 2147483647;
const BIGINT_COLUMN_MAX = 9223372036854775807;
const DEFAULT_COLUMN_MAX_VALUE = Number(process.env.NUMBER_RANGE_COLUMN_MAX || INT_COLUMN_MAX);

const PLACEHOLDER = /\{n(?:,(\d+))?\}/;

export function evaluateNumberRangeHeadroom(pattern, lastValue, columnMaxValue, safetyMarginPercent = 0.05) {
  const match = PLACEHOLDER.exec(pattern || "");
  let effectiveCeiling;
  if (match && match[1]) {
    const width = parseInt(match[1], 10);
    effectiveCeiling = Math.pow(10, width) - 1;
  } else {
    effectiveCeiling = columnMaxValue;
  }

  const remaining = effectiveCeiling - lastValue;
  const remainingPercent = effectiveCeiling ? remaining / effectiveCeiling : 0;

  let status;
  if (remainingPercent <= safetyMarginPercent || remaining <= 0) {
    status = "critical";
  } else if (remainingPercent <= safetyMarginPercent * 4) {
    status = "warn";
  } else {
    status = "ok";
  }

  return { status, effectiveCeiling, remaining, remainingPercent };
}

async function getAccessToken() {
  const res = await fetch(`${SHOPWARE_URL}/api/oauth/token`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      grant_type: "client_credentials",
      client_id: CLIENT_ID,
      client_secret: CLIENT_SECRET,
    }),
  });
  if (!res.ok) throw new Error(`Shopware auth ${res.status}`);
  const body = await res.json();
  return body.access_token;
}

async function api(method, path, token, jsonBody) {
  const res = await fetch(`${SHOPWARE_URL}${path}`, {
    method,
    headers: {
      Authorization: `Bearer ${token}`,
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: jsonBody ? JSON.stringify(jsonBody) : undefined,
  });
  if (!res.ok) throw new Error(`Shopware ${method} ${path} ${res.status}`);
  return res.status === 204 ? null : res.json();
}

async function fetchNumberRanges(token) {
  const body = {
    page: 1,
    limit: 500,
    associations: {},
    sort: [{ field: "name", order: "ASC" }],
    "total-count-mode": 1,
  };
  const data = await api("POST", "/api/search/number-range", token, body);
  return data.data.map((row) => ({
    id: row.id,
    name: row.name,
    type: typeof row.type === "object" && row.type ? row.type.technicalName : row.type,
    pattern: row.pattern,
    start: row.start,
    global: row.global || false,
  }));
}

async function fetchLastValue(token, numberRangeId) {
  const body = {
    page: 1,
    limit: 1,
    filter: [{ type: "equals", field: "id", value: numberRangeId }],
  };
  const data = await api("POST", "/api/search/number-range-state", token, body);
  return data.data.length ? data.data[0].lastValue : null;
}

async function previewNextNumber(token, numberRangeId, pattern, start) {
  const body = { pattern, start };
  const result = await api("POST", `/api/_action/number-range/${numberRangeId}/preview-pattern`, token, body);
  return result ? result.number : null;
}

async function widenPatternPadding(token, numberRangeId, newPattern) {
  // Non-destructive: only ever raises the zero-pad width, e.g. '{n,6}' -> '{n,8}'.
  // Never touches lastValue or start. Requires DRY_RUN=false and human approval.
  await api("PATCH", `/api/number-range/${numberRangeId}`, token, { pattern: newPattern });
}

export async function run() {
  const token = await getAccessToken();
  const ranges = await fetchNumberRanges(token);

  let flagged = 0;
  for (const numberRange of ranges) {
    const lastValue = await fetchLastValue(token, numberRange.id);
    if (lastValue === null) continue;

    const result = evaluateNumberRangeHeadroom(
      numberRange.pattern, lastValue, DEFAULT_COLUMN_MAX_VALUE, SAFETY_MARGIN_PERCENT
    );
    if (result.status === "ok") continue;

    flagged++;
    console.warn(
      `Number range ${numberRange.name} (${numberRange.type}) pattern=${numberRange.pattern} lastValue=${lastValue} ` +
      `status=${result.status} effectiveCeiling=${result.effectiveCeiling} remaining=${result.remaining} ` +
      `(${(result.remainingPercent * 100).toFixed(1)}% left)`
    );

    // Read only report entry. No write happens here unless a human explicitly
    // runs widenPatternPadding() with DRY_RUN=false after reviewing this line.
    if (!DRY_RUN) {
      console.log(
        `DRY_RUN is false, but widening a pattern requires an explicit new pattern per range; ` +
        `this script still will not guess one automatically for ${numberRange.name}.`
      );
    }
  }

  console.log(`Done. ${flagged} number range(s) need attention.`);
}

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

Add a test

evaluate_number_range_headroom is the part most worth testing, because it decides which ranges show up in the report. It takes a pattern string, a plain integer, and a column max, with no network call inside it, so the test needs no Shopware instance at all.

test_headroom_evaluation.py

from number_range_max_value_overflow import evaluate_number_range_headroom

INT_MAX = 2147483647


def test_zero_padded_pattern_uses_pad_width_ceiling():
    result = evaluate_number_range_headroom("order{n,6}", 500000, INT_MAX)
    assert result["effectiveCeiling"] == 999999
    assert result["status"] == "ok"


def test_zero_padded_pattern_critical_near_ceiling():
    result = evaluate_number_range_headroom("order{n,6}", 999000, INT_MAX)
    assert result["status"] == "critical"
    assert result["remaining"] == 999


def test_unpadded_pattern_falls_back_to_column_max():
    result = evaluate_number_range_headroom("order{n}", 1000, INT_MAX)
    assert result["effectiveCeiling"] == INT_MAX
    assert result["status"] == "ok"


def test_unpadded_pattern_critical_near_column_max():
    result = evaluate_number_range_headroom("order{n}", INT_MAX - 1, INT_MAX)
    assert result["status"] == "critical"


def test_exactly_at_ceiling_is_critical():
    result = evaluate_number_range_headroom("order{n,6}", 999999, INT_MAX)
    assert result["remaining"] == 0
    assert result["status"] == "critical"


def test_one_below_ceiling_is_critical_within_default_margin():
    # 999998 leaves 1 remaining out of 999999, well under the 5% safety margin
    result = evaluate_number_range_headroom("order{n,6}", 999998, INT_MAX)
    assert result["status"] == "critical"


def test_comfortable_headroom_is_ok():
    result = evaluate_number_range_headroom("order{n,6}", 10000, INT_MAX)
    assert result["status"] == "ok"


def test_missing_placeholder_falls_back_to_column_max():
    result = evaluate_number_range_headroom("no-placeholder-here", 1000, INT_MAX)
    assert result["effectiveCeiling"] == INT_MAX


def test_custom_safety_margin_widens_warn_band():
    result = evaluate_number_range_headroom("order{n,6}", 900000, INT_MAX, safety_margin_percent=0.2)
    assert result["status"] in ("warn", "critical")

headroom.test.js

import { test } from "node:test";
import assert from "node:assert/strict";
import { evaluateNumberRangeHeadroom } from "./number-range-max-value-overflow.js";

const INT_MAX = 2147483647;

test("zero padded pattern uses pad width ceiling", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 500000, INT_MAX);
  assert.equal(result.effectiveCeiling, 999999);
  assert.equal(result.status, "ok");
});

test("zero padded pattern critical near ceiling", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 999000, INT_MAX);
  assert.equal(result.status, "critical");
  assert.equal(result.remaining, 999);
});

test("unpadded pattern falls back to column max", () => {
  const result = evaluateNumberRangeHeadroom("order{n}", 1000, INT_MAX);
  assert.equal(result.effectiveCeiling, INT_MAX);
  assert.equal(result.status, "ok");
});

test("unpadded pattern critical near column max", () => {
  const result = evaluateNumberRangeHeadroom("order{n}", INT_MAX - 1, INT_MAX);
  assert.equal(result.status, "critical");
});

test("exactly at ceiling is critical", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 999999, INT_MAX);
  assert.equal(result.remaining, 0);
  assert.equal(result.status, "critical");
});

test("one below ceiling is critical within default margin", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 999998, INT_MAX);
  assert.equal(result.status, "critical");
});

test("comfortable headroom is ok", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 10000, INT_MAX);
  assert.equal(result.status, "ok");
});

test("missing placeholder falls back to column max", () => {
  const result = evaluateNumberRangeHeadroom("no-placeholder-here", 1000, INT_MAX);
  assert.equal(result.effectiveCeiling, INT_MAX);
});

test("custom safety margin widens warn band", () => {
  const result = evaluateNumberRangeHeadroom("order{n,6}", 900000, INT_MAX, 0.2);
  assert.ok(result.status === "warn" || result.status === "critical");
});

Case studies

Legacy order range

Six years of orders on a six-digit pattern

A store had been running on the same order{n,6} pattern since launch, comfortably under six digits for years. Growth accelerated, and nobody had a reason to look at number_range_state until checkout started throwing 500s for a handful of customers a day, always on the same order type.

Running the report showed the order range at lastValue 998,400 against an effective ceiling of 999,999, deep in the critical band. The team could not safely renumber years of legal order numbers, so they raised the pattern to {n,8}, a pure widening that touched nothing already issued, and the 500s stopped the same day.

Invoice range

An invoice range close to its ceiling nobody had checked

An agency managing several Shopware installs assumed the database column was the only limit that mattered, and never looked at pattern padding. One client's invoice range used {n,5}, a five-digit pad, while comfortably under the column's own INT ceiling.

The headroom script flagged it as critical anyway, since the pattern's practical ceiling of 99,999 was the real constraint, not the column. Because the check computes headroom against both the pattern and the column and reports whichever is smaller, the agency caught it and widened the pattern weeks before it would have failed silently during month end invoicing.

What good looks like

After this runs on a schedule, every number range's headroom is visible before it becomes an outage, not after. The report tells you exactly which range, its current lastValue, its effective ceiling from whichever constraint binds first, and how much room is left. Nothing gets renumbered or reset automatically, since that stays a human decision tied to audit and tax rules, but widening a pattern's padding is a safe, reviewed, one line fix that buys years of headroom without touching a single number already issued.

FAQ

Why does order or document creation fail once a Shopware number range gets old enough?

Shopware stores each number range's counter as lastValue in the number_range_state table and lets merchants set an arbitrary start value on the parent number-range entity, but it never validates that start or the pattern against the column's real numeric capacity. NumberRangeValueGenerator keeps incrementing lastValue until it passes the pattern's zero-padded width, such as 999999 for {n,6}, or the database column's own ceiling, and the raw SQL increment then throws SQLSTATE[22003] Numeric value out of range instead of a catchable domain exception, so the order or document creation request fails with a 500.

Is it safe to fix a number range that is close to its maximum with a script?

Not by resetting or rewriting lastValue or start. Order, invoice, and credit note numbers have to stay unique and sequential for audit and tax reasons, so an automated PATCH to the counter risks colliding with numbers already issued. The safe automated action is read only reporting, and the only write worth allowing under a human-approved flag is widening the pattern's zero-pad width, for example from {n,6} to {n,8}, which only raises the ceiling and never touches a number that was already generated.

What is the difference between a number range's pattern and its lastValue?

pattern is the template on the number-range entity, such as order{n,6}, and its {n} placeholder controls both how the number is formatted and, when zero-padded, its practical maximum. lastValue lives in the separate number_range_state table and is the actual counter NumberRangeValueGenerator increments on every new number. A range can be far below its database column's limit and still fail, because a zero-padded pattern caps the usable range well below what the column could otherwise hold.

Related field notes

Citations

On the problem:

Shopware GitHub Issue #12002: Handling maximum value of number ranges. github.com/shopware/shopware/issues/12002
Shopware 6 Documentation: Settings, Number ranges. docs.shopware.com/en/shopware-6-en/settings/Numberranges
Shopware changelog: use number range start number from settings if it is higher than the current number range state. github.com/shopware/shopware changelog release-6-4-2-0

On the solution:

Shopware Developer Documentation: Number Ranges, hosting and performance guide. developer.shopware.com/docs/guides/hosting/performance/number-ranges.html
Shopware Admin API Reference: the NumberRange entity. shopware.stoplight.io/docs/admin-api/a9621db91f093-number-range
Shopware Admin API Reference: the NumberRangeState entity. shopware.stoplight.io/docs/admin-api/c2NoOjE0MzUxMjcw-number-range-state

Stuck on a tricky one?

If you have a problem in Shopware order states, stock, the message queue, or data integrity 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 save your number ranges?

If this saved you from a surprise 500 on checkout day, 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 Shopware field notes