Machine‑readable SKU & carton schema + validation, ATP math and carrier‑mapping rules for bulky furniture

Most furniture retailers discover their SKU data is broken the hard way: a customer orders a 3‑piece sectional, the system shows "12 available," and then the warehouse finds 12 left‑arm chaises, 4 right‑arm pieces, and 9 armless middles. The parent product says in stock. The actual set is not. That gap between "we have cartons" and "we can ship a complete sofa" is where availability lies get born.

If you've already read the minimal SKU data spec for bulky furniture, this is the companion that fills the part that spec left open on purpose: the actual machine‑readable schema, the validation rules, the math for computing parent availability from carton stock, and the carrier‑mapping thresholds. No philosophy here. This is the thing you hand to your engineer, your 3PL integration person, and your supplier's data team so nobody has to guess what weight_kg means or whether dims is in centimeters.

Everything below targets the bulky furniture SKU JSON schema problem specifically — multi‑carton products where the sellable unit and the shipped unit are not the same thing.

The core data model: product vs carton

The single most important decision is separating the product (what the customer buys) from the carton (what physically moves). A sectional is one product. It ships as three cartons. A dining set is one product that might ship as five cartons across two pallets. Flatten these together in your schema and you will never compute availability correctly.

1. 1
   A product record describes the sellable thing, its components, and how many of each carton make a complete set.
2. 2
   A carton record describes one physical box

   dimensions, weight, barcode, and which product(s) it belongs to.

Inventory is counted at the carton level. Availability is computed at the product level. That one rule prevents most false‑availability problems in this category.

The JSON Schema (Draft‑07)

Here's the product schema. It's deliberately strict — additionalProperties: false so nobody quietly injects undocumented fields that break downstream parsers.

{ "$schema": "http://json-schema.org/draft-07/schema#", "$id": "https://example.com/schemas/furniture-product.json", "title": "FurnitureProduct", "type": "object", "additionalProperties": false, "required": ["productid", "sku", "title", "category", "components", "status"], "properties": { "productid": { "type": "string", "pattern": "^PRD-[0-9]{6}$", "description": "Internal product identifier" }, "sku": { "type": "string", "pattern": "^[A-Z0-9]{3,6}-[A-Z0-9]{2,8}$", "description": "Customer-facing SKU, e.g. SOFA-SECT3L" }, "title": { "type": "string", "minLength": 3, "maxLength": 120 }, "category": { "type": "string", "enum": ["sofa", "sectional", "diningset", "bed", "wardrobe", "table", "chair", "storage", "other"] }, "status": { "type": "string", "enum": ["active", "discontinued", "preorder", "hold"] }, "assemblyrequired": { "type": "boolean" }, "components": { "type": "array", "minItems": 1, "items": { "type": "object", "additionalProperties": false, "required": ["cartonsku", "qtyperset"], "properties": { "cartonsku": { "type": "string", "pattern": "^CTN-[A-Z0-9]{4,10}$" }, "qtyperset": { "type": "integer", "minimum": 1, "maximum": 20 }, "role": { "type": "string", "enum": ["primary", "accessory", "hardware", "optional"] } } } }, "deliveryserviceprofile": { "type": "string", "enum": ["parcel", "ltlcurbside", "ltlthreshold", "twopersonroomofchoice", "whiteglove"] }, "leadtime_days": { "type": "integer", "minimum": 0, "maximum": 365 } } }

And the carton schema:

{ "$schema": "http://json-schema.org/draft-07/schema#", "$id": "https://example.com/schemas/furniture-carton.json", "title": "FurnitureCarton", "type": "object", "additionalProperties": false, "required": ["cartonsku", "gtin", "dimscm", "weightkg"], "properties": { "cartonsku": { "type": "string", "pattern": "^CTN-[A-Z0-9]{4,10}$" }, "gtin": { "type": "string", "pattern": "^[0-9]{12,14}$", "description": "UPC/EAN/GTIN-14, numeric only" }, "dimscm": { "type": "object", "additionalProperties": false, "required": ["length", "width", "height"], "properties": { "length": { "type": "number", "minimum": 1, "maximum": 400 }, "width": { "type": "number", "minimum": 1, "maximum": 300 }, "height": { "type": "number", "minimum": 1, "maximum": 300 } } }, "weightkg": { "type": "number", "minimum": 0.1, "maximum": 150 }, "stackable": { "type": "boolean" }, "freight_class": { "type": "string", "enum": ["50", "55", "60", "65", "70", "77.5", "85", "92.5", "100", "110", "125", "150", "175", "200"] } } }

A note on why dimscm and weightkg carry units in the field name: integrations break when one supplier sends inches and another sends centimeters into a field just called length. Put the unit in the key. It's ugly. It's also unambiguous, and ugly‑but‑unambiguous wins every time in supplier data.

