Products & Variants

How a seller's catalog is modeled: Product is the sellable unit; ProductVariant captures size/color/SKU splits. Images are Cloudinary URLs stored inline; categories are a String[].

1. Overview

Every Product belongs to a User (OWNER). A product may have 0..N ProductVariant rows — if it has variants, the variant carries SKU, price override, and stock. Categories are String[] on Product (no ProductCategory table). Images are Cloudinary URLs on Product.imageUrl, Product.imageUrls, and ProductVariant.imageUrl (no ProductImage table). Every product write fires revalidateCatalog({ userId }) to bust the public catalog cache.

2. Architecture

3. Data model

See schema.prisma:L316-L357 for Product, L359-L380 for ProductVariant, L382-L399 for ExternalProductId (WhatsApp/Instagram catalog IDs).

Not modeled as separate tables: images (URL strings), categories (String[]).

4. Key flows

4.1 Create product with variants

4.2 Image upload

5. Lifecycle

Delete is a hard delete. OrderItem.productId becomes dangling (no FK cascade).

6. Key files

backend/routes/products.ts:L334 — create
backend/routes/products.ts:L506 — update
backend/routes/products.ts:L721 — delete
backend/routes/products.ts:L836, L960 — image upload
backend/routes/products.ts:L1080 — image delete
backend/routes/products.ts:L1179-L1698 — variant CRUD
backend/lib/validation.ts — Zod schemas
backend/lib/cloudinary-storage.ts — upload helper
src/components/products/ProductForm.tsx
schema.prisma:L316-L399

Every write calls revalidateCatalog({ userId }) — see catalog-caching.md.

7. Env vars & config

Var	Required	Purpose
`CLOUDINARY_CLOUD_NAME`	yes	Image host
`CLOUDINARY_API_KEY`	yes	Upload auth
`CLOUDINARY_API_SECRET`	yes	Upload auth
`REVALIDATION_SECRET` / `FRONTEND_URL`	yes (prod)	Cache invalidation

Defined in .env.example:L21-23.

8. Gotchas & troubleshooting

Product SKU uniqueness is app-level and racy → products.ts:L334 does a findFirst before insert. No DB @@unique. Two concurrent creates with same SKU both pass, both insert.
Variant SKU has no uniqueness check at all → not even findFirst. Duplicates silently allowed.
Variant price falls back to product price when null → ProductVariant.price is nullable. Code reads variant.price ?? product.price. 0 vs null means different things.
Product delete does NOT delete Cloudinary blobs → products.ts:L721. Orphans accumulate; no cleanup job.
Image delete swallows Cloudinary errors → products.ts:L1080 console.warns and still updates DB. Cloudinary down = DB forgets URL, blob persists.
imageUrl stores a relative Cloudinary path → not full https://res.cloudinary.com/.... FE prepends cloud name.
Slug unique [userId, slug] → two sellers can both have /product/tshirt. Scoped to store.
OrderItem FK not enforced on delete → hard-deleting a product leaves dangling productId on historical orders. Old order UI must handle "product not found".
Categories as String[] aren't normalized → renaming a category = write across every product.

9. Extension points

New variant attribute (e.g. material) → add to ProductVariant schema + migration; update Zod + ProductForm.
External channel catalog linking → ExternalProductId with platform: 'WHATSAPP'|'INSTAGRAM'.
Image transformations → do at render time via Cloudinary URL params.

10. Related docs

Inventory Invoice Numbering