# Product IDs are unique and consistent

- **Pattern:** `ab-001114` (`ecommerce-catalog.product-data.unique-ids`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001114
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001114)

## Why it matters

Without a database-level primary key uniqueness constraint, two concurrent product creation requests can produce duplicate IDs — a race condition that corrupts order references, inventory lookups, and analytics joins. Application-level uniqueness checks (checking whether a record exists before inserting) cannot close this window: two simultaneous requests both read zero rows, both proceed to insert, and both succeed. CWE-694 (Use of Multiple Resources with Duplicate Identifier) and ISO 25010:2011 functional-correctness both flag this: duplicate product IDs cause order-to-product joins to return wrong results, cascade deletes to hit unintended rows, and audit logs to become unreliable.

## Severity rationale

Critical because duplicate product IDs corrupt order records, inventory counts, and revenue reporting — damage that spreads silently and is difficult to reverse once orders reference the wrong product.

## Remediation

Add a primary key with a database-enforced uniqueness constraint in `prisma/schema.prisma`. Use `@id @default(cuid())` so the ID is generated server-side with collision resistance, not assembled from user input.

```prisma
model Product {
  id String @id @default(cuid())
  // other fields
}
```

For SQL migrations, enforce at the DDL level:

```sql
CREATE TABLE products (
  id VARCHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);
```

Remove any application-level `if (!existingProduct)` guards that attempt to substitute for a real constraint — they don't work under concurrent load.

## Detection

- **ID:** `unique-ids`
- **Severity:** `critical`
- **What to look for:** Count all product entity definitions in the codebase (Prisma models, SQL tables, TypeScript types). For each, check for a primary key with database-level uniqueness constraint (e.g., `@id` in Prisma, `PRIMARY KEY` in SQL, `_id` in MongoDB with unique index). Enumerate every product creation path in the codebase and verify the ID is auto-generated or validated as unique.
- **Pass criteria:** The product model has exactly 1 unique primary key with a database-level uniqueness constraint enforced. All product creation paths (at least 1 must exist) use the same ID generation strategy. Report: "X of Y product creation paths use consistent ID generation."
- **Fail criteria:** No primary key exists, or the primary key is not enforced as unique at the database level (only in application code), or multiple creation paths use different ID strategies.
- **Do NOT pass when:** The ID field exists but relies on application-level uniqueness checks (e.g., `if (!existingProduct)`) instead of database constraints. Application-level checks alone are vulnerable to race conditions.
- **Skip (N/A) when:** Never — all products must have unique identifiers.
- **Cross-reference:** For database constraint patterns and migration best practices, the Database Design & Operations audit covers index and constraint design in depth.
- **Cross-reference:** For API route security around product creation, the API Security audit covers input validation and authorization patterns.
- **Cross-reference:** For unique constraint testing patterns, the Error Resilience audit covers edge case handling and constraint violation responses.
- **Detail on fail:** `"Product model has no primary key defined"` or `"Product ID is created in code but not enforced as unique at the database level — found in src/app/api/products/route.ts"`
- **Remediation:** Define a primary key with database-level uniqueness in `prisma/schema.prisma` using `@id @default()`:

  ```prisma
  model Product {
    id String @id @default(cuid())
    // ... other fields
  }
  ```

  In SQL:

  ```sql
  CREATE TABLE products (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    -- ...
  );
  ```

## External references

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

Taxons: data-integrity

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