Example payloads

A 3‑carton sectional product record:

{ "productid": "PRD-004821", "sku": "SECT-3LCHS", "title": "Halden 3-Piece Left-Chaise Sectional", "category": "sectional", "status": "active", "assemblyrequired": true, "deliveryserviceprofile": "twopersonroomofchoice", "leadtimedays": 21, "components": [ { "cartonsku": "CTN-HAL3L01", "qtyperset": 1, "role": "primary" }, { "cartonsku": "CTN-HAL3L02", "qtyperset": 1, "role": "primary" }, { "cartonsku": "CTN-HAL3L03", "qtyper_set": 1, "role": "primary" } ] }

One of its cartons:

{ "cartonsku": "CTN-HAL3L01", "gtin": "0081234500011", "dimscm": { "length": 165, "width": 95, "height": 88 }, "weightkg": 41.5, "stackable": false, "freightclass": "150" }

Validation rules that actually catch bad data

Schema validation gets you type safety. It doesn't catch the stuff that causes false availability. These cross‑field and semantic rules do.

The oversize‑vs‑profile rule is the one most retailers skip and most regret. If a sectional carton gets tagged parcel by mistake, your site quotes something like $12 shipping, the customer pays, and now you're eating $180 in LTL freight on every one of those orders until someone catches it in a monthly reconciliation.

Computing parent ATP from carton inventory

Available‑to‑promise for a multi‑carton product is not the smallest carton count. It's the number of complete sets you can build, accounting for how many of each carton a set needs, minus what's reserved and what's genuinely sellable from in‑transit.

setsavailable = MIN over all primary components of: floor( (onhand[carton] - reserved[carton]) / qtyperset[carton] )

SELECT p.productid, MIN( FLOOR( (inv.onhand - inv.reserved) / c.qtyperset ) ) AS setsavailable FROM productcomponents c JOIN inventory inv ON inv.cartonsku = c.cartonsku JOIN products p ON p.productid = c.productid WHERE c.role = 'primary' GROUP BY p.product_id;

1. 1
   It only counts primary cartons. Accessory and hardware cartons are usually abundant and shouldn't gate set availability — but you track them separately so you can flag the rare case where you're actually out of leg hardware.
2. 2
   It subtracts reserved before dividing. Reserved stock is allocated to open orders. Counting it as available is the classic double‑sell.
3. 3
   FLOOR handles partial sets. If you have 7 of carton A and 5 of carton B, each needing 1 per set, you can build 5 sets. The 2 extra A cartons are real inventory but not sellable as complete product.

For in‑transit handling, don't blend it into on_hand. Keep it a separate column and expose it as a distinct ATP tier:

SELECT p.productid, MIN(FLOOR((inv.onhand - inv.reserved) / c.qtyperset)) AS setsnow, MIN(FLOOR((inv.onhand - inv.reserved + inv.intransit) / c.qtyperset)) AS setswithintransit FROM productcomponents c JOIN inventory inv ON inv.cartonsku = c.cartonsku JOIN products p ON p.productid = c.productid WHERE c.role = 'primary' GROUP BY p.product_id;

setsnow drives "ships this week." setswith_intransit drives "available for backorder with date." Mixing them is exactly how a customer gets a two‑day promise on a sofa that's still on a container.

The partial‑set trap

The most common real pattern: a supplier ships carton 1 and carton 2 of a sectional, but carton 3 is on a later production run. Receiving scans in two of three, inventory shows positive counts on both, and any logic that forgets to check all three components will happily mark the set available. The MIN across all primary components — including the one sitting at zero — is what keeps that set correctly at zero sellable.

Inventory queries for multi‑carton products should always follow this general sequence:

1. 1
   Pull on‑hand and reserved counts at the carton level.
2. 2
   Compute net available per carton (on_hand - reserved).
3. 3
   Divide each net count by that carton's qtyperset.
4. 4
   Take the MIN across all primary components.
5. 5
   FLOOR the result to whole sets only.
6. 6
   Separately compute setswithintransit using the same logic but adding in_transit before dividing.

Running these as two separate queries — not one blended number — is what gives your checkout a figure it can actually stand behind.

Visualize this as a workflow:

Keep the queries separate and label the ATP tiers clearly so UX and ops know which number is which.

Carrier / delivery_service_profile mapping thresholds

The deliveryserviceprofile enum isn't cosmetic. It decides which carrier type gets the order and what you quote at checkout. Map it from carton physical attributes using explicit thresholds so it's derivable, not hand‑typed.

Keep the thresholds in a config table, not hardcoded. Carriers change their oversize cutoffs, and you want to re‑derive profiles in a batch job rather than re‑edit code. When a carrier drops its parcel max from 150 cm to 120 cm, you change one row and re‑run the mapping.

Automated checks and anomaly thresholds

Validation runs at ingest. Anomaly detection runs continuously, because data rots after it's loaded — suppliers re‑pack, warehouses re‑measure, and someone always edits a record at 4pm on a Friday.

1. 1
   Negative sellable sets — any product where on_hand - reserved < 0 on a primary carton. Hard alert, immediate. This means you've oversold.
2. 2
   Dimension drift — carton dims changed by more than 10% from the last recorded value. Soft alert for review; usually a re‑measure or a supplier packing change.
3. 3
   Weight outliers — a carton whose weight_kg is more than 3× or less than 0.3× the median weight of cartons in its category. Catches the "41.5 entered as 415" class of error.
4. 4
   Orphaned cartons — carton records referenced by no product, or product components pointing at cartons that don't exist. Weekly digest.
5. 5
   Profile mismatch — any product where the stored deliveryserviceprofile disagrees with what the threshold mapping would derive. This is your single best signal that freight is being mis‑quoted.

A simple alert tiering keeps the noise manageable:

1. 1
   Hard (page someone)

   oversell, phantom carton on an active product with open orders.

2. 2
   Soft (queue for daily review)

   dimension drift, weight outlier, profile mismatch.

3. 3
   Digest (weekly)

   orphaned cartons, discontinued products still carrying stock.

Wiring everything to the same Slack channel is the mistake most teams make. Within a week people mute it, and the one oversell alert that actually mattered gets buried under forty dimension‑drift notices.

Onboarding ownership and SLAs

Schema and math only hold if someone owns the data at the point it enters. A tight ownership map, even for a small team:

The sign‑off that gets skipped most is the first‑receipt re‑verification of carton weight and dims. Suppliers send spec‑sheet numbers; actual packed cartons differ. One physical measurement on the first shipment prevents a year of wrong freight quotes.

Audit KPIs and cadence

Track these so you know the data is healthy, not just assume it:

1. 1
   Profile mismatch rate — % of active products where derived profile ≠ stored profile. Target under 2%. Review weekly.
2. 2
   Oversell incidents — count of orders placed against negative sellable sets. Target zero. Review on occurrence.
3. 3
   First‑receipt measurement compliance — % of new cartons physically verified within SLA. Target above 90%. Review monthly.
4. 4
   Orphan rate — orphaned cartons ÷ total cartons. Keep under 1%. Review monthly.
5. 5
   Phantom‑set catches — number of products correctly held at zero availability due to a missing component carton.

This is a positive metric — it tells you the ATP math is doing its job.

End‑to‑end test plan with sample data

Before this goes anywhere near production, run it against a known 3‑carton sectional and verify every layer.

Sample fixtures: the Halden sectional (PRD-004821) with three primary cartons, each qtyperset = 1.

Test cases:

1. 1
   Full set available. Load onhand = 5 for all three cartons, reserved = 0. Expect setsnow = 5.
2. 2
   Partial set. Load onhand = 5, 5, 0. Expect setsnow = 0. This is the critical one — two cartons stocked, one empty, must return zero.
3. 3
   Reserved stock. Load onhand = 5, 5, 5, reserved = 2 on carton 2. Expect setsnow = 3.
4. 4
   In‑transit split. onhand = 5, 5, 0; intransit = 0, 0, 10. Expect setsnow = 0, setswith_intransit = 5.
5. 5
   Oversell guard. on_hand = 1, 1, 1; attempt to reserve 2 sets. Expect reservation rejected and a hard alert.
6. 6
   Profile mapping. Carton 1 at 41.5 kg, assembly required, set weight > 50 kg. Expect derived profile white_glove; if stored profile says parcel, expect a mismatch flag.
7. 7
   Validation rejection. Submit a carton with weight_kg = 415. Expect weight‑outlier flag and schema still passing (it's under 150? no — 415 exceeds max, so schema rejects). Confirm the hard cap catches it.

If all seven pass against the sample sectional, the schema, the ATP math, and the mapping logic are consistent with each other — which is the whole point of writing them in one spec.

Where this fits

This spec is intentionally narrow: one data model, one set of validation rules, one ATP formula, one mapping table. It doesn't cover how to govern the data over time or reconcile it across systems — that's a different problem. What it does is remove the ambiguity that makes multi‑carton furniture availability unreliable in the first place.

If you implement nothing else from this, implement the MIN across all primary components and the first‑receipt carton measurement. Those two changes alone eliminate the majority of "it said available but we can't ship it" tickets that plague bulky furniture fulfillment — and they give your checkout a number it can actually stand behind.