# Each variant has a unique SKU or identifier

- **Pattern:** `ab-001120` (`ecommerce-catalog.variant-pricing.unique-sku`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001120
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001120)

## Why it matters

Duplicate SKUs break every system that uses SKU as a lookup key: warehouse management, shipping label generation, return processing, and analytics attribution. CWE-694 (Use of Multiple Resources with Duplicate Identifier) applies directly — two variants sharing `SHIRT-RED-M` means a barcode scan on the warehouse floor resolves ambiguously, order fulfillment picks the wrong record, and return lookups return both variants. Without a database-level `UNIQUE` constraint, application-level deduplication checks are vulnerable to race conditions under concurrent insertion — two simultaneous product imports can both pass validation and both write the same SKU.

## Severity rationale

High because duplicate SKUs cause warehouse fulfillment ambiguity, incorrect return lookups, and broken order-to-variant joins that corrupt inventory counts.

## Remediation

Add a `@unique` constraint to the `sku` field in `prisma/schema.prisma` so the database rejects duplicate SKUs at the storage layer.

```prisma
model ProductVariant {
  id        String  @id @default(cuid())
  sku       String  @unique
  productId String
  product   Product @relation(fields: [productId], references: [id])
  size      String
  color     String
}
```

In `src/app/api/products/route.ts`, catch the Prisma unique constraint error (`P2002`) and return a 409 with a clear message rather than letting it surface as a 500.

## Detection

- **ID:** `unique-sku`
- **Severity:** `high`
- **What to look for:** Count all variant records in seed data, fixtures, or sample responses. For each variant, check whether a SKU field exists. Verify the SKU has a database-level unique constraint (`@unique` in Prisma, `UNIQUE` in SQL). Enumerate any duplicate SKUs found in the data.
- **Pass criteria:** All variants have a SKU field with a database-level unique constraint, and 0 duplicate SKUs exist in seed data or fixtures. Report: "X variants checked, 0 duplicates found, unique constraint: [present/absent]."
- **Fail criteria:** No SKU field exists on the variant model, or the SKU field lacks a unique constraint, or at least 1 duplicate SKU exists in seed data.
- **Skip (N/A) when:** The project has no variants (all products have a single SKU) — confirmed by absence of a variant model or relation in the schema.
- **Cross-reference:** For SKU generation and uniqueness patterns, the Database Design & Operations audit covers unique constraint design.
- **Cross-reference:** For SKU display in order management, the Pre-Launch audit covers data integrity in transactional flows.
- **Cross-reference:** For inventory lookup by SKU, the check `ecommerce-catalog.inventory.stock-initialized` in this audit covers stock tracking per variant.
- **Detail on fail:** `"Variant schema in prisma/schema.prisma has no SKU field"` or `"2 variants share the same SKU: 'SHIRT-RED-M' in prisma/seed.ts"`
- **Remediation:** Add a unique SKU field to the variant schema in `prisma/schema.prisma`:

  ```prisma
  model ProductVariant {
    id String @id @default(cuid())
    sku String @unique
    productId String
    size String
    color String
  }
  ```

## External references

- cwe CWE-694
- iso-25010 functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-001